| name | supabase-migrations |
| description | Use when creating database migrations or modifying schema |
Supabase: Migrations
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
pnpm sb:dev:new add_my_feature
pnpm sb:dev:push
pnpm sb:dev:types
pnpm dev
pnpm sb:dev:reset
pnpm sb:prod:push
Migration Template
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);
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())
));
Org-Scoped Table Checklist
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
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