Migrations

Database migrations allow you to version control your database schema changes and apply them consistently across environments.

Common Commands Reference

The Golden Rule

Local migration files are the source of truth. Both your local database and remote (Supabase cloud) database should only have migrations that exist as files in apps/web/supabase/migrations/.

Understanding the Migration System

Supabase tracks which migrations have been applied in a schema_migrations table. Both your local database and remote database have their own copy of this table.

What causes sync issues:

• Applying migrations directly to remote without having the file locally
• Making schema changes in Supabase Dashboard without pulling them
• Deleting migration files after they've been applied
• Having different migration files locally vs what's recorded in remote

Standard Workflow (Safe Path)

1. Create a New Migration

# Option A: Create migration from schema diff
pnpm --filter web run supabase:db:diff -f my-feature-name

# Option B: Create empty migration and write SQL manually
pnpm --filter web supabase migrations new my-feature-name

2. Test Locally

# Apply the migration to local database
pnpm --filter web supabase migrations up

# Or reset completely (applies all migrations fresh)
pnpm supabase:web:reset

3. Generate Types

pnpm supabase:web:typegen

4. Commit and Push

git add .
git commit -m "feat: add my-feature schema"
git push

5. Apply to Remote (Production)

Only after the code is pushed:

# Link to your project if not already
npx supabase link --project-ref your-project-ref

# Push migrations to remote
npx supabase db push

Fixing Migration Sync Issues

Symptom: "Remote migration versions not found in local migrations directory"

This means remote has migrations recorded that don't exist as local files.

Fix:

cd apps/web

# 1. Mark the problematic migrations as reverted on remote
npx supabase migration repair --status reverted <migration_id_1> <migration_id_2>

# 2. Pull any schema changes from remote (if needed)
npx supabase db pull

# 3. Reset local database
pnpm supabase:web:reset

Symptom: "Migration history does not match local files"

Fix:

cd apps/web

# Check what remote thinks is applied
npx supabase migration list

# Mark your local migration as applied on remote (if it should be)
npx supabase migration repair --status applied <migration_id>

# Or mark remote migration as reverted (if it shouldn't exist)
npx supabase migration repair --status reverted <migration_id>

Nuclear Option: Full Reset

If things are really messed up:

cd apps/web

# 1. List all migrations on remote
npx supabase migration list

# 2. Revert ALL remote migrations that don't have local files
npx supabase migration repair --status reverted <id1> <id2> <id3>...

# 3. Mark all local migrations as applied on remote
npx supabase migration repair --status applied <local_id1> <local_id2>...

# 4. Reset local
pnpm supabase:web:reset

# 5. Regenerate types
pnpm supabase:web:typegen

If You Made Changes in Supabase Dashboard

If you modified the schema directly in the Supabase Dashboard:

cd apps/web

# Pull those changes as a new migration file
npx supabase db pull

# This creates a migration file with the diff
# Review it, then reset local to apply
pnpm supabase:web:reset

# Commit the new migration file
git add supabase/migrations/
git commit -m "feat: pull schema changes from remote"

Best Practices

1. Never edit migrations that have been applied to production - create a new migration instead

2. Always test locally first with pnpm supabase:web:reset before pushing to remote

3. Commit migration files immediately after creating them

4. Run typegen after every schema change to keep TypeScript types in sync

5. Don't make schema changes directly in Supabase Dashboard unless you immediately pull them

6. One feature = one migration - keep migrations focused and atomic

Example Migration

-- Create a new table
CREATE TABLE public.tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id UUID NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  completed BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Always enable RLS
ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;

-- Revoke default permissions
REVOKE ALL ON public.tasks FROM authenticated, service_role;

-- Grant specific permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.tasks TO authenticated;

-- Create RLS policies
CREATE POLICY "tasks_read" ON public.tasks FOR SELECT
  TO authenticated USING (
    account_id = auth.uid() OR
    public.has_role_on_account(account_id)
  );

CREATE POLICY "tasks_write" ON public.tasks FOR INSERT
  TO authenticated WITH CHECK (
    account_id = auth.uid() OR
    public.has_role_on_account(account_id)
  );