Skip to content

0029: Migrations run inside the deploy, after a backup, and only expand ​

Status note 2026-11-09: not being built. Issue #74 was closed as not planned on 2026-11-08, and the Product Owner accepted that on 2026-11-09. Migrations still run in the migrate-nas job over the LAN before each deploy (ADR 0014). Slice 0's migration lint and production guards stay.

Status ​

Accepted by the Product Owner on 2026-11-06. DH-5, DH-11, DH-12 and DH-14 were decided as recommended the same day.

Proposed, with docs/plans/deploy-hardening.md (slice 2). It depends on ADR 0028, because the migrate step runs the signed image. It amends ADR 0014 decision 3 (who runs migrations, and how they connect). ADR 0014's single transaction and advisory lock stay as they are.

Context ​

Issue #74. Today the order and the risks are as follows:

  • Order. migrate-nas runs doppler run -- pnpm db:migrate with the dev config's owner DATABASE_URL. It reaches the NAS Postgres over LAN port 5433. Only after that does deploy-nas build the new app. The old app serves on the new schema for as long as the build takes.
  • No backup. Nothing takes a backup first.
  • No compatibility rule. Nothing requires a migration to work with the previous app.
  • No tracking. There is no record of which migrations have been applied. Every file is re-applied on every run, so each must be idempotent.
  • All migrations so far are expand-only. Files 0000 to 0008 only add, or loosen (0008 drops a not null), apart from 0004's foreign key and 0008's partial unique index, which tighten. That has been luck, and nothing checks it.
  • The image cannot migrate. tsc does not copy the .sql files into dist, and tsx is pruned.
  • create-role.sql never alters the app role, and runMigrations never creates it. Only db:setup runs create-role.sql. On a fresh or restored cluster, policies.sql (to app) therefore fails, and changing APP_DB_PASSWORD in Doppler breaks the app's login instead of rotating the password.
  • policies.sql dropped and re-created every policy on every run, which took an ACCESS EXCLUSIVE lock on every RLS table each deploy. Fixed in plan slice 0.6: it now only changes a policy that is missing or differs.
  • No backup has been shown to restore with its grants and policies. The restore test uses --no-owner --no-privileges on an unpinned image, runs from /etc/crontab (which DSM regenerates), and nothing records that the nightly dumps exist.

Decision ​

  1. The image carries its migrations and can bootstrap an empty cluster. The build copies src/platform/migrations/*.sql into dist/platform/migrations/ (where readMigrations looks), and policies.sql, bootstrap.sql and create-role.sql into dist/platform/, without changing rootDir. A compiled dist/platform/migrate-cli.js creates the app role if it is missing, sets its password, then calls runMigrations. runMigrations sets lock_timeout (5 s) and statement_timeout (120 s) inside its transaction.

  2. A one-shot migrate service in the root-owned compose file. It:

    • uses the same verified digest as app;
    • uses the owner connection built from POSTGRES_PASSWORD over the compose network;
    • has profiles: ["migrate"] and restart: "no";
    • has no ports and no volumes.

    It also sets the app role's password from APP_DB_PASSWORD, as a SCRAM-SHA-256 verifier computed in the client, so no plain-text password reaches a Postgres log. This lives only in migrate-cli, never in runMigrations, which migrate-nas also calls with Doppler dev's value during the overlap.

  3. getmy-books-up runs, in order:

    1. verify and pull (ADR 0028);
    2. start postgres;
    3. backup: getmy-books-backup predeploy <sha7>. It writes to /volume1/backups/getmy-books/predeploy/, keeps 5, applies the nightly script's checks, and prints the path. A failure refuses the deploy. It is skipped only when the data volume was created by step 2 and holds no tables; a volume that existed without its container is still backed up;
    4. migrate: run --rm migrate. A failure refuses the deploy, and the old app keeps serving;
    5. up -d --no-build --wait;
    6. /health.

    The script still takes no arguments. The backup call is root to root.

  4. Rollback.

    • A failed migration: nothing to do. It was one transaction.
    • A failed /health after a good migration: the last-good image is started again automatically, with the current env (so a password the migrate step just rotated still works), and without the network (ADR 0028). The expand-only rule is what makes that safe.
    • A restore from a dump: a manual runbook step that the Product Owner approves at the time. It is never automatic, because it discards the writes made since the dump.
  5. Expand, then contract.

    • A release may only expand:
      • new tables;
      • nullable or defaulted columns;
      • new indexes;
      • new policies;
      • additive data.
    • Contract changes wait at least one release after no deployed code reads the old shape. They include:
      • drop;
      • rename;
      • type change;
      • SET NOT NULL;
      • a tighter constraint;
      • deleting data or grants.
    • A contract change carries -- contract: #<issue>.
    • policies.sql, bootstrap.sql and create-role.sql are re-applied on every deploy, so the same rules apply to them. policies.sql enables row level security only where it is off, and drops and re-creates a policy only when it is missing or its definition differs from the file's. It compares the definition, not just the name, by creating the policy on an empty temporary copy of the table and comparing the two in pg_policies, so a deploy with nothing to change takes no ACCESS EXCLUSIVE lock on the RLS tables, and a database restored from an older backup gets today's policies back. A changed policy is still a numbered migration (drop, then create in a do block that tests pg_policies), and policies.sql is updated to match.
    • A rename becomes add, dual-write, backfill, switch reads, and drop, in separate releases.
    • Migration files are append-only.
    • Every statement is guarded or idempotent, because the runner re-applies every file on every deploy.
    • Role passwords are set only by migrate-cli, as client-computed SCRAM verifiers, never by runMigrations and never as a plain literal.
    • create index concurrently cannot run in the single transaction. It waits for a tracked, non-transactional migration, when first needed.
  6. CI enforces the rule twice:

    • A lint in verify (scripts/check-migrations.ts, run as pnpm lint:migrations; plan slice 0.6). It compares the working tree with the merge-base with origin/main (on main itself, the previous commit; MIGRATION_BASE overrides it) and fails when:

      1. a migration file that exists at the base was edited, deleted or renamed;
      2. a destructive statement has no contract marker. Destructive means a drop, truncate, delete from, rename, type change, set not null, revoke, add constraint, validate constraint, an unnamed add unique, add check, add foreign key, add primary key or add exclude, create unique index, alter policy, a policy as restrictive, or a new column (with or without the optional column keyword) that is not null with no default or carries its own constraint (alter column … drop not null loosens one, so it is an expand). In a migration the marker is a -- contract: #<issue> line in the file's header comment; in a re-applied file it is the same line directly above the statement, and only statements not already on main need one;
      3. a statement is neither guarded (if not exists, if exists, on conflict … do nothing; in an alter table, every action must be add … if not exists, drop … if exists, alter column … drop not null or enable row level security, because the table's own if exists does not make a rename or any other action safe to repeat, and any other alter goes in a do block) nor one of the idempotent forms with no guard syntax: grant, revoke (which still needs its marker), alter default privileges, alter table … enable row level security, create or replace function, and a do $$ … $$ block, which must test its own condition (if, on conflict or exception when) and is printed for review;
      4. a new migration file is added while .migration-freeze exists at the repository root (added by the slice 2b PR, removed by 2c).

      It also fails when a policy is removed from or changed in policies.sql unless a new migration in the same change drops it (with a contract header) or re-creates it, so the change is reviewed as a migration and policies.sql never needs to drop a policy it no longer defines; and when policies.sql on the base drops a legacy policy it never creates (a clean-up for an old or restored database) and neither it nor any migration drops it now. Policies are matched by table and name. Migration 0009 carries the one such clean-up that policies.sql had, for the pre-M6 ledger_write_entity_isolation. Files 0000 to 0008, every migration on main before the lint, are grandfathered by name for rules 2 and 3 (0004's foreign key and 0008's partial unique index count as tighter constraints, but they are already applied everywhere); rule 1 covers them like any other. The lint covers policies.sql, bootstrap.sql and create-role.sql too. The plan put the contract marker for those files in the PR description; it lives in the file instead, so the lint can read it on main as well as on a pull request.

    • An N-1 test runs main's database tests against a schema built the way production builds it (create-role plus migrate-cli with the PR's files), not drizzle-kit push. It triggers on changes to migrations, the re-applied files and the migrate code.

  7. A tracking table (schema_migration) comes with the first contract migration, as ADR 0014 anticipated. It is not added before then.

  8. Production data stays out of CI. Issue #74 asked for CI to test migrations against a restored copy of production. This decision deliberately does not do that: the tests use synthetic data. An optional rehearsal on the NAS against the fresh pre-deploy dump, on a throwaway internal network, is the alternative if the Product Owner wants real data tested.

  9. migrate-nas is removed after two clean in-deploy migrations. Migrations are frozen while both run, enforced by the lint. Running both is safe because of idempotency and the advisory lock, and because migrate-nas never touches the app password. Once it is removed, the deploy runner loses the dev Doppler token.

  10. The restore test proves the runbook, weekly. It runs the manual restore runbook's exact commands in a throwaway compose project, with the pinned postgres image and the last-good app image: it creates the app role, restores with owners and privileges, runs migrate-cli, checks the table and pg_policies counts, runs a query as app that RLS must scope, and checks /health. It can also take a pre-deploy dump. The backup and the restore test run from DSM Task Scheduler, not /etc/crontab, alert through ntfy on failure, and ping an external heartbeat on success, so a job that stops running is noticed from off the NAS.

  11. Evidence first. The window that installs this starts only after a recorded, read-only check that the nightly dumps exist (the newest one's age, the count) and one passing run of the new restore test.

What changes, and where ​

WhereChange
Reposrc/platform/migrate.ts, migrate-cli.ts, build copy step; scripts/check-migrations.ts and the N-1 job in ci.yml; migrate-nas removed; infra/nas/docker-compose.yml (migrate); infra/nas/deploy/getmy-books-up; infra/nas/backup/getmy-books-backup, getmy-books-restore-test, crontab-line; docs/sge/change-protocol.md; ADR 0014
NASgetmy-books-up, the compose file, getmy-books-backup and getmy-books-restore-test reinstalled; /volume1/backups/getmy-books/predeploy (root 0700); DSM Task Scheduler tasks for the backup and the weekly restore test, replacing the /etc/crontab line (alert.env arrives with ADR 0028's window)
ElsewhereAn external heartbeat monitor for the backup and the restore test
RunnerDOPPLER_TOKEN (dev) removed from runner.envs once migrate-nas is gone
DopplerThe dev service token revoked; prd DATABASE_URL and APP_DATABASE_URL deleted (nothing reads them)

Consequences ​

Amended 2026-10-07 (pre-sale track B, step 11, approved by the Product Owner 2026-10-06): create-role.sql and drizzle-kit push are gone. The role step this ADR describes is ensureAppRole alone, called by migrate-cli and, for throwaway databases only, by db:setup, which now builds the test database from the migrations (see ADR 0014's amendment). Where this ADR and the plan mention create-role.sql (the build copy, the re-applied files, the N-1 job's "create-role plus migrate-cli"), read ensureAppRole. build/copy-sql.mjs copies policies.sql and bootstrap.sql only, and the migration lint re-checks those two.

  • Every deploy log shows a checked backup before the migration. Migrations run without port 5433 and without any CI database credential.
  • The schema changes seconds before the new app starts, not 15 minutes before.
  • A busy moment can fail a deploy on lock_timeout. That fails safe, but it can repeat.
  • A failed backup, or a full /volume1, blocks all deploys. That is intended.
  • Pre-deploy dumps are more copies of personal and financial data. They fall under ADR 0026's retention: 5 are kept, well inside 35 days.
  • Migrations still run as the Postgres superuser, confined to a one-shot container. A dedicated schema-owner role is a follow-up.
  • Dumps hold tokens encrypted with XERO_TOKEN_ENCRYPTION_KEY, and that key is in every release env and in docker inspect on the same NAS, and possibly in the same off-site backup. Dumps are only as safe as the NAS and the backup target until #63 rotates the key and it leaves the release env.
  • Expand/contract rests on the lint, the N-1 test and review. An incompatibility that no test covers would make the automatic rollback unsafe, and would need a manual restore.