ops(bunyip-101): script to reconcile _sqlx_migrations after in-place edits #141
No reviewers
Labels
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
No due date set.
Dependencies
No dependencies set
Reference
psa-systems/bunyip!141
Loading…
Reference in a new issue
No description provided.
Delete branch "ops/bunyip-101-reconcile-migration-checksums"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Add scripts/reconcile-sqlx-checksums.{sql,md}: a one-shot reconcile script for the 11 migration files BUNYIP-79 (commit
9c082eb) edited in place. The new migrations/README.md already documents that editing applied bodies changes their checksum and that the operator must reconcile before redeploying; this is the missing scripted procedure.Per migration the script:
Every schema delta is wrapped in a pg_constraint / pg_index guard so the script is idempotent: rerunning it no-ops cleanly. The final DO block asserts all 11 versions still exist in _sqlx_migrations, raising (and rolling everything back via --single-transaction) if any are missing.
Two raise-early checks live inline so an operator hitting a non-trivial precondition cannot silently miss it: email_change_requests duplicate-token_hash rows block the UNIQUE add, and out-of-range oauth_clients.refresh_idle_ttl_seconds rows block the CHECK add.
Companion markdown walks through stop-bunyip / run-sql / verify / restart / smoke-test on each environment, plus the regenerate-hashes incantation for the next time someone edits one of these files in place.
#BUNYIP-101
Add scripts/reconcile-sqlx-checksums.{sql,md}: a one-shot reconcile script for the 11 migration files BUNYIP-79 (commit9c082eb) edited in place. The new migrations/README.md already documents that editing applied bodies changes their checksum and that the operator must reconcile before redeploying; this is the missing scripted procedure. Per migration the script: 1. Applies the schema delta the new file describes that the live DB lacks (CHECK + UNIQUE constraints on email_change_requests / feedback_attachments / application_entitlements / oauth_clients, the new lifetime_price_id column on tier_config, the recreated plain idx_applications_is_hosted index, the COMMENT ON COLUMN explainer on lifecycle_event_outbox.user_id, and the disabled_at stamp on the abandoned placeholder mokosh client row). 2. UPDATEs _sqlx_migrations.checksum to the SHA-384 of the new file body so sqlx will accept the row on the next bunyip-api boot. Every schema delta is wrapped in a pg_constraint / pg_index guard so the script is idempotent: rerunning it no-ops cleanly. The final DO block asserts all 11 versions still exist in _sqlx_migrations, raising (and rolling everything back via --single-transaction) if any are missing. Two raise-early checks live inline so an operator hitting a non-trivial precondition cannot silently miss it: email_change_requests duplicate-token_hash rows block the UNIQUE add, and out-of-range oauth_clients.refresh_idle_ttl_seconds rows block the CHECK add. Companion markdown walks through stop-bunyip / run-sql / verify / restart / smoke-test on each environment, plus the regenerate-hashes incantation for the next time someone edits one of these files in place. #BUNYIP-101