# 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.

```sh
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](../../workflows.md)). 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`.<br>
See [system/storage](storage.md), [system/permissions](../access/permissions.md).