db

name	db
description	Database migration workflow. Guides you through creating migrations, testing them, and keeping docs current. Use when creating tables, altering schemas, adding indexes, or any database change.
user-invocable	true
argument-hint	[new table | alter table | describe change]

Database Migration Workflow

You are making a database schema change. This skill ensures every migration follows conventions, tests cleanly, and docs stay current.

Step 0: Load Context

Read these before doing anything:

1. docs/architecture/DATABASE-SCHEMA.md — Source of truth for all tables, conventions, and the ER diagram. Read in full.
2. docs/architecture/DATABASE-MIGRATIONS.md — Migration workflow, local setup, and file format.

Step 1: Understand the Change

Classify what you're doing:

Type	Examples
New table	CREATE TABLE, new domain entity
Alter table	ADD COLUMN, ALTER COLUMN, DROP COLUMN
New index	Performance optimization, new query pattern
New FK	Linking existing tables

State clearly:

• What table(s) are affected?
• What's the business reason?
• Does it reference the user table? (If yes: CASCADE + index required.)

Step 2: Pre-flight Checks

• Does the table/column already exist?
• Naming conventions followed? (snake_case, TIMESTAMPTZ, proper index names)
• No conflicting migrations in the queue?

Step 3: Write the Migration

Create the file and write both up and down directions:

-- Up Migration
CREATE TABLE IF NOT EXISTS ...

-- Down Migration
DROP TABLE IF EXISTS ...

Rules:

• Both directions required
• Use IF NOT EXISTS / IF EXISTS for safety
• Down must cleanly reverse up

Step 4: Test

# Apply
pnpm run db:up
# Rollback
pnpm run db:down
# Re-apply (proves round-trip)
pnpm run db:up
# Tests
pnpm test
# Build
pnpm run build

Step 5: Update Documentation (MANDATORY)

• Update schema docs with new tables, columns, relationships
• Update ER diagram if one exists
• Add migration to the log
• Update CLAUDE.md if new env vars or patterns introduced
• Check off ticket ACs if working from a backlog ticket