bug: alembic migration 029 fails on fresh-DB upgrade head (Enum create_type=False reuse) #516

New issue

Open

opened 2026-04-25 14:04:16 +00:00 by forgejo_admin · 0 comments

forgejo_admin commented

2026-04-25 14:04:16 +00:00

Owner

Type

Bug

Lineage

Discovered 2026-04-25 during dev-agent work on forgejo_admin/basketball-api#510 (migration 048). The C2 dev agent attempted alembic upgrade head on a fresh local DB to validate the round-trip and hit this pre-existing failure at migration 029. Filed per feedback_discovered_scope_always_tracked. Reproduces on main. Does NOT affect prod (already past 029); affects dev-loop only.

Repo

forgejo_admin/basketball-api

User Story

As an engineer
I want alembic upgrade head to succeed on a fresh local Postgres DB
So that I can validate new migrations end-to-end without manually stamping past broken migrations

What Broke

alembic/versions/029_add_schedule_tables.py references the division enum type twice with Enum(..., create_type=False) (lines ~34 and ~77). On a fresh DB upgrade, the second reference fails with type "division" already exists because the first reference (without explicit .create()) implicitly creates the type, then the second reference tries to create it again under create_type=False's "assume it exists" semantics — resulting in a collision.

Repro Steps

cd ~/basketball-api
# Drop + recreate basketball db
psql -c "DROP DATABASE IF EXISTS basketball; CREATE DATABASE basketball;"
alembic upgrade head
# Fails at: Running upgrade 028 -> 029, type "division" already exists

Expected Behavior

alembic upgrade head succeeds from an empty DB to current head (047+).

Environment

Branch: main @ current HEAD (verified 2026-04-25)
Postgres: same version as basketball-api prod CNPG cluster
SQLAlchemy: 2.0.48 (per project pyproject)
Alembic: per project lock file
Reproduces on every fresh DB upgrade-head; does NOT affect already-stamped DBs (prod is past 029)

Likely Fix

Either:

Make the first Enum reference in 029 explicit-create (.create(op.get_bind(), checkfirst=True)) and the second reference truly create_type=False
OR factor the enum creation into its own op.execute("CREATE TYPE ...") line and have both references use create_type=False

The right pattern matches whatever SQLAlchemy 2.0+ recommends for shared enums.

Acceptance Criteria

Fresh-DB alembic upgrade head runs to current head with zero errors
No regression on alembic downgrade base round-trip
Migration 029's intent (the schedule tables it creates) is preserved unchanged

Test Expectations

pytest tests/ continues to pass
Manual fresh-DB upgrade-head succeeds
Run command: psql -c "DROP DATABASE IF EXISTS basketball_test; CREATE DATABASE basketball_test;" && DATABASE_URL=...basketball_test alembic upgrade head

Constraints

Touch only 029_add_schedule_tables.py if at all possible
Do NOT renumber 029 — the rest of the alembic chain depends on down_revision = "029" in 030
Do NOT change the table shapes 029 creates — only the enum-creation mechanic

Checklist

PR opened
Fresh-DB upgrade head verified locally
No unrelated changes

forgejo_admin/basketball-api#491 — sibling pre-existing-CI-red ticket (different tests)
forgejo_admin/basketball-api#510 — surfaced this bug while validating migration 048
forgejo_admin/basketball-api#515 — the PR for #510, contains the QA agent's discovery comment

### Type Bug ### Lineage Discovered 2026-04-25 during dev-agent work on `forgejo_admin/basketball-api#510` (migration 048). The C2 dev agent attempted `alembic upgrade head` on a fresh local DB to validate the round-trip and hit this pre-existing failure at migration 029. Filed per `feedback_discovered_scope_always_tracked`. Reproduces on `main`. Does NOT affect prod (already past 029); affects dev-loop only. ### Repo `forgejo_admin/basketball-api` ### User Story As an engineer I want `alembic upgrade head` to succeed on a fresh local Postgres DB So that I can validate new migrations end-to-end without manually stamping past broken migrations ### What Broke `alembic/versions/029_add_schedule_tables.py` references the `division` enum type twice with `Enum(..., create_type=False)` (lines ~34 and ~77). On a fresh DB upgrade, the second reference fails with `type "division" already exists` because the first reference (without explicit `.create()`) implicitly creates the type, then the second reference tries to create it again under `create_type=False`'s "assume it exists" semantics — resulting in a collision. ### Repro Steps ```bash cd ~/basketball-api # Drop + recreate basketball db psql -c "DROP DATABASE IF EXISTS basketball; CREATE DATABASE basketball;" alembic upgrade head # Fails at: Running upgrade 028 -> 029, type "division" already exists ``` ### Expected Behavior `alembic upgrade head` succeeds from an empty DB to current head (`047`+). ### Environment - Branch: `main` @ current HEAD (verified 2026-04-25) - Postgres: same version as basketball-api prod CNPG cluster - SQLAlchemy: 2.0.48 (per project pyproject) - Alembic: per project lock file - Reproduces on every fresh DB upgrade-head; does NOT affect already-stamped DBs (prod is past 029) ### Likely Fix Either: - Make the first `Enum` reference in 029 explicit-create (`.create(op.get_bind(), checkfirst=True)`) and the second reference truly `create_type=False` - OR factor the enum creation into its own `op.execute("CREATE TYPE ...")` line and have both references use `create_type=False` The right pattern matches whatever SQLAlchemy 2.0+ recommends for shared enums. ### Acceptance Criteria - [ ] Fresh-DB `alembic upgrade head` runs to current head with zero errors - [ ] No regression on `alembic downgrade base` round-trip - [ ] Migration 029's intent (the schedule tables it creates) is preserved unchanged ### Test Expectations - [ ] `pytest tests/` continues to pass - [ ] Manual fresh-DB upgrade-head succeeds - Run command: `psql -c "DROP DATABASE IF EXISTS basketball_test; CREATE DATABASE basketball_test;" && DATABASE_URL=...basketball_test alembic upgrade head` ### Constraints - Touch only `029_add_schedule_tables.py` if at all possible - Do NOT renumber 029 — the rest of the alembic chain depends on `down_revision = "029"` in 030 - Do NOT change the table shapes 029 creates — only the enum-creation mechanic ### Checklist - [ ] PR opened - [ ] Fresh-DB upgrade head verified locally - [ ] No unrelated changes ### Related - `forgejo_admin/basketball-api#491` — sibling pre-existing-CI-red ticket (different tests) - `forgejo_admin/basketball-api#510` — surfaced this bug while validating migration 048 - `forgejo_admin/basketball-api#515` — the PR for #510, contains the QA agent's discovery comment