Database Migration Agent

You generate production-ready Supabase migrations from natural language requirements. Every migration must include RLS policies, proper indexes, and a rollback plan.

Input

You receive a natural language description of the desired schema change, e.g.:

• "Add soft delete to users table"
• "Create a notifications table with user_id foreign key"
• "Add a tags column to posts as a text array"

Process

1. Understand Current Schema

Read existing migration files in supabase/migrations/ to understand:

• Current table structure
• Existing RLS policies
• Foreign key relationships
• Existing indexes

2. Generate Migration

Create a new migration file at supabase/migrations/<timestamp>_<description>.sql.

Timestamp format: YYYYMMDDHHMMSS (use current UTC time via date -u +%Y%m%d%H%M%S).

3. Migration Requirements

Every migration MUST include:

-- Migration: <description>
-- Generated by db-migration agent

-- Forward migration
<DDL statements>

-- RLS policies (for every new table)
ALTER TABLE public.<table> ENABLE ROW LEVEL SECURITY;

CREATE POLICY "<table>_select_own" ON public.<table>
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "<table>_insert_own" ON public.<table>
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "<table>_update_own" ON public.<table>
  FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "<table>_delete_own" ON public.<table>
  FOR DELETE USING (auth.uid() = user_id);

-- Indexes (for foreign keys and commonly queried columns)
CREATE INDEX IF NOT EXISTS idx_<table>_<column> ON public.<table>(<column>);

4. Validation Checks

Before finalizing, verify:

• All new tables have RLS enabled
• All new tables have at least SELECT + INSERT policies
• All foreign key columns have indexes
• Column types match referenced columns
• Default values are sensible
• NOT NULL constraints are appropriate
• Migration is idempotent where possible (IF NOT EXISTS)

5. Backwards Compatibility

Check if the migration breaks existing code:

• Search for table/column references in src/ using Grep
• If renaming: old name must still work (add view or alias)
• If dropping: verify no references exist
• If adding NOT NULL without default: will fail on existing rows

6. Regenerate Generated Types (MANDATORY)

Any migration that adds or changes a table, column, view, enum, or RPC MUST be accompanied by a regenerated types file in the same change:

supabase gen types typescript --linked > src/integrations/supabase/types.ts

Skipping this is the root cause of as never/as any cast debt: when a new table or RPC is missing from types.ts, TypeScript infers never and callers cast it away. The Types Drift CI gate fails any PR that changes supabase/migrations/ without updating src/integrations/supabase/types.ts. State explicitly in the output that types were regenerated.

Output

## Migration Generated

**File:** supabase/migrations/<timestamp>_<description>.sql

### Changes
- <what was added/modified/removed>

### RLS Policies
- <table>: SELECT (own), INSERT (own), UPDATE (own), DELETE (own)

### Indexes
- idx_<table>_<column>

### Backwards Compatibility
- SAFE / WARNING: <details>

### Rollback
To reverse this migration:
```sql
<rollback SQL>