Local Knowledge Base Search With FTS5 and Vector Embeddings—No Cloud Required

Most knowledge base solutions for AI systems assume you’ll use a cloud vector database. We built a fully local system using SQLite that handles both full-text and semantic search, costs nothing to run, and works entirely offline. Here’s the architecture.

The Problem With Cloud Vector Databases

Pinecone, Weaviate, Chroma, and similar tools are excellent products, but they introduce dependencies: API keys, network latency, monthly costs that scale with data volume, and—most importantly for sensitive workloads—data leaving your infrastructure. For an internal knowledge base containing operational documents, communication summaries, and proprietary procedures, those tradeoffs weren’t acceptable.

The Architecture

Each knowledge base is a single SQLite file with two search mechanisms working in parallel:

• FTS5 (Full-Text Search): SQLite’s built-in full-text index. Handles keyword queries, boolean operators, and prefix matching. Extremely fast and requires no external dependencies.
• Vector embeddings: Dense vector representations of each document chunk, generated by a locally-running embedding model. Stored in a companion table and used for semantic similarity search.

At query time, both indexes are searched in parallel. Results are merged using Reciprocal Rank Fusion (RRF), a well-studied technique for combining ranked lists from different retrieval systems. FTS5 results are weighted at 2.0 (keyword precision matters more for most queries); vector results are weighted at 1.0. A word-bonus heuristic boosts documents whose filenames or titles contain query terms.

Graceful Degradation

The vector search depends on a locally-running embedding model via Ollama. If Ollama is unavailable—machine is under load, model not yet downloaded, Ollama not running—the system automatically falls back to FTS5-only search. The results are slightly less semantically rich but still highly functional. This means the knowledge base works reliably even in resource-constrained conditions.

What We Index

We maintain five separate knowledge bases, each for a different domain:

• Workspace documents: markdown files, plans, research notes, brainstorms
• Live signal layer: extracted concepts from emails, messages, and chat (the “subconscious”)
• Operations knowledge: SOPs, procedures, reference guides
• CRM: 10,000+ contact records with notes and interaction history
• Access control event log: 200,000+ timestamped events

Query Performance

On a standard laptop, FTS5 queries return in under 5 milliseconds for databases with hundreds of thousands of rows. Vector queries add 50–200ms depending on the embedding model and result set size. This is fast enough for interactive use and well within the latency budget for agent-initiated queries.

The Case for Local First

For many knowledge base use cases, local SQLite + FTS5 is the right answer—not a compromise. It’s faster than most cloud alternatives for keyword retrieval, eliminates API dependencies, and keeps sensitive data on-premise. The combination with local vector embeddings gives you hybrid search that rivals commercial solutions at zero marginal cost.