orb(selfhost): the migration ledger records only a filename — a migration edited after it was applied is undetectable, and a half-applied file is recorded as complete

[JSONbored]

Problem

The migration ledger records only a filename, so a migration edited after it was applied is undetectable.

src/selfhost/migrate.ts:77-95: the ledger is
_selfhost_migrations (name TEXT PRIMARY KEY, applied_at TEXT NOT NULL) — no content hash — and the
apply loop is if (applied.has(file)) continue;.

A migration whose body changes after it has been recorded is never re-read and never flagged. The running
DB and the repo's declared schema diverge with no signal at boot, in /health, or in preflight.ts.
There is also no down-migration path at all.

Ordering is not the gap: scripts/check-migrations.ts and src/db/migration-collisions.ts catch
duplicate numbers and gaps pre-merge, and the four grandfathered duplicate pairs (0015/0017/0074/0156)
are documented. Content drift is.

Trigger

A fix is applied by editing migrations/0142_foo.sql in place rather than adding 0185_fix_foo.sql — a
natural instinct when the file is only days old and "hasn't shipped yet". Every already-deployed ORB
silently keeps the old schema. The next code change that assumes the new column fails at runtime, not at
boot.

Impact

Schema drift between fleet instances with no detection surface. It also compounds #9027
(non-transactional per-file apply): a file that half-applied is recorded as fully applied, and no
checksum exists to notice that the DB does not match the file. Together they mean "the migration ran" is
an unverifiable claim.

Dedup

Distinct from #9027, which is scoped to the transactionality of a single apply run. This is the absence of
any content identity in the ledger — it persists across runs and is not addressed by wrapping a file in a
transaction. They should be fixed together.

Requirements

• Add a content_sha256 column, populate it on apply, and on every boot compare each applied file's
  current hash.
• Log loudly and fail readiness on mismatch rather than silently skipping.
• Decide and document the policy for the four grandfathered pairs so the check does not trip on them.
• Consider whether a down-migration path is wanted, or state explicitly that it is not.

Test Coverage Requirements

99%+ patch coverage, branch-counted; both arms (hash matches / differs), plus the first-run
backfill-of-hashes path for existing deployments.

Links & Resources

• src/selfhost/migrate.ts ~77-95; scripts/check-migrations.ts; src/db/migration-collisions.ts
• orb(selfhost): migrations are not transactional per file — a crash mid-file can brick boot or double-apply DML #9027 (non-transactional per-file apply)

maintainer-only — schema integrity.

[linear-code]

JSO-6336