ECC DPS · Engineering Docs

Appearance

Database Deployment ​

The database is a Supabase PostgreSQL project. Schema changes ship as ordered migration files and are applied before the application code that depends on them.

Safe deployment order ​

text
Backup / Snapshot
      ↓
Database Migrations
      ↓
Backend Deployment
      ↓
Frontend Deployment
      ↓
Smoke Tests

Why migration compatibility matters ​

Application code is deployed after the migrations it calls. The reverse order — new code against old schema — breaks immediately (missing function, missing column). Migrations are written to be backward compatible in the other direction (additive columns, defaulted function parameters, dropped-then- recreated signatures that old callers satisfy via default-argument resolution), so the window between "schema applied" and "new API live" is safe: the previous API keeps working on the new schema.

The documented special case (migration 034): apply the migration, then deploy the API. Do not re-apply 033 after 034 in the same environment.

Applying migrations ​

Use the repository helper (dps-code-api/apply_rpc_migrations.py) with the Supabase Session pooler URI:

bash
cd dps-code-api
python apply_rpc_migrations.py <url-file>        # apply the default set (005, 006)
python apply_rpc_migrations.py <url-file> 034_event_authority.sql

For a full environment (fresh project), apply every file 000–034 in ascending order, one at a time. Each applied file is recorded in schema_migrations (file, applied_at, checksum) and visible in the admin Schema tab.

DANGER

apply_rpc_migrations.py connects wherever its connection string points. Verify you are targeting the intended project (demo vs production) before running. Never run production migrations without explicit approval, and always take a backup first.

Production precautions ​

  1. Backup first. Take a Supabase backup/snapshot or confirm PITR is enabled (Dashboard → Database → Backups) before any migration run.
  2. Apply one at a time in filename order; watch for OK + recorded in schema_migrations output.
  3. Verify the ledger. schema_migrations should list every applied file with a checksum; the admin Schema tab mirrors this.
  4. Deploy the API immediately after the migrations that it requires (migration-before-code contract).
  5. Deploy the frontend after the API.
  6. Smoke-test per the production smoke test.
  7. Demo parity. The demo project carries the same migration chain; apply migrations to demo first when possible, and keep both in sync.

Rollback considerations ​

  • Forward-only convention. Do not modify an already-applied migration to change production behavior; create a corrective migration. Rollback of a bad schema change is a corrective migration (or a restore from backup for data corruption).
  • Retention tables are never dropped. events, admin_audit_log, admin_health_history, punishments, and timeline are records; any "rollback" of their features must be additive (stop writing) rather than destructive.
  • Restore path. For data-loss incidents, restore the Supabase backup/PITR point, then re-apply migrations after the restore point if needed. Restore is a coordinated incident action — see Operations.

Migration failure handling ​

  • A failed file leaves no partial state from that file if it runs inside one transaction; verify whether earlier statements in the file committed before re-running (see Troubleshooting).
  • The 033 → 034 ordering constraint means a mistaken re-apply of 033 can recreate superseded function overloads; the documented fix is to re-apply 034 (which drops them) — do not invent a partial cleanup.