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.

Current Tech Stack

• Migrations: Raw SQL files in src/db/migrations/
• Naming: XXX_description.sql (e.g., 001_initial_schema.sql)
• Registration: src/db/migrations/index.ts auto-discovers and runs .sql files
• Execution: Auto-run on startup in sorted order, tracked in migrations table
• Database: PostgreSQL (prod) / SQLite (dev)
• No ORM migrations: Raw SQL only (no ORM migration tools)

When This Skill Applies

• Creating database migrations
• Adding new tables
• Altering existing schemas
• Adding indexes or constraints
• Schema impact analysis
• Data migration planning

Stop-the-Line Conditions

FORBIDDEN Patterns

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

-- FORBIDDEN: Table without RLS consideration
CREATE TABLE user_data (...);
-- Must document RLS plan even if not yet enforced

-- FORBIDDEN: Missing index on foreign key / filter columns
CREATE TABLE planning_features (...);
-- Missing: CREATE INDEX idx_planning_features_session_id ON planning_features(session_id);

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

CORRECT Patterns

-- CORRECT: Complete migration with indexes in same file
-- File: src/db/migrations/008_add_audit_log.sql

CREATE TABLE IF NOT EXISTS audit_log (
  id SERIAL PRIMARY KEY,
  organization_id TEXT NOT NULL,
  action TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id TEXT,
  payload JSONB,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Index for query performance (MANDATORY for filter columns)
CREATE INDEX IF NOT EXISTS idx_audit_log_org_id ON audit_log(organization_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_created_at ON audit_log(created_at);

-- RLS (when enforced at DB level):
-- ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY audit_log_org_isolation ON audit_log
--   FOR ALL USING (organization_id = current_setting('app.current_org_id', true));