Skip to main content

create_patients_search_index

Build the FTS5 trigram index that serves patient-list search.

search_patients matches a case-insensitive substring against name and ehr_patient_id. As a LIKE '%term%' that is unindexable, so every search scans every patient row and runs both columns through their type decorators on the way past — ~110ms at 200k patients, and the patient list issues one of those per 300ms debounce window as someone types.

An external-content FTS5 index over those two columns makes the same substring match seekable. Two choices matter:

  • tokenize=trigram, not unicode61. unicode61 matches token prefixes, so "mith" would stop finding Smith — a silent narrowing of what search returns. Trigram is a true substring match and so preserves the current semantics. It costs a larger index (~17MB at 200k patients) and cannot index terms shorter than 3 characters, which is why search_patients keeps a LIKE fallback for those.
  • content='patients', not a copy. The index stores only the tokens and points back at patients.rowid, so the PHI is not duplicated at rest and there is one source of truth for the column values.

External content means FTS5 does not see writes to patients, so the three triggers below mirror them. They are part of the index, not an optimisation: without them the index goes stale silently and search starts missing patients.

Registered strictly after convert_encrypted_to_plaintext so the rebuild reads plaintext: a legacy cache holds enc:v1: ciphertext in these columns, which tokenises to garbage an index could never match.

Degrades rather than fails: on a SQLite built without FTS5 (or older than the 3.34 that added the trigram tokenizer) this creates nothing and says so once. search_patients tests for the index's presence and falls back to LIKE, so search stays correct — just slower — on such a build. SQLite is asked whether it supports the index rather than the failure being caught, because this runs on every open and a caught CREATE would put a stack trace in the log each time.

A half-built index is worse than no index: a virtual table that exists but was never populated makes search_patients take the FTS path and match nothing, turning a slow search into a silently empty one. Nothing about the table or its triggers reveals that, so apply tracks whether a rebuild has completed and repairs the index whenever it has not — see its docstring for the two ways the contents can go bad while the table looks healthy.

pysqlite implicitly commits before DDL, so the session's rollback does not undo a partial build; a failed build explicitly tears down whatever it created instead. The same commit boundary leaves a window on a successful build: another process already serving searches sees the virtual table before the rebuild lands, so its searches return nothing for the seconds the rebuild takes.

Unlike the other modules here this is not marker-gated but re-checked on every open (migrations._RECONCILED), because everything it builds is derived state that a later change to patients can invalidate. Gating it would freeze whatever damage that change did, and search would silently stop returning patients while list_patients kept finding them.

Module​

Functions​

apply​

def apply(cache: CacheProtocol) ‑> int:

Bring the search index, its sync triggers and its contents back.

Three pieces of state have to agree, and each can be lost without the others: the virtual table, the triggers (which hang off patients, so they go whenever that table is dropped, recreated or copied), and the index's contents.

Contents are the subtle one, and are tracked by a marker rather than inferred from the other two, because neither implies them:

  • pysqlite commits each DDL statement, so a process killed between _CREATE_INDEX and _REBUILD leaves a table and triggers that look healthy over an index holding nothing. search_patients would take the FTS path and match nobody.
  • A table copy — what an Alembic batch_alter_table performs on SQLite — keeps the rows but reassigns their rowids. The index's entries then point at whichever rows happen to hold those rowids now, or at none. Restoring the triggers does not fix that; only a rebuild does.

So anything short of "table, triggers and marker all present" rebuilds. The rebuild scans patients (~2s at 200k), which is the price of a repair and is not paid on a healthy open.

Arguments

  • cache: The cache backend.

Returns The number of objects created or rebuilt, or 0 if everything was already in place or this SQLite cannot build the index.

Global variables​

  • NAME - Identifier this migration is logged under. Unlike the marker-gated migrations it is never recorded as applied — see migrations._RECONCILED.