runSelfHostMigrations (src/selfhost/migrate.ts ~76-99) executes each statement individually and records the file as applied only after all statements succeed (~95). A crash mid-file re-runs the whole file next boot. The only tolerated re-run error is /duplicate column|already exists/ (~91) — anything else throws and bricks boot.
Several migrations contain non-idempotent DML: e.g. 0176_orb_signals_superseded_reversal.sql ~28 (INSERT INTO orb_signals_new ... SELECT ..., the table-rebuild pattern), plus UPDATE/DELETE/INSERT in 0009, 0025, 0066, 0083, 0102, 0107. A mid-rebuild crash either double-inserts (data corruption) or hits a PK conflict that is not "already exists" → boot fails.
Given #9007 (every busy deploy currently ends in SIGKILL), the odds of a mid-migration kill are not hypothetical.
Ordering is otherwise correct: migrations run after queue.init()/recovery and before queue.start(), so recovered jobs never see the old schema.
Fix
Wrap each migration file in a single transaction (Postgres DDL is transactional) so a file is all-or-nothing, and commit the ledger insert inside that same transaction.
runSelfHostMigrations(src/selfhost/migrate.ts~76-99) executes each statement individually and records the file as applied only after all statements succeed (~95). A crash mid-file re-runs the whole file next boot. The only tolerated re-run error is/duplicate column|already exists/(~91) — anything else throws and bricks boot.Several migrations contain non-idempotent DML: e.g.
0176_orb_signals_superseded_reversal.sql~28 (INSERT INTO orb_signals_new ... SELECT ..., the table-rebuild pattern), plusUPDATE/DELETE/INSERTin 0009, 0025, 0066, 0083, 0102, 0107. A mid-rebuild crash either double-inserts (data corruption) or hits a PK conflict that is not "already exists" → boot fails.Given #9007 (every busy deploy currently ends in SIGKILL), the odds of a mid-migration kill are not hypothetical.
Ordering is otherwise correct: migrations run after
queue.init()/recovery and beforequeue.start(), so recovered jobs never see the old schema.Fix
Wrap each migration file in a single transaction (Postgres DDL is transactional) so a file is all-or-nothing, and commit the ledger insert inside that same transaction.