FTS5 for titles; table scan when filters win

The claim is sharp: free-text search uses SQLite FTS5 on titles and search text; when the user is only filtering by category, lifecycle, or similar constraints, the query stays on ordinary table indexes. Either path returns pages of thin list rows. Neither path loads the vault into Svelte state.

That sounds like a small planner detail. It is the difference between a vault that stays responsive at ten thousand items and one that quietly turns every keystroke into a JavaScript walk of the attic.

The constraint: one search habit cannot serve every list gesture

People do not use a vault list as a single verb. They open “Credentials,” browse recent updates, jump into archived items, clear a trash filter, or type three characters of a site name. Those gestures look similar in the UI. They are not the same problem for the database.

Free-text is a needle problem. The user wants rows whose title or projected search text matches tokens—often prefixes, often across languages that do not split on spaces the way English does. Scanning every title in the UI layer is the naive answer. It works until the vault is large, the main thread is busy, and decrypted payloads start hitchhiking “just in case.”

Category and lifecycle are set problems. “Show active credentials” is not a fuzzy title match. It is a predicate over columns the schema already indexes for list work. Running that through a full-text engine adds machinery without buying relevance. It also muddies the mental model: filters that should be exact become something that feels like search.

Hydrating the attic for either case fails for the same reason. If unlock or a filter change means “load every item, then filter in memory,” memory grows with vault size, reactivity rewalks work SQL already finished, and unlock latency becomes proportional to history. The earlier post in this series already refused that architecture for the list itself. Search is not an exception that earns a second copy of the attic.

Server-side plaintext search is off the table. A zero-knowledge vault does not upload titles so an edge index can answer faster. Local search has to be good on the device, offline, with sealed payloads still sealed.

So the product needs a local strategy switch: use the index that matches the question, keep results paged, and never make the browser tab pretend it is the database.

The design: match text with FTS5; let filters stay table-native

NT² keeps searchable list metadata in the per-vault SQLite database. Titles are plaintext list fields by design—enough to navigate without decrypting item payloads. Alongside the item table, a dedicated FTS5 index covers title and search text used for free-text matching. Writes keep that index in sync when titles, tags, or projected field text change.

The list query path builds a single WHERE clause from the current filter set, then pages with LIMIT / OFFSET (default page size fifty). The strategy fork is inside that clause builder:

1. When the user typed a text query, the clause includes an FTS5 MATCH against the search index, with tokens segmented for the active locale and joined as prefix terms. Hits resolve back to item rowids. Category, tag, travel-mode, and lifecycle predicates still apply on the same page query so typing inside “Credentials” does not leak other categories.
2. When there is no free-text query, the FTS branch stays dark. The query is ordinary SQL over the item table and related filter tables: category slug, archive and trash lifecycle, tags, and similar constraints. Indexes on those columns do the narrowing work. Calling this a “table scan” in the product sense means “no FTS MATCH”—not “always read every page of the heap without indexes.”
3. Counts follow the same WHERE. Category badges and “how many match?” answers come from aggregate SQL, not from array.length on a hydrated universe.
4. UI state still holds only a window. Debounced search and filter changes reset or soft-refresh listRows. Virtual scroll asks for the next page. Opening an item is a separate decrypt path. Search never requires decrypting the vault to score titles.

flowchart TD
  Filters[List filters + optional text]
  Decide{Free-text present?}
  FTS[FTS5 MATCH on title / search text]
  Table[Indexed table predicates]
  Page[count + page of list rows]
  UI[Virtual list window]

  Filters --> Decide
  Decide -->|yes| FTS
  Decide -->|no| Table
  FTS --> Page
  Table --> Page
  Page --> UI

Two details matter for honesty.

FTS is for finding, not for owning. A match returns identifiers and thin list columns. It does not mean the content key for that item is unwrapped, and it does not mean every matching row is resident in memory at once.

Filters compose; they do not invent a second list architecture. Tag intersection, archived-only views, and travel-mode constraints are still SQL. The FTS path is additive when text is present. Clearing the search box drops back to the table-native path without changing how paging or virtualization work.

That is the same discipline as the paged list: the durable attic stays in SQLite; the interactive surface asks precise questions and keeps a bounded answer set.

The trade-off: two query shapes, one index to maintain

A single always-FTS design would be simpler to explain. So would a single always-scan-in-JavaScript design. Both are worse products at scale.

We pay for an FTS index. Every title or search-text change updates the full-text side. Schema migrations and locale-aware segmentation add code. CJK and other scripts need word breaking that whitespace splitting cannot fake. Prefix MATCH queries need careful escaping so user input stays data, not query syntax.

We pay for a strategy branch. Contributors must extend the shared WHERE builder instead of inventing a one-off filter in the UI. Optimistic list patches have to approximate the same rules—or accept that tag-name-only hits may wait for a reload. Debouncing exists because each keystroke is a real database question, not a free filter over a cache that already holds everything.

We refuse the illusion of one universal scan. Engineers sometimes prefer “just LIKE %q% on title” because it is obvious. On a large local vault, repeated leading-wildcard scans and main-thread fallbacks are exactly how lag returns. FTS5 exists so title search stays an index problem. Table predicates exist so “show this category” stays a set problem.

What we get back is predictable cost. Typing a title fragment touches the full-text index and returns a page. Switching category without text skips FTS entirely. Memory stays related to the window, not the attic. Offline still works because the index lives next to the vault file on the device.

There is also a product honesty trade-off. We do not claim the cloud can search your plaintext titles. We do not claim search decrypts notes to find a buried phrase in every field of every payload. List search is built around titles, tags, and projected search text that the schema chooses to index for navigation. Opening a record remains the moment for full payload work. That boundary keeps zero-knowledge sync from becoming a fantasy search appliance.

What we refuse

Architecture is clearer when the refusals are explicit.

We refuse to search by loading every title into Svelte state. If free-text needs to run, it runs in SQLite through FTS5 and returns pages. The UI does not become an in-memory search engine over a full-table cache.

We refuse to force FTS onto pure filter gestures. Category, lifecycle, and similar exact constraints do not need a full-text MATCH to feel correct. When filters already win, stay on table predicates.

We refuse to treat backup enumeration as the search path. Walking every item is a batch job for export and similar transitions. It is not how the unlocked list answers “find Stripe.”

We refuse to decrypt payloads to power the scroll search box. Titles and indexed search text are enough to navigate. Ciphertext stays sealed until the user opens a record.

We refuse server-side plaintext title indexes. Optional sync may move ciphertext. It does not earn a honey-pot of searchable secrets on the edge.

We refuse unbounded result hydration. A broad query that matches thousands of rows still pages. Feeling “searchable” does not require holding every hit in reactive state at once.

These refusals keep local-first from meaning “everything hot in the tab.” The device owns the database. The query layer picks the right index. The UI keeps a window.

Ask SQLite the right question

A large vault stays usable when search and filter are honest about what they are. Free-text is FTS5 over titles and search text. Exact filters are ordinary indexed SQL. Both return thin pages into a virtual list. Neither ships the attic to RAM.

That is the same stack story as keeping vault SQLite in a real browser file store, refusing a full-table Svelte load for the list, and designing for ten thousand items without making unlock feel like a spreadsheet import. Storage holds structure. Query chooses strategy. UI refuses to become a second, weaker database.

For the product feeling at scale, read Ten thousand items, still a vault. Directly before this installment: Never load the whole vault into Svelte state. Earlier in the series: why a PWA can be a local-first, zero-server vault and why vault SQLite lives in OPFS, not IndexedDB.

If that model fits how you want private search to work on-device, you can try NT² Vault or read more at nt2.me.