Execute Supabase major re-architecture and migration strategies with strangler fig pattern.
Use when migrating to or from Supabase, performing major version upgrades,
or re-platforming existing integrations to Supabase.
Trigger with phrases like "migrate supabase", "supabase migration",
"switch to supabase", "supabase replatform", "supabase upgrade major".
Supabase migrations are timestamped SQL files managed by the CLI that track schema changes across environments. This skill covers the full lifecycle — creating migrations, zero-downtime schema changes, batch backfills, versioning, rollback, and TypeScript type generation — using real CLI commands and createClient from @supabase/supabase-js.
When to use: Creating new database migrations, modifying production schemas without downtime, backfilling existing data after adding columns, managing migration history across dev/staging/production, rolling back failed migrations, or regenerating TypeScript types.
@supabase/supabase-js v2+ installed in your project
Local Supabase running: npx supabase start
Understanding of PostgreSQL DDL and transaction behavior
Instructions
Step 1: Create and Manage Migrations
Create each migration as a timestamped SQL file, write the DDL, test it against a local reset, then promote it through environments with db push:
npx supabase migration new add_profiles_table # create timestamped SQL file
npx supabase migration list # show applied/pending status
npx supabase db reset # apply + seed locally (destructive)
npx supabase gen types typescript --local > lib/database.types.ts
npx supabase link --project-ref "<ref>" && npx supabase db push # promote to remote
Write DDL that enables RLS, adds policies, indexes, and triggers in the same file so the schema is complete when applied. For the full worked migration (a profiles table with RLS policies, an email index, a signup trigger, and an updated_at trigger) plus the local-test and staging/production promotion commands, see creating migrations.
Step 2: Zero-Downtime Migration Patterns
Production schema changes must avoid locking tables. The core rules: adding a nullable column with a default is lock-free on Postgres 11+; build indexes with CREATE INDEX CONCURRENTLY in a -- supabase:disable-transaction migration; rename or retype a column in two phases (add new column, backfill, sync trigger, then drop the old one) rather than an in-place ALTER COLUMN. Example of the safe column-add:
-- Nullable column with a default does NOT lock the table in Postgres 11+
ALTER TABLE public.orders ADD COLUMN status text DEFAULT 'pending';
-- Then, in a separate -- supabase:disable-transaction migration:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON public.orders(status);
For the complete set — two-phase column rename with a sync trigger, safe type change via add-backfill-swap, and an SDK health-check that samples query latency during the migration — see zero-downtime patterns.
Step 3: Data Backfill, Versioning, and Rollback
See data backfill, versioning, and rollback for batch backfill patterns with the SDK, schema versioning across environments, three rollback strategies (compensating migration, repair, feature flags), and type regeneration after migrations.
Output
This skill produces:
Migration creation workflow — npx supabase migration new with descriptive SQL files and local testing
Type regeneration — supabase gen types typescript after every schema change
Migration promotion — db push workflow from local to staging to production
Error Handling
Error
Cause
Solution
migration has already been applied
Re-running existing migration
Use supabase migration list to check status; never modify applied migrations
cannot run inside a transaction block
CREATE INDEX CONCURRENTLY in transaction
Add -- supabase:disable-transaction comment at top of migration file
column does not exist after migration
Migration not applied or types stale
Run supabase db push then supabase gen types typescript
deadlock detected during backfill
Concurrent updates on same rows
Reduce batch size; add retry logic with exponential backoff
statement timeout on large table
Migration takes longer than timeout
Increase statement_timeout in migration: SET statement_timeout = '300s';
migration repair failed
Wrong version number
Use exact version from supabase migration list (the timestamp prefix)
db diff shows unexpected changes
Schema drift from manual SQL Editor changes
Run supabase db pull to capture manual changes as a migration
Type mismatch after migration
Generated types don't match new schema
Delete database.types.ts and regenerate from --local or --linked
Examples
The create-write-test-push workflow for a real column addition:
npx supabase migration new add_tags_to_projects
cat > supabase/migrations/20260322150000_add_tags_to_projects.sql << 'SQL'
ALTER TABLE public.projects ADD COLUMN tags text[] DEFAULT '{}';
CREATE INDEX idx_projects_tags ON public.projects USING GIN(tags);
SQL
npx supabase db reset
npx supabase gen types typescript --local > lib/database.types.ts
npx supabase link --project-ref <staging-ref> && npx supabase db push
See examples for three full worked examples: the complete local-to-production migration workflow, a batch backfill with progress tracking from the SDK, and a safe enum migration using a CHECK constraint instead of ALTER TYPE.
