Database Migrations Skill

When This Skill Applies

Use this skill BEFORE any of these actions:

Creating a new migration file
Editing an existing migration file
Fixing a bug found in a migration
Responding to code review feedback on migrations

Critical Rule

NEVER edit a migration file that has been pushed to a remote branch.

Once pushed → CI applies to TEST DB → Editing does NOTHING on TEST
                                    → Creates TEST/PROD inconsistency

Mandatory Decision Tree

Before ANY migration file work, follow this decision tree:

Step 1: What are you doing?

A) Creating a NEW migration? → Go to "Creating New Migration" section

B) Editing an EXISTING migration? → Continue to Step 2

Step 2: Has this migration been pushed to remote?

Run this check:

git log --oneline origin/main..HEAD -- supabase/migrations/
git log origin/<branch> -- <migration-file>

Answer: NO, it's local only → You may edit, but verify again before pushing

Answer: YES, it has been pushed → STOP! Go to "Fixing a Pushed Migration" section

Answer: UNSURE → Assume YES. Go to "Fixing a Pushed Migration" section

Creating New Migration

Create the file:

touch supabase/migrations/$(date +%Y%m%d)_<description>.sql

For multiple migrations same day, use timestamp:

touch supabase/migrations/$(date +%Y%m%d%H%M%S)_<description>.sql

Use the template from REFERENCE.md
Test locally:
```
supabase db reset
npm run test:rls
```
Verify with local MCP:
```
mcp__supabase__execute_sql
```
Push to PR - CI will apply to TEST DB
Verify on TEST DB before merging:
```
mcp__supabase-test__execute_sql
```

Fixing a Pushed Migration

When you find a bug in an already-pushed migration:

DO NOT edit the original migration file

Create a NEW fix migration:

touch supabase/migrations/$(date +%Y%m%d%H%M%S)_fix_<original_name>.sql

Write the fix using idempotent patterns:
- CREATE OR REPLACE FUNCTION for functions
- ALTER TABLE ... ADD COLUMN IF NOT EXISTS for columns
- DROP ... IF EXISTS for removals
Apply fix to TEST DB directly (since new migration won't auto-run there):
```
mcp__supabase-test__execute_sql
```
Push the NEW migration file
Verify on TEST DB that fix works

Checklist (Create TodoWrite items for each)

Before completing migration work:

Confirmed no pushed migration files were edited
New migrations tested locally with supabase db reset
RLS policies tested with npm run test:rls (if applicable)
Changes verified on TEST DB with mcp__supabase-test__execute_sql
Ready for merge

Why This Matters

LOCAL ──push──► TEST DB (CI auto-applies) ──merge+approve──► PRODUCTION
                     │
                     └── Migration already applied here!
                         Editing the file does NOTHING.
                         PRODUCTION gets different code than TEST ran.

This creates:

Inconsistent environments
Bugs that work on TEST but fail on PROD (or vice versa)
Difficult-to-debug issues

Common Mistakes to Avoid

"It's just a small fix" → Still create a new migration
"CREATE OR REPLACE is idempotent" → True, but migration tracking isn't
"I'll just push the edit" → TEST won't re-run it
"Code review asked for changes" → Create new migration for the fix

Reference

See REFERENCE.md for:

Migration file template
Common SQL patterns
RLS policy examples

database-migrations