Skip to main content

​
Overview

Database migrations live in supabase/migrations/ and are applied sequentially to evolve the PostgreSQL schema. Each migration is a SQL file with a timestamp prefix.

​
File Naming Convention

YYYYMMDDHHMMSS_description.sql
Examples: 
20250315143000_billing_infrastructure.sql
20250401092000_sync_control_maturity.sql
20250410160000_integration_sync_cron.sql
The timestamp prefix determines execution order. Always use the current UTC timestamp when creating a new migration to avoid ordering conflicts with other developers.

​
Creating Migrations

​
Generate via CLI

supabase migration new my_description
This creates an empty file in supabase/migrations/ with the correct timestamp. Edit the file to add your SQL.

​
Manual Creation

Create a file directly: 
touch supabase/migrations/$(date -u +%Y%m%d%H%M%S)_add_tenant_settings.sql

​
Migration Template

-- Migration: add_tenant_settings
-- Description: Adds settings JSONB column to tenants table

-- Up
ALTER TABLE tenants
ADD COLUMN IF NOT EXISTS settings jsonb DEFAULT '{}';

-- Update RLS policies if needed
-- Always use IF NOT EXISTS / IF EXISTS for idempotency
Make migrations idempotent where possible using IF NOT EXISTS, IF EXISTS, and CREATE OR REPLACE. This prevents failures if a migration is accidentally run twice.

​
Applying Migrations

Push all pending migrations to the linked Supabase project: 
supabase db push
This compares local migration files against the supabase_migrations.schema_migrations table and applies any new files in order.

​
Via SQL Editor (Production Hotfixes)

For urgent production fixes, you can run SQL directly in the Supabase Dashboard SQL Editor. After applying:
  1. Create a matching migration file locally
  2. Insert the migration record manually so db push does not re-apply it:
INSERT INTO supabase_migrations.schema_migrations (version, name)
VALUES ('20250405120000', 'hotfix_description');
Always create a local migration file for any SQL run directly in production. Failure to do so causes migration history mismatches that break future db push operations.

​
Migration History Mismatch

If supabase db push fails with a migration history mismatch:

​
Symptom

Error: migration version mismatch. Expected 20250401092000 but found 20250315143000

​
Fix

1

Check remote migration history

SELECT version, name, statements_applied_at
FROM supabase_migrations.schema_migrations
ORDER BY version;
2

Identify the gap

Compare remote history with local supabase/migrations/ files.
3

Repair the history

If a migration was applied manually, insert the record:
INSERT INTO supabase_migrations.schema_migrations (version, name)
VALUES ('20250401092000', 'sync_control_maturity');
4

Retry push

supabase db push

​
Key Migrations

Notable migrations in the history:
MigrationDescription
billing_infrastructureStripe billing tables, webhook event log, subscription management
sync_control_maturityTrigger that recalculates control maturity when evidence is validated
integration_sync_cronpg_cron jobs for nightly integration syncs and risk recompute
unified_controls_seedSeeds the 270+ unified controls and framework mappings
rls_policies_v2Updated RLS policies for partner-through-tenant access
evidence_pipelineEvidence tables, validation workflow, certificate generation

​
Best Practices

Each migration should address a single change. Do not combine unrelated schema changes. This makes rollbacks and debugging easier.
While Supabase does not have automatic rollback, document the undo SQL in comments for manual rollback if needed:
-- DOWN: ALTER TABLE tenants DROP COLUMN IF EXISTS settings;
Use supabase start to run migrations against a local database before applying to staging or production.
After applying a migration, regenerate TypeScript types:
supabase gen types typescript --project-id hcyyegiialkkjcdxpfat > packages/supabase/src/database.types.ts
If you add a new table, include RLS policies in the same migration. A table without RLS is accessible to any authenticated user.