FTS5 + sqlite-vec — hybrid search in Arachne
July 3rd. Arachne already had a solid extraction pipeline with 4 fallback layers, SQLite cache with FTS5, and thousands of indexed pages. But one essential piece was missing: semantic search.
The FTS5 cache I had built in June was excellent for keyword searches — you search “Cloudflare bypass” and it returns pages that mention these exact terms, ranked by BM25. But it didn’t understand context. “How to bypass Cloudflare protection” wouldn’t find the same result because the words don’t match exactly.
The solution? Hybrid search — combining FTS5 (keyword) with vector embeddings (semantics) using RRF (Reciprocal Rank Fusion).
The problem with pure FTS5
SQLite FTS5 is phenomenal for text search. Using porter unicode61 as tokenizer, it handles English stemming (running → run) and Unicode (accents in Portuguese). The schema looks like this:
CREATE VIRTUAL TABLE IF NOT EXISTS search_index_fts USING fts5(
title,
text_content,
description,
content='search_index',
content_rowid='id',
tokenize='porter unicode61'
);The virtual table mirrors the real search_index table using FTS5’s external content feature — no data duplication. Triggers keep everything in sync automatically:
CREATE TRIGGER search_index_fts_insert AFTER INSERT ON search_index
BEGIN
INSERT INTO search_index_fts(rowid, title, text_content, description)
VALUES (new.id, new.title, new.text_content, new.description);
END;This works well, but it has a fundamental limitation: it needs lexical matching. The user asks “solve captcha” and the content says “bypass Cloudflare” — FTS5 can’t connect the dots.
Enter sqlite-vec
sqlite-vec is a SQLite extension that adds vector search. Arachne uses the all-MiniLM-L6-v2 model which generates 384-dimensional embeddings. Each extracted text chunk gets a vector stored as a BLOB:
class SearchIndex(SQLModel, table=True):
# ...
embedding: Optional[bytes] = Field(default=None) # 384 floats, all-MiniLM-L6-v2Semantic search transforms the user’s query into the same embedding and compares using cosine similarity in the database. The result: “how to bypass Cloudflare” finds pages about “WAF bypass” even with no terms in common.
The magic: Reciprocal Rank Fusion (RRF)
The problem with merging two searches that have different score distributions (BM25 vs cosine) is that you can’t simply sum the scores — they’re on completely different scales.
The classic solution is RRF: ignore absolute scores and work with positions (ranking):
def search_hybrid(self, query_text, query_vector, kb_id,
top_k=10, min_score=0.3):
# 1. FTS5 search (keyword)
fts_results = _fts_search(kb_id, query_text, limit=top_k * 2)
# 2. Vector search (semantic)
vec_results = self.search(query_vector, kb_id, top_k=top_k * 2,
min_score=min_score)
# 3. RRF fusion
K_RRF = 60
scores = {}
for rank, r in enumerate(fts_results):
key = (r["doc_id"], r["chunk_index"])
scores[key] = scores.get(key, 0.0) + 1.0 / (K_RRF + rank)
for rank, r in enumerate(vec_results):
key = (r["doc_id"], r["chunk_index"])
scores[key] = scores.get(key, 0.0) + 1.0 / (K_RRF + rank)
# Sort by RRF score
merged.sort(key=lambda r: r["score"], reverse=True)
return merged[:top_k]Each result gets 1 / (K + position) from each ranking. K=60 is the standard literature value (Cormack et al.) — it smooths the difference between top-ranked items. A result appearing 5th in FTS5 and 10th in vector search gets 1/65 + 1/70 = ~0.029, while one appearing only 30th in vector search gets just 1/90 = ~0.011.
Result: documents relevant to BOTH searches rise to the top, documents relevant to only one still appear but with a lower score.
Cross-encoder reranker as the cherry on top
After RRF fusion, Arachne still passes the top-K results through a cross-encoder (a BERT model that compares query and document directly). While the embedding is a “black box” that compresses text into 384 numbers, the cross-encoder analyzes each (query, document) pair token by token:
def semantic_search(query, kb_id, top_k=10, hybrid=True, use_reranker=True):
results = vector_store.search_hybrid(query, query_vector, kb_id, top_k=top_k)
if use_reranker and len(results) >= 2:
results = rerank(query, results, top_k=top_k)
return resultsThe reranker is expensive (O(n·m) where n = results, m = document tokens), so it only runs on the top-K from hybrid search (~20 items), not the entire corpus. The precision gain is notable — in tests, MRR (Mean Reciprocal Rank) went from 0.72 to 0.89 with the reranker.
Final architecture
┌─────────────┐ ┌──────────────────┐
│ Query │───→│ Embedding │
│ "bypass │ │ (all-MiniLM) │
│ Cloudflare"│ └────────┬─────────┘
└─────────────┘ │
│ ▼
│ ┌──────────────────┐
│ │ Vector Search │
│ │ (sqlite-vec) │
│ └────────┬─────────┘
▼ │
┌─────────────┐ │
│ FTS5 │ │
│ Keyword │ │
│ Search │ │
└────────┬────┘ │
│ │
▼ ▼
┌──────────────────────────┐
│ RRF Fusion (K=60) │
└────────────┬─────────────┘
▼
┌──────────────────────────┐
│ Cross-encoder Reranker │
└────────────┬─────────────┘
▼
┌──────────────────────────┐
│ Final Result │
│ (ranked, relevant) │
└──────────────────────────┘Lessons learned
- FTS5 with external content is underrated — you get full-text search without duplicating data, and triggers keep everything automatically synced. Perfect for projects that already use SQLite.
- RRF > score blending — trying to normalize BM25 + cosine to the same scale is a headache. RRF ignores absolute values and works with rank, which is much more stable.
- sqlite-vec vs external services — for mid-sized projects (< 500K chunks), sqlite-vec eliminates the need for a dedicated vector database (Pinecone, Qdrant). Less network latency, less cost.
- Cross-encoder at the end — approximate embedding + precise reranker is the gold standard for retrieval. The embedding cheapens the search (thousands of docs → top 20), the reranker refines (top 20 → final result).
Test commands
Hybrid search has dedicated tests that I use to validate changes:
Every self-respecting search pipeline has two legs: keyword for lexical precision, semantics for conceptual recall. Together with RRF, they complement each other. Apart, they leave gaps.