Skip to content
View as .md

Supabase

Schema, migrations, and verify scripts in packages/supabase/. There is no local DB — the project is linked straight to the remote (prod) via packages/supabase/.temp/project-ref, so every push and write hits production. Migrations are dated SQL files at packages/supabase/migrations/YYYYMMDDHHMMSS_slug.sql.

Apply migrations with bun run push-migrations from the repo root — it filters to @arbe/supabase (so the cwd is right; running supabase db push from the repo root hits the separate edge-functions supabase/ dir and fails the migration-history check) and regenerates database.types.ts afterward. Don’t reach for the raw bunx supabase db push unless you need a flag the wrapper lacks.

Terminal window
bun run push-migrations                       # apply new migrations to prod + regen types (run from repo root)


cd packages/supabase                          # for the lower-level commands below
bunx supabase db push --linked --dry-run      # preview only
bunx supabase migration list --linked         # diff local vs remote history
bun run --filter '@arbe/supabase' update-types   # regenerate database.types.ts on its own


cd packages/                                 # workdir auto-resolves here, NOT packages/supabase/
bunx supabase db query --linked "select tablename from pg_tables where schemaname='public'" -o table  # ad-hoc read
bunx supabase db query --linked -f supabase/tests/verify-orphan-house-cleanup.sql
bunx supabase db query --linked -f supabase/tests/verify-cascade-delete-atomicity.sql

Extensions: pg_cron is enabled. Workflow schedules own the wf:<id> jobs — a trigger syncs them from workflows.schedule, so manage them through the column, never cron.schedule directly (see workflows). Inspect with select * from cron.job, firing history in cron.job_run_details.

Verify scripts (packages/supabase/tests/*.sql) are begin; do $$ ... $$; rollback; blocks — DB untouched, asserts raise on first failure. Empty rows + no 400 = passed (the Management API swallows raise notice). A failure surfaces as unexpected status 400: ... ERROR: P0004: FAIL: <message>. psql "$POOLER_URL" -f … also works since scripts are pure SQL.

Gotchas worth remembering:

  • supabase db query is not psql — it routes through the Management API, which rejects \set, \echo, or any \-prefixed line with syntax error at or near "\". Keep verify scripts portable: no meta-commands; any end-of-run banner goes as raise notice inside a DO block (and accept that the API swallows it).
  • -f is mandatory for files. A bare positional like bunx supabase db query --linked ./tests/foo.sql is interpreted as inline SQL and fails with syntax error at or near ".".
  • db query --linked does not run concurrently. Each invocation mints the same temporary cli_login_postgres login role (“Initialising login role…”), so two agents querying at the same moment knock each other out with password authentication failed for user "cli_login_postgres" (SQLSTATE 28P01). It reads exactly like dead credentials and is not — retry once or twice before calling it a blocker, and only believe it when a read fails alone and repeatedly. Worth telling delegated workers explicitly; a fresh worker treats the first 28P01 as a hard stop.
  • Workdir auto-detection lands on packages/, not packages/supabase/. Paths passed to -f are resolved relative to that workdir, so from anywhere inside the supabase package you still write supabase/tests/foo.sql.
  • A new function is executable by everyone until you revoke it. Postgres grants EXECUTE to PUBLIC on create, and PostgREST exposes every public-schema function as an RPC — so grant execute ... to service_role alone narrows nothing; anon still has it via PUBLIC. Any security definer function needs an explicit revoke all on function ... from public, anon, authenticated followed by a grant to the roles that may call it (the revoke strips service_role too when PUBLIC was its only grant). This is how resolve_secrets_for_scope stayed anon-callable. Guard: bunx supabase db query --linked -f supabase/tests/verify-definer-grants.sql.
  • RLS on with no policies is deny-all, but only for rows. That is the right shape for service-role-only tables such as usage_events, wf_conductors, wf_run_threads, thread_directors, and feedback. Revoke table grants too: TRUNCATE is a table-level privilege that no policy can filter. The same verify script covers both layers.
  • Rewriting a function needs a new migration. Always rebuild from the latest definition, not the earliest: rg -l "function_name" supabase/migrations/ | tail and start from the newest migration’s version — recreating from an older one silently drops parameters/columns added later (this broke the feedback RPC when a rate-limit edit restored a pre-sentiment signature). To change cleanup_orphaned_houses or any create or replace function, add a new dated migration that re-issues the statement — don’t edit historical migration files (e.g. 20260417000000_retire_stranded_bots.sql layered on top of earlier RLS work).
  • Connection pooler vs direct. supabase db push uses the pooler URL (port 5432, pooler.supabase.com). Direct db.<project>.supabase.co:5432 works too but isn’t what the CLI advertises.
  • Never edit database.types.ts by hand — it’s a generated artifact. Manual edits drift from the remote schema and silently break adapters that trust the type.
  • A jsonb parameter bound to the string "null" becomes jsonb null, not SQL NULL, and fails col is null checks. Bind JS null for SQL NULL; stringify only actual objects.
  • Cannot find project ref? --linked reads the ref only from packages/supabase/.temp/project-ref (gitignored, not committed) — not from config.toml project_id nor $SUPABASE_PROJECT_REF. On a fresh clone, relink once from inside the package: cd packages/supabase && bunx supabase link --project-ref gxlrglyxsrldjjrpkdsy. And run query/migration commands from inside packages/ — the repo-root supabase/ is a separate dir (edge functions) whose --linked lookup fails the same way.

Code: packages/supabase/migrations/, packages/supabase/tests/, packages/supabase/database.types.ts.
See system/storage, system/permissions.