Migration Patterns Skill

Purpose

Guide database migration creation with mandatory RLS policies, following security-first architecture and approval workflow.

When This Skill Applies

• Creating database migrations
• Adding new tables (all tables need RLS)
• Updating Prisma schema
• Adding GRANT statements
• Schema impact analysis
• Data migration planning

Stop-the-Line Conditions

FORBIDDEN Patterns

sql
-- FORBIDDEN: RLS policies in separate file
-- RLS MUST be in the same migration.sql file as the table creation

-- FORBIDDEN: Table without RLS
CREATE TABLE user_data (...);
-- Missing: ALTER TABLE user_data ENABLE ROW LEVEL SECURITY;

-- FORBIDDEN: Resolve applied migrations
npx prisma migrate resolve --applied "migration_name"
-- This bypasses migration verification

-- FORBIDDEN: Missing user_id index
CREATE TABLE payments (...);
-- Missing: CREATE INDEX idx_payments_user_id ON payments(user_id);

-- FORBIDDEN: Schema changes without ARCHitect approval
-- All migrations require approval before PR

CORRECT Patterns

sql
-- CORRECT: Complete migration with RLS in same file
CREATE TABLE user_data (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id TEXT NOT NULL,
  data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS (SAME FILE - MANDATORY)
ALTER TABLE user_data ENABLE ROW LEVEL SECURITY;

-- User policy
CREATE POLICY user_data_user_select ON user_data
  FOR SELECT TO {{PROJECT}}_app_user
  USING (user_id = current_setting('app.current_user_id', true));

-- Index for RLS performance (MANDATORY)
CREATE INDEX idx_user_data_user_id ON user_data(user_id);

-- Grant permissions
GRANT SELECT, INSERT, UPDATE ON user_data TO {{PROJECT}}_app_user;

Migration Workflow (MANDATORY)

Step 1: Get ARCHitect Approval

Before ANY schema change:

text
1. Document proposed changes
2. Get ARCHitect approval (create issue or discussion)
3. Only proceed after explicit approval

Step 2: Create Migration

bash
# Generate migration
npx prisma migrate dev --name descriptive_name

# Verify migration file created
ls prisma/migrations/

Step 3: Add RLS to Migration

Edit the generated migration to include:

• ] ALTER TABLE ... ENABLE ROW LEVEL SECURITY
• ] User SELECT policy
• ] User INSERT policy (if applicable)
• ] User UPDATE policy (if applicable)
• ] Admin policies (if needed)
• ] System policies (for background jobs)
• ] Index on user_id column
• ] GRANT statements

Step 4: Verify Locally

bash
# Test migration
DATABASE_URL="..." npx prisma migrate dev

# Verify RLS is enabled
psql -c "SELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public';"

Step 5: Update Documentation

After successful migration:

• ] Update docs/database/DATA_DICTIONARY.md (MANDATORY)
• ] Update RLS policy catalog if new policies added
• ] Document in Linear ticket

RLS Policy Templates

User Read Policy

sql
CREATE POLICY {table}_user_select ON {table}
  FOR SELECT TO {{PROJECT}}_app_user
  USING (user_id = current_setting('app.current_user_id', true));

User Write Policy

sql
CREATE POLICY {table}_user_insert ON {table}
  FOR INSERT TO {{PROJECT}}_app_user
  WITH CHECK (user_id = current_setting('app.current_user_id', true));

Admin Policy

sql
CREATE POLICY {table}_admin_all ON {table}
  FOR ALL TO {{PROJECT}}_app_user
  USING (current_setting('app.user_role', true) = 'admin');

System Policy (Background Jobs)

sql
CREATE POLICY {table}_system_all ON {table}
  FOR ALL TO {{PROJECT}}_app_user
  USING (current_setting('app.context_type', true) = 'system');

Migration Checklist

Before PR:

• ] ARCHitect approval obtained
• ] RLS policies in same migration file
• ] User policies created
• ] user_id index created
• ] GRANT statements added
• ] Local migration test passed
• ] DATA_DICTIONARY.md updated
• ] Evidence attached to Linear

PROD Migration Requirements

For production migrations:

• ] Human operator must be present (MANDATORY)
• ] Backup taken before migration
• ] Rollback plan documented
• ] Post-migration validation steps defined
• ] Data integrity checks planned

Reference

• Migration SOP: docs/database/RLS_DATABASE_MIGRATION_SOP.md (MANDATORY)
• Data Dictionary: docs/database/DATA_DICTIONARY.md (update after changes)
• RLS Implementation: docs/database/RLS_IMPLEMENTATION_GUIDE.md
• RLS Policies: docs/database/RLS_POLICY_CATALOG.md
• Security First: docs/guides/SECURITY_FIRST_ARCHITECTURE.md