Files

13 KiB

Supabase: local stack, migrations, tests

Status: Step 3 deliverable, extended at Step 4 (2026-10-08). Covers the foundation (profiles, platform roles, audit log) and the anatomical sheet tables (ANATOMICAL_SHEET_MODEL.md). Conventions: BACKEND_ARCHITECTURE.md §3–§9.

1. Layout

supabase/
├─ config.toml            CLI config (public schema exposed only, PG 17, strong passwords)
├─ migrations/            forward-only SQL, <timestamp>_<name>.sql
│  ├─ 20261008100000_foundation.sql       schemas private/audit, updated_at, audit log
│  ├─ 20261008100100_profiles.sql         profiles + auth.users trigger + RLS
│  ├─ 20261008100200_platform_roles.sql   platform_role, profile_roles, helpers, RPCs
│  ├─ 20261008110000_anatomical_sheets.sql species, sheets, immutable versions, assets, lifecycle RPCs
│  ├─ 20261008110100_private_execute_hardening.sql EXECUTE revoked from PUBLIC on the Step 3 helpers
│  ├─ 20261009100000_sheet_asset_storage.sql bucket anatomical-sheet-assets + read policy through the sheet
│  └─ 20261010100000_sheet_document_schema_v2.sql document schema 2: references, review_status, count_range checks
├─ seed.sql               LOCAL dev accounts + first owner (never run in prod)
├─ seed/anatomical-sheets/equine/  official equine sheet as a DRAFT, generated by `pnpm import:equine`
│  ├─ legacy-sources/     the historical Horse.svg, Horse_head.svg, pathologies.json, vertebrae.ts (importer input)
│  ├─ legacy-import/      baseline import (document, id map, SVG copies, IMPORT-REPORT.md), no SQL
│  ├─ curation/           authored changes.json + references.json
│  └─ draft/              curated working revision (document, horse.svg, sheet-ref.json, report-migration-map.json, CHANGELOG, REVIEW-REPORT, review/*.svg; bundled into the app with SHEET_BUNDLE=draft) + equine_draft.sql: idempotent, converging seed (sheet, 1 asset row, working draft), applied by db reset and by PGlite
└─ tests/                 pgTAP, one file per concern, allowed AND denied paths
packages/supabase/
├─ src/client.ts          createBrowserClient() (anon key, PKCE)
├─ src/database.types.ts  `supabase gen types` output
├─ src/anatomical-sheets.ts publishSheetVersion(): Zod + integrity gate before the RPC
└─ tests/db/              PGlite harness: runs the pgTAP files without Docker
packages/domain/
└─ src/anatomical-sheet/  canonical sheet document (Zod), validateSheetDocument, createEmptySheetDocument

2. Prerequisites

  • Node 24 + pnpm via corepack (WORKSPACE.md).

  • Docker daemon reachable by your user for the real stack. If docker info says permission denied ... docker.sock, add yourself to the docker group and log in again:

    sudo usermod -aG docker "$USER"
    
  • No Docker: the PGlite suite still runs (see §5), the CLI commands do not.

The Supabase CLI is a root devDependency (pnpm supabase <cmd>), pinned like every other tool. Never install it globally.

3. Start, stop, reset

pnpm db:start      # supabase start: pulls images, applies migrations + seed, prints URLs and keys
pnpm db:stop       # supabase stop (keeps the volume)
pnpm db:reset      # drop + replay every migration + seed from zero

Local endpoints (config.toml):

Service URL
API (PostgREST, Auth, Storage) http://127.0.0.1:54321
PostgreSQL postgresql://postgres:postgres@127.0.0.1:54322/postgres
Studio http://127.0.0.1:54323
Mail catcher (confirmation / OTP mails) http://127.0.0.1:54324

pnpm db:start prints the anon and service_role keys. Only the anon key ever reaches the browser.

Containers, volumes and network are named supabase_<service>_faunosteo (project_id of config.toml): the CLI imposes this pattern, so the faunosteo_ prefix convention of DEPLOY.md ("Docker resource naming") applies as a suffix here. Several projects can run side by side as long as their project_id and ports differ.

Two kinds of seed (config.toml [db.seed] sql_paths):

File Scope Content
seed.sql local only dev accounts + first owner
seed/…/*.sql every environment versioned reference data, idempotent (today: the official equine sheet as a draft); each file listed explicitly in sql_paths

The PGlite harness applies every .sql under seed/ after the migrations, seed.sql never (it needs the real auth schema); a test fails when a seed file is missing from sql_paths. On another environment, apply a versioned seed with psql -f from a privileged session, then upload its assets (§3.1). Never run supabase db push --include-seed against a shared database: the list also contains seed.sql and its known local password.

3.1 Sheet assets (Storage)

SVG files live in the private bucket anatomical-sheet-assets, keyed by content (<sha256>.svg). Reading goes through the sheet (policy anatomical_sheet_assets_read): a published version of a usable sheet, or any version for its editors. No API role uploads; the operator does, with the service key, from the generated seed folder:

export SUPABASE_URL=http://127.0.0.1:54321
read -rs SUPABASE_SERVICE_ROLE_KEY && export SUPABASE_SERVICE_ROLE_KEY   # typed, not in shell history
pnpm assets:upload supabase/seed/anatomical-sheets/equine/draft

The script verifies each file against assets.json (sha256, size) before uploading, keeps objects already present, and calls confirm_sheet_asset_upload (service role only) for each asset row: the read policy ignores a row whose upload was never confirmed, so a user who registers a known hash on their own sheet opens nothing.

Seed accounts (local only, password LocalDev-only-password-1):

Email Role
owner@faunosteo.local platform owner (bootstrapped by seed.sql)
alice@faunosteo.local plain user
bob@faunosteo.local plain user

4. Writing a migration

  1. pnpm supabase migration new <name> creates supabase/migrations/<timestamp>_<name>.sql.
  2. A migration that adds a table adds, in the same file: RLS enable, explicit revoke all ... from public, anon, authenticated then the exact grants, separate SELECT / INSERT / UPDATE / DELETE policies, indexes, comments.
  3. Add or extend a pgTAP file under supabase/tests/: the allowed path and every denied path (other user, anon, direct write bypassing an RPC). Scope counts to the fixture ids: on the real stack the seed accounts exist, in PGlite they do not.
  4. pnpm --filter @faunosteo/supabase test:run (PGlite, seconds), then pnpm db:reset && pnpm db:test on the real stack.
  5. pnpm db:types and commit packages/supabase/src/database.types.ts.
  6. Never edit an applied migration. Corrections are new migrations.

Rules enforced by the schema-types test for every future public table: RLS enabled, no using (true) / with check (true) policy for anon or authenticated.

5. Tests

Two runners, one SQL source of truth (supabase/tests/*.sql, pgTAP):

Command Engine Needs Docker What runs
pnpm --filter @faunosteo/supabase test:run PGlite (PostgreSQL 17 in WASM) no shim of the Supabase base schema (auth + storage tables) + all migrations + seed/** + every pgTAP file; schema ↔ types check; client factory; publish gate
pnpm db:test supabase test db (pg_prove in the local stack) yes the same pgTAP files against the real Supabase image

pnpm test:run at the root runs the PGlite suite together with the app suite. The shim (packages/supabase/tests/db/supabase-shim.sql) recreates the API roles, auth.users, auth.uid() and the permissive default privileges of Supabase so that a forgotten revoke fails a test.

Two parity traps met so far: counts must be scoped to fixture ids (the real stack carries seed.sql rows), and order by on text must pin collate "C" (the real image sorts with a locale collation, PGlite with C).

How a test impersonates a user (same technique in both runners):

set local role authenticated;
select set_config('request.jwt.claims', '{"sub":"<user uuid>","role":"authenticated"}', true);

6. Generated types

pnpm db:types          # regenerate packages/supabase/src/database.types.ts (local stack running)
pnpm db:types:check    # CI: fail on any diff

The CLI emits unformatted TypeScript; both scripts pipe it through Prettier (repo config) so the committed file is stable byte for byte. The schema-types test checks tables, columns, enum labels and RPC argument names against the migrated schema, so a stale file fails before CI.

7. First platform owner in a real environment

The RPCs refuse self-grants and need an existing owner or admin. The first owner is created once, from a privileged SQL session (service connection, psql on the host), never from the browser:

select private.assign_platform_role(null, '<auth.users.id of the owner>', 'owner');

The call is audited with a null actor. Afterwards every role change goes through grant_platform_role / revoke_platform_role.

8. Manual validation (Definition of Done: "an authenticated request hits a protected table safely")

URL=http://127.0.0.1:54321
ANON=<anon key printed by pnpm db:start>

token() {  # $1 = email
  curl -s -X POST "$URL/auth/v1/token?grant_type=password" \
    -H "apikey: $ANON" -H "Content-Type: application/json" \
    -d "{\"email\":\"$1\",\"password\":\"LocalDev-only-password-1\"}" | jq -r .access_token
}
ALICE=$(token alice@faunosteo.local); OWNER=$(token owner@faunosteo.local)
ALICE_ID=10000000-0000-0000-0000-00000000000a

# 1. alice reads exactly one profile (hers)
curl -s "$URL/rest/v1/profiles?select=id,display_name" -H "apikey: $ANON" -H "Authorization: Bearer $ALICE"

# 2. anon reads nothing (permission denied)
curl -s "$URL/rest/v1/profiles" -H "apikey: $ANON"

# 3. alice cannot deactivate herself (column privilege) nor self-grant admin
curl -s -X PATCH "$URL/rest/v1/profiles?id=eq.$ALICE_ID" -H "apikey: $ANON" \
  -H "Authorization: Bearer $ALICE" -H "Content-Type: application/json" -d '{"active":false}'
curl -s -X POST "$URL/rest/v1/rpc/grant_platform_role" -H "apikey: $ANON" \
  -H "Authorization: Bearer $ALICE" -H "Content-Type: application/json" \
  -d "{\"target_user_id\":\"$ALICE_ID\",\"role\":\"admin\"}"

# 4. the owner grants moderator to alice; the grant is audited
curl -s -X POST "$URL/rest/v1/rpc/grant_platform_role" -H "apikey: $ANON" \
  -H "Authorization: Bearer $OWNER" -H "Content-Type: application/json" \
  -d "{\"target_user_id\":\"$ALICE_ID\",\"role\":\"moderator\"}"
psql postgresql://postgres:postgres@127.0.0.1:54322/postgres \
  -c "select actor_user_id, action, target_user_id, metadata from audit.audit_logs order by occurred_at"

Expected: 1 → one row; 2 → 42501; 3 → both 42501; 4 → a uuid, then two audit rows (bootstrap with null actor, grant by the owner).

9. Wiring the app

.env (Docker) or apps/app/.env.local (Vite dev):

APP_SUPABASE_URL=http://127.0.0.1:54321        # VITE_SUPABASE_URL in dev
APP_SUPABASE_ANON_KEY=<anon key>               # VITE_SUPABASE_ANON_KEY in dev

Both empty (the default) keeps the app in its current local-only mode: getSupabaseClient() returns null. No screen uses the client yet.

10. Self-hosted parity

The local stack runs the same images as a self-hosted deployment (supabase/docker compose), so migrations, policies and tests transfer unchanged. The self-hosted runbook (VPS sizing, secrets, backups, TLS) is a separate deliverable once the hosting question (OPEN_QUESTIONS Q2) is settled; it reuses pnpm supabase db push --db-url <...> for migrations.