Database Migration Manager

You manage Alembic database migrations for the OPRE OPS project. Migrations must be run from the backend/ directory because that's where alembic.ini lives and where models is importable.

How to Determine What to Do

Interpret $ARGUMENTS to decide the action:

1. Create Migration: $ARGUMENTS starts with create

Generate a new migration from model changes.

Step 1: Verify the working directory and database

cd backend

# Check that the database is running
docker compose ps db --format json 2>/dev/null | jq -r '.Name + " " + .State' || echo "DB service not found — is Docker running?"

# Check current migration state
alembic current

If the database isn't running, tell the user:

docker compose up db -d

Step 2: Check for model changes

Look at what's changed to understand what the migration should contain:

# What model files have changed on this branch?
git diff main...HEAD --name-only -- models/

# Or if working off unstaged changes:
git diff --name-only -- models/

Read the changed model files to understand the schema changes.

Step 3: Generate the migration

Extract the message from $ARGUMENTS (everything after "create"):

cd backend
alembic revision --autogenerate -m "your migration message"

Migration files follow the naming pattern: YYYY_MM_DD_HHMM-<revision>_<slug>.py and are created in backend/alembic/versions/.

Step 4: Review the generated migration (critical!)

Auto-generated migrations can miss things or generate incorrect operations. Read the new file and check for:

• Dropped columns/tables: Alembic sometimes generates drops for things it can't detect properly. If a column was renamed rather than dropped-and-added, fix the migration to use op.alter_column() instead.
• Missing operations: Alembic can't auto-detect: table/column renames, changes to constraints names, or changes to enum values. These need manual additions.
• Data migrations: If the schema change requires data transformation (e.g., populating a new non-nullable column), add data migration logic between the schema changes.
• Downgrade function: Verify the downgrade() reverses the upgrade() correctly.
• Import statements: Ensure any custom types or enums are imported.

Present a summary to the user: "Here's what the migration does: [list of operations]. Does this look right?"

Step 5: Test the migration

cd backend

# Apply the migration
alembic upgrade head

# Verify it applied
alembic current

# Test the downgrade
alembic downgrade -1

# Re-apply to leave DB in upgraded state
alembic upgrade head

Report results: whether upgrade and downgrade both succeeded, and any errors encountered.

2. Review Migration: $ARGUMENTS is review or review <filename>

Review an existing migration file for correctness.

If a specific filename is provided, read that file. Otherwise, review the most recent migration:

ls -t backend/alembic/versions/*.py | head -1

Check for the same issues listed in Step 4 above. Also check:

• That the revision chain is correct (Revises: points to the expected parent)
• That the migration is idempotent where possible (e.g., if not guards for index creation)
• That the migration doesn't break existing data (e.g., adding a NOT NULL column without a default)

3. Rebase: $ARGUMENTS is rebase

Fixes a migration chain conflict: another PR merged a new migration to main after this branch's own migration was written, and both point at the same down_revision. Left alone, this creates two divergent heads the moment this branch merges — alembic upgrade head becomes ambiguous.

Step 1: Find what main added that this branch doesn't have

cd backend
git fetch origin main
git diff --name-status HEAD origin/main -- alembic/versions/

Files marked A exist on origin/main but not on this branch yet.

Step 2: Confirm the conflict

For each new file, check its down_revision:

git show origin/main:backend/alembic/versions/<new-file>.py | grep -m1 "^down_revision"

If it matches the down_revision this branch's own new migration already declares, they're siblings claiming the same parent. alembic heads will report more than one head once both exist together — the branch's own migration is almost always the one that needs to move, since the other side is already merged and shouldn't be rewritten.

Note this matched value somewhere durable (a scratch note, a task description) as <old-parent-revision> — it's needed again in Step 5, but Step 4 overwrites the only file in the tree that currently shows it, so it won't be recoverable from a fresh grep after that point.

If main added more than one new migration since this branch was cut, they may form their own chain (e.g. A → B, both new). Only the file whose down_revision equals the old parent will match this check — that's A, not the true new head B. Read every new file from Step 1's diff and follow each one's down_revision forward until you reach the file nothing else points to; that's the real target for Steps 3–4, not just the first match.

Step 3: Decide how to bring the new file(s) in

Read each new migration before touching anything. If it only calls op.*/sa.* (pure DDL or raw SQL, no imports of models or other application code), it's safe to cherry-pick on its own — a migration that only talks to the database doesn't care what else has or hasn't landed on this branch:

git show origin/main:backend/alembic/versions/<new-file>.py > backend/alembic/versions/<new-file>.py

If more than one new migration was found in Step 2, copy all of them in chain order (the one built on the old parent first, then whatever's built on that, and so on) — copying only the first link leaves the later ones missing and the conflict unresolved.

If any of them imports application code, don't cherry-pick a single file into an inconsistent state — merge main into the branch instead so the code it depends on comes along with it.

Step 4: Repoint this branch's migration onto the new head

Update this branch's own migration's down_revision (and the Revises: line in its docstring) to the true new head's revision id — the final file in the chain from Step 2/3, not necessarily the first new file found. Confirm with alembic heads — it should report exactly one head, and alembic history -r <old-parent-revision>:<this-branch's-revision> --verbose should show a single unbroken chain.

Step 5: Verify with a real downgrade/upgrade round-trip

Don't trust alembic current alone here — it only reports what the alembic_version table says, not what's actually in the schema. And if test data is seeded through a Docker image (e.g. data-import), that image has its own baked-in copy of the repo from whenever it was last built — if the new migration file from Step 3 didn't exist yet at build time, the container's own alembic upgrade head never touched it, even though the database ends up stamped at head. That produces a very convincing false failure on downgrade (a column reported as missing that should be there) that looks like a real migration bug but is actually just a stale image. Rebuild before testing:

docker compose down
docker compose --profile setup up db data-import --build -d

Then walk the chain for real and check the actual schema/data at each step (e.g. \d <table> in psql), not just the reported revision:

cd backend
alembic downgrade <old-parent-revision>
# confirm the new migration's columns/data are gone
alembic upgrade head
# confirm they're back

4. Upgrade: $ARGUMENTS is upgrade or upgrade <target>

Apply migrations:

cd backend

# Upgrade to head (default)
alembic upgrade head

# Or upgrade to a specific revision
alembic upgrade <target>

# Verify
alembic current

5. Downgrade: $ARGUMENTS is downgrade or downgrade <target>

Roll back migrations:

cd backend

# Roll back one migration (default)
alembic downgrade -1

# Or downgrade to a specific revision
alembic downgrade <target>

# Verify
alembic current

6. Status: $ARGUMENTS is status

Show the current migration state:

cd backend

# Current revision applied to the database
alembic current

# Check if there are unapplied migrations
alembic check 2>&1 || true

# Show the head revision
alembic heads

Report whether the database is up to date or has pending migrations.

7. History: $ARGUMENTS is history

Show recent migration history:

cd backend

# Show last 10 migrations
alembic history -r -10:current --verbose

8. Default: $ARGUMENTS is empty or unrecognized

Show help:

Database Migration Skill - Available Commands:

  /db-migrations create <message>   Generate a new migration from model changes
  /db-migrations review             Review the most recent migration for correctness
  /db-migrations review <file>      Review a specific migration file
  /db-migrations rebase             Fix a migration chain conflict against origin/main
  /db-migrations upgrade            Apply all pending migrations (alembic upgrade head)
  /db-migrations downgrade          Roll back one migration (alembic downgrade -1)
  /db-migrations status             Show current migration state and pending changes
  /db-migrations history            Show recent migration history

Prerequisites:
  - Docker database must be running: docker compose up db -d
  - Run from backend/ directory (handled automatically by this skill)
  - 67 existing migrations in backend/alembic/versions/

Key File Locations

• Alembic config: backend/alembic.ini
• Migration versions: backend/alembic/versions/ (67 migrations)
• Alembic env: backend/alembic/env.py
• Models: backend/models/ (shared across ops_api and data_tools)
• Schema reset scripts: backend/data_tools/scripts/initial_data.sh, backend/data_tools/scripts/upgrade_schema.sh

Common Pitfalls

• Wrong directory: Alembic must run from backend/, not backend/ops_api/ or the project root
• Renamed columns: Alembic generates a drop + add instead of a rename. Use op.alter_column() with new_column_name manually.
• Enum changes: Alembic can't auto-detect new enum values. Add op.execute("ALTER TYPE ... ADD VALUE ...") manually.
• Non-nullable columns: Adding a NOT NULL column to an existing table needs a server default or a two-step migration (add nullable, backfill, alter to non-null).
• History triggers: The app uses before_commit/after_flush for history tracking. Migrations that add new models should ensure the corresponding *_history table is also created if the model inherits from BaseModel.