scaffold-alembic-migration

/scaffold-alembic-migration

Generate a new Alembic database migration following the design-first workflow.

Usage

/scaffold-alembic-migration <description>

Example: /scaffold-alembic-migration add_user_table

Prerequisites

Before running, identify the project's Alembic configuration:

• Alembic config file location (typically alembic.ini under the shared services directory)
• SQLAlchemy models directory
• Pydantic schemas directory

Check the project's CLAUDE.md for the canonical paths.

Full Workflow

Step 1 — Design Doc Update (human reviews before code)

Update the design docs to reflect the planned change:

# Read current schema docs (adjust paths to your project)
cat docs/database/entity-model.md
cat docs/database/entity-erd.md

• Add/modify the entity definition in the schema docs
• Add/modify the Mermaid ERD
• Present the doc diff to the human — do NOT proceed until approved

Step 2 — Update SQLAlchemy Model (source of truth)

Locate the shared models directory for this project and update the relevant model file.

Rules:

• Use Mapped[] type annotations (SQLAlchemy 2.0+ async)
• Import and extend the project's Base and any shared mixins (e.g. TimestampMixin, UUIDMixin)
• Add proper foreign keys with ForeignKey() constraints
• Add relationship back-references where needed
• Export the new model in the models __init__.py

Step 3 — Update Pydantic Schemas

Locate the shared schemas directory and update the relevant schema file:

• Create/update request and response schemas matching the model
• Export in schemas/__init__.py

Step 4 — Generate Migration (autogenerate)

# Adjust the -c path to match the project's alembic.ini location
uv run alembic -c <path/to/alembic.ini> revision --autogenerate -m "$ARGUMENTS"

Step 5 — Self-Review Migration (AI blind spot check)

Read the generated migration file and check for these known autogenerate problems:

Problem	Detection	Fix
Column rename → shows as drop+add	op.drop_column + op.add_column for same data	Rewrite as op.alter_column(table, old_name, new_column_name=new_name)
Enum value addition	PostgreSQL enums need ALTER TYPE ... ADD VALUE	Add op.execute("ALTER TYPE enum_name ADD VALUE 'new_val'") manually
Enum value removal	Autogenerate ignores this entirely	Write manual migration — PostgreSQL can't remove enum values in-place
Data loss risk	Any op.drop_column or op.drop_table	Flag with # WARNING: DATA LOSS — requires human approval comment
Missing index	New FK column without index	Add op.create_index() for expected query patterns
pgvector column	Vector columns need special handling	Ensure from pgvector.sqlalchemy import Vector is imported
Nullable mismatch	Default nullable doesn't match model intent	Verify nullable=True/False matches Optional[] in model