Database — Drizzle schema and migration discipline

The stack

• ORM — Drizzle. Schema-first, no runtime reflection, no decorators.
• Local DB — PGlite. Postgres compiled to WASM, runs in-process. No Docker. No managed service.
• Production DB — any Postgres-compatible target (Vercel Postgres, Neon, Supabase, RDS, your own Postgres).

The same schema runs on both — there is no "Postgres-only" feature in the boilerplate. PGlite supports triggers, RLS, JSONB, and every column type the app uses.

Schema lives in one file

src/models/Schema.ts is the source of truth. Every table, every enum, every index. Some teams break this up; we deliberately don't.

• The whole schema fits in your editor.
• Cross-table relationships are visible without jumping files.
• Renaming a column is one find-and-replace.
• Migrations are generated from the diff against this file.

When the file is too big to fit on one screen, that's a signal to extract a separate Drizzle schema for a new bounded context — not to split Schema.ts into 17 partials.

The migration discipline

Append-only, numbered, never edited

migrations/
  0000-initial.sql
  0001-credits-ledger.sql
  ...
  0008-subscriptions.sql
  ...
  0014-rgpd-and-deletion.sql
  0016-audit-chain.sql
  0017-llm.sql
  0019-idempotency.sql
  0022-saved-views.sql
  0023-evidence-completeness.sql

A migration that has run in any environment must never be edited. The hash is part of the Drizzle migration journal; editing breaks every environment that already applied it.

Need to change something a previous migration set up? Write a new migration that does the change. Your future self will thank you.

Generate, review, commit

npm run db:generate   # diff Schema.ts → SQL
git add migrations/
npm run db:migrate    # apply locally

The generator is dumb. It will produce an ALTER TYPE … ADD VALUE for an enum addition, a CREATE TABLE for a new table, and a DROP COLUMN if you removed something — review the output, don't trust it blindly.

Migrations include the dangerous parts

Triggers, RLS policies, generated columns, default expressions — all live in the migration SQL, not in the ORM. Drizzle is the source of truth for table shape; Postgres features that the ORM doesn't model live in the migration where Postgres can verify them at apply time.

Example: 0016-audit-chain.sql ships four tamper-evidence safeguards (RLS, two triggers, unique index) — none of those are expressible in Drizzle, so the migration carries the SQL directly.

Money is integer cents

Every monetary column is integer NOT NULL with a _usd_cents suffix:

• cost_usd_cents (LLM ledger)
• current_day_spend_usd_cents (LLM budget)
• daily_cap_usd_cents, monthly_cap_usd_cents

Dollars are derived in the UI layer (Math.round(cents / 100)), never stored. This is enforced by rules/core.md §6 and reviewed in every PR.

Audit-chain rows can't be deleted

audit_chain ships with:

• ENABLE ROW LEVEL SECURITY plus FOR UPDATE / FOR DELETE policies that return false always.
• A BEFORE DELETE trigger that RAISE EXCEPTIONs.
• A BEFORE UPDATE trigger that RAISE EXCEPTIONs.
• A worm boolean — when true, an additional trigger blocks delete even for super-users that bypass the first two.

Defense in depth. See compliance for the full design.

When to extract a new bounded context

If a feature ships its own table family (3+ tables) and its own server actions and its own dashboard route, it earns its own Drizzle schema in src/models/<context>/Schema.ts, imported into the main Schema.ts. Examples that earned it:

• audit_chain (1 table, 1 enum) — stayed in Schema.ts (too small).
• llm_usage + llm_budgets + llm_prompts + llm_prompt_evals + org_settings (5 tables) — could earn its own context. Currently in Schema.ts for inertia reasons; on the roadmap to extract.

Don't extract too early. Premature splitting is harder to recover from than a 2,000-line schema file.