Kent C. Dodds Adds SQLite FTS5 to Vector Search

WHY IT MATTERS: Pure vector search is now table stakes, but it fails on exact identifiers. Kent C. Dodds discovered this when his own semantic search could not surface the canonical "React Testing Library" introduction post despite an exact title match. Embedding models optimize for conceptual meaning, so they routinely miss library names, API identifiers like useState or loader, error messages, and version numbers. If your product includes technical documentation, code snippets, or structured content, a search pipeline that relies only on embeddings will frustrate users who know exactly what they are looking for.

WHAT CHANGED: Dodds kept his existing Cloudflare Vectorize semantic pipeline and added lexical retrieval via SQLite FTS5, which provides BM25-ranked full-text search with minimal infrastructure because his site already uses SQLite replicated through LiteFS. The architecture works as follows: indexers emit lexical artifacts as JSON files to R2 alongside existing vector manifests; the application syncs those artifacts into a local FTS5 index; at query time, lexical and semantic retrieval run in parallel; and results merge through Reciprocal Rank Fusion. The entire first implementation took roughly twenty minutes using Cursor with GPT-5.4 through an iterative workflow that included investigating the existing system, diagnosing failure modes, exploring alternatives, generating an implementation plan, and verifying data landed correctly before deployment.

WHAT TO WATCH: Dodds notes that production revealed issues the development build did not, suggesting that hybrid search tuning is an ongoing process rather than a one-time setup. Engineers adopting similar pipelines should monitor RRF weighting between lexical and semantic signals, measure recall on exact identifier queries separately from natural-language questions, and consider whether their own SQLite or Postgres full-text extensions can avoid adding a new managed service. The broader pattern is that hybrid retrieval is becoming the default for site search, not an advanced optimization.

Source: Kent C. Dodds Blog