Database & migrations
Set up the supabase database , cron jobs, and run migrations.
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.
Scheduled jobs: pg_cron vs housekeeping worker
Postgres pg_cron runs SQL-only maintenance inside the database (for example deleting expired refresh tokens — see below). It does not call external HTTP APIs.
Directory SEO domain rating refresh and notification email digest flushing run in the stateless housekeeping orchestrator worker (pnpm orchestrator:dev:worker:housekeeping-bullmq / pnpm railway:orchestrator:start:housekeeping). Each timer reloads work from Postgres or Redis; durable cache rows live in seo_domain_metric_snapshots (rebuildable). Configure DIRECTORY_SEO_WEEKLY_REFRESH_ENABLED, DIRECTORY_SEO_WEEKLY_REFRESH_INTERVAL_MS, and DATAFORSEO_API_KEY on that worker. See Configuration - Worker.
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 npx supabase@latest migration repair <YYYYMMDD> --status applied --remote from backend/ afterward so CLI history matches the remote. See Production — deployment → Supabase production migrations for Option 1 vs Option 2 and when to use applied vs reverted.
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;