Database & migrations
Supabase CLI, migrations, pg_cron notes, and type generation for OpenQuok.
Connect your agent today
Draft from chat, review in your calendar, and publish only what you approve.
Overview
Database workflow is managed through the Supabase CLI + the migrations under backend/supabase/.
Steps (local)
Install and configure Supabase CLI
Set your project id in backend/supabase/config.toml.
Start local Supabase
cd backend
supabase startAfter the stack is up, print connection variables in shell-friendly form:
supabase status -o envSupabase CLI 2.45+ required
supabase start emits the new key format (Publishable key / Secret key) from CLI 2.45+ only. Set them in backend/.env.development.local as PUBLIC_SUPABASE_PUBLISHABLE_KEY and SUPABASE_SECRET_KEY. If supabase status still prints anon key / service_role key, upgrade the CLI (e.g. brew upgrade supabase). See Supabase.Reset DB to current migrations
supabase db resetGenerate types (optional)
pnpm db:local:typegenAggregated migrations
This repo aggregates SQL modules under backend/supabase/db/ into backend/supabase/migrations/.
Aggregate all modules:
pnpm db:aggregate-migrations-allAggregate a single module:
pnpm db:aggregate-migrations-single configProduction backup
Before you squash migrations or move a production project to a new region, back up the database and Storage. See Supabase backup.
Cron: expired refresh tokens (pg_cron)
The user-auth module includes a cron job that deletes expired rows in public.refresh_tokens (runs every Saturday at 3:30 AM GMT). It relies on the Postgres pg_cron extension.
After you enable pg_cron on Supabase Cloud, push the migrations so the cron job becomes active.
When you push schema to a linked production project (in backend/):
pnpm db:production:migration-list
pnpm db:production:push-db:dry-run
pnpm db:production:typegen
pnpm db:production:push-dbSQL editor + migration repair
If you apply module SQL manually in the Supabase Dashboard instead of db push, run migration repair --linked --status applied <YYYYMMDD> afterward so CLI history matches the remote. Full operator steps (RLS in one batch, dry-run vs push) are on Production — deployment → Supabase production migrations.
Supabase Cloud notes (pg_cron)
Enable cron integration on Supabase Cloud
pg_cron, enable it first in the Supabase Dashboard (Integrations → Cron), then run your migrations / push. On Supabase Cloud, you can also run the following once to create the extension and grant the required privileges:
create extension pg_cron with schema pg_catalog;
grant usage on schema cron to postgres;
grant all privileges on all tables in schema cron to postgres;