# SQLite source history: bm25() sign-convention change

## Metadata

| Field | Value |
|---|---|
| Institute | Molodetz Institute — Research & Retrieval Division |
| Subject | agent-memory-recency-tracking |
| Document type | Source Dossier |
| Tier | 1 |
| Source title | SQLite source/commit history entry concerning the bm25() auxiliary function |
| Source URL | https://sqlite.org/src/info/3ee7b5a9f987c269 |
| Source publisher / author | SQLite project (official source repository) |
| Source type | other (source control history entry) |
| Source date (published) | Unknown — not stated (historical commit; exact date not captured by this research pass) |
| Date researched | 2026-10-09 |
| Researched by | tai (agent), model: Claude Sonnet 5, on behalf of retoor |
| Research method | web search synthesis |
| Confidence | Medium |
| Status | Active |

## Summary

A SQLite source-history record documenting that the `bm25()` FTS5 auxiliary ranking function's result is multiplied by -1, so that a *lower* (more negative) score means a *better* match — meaning correct usage requires `ORDER BY rank` ascending, not descending, which is the opposite of the intuitive "higher score = better" assumption.

## Key findings

- `bm25()` scores are inverted relative to naive expectation: better matches produce lower (more negative) numbers.
- Consequently, ranking queries must sort ascending (`ORDER BY rank` with no `DESC`) to get best-matches-first, not descending.

## Pros / strengths

- Primary-source (SQLite's own project history) confirmation of a detail that is easy to get backwards and silently misrank results if assumed rather than checked.

## Cons / limitations / caveats

- This is a narrow, mechanical fact about FTS5's API, not itself evidence about recency tracking or write amplification — it's included here because it surfaced in the same research query about combining BM25 with recency-aware ranking, and is directly relevant to *correctly implementing* any future phase that blends `last_used` into search ranking.

## Relevance to tai

Tai's existing `fts_search()` code already sorts by `bm25(fts_docs)` ascending (confirmed independently in the codebase, not from this source) — this dossier exists so that if/when a future phase blends a recency signal into ranking, whoever implements it does not accidentally assume "higher bm25 score is better" and invert the ranking.

## Changelog

| Date | Change |
|---|---|
| 2026-10-09 | Initial dossier created. |