supabase-migration-deep-dive

name	supabase-migration-deep-dive
description	Database migration patterns with the Supabase CLI: npx supabase migration new, zero-downtime migrations, data backfill strategies, schema versioning, rollback strategies, and TypeScript type generation. Use when creating database migrations, performing zero-downtime schema changes, backfilling data in production, managing schema versions, or planning rollback strategies. Trigger with "supabase migration", "supabase schema change", "supabase zero downtime", "supabase rollback", "supabase db push", "supabase migration new".
allowed-tools	Read, Write, Edit, Bash(npx supabase:*), Bash(supabase:*), Bash(psql:*), Grep, Glob
version	1.53.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","supabase","migration","database","schema","zero-downtime","rollback"]
compatibility	Designed for Claude Code

Supabase Migration Deep Dive

Overview

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.

Prerequisites

• Supabase CLI installed: npm install -g supabase or npx supabase --version
• @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 in a 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 . Example of the safe column-add: