0011. Write migrations in SQL, and use Drizzle only to build queries

• Status: accepted; the mention of Graphile Worker’s migrations is superseded by 0019
• Date: 2026-10-26

Context

Tenant isolation lives in the schema (analysis, chapter 8): forced row-level security with one policy per table, composite primary and foreign keys that start with workspace_id, check constraints, and grants to runtime roles whose names depend on the instance. These are PostgreSQL features that reviewers must be able to read exactly as they run. Migrations also run on databases GuestCraft does not operate (Neon, Supabase, Docker), under the customer’s owner role, possibly from two deploys at once (ADR 0010).

Drizzle gives typed queries without hiding SQL, and drizzle-kit can generate migrations from the TypeScript definitions. Generated migrations make TypeScript the source of the schema, and the generated SQL still needs the same review as hand-written SQL.

Decision

• SQL is the source of truth. Migrations are hand-written files, packages/db/migrations/NNNN_snake_case.sql, numbered without gaps and append-only: a fix is a new migration.
• Our own small runner (db/migrations):
  • it takes a session advisory lock, so two deploys never migrate at once;
  • it applies each file in its own transaction, together with its journal entry in guestcraft_meta.migrations;
  • it records a checksum of each file, computed after normalizing line endings, and stops when an applied file has changed or when the database knows a migration that the code does not.
• Runtimes never migrate. At startup they compare their migration checksum prefix with the journal, tolerating only later declared expand entries as ADR 0038 specifies.
• Grants are not in migrations. They depend on the instance’s role names, so applyGrants applies them from the table inventory after every migration. A function created by a migration is revoked from PUBLIC in the same migration.
• Drizzle defines tables only to build queries. src/schema.ts is checked against the migrated catalog, column by column, by a test. drizzle-kit is not a dependency.
• The migration step uses one PostgreSQL session, a direct connection or a session pooler, because the advisory lock belongs to the session. The runner refuses a transaction pooler by its behavior, not by the absence of a proxy: a random session-level marker must persist on the locked backend, and a probe connection with the same URL must neither see it nor take the lock. Runtimes may use a transaction pooler (ADR 0009).

Consequences

• Reviewers read the exact SQL that runs, including policies and constraints.
• Typed queries can drift from the schema only until the conformance test runs, in the same pull request.
• We maintain the runner and its tests: unit tests for names, numbering, checksums and failure reporting; integration tests for applying, re-running, refusing changed and unknown migrations, rolling back a failing file with its journal entry, and concurrent deploys.
• There are no down migrations. An expand-only candidate preserves the previous runtime’s cold starts; a contract release needs roll-forward or a separately evaluated backup restore (ADR 0038).

Alternatives considered

• drizzle-kit migrations. Convenient for simple tables, but the schema would have two representations, the TypeScript definitions and the generated SQL, and the RLS policies, composite keys and grants are clearer written and reviewed as SQL.
• A migration tool such as node-pg-migrate or graphile-migrate. Both are good; they bring a workflow and conventions of their own, or migrations written in JavaScript, for needs that a small runner covers with tests we control. Graphile Worker keeps its own migrations through its API (ADR 0010).
• Migrations at application startup. Rejected in ADR 0010.