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 patternsork:testing-integration- Integration testing patterns including migration testingcaching- Cache layer design to complement database performanceork:performance- Performance optimization patterns