A downgrade must not guess whether a review already happened

The thing that stops a pull request being reviewed twice is a UNIQUE(event_id) constraint in SQLite. The constraint is only as good as the agreement about what event_id means, and that agreement lives in the binary — which people replace in place, over a database that is never rebuilt.

So the case to worry about is an older Engwire opening a newer database, and SQLite will not stop it. A SELECT or INSERT that names its columns keeps working when columns are added beside them: the old binary’s queries do not fail, they succeed against a shape whose rules it does not know. Change what makes two runs the same request, and the old code goes on answering “has this review already been done” with reasoning that no longer maps — confidently, and in the direction that posts a second review.

No such migration exists yet. This is a gate built before the first schema change rather than after the first duplicate review.

What it does now

The schema version is stamped into SQLite’s own user_version, and anything higher than this binary understands is refused before the runner starts. The message names both numbers and both ways out:

This database uses schema 2, but this Engwire version understands schema 1.
Upgrade Engwire, or point ENGWIRE_HOME at a separate installation.

The second is the one worth knowing about. An installation is its data directory, so pointing ENGWIRE_HOME somewhere else gives the older binary a database of its own rather than making it argue with one it cannot read.

Three details do more work than the stamp itself.

The version is read before anything can persist a change. journal_mode = WAL and foreign_keys = ON come after the check — and journal_mode is not a session setting, it rewrites the file header — so a database this Engwire refuses keeps the schema and rows the newer one left. Refusing to open something is not much of a refusal if opening it changed it.

Zero is ambiguous, and the ambiguity is safe to ignore. SQLite stamps a fresh file 0, and so does a database written before the stamp existed. Both take the same path: v0.1.0 carried this same schema, so CREATE TABLE IF NOT EXISTS adopts its rows rather than replacing them, and a genuinely new file gets built.

The asymmetry is on the other side. A database already stamped 1 skips creation entirely, so a table that has gone missing fails loudly rather than being rebuilt empty. The rows are gone either way; rebuilding is what hides it. An empty table reads as “nothing has ever been reviewed”, which is the deduplication guarantee answering every question wrong at once.

The read itself gets the same treatment. PRAGMA user_version cannot come back empty — 0 is the pragma’s own default — so a missing row would mean SQLite is not behaving. Answering that with “unstamped, go ahead and adopt it” is the one thing this gate must never say, so the absence is a fault rather than a zero.

Creating and stamping happen in one transaction, so a crash between them cannot leave a database that disagrees with its own version.

The handle nobody can close

One more thing had to change for the refusal to be usable twice. The version check throws from a constructor, so the caller never receives the handle and has nothing to close. The database is open, unreachable from any code, and left to the garbage collector to notice — which it will do eventually, on its own schedule, in a process that has meanwhile gone on to open the same file again.

So the constructor closes it before rethrowing, on every failure and not only the version one. A PRAGMA refused on an unsuitable file, or a schema that will not apply, strands the handle in exactly the same way, and none of them is worth debugging as a leak later.

No migration machinery, deliberately

There is none, and that is a decision rather than an omission. A framework written before any migration exists is guesswork about a shape nobody has seen; the first real schema change is what teaches it.

What that change will have to settle is already visible: whether migrations run forward only or can be reversed, how wide a compatibility window each release keeps, and what happens to a runner that is mid-review when the binary underneath it is replaced. The gate answers none of those. It only guarantees that until they are answered, the wrong binary stops instead of guessing.

The one downgrade it cannot catch

v0.1.0 shipped before any of this, so it does not know to look. Point it at a database from a later Engwire and it opens it, because nothing in it ever reads user_version. Every protection above is a property of the binary doing the opening, and that binary does not have it.

The hole is bounded in one useful way: v0.1.0 never writes user_version either. The stamp survives it, so the gate still holds for every later binary that opens the same file afterwards. What it cannot do is stop the one release that predates it — and no version of this check ever will, because the fix has to be in the code that runs, not in the file it reads.