Appearance
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
- Schema changes are SQL files in
src/platform/migrations/, numbered and idempotent.0000-baseline.sqlis the schema push created, confirmed identical to the NAS database (columns, constraints, RLS and policies) on 2026-09-28. pnpm db:migrateapplies every migration, thenpolicies.sql, thenbootstrap.sql, in one transaction under an advisory lock. A failure changes nothing, so a table is never left without its policy.- The
migrate-nasjob applies it afterverifypasses on main. The owner connection (DATABASE_URL) comes from Doppler, opened with a read-only service token held asDOPPLER_TOKENin 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. - A test builds a fresh database from the migrations alone and requires it to equal the database
schema.tsproduces, 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:setupno longer pushes. It builds the throwaway test database with the same migration runner production uses (app role, thensrc/platform/migrations/), so tests run on exactly the production schema.drizzle-kitanddb:pushare removed.schema.tsis 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 whoseDATABASE_URLis 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.