Migrations are recomputed on every boot by checking which columns and tables exist, with three different "already ran" markers. migrate.ts still promises numbered migrations, and the ordering in db/index.ts is held together by comments like "must run BEFORE…".
Fix
Move to an ordered list of migrations tracked with PRAGMA user_version, so the order is explicit.
Migrations are recomputed on every boot by checking which columns and tables exist, with three different "already ran" markers. `migrate.ts` still promises numbered migrations, and the ordering in `db/index.ts` is held together by comments like "must run BEFORE…".
**Fix**
- Move to an ordered list of migrations tracked with `PRAGMA user_version`, so the order is explicit.
Reviewed 8d1bb69591. Database upgrades are now an ordered, append-only list tracked with PRAGMA user_version, and an upgraded old database ends up with the same schema as a fresh one, so I'm closing this.
Checklist
Ordered migrations tracked with PRAGMA user_version.migrations.ts:18-69 lists versions 1 to 12 and says "Append only". runMigrations refuses gaps, rejects a database newer than the code, and skips applied versions. It rechecks the version after taking the write lock, and commits each step together with its user_version bump (migration-runner.ts:13-60). Boot now just sets the pragmas and runs the list (db/index.ts:21-36). The old existence checks and markers only run inside the first steps, as a one-time bridge for unversioned databases, and legacy.ts:9-11 marks that bridge as frozen. migrate.ts now describes what it does, and no "must run BEFORE" comments are left. The host, auth and game-file schemas are now versions 9-11 instead of about 20 scattered lazy ensure* calls.
Review notes
Upgrade test: a database created and seeded by the parent commit, upgraded by this code, logs versions 1 to 12 once each. Its sqlite_master is identical to a fresh database's (98 tables, 121 indexes, 36 triggers), and its rows match what the old code produces on its next boot. A database from an older release (18283c6) also upgrades to the same schema. Going back to the parent release on an upgraded database still works, because the old markers are kept.
Good extra catches: table rebuilds turn foreign keys off before their transaction and run foreign_key_check before committing. VACUUM runs outside a transaction and is recorded only after it succeeds. Reopening an up-to-date database runs no migration SQL, and a test checks that.
The 7 migration, index, key, host-deletion, fixture and fleet-settings test files pass locally (122 tests). CI run 848 on this commit is green, and so is run 849 on the current HEAD. The docs (docs/development/README.md:223-237) match the code.
Minor and non-blocking: missing fleet settings are no longer filled in from the defaults on every boot. The old boot path always called initializeFleetSettings, which merges defaultFleetSettings into the saved value; now only version 1 calls it (legacy.ts:565). fleetSettings() returns the stored JSON as is (settings.ts:5-10). So the next new setting needs a migration that adds its default, or existing databases will read it as undefined. Several past commits added settings this way. Merging the defaults in fleetSettings(), or a note next to defaultFleetSettings, would prevent that.
Minor and non-blocking: the foreign_key_check after a rebuild checks the whole database (migration-runner.ts:43-49). A single orphaned row anywhere, which the old code tolerated, now stops startup with "Database migration 1 left broken references". The rollback is clean, but the message doesn't name the table.
Minor and non-blocking: the bodies of versions 2-11 live in their domain modules (for example migrateHostSchema in hosts/schema.ts:18) without the frozen note that legacy.ts has. Editing one to add a column would make fresh and upgraded databases differ.
Reviewed 8d1bb69591c359027d1fb26cc917fdfbce521d82. Database upgrades are now an ordered, append-only list tracked with `PRAGMA user_version`, and an upgraded old database ends up with the same schema as a fresh one, so I'm closing this.
**Checklist**
- [x] **Ordered migrations tracked with `PRAGMA user_version`.** `migrations.ts:18-69` lists versions 1 to 12 and says "Append only". `runMigrations` refuses gaps, rejects a database newer than the code, and skips applied versions. It rechecks the version after taking the write lock, and commits each step together with its `user_version` bump (`migration-runner.ts:13-60`). Boot now just sets the pragmas and runs the list (`db/index.ts:21-36`). The old existence checks and markers only run inside the first steps, as a one-time bridge for unversioned databases, and `legacy.ts:9-11` marks that bridge as frozen. `migrate.ts` now describes what it does, and no "must run BEFORE" comments are left. The host, auth and game-file schemas are now versions 9-11 instead of about 20 scattered lazy `ensure*` calls.
**Review notes**
- Upgrade test: a database created and seeded by the parent commit, upgraded by this code, logs versions 1 to 12 once each. Its `sqlite_master` is identical to a fresh database's (98 tables, 121 indexes, 36 triggers), and its rows match what the old code produces on its next boot. A database from an older release (`18283c6`) also upgrades to the same schema. Going back to the parent release on an upgraded database still works, because the old markers are kept.
- Good extra catches: table rebuilds turn foreign keys off before their transaction and run `foreign_key_check` before committing. VACUUM runs outside a transaction and is recorded only after it succeeds. Reopening an up-to-date database runs no migration SQL, and a test checks that.
- The 7 migration, index, key, host-deletion, fixture and fleet-settings test files pass locally (122 tests). CI [run 848](https://gitea.thewrightserver.net/josh/electric-cherry/actions/runs/848) on this commit is green, and so is [run 849](https://gitea.thewrightserver.net/josh/electric-cherry/actions/runs/849) on the current HEAD. The docs (`docs/development/README.md:223-237`) match the code.
- Minor and non-blocking: missing fleet settings are no longer filled in from the defaults on every boot. The old boot path always called `initializeFleetSettings`, which merges `defaultFleetSettings` into the saved value; now only version 1 calls it (`legacy.ts:565`). `fleetSettings()` returns the stored JSON as is (`settings.ts:5-10`). So the next new setting needs a migration that adds its default, or existing databases will read it as `undefined`. Several past commits added settings this way. Merging the defaults in `fleetSettings()`, or a note next to `defaultFleetSettings`, would prevent that.
- Minor and non-blocking: the `foreign_key_check` after a rebuild checks the whole database (`migration-runner.ts:43-49`). A single orphaned row anywhere, which the old code tolerated, now stops startup with "Database migration 1 left broken references". The rollback is clean, but the message doesn't name the table.
- Minor and non-blocking: the bodies of versions 2-11 live in their domain modules (for example `migrateHostSchema` in `hosts/schema.ts:18`) without the frozen note that `legacy.ts` has. Editing one to add a column would make fresh and upgraded databases differ.
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Migrations are recomputed on every boot by checking which columns and tables exist, with three different "already ran" markers.
migrate.tsstill promises numbered migrations, and the ordering indb/index.tsis held together by comments like "must run BEFORE…".Fix
PRAGMA user_version, so the order is explicit.Reviewed
8d1bb69591. Database upgrades are now an ordered, append-only list tracked withPRAGMA user_version, and an upgraded old database ends up with the same schema as a fresh one, so I'm closing this.Checklist
PRAGMA user_version.migrations.ts:18-69lists versions 1 to 12 and says "Append only".runMigrationsrefuses gaps, rejects a database newer than the code, and skips applied versions. It rechecks the version after taking the write lock, and commits each step together with itsuser_versionbump (migration-runner.ts:13-60). Boot now just sets the pragmas and runs the list (db/index.ts:21-36). The old existence checks and markers only run inside the first steps, as a one-time bridge for unversioned databases, andlegacy.ts:9-11marks that bridge as frozen.migrate.tsnow describes what it does, and no "must run BEFORE" comments are left. The host, auth and game-file schemas are now versions 9-11 instead of about 20 scattered lazyensure*calls.Review notes
sqlite_masteris identical to a fresh database's (98 tables, 121 indexes, 36 triggers), and its rows match what the old code produces on its next boot. A database from an older release (18283c6) also upgrades to the same schema. Going back to the parent release on an upgraded database still works, because the old markers are kept.foreign_key_checkbefore committing. VACUUM runs outside a transaction and is recorded only after it succeeds. Reopening an up-to-date database runs no migration SQL, and a test checks that.docs/development/README.md:223-237) match the code.initializeFleetSettings, which mergesdefaultFleetSettingsinto the saved value; now only version 1 calls it (legacy.ts:565).fleetSettings()returns the stored JSON as is (settings.ts:5-10). So the next new setting needs a migration that adds its default, or existing databases will read it asundefined. Several past commits added settings this way. Merging the defaults infleetSettings(), or a note next todefaultFleetSettings, would prevent that.foreign_key_checkafter a rebuild checks the whole database (migration-runner.ts:43-49). A single orphaned row anywhere, which the old code tolerated, now stops startup with "Database migration 1 left broken references". The rollback is clean, but the message doesn't name the table.migrateHostSchemainhosts/schema.ts:18) without the frozen note thatlegacy.tshas. Editing one to add a column would make fresh and upgraded databases differ.