Ops — Schema Changes

All database DDL lives in `supabase/` as plain Postgres SQL, applied as-is to Neon (the folder name is historical — Supabase was replaced by Neon in DECISION…

All database DDL lives in supabase/ as plain Postgres SQL, applied as-is to Neon (the folder name is historical — Supabase was replaced by Neon in DECISIONS.md, and the SQL is portable unchanged).

The four files (apply order is binding)

Order	File	What it provides
1	supabase/auth-shim.sql	Supabase-provided objects on bare Postgres/Neon: auth.users bridge table (Clerk user_… → UUID), auth.uid() function reading the app.user_id GUC, authenticated role + grants. Idempotent.
2	supabase/schema.sql	The 8 app tables, 10 enums, indexes, updated_at triggers.
3	supabase/rls-policies.sql	Row-Level Security: enables RLS on all 8 tables and creates the policies (active-profile reads, owner full access, participant-scoped matches/bids/referrals/fee-splits, message send into accepted/active matches, etc.).
4	supabase/seed.sql	Reference data: 45 practice_areas and 66 jurisdictions (used by the reference endpoints and by matching).

Never run the BUILD-SPEC's §4.3 / §7.5 ALTER statements. The columns they add (triage_result, last_read_at) are already consolidated into schema.sql. Running them separately causes duplicate-column errors (pass-1 review note 1; the scripts enforce this).

Local apply + verify (ephemeral, deterministic)

# Applies auth-shim -> schema -> rls -> seed to an ephemeral Docker
# Postgres 16 on :54329 (destroys/recreates it each run).
bash scripts/db-apply.sh
 
# Asserts the result: 8 app tables + 2 seed tables, 10 enums, policy count,
# 45 practice areas / 66 jurisdictions, auth.uid() behavior.
bash scripts/db-check.sh
 
# Remove the ephemeral container when done.
bash scripts/db-apply.sh --teardown

If DATABASE_URL is set in the environment, db-apply.sh targets that database instead of Docker (and --teardown becomes a no-op) — useful for applying to Neon, and how the test suite reuses a live DB when one is present.

Applying to Neon

The live database is a Neon project (guild). Options:

# Using the pooled connection string from .env.local (needs `export` — see NOTES):
export $(grep -v '^#' .env.local | xargs)
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f supabase/auth-shim.sql \
  -f supabase/schema.sql -f supabase/rls-policies.sql -f supabase/seed.sql
 
# Or passwordless via the Neon CLI (no DB password leaves the machine):
neonctl psql -- -f supabase/auth-shim.sql -f supabase/schema.sql \
  -f supabase/rls-policies.sql -f supabase/seed.sql

How to ship a schema change

1. Edit the SQL file(s) in supabase/ — never hand-write ALTERs that duplicate existing columns.
2. Apply to the ephemeral Docker DB and run bash scripts/db-check.sh — it must pass.
3. Run the test suite (npm test) — RLS and integration suites assert policy behavior against an ephemeral DB.
4. Commit the SQL change with a conventional message.
5. Apply to Neon (command above) and re-run db-check.sh against it.
6. Update docs if the change alters behavior described in docs/user/, docs/dev/, or this file — docs must match main.

RLS notes (read before writing policies)

• Every table has RLS enabled; the app connects as the authenticated role with a per-request app.user_id GUC (set by dbForUser in lib/db.ts), so policies govern every app query.
• conflict_checks deliberately has no INSERT policy — checks are created server-side through dbAdmin (no RLS), matching spec §1.2's "System creates conflict checks".
• The Neon connection user needs authenticated role membership for SET LOCAL ROLE to work — done once on the live project (GRANT authenticated TO <user>).
• After changing rls-policies.sql, update the policy-count assertion in scripts/db-check.sh if the number of CREATE POLICY statements changes.