Editing 11 applied migrations in `9c082eb` (BUNYIP-79) crash-loops already-migrated DBs on _sqlx_migrations checksum mismatch #172

New issue

Closed

opened 2026-06-19 03:27:32 +02:00 by longjacksonle · 0 comments

longjacksonle commented

2026-06-19 03:27:32 +02:00

Owner

Summary

Commit 9c082eb ("fix(migrations): close data-integrity gaps and gate version collisions", BUNYIP-79) edits 11 already-applied migration files in place. sqlx records a per-file SHA-384 in _sqlx_migrations on first apply and recomputes + compares it on every subsequent start, so any DB that already applied the originals now fails startup with migration <version> was previously applied but has been modified and crash-loops until the recorded checksums are reconciled by hand. The commit message and bunyip-api/migrations/README.md both call out this checksum caveat, but the reconciliation is fully manual and nothing in the deploy path performs or enforces it, so an existing environment that pulls the change just goes dark.

Impact (observed)

A developer dev OP (dev-bunyip-api-long) was down for ~24h, crash-looping on:

migration 20241230000014_create_email_change_requests.sql was previously applied but has been modified
Error: ... Exit status: 1

With the API process dead, Traefik had no backend and every dependent relying party failed (in this case a lets-chat dev stack could not complete OIDC discovery). It was only recovered by manually realigning the recorded checksums in _sqlx_migrations to the current file hashes.

Affected migrations (all 11 edited in `9c082eb`)

20241230000014_create_email_change_requests.sql
20241230000017_add_application_subdomain.sql
20260313000021_add_feedback_attachments.sql
20260319000025_encrypt_stripe_secrets.sql
20260417000040_create_oidc_clients.sql
20260417000041_create_oidc_sessions_and_codes.sql
20260417000042_create_oidc_tokens.sql
20260429000045_add_price_ids_to_tier_config.sql
20260502000048_register_mokosh_oidc_client.sql
20260602000050_seed_distribution_catalog.sql
20260605000010_create_application_entitlements.sql

Why the documented caveat is not enough

The README says to "reconcile the recorded checksum (or confirm the environment re-migrates from scratch) before deploying," but:

There is no tooling to do the reconciliation, so each operator hand-edits _sqlx_migrations per affected version (error-prone, and easy to miss in an unattended dev/CI deploy that just restarts the container).
Several of these were not comment-only edits (the commit guards "destructive/unconstrained DDL"). Patching the checksum makes sqlx treat the migration as applied without running the new DDL, so the recorded-as-fixed schema may not actually have the new guards/constraints on an already-migrated DB. The checksum patch unblocks startup but does not deliver the data-integrity fix the commit intended.

Suggested remediation (pick one)

Prefer forward-only migrations: revert the in-place body edits and re-express each behavioral change as a NEW migration with a fresh version, so already-migrated DBs converge by applying the delta and checksums never diverge. This is the only option that actually applies the guards to existing data.
If in-place edits must stay, ship an idempotent reconciliation step (a small bunyip-bootstrap subcommand or a script) that updates the recorded SHA-384 for the affected versions, and run it automatically before migrate on startup, so no environment crash-loops.
At minimum, add a CI/deploy preflight that detects content-vs-recorded checksum drift against a representative already-migrated DB and fails the deploy loudly instead of letting the service restart-loop.

Reference

sqlx 0.7.4 checksum: Sha384::digest(file_bytes) in sqlx-core/src/migrate/migration.rs.
The immutable-applied-migration rule is a one-way ratchet for every file-hashing migrator (sqlx, refinery, golang-migrate, alembic, flyway); editing a shipped file is the trigger regardless of whether the change is a comment or DDL.

## Summary Commit `9c082eb` ("fix(migrations): close data-integrity gaps and gate version collisions", BUNYIP-79) edits 11 already-applied migration files in place. sqlx records a per-file SHA-384 in `_sqlx_migrations` on first apply and recomputes + compares it on every subsequent start, so any DB that already applied the originals now fails startup with `migration <version> was previously applied but has been modified` and crash-loops until the recorded checksums are reconciled by hand. The commit message and `bunyip-api/migrations/README.md` both call out this checksum caveat, but the reconciliation is fully manual and nothing in the deploy path performs or enforces it, so an existing environment that pulls the change just goes dark. ## Impact (observed) A developer dev OP (`dev-bunyip-api-long`) was down for ~24h, crash-looping on: ``` migration 20241230000014_create_email_change_requests.sql was previously applied but has been modified Error: ... Exit status: 1 ``` With the API process dead, Traefik had no backend and every dependent relying party failed (in this case a lets-chat dev stack could not complete OIDC discovery). It was only recovered by manually realigning the recorded checksums in `_sqlx_migrations` to the current file hashes. ## Affected migrations (all 11 edited in `9c082eb`) - 20241230000014_create_email_change_requests.sql - 20241230000017_add_application_subdomain.sql - 20260313000021_add_feedback_attachments.sql - 20260319000025_encrypt_stripe_secrets.sql - 20260417000040_create_oidc_clients.sql - 20260417000041_create_oidc_sessions_and_codes.sql - 20260417000042_create_oidc_tokens.sql - 20260429000045_add_price_ids_to_tier_config.sql - 20260502000048_register_mokosh_oidc_client.sql - 20260602000050_seed_distribution_catalog.sql - 20260605000010_create_application_entitlements.sql ## Why the documented caveat is not enough The README says to "reconcile the recorded checksum (or confirm the environment re-migrates from scratch) before deploying," but: - There is no tooling to do the reconciliation, so each operator hand-edits `_sqlx_migrations` per affected version (error-prone, and easy to miss in an unattended dev/CI deploy that just restarts the container). - Several of these were not comment-only edits (the commit guards "destructive/unconstrained DDL"). Patching the checksum makes sqlx treat the migration as applied without running the new DDL, so the recorded-as-fixed schema may not actually have the new guards/constraints on an already-migrated DB. The checksum patch unblocks startup but does not deliver the data-integrity fix the commit intended. ## Suggested remediation (pick one) 1. Prefer forward-only migrations: revert the in-place body edits and re-express each behavioral change as a NEW migration with a fresh version, so already-migrated DBs converge by applying the delta and checksums never diverge. This is the only option that actually applies the guards to existing data. 2. If in-place edits must stay, ship an idempotent reconciliation step (a small `bunyip-bootstrap` subcommand or a script) that updates the recorded SHA-384 for the affected versions, and run it automatically before `migrate` on startup, so no environment crash-loops. 3. At minimum, add a CI/deploy preflight that detects content-vs-recorded checksum drift against a representative already-migrated DB and fails the deploy loudly instead of letting the service restart-loop. ## Reference - sqlx 0.7.4 checksum: `Sha384::digest(file_bytes)` in `sqlx-core/src/migrate/migration.rs`. - The immutable-applied-migration rule is a one-way ratchet for every file-hashing migrator (sqlx, refinery, golang-migrate, alembic, flyway); editing a shipped file is the trigger regardless of whether the change is a comment or DDL.