SQL integration test harness

DB-level logic that the TypeScript suite can’t reach — RPCs, RLS policies, CHECK constraints, triggers — is tested with plain psql integration tests in supabase/tests/*.test.sql.

Each file wraps its assertions in a single BEGIN … ROLLBACK, uses RAISE EXCEPTION to fail, and ends with a RAISE NOTICE '… ALL TESTS PASSED'. They leave no residue, so they’re safe to run repeatedly against any database that has the migrations applied.

Running

# All test files against one database:
scripts/run-db-tests.sh "postgresql://user:pass@host:5432/db"

# A single file:
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f supabase/tests/vpat_check_key.test.sql

Against a throwaway Supabase branch (no prod risk)

The Supabase MCP / dashboard can create a development branch, which gives an isolated Postgres with the base Supabase roles (service_role, auth, …). Apply the migrations you need, run the test, then delete the branch.

Why not just supabase start / a branch with all migrations? The migration files use date-only version prefixes (e.g. 20250213_001, 20250213_002), and Supabase’s migration runner derives the version from the leading numeric token — so same-day migrations collide and the from-scratch apply stops after the first one (MIGRATIONS_FAILED). Until that’s fixed, run these tests against a database that already has the full schema (e.g. a branch you migrate manually, or a staging DB). Wiring this into CI is tracked separately.

What’s covered

File	Subject
`vpat_check_key.test.sql`	`vpat_check_key` RPC — auth + per-key rate limiting (#1319)
`credit_idempotency.test.sql`	idempotent credit deduction
`multiplier_pricing.test.sql`	multiplier pricing math
`course_maps_rls.test.sql`	course-map row-level security
`s3_integrations_constraints.test.sql`	s3 integration CHECK constraints
`cents_denomination_*.test.sql`	cents denomination invariants
`legacy_deduct_to_cents.test.sql`	legacy → cents deduction shim
`entitlement_rpcs.test.sql`	per-product entitlement consume/refund/status
`entitlement_webhook.test.sql`	Stripe webhook RPCs (set_account_package, grants)
`team_entitlement.test.sql`	team pool + per-member hard caps (#1347)

Adding a test

Create supabase/tests/<subject>.test.sql following the BEGIN … ROLLBACK pattern (copy an existing file).
Use RECORD for rows returned by a set-returning function — %ROWTYPE only works on a table/view/composite type, not a function’s RETURNS TABLE.
Verify against a real Postgres before committing.