Skip to content
Surfc Wiki

Postgres Test Harness

Postgres Test Harness

Hermetic Vitest tests against a local Supabase stack. Pre-merge gate for every PR that touches supabase/** in the surfc/ repo.

Built across SUR-331 and its four sub-issues:

SliceIssueLands
331a — harness skeleton + migrations replaySUR-334PR #164
331b — RLS coverageSUR-335PR #166
331c — RPC + trigger + two-session concurrencySUR-336PR #167
331d — soft-delete + smoke-of-the-smoke + docsSUR-337PR pending

Storage RLS (note-images bucket) is deferred to SUR-338 — it requires the storage HTTP API rather than raw pg.

Why this exists

The Supabase Postgres surface in surfc reached the point where a regression in an RLS policy, an RPC, a trigger, or a migration could only be caught at deploy time. Pre-SUR-331, the only DB CI gate was a live-DB drift check against a real Supabase project — useful for catching out-of-band SQL-editor edits, but unable to catch a policy-file bug before merge.

The harness is the pre-merge counterpart: hermetic, fast, runs on every PR. Together with the (now weekly) drift check, the two cover both directions:

Pre-merge harnessLive-DB drift cron
CatchesMigration / RLS / RPC / trigger bugs in PR diffsSQL-editor edits to live DB that bypass migrations
CadencePer PR touching supabase/**Mondays 09:00 UTC + manual dispatch
NetworkNone (local Postgres)Real (Supabase live DB)
Cost$0$0

Running it locally

Prereqs: Docker, Node 20.

Terminal window
cd surfc/
npm run db:start    # supabase start — brings up the local stack and applies migrations
npm run db:reset    # supabase db reset --no-seed — re-applies migrations from scratch
npm run test:db     # vitest run --config vitest.db.config.js

db:start is one-time-per-session; db:reset && test:db is the inner loop. npx supabase stop when you’re done.

What’s covered

56 tests across:

  • Schema invariants — every key public table exists; RLS enabled on every user-owned table; key triggers attached; key constraints + generated columns intact; key RPCs callable; key trigger-backing functions exist; migration filenames match 0NNN_<snake_name>.sql.
  • RLS — positive + negative coverage for notes, books, custom_ideas, wrapped_key_blobs, user_profiles, ai_usage_daily, waitlist_requests. Service-role visibility, own-row visibility, no-cross-user-UPDATE, plus SUR-363 ownership-only direct-signup edge cases (no waitlist row at all; approved waitlist with user_id IS NULL).
  • RPCs — select_fresh_transfer_blob (fresh / expired / future-skewed / cross-user IDOR / p_max_age_ms).
  • Triggers — on_auth_user_created → handle_new_auth_user (SUR-362; full_name path, email-local-part fallback, empty-string fallback, ON CONFLICT DO NOTHING preserves existing profile, decoupled from waitlist).
  • Integration — direct-signup end-to-end via supabase-js admin client (auth.admin.createUser → trigger → user_profiles row with month_limit=50+user_tier=free). Real committed rows; explicit afterEach cleanup.
  • Concurrency — two-session test for the per-user pg_advisory_xact_lock introduced in migration 0018 (closes the gap deferred from SUR-330).
  • Soft-delete invariant — for each user-owned content table: default fetch excludes, explicit filter includes, updated_at advances.

The full list with file pointers is in supabase/test/README.md.

Architecture notes

  • Vitest + pg is the runner of record. SQL-only test files (e.g. supabase/tests/0018_*.sql) remain runbook artefacts pasteable into the Supabase SQL editor; they are not invoked from the harness.
  • Single pg.Client per test, BEGIN/ROLLBACK around fixture load + assertions. Identity-switching is done via SET LOCAL ROLE + set_config('request.jwt.claims', ...), all auto-reverted at ROLLBACK. Tests are therefore order-independent and don’t pollute each other.
  • The two-session concurrency test in rpc.usageMonth.test.js is the only one that COMMITs. Advisory locks only release at the outer transaction boundary, so BEGIN/ROLLBACK can’t exercise contention. That test uses random UUIDs and explicit cleanup in finally — ai_usage_daily is deleted first because it does NOT cascade from auth.users (only user_profiles does).
  • JWT minter uses node:crypto rather than jsonwebtoken — no extra dependency.
  • Localhost-only safety guard — the harness’s pg.Pool factory refuses any connection string that doesn’t include localhost or 127.0.0.1. Defense against accidentally pointing at a real DB.

CI

.github/workflows/db-test.yml. Runs on pull_request + push to main with the supabase/** path filter. Sequence: checkout → setup-node 20 → setup-supabase-cli → npm ci → supabase start → supabase db reset --no-seed → npm run test:db → supabase stop (in if: always()).

Cold-start runtime is ~60–120 s. Workflow timeout cap is 10 minutes.

.github/workflows/schema-smoke-test.yml (live-DB drift check) was downgraded from per-PR to weekly cron in SUR-334 once the local harness took over per-PR coverage.

Smoke-of-the-smoke

Periodic confidence check that the harness still catches regressions. Manual procedure documented in supabase/test/README.md — drop one RLS policy / remove one advisory-lock line / break one constraint, run the harness, observe the expected failure, revert. Don’t merge the throwaway branch.

  • Data architecture — the user-owned tables under test.
  • Deployment architecture — Supabase Edge Functions and how their tests sit alongside this harness.
  • SUR-307 — Supabase staging project. Independent of this harness; shares migration-apply tooling.