bug: non-idempotent division enum in migration 008 causes CrashLoopBackOff on deploy #85

New issue

Closed

opened 2026-03-15 01:05:06 +00:00 by forgejo_admin · 0 comments

forgejo_admin commented

2026-03-15 01:05:06 +00:00

Owner

Lineage

plan-2026-03-08-tryout-prep → Phase 10a (Team model + assignment endpoints)
Deployment blocker — migration 008 crashes on alembic upgrade head.

Repo

forgejo_admin/basketball-api

User Story

As a platform operator
I want migrations to run idempotently on every pod restart
So that new deployments don't CrashLoopBackOff

Context

basketball-api is in CrashLoopBackOff after CI pushed a new image. The Dockerfile runs alembic upgrade head before starting uvicorn. The DB is at revision 007. Migration 008 (008_add_team_model.py) needs to run for the first time to create the teams table.

The migration correctly uses postgresql.ENUM("boys", "girls", name="division", create_type=False) for the division column. However, alembic/env.py imports all models and sets target_metadata = Base.metadata. The Team model in models.py defines division as Enum(Division) (generic SQLAlchemy Enum with create_type=True default). When op.create_table("teams") executes, SQLAlchemy's table creation events fire using the metadata's enum type (which has create_type=True), overriding the migration's create_type=False. This issues CREATE TYPE division AS ENUM ('boys', 'girls') without checking first — but the type already exists from migration e09c9e678004.

Error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) type "division" already exists
[SQL: CREATE TYPE division AS ENUM ('boys', 'girls')]

File Targets

Files the agent should modify:

alembic/versions/008_add_team_model.py — replace op.create_table() with raw SQL via op.execute() to bypass SQLAlchemy's type creation event system. Use CREATE TABLE IF NOT EXISTS for the teams table. Handle agegroup enum with DO $$ BEGIN ... EXCEPTION WHEN duplicate_object THEN null; END $$ pattern. Keep the op.add_column for players.team_id and downgrade() as-is.

Files the agent should NOT touch:

src/basketball_api/models.py — the model is correct, the bug is in the migration
alembic/env.py — metadata import is correct for autogenerate
Any other migration files

Acceptance Criteria

alembic upgrade head succeeds from revision 007 without error
teams table created with correct columns: id, tenant_id, name, division (division enum), age_group (agegroup enum), coach_id, created_at
players.team_id FK column added
All existing tests pass (242 tests)
Pod starts without CrashLoopBackOff after image deploy

Test Expectations

Existing test suite passes: pytest (242 tests)
No new tests needed — this is a migration fix, not a feature change
Run command: pytest

Constraints

Use op.execute() with raw SQL for table creation — do NOT try to fix the SQLAlchemy metadata interaction
Keep downgrade() function unchanged
The agegroup enum creation should also use the idempotent DO $$ BEGIN ... EXCEPTION pattern
Keep indexes on tenant_id and coach_id (create with op.create_index or raw SQL)
DB is currently at revision 007 — migration must work from that state

Checklist

PR opened
Tests pass
No unrelated changes

project-westside-basketball — deployment blocker for basketball-api
Phase 10a was code-complete (PR #82 merged) but migration crashes on deploy

### Lineage `plan-2026-03-08-tryout-prep` → Phase 10a (Team model + assignment endpoints) Deployment blocker — migration 008 crashes on `alembic upgrade head`. ### Repo `forgejo_admin/basketball-api` ### User Story As a platform operator I want migrations to run idempotently on every pod restart So that new deployments don't CrashLoopBackOff ### Context basketball-api is in **CrashLoopBackOff** after CI pushed a new image. The Dockerfile runs `alembic upgrade head` before starting uvicorn. The DB is at revision `007`. Migration 008 (`008_add_team_model.py`) needs to run for the first time to create the `teams` table. The migration correctly uses `postgresql.ENUM("boys", "girls", name="division", create_type=False)` for the division column. However, `alembic/env.py` imports all models and sets `target_metadata = Base.metadata`. The `Team` model in `models.py` defines `division` as `Enum(Division)` (generic SQLAlchemy Enum with `create_type=True` default). When `op.create_table("teams")` executes, SQLAlchemy's table creation events fire using the **metadata's** enum type (which has `create_type=True`), overriding the migration's `create_type=False`. This issues `CREATE TYPE division AS ENUM ('boys', 'girls')` without checking first — but the type already exists from migration `e09c9e678004`. **Error:** ``` sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) type "division" already exists [SQL: CREATE TYPE division AS ENUM ('boys', 'girls')] ``` ### File Targets Files the agent should modify: - `alembic/versions/008_add_team_model.py` — replace `op.create_table()` with raw SQL via `op.execute()` to bypass SQLAlchemy's type creation event system. Use `CREATE TABLE IF NOT EXISTS` for the teams table. Handle `agegroup` enum with `DO $$ BEGIN ... EXCEPTION WHEN duplicate_object THEN null; END $$` pattern. Keep the `op.add_column` for `players.team_id` and `downgrade()` as-is. Files the agent should NOT touch: - `src/basketball_api/models.py` — the model is correct, the bug is in the migration - `alembic/env.py` — metadata import is correct for autogenerate - Any other migration files ### Acceptance Criteria - [ ] `alembic upgrade head` succeeds from revision 007 without error - [ ] `teams` table created with correct columns: id, tenant_id, name, division (division enum), age_group (agegroup enum), coach_id, created_at - [ ] `players.team_id` FK column added - [ ] All existing tests pass (242 tests) - [ ] Pod starts without CrashLoopBackOff after image deploy ### Test Expectations - [ ] Existing test suite passes: `pytest` (242 tests) - [ ] No new tests needed — this is a migration fix, not a feature change - Run command: `pytest` ### Constraints - Use `op.execute()` with raw SQL for table creation — do NOT try to fix the SQLAlchemy metadata interaction - Keep `downgrade()` function unchanged - The `agegroup` enum creation should also use the idempotent `DO $$ BEGIN ... EXCEPTION` pattern - Keep indexes on `tenant_id` and `coach_id` (create with `op.create_index` or raw SQL) - DB is currently at revision 007 — migration must work from that state ### Checklist - [ ] PR opened - [ ] Tests pass - [ ] No unrelated changes ### Related - `project-westside-basketball` — deployment blocker for basketball-api - Phase 10a was code-complete (PR #82 merged) but migration crashes on deploy