Hybrid Retrieval Pipelines: Why SQLite FTS5 + Dense Vectors Beat Naive RAG

When building Retrieval-Augmented Generation (RAG) applications, the standard prescription is simple: chunk your documents, run them through an embedding model, load the vectors into a dedicated vector database, and perform cosine similarity search.

This is dense-only retrieval. While it works well for generic semantic matching, it frequently fails in domain-specific applications (such as legal, medical, or corporate wikis) where exact term matching, code sections, and keyword precision are critical.

In this article, we'll walk through why dense-only RAG pipelines fail in specialized domains, explore the mechanics of a hybrid search architecture (SQLite FTS5 + Dense Vectors + Reciprocal Rank Fusion), and review how to build a highly optimized, low-latency search engine that runs locally under a 48 MB RAM footprint.

The Architecture Overview #

Instead of spinning up a heavy vector database container, we can decouple the storage and indices into a local SQLite database and an in-memory cache.

Here is the visual representation of our multi-stage hybrid retrieval pipeline:

Figure: Hybrid retrieval pipeline flow combining keyword matching and vector search.

And here is the flow representation in text form:

                Query
                  │
      ┌───────────┴───────────┐
      │                       │
 SQLite FTS5            Dense Vectors
   (BM25)             (Cosine Similarity)
      │                       │
      └───────────┬───────────┘
                  │
         Reciprocal Rank Fusion (RRF)
                  │
          Domain Reranker
                  │
            Top-K Documents
                  │
                 LLM

This pipeline runs entirely on a Node.js backend. The LLM remains almost unchanged; nearly all retrieval accuracy and performance gains are derived by optimizing the database query and fusion layers.

Why Vector-Only Retrieval Fails on Domain Data #

To understand the need for hybrid search, consider the following query asked to a dense-only legal retrieval prototype:

Query: "Someone forged my signature"

In a naive dense-only retrieval setup (v1), the results looked like this:

Query:
"Someone forged my signature"
 
Top result (v1):
❌ BNS Section 180 — Possession of counterfeit coin
 
Expected:
✅ BNS Section 336 — Forgery
✅ BNS Section 340 — Using forged document

The Problem: Semantic Generalization #

The embedding model wasn't "wrong." It was doing exactly what it was trained to do: map semantically close concepts close together in vector space.

This failure isn't unique to legal search. The same pattern appears in internal documentation, code search, healthcare, finance, and enterprise knowledge bases—anywhere exact terminology matters as much as semantic similarity.

Because "forgery" and "counterfeit" ended up close in embedding space, the retriever ranked counterfeit coin and government stamp provisions above the actual definition of document forgery. The retriever generalized too aggressively, missing the specific definition of forgery (BNS Section 336) because the word "signature" was semantically distant from generic statutory descriptions of the offence.

System Bloat & Overlap #

In a standard RAG prototype, developers load embedding coordinates alongside large raw text strings (document metadata and full content) directly into application memory.

For a corpus of approximately 4,900 legal sections, this linear JSON search array causes the Node.js process to consume over 320 MB of RAM at startup. Additionally, redundant sections across different categories clutter the LLM's context window, degrading synthesis accuracy.

Building the Hybrid Solution #

A robust retrieval system requires two search indexes working in parallel:

1. Sparse Index (Lexical/Keyword): Captures exact terms, section numbers, and names using traditional TF-IDF or BM25 ranking.
2. Dense Index (Semantic): Captures the abstract conceptual meaning of the query.

1. Sparse Retrieval with SQLite FTS5 #

By storing our records in SQLite, we gain access to an embedded, ACID-compliant database with zero network hop latency and no complex infrastructure to manage. Furthermore, its built-in FTS5 extension compiles virtual tables to run full-text searches with highly optimized BM25 rankings.

We define our SQLite tables like this:

-- Structured store for legal acts and metadata
CREATE TABLE IF NOT EXISTS laws (
    id TEXT PRIMARY KEY,
    law_name TEXT NOT NULL,
    law_code TEXT NOT NULL,
    chapter TEXT,
    title TEXT NOT NULL,
    content TEXT NOT NULL
);
 
-- Virtual table for exact BM25 keyword matching
CREATE VIRTUAL TABLE IF NOT EXISTS laws_fts USING fts5(
    id UNINDEXED,
    law_name,
    chapter,
    title,
    content,
    content='laws',
    content_rowid='rowid'
);

When a query is received, we run a virtual FTS5 match query, which executes in single-digit milliseconds and guarantees that exact keywords (like "forgery", "signature", or "Section 65") rank highly:

SELECT id, BM25(laws_fts) as rank 
FROM laws_fts 
WHERE laws_fts MATCH ? 
ORDER BY rank 
LIMIT 50;

Synchronizing the FTS5 Virtual Table #

Because laws_fts is defined as an external-content table (content='laws'), the virtual table does not duplicate the content text itself, saving disk space. However, we must ensure that any insertions, deletions, or updates to the laws table are propagated to the virtual table index.

In production, we can keep the index in sync automatically using standard SQLite triggers:

-- Trigger to sync insertions
CREATE TRIGGER IF NOT EXISTS laws_ai AFTER INSERT ON laws BEGIN
  INSERT INTO laws_fts(rowid, law_name, chapter, title, content) 
  VALUES (new.rowid, new.law_name, new.chapter, new.title, new.content);
END;
 
-- Trigger to sync deletions
CREATE TRIGGER IF NOT EXISTS laws_ad AFTER DELETE ON laws BEGIN
  INSERT INTO laws_fts(laws_fts, rowid, law_name, chapter, title, content) 
  VALUES ('delete', old.rowid, old.law_name, old.chapter, old.title, old.content);
END;
 
-- Trigger to sync updates
CREATE TRIGGER IF NOT EXISTS laws_au AFTER UPDATE ON laws BEGIN
  INSERT INTO laws_fts(laws_fts, rowid, law_name, chapter, title, content) 
  VALUES ('delete', old.rowid, old.law_name, old.chapter, old.title, old.content);
  INSERT INTO laws_fts(rowid, law_name, chapter, title, content) 
  VALUES (new.rowid, new.law_name, new.chapter, new.title, new.content);
END;

If you prefer to load data in batches (for instance, via a nightly migration process), you can disable triggers to speed up writes and manually rebuild the search index in a single command afterwards:

INSERT INTO laws_fts(laws_fts) VALUES('rebuild');

2. Lightweight In-Memory Vector Cache #

To keep the application's RAM footprint minimal, we discard all text strings and metadata from RAM. We load only the id (string) and the coordinate list—pre-processed into a compact Float32Array object—into memory:

// Compact in-memory vector cache
const vectorCache = [
  { id: "bns_336", vector: new Float32Array([...]) },
  { id: "bns_340", vector: new Float32Array([...]) }
];

When a search runs, we calculate cosine similarity against this typed cache in memory. Once we identify the top 5 document IDs, we perform a single, fast SQL query to hydrate the text from the local SQLite database file on disk. This reduces memory usage by 85% (from 320 MB to 48 MB).

3. Reciprocal Rank Fusion (RRF) #

We now have two lists of rankings: the top 50 matches from FTS5 (sparse) and the top 50 matches from our vector cache (dense).

BM25 and cosine similarity produce scores on completely different scales. Rather than trying to normalize incompatible scores, Reciprocal Rank Fusion (RRF) ignores the score values entirely and combines only ranking positions. This makes it robust across heterogeneous retrieval systems by summing the reciprocal rank of each item across both runs:

RRF_Score(d) = Sum_{m in M} (1 / (k + rank_m(d)))

Where d represents a document, M is the set of retrieval channels (FTS5 and dense vector search), rank_m(d) is the 1-based rank of the document in channel m, and k is a constant (typically 60) that dampens the influence of high-ranking outliers. Here is the JavaScript implementation:

const rrfScores = new Map();
const k = 60;
 
// Process vector rankings (dense)
vectorRankings.forEach((item, index) => {
  rrfScores.set(item.id, 1 / (k + index + 1));
});
 
// Process FTS5 keyword rankings (sparse)
ftsRankings.forEach((item, index) => {
  const existingScore = rrfScores.get(item.id) || 0;
  rrfScores.set(item.id, existingScore + (1 / (k + index + 1)));
});
 
// Sort candidates based on RRF scores
const mergedRanking = Array.from(rrfScores.entries())
  .sort((a, b) => b[1] - a[1])
  .slice(0, 20); // Retain top 20 candidates for reranking

4. Deterministic Domain Reranking #

Finally, to eliminate domain-specific retrieval errors (like matching "forged signature" to "counterfeit coin") without running a heavy cross-encoder model, we implement a rule-based Domain Reranker. This is a deterministic rule-based reranker tailored to the legal domain—not a learned neural cross-encoder.

It evaluates the top 20 fused candidates and applies deterministic weights based on specific keyword flags:

// Heuristic Domain Reranking
if (isDocumentForgeryRelated) {
  if (isCoinOrStampOrCurrency) {
    adjustedScore *= 0.01; // heavily penalize counterfeit coin/stamps (reduce by 99%)
  } else if (titleLower.includes('forgery') || titleLower.includes('forged')) {
    adjustedScore *= 3.0; // strong boost for direct forgery definitions/offences
  }
}

Performance and System Latency #

To evaluate the redesign, I assembled a benchmark of 100 manually verified legal queries spanning criminal law, cybercrime, family law, consumer protection, and procedural law.

Latency is based on 100 benchmark queries. Memory is process-level heap size at startup. Accuracy is evaluated on top-5 target matches using a manually verified benchmark dataset of 100 queries.

Benchmark Methodology and System Context #

To ensure these performance and quality metrics are fully reproducible, here is the technical context under which they were measured:

• Corpus Size: Approximately 4,900 legal sections spanning various Indian legal acts (e.g., Bharatiya Nyaya Sanhita (BNS), Bharatiya Nagarik Suraksha Sanhita (BNSS), Information Technology Act, Evidence Act, and personal laws).
• Hardware Profile: Benchmark executed locally on an AMD Ryzen 5 5600H CPU with 16 GB RAM.
• Software Stack: Node.js v22.x and SQLite v3.x (accessed via the synchronous better-sqlite3 driver).
• Embedding Model: Xenova's all-MiniLM-L6-v2 ONNX pipeline generating 384-dimensional dense vectors.
• Query Construction: The benchmark dataset consists of 100 distinct queries created to reflect realistic user legal inquiries across criminal, family, cyber, and procedural law.
• Relevance Judgment: Retrieval is marked successful if the target statutory code (manually pre-mapped as the correct provision by legal domain review) is returned within the top 5 slots of the fused ranking.

None of these improvements required changing the language model. The gains came almost entirely from retrieval engineering. For the signature forgery query, the top retrieved references aligned with the expected legal provisions:

1. BNS 340: Forged document and using it as genuine
2. BNS 336: Forgery definition and penalty
3. BNS 339: Possession of forged document
4. BNS 335: Making a false document
5. Evidence Act Section 65: Proof of signature and handwriting

System Screen Demonstrations #

User Chat Interface #

Clean, legal explanation interface for end users:

Structured Offence and Citation Details #

Deduplicated citations with developer metrics visible in Developer Mode:

System Evaluation Dashboard #

Performance comparisons and technical architecture story:

Operational Fit and Architectural Tradeoffs #

When this architecture makes sense #

This approach is highly suitable under the following parameters:

• Corpus <100k–500k documents: Linear typed array scans and SQLite FTS5 index scans are highly efficient, completing in single-digit milliseconds without needing complex parallel indexing trees.
• Local-First / Edge Applications: The entire search database resides inside a single file. This is highly suitable for desktop apps, edge nodes (e.g. Fly.io, Cloudflare Workers, Vercel Serverless with persistent mounts), or local CLI utilities because it eliminates network roundtrip times.
• Kubernetes Workloads with Ephemeral/Local Storage: Fits beautifully inside a Kubernetes pod using local volumes or Persistent Volume Claims (PVC). By keeping the database embedded in the application container, you avoid provisioning, configuring, and monitoring secondary database container pods (like Qdrant or Milvus), saving hundreds of megabytes of RAM per deployment.
• Serverless and Low RAM Constraints: Perfect for serverless compute models (e.g. AWS Lambda, Google Cloud Run) where memory is restricted. An optimized 48 MB footprint keeps containers small, minimizes execution costs, and avoids out-of-memory errors during concurrent requests.
• Low Operational Complexity: No servers to provision, no connection pools to manage, and zero maintenance overhead.

When to graduate to dedicated Vector Databases (Elasticsearch/Qdrant/Milvus) #

Consider migrating to a distributed search architecture when:

• Millions of documents: The corpus exceeds memory boundaries, and you need highly parallelized vector indices like HNSW.
• Decoupled Scaling: The index compute needs to scale independently from storage.
• High Write Throughput: The app requires concurrent, real-time background indexing and streaming text updates.
• Horizontal Availability: The database must support replicas, high availability clustering, or managed cloud services.

Key Takeaways #

Modern RAG systems are increasingly becoming search systems with an LLM attached. If retrieval returns the wrong documents, no prompt or model can recover the missing information. Investing in indexing, ranking, and retrieval architecture often yields larger gains than changing the model itself.

Repository #

The complete implementation—including SQLite ingestion, FTS5 indexing, Reciprocal Rank Fusion, evaluation queries, and benchmark samples—is available on GitHub.

GitHub: https://github.com/ishwar170695/LawDecoder