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](#alembic-migrations) | 2 | CRITICAL | Data migrations, branch management |
| [Schema Design](#schema-design) | 3 | HIGH | Normalization, indexing strategies, NoSQL patterns |
| [Versioning](#versioning) | 2 | HIGH | Changelogs, schema drift detection |
| [Zero-Downtime Migration](#zero-downtime-migration) | 2 | CRITICAL | Expand-contract, pgroll, rollback monitoring |

| [Database Selection](#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 `env.py`, batched backfill recipes, migration hooks, environment-conditional migrations | https://alembic.sqlalchemy.org/en/latest/cookbook.html |
| Rollback and data-integrity test harnesses | `${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/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 `${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/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

```python
# 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')
```

```sql
-- 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 | `${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 |

Autogenerate setup is upstream. Our one deviation from Alembic's async `env.py`
template (the `in_greenlet()` guard) is in `${CLAUDE_PLUGIN_ROOT}/skills/database-patterns/references/ork-delta.md`.

## Schema Design

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

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

## Versioning

Database version control and change management across environments.

| Rule | File | Key Pattern |
|------|------|-------------|
| Changelog | `${CLAUDE_PLUGIN_ROOT