db-migrator

When Invoked

1. Understand the schema change needed
2. Check existing schema and migrations
3. Generate migration SQL
4. Apply and verify

Pre-Migration Checklist

# Check existing schema
ls -la supabase/migrations/ | tail -10
# Check current tables (Supabase MCP)
# Review related migrations for naming patterns

Migration Standards

File Naming

supabase/migrations/YYYYMMDD_description.sql

Example: 20260208_add_user_profiles.sql

Required Elements for Every Table

-- 1. UUID primary key
CREATE TABLE table_name (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- 2. Your columns here
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  
  -- 3. Always include timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 4. Auto-update trigger for updated_at
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON table_name
  FOR EACH ROW EXECUTE FUNCTION moddatetime(updated_at);

-- 5. Indexes on foreign keys and frequently queried columns
CREATE INDEX idx_table_name_status ON table_name(status);

-- 6. RLS — MANDATORY on every table
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

-- 7. RLS policies
CREATE POLICY "Users can read own data" ON table_name
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own data" ON table_name
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own data" ON table_name
  FOR UPDATE USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

Column Type Guide

Data	Use	Don't Use
IDs	UUID	SERIAL, TEXT
Money	DECIMAL(10,2)	FLOAT, REAL
Timestamps	TIMESTAMPTZ	TIMESTAMP
Status/enum	TEXT + CHECK or PG ENUM	Unconstrained TEXT
JSON data	JSONB	JSON, TEXT
Boolean	BOOLEAN	INT, TEXT

ALTER TABLE Patterns

-- Add column (safe, non-breaking)
ALTER TABLE users ADD COLUMN bio TEXT;

-- Add column with default (safe)
ALTER TABLE users ADD COLUMN role TEXT NOT NULL DEFAULT 'user';

-- Add NOT NULL to existing (requires backfill first)
UPDATE users SET bio = '' WHERE bio IS NULL;
ALTER TABLE users ALTER COLUMN bio SET NOT NULL;

-- Rename column (breaking — update app code first)
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Add index (non-blocking)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);