Database Workflow

The Referenta database is managed declaratively. supabase/schemas/*.sql is the desired-state source of truth, and supabase/migrations/ is the generated deployment history that gets pushed to hosted environments.

In other words: you edit the schemas, you generate migrations from them, and the migrations are what actually run against shared dev, staging, and production.

Schema File Layout

Schema files are split by product domain so it's clear which file owns which feature:

• 10_shared_core.sql — shared core tables and primitives used across products.
• 20_assistant.sql — Assistant product schema.
• 30_research.sql — Research product schema.
• 40_knowledgebase.sql — Knowledgebase product schema.
• 50_contactbase.sql — Contactbase product schema.
• 60_press_monitor.sql — Press Monitor product schema.
• 70_projects.sql — Projects product schema.
• 80_support.sql — Support product schema.

The numeric prefix controls load order. Keep new tables, columns, and policies in the file that matches their product domain instead of creating new top-level files.

RLS Lives With Its Table

Keep Row Level Security policies in the same schema file as the table they protect. This makes ownership obvious and prevents a policy from drifting away from the table definition.

Avoid leaving table or policy changes only in the Supabase dashboard. If something was changed there to unblock work, bring it back into the corresponding supabase/schemas/ file so the repo stays the source of truth.

Standard Flow

The day-to-day database change flow is:

pnpm db:diff my_change
pnpm migration:up

Step by step that means:

1. Edit the matching file in supabase/schemas/.
2. Run pnpm db:diff <name> to generate a migration file in supabase/migrations/.
3. Run pnpm migration:up to apply pending migrations to the local database.
4. Regenerate TypeScript types with pnpm db:types:local if the schema changed.
5. Commit both the updated schema file and the generated migration.

How Changes Reach Hosted Environments

In the normal flow, hosted deploys are automatic:

• merging a branch into dev automatically triggers a database deploy to staging through GitHub Actions, and
• merging into main automatically triggers a database deploy to production through GitHub Actions.

Use pnpm db:push only when you explicitly need a manual push to the linked hosted dev project. The shared hosted dev environment is not auto-promoted, so manual pushes are intentional, not routine.

The CI workflow that handles hosted deploys is .github/workflows/deploy-dashboard.yml. It runs supabase db push --linked against the matching hosted environment first when supabase/migrations/** changed, then deploys apps/dashboard with the Vercel CLI. Automated migration pushes only run for dev and main, and are skipped when migration files did not change.

Feature branch pull requests can deploy dashboard previews through .github/workflows/preview-dashboard.yml, but those previews do not automatically push database migrations. If a feature branch includes database changes that need a working online database for review, manually push those migrations to the linked hosted dev project with pnpm db:push.

When The Remote History Doesn't Match

The first time you link to shared dev you'll pull a baseline:

pnpm db:pull -- remote_dev_baseline

This creates the baseline in supabase/migrations/. If the remote migration history does not match your local files, repair the linked history first and then run the pull again. Don't force-push migration history onto a shared environment to make the error go away — that erases traceability for everyone else.

Quick Command Reference

pnpm db:stop
pnpm db:pull -- <name>
pnpm db:reset
pnpm db:diff <name>
pnpm db:push
pnpm migration:new -- <name>
pnpm migration:up
pnpm db:types:dev
pnpm db:types:linked
pnpm db:types:local

See Commands for what each one does and when to reach for it.