Skip to content

0014: Migrations by pipeline, in one transaction ​

Note 2026-11-06: the git host and CI moved to GitHub (FEAW-Serv/getmy-books), ADR 0046. References below to the previous host are history; ADR 0046 says what replaces them.

Context ​

The schema reached the NAS database through drizzle-kit push. Tested on 2026-09-28 against a throwaway database: re-running push on a database that already has our row-level security policies disables RLS on every table and drops every policy, because the policies live in policies.sql rather than in schema.ts. db:setup then re-applies them, but between the two steps every entity's rows are open to the app role. Nothing applied schema changes to the NAS apart from someone running those commands by hand.

Decision ​

  1. Schema changes are SQL files in src/platform/migrations/, numbered and idempotent. 0000-baseline.sql is the schema push created, confirmed identical to the NAS database (columns, constraints, RLS and policies) on 2026-09-28.
  2. pnpm db:migrate applies every migration, then policies.sql, then bootstrap.sql, in one transaction under an advisory lock. A failure changes nothing, so a table is never left without its policy.
  3. The migrate-nas job applies it after verify passes on main. The owner connection (DATABASE_URL) comes from Doppler, opened with a read-only service token held as DOPPLER_TOKEN in the runner's own environment, so no secret lives in the repository or in the git host. Nobody migrates the NAS by hand, and push is never run against it.
  4. A test builds a fresh database from the migrations alone and requires it to equal the database schema.ts produces, policies included, so the two cannot drift.

Consequences ​

  • db:setup (push) stays for throwaway test databases only. Amended 2026-10-07 (pre-sale track B, step 11, approved by the Product Owner 2026-10-06): db:setup no longer pushes. It builds the throwaway test database with the same migration runner production uses (app role, then src/platform/migrations/), so tests run on exactly the production schema. drizzle-kit and db:push are removed. schema.ts is checked against the migrated database by a test (tables, columns, types, nullability); its constraints and indexes are no longer compared.
  • The runner must sit on the NAS LAN and carry DOPPLER_TOKEN (a read-only Doppler service token for the config whose DATABASE_URL is the NAS) before the job can pass.
  • A change that is not idempotent in plain SQL (renames, type changes) needs a guarded migration; revisit tracking applied migrations when that happens.