Supabase Migrations — Version Control for Your Database Schema

# Initialize Supabase project
supabase init

# Create a new migration
supabase migration new add_files_table

# This creates:
# supabase/migrations/<timestamp>_add_files_table.sql

# Edit the SQL file with your schema changes

-- supabase/migrations/<timestamp>_add_files_table.sql
-- Migration: Add files table

CREATE TABLE IF NOT EXISTS files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    size_bytes BIGINT NOT NULL DEFAULT 0,
    mime_type TEXT,
    storage_path TEXT,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_files_user_id ON files(user_id);
CREATE INDEX idx_files_status ON files(status);

ALTER TABLE files ENABLE ROW LEVEL SECURITY;

# Apply all pending migrations to local database
supabase migration up

# Apply to remote Supabase project
supabase db push

# Check migration status
supabase migration list

# View applied migrations
supabase db remote changes

# migration_workflow.py
# Migration workflow steps

def migration_workflow():
    steps = [
        "supabase init -- Initialize local Supabase project",
        "supabase migration new <name> -- Create migration file",
        "Write SQL migration in the generated file",
        "supabase migration up -- Apply to local DB",
        "supabase db push -- Apply to remote Supabase project",
        "supabase migration list -- Verify applied migrations",
    ]
    
    print("Migration Workflow:")
    for step in steps:
        print(f"  {step}")

migration_workflow()

-- 001_add_tables.sql
-- Initial schema creation

CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    user_id UUID REFERENCES auth.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

---

-- 002_add_description.sql
-- Adding a column (safe, no data loss)

ALTER TABLE projects ADD COLUMN description TEXT;

---

-- 003_rename_column.sql
-- Rename a column (use RENAME COLUMN)

ALTER TABLE projects RENAME COLUMN description TO summary;

---

-- 004_add_index.sql
-- Performance index

CREATE INDEX idx_projects_user_id ON projects(user_id);

---

-- 005_change_type.sql
-- Change column type (requires USING clause)

ALTER TABLE projects ALTER COLUMN status TYPE TEXT 
  USING status::TEXT;

# migration_patterns.py
# Common migration patterns

def describe_patterns():
    patterns = {
        "Add column": "ALTER TABLE table ADD COLUMN column type;",
        "Drop column": "ALTER TABLE table DROP COLUMN column;",
        "Rename column": "ALTER TABLE table RENAME COLUMN old TO new;",
        "Change type": "ALTER TABLE table ALTER COLUMN col TYPE newtype USING col::newtype;",
        "Add index": "CREATE INDEX name ON table(column);",
        "Add constraint": "ALTER TABLE table ADD CONSTRAINT name CHECK (condition);",
        "Enable RLS": "ALTER TABLE table ENABLE ROW LEVEL SECURITY;",
        "Create policy": "CREATE POLICY name ON table FOR SELECT USING (condition);",
    }
    
    print("Common Migration Patterns:")
    for pattern, sql in patterns.items():
        print(f"  {pattern:20s} | {sql}")

describe_patterns()

# migration_safety.py
# Migration safety checklist

def migration_safety_checklist():
    checks = [
        "Test migration on a local copy of production data",
        "Write DOWN migration for rollback capability",
        "Avoid long-running locks on large tables",
        "Use IF NOT EXISTS / IF EXISTS for idempotent migrations",
        "Do not modify data in the same migration as schema changes",
        "Include default values for new NOT NULL columns",
        "Run ANALYZE after bulk data changes",
    ]
    
    print("Migration Safety Checklist:")
    for check in checks:
        print(f"  [ ] {check}")

migration_safety_checklist()

def migration_plan():
    migrations = [
        "001_create_comments_table.sql -- Create table with id, user_id, file_id, content, created_at",
        "002_add_comments_indexes.sql -- Add indexes on user_id and file_id",
        "003_enable_comments_rls.sql -- Enable RLS on comments table",
        "004_create_comments_policies.sql -- Users can CRUD own comments",
        "005_backfill_existing_data.sql -- Optional: import legacy comments",
    ]
    
    print("Feature Migration Plan:")
    for m in migrations:
        print(f"  {m}")

migration_plan()