Appearance

Database Migrations ​

Migrations are the only way the schema changes. Live DB edits (Studio) are not a source of truth.

Rules [ENFORCED] ​

  • Filename: 14-digit YYYYMMDDHHMMSS_name.sql. Prefer npx supabase migration new {name}. Never the old NNN_ form — it collides on the version parser.
  • Never edit a merged migration — add a new one.
  • Expand-contract, always. Never a breaking change in one step.
  • RLS on every user-facing table. Append-only tables carry no UPDATE/DELETE policy.
  • Use row_to_json(table.*) in RPCs for forward-compat.
  • Capture pg_cron schedules and storage buckets as migrations, not just live state.

Expand-contract ​

Each arrow is (potentially) a separate migration + deploy. The app is always runnable at every step — old and new code both work against the intermediate schema.

The baseline ​

The live schema is the v2 baseline, authored from ADR-0020 as a layered series that starts at supabase/migrations/20260808090000_v2_extensions.sql. The earlier schema-only squash, 20260710134136_baseline_schema_from_prod.sql, is retired: it lives in supabase/migrations/_archive_pre_v2/ with the rest of the 40-file pre-v2 series (reference only, never to be applied; see that folder's README.md), and the three files it once superseded are in supabase/migrations/_archive_pre_baseline/.

Workflow ​

bash
npx supabase migration new add_thing_flag      # creates the timestamped file
# ...write expand-contract SQL + RLS...
# apply to Dev, verify, then Prod (see Promotion)

Promotion (mandatory) ​

Every migration is promoted to both Supabase projects deliberately (Dev first, then Prod). Dev/UAT does not self-sync to Prod. Full procedure + parity-verification SQL in supabase/docs/PROMOTION_RUNBOOK.md. See Deployment & Promotion.

[TRANSITIONAL] — not yet on Dev ​

  • pg_cron job — hardcodes Prod's Edge Function URL; needs a project-URL-aware rewrite first.
  • profile-pictures storage bucket — environment-specific data, not portable schema.

Rollback (DR) ​

Backend rollback = a compensating expand-contract migration (not an in-place revert). Supabase managed daily backups are the interim safety net; PITR + formal RPO/RTO are a deferred decision.

Migration and DB rules (operating-manual text) ​

Provenance — moved from CLAUDE.md on 2026-09-23 (QRS-1288)

This is the verbatim text of CLAUDE.md § "Migrations & DB rules" as of commit 00c1eca, relocated here under the context-architecture programme. Sentences of the form "this said X until [date]" are corrections recorded at the time they were made; the live rule is the corrected one. Retired vocabulary inside those corrections names what was retired and is not a live claim.

Migrations & DB rules [ENFORCED] ​

14-digit YYYYMMDDHHMMSS_name.sql (prefer npx supabase migration new; never the old NNN_ form — it collides on the version parser). Never edit a merged migration — add a new one. Expand-contract (add-nullable → backfill → switch → drop; never a breaking change in one step). RLS on every user-facing table; append-only tables carry no UPDATE/DELETE policy; use row_to_json(table.*) in RPCs for forward-compat. Capture cron (pg_cron) schedules + storage buckets as migrations, not just live DB state.

Domain schemas for bounded contexts (ADR-0031, operating-manual text) ​

Provenance — moved from CLAUDE.md on 2026-09-23 (QRS-1288)

This is the verbatim text of CLAUDE.md § "DOMAIN SCHEMAS FOR BOUNDED CONTEXTS" as of commit 00c1eca, relocated here under the context-architecture programme. Sentences of the form "this said X until [date]" are corrections recorded at the time they were made; the live rule is the corrected one. Retired vocabulary inside those corrections names what was retired and is not a live claim.

DOMAIN SCHEMAS FOR BOUNDED CONTEXTS [ENFORCED — ADR-0031, owner decision 2026-09-04] ​

A bounded context that owns a table set gets its own Postgres schema. Core and shared objects stay in public. The decidable line:

public holds what MORE THAN ONE product surface depends on. A domain schema holds what EXACTLY ONE product surface owns.

The schema is the MODULE; public is its PUBLISHED INTERFACE. RPCs stay in public — it is the only schema PostgREST exposes (config.toml: schemas = ["public", "graphql_public"]) — so a domain schema costs the client nothing, and domain tables are consequently not REST-reachable at all. A name inside a schema never repeats it: biodata.fields, never biodata.biodata_fields.

Open contexts (CLOSED vocabulary, amended only by amending ADR-0031): biodata · meetings. Everything else is public. It governs new contexts; the shared tables in public are not swept.

⚠ THE AXIS IS THE BOUNDED CONTEXT, NEVER THE AUDIENCE. consumer_biodata_* was proposed by the owner, evaluated, and rejected as factually wrong on day one: a biodata is owned by a person, and handle_new_user says primary_context is "PREFERENCE ONLY — never an authorization input", so a merchant can own one for their sister with no schema change — and the design already contains a marriage bureau managing them for families. chat is the same trap from the other side, since conversations carries both workspace_id and consumer_user_id.

⚠ FLAT PREFIXES ALSO HIT THE 63-BYTE IDENTIFIER CEILING, MEASURED. Postgres truncates silently, so two constraints sharing a 63-byte prefix collide under a name nobody wrote: consumer_biodata_access_requests_share_id_requester_user_id_key is exactly 63, while access_requests_share_id_requester_user_id_key is 46. This repo already sits at 61 for whatsapp_message_templates_waba_id_template_name_language_key with no prefix at all.

⚠ anon AND authenticated GET NO USAGE ON A DOMAIN SCHEMA, so the only way in is a SECURITY DEFINER function in public. This is the part a prefix could never give: REVOKE ALL ON SCHEMA is enforceable, a prefix grants nothing. Asserted both ways in supabase/tests/database/domain_schema_boundary_test.sql.

⚠ NEVER WIDEN A DEFINER FUNCTION'S search_path TO REACH A DOMAIN TABLE. Keep SET search_path = public and qualify the table — a qualified name needs no search path, and a wider one inside a definer function is a privilege-escalation vector. Qualification discipline is a known live hazard here, not a hypothetical: "a bare citext passes a full local db reset and FAILS on Dev".

⚠ Edge Functions take a context prefix instead (biodata-manage, biodata-read), because their namespace is genuinely flat and global and appears in the URL and every log line. This inverts the existing verb-noun convention (manage-item), and the twelve live functions are deliberately not renamed — a rename is a deploy plus a client change, and config.toml's own comment warns that a deployed-but-stale function "reads as a working feature". Accepted consequence: a mixed convention.