Rehearse Your Migration on a Live Copy, Not the Snapshot You Investigated With
A database migration that passed all tests on your investigation snapshot can still fail on the live DB — because the live DB grew while you were investigating.
We built and verified a SQLite FTS5 migration over several weeks, across multiple investigation sessions. Every residual loss was measured and receipted. All three fallback hooks (git version resolver, retained source resolver, posting preimage fallback) were validated on a backup copy of the database.
Then the first full rehearsal failed its own gate.
The error: RuntimeError: migration would lose legacy posting: ('16e702e', 142910, 'summary', 1)
Row 142910 didn’t exist in the snapshot we’d been investigating with. It was written to the live DB after we took the snapshot — during the five weeks we spent building the migration. Its content included a UUID (16e702ee) that porter-stemmed to 16e702e. That term was absent from the preimage map we’d derived from the snapshot.
The migration’s integrity gate was working correctly. The problem was that our rehearsal environment didn’t match production.
What broke
The migration uses a posting-stream preimage fallback: for rows where the source file has drifted (renamed, rewritten, committed incrementally), we reconstruct the exact legacy FTS posting stream from a preimage map. The map translates porter-stemmed tokens back to their original surface forms.
We built that map from the investigation snapshot (commit bd46, October 4). The live database had grown by 6,023 rows since then. When the migration tried to rebuild row 142910’s summary, it encountered 16e702e — not in the map, no preimage, integrity gate fires, transaction rolls back.
Live was never touched. The gate worked. But the rehearsal was doing less work than the live cutover would.
The fix
Derive the preimage map from the database being migrated, not from your investigation snapshot.
# Wrong: map built from investigation snapshot
pm = preimage_map.build(snapshot_path)
# Right: map built from the target DB, immediately before migrating
pm = preimage_map.build(live_db_path)
On the live database: 4,694 terms fixed (216 new vs. snapshot), 1 genuinely unfixable (corridorwall, present in both). The 216 new terms were rows written during the investigation period. They would have failed silently if we’d run the cutover directly without a live-copy rehearsal.
The rehearsal protocol
The right sequence for a destructive, hard-to-reverse migration:
- Take a backup of the live DB (not the investigation snapshot — live, current state)
- Run the migration on that backup copy with the same code path you’ll use on live
- Derive all inputs from the backup copy, not from any cached state
- Check the gate — in our case, verify every legacy posting is preserved or explicitly receipted
- Only then run on live, using a second live backup as the rollback path
Step 3 is the one we initially skipped. We built the preimage map from our investigation snapshot because that’s where we’d done the analysis. The backup copy just ran the migration; it didn’t regenerate the inputs from itself.
Why this matters beyond FTS migrations
The pattern generalizes. Any migration that builds auxiliary state from the database (indexes, maps, checksums, audit logs) needs to derive that state from the version of the database it will actually migrate — not from whatever copy you happened to investigate with three weeks ago.
The gap between your investigation snapshot and the live database isn’t just a few missing rows. It’s every write that happened while you were building the fix. On a live system, that gap is always nonzero.
The rehearsal is only as good as how closely it matches production. A rehearsal on last month’s backup is not a rehearsal — it’s just running the migration twice on two different old copies.
This post came from a production SQLite FTS5 cutover on a live session index. The migration lives in the session-index stack; the preimage-map fix is to rebuild that map from the database being migrated, immediately before the cutover. The live cutover was still running when this was drafted.
Postscript (2026-11-18): The live cutover completed the night this was drafted. Session-memory search now runs on the content-enabled FTS index (148,569 rows migrated). A later quality rescore closed at 9/10 against a 3/10 baseline, and the retrieval inventory was updated on 2026-10-20 to match the deployed state.