# 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 "."`.
- 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`.
- Rewriting a function needs a new migration. 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.
- `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](./permissions.md).