database-patterns

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 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 env.py, batched backfill recipes, migration hooks, environment-conditional migrations	https://alembic.sqlalchemy.org/en/latest/cookbook.html
Rollback and data-integrity test harnesses	references/migration-testing.md
JSONB operators, indexing and storage tradeoffs	https://www.postgresql.org/docs/current/datatype-json.html (normal forms and the house denormalization call stay in rules/schema-normalization.md)
Full index-type reference and syntax (B-tree, GIN, partial, covering, CREATE INDEX CONCURRENTLY, REINDEX)	https://www.postgresql.org/docs/current/sql-createindex.html (the house subset we actually apply stays in rules/schema-indexing.md)
lock_timeout, statement_timeout, advisory locks during migration	https://www.postgresql.org/docs/current/runtime-config-client.html and rules/versioning-drift.md
Enum type changes	https://www.postgresql.org/docs/current/datatype-enum.html
Table partitioning	https://www.postgresql.org/docs/current/ddl-partitioning.html
Trigger functions	https://www.postgresql.org/docs/current/plpgsql-trigger.html
Foreign-key cascade semantics	https://www.postgresql.org/docs/current/ddl-constraints.html
Temporal and audit-trail tables, CDC change logs, stored-procedure and view versioning	https://www.postgresql.org/docs/18/sql-createtable.html (read references/ork-delta.md before assuming these give row history)
HNSW and vector index tuning (m, ef_construction, hnsw.ef_search)	https://github.com/pgvector/pgvector
Generic pre-deployment, backup and schema-review checklists	https://alembic.sqlalchemy.org/en/latest/tutorial.html
Async SQLAlchemy sessions, FastAPI wiring, connection pool tuning	ork:python-backend skill

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
Data Migration	rules/alembic-data-migration.md	Batch backfill, two-phase NOT NULL, zero-downtime
Branching	rules/alembic-branching.md	Feature branches, merge migrations, conflict resolution

Autogenerate setup is upstream. Our one deviation from Alembic's async env.py template (the in_greenlet() guard) is in references/ork-delta.md.

Schema Design

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

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

Versioning

Database version control and change management across environments.

Rule	File	Key Pattern
Changelog	rules/versioning-changelog.md	Schema version table, semantic versioning, audit trails
Drift Detection	rules/versioning-drift.md	Environment sync, checksum verification, migration locks

Rollback testing lives in references/migration-testing.md; the docstring convention for lossy downgrades is in references/ork-delta.md.

Database Selection

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

Rule	File	Key Pattern
Selection Guide	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
references/ork-delta.md	Our corrections and house conventions. Read this first
references/migration-testing.md	Upgrade/downgrade cycle and data-integrity test harnesses
references/postgres-vs-mongodb.md	Head-to-head comparison behind the PostgreSQL-first default
references/db-migration-paths.md	Cross-engine migration risk matrix
references/cost-comparison.md	Managed database cost analysis
references/storage-and-cms.md	Object storage and CMS selection
scripts/	Migration template, 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	rules/migration-zero-downtime.md	Expand phase, backfill, contract phase, pgroll automation
Rollback & Monitoring	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