Fix AgeGroup enum Python↔Postgres mismatch (names lowercase, values uppercase) #446

New issue

Open

opened 2026-04-11 20:19:24 +00:00 by forgejo_admin · 0 comments

forgejo_admin commented

2026-04-11 20:19:24 +00:00

Owner

Type

Bug

Lineage

Standalone — latent bug discovered 2026-04-10/11 during basketball-api#425 end-to-end testing. When Jacelyn's tier-change mint tried to move her to the newly-created 16U Local Queens team (team id 12), the endpoint 500'd with LookupError: 'U16' is not among the defined enum values. Enum name: agegroup.

Repo

forgejo_admin/basketball-api

What Broke

The AgeGroup Python enum in src/basketball_api/models.py:170 defines:

class AgeGroup(enum.Enum):
    u8 = "U8"
    u10 = "U10"
    u12 = "U12"
    u14 = "U14"
    u16 = "U16"
    u18 = "U18"

Enum NAMES are lowercase (u16) but enum VALUES are uppercase (U16). The Postgres agegroup enum type only accepts {U8, U10, U12, U14, U16, U18} (uppercase — the values).

SQLAlchemy is configured to read enums by NAME. So when SQLAlchemy reads a row with age_group = 'U16' (the actual stored value matching Postgres enum), it tries to find a Python enum member NAMED U16 — which doesn't exist. Only u16 (lowercase) exists. Raises LookupError.

Why it didn't surface before 2026-04-10: All 7 pre-existing teams have age_group = NULL. None of the pre-existing migrations ever populated the column. Migration 040 (040_create_16u_local_queens_team.py) was the first migration to insert a non-NULL value, and it inserted 'U16'. Next time a read hit team 12, the LookupError fired.

Current workaround: Ava ran UPDATE teams SET age_group = NULL WHERE id = 12 directly on prod to match the other 7 teams. The mismatch is still in the model, just not being exercised.

Repro Steps

On a fresh clone of basketball-api main (commit 9598c4d or later), read src/basketball_api/models.py:170 and confirm AgeGroup.u16 = "U16"
In a Python shell: from basketball_api.models import AgeGroup; AgeGroup('U16') — succeeds (by VALUE lookup)
In a Python shell: AgeGroup['U16'] — FAILS with KeyError (by NAME lookup)
SQLAlchemy's default Enum type stores by name and reads by name, which is the bug surface

Expected Behavior

The Python enum and the Postgres enum should agree on casing. Two possible fixes:

Option A — lowercase everywhere: change the Python enum values to lowercase (u16 = "u16") and ALTER the Postgres enum type to lowercase. Requires a migration that renames the enum values. Clean, but touches existing data if any teams ever had uppercase values stored (none currently, so safe).

Option B — uppercase everywhere: change the Python enum names to uppercase (U16 = "U16"). Simpler migration, no enum rename. But breaks Python convention (enum names are usually lowercase or PascalCase, not UPPERCASE).

Option C — use values_callable: pass values_callable=lambda x: [e.value for e in x] to the SQLAlchemy Enum column type so SQLAlchemy stores/reads by VALUE instead of NAME. No migration needed, no model change needed, just a one-line Column def tweak. This is the correct answer in most cases and preserves the Python convention.

Recommended: Option C.

Environment

Cluster/namespace: prod (basketball-api)
Service version/commit: main at commit containing migration 040
Affected table: teams (age_group column)
Affected data: only team id 12 was exercising this path — currently mitigated by NULL
Related alerts: none (pod crashloop was attributed to migration 042 at the time)

Acceptance Criteria

Team.age_group column type uses values_callable to store/read by value (Option C recommended)
Existing UPDATE teams SET age_group = NULL WHERE id = 12 reversed — team 12 can hold AgeGroup.u16 without blowing up reads
Test: db.query(Team).filter(Team.id == 12).first() returns without LookupError
Integration test: create a team with age_group=AgeGroup.u16, commit, re-query, assert .age_group == AgeGroup.u16
No regression on other enum types (check Division, TeamPreference, ContractStatus, etc. for similar patterns and fix if found)

Test Expectations

Unit: test_team_age_group_roundtrip — create, read, assert equality
Unit: test_team_age_group_null_still_works — ensure NULL reads don't error
Integration: migration that reverses the NULL workaround and restores age_group='U16' for team 12

Constraints

Prefer Option C (values_callable) — least invasive, preserves Python naming convention
Do not change existing enum member names OR values — only the Column type configuration
Audit all other enum Column types in models.py for the same pattern

westside-basketball — project this affects
basketball-api#422 — the migration that exposed this bug
basketball-api#425 — the endpoint whose end-to-end test surfaced this

### Type Bug ### Lineage Standalone — latent bug discovered 2026-04-10/11 during basketball-api#425 end-to-end testing. When Jacelyn's tier-change mint tried to move her to the newly-created 16U Local Queens team (team id 12), the endpoint 500'd with `LookupError: 'U16' is not among the defined enum values. Enum name: agegroup`. ### Repo `forgejo_admin/basketball-api` ### What Broke The `AgeGroup` Python enum in `src/basketball_api/models.py:170` defines: ```python class AgeGroup(enum.Enum): u8 = "U8" u10 = "U10" u12 = "U12" u14 = "U14" u16 = "U16" u18 = "U18" ``` **Enum NAMES are lowercase** (`u16`) but **enum VALUES are uppercase** (`U16`). The Postgres `agegroup` enum type only accepts `{U8, U10, U12, U14, U16, U18}` (uppercase — the values). SQLAlchemy is configured to read enums by NAME. So when SQLAlchemy reads a row with `age_group = 'U16'` (the actual stored value matching Postgres enum), it tries to find a Python enum member NAMED `U16` — which doesn't exist. Only `u16` (lowercase) exists. Raises `LookupError`. **Why it didn't surface before 2026-04-10**: All 7 pre-existing teams have `age_group = NULL`. None of the pre-existing migrations ever populated the column. Migration 040 (`040_create_16u_local_queens_team.py`) was the first migration to insert a non-NULL value, and it inserted `'U16'`. Next time a read hit team 12, the LookupError fired. **Current workaround**: Ava ran `UPDATE teams SET age_group = NULL WHERE id = 12` directly on prod to match the other 7 teams. The mismatch is still in the model, just not being exercised. ### Repro Steps 1. On a fresh clone of basketball-api main (commit 9598c4d or later), read `src/basketball_api/models.py:170` and confirm `AgeGroup.u16 = "U16"` 2. In a Python shell: `from basketball_api.models import AgeGroup; AgeGroup('U16')` — succeeds (by VALUE lookup) 3. In a Python shell: `AgeGroup['U16']` — FAILS with KeyError (by NAME lookup) 4. SQLAlchemy's default Enum type stores by name and reads by name, which is the bug surface ### Expected Behavior The Python enum and the Postgres enum should agree on casing. Two possible fixes: **Option A** — lowercase everywhere: change the Python enum values to lowercase (`u16 = "u16"`) and ALTER the Postgres enum type to lowercase. Requires a migration that renames the enum values. Clean, but touches existing data if any teams ever had uppercase values stored (none currently, so safe). **Option B** — uppercase everywhere: change the Python enum names to uppercase (`U16 = "U16"`). Simpler migration, no enum rename. But breaks Python convention (enum names are usually lowercase or PascalCase, not UPPERCASE). **Option C** — use `values_callable`: pass `values_callable=lambda x: [e.value for e in x]` to the SQLAlchemy `Enum` column type so SQLAlchemy stores/reads by VALUE instead of NAME. No migration needed, no model change needed, just a one-line Column def tweak. This is the correct answer in most cases and preserves the Python convention. **Recommended: Option C.** ### Environment - Cluster/namespace: prod (basketball-api) - Service version/commit: main at commit containing migration 040 - Affected table: `teams` (age_group column) - Affected data: only team id 12 was exercising this path — currently mitigated by NULL - Related alerts: none (pod crashloop was attributed to migration 042 at the time) ### Acceptance Criteria - [ ] `Team.age_group` column type uses `values_callable` to store/read by value (Option C recommended) - [ ] Existing `UPDATE teams SET age_group = NULL WHERE id = 12` reversed — team 12 can hold `AgeGroup.u16` without blowing up reads - [ ] Test: `db.query(Team).filter(Team.id == 12).first()` returns without LookupError - [ ] Integration test: create a team with `age_group=AgeGroup.u16`, commit, re-query, assert `.age_group == AgeGroup.u16` - [ ] No regression on other enum types (check Division, TeamPreference, ContractStatus, etc. for similar patterns and fix if found) ### Test Expectations - [ ] Unit: `test_team_age_group_roundtrip` — create, read, assert equality - [ ] Unit: `test_team_age_group_null_still_works` — ensure NULL reads don't error - [ ] Integration: migration that reverses the NULL workaround and restores `age_group='U16'` for team 12 ### Constraints - Prefer Option C (values_callable) — least invasive, preserves Python naming convention - Do not change existing enum member names OR values — only the Column type configuration - Audit all other enum Column types in models.py for the same pattern ### Related - `westside-basketball` — project this affects - basketball-api#422 — the migration that exposed this bug - basketball-api#425 — the endpoint whose end-to-end test surfaced this