2. Database migrations as raw SQL via Supabase

Date: 2026-06-06

Status

Accepted

Context

The schema is RLS-, trigger-, and view-heavy: every table has Row Level Security policies, audit is enforced by database triggers, contractor invoice visibility needs careful policy logic, and scheduled jobs will use pg_cron. These are all first-class SQL features that ORMs model awkwardly or not at all. The project convention is also "DB access goes through src/db/ — no inline SQL in route handlers."

Decision

Use raw SQL migrations (Supabase migration format) as the single source of truth for the schema, stored in src/db/migrations/NNNN_*.sql and applied in order. TypeScript types are generated from the live schema (supabase gen types) into src/db/types.ts (git-committed, never hand-edited, excluded from Biome). Application code reads/writes only through named, typed query functions in src/db/queries/ and the two client factories in src/db/clients/; no other module constructs a Supabase client or embeds SQL.

No ORM (e.g. Drizzle): it would split the source of truth between an ORM schema and hand-written policies/triggers/views, and add a layer over the RLS-centric design without buying much here.

Consequences

• One place to read the full schema and its security rules: the migration files.
• Compile-time safety on table/column names via generated types, without an ORM runtime.
• Migrations are applied via the Supabase tooling/MCP during development and should be run through the Supabase CLI in CI/CD. Each migration is additive; applied migrations are never edited (fixes go in a new migration — see 0005/0006).
• src/db/types.ts must be regenerated whenever a migration changes the schema.