Database Migrations

Trigger: any schema change. Owner: Jonny Allum. Policy anchors: POL-01 (RLS), POL-06, POL-10.

Rules

1. Numbered and sequential. Next number lives in BUILD_PLAN.md (0133 applied live as of 12/07/2026); take it, bump the counter in the same commit.
2. Idempotent. create table if not exists, on conflict seeds, guarded alters — a migration must be safely re-runnable.
3. RLS + touch triggers on every table. A table without per-tenant RLS does not ship. Service-only tables (e.g. O11 writes) say so explicitly in their policies.
4. Forward-only and additive. No destructive drop/rename of columns in use; deprecate, dual-write if needed, remove in a later migration once nothing reads the old shape. Rollback is the app's job, not the schema's (SOP-03).
5. Types follow schema. Regenerate/extend packages/db/src/types.ts in the same PR — it is the source of truth for row shapes.
6. Registry mirror. If module ownership of tables changes, regenerate supabase/seed/module_registry.sql via module-registry-sync.

Order of operations for a production schema change

1. Write the migration; run it against local/branch Supabase.
2. Round-trip test through the Supabase store (not just local).
3. Apply to production Supabase before deploying dependent code.
4. Verify: table present, RLS enabled (select * from pg_policies), touch trigger firing, then deploy the app (SOP-03).

Direct data fixes (the exception)

Production data is edited outside a migration only for a logged support action (SOP-07): the SQL, the tenant's request/consent, and the before/after are recorded in the ticket. Never structural changes this way.