Database patterns

<!-- directive-density: intentional (teaches migration anti-patterns; NEVER markers describe real production-break conditions, not aspirational guidance) -->

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
-- PG18: prefer uuidv7() (time-ordered, better B-tree locality) over gen_random_uuid() (random v4)
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    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