Database schema verification

Production once ran “healthy” while the schema was behind the deployed code — a missing messages.snoozed_until column had to be added by hand. This page defines how migrations are applied and how drift is detected, so that cannot happen silently again.

Applying migrations#

All schema lives in supabase/migrations/*.sql and is written to be idempotent (CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS), so re-running the full set is always safe.

Terminal

supabase db push

Against a raw Postgres connection without the CLI, apply in lexical order:

Terminal

for f in supabase/migrations/*.sql; do psql "$DATABASE_URL" -f "$f"; done

Then reload the PostgREST schema cache so the API sees new columns immediately:

Reload the API schema

NOTIFY pgrst, 'reload schema';

Drift detection#

The application declares the columns it requires in server/src/services/schema-verification.ts as REQUIRED_SCHEMA.

Pre-deploy gate#

Terminal — server

bun run verify:schema

Exits 0 when the live database satisfies REQUIRED_SCHEMA, 1 on drift (listing the missing tables and columns), and 2 if the check could not run. Wire it into the deploy pipeline as a pre-release step against the production database.

Runtime readiness#

GET /health includes a schema check. On drift it reports status: "degraded" with HTTP 503 and a checks.schema entry listing the affected tables, so an incompatible deploy fails readiness before taking traffic. The result is cached for 60 seconds to keep polling cheap.

Why this is RLS-safe#

A missing column or table returns a Postgres 42703/42P01 — or PostgREST PGRST204/PGRST205 — regardless of row-level security, while a present-but-empty table simply returns no rows. Transient and operational errors (network failures, expired JWTs) are deliberately not reported as drift.

Backup and rollback expectations#

Backup. Automated daily backups run on the managed database; take an on-demand snapshot immediately before applying migrations to production.
Forward-only. Migrations are additive and idempotent. Prefer a new corrective migration over editing one that has shipped.
Rollback. If a deploy fails verify:schema or health readiness, roll the application back to the previous release rather than dropping columns. Additive columns left in place are harmless to older code.