database-patterns

Database design and migration patterns for Alembic migrations, schema design (SQL/NoSQL), and database versioning. Use when creating migrations, designing schemas, normalizing data, managing database versions, or handling schema drift.

الانتقال إلى التثبيت

المستودع: yonatangross/orchestkit
آخر نشاط في المصدر: ٥ أغسطس ٢٠٢٦ في ١٦:٤٠
لغة SKILL.md المكتشفة: الإنجليزية
النجوم: ٢١٩
التفرعات: ٢٠

خيارات التثبيت

يُحدَّد Prompt الذي يراجع المصدر أولًا بشكل افتراضي. يمكنك التبديل إلى أمر مباشر أو تنزيل نسخة محلية.

مراجعة ملفات المصدر

اقرأ SKILL.md وأي ملفات مرافقة يعرضها SkillsMP قبل أن تقرر التثبيت.

العودة إلى البحث العودة إلى التثبيت

name	database-patterns
license	MIT
compatibility	Claude Code 2.1.220+.
description	Database design and migration patterns for Alembic migrations, schema design (SQL/NoSQL), and database versioning. Use when creating migrations, designing schemas, normalizing data, managing database versions, or handling schema drift.
tags	["database","migrations","alembic","schema-design","versioning","postgresql","sql","nosql"]
context	fork
agent	database-engineer
version	2.0.0
author	OrchestKit
user-invocable	false
disable-model-invocation	false
complexity	medium
persuasion-type	reference
targets	[{"library":"sqlalchemy","version":">=2.0.0"}]
metadata	{"category":"document-asset-creation"}
allowed-tools	["Read","Glob","Grep","WebFetch","WebSearch"]
paths	["/migrations/","/models/","alembic.ini","*/schema"]
path_patterns	[".sql","/migrations/","/alembic/","/prisma/*"]

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	2	CRITICAL	Data migrations, branch management
Schema Design	3	HIGH	Normalization, indexing strategies, NoSQL patterns
Versioning	2	HIGH	Changelogs, 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: 10 rules across 5 categories

This skill is a wrap around Alembic and PostgreSQL, not a replacement for their docs. Read ${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/references/ork-delta.md first: it holds the version floors, corrections and house conventions that upstream does not carry. Everything in the table below was removed on purpose.

Upstream coverage (do not restate)

These topics are vendor documentation. Fetch them from the source instead of re-teaching them here.

Topic	First-party source
Alembic autogenerate, async `env.py` template, `revision`/`upgrade`/`downgrade`/`history` CLI	https://alembic.sqlalchemy.org/en/latest/autogenerate.html (our one correction to the async template is in `references/ork-delta.md`)
Migration branches, merge revisions, tuple `down_revision`, branch labels	https://alembic.sqlalchemy.org/en/latest/branches.html
Multi-database , batched backfill recipes, migration hooks, environment-conditional migrations

env.py

# 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);

Rule	File	Key Pattern
Data Migration	`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/rules/alembic-data-migration.md`	Batch backfill, two-phase NOT NULL, zero-downtime
Branching	`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/rules/alembic-branching.md`	Feature branches, merge migrations, conflict resolution

Rule	File	Key Pattern
Normalization	`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/rules/schema-normalization.md`	1NF-3NF, when to denormalize, JSON vs normalized
Indexing	`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/rules/schema-indexing.md`	B-tree, GIN, HNSW, partial/covering indexes
NoSQL Patterns	`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/rules/schema-nosql.md`	Embed vs reference, document design, sharding

Rule	File	Key Pattern
Changelog	`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/rules/versioning-changelog.md`	Schema version table, semantic versioning, audit trails
Drift Detection	`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/rules/versioning-drift.md`	Environment sync, checksum verification, migration locks

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

# 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)

Resource	Description
`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/references/ork-delta.md`	Our corrections and house conventions. Read this first
`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/references/migration-testing.md`	Upgrade/downgrade cycle and data-integrity test harnesses
`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/references/postgres-vs-mongodb.md`	Head-to-head comparison behind the PostgreSQL-first default
`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/references/db-migration-paths.md`	Cross-engine migration risk matrix
`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/references/cost-comparison.md`	Managed database cost analysis
`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/references/storage-and-cms.md`	Object storage and CMS selection
`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/scripts`	Migration template, model change detector

Rule	File	Key Pattern
Expand-Contract	`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/rules/migration-zero-downtime.md`	Expand phase, backfill, contract phase, pgroll automation
Rollback & Monitoring	`${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/rules/migration-rollback.md`	pgroll rollback, lock monitoring, replication lag, backfill progress