Local-First RAG with SQLite: Hybrid Search Using sqlite-vec, FTS5, and Reciprocal Rank Fusion

A lot of RAG projects start with the same architecture diagram: an app server, a Postgres database for metadata, a hosted vector database for embeddings, and a sync job to keep the last two consistent. For a team indexing 20,000 internal docs, or an agent that needs a memory store on a laptop, that's three moving parts too many.

The pattern that keeps showing up in this year's agent tooling is local-first retrieval: one SQLite file holding the text, the metadata, a full-text index, and a vector index. Coding agents, desktop assistants, and several open-source agent memory systems now ship this way. It works because of two SQLite extensions:

• FTS5, which is built into most SQLite distributions and gives you BM25 keyword search.
• sqlite-vec, a small extension by Alex Garcia that adds a vec0 virtual table for vector search. It runs anywhere SQLite runs: Python, Node, Go, mobile, and WASM.

This post builds a working hybrid retriever in about 120 lines of Python, explains why each piece is there, and is honest about where you'll outgrow it.

Why hybrid, and why in one file

Pure vector search is bad at exact tokens. Ask "what does error E_QUOTA_4021 mean?" and embeddings will happily return chunks about quotas in general, ranking the one chunk containing the literal code below them. Pure BM25 has the opposite problem: "how do I stop the bill from growing" never matches a doc titled "Cost controls and spend limits."

Hybrid retrieval runs both and merges the results. Most production RAG systems end up here eventually. The usual reason teams skip it is operational: keyword search lives in Elasticsearch, vectors live in Pinecone, and now you have two indexes to keep in sync with your source of truth.

Putting everything in SQLite removes that problem:

1. One transaction updates everything. Insert a chunk, its FTS row, and its vector together, or roll back together. No orphaned embeddings.
2. Joins are free. Filter by tenant_id, doc_type, or updated_at with plain SQL instead of a vector DB's metadata filter dialect.
3. Deployment is a file. Copy it, back it up, ship it inside a desktop app, or attach it to an agent's sandbox.

The schema

Three tables: a regular table as the source of truth, an FTS5 index over the text, and a vec0 table for embeddings. All three share the same rowid.

# pip install sqlite-vec sentence-transformers
import sqlite3
import sqlite_vec
from sentence_transformers import SentenceTransformer

EMBED_DIM = 384  # matches all-MiniLM-L6-v2; change if you swap models

def open_db(path: str = "rag.db") -> sqlite3.Connection:
    db = sqlite3.connect(path)
    db.enable_load_extension(True)
    sqlite_vec.load(db)
    db.enable_load_extension(False)

    db.executescript(f"""
    CREATE TABLE IF NOT EXISTS chunks (
        id INTEGER PRIMARY KEY,
        doc_id TEXT NOT NULL,
        source TEXT,
        body TEXT NOT NULL
    );

    CREATE VIRTUAL TABLE IF NOT EXISTS chunks_fts USING fts5(
        body,
        content='chunks',
        content_rowid='id',
        tokenize='porter unicode61'
    );

    CREATE VIRTUAL TABLE IF NOT EXISTS chunks_vec USING vec0(
        embedding float[{EMBED_DIM}] distance_metric=cosine
    );
    """)
    return db

A few deliberate choices here:

• content='chunks' makes FTS5 an external-content index. It doesn't store a second copy of your text, but it also won't update itself. You must write to it explicitly (shown below) or use triggers.
• porter unicode61 stems English words, so "billing" matches "billed." If your corpus is mostly code identifiers, try unicode61 alone or the trigram tokenizer instead.
• distance_metric=cosine matters if your embedding model doesn't return normalized vectors. The default is L2. Check the sqlite-vec docs for your installed version, since options have changed across the 0.1.x releases.

Some Python builds, including the default macOS system Python, ship SQLite with extension loading disabled. If enable_load_extension raises AttributeError, use Python from Homebrew, pyenv, or uv, or install pysqlite3.

Ingestion

from sqlite_vec import serialize_float32

model = SentenceTransformer("sentence-transformers/all-MiniLM-L6-v2")

def ingest(db: sqlite3.Connection, doc_id: str, source: str, chunks: list[str]):
    vectors = model.encode(chunks, normalize_embeddings=True)
    with db:  # single transaction for all three tables
        db.execute("DELETE FROM chunks_vec WHERE rowid IN (SELECT id FROM chunks WHERE doc_id = ?)", (doc_id,))
        db.execute(
            "INSERT INTO chunks_fts(chunks_fts, rowid, body) "
            "SELECT 'delete', id, body FROM chunks WHERE doc_id = ?", (doc_id,)
        )
        db.execute("DELETE FROM chunks WHERE doc_id = ?", (doc_id,))

        for text, vec in zip(chunks, vectors):
            cur = db.execute(
                "INSERT INTO chunks(doc_id, source, body) VALUES (?, ?, ?)",
                (doc_id, source, text),
            )
            rowid = cur.lastrowid
            db.execute("INSERT INTO chunks_fts(rowid, body) VALUES (?, ?)", (rowid, text))
            db.execute(
                "INSERT INTO chunks_vec(rowid, embedding) VALUES (?, ?)",
                (rowid, serialize_float32(vec.tolist())),
            )

The function is idempotent per document: re-ingesting doc_id deletes its old chunks from all three tables first, inside the same transaction. That's the property that's hard to get when your vectors live in a separate service. The odd-looking INSERT ... VALUES('delete', ...) is FTS5's documented way to remove rows from an external-content index. It needs the original text, so it has to run before the DELETE FROM chunks.

Querying: two retrievers and a fusion step

def vector_search(db, query: str, k: int = 20) -> list[int]:
    qvec = model.encode([query], normalize_embeddings=True)[0]
    rows = db.execute(
        "SELECT rowid FROM chunks_vec WHERE embedding MATCH ? AND k = ? ORDER BY distance",
        (serialize_float32(qvec.tolist()), k),
    ).fetchall()
    return [r[0] for r in rows]

def keyword_search(db, query: str, k: int = 20) -> list[int]:
    # Quote each term so user punctuation can't break FTS5 query syntax
    terms = [t.replace('"', '""') for t in query.split() if t.strip()]
    if not terms:
        return []
    fts_query = " OR ".join(f'"{t}"' for t in terms)
    rows = db.execute(
        "SELECT rowid FROM chunks_fts WHERE chunks_fts MATCH ? ORDER BY bm25(chunks_fts) LIMIT ?",
        (fts_query, k),
    ).fetchall()
    return [r[0] for r in rows]

def rrf(result_lists: list[list[int]], k: int = 60) -> list[tuple[int, float]]:
    scores: dict[int, float] = {}
    for results in result_lists:
        for rank, rowid in enumerate(results):
            scores[rowid] = scores.get(rowid, 0.0) + 1.0 / (k + rank + 1)
    return sorted(scores.items(), key=lambda x: x[1], reverse=True)

def hybrid_search(db, query: str, top_n: int = 5) -> list[dict]:
    fused = rrf([vector_search(db, query), keyword_search(db, query)])[:top_n]
    out = []
    for rowid, score in fused:
        doc_id, source, body = db.execute(
            "SELECT doc_id, source, body FROM chunks WHERE id = ?", (rowid,)
        ).fetchone()
        out.append({"id": rowid, "doc_id": doc_id, "source": source, "score": round(score, 4), "body": body})
    return out

Why Reciprocal Rank Fusion

BM25 scores are unbounded and depend on the corpus. Cosine distances sit in a narrow band that depends on the embedding model. Normalizing one against the other is fragile, and the weights you tune today break when you change models.

RRF ignores scores and uses only rank positions. Each document gets 1 / (k + rank) from each list it appears in, and the contributions are summed. A chunk ranked #3 by vectors and #2 by keywords beats one ranked #1 by vectors that BM25 never found. The constant k = 60 comes from the original RRF paper (Cormack et al., 2009) and is a reasonable default. Lower values give more weight to the top ranks.

You can weight the retrievers by multiplying each list's contribution. If your users mostly search by error codes and SKUs, give keyword results 1.5x. Measure before you tune. RRF without weights is a surprisingly strong baseline.

Note on the k = ? syntax

The k = ? constraint in the vector query is how vec0 receives the KNN limit. It needs SQLite 3.41 or newer. On older builds, use LIMIT ? instead. Both are described in the sqlite-vec README.

Adding metadata filters

The usual complaint about vector databases is filtered search: "nearest neighbours, but only from tenant 42." With exact KNN you can over-fetch and filter in SQL:

def vector_search_filtered(db, query: str, doc_ids: list[str], k: int = 20) -> list[int]:
    qvec = serialize_float32(model.encode([query], normalize_embeddings=True)[0].tolist())
    placeholders = ",".join("?" * len(doc_ids))
    rows = db.execute(f"""
        WITH knn AS (
            SELECT rowid, distance FROM chunks_vec
            WHERE embedding MATCH ? AND k = ?
        )
        SELECT knn.rowid FROM knn
        JOIN chunks c ON c.id = knn.rowid
        WHERE c.doc_id IN ({placeholders})
        ORDER BY knn.distance LIMIT ?
    """, (qvec, k * 10, *doc_ids, k)).fetchall()
    return [r[0] for r in rows]

Over-fetching (k * 10) is a heuristic. If the filter is highly selective, for example one tenant out of thousands, you'll get fewer than k results. Newer sqlite-vec versions support metadata and partition-key columns declared directly in vec0, which filter during the scan instead of after it. If you're multi-tenant, read the current docs on partition key columns before using the over-fetch approach.

How far does this scale?

vec0 currently does exact (brute-force) search. Every query scans every vector. That sounds bad, but modern CPUs are fast at it:

Corpus size (384-dim float32)	Raw vector storage	Exact KNN latency (rough)	Verdict
10k chunks	~15 MB	~1 ms	Trivial
100k chunks	~150 MB	single-digit ms	Comfortable
500k chunks	~770 MB	tens of ms	Fine for most apps, start measuring
2M+ chunks	~3 GB+	100 ms+	Consider binary quantization or an ANN index

These are order-of-magnitude figures. Benchmark on your own hardware. Two techniques extend the range a lot:

• Binary quantization. sqlite-vec supports bit[N] columns. Store a 1-bit-per-dimension copy, run a fast Hamming-distance pre-filter for the top 200, then re-rank those with full float vectors. Storage drops 32x and the quality loss after re-ranking is usually small.
• Smaller embeddings. Matryoshka-trained models let you truncate 1024-dim vectors to 256 with modest recall loss.

SQLite + sqlite-vec vs. the alternatives

	SQLite + sqlite-vec + FTS5	Postgres + pgvector	Hosted vector DB
Infra to run	None (a file)	A database server	A managed service + sync job
Hybrid search	FTS5 + RRF in SQL/app	tsvector + RRF	Varies, often built in
Transactional with your data	Yes	Yes	No
Concurrent writers	One at a time (WAL helps reads)	Many	Many
ANN index for 10M+ vectors	Not yet	HNSW / IVFFlat	Yes
Best fit	Agents, desktop/edge apps, single-tenant tools, prototypes, < ~1M chunks	Multi-user web apps already on Postgres	Very large corpora, teams that don't want to run search

The real limit is usually concurrent writes, not vector count. SQLite allows one writer at a time. A desktop app or an agent's memory is fine. A web app where 200 users upload documents at once needs a queue in front of ingestion, or Postgres.

Common pitfalls

1. Forgetting to sync external-content FTS. If you insert into chunks but not chunks_fts, keyword search silently returns nothing for new docs. Put all three writes in one function, or add AFTER INSERT/DELETE triggers.
2. Dimension mismatch after swapping models. vec0 columns have a fixed size. Switching from a 384-dim to a 1024-dim model needs a new table and a full re-embed. Store the model name in a meta table and check it at startup.
3. Raw user input in MATCH. FTS5 has its own query syntax, so a query like C++ "unterminated raises an error. Always quote or sanitize terms, as keyword_search does above.
4. Mixing normalized and unnormalized vectors. If you use L2 distance with some vectors normalized and some not, rankings get quietly wrong. Pick one convention and enforce it at ingest.
5. Skipping WAL mode. Run PRAGMA journal_mode=WAL; once. Without it, readers block during ingestion and your agent stalls while it re-indexes.
6. Evaluating only end-to-end answers. Build 50 query → expected-chunk pairs and track recall@5 for vector-only, keyword-only, and hybrid. That's how you know whether the hybrid layer is helping your corpus, and it takes an afternoon.
7. Assuming the extension is available everywhere. Lambda runtimes, some Docker base images, and older Python builds ship SQLite without extension loading. Check this in CI, not after you deploy.

Checklist before you ship

• WAL mode enabled
• Ingest is idempotent per document and wrapped in one transaction
• Embedding model name and dimension stored and checked at startup
• FTS queries sanitized
• Recall@k measured for vector, keyword, and hybrid on a labelled set
• Latency measured at your expected corpus size, plus 3x headroom
• Backup strategy (it's a file: sqlite3 rag.db ".backup rag-backup.db" or Litestream for continuous replication)

Next steps

Take the code above, point ingest at a folder of your team's markdown docs, and run 20 real questions through vector_search, keyword_search, and hybrid_search side by side. You'll probably see queries where hybrid gets the right chunk and one of the single retrievers misses it. That's a concrete case for hybrid retrieval.

From there, the natural upgrades are a cross-encoder reranker on the fused top 20, and contextual chunk headers to improve what both retrievers see. Neither requires leaving SQLite.

If you want to build retrieval systems like this end to end, with evals and code review from engineers who run them in production, that's the core of Joinloop's AI engineering cohort.