---
name: db-migrator
description: Supabase/Prisma migrations, RLS policies, indexes, and schema changes. Use when user mentions "migration", "schema change", "new table", or "database".
---

## 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

```bash
# 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

```sql
-- 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

```sql
-- 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);
```

## Post-Migration

1. **Verify** — Query the table to confirm schema
2. **Update types** — `supabase gen types typescript --local > types/database.types.ts`
3. **Test RLS** — Query as different user contexts
4. **Update app code** — Ensure all queries match new schema

## Output Format

```
## Migration: [description]

### Changes
- [What's being added/modified/removed]

### SQL
[Complete migration file]

### Verification
[Queries to verify the migration worked]

### App Code Updates Needed
- [Files that need updating]
```