migration-patterns

Migration Patterns Skill

TEMPLATE: This skill uses {{PLACEHOLDER}} tokens. Replace with your project values before use.

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 ORM schema
• Adding GRANT statements
• Schema impact analysis
• Data migration planning

Stop-the-Line Conditions

FORBIDDEN Patterns

-- FORBIDDEN: RLS policies in separate file
-- RLS MUST be in the same migration 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 (bypasses verification)
-- npx prisma migrate resolve --applied "migration_name"
-- alembic stamp head

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

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

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