Supabase: Migrations

Guidelines for creating and managing database migrations.

⚠️ CRITICAL RULES

1. NEVER manually edit database.types.ts - auto-generated
2. ALWAYS run pnpm sb:dev:types after ANY schema change
3. ALWAYS test locally with pnpm sb:dev:push or pnpm sb:dev:reset before production

Migration Naming

Pattern: YYYYMMDDHHMMSS_descriptive_name.sql

Examples: 20251106183429_add_projects_table.sql

Two Methods

Complete Workflow

# 1. Create migration
pnpm sb:dev:new add_my_feature  # OR pnpm sb:dev:diff add_my_feature

# 2. Write SQL (see template below)

# 3. Apply locally
pnpm sb:dev:push

# 4. Generate TypeScript types
pnpm sb:dev:types

# 5. Test application
pnpm dev

# 6. Push to production (after full verification)
pnpm sb:dev:reset  # Verify from scratch first
pnpm sb:prod:push

Migration Template

-- ============================================
-- [FEATURE NAME]
-- ============================================

-- PHASE 1: CREATE TABLE
CREATE TABLE public.my_table (
    id TEXT PRIMARY KEY DEFAULT generate_id('pre'),
    organization_id TEXT NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_my_table_org ON public.my_table(organization_id);

-- PHASE 2: ENABLE RLS
ALTER TABLE public.my_table ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Organization members can view"
    ON public.my_table FOR SELECT TO authenticated
    USING (EXISTS (
        SELECT 1 FROM public.organization_members
        WHERE organization_members.organization_id = my_table.organization_id
        AND organization_members.user_id = (SELECT auth.uid())
    ));

-- PHASE 3: REALTIME (if org-scoped)
-- Add CASE in get_organization_id_for_change() + attach trigger

Org-Scoped Table Checklist

• Create table with proper schema
• Add indexes for foreign keys
• Enable RLS and create policies
• Add to realtime system (see supabase-schema-design)
• Generate types: pnpm sb:dev:types
• Add QueryKeys entry

Common Commands

When to Use Each

• sb:dev:push: Day-to-day development (preserves test data)
• sb:dev:reset: Before production deployment, corrupted state, full verification

Verification

cd spark/frontend/my-vite-app && supabase db lint

Fix 0003_auth_rls_initplan, 0006_multiple_permissive_policies, 0009_duplicate_index warnings before deploying.

See Also

• supabase-schema-design - Table design, realtime triggers
• supabase-common-workflows - Step-by-step recipes
• frontend-realtime-sync - Frontend realtime consumption