Here is a bug that cannot be found by testing, does not appear in any log, and gets worse the longer it goes unnoticed.
You ship migration 18. A week later you spot that its CREATE INDEX is on the wrong column, or that a DEFAULT is missing, or simply that the description above it is misspelled. So you fix it, in place, in the file where it lives. The tests pass. A fresh install comes up perfectly. You ship.
Nothing you did reached a single existing user, and now there are two versions of your database schema in the world.
A migration runs once, per database, forever
That is the whole mechanism. tauri-plugin-sql, like every migration runner I have used, records which versions a given database has applied and never runs them again. It is exactly the behaviour you want — it is what makes the migration list replayable from empty and safe to run on every launch.
It also means the SQL text of migration 18 is only ever read on machines that have not yet reached 18. Edit it, and:
- Everyone who installed before the edit keeps the old schema. They applied version 18, the runner has recorded 18, and it will never look at that SQL again.
- Everyone who installs after the edit gets the new schema.
- Both populations are on the same app version, reporting the same version number, running the same code against two different tables.
There is no error. There is no warning. There is no query you can run in the app to detect it, because the app has no idea anything is wrong — the code compiled, the database opened, the index either exists or does not.
What it produces is the worst genre of bug report: it works on mine. Months later, one user's search is slow and yours is not; one user's column is null and yours has a default. The reproduction step you will never guess is "install the app before 14 August".
Why the obvious defence doesn't work
The obvious defence is discipline. Don't edit shipped migrations. Everybody knows this rule, and everybody knows it in the abstract while looking at a one-character typo in a line of SQL they wrote four days ago.
The failure is not ignorance. The failure is that the cost is invisible at the moment of the edit and enormous six months later, which is the exact profile of a mistake people keep making. So it needs a mechanism rather than a resolution.
What the mechanism is
There is a file, migrations.lock, that records a SHA-256 for every migration that has ever shipped. Thirty of them, at the time of writing.
The hash is not of the SQL alone. It is of a canonical form:
version=<n>
kind=<kind>
description=<byte length>:<text>
sql=<byte length>:<text>The length prefixes are the part worth explaining. Without them, the framing and the content can be confused with each other: a description ending in \nsql= produces the same bytes as a shorter description followed by the real field. Prefixing each text with its own byte length makes that impossible — the reader knows how many bytes to take before it looks at what they say. It is the same reason network protocols length-prefix frames rather than hunting for a delimiter.
A script, run before anything else, reads the migration list out of the Rust source and compares it against the lock. An edit fails. A deletion fails. Both name the version and say what to do instead. A brand-new migration above the highest locked version passes and is reported as unlocked, because that is a normal state to be in halfway through writing one; npm run migrations:bless appends it to the lock when it is ready to ship.
Byte-exact, deliberately
The first thing anyone suggests here is whitespace normalisation. Strip the indentation, collapse runs of spaces, then hash — so that reformatting a migration does not trip the guard.
I decided against it, and the reason is specific. Tolerating reformatting also tolerates edits inside string literals. DEFAULT 'a b' to DEFAULT 'a b' is a whitespace change to a formatter and a data change to every row inserted afterwards. The same applies inside an FTS5 tokenize argument, where the spacing is an argument list, not a layout.
The asymmetry settles it. A false alarm costs you a git checkout of one file. A missed edit costs you a permanently split user base that you find out about from a stranger, in a year, with no way to tell which of them are on which side.
Where it runs
Two places, and the choice of both is deliberate.
First in npm test. Not last. It takes about a second, needs no build, and catches the mistake while the change is still in your head. A guard that runs after four minutes of bundling is a guard you learn to skip.
As a release preflight in ship.sh, before anything is bumped, built, signed or uploaded. The release script's job is to make a bad release impossible to publish by accident, and this is the failure that would otherwise be undetectable after the fact — every other release mistake can be fixed by releasing again.
Two readers, so neither can drift
The guard reads the migration list by parsing Rust source with JavaScript. That is a text parser looking at a language it does not understand, and text parsers drift from languages.
So the migration list moved out of the middle of a function into pub fn migrations(), and there is a Rust test that hashes the same lock file against the structs as the compiler sees them. Two independent readers of the same source of truth, in two languages, checked against one lock file. If the parser ever stops seeing what the compiler sees, the two disagree and the build fails — which is the only way a parser like that can be trusted at all.
The general rule
The database on a user's disk is state I will never see. There is no telemetry in this app, no server holding a copy, and — for a local-first app — that is the point rather than an oversight. But it does mean that anything I cannot observe in the field has to be made impossible at build time instead.
Migrations are the sharpest instance because they are append-only in the same way an event log is: once a version has run on somebody's machine, that version is a historical fact and no longer a piece of source code. The file it lives in is misleading — it looks editable, it sits in your editor, the linter will happily reformat it. Everything about the medium says "text you can change", and exactly one thing says otherwise.
That is what the lock file is really for. Not to catch a mistake, but to change what the file looks like when you open it.
If you want the shape of what all these migrations have built, the whole data model is one SQLite file — and the reason its integrity matters so much is that it is also the thing you back up, move and inherit.