GitBasedDocs search moved from LIKE over every page body to SQLite FTS5 kept in step by triggers. Word prefixes and accent folding came with the tokenizer. The real work was quoting input, choosing a stable order, and a backup detail about rowids.
Search in GitBasedDocs was one LIKE '%term%' per word against the title and the whole body, with % and _ escaped so "100%" meant a percent sign. It worked. It also scanned every page body on every keystroke in the palette, and the only ranking was "title matches first".
Checking the engine was there
The app uses @libsql/client, which bundles its own SQLite. Before writing a migration that depends on FTS5, I wanted proof that the build included it with the options I planned.
The first two probes failed, both times because of my shell quoting, not libsql. Inline bun -e scripts inside single quotes turned SQL string literals into bare words, and SQLite read them as column names. The third probe lived in a file and passed: prefix queries matched, and "cafe" found "Café".
It also showed bm25 scores of -0.000. With two rows that both contain the term, there is nothing to rank. That mattered later.
The index keeps itself in step
Migration 0012 is custom SQL, generated with drizzle-kit generate --custom so the migration journal stays in step:
CREATE VIRTUAL TABLE `doc_pages_fts` USING fts5(
title,
content,
content='doc_pages',
content_rowid='rowid',
tokenize='unicode61 remove_diacritics 2',
prefix='2 3'
);
External content means the index stores no second copy of the text; it points at doc_pages rows. Triggers do the upkeep:
CREATE TRIGGER `doc_pages_fts_update` AFTER UPDATE OF title, content ON `doc_pages` BEGIN
INSERT INTO doc_pages_fts(doc_pages_fts, rowid, title, content) VALUES ('delete', old.rowid, old.title, old.content);
INSERT INTO doc_pages_fts(rowid, title, content) VALUES (new.rowid, new.title, new.content);
END;
With triggers, sync never mentions the index. Neither does the danger zone's purge, nor any check that inserts fixture pages. No code path can forget it, because no code path is responsible for it. The migration ends with a rebuild for pages synced before it existed.
Drizzle splits migrations on its breakpoint comment, not on semicolons, so the ; inside a trigger body is safe as long as the breakpoint sits after END;.
Typed text never becomes syntax
FTS5 has a query language: OR, NOT, NEAR, column filters, quotes. A person searching the docs should never trigger any of it. Every term is quoted, with quotes inside doubled, and marked as a prefix:
export function ftsQuery(terms: string[]): string {
return terms.map((t) => `"${t.replaceAll('"', '""')}"*`).join(" ")
}
nonsense OR token returns nothing, because or is a word that has to appear. A stray "token returns the same pages as token.
Terms with no letter or digit are dropped before quoting. A lone % used to match the one page containing a percent sign. Now it matches nothing, which is what a word index can honestly say about a symbol.
An order that does not depend on luck
That -0.000 from the probe is what small projects look like. When a term appears in most pages, bm25 separates them poorly, and ties fall back to whatever order SQLite returns rows in.
So the order is explicit: pages with more title matches first, then bm25 with the title weighted ten to one over the body, then title. The title count reuses the old escaped LIKE, now run only on rows the index already matched.
One behaviour change
A match now has to start a word. "rot" finds "rotate", but "oken" no longer finds "token". The search check says so, rather than the old assertion being quietly deleted:
assert.deepEqual(await slugs("rot"), ["tokens"], "prefix of rotate")
assert.deepEqual(await slugs("oken"), [], "the middle of a word is not a match")
The check also edits a page and deletes another, then searches again, so the triggers are tested against old words leaving the index.
The rowid detail
doc_pages has a text primary key, so its rowid is SQLite's hidden one. The documentation is clear that VACUUM may change hidden rowids, and VACUUM INTO is exactly how backups copy the database. An external content index pointing at renumbered rows would return the wrong pages after a restore.
The backup already opened its copy to clear the synced commit. It now also runs a rebuild there, and the backup check extracts the archive and searches the restored database to prove it.
