Hybrid search in a single SQLite file

When I built search for a documentation chatbot, the obvious route was a separate vector database. I kept everything in one SQLite database on Turso instead: documents, vectors and the text index together, so each question costs a single network round trip.

The schema

There are three tables:

CREATE TABLE documents (
  doc_id  TEXT NOT NULL,
  locale  TEXT NOT NULL,
  public  INTEGER NOT NULL CHECK (public IN (0, 1)),
  roles   TEXT NOT NULL CHECK (json_type(roles) = 'array'),
  PRIMARY KEY (doc_id, locale)
);

CREATE TABLE chunks (
  chunk_id TEXT NOT NULL PRIMARY KEY,
  doc_id   TEXT NOT NULL,
  locale   TEXT NOT NULL,
  content  TEXT NOT NULL,
  emb      F32_BLOB(768) NOT NULL,
  FOREIGN KEY (doc_id, locale) REFERENCES documents (doc_id, locale) ON DELETE CASCADE
);

CREATE VIRTUAL TABLE chunks_fts USING fts5(
  chunk_id UNINDEXED,
  text,
  tokenize = "unicode61 remove_diacritics 2"
);

Access control (public, roles) lives only in documents. An earlier version copied the flag onto every chunk, and a stale copy leaked a restricted document on both legs of the search. Now both legs JOIN documents and filter there.

The two legs

The dense leg orders by cosine distance. No ANN index: with 808 chunks, a full scan took 3.8 ms at 768 dimensions (12.5 ms at 3072). An approximate index only pays off once the scan starts to hurt.

SELECT c.chunk_id, vector_distance_cos(c.emb, vector32(?)) AS score
  FROM chunks c
  JOIN documents d ON d.doc_id = c.doc_id AND d.locale = c.locale
 WHERE (d.public = 1 OR EXISTS (SELECT 1 FROM json_each(d.roles) WHERE value IN (?)))
 ORDER BY score, c.chunk_id
 LIMIT ?;

The lexical leg uses FTS5 and bm25(), with the same JOIN and the same filter:

SELECT c.chunk_id, bm25(chunks_fts) AS score
  FROM chunks_fts
  JOIN chunks c ON c.chunk_id = chunks_fts.chunk_id
  JOIN documents d ON d.doc_id = c.doc_id AND d.locale = c.locale
 WHERE chunks_fts MATCH ?
   AND (d.public = 1 OR EXISTS (SELECT 1 FROM json_each(d.roles) WHERE value IN (?)))
 ORDER BY score, c.chunk_id
 LIMIT ?;

Two things bit me here. The filter has to come before LIMIT, in the same query: the wrong shape (paging in a subquery and filtering afterwards) returned 7 rows when I asked for 10, with no error at all. And chunk_id breaks ties in the ordering, because SQLite may return equal scores in any order, and the fusion works on positions, so without a tie-break the result can change between runs.

One round trip

Both queries go in the same batch(), in read mode:

const [dense, lexical] = await client.batch(
  [
    { sql: denseSql, args: [vector, ...aclArgs, 40] },
    { sql: bm25Sql, args: [match, ...aclArgs, 40] },
  ],
  "read",
);

Measured on the real database: batch() took 63.1 ms, against 122.5 ms for two execute() calls. With a remote database, the round trip is half of the query time. Read mode also makes a DELETE slipped in by mistake fail instead of running.

Before sending, the code checks that both statements carry the access filter. A query without the filter still returns normal-looking rows, so a leak would go unnoticed.

Merging the results

The two legs' scores don't compare: cosine goes from 0 to 2, and bm25() is a negative number that depends on the corpus. So the fusion uses position, with Reciprocal Rank Fusion:

export function fuseByRrf(legs: string[][], k = 60) {
  const scores = new Map<string, number>();
  for (const leg of legs) {
    const seen = new Set<string>();
    leg.forEach((id, i) => {
      if (seen.has(id)) return; // count once, at the best position
      seen.add(id);
      scores.set(id, (scores.get(id) ?? 0) + 1 / (k + i + 1));
    });
  }
  return [...scores].sort((a, b) => b[1] - a[1]);
}

Each leg fetches 40 candidates and the top 20 of the fusion come out.

What the benchmark showed

Before defending hybrid search, I went and measured it. I ran a public benchmark close to technical documentation (CQADupStack, 13,145 questions). At 100 results, dense and hybrid tied within the margin of error. At 50, dense was better. BM25 alone trailed by 12 to 20 points.

On that corpus, keyword search added no recall. It stays in the system for API names, error codes and exact phrases, which vectors handle badly, and that's the subject of the post on Turso FTS in RAG. At least now I know what it's worth.