Database Patterns

Comprehensive patterns for database migrations, schema design, and version management. Each category has individual rule files in rules/ loaded on-demand.

Quick Reference

Category Rules Impact When to Use

Alembic Migrations 3 CRITICAL Autogenerate, data migrations, branch management

Schema Design 3 HIGH Normalization, indexing strategies, NoSQL patterns

Versioning 3 HIGH Changelogs, rollback plans, schema drift detection

Zero-Downtime Migration 2 CRITICAL Expand-contract, pgroll, rollback monitoring

| Database Selection | 1 | HIGH | Choosing the right database, PostgreSQL vs MongoDB, cost analysis |

Total: 12 rules across 5 categories

Quick Start

Alembic: Auto-generate migration from model changes

alembic revision --autogenerate -m "add user preferences"

def upgrade() -> None: op.add_column('users', sa.Column('org_id', UUID(as_uuid=True), nullable=True)) op.execute("UPDATE users SET org_id = 'default-org-uuid' WHERE org_id IS NULL")

def downgrade() -> None: op.drop_column('users', 'org_id')

-- Schema: Normalization to 3NF with proper indexing CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), customer_id UUID NOT NULL REFERENCES customers(id), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Alembic Migrations

Migration management with Alembic for SQLAlchemy 2.0 async applications.

Rule File Key Pattern

Autogenerate ${CLAUDE_SKILL_DIR}/rules/alembic-autogenerate.md

Auto-generate from models, async env.py, review workflow

Data Migration ${CLAUDE_SKILL_DIR}/rules/alembic-data-migration.md

Batch backfill, two-phase NOT NULL, zero-downtime

Branching ${CLAUDE_SKILL_DIR}/rules/alembic-branching.md

Feature branches, merge migrations, conflict resolution

Schema Design

SQL and NoSQL schema design with normalization, indexing, and constraint patterns.

Rule File Key Pattern

Normalization ${CLAUDE_SKILL_DIR}/rules/schema-normalization.md

1NF-3NF, when to denormalize, JSON vs normalized

Indexing ${CLAUDE_SKILL_DIR}/rules/schema-indexing.md

B-tree, GIN, HNSW, partial/covering indexes

NoSQL Patterns ${CLAUDE_SKILL_DIR}/rules/schema-nosql.md

Embed vs reference, document design, sharding

Versioning

Database version control and change management across environments.

Rule File Key Pattern

Changelog ${CLAUDE_SKILL_DIR}/rules/versioning-changelog.md

Schema version table, semantic versioning, audit trails

Rollback ${CLAUDE_SKILL_DIR}/rules/versioning-rollback.md

Rollback testing, destructive rollback docs, CI verification

Drift Detection ${CLAUDE_SKILL_DIR}/rules/versioning-drift.md

Environment sync, checksum verification, migration locks

Database Selection

Decision frameworks for choosing the right database. Default: PostgreSQL.

Rule File Key Pattern

Selection Guide ${CLAUDE_SKILL_DIR}/rules/db-selection.md

PostgreSQL-first, tier-based matrix, anti-patterns

Key Decisions

Decision Recommendation Rationale

Async dialect postgresql+asyncpg

Native async support for SQLAlchemy 2.0

NOT NULL column Two-phase: nullable first, then alter Avoids locking, backward compatible

Large table index CREATE INDEX CONCURRENTLY

Zero-downtime, no table locks

Normalization target 3NF for OLTP Reduces redundancy while maintaining query performance

Primary key strategy UUID for distributed, INT for single-DB Context-appropriate key generation

Soft deletes deleted_at timestamp column Preserves audit trail, enables recovery

Migration granularity One logical change per file Easier rollback and debugging

Production deployment Generate SQL, review, then apply Never auto-run in production

Anti-Patterns (FORBIDDEN)

NEVER: Add NOT NULL without default or two-phase approach

op.add_column('users', sa.Column('org_id', UUID, nullable=False)) # LOCKS TABLE!

NEVER: Use blocking index creation on large tables

op.create_index('idx_large', 'big_table', ['col']) # Use CONCURRENTLY

NEVER: Skip downgrade implementation

def downgrade(): pass # WRONG - implement proper rollback

NEVER: Modify migration after deployment - create new migration instead

NEVER: Run migrations automatically in production

Use: alembic upgrade head --sql > review.sql

NEVER: Run CONCURRENTLY inside transaction

op.execute("BEGIN; CREATE INDEX CONCURRENTLY ...; COMMIT;") # FAILS

NEVER: Delete migration history

command.stamp(alembic_config, "head") # Loses history

NEVER: Skip environments (Always: local -> CI -> staging -> production)

Detailed Documentation

Resource Description

${CLAUDE_SKILL_DIR}/references/

Advanced patterns: Alembic, normalization, migration, audit, environment, versioning

${CLAUDE_SKILL_DIR}/checklists/

Migration deployment and schema design checklists

${CLAUDE_SKILL_DIR}/examples/

Complete migration examples, schema examples

${CLAUDE_SKILL_DIR}/scripts/

Migration templates, model change detector

Zero-Downtime Migration

Safe database schema changes without downtime using expand-contract pattern and online schema changes.

Rule File Key Pattern

Expand-Contract ${CLAUDE_SKILL_DIR}/rules/migration-zero-downtime.md

Expand phase, backfill, contract phase, pgroll automation

Rollback & Monitoring ${CLAUDE_SKILL_DIR}/rules/migration-rollback.md

pgroll rollback, lock monitoring, replication lag, backfill progress

Related Skills

• sqlalchemy-2-async

• Async SQLAlchemy session patterns

• ork:testing-integration

• Integration testing patterns including migration testing

• caching

• Cache layer design to complement database performance

• ork:performance

• Performance optimization patterns