sqlalchemy-alembic-expert-best-practices-code-review

This skill is a curated set of SQLAlchemy ORM and Alembic migration best practices for building safe, performant database schemas. It solves a high-stakes problem: schema migrations that block reads and writes, cause table locks, or produce redundant and inefficient indexes in production databases (primarily PostgreSQL). It is meant to be consulted when writing, reviewing, or refactoring SQLAlchemy models, Alembic migrations, or query patterns.

The guidance is organized into eight rules across four priority tiers. Index management rules require creating indexes concurrently with autocommit blocks (postgresql_concurrently=True) and verifying that query patterns are actually backed by indexes. Constraint-safety rules split risky operations into non-blocking steps: create a unique index concurrently before attaching the unique constraint, add foreign keys and check constraints with NOT VALID first and validate them separately, and change column types through a multi-step add-backfill-swap-drop sequence instead of a locking ALTER. Optimization rules limit non-unique indexes to at most three columns and avoid indexes already covered by a composite index. Each rule file provides an impact level, a bad example, and a corrected implementation.

It targets backend and platform engineers, database-focused developers, and code reviewers who need reproducible, opinionated rules for zero-downtime migrations. This is a read-only, defensive code-review reference; while its examples contain SQL schema operations including column drops, they are illustrative migration best practices, not destructive automation, and there is no external network access or credential handling.