I built a search layer over my own notes. Now I can text my assistant a question and get an answer from my vault, not a guess. An indexer walks the notes, splits them on headings, and embeds each chunk with a local model. It writes the vectors into SQLite. 247 files, 1,455 chunks. Ask a question, and the system embeds the question, pulls the nearest chunks, and gives them to a model with an instruction to cite its sources.
It worked immediately. That was a warning sign, and I missed it.
Then I asked for my Rotella plan. That is a golf practice note that I wrote months earlier. The system returned nothing useful. The file was in the index. I checked. The database held its chunks and its embeddings, and the search would not surface it.
The tell: a longer question found the file immediately. Ask about the mental-game approach in that book, in a full sentence, and the correct file came back first every time. Ask for “rotella plan,” and the file vanished.
Embeddings are good at subjects and bad at rare tokens
That gap is the full diagnosis. A semantic embedding encodes the subject of a passage. Two words with one unusual proper noun almost have no subject. The model has almost no context to place, so the query vector lands in a vague position. A vague position is equidistant from everything. From there, a 500-token chunk about golf psychology and a 500-token chunk about network hardware are both far away.
A different embedding model does not fix this flaw. Each semantic retriever has this shape. Short, rare, high-signal tokens are the strength of a keyword index. They are the weakness of a vector index.
So: add a keyword index. The pleasing part: I needed nothing new for that. SQLite ships with FTS5 compiled in. Full-text search with BM25 ranking, with no new package, no service, and no daemon. It already sat inside the database that I already used. Every SQLite question ends this way.
The interesting problem is fusion, not retrieval
Two retrievers are easy to run. The combination is where people go wrong. I went wrong there for about one hour.
The naive move is a blend of the scores. It does not work, and the reason is worth understanding. Cosine distance and BM25 do not measure the same thing on the same scale. Cosine sits in a bounded range. BM25 is unbounded, and its magnitude depends on the corpus statistics: the term rarity, the document length, and the match count. No principled constant converts one into the other. A blend of the two is an average of a temperature and a zip code.
The usual patch is min-max normalization of each result set before the blend. That is worse than it looks, because now each score depends on the set that it arrived in. Add one mediocre result to the bottom of a list, and you rescale everything above it. The score of your top result changes because of a document that no person will read.
Reciprocal Rank Fusion discards the scores and keeps only the order. Each retriever contributes 1 / (k + rank) for each document that it returns, summed across the retrievers:
K_RRF = 60 # Cormack et al., 2009
def _rrf_fuse(*result_lists, k_rrf=K_RRF, final_k=8):
"""Each input list is chunk_ids, best-first."""
scores = {}
for results in result_lists:
for rank, chunk_id in enumerate(results, start=1):
scores[chunk_id] = scores.get(chunk_id, 0.0) + 1.0 / (k_rrf + rank)
ranked = sorted(scores.items(), key=lambda x: -x[1])
return [cid for cid, _ in ranked[:final_k]]That is the full fusion layer. Ten lines, no configuration, and no tuning constants to maintain.
Three properties earn its place. First, only ordinal information crosses the boundary, so there is nothing to normalize and nothing to calibrate. Second, the constant k damps the top of each list. The value 60 comes from the original paper, and I never had a cause to move it. Rank 1 in one retriever is a strong signal, not an overwhelming signal. A document that both retrievers like can beat a document that one retriever loves, and it should.
Third, a retriever that returns nothing contributes nothing, automatically. No branch, no fallback path, no “if hybrid enabled” config. An empty list is a list that adds zero.
That last property is the reason that I refused to build a hybrid-vs-vector-only switch. RRF degrades to a single-retriever ranking on its own. A toggle would give me only a second code path to test and a setting to get wrong.
The trick that fixed my query
Here is the part that I would miss with an index of chunk text only.
The FTS5 table indexes three columns: the chunk text, its heading path, and a filename slug derived from the note’s path. The slug replaces the separators with spaces, so the path becomes searchable tokens:
def filename_slug(path: str) -> str:
stem = path[:-3] if path.endswith(".md") else path
return re.sub(r"[/\-_]+", " ", stem).strip()So personal/golf/rotella-application-plan.md becomes personal golf rotella application plan.
This is the reason that the failed query now works. The proper noun lived in the filename. The body of the chunk discussed the ideas and rarely repeated the name. Semantic search could not reach the file, because the body is not about the word. Keyword search over the body alone would also fail. The path index closed the gap, and it costs one derived column.
Additive by design
The change is purely additive. That is the reason the ship was uneventful:
- One virtual table and one trigger. The delete trigger mirrors the existing trigger for embeddings. When a file is removed, the foreign-key cascade tears down its chunks, and both side tables stay in sync. No orphans, no cleanup job.
- A one-time backfill with a guard on
count > 0. The first indexer run after the deploy fills the keyword index from the chunks that already exist. Each later run is a no-op. It is idempotent by construction, so I never thought about a second run. - No re-embedding. The existing vectors did not change. A full second retrieval strategy cost zero embedding calls.
- The rollback is one file. Revert the query module, and the indexer writes keyword rows into a table that no code reads. That is harmless. If the table itself becomes a problem, drop it. The schema is purely additive.
The storage for 1,455 chunks: about 400 KB. Each retriever returns 20 candidates, and the fusion picks the top 8 for the model. The test count went from 36 to 62. The query latency did not change. The fusion is a dictionary and a sort over 40 integers. Your time does not go there when a language model waits at the end of the pipeline.
Three bugs that reported success
Three defects bit me around this work. All three share one shape. The system said that everything was fine.
An indexer run finished with errors=7. Seven large files had oversized sections, and the sections truncate at embed time in silence instead of a loud failure. The run “succeeded.” The count sat in the log line, and I read past it for one day. A summary line that ends in a number that you decided is normal is a summary line that you stopped reading.
A worse defect: a subprocess exited with a nonzero code and wrote its real error message to stdout, not stderr. The calling code checked the return code, took the failure branch, and reported a clean zero result. There was nothing to see. Only a quiet “found 0 items,” equal in appearance to an honest empty result. I patched the call site to log the return code, stdout, and stderr, all three. Then a permissions error printed itself in one line. I had stared past it for hours.
And a set of scheduled jobs recorded last_status: ok while they carried a last_delivery_error. The error said that no delivery target could be resolved. The jobs ran. They completed. They sent nothing, with success, for weeks. A status field that reports “the code finished” instead of “the work happened” will lie to you.
What I would test first
On my notes, semantic search failed in one repeatable direction: short queries containing the rare word I actually remembered. Prompt work could not recover a document that retrieval never supplied.
I would now test that case before tuning anything else: choose a known file, search for it with its ordinary two-word name, and make the system prove that it can find it. Here, a second ordinary retriever with different weaknesses helped more than a stronger version of the first. Better combinations exist. This is the smallest one that stopped hiding my own file from me.