Skip to main content

Migrations

Migrations are plain SQL files in db/migrations, applied in lexical order by a small Node runner. There is no ORM-generated migration step — the hand-authored SQL is the source of truth, because the RLS + role model can't be expressed coherently by drizzle-kit's generator (the Drizzle definitions in db/src/schema are the typed query builder only).

Naming & layout​

Files are numbered NNNN_short_name.sql, zero-padded so lexical sort = apply order:

db/migrations/
  0001_init.sql                 # tenant/identity/survey/response/compliance schemas
  0002_rls.sql                  # roles + the _apply_tenant_rls helper + policies
  0003_auth.sql
  …
  0117_privacy_barrier_and_transcription_cascade.sql
  0118_make_deletion_records_append_only.sql  # latest

The latest migration is 0118. Each migration is self-contained: when it adds a tenant-owned table it must also grant the table to app_tenant and call _apply_tenant_rls(...) (the 0002 grant was a one-time snapshot). For example:

-- db/migrations/0005_alerts.sql
create table if not exists response.alert (
  id                bigint generated always as identity primary key,
  public_id         uuid not null unique,
  client_account_id bigint not null,
  -- …
);
grant select, insert, update, delete on response.alert to app_tenant;
grant usage, select on all sequences in schema response to app_tenant;
select _apply_tenant_rls('response.alert');

The runner — migrate.mjs​

db/scripts/migrate.mjs (run via npm run db:migrate --workspace db) applies pending files. Its contract:

  • Reads DATABASE_URL from the environment or api/.dev.vars and connects to the direct (non-pooled) endpoint for DDL. It never prints the connection string.

  • Ensures the ledger table exists:

    create table if not exists public.schema_migrations (
      filename   text primary key,
      applied_at timestamptz not null default now()
    );

  • Lists *.sql files sorted lexically, skips any already in schema_migrations, and applies the rest. Each file runs in its own transaction alongside the insert … into schema_migrations, so a file is all-or-nothing and the ledger stays consistent.

  • The connection uses prepare: false, so a migration file may contain multiple statements (simple protocol) and is pooler-safe.

The run is idempotent — re-running applies nothing new:

· skip 0001_init.sql (already applied)
→ apply 0118_make_deletion_records_append_only.sql … done
✓ applied 1 migration(s)

Verifying tenant isolation​

npm run db:migrate:rehearse --workspace db first executes every pending file against the target schema in one transaction and deliberately rolls it back. Apply only after that rehearsal passes. npm run db:verify-rls --workspace db then proves the RLS regime empirically after migrating (default-deny, FORCE, and that a wrong-tenant query returns zero rows). It is part of the green gate before any commit — see Multi-tenancy & RLS.

Adding a migration​

  1. Create the next-numbered file, e.g. db/migrations/0119_my_change.sql. Keep the numbering contiguous and the name a short imperative phrase.
  2. Follow the schema conventions: bigint identity PK, uuid public_id for external refs, client_account_id on every tenant-owned row, timestamptz, jsonb for open shapes, a tenant-scoped idempotency_key unique index on externally-driven write tables.
  3. If it adds a tenant-owned table, grant only the required operations to app_tenant, grant the schema's sequences, and call select _apply_tenant_rls('schema.table');. (For a table whose own id is the discriminator, pass the column: _apply_tenant_rls('tenant.foo', 'id').)
  4. Every foreign key between tenant-owned tables must carry client_account_id on both sides and be validated. A scalar internal-id foreign key is not a tenant-ownership proof.
  5. If you also want the Drizzle query builder to see the table, add/adjust the matching definition in db/src/schema.
  6. Run npm run db:migrate:rehearse --workspace db, then npm run db:migrate --workspace db, then npm run db:verify-rls --workspace db, and confirm the file appears once in public.schema_migrations.

Migrations are forward-only; there is no down-migration mechanism (this is a greenfield platform with no data to roll back). To change something, write a new migration.