Migrations

The entire database schema — tables, indexes, functions, triggers, grants, and RLS — is versioned as ordered SQL files in dps-code-api/migrations/. The chain runs from 000_base_schema.sql through 034_event_authority.sql.

Migration ordering

Migrations are strictly ordered by filename and must be applied in ascending order, one at a time, to a project that has the preceding ones applied. Key ordering facts:

• 000_base_schema.sql is the reconstructed pre-migration baseline (reports, agents, notes, evidence, timeline). It is idempotent and does not duplicate columns later migrations add themselves.
• Migrations 001–019 then 022–034 build the feature surface. There are no files 020/021 — the numbering is not contiguous; do not assume a missing number implies an unapplied migration.
• 033_event_system.sql must run before 034_event_authority.sql (034 CREATE OR REPLACEs functions from 033). Do not re-apply 033 after 034 in the same environment: 033's 9-arg CREATE OR REPLACE would recreate the superseded overloads that 034 drops.
• 034's rpc_report_action adds p_actor_id as a defaulted 10th parameter and explicitly drops the old 9-arg signature, so old deployed code keeps working during rollout (default-argument resolution) — the documented deploy order is apply migration 034, then deploy the API.

How migrations are applied

The repository helper is dps-code-api/apply_rpc_migrations.py:

# Reads the connection string from db.url (or DB_URL), applies 005 + 006
python apply_rpc_migrations.py

# Apply one specific migration file
python apply_rpc_migrations.py <url-file> <migration>

# Read the connection string from a file other than db.url
python apply_rpc_migrations.py <url-file>

Mechanics:

• Uses pg8000 with the Session pooler URI from Supabase (Project Settings → Connect → Session pooler), sslmode=require semantics (encrypted, no public-CA pinning).
• Verifies the migration file list exists before connecting.
• Runs each file's SQL, then records it in public.schema_migrations (file, applied_at, checksum — sha256) via upsert. The record insert is guarded so an older database without the table stays runnable.
• The admin Schema tab reads schema_migrations via rpc_admin_schema_status to show the applied-migration ledger.

Migration files themselves are idempotent by convention (CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE, guarded DO $$ … EXCEPTION WHEN duplicate_column blocks), so re-running an already-applied file is generally safe — with the 033 → 034 exception above, and with the understanding that any new environment should apply the chain once, in order.

How to create a migration

1. Name it NNN_short_snake_name.sql with the next free number in the chain.
2. Follow the established conventions:
   • CREATE TABLE IF NOT EXISTS / CREATE OR REPLACE FUNCTION / guarded DO blocks so the file is idempotent.
   • Explicit revoke from public, anon, authenticated (and often service_role) followed by narrow grant lines for exactly the privileges the API needs.
   • ALTER TABLE … ENABLE ROW LEVEL SECURITY on any new table holding sensitive data.
   • Triggers for append-only/delete-blocking where retention matters.
   • Every new rpc_* function gets SECURITY INVOKER (or DEFINER only with justification), a fixed search_path, EXECUTE revoked from public/anon/authenticated, and EXECUTE granted to service_role.
3. Write a header comment explaining the why (motivation, security model, retention decisions) — every migration in the chain has one; follow suit.
4. If the migration changes production behavior, do not edit an already-applied migration. Create a corrective migration (project rule).
5. Record the migration's purpose in the Changelog and update this documentation when it alters schema, permissions, or operations.

How migrations should be tested

• Apply the full chain 000–034 to a scratch PostgreSQL 16 project (the release verification process applies the chain in order on top of 000).
• Verify idempotency claims: re-running a guarded file should not error.
• Exercise each new RPC through PostgREST or direct SQL: happy path, each documented error tag (REPORT_NOT_FOUND, STATUS_CONFLICT, ACTION_ALREADY_COMPLETED, DIRECTOR_REQUIRED, …), and privilege checks — confirm public/anon/authenticated cannot execute it and service_role can.
• Verify RLS: with a browser-role JWT, sensitive tables must return nothing and the Realtime probe path must behave as designed (migrations 026/027 have dedicated live-production verification notes).
• The API contract suites (*_tests.py) exercise route → RPC error mapping against a fake Supabase; SQL-level behavior needs direct verification.

How to safely deploy migrations

Deploy order matters. The safe sequence for a schema change:

Backup / Snapshot
      ↓
Database Migrations      (apply first — the new functions must exist
      ↓                   before the API calls them)
Backend Deployment       (API code compatible with the new schema)
      ↓
Frontend Deployment      (bundle expecting the new endpoints/shapes)
      ↓
Smoke Tests

Migration compatibility: application code must be deployed after the migrations it depends on, and must remain compatible with the previous schema until the new code ships. Migrations are written to be backward compatible (defaulted parameters, additive columns), but the reverse direction — deploying new code before its schema — breaks requests immediately. Never deploy application code that calls a function/column the database does not have yet.

Concrete checklist:

1. Take a Supabase backup/snapshot (Dashboard → Database → Backups) or verify point-in-time recovery is enabled.
2. Apply migrations in order via apply_rpc_migrations.py, one at a time, watching output for the OK + recorded in schema_migrations lines.
3. Spot-check schema_migrations rows and the admin Schema tab.
4. Deploy the API (Render auto-deploys from main).
5. Deploy the frontend (Cloudflare Pages).
6. Run the production smoke test.
7. If a migration failed partway: because files run inside one connection transaction per file (con.run(sql)), a failed file leaves no partial state from that file — but check whether earlier statements in the same file committed (pg8000 native run does not wrap multi-statement input in one transaction unless the file uses one). Investigate before re-running.

Rollback considerations

• Roll forward, not back. Project convention: do not modify an already-applied migration to change production behavior; create a corrective migration instead.
• Tables/functions created by a migration can be dropped in a corrective migration if a feature is being removed, but data-bearing tables (e.g. events, punishments, admin_audit_log) are retention records — they are never dropped.
• CREATE OR REPLACE keeps privileges on the same function OID; DROP FUNCTION + recreate (as 034 does for signature changes) requires re-stating grants, which 034 documents and performs in the same file.
• RLS/policy changes are covered by the same forward-only rule.

Production precautions

• Migrations that establish security, RPC, event, and audit authority deserve extra review. The chain's security spine:
  • 009 — admin allowlist + append-only admin audit + schema ledger.
  • 010/011 — narrow admin RPCs, least-privilege grants.
  • 012 — Director rank checks inside the database.
  • 016 — global RPC privilege lockdown (EXECUTE revoked from public/anon/authenticated).
  • 017 — foreign keys/cascades + status constraints + timeline retention.
  • 019 — contact-thread guards + agent rank hierarchy.
  • 026/027 — Realtime private-channel authorization fixes.
  • 033/034 — canonical event system + actor identity + atomic destructive operations.
• Never run migrations against production from a development machine without explicit approval; apply_rpc_migrations.py connects wherever the connection string points, so verify the target project first.
• The demo project must have the same migration chain applied as production.

Related reading

• Schema
• Database deployment
• Troubleshooting — migration failures