Supabase: Migrations

Name: supabase-migrations
Rating: 92
Author: NTQ78

Guidelines for creating and managing database migrations.

⚠️ CRITICAL RULES

•NEVER manually edit database.types.ts - auto-generated
•ALWAYS run pnpm sb:dev:types after ANY schema change
•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

Method	Command	Best For
Direct SQL	`pnpm sb:dev:new my_feature`	New tables, functions, triggers
Studio UI Diff	`pnpm sb:dev:diff my_feature`	Column changes, visual design

Complete Workflow

bash

# 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

sql

-- ============================================
-- [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

Command	Purpose
`pnpm sb:dev:start`	Start local Supabase
`pnpm sb:dev:push`	Apply migrations (preserves data)
`pnpm sb:dev:reset`	Reset DB, re-run all migrations
`pnpm sb:dev:types`	Generate TypeScript types
`pnpm sb:dev:new <name>`	Create empty migration
`pnpm sb:dev:diff <name>`	Generate from UI changes
`pnpm sb:prod:push`	Push to production

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