alembic-migration-patterns

name	alembic-migration-patterns
description	Alembic migration workflow for SQLAlchemy model changes, emphasizing autogeneration only, date-prefixed migration filenames, env.py model imports, review of generated revisions, and validation with upgrade commands and integration tests.

Alembic Migration Patterns

Use this skill when a backend change affects database schema in this project.

When to Use

• adding or changing SQLAlchemy models that affect database schema
• generating a new Alembic revision from model changes
• reviewing or validating an autogenerated migration
• planning zero-downtime schema changes, backfills, or rollback paths

First Reads

Read these files before touching migrations:

• backend/alembic.ini
• backend/alembic/env.py
• backend/alembic/versions/20260317_112239_create_auth_users.py
• backend/packages/domains/core/src/ezra_core/database.py
• backend/packages/domains/test-utils/src/ezra_test_utils/database.py
• docs/plans/database-and-local-infra-foundation.md

Non-Negotiable Rules

1. Never hand-write a migration file from scratch.
2. Always change the SQLAlchemy models first, then generate the migration with Alembic.
3. Always run Alembic from backend/ via uv run.
4. Keep the generated date-prefixed filename. Do not rename it to a random or manual format.
5. Review the generated migration before considering it done.
6. Keep one logical schema change per migration whenever possible.
7. Never modify a migration after it has been deployed to a shared environment. Follow up with a new migration instead.

The correct default command is:

cd backend
uv run alembic revision --autogenerate -m "domain: short description"

Why This Repo Uses Autogenerate

This project already has the plumbing needed for autogenerated migrations:

• backend/alembic.ini sets a date-based file_template
• backend/alembic/env.py wires in Base.metadata
• domain models attach to the shared Base

That means the source of truth is the ORM model layer. The migration should reflect model changes, not replace them.

Standard Workflow

1. Change models first

Update the model definitions in the owning domain package.

Example:

• backend/packages/domains/authentication/src/ezra_authentication/models.py

Do not start by creating a blank revision file.

2. Make sure Alembic can see the models

backend/alembic/env.py must import every domain model package that should participate in autogeneration.

The repo already warns about this in the file itself:

# Import all domain models here so autogenerate can detect them.

If a new domain model is missing from env.py, Alembic will often generate an empty migration.

3. Generate the migration

Run from the backend/ directory:

cd backend
uv run alembic revision --autogenerate -m "auth: add user table"

Rules for the message:

• prefix with the domain or bounded context, like auth: or billing:
• keep it short and action-oriented
• describe the schema change, not the ticket number

4. Review the generated revision

Autogeneration is mandatory, but review is still required.

Check that:

• upgrade() contains only the intended schema changes
• downgrade() cleanly reverses them
• indexes, constraints, defaults, and nullability match the model changes
• there are no unrelated table drops or column rewrites

Use the existing auth migration as a shape reference:

• backend/alembic/versions/20260317_112239_create_auth_users.py

5. Validate against a real database

At minimum, verify the migration applies:

cd backend
uv run alembic upgrade head

If you need to validate rollback behavior locally, use:

cd backend
uv run alembic downgrade -1

If the change affects integration tests, the shared test fixtures will also exercise the migration path through:

• backend/packages/domains/test-utils/src/ezra_test_utils/database.py

6. Keep migrations small and focused

Prefer one logical schema change per migration.

Good examples:

• add organization_id to users
• create billing_invoices
• add index for events.created_at

Avoid combining unrelated changes into one revision unless they must ship together for correctness. Smaller migrations are easier to review, debug, roll back locally, and reason about in production.

Date-Prefixed Filenames

Migration filenames should be ordered chronologically. That is already configured in backend/alembic.ini:

file_template = %%(year)d%%(month).2d%%(day).2d_%%(hour).2d%%(minute).2d%%(second).2d_%%(slug)s

This yields filenames like:

20260317_112239_create_auth_users.py

Guidelines:

• let Alembic generate the filename
• keep the generated date prefix intact
• do not rename the file afterward unless there is a very specific repo-wide reason

Note:

• the filename should be date-prefixed even if Alembic's internal revision value is still hash-like

What "Never Hand-Write Migrations" Means

It means:

• do not create a new file under backend/alembic/versions/ by hand
• do not start from an empty revision template and manually write all operations
• do not treat the migration file as the primary artifact

If Alembic generates something incomplete or unsafe, fix the model definition or Alembic visibility first. Only make minimal edits to the autogenerated script when Alembic cannot express the final safe form on its own, and keep those edits small and reviewable.

Data Migration Guidance

Keep schema migrations and data migrations conceptually separate even when both are needed for one feature.

Preferred rollout:

1. Add the new nullable column or table structure.
2. Deploy application code that can read and write both old and new shapes if needed.
3. Backfill data in a dedicated migration or operational job.
4. Enforce stricter constraints only after the backfill is complete.

For large backfills:

• avoid one huge UPDATE over the whole table
• batch the work or move it to an operational script/job when appropriate
• be cautious with locks, table rewrites, and transaction size

Do not mix fragile, long-running data transforms into an otherwise simple schema revision unless you have a strong reason and a clear rollback story.

Zero-Downtime Pattern

When changing large production tables, prefer expand-contract thinking over destructive one-step changes.

Example for a concurrently created index:

def upgrade() -> None:
    op.execute(
        """
        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_org
        ON users (organization_id, created_at DESC)
        """
    )


def downgrade() -> None:
    op.execute("DROP INDEX CONCURRENTLY IF EXISTS idx_users_org")

Notes:

• CONCURRENTLY avoids the normal write-blocking behavior of a standard index build on large tables
• PostgreSQL does not allow CONCURRENTLY inside a transaction block
• if a migration truly needs this pattern, configure Alembic execution accordingly instead of forcing it into the default transactional flow
• keep such migrations especially small and well reviewed

Common Commands

Create a revision:

cd backend
uv run alembic revision --autogenerate -m "auth: add user table"

Apply all pending revisions:

cd backend
uv run alembic upgrade head

Check migration status:

cd backend
uv run alembic current
uv run alembic heads

Downgrade one revision locally:

cd backend
uv run alembic downgrade -1

Review Checklist

Before finishing a migration task, verify:

• the schema change started from model edits
• env.py imports the affected model package
• the revision file was generated by Alembic
• the filename has the date prefix
• the message is domain-prefixed and readable
• upgrade() and downgrade() are sensible
• the migration applies cleanly with uv run alembic upgrade head

Common Failure Modes

Empty migration

Usually means Alembic could not see the models.

Check:

• the model inherits from ezra_core.database.Base
• the model package is imported in backend/alembic/env.py
• you ran the command from backend/

Unexpected unrelated operations

Usually means metadata imports changed, models drifted, or the local database is out of sync with migration history.

Pause and inspect before committing the file. Do not accept a suspicious autogenerated diff blindly.

Wrong working directory

Running Alembic outside backend/ can break path resolution for the uv workspace and config discovery. Always run from backend/.

Decision Table

Situation	Preferred move
New table or column from model changes	Edit models first, then revision --autogenerate
Empty autogenerated migration	Check env.py imports and shared Base usage
Large table index	Consider zero-downtime/concurrent index pattern
Backfill or transform existing data	Use a dedicated backfill step or carefully scoped data migration
Need to change an already deployed migration	Create a new forward migration instead
Unrelated schema changes in one diff	Split into separate migrations when possible

Anti-Patterns

Do not:

• create revision files manually in backend/alembic/versions/
• write raw SQL first and retrofit models later
• edit an already deployed migration in place
• skip review because "Alembic generated it"
• rename generated files to non-date-prefixed names
• run migration commands from the repo root when the command expects backend/

Done Checklist

Before declaring the migration work done, verify:

• models and metadata imports are correct
• the revision was autogenerated
• the filename is date-prefixed
• the migration applies cleanly
• related integration tests still have a valid migration path