#384 - Bug: Migration 037 fails on fresh deploy — enum ADD VALUE in same transaction as INSERT - forgejo_admin/basketball-api

forgejo_admin commented

2026-04-07 19:15:01 +00:00

Owner

Type

Bug

Lineage

Regression from PR #370 which added migration 037. Discovered during CRM incident response 2026-04-07.

Repo

forgejo_admin/basketball-api — file is on main branch (remote). Local checkouts may need git pull to see migrations 031-037 which were merged recently.

What Broke

Migration 037_add_monthly_category_and_first_payment_email.py crashes on fresh deploy with:

sqlalchemy.exc.OperationalError: (psycopg2.errors.UnsafeNewEnumValueUsage)
unsafe use of new value "monthly" of enum type productcategory
HINT: New enum values must be committed before they can be used.

The migration adds monthly to productcategory enum AND inserts a row using that value in the same transaction. PostgreSQL requires new enum values to be committed before they can be referenced in DML.

Repro Steps

Deploy basketball-api with a database that hasn't run migration 037
Pod starts, runs alembic upgrade head
Migration 037 executes: ALTER TYPE productcategory ADD VALUE 'monthly' then INSERT ... 'monthly'
PostgreSQL rejects the INSERT — enum value not yet committed
Pod enters CrashLoopBackOff

Expected Behavior

Migration 037 completes successfully on any database state.

Environment

Cluster/namespace: prod / basketball-api
Pod: basketball-api-6868cf458d-zc74s (CrashLoopBackOff, 5 restarts before workaround)
Workaround applied: manually ran ALTER TYPE ... ADD VALUE via psql, pod recovered

Acceptance Criteria

alembic upgrade head succeeds on a fresh database (no prior migrations)
alembic upgrade head succeeds on existing prod database (workaround already applied — IF NOT EXISTS handles this)
CI pipeline passes (all 860+ tests)
No regressions on databases where migration already succeeded

Fix (verified pattern)

Standard Alembic pattern for ADD VALUE — same approach used in migrations 011, 012, 018, 027:

import sqlalchemy as sa
from alembic import op

def upgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("COMMIT"))
    conn.execute(sa.text("ALTER TYPE productcategory ADD VALUE IF NOT EXISTS 'monthly'"))
    conn.execute(sa.text("ALTER TYPE emailtype ADD VALUE IF NOT EXISTS 'first_payment'"))
    conn.execute(sa.text("BEGIN"))

    op.execute("""
        INSERT INTO products (tenant_id, name, price_cents, product_type, category, active)
        VALUES (1, 'Monthly Fee — Prorated April', 0, 'one_time', 'monthly', true)
        ON CONFLICT DO NOTHING
    """)

File Targets

alembic/versions/037_add_monthly_category_and_first_payment_email.py (on remote main — agent must git pull first)

westside-basketball — blocked deploy of CRM fixes (#372, #374)
PR #370 — introduced this migration
Migrations 011, 012, 018, 027 — use the correct COMMIT/BEGIN pattern (reference implementations)

### Type Bug ### Lineage Regression from PR #370 which added migration 037. Discovered during CRM incident response 2026-04-07. ### Repo `forgejo_admin/basketball-api` — file is on `main` branch (remote). Local checkouts may need `git pull` to see migrations 031-037 which were merged recently. ### What Broke Migration `037_add_monthly_category_and_first_payment_email.py` crashes on fresh deploy with: ``` sqlalchemy.exc.OperationalError: (psycopg2.errors.UnsafeNewEnumValueUsage) unsafe use of new value "monthly" of enum type productcategory HINT: New enum values must be committed before they can be used. ``` The migration adds `monthly` to `productcategory` enum AND inserts a row using that value in the same transaction. PostgreSQL requires new enum values to be committed before they can be referenced in DML. ### Repro Steps 1. Deploy basketball-api with a database that hasn't run migration 037 2. Pod starts, runs `alembic upgrade head` 3. Migration 037 executes: `ALTER TYPE productcategory ADD VALUE 'monthly'` then `INSERT ... 'monthly'` 4. PostgreSQL rejects the INSERT — enum value not yet committed 5. Pod enters CrashLoopBackOff ### Expected Behavior Migration 037 completes successfully on any database state. ### Environment - Cluster/namespace: prod / basketball-api - Pod: `basketball-api-6868cf458d-zc74s` (CrashLoopBackOff, 5 restarts before workaround) - Workaround applied: manually ran `ALTER TYPE ... ADD VALUE` via psql, pod recovered ### Acceptance Criteria - [ ] `alembic upgrade head` succeeds on a fresh database (no prior migrations) - [ ] `alembic upgrade head` succeeds on existing prod database (workaround already applied — `IF NOT EXISTS` handles this) - [ ] CI pipeline passes (all 860+ tests) - [ ] No regressions on databases where migration already succeeded ### Fix (verified pattern) Standard Alembic pattern for `ADD VALUE` — same approach used in migrations 011, 012, 018, 027: ```python import sqlalchemy as sa from alembic import op def upgrade() -> None: conn = op.get_bind() conn.execute(sa.text("COMMIT")) conn.execute(sa.text("ALTER TYPE productcategory ADD VALUE IF NOT EXISTS 'monthly'")) conn.execute(sa.text("ALTER TYPE emailtype ADD VALUE IF NOT EXISTS 'first_payment'")) conn.execute(sa.text("BEGIN")) op.execute(""" INSERT INTO products (tenant_id, name, price_cents, product_type, category, active) VALUES (1, 'Monthly Fee — Prorated April', 0, 'one_time', 'monthly', true) ON CONFLICT DO NOTHING """) ``` ### File Targets - `alembic/versions/037_add_monthly_category_and_first_payment_email.py` (on remote `main` — agent must `git pull` first) ### Related - `westside-basketball` — blocked deploy of CRM fixes (#372, #374) - PR #370 — introduced this migration - Migrations 011, 012, 018, 027 — use the correct COMMIT/BEGIN pattern (reference implementations)

forgejo_admin commented

2026-04-07 19:19:14 +00:00

Author

Owner

Scope Review: NEEDS_REFINEMENT

Review note: review-889-2026-04-07

Well-scoped bug with a known fix pattern, but three items need attention before dispatch:

[SCOPE] Architecture note arch-basketball-api does not exist in pal-e-docs. Needs creation for traceability.
[BODY] Migration 037 does not exist on local main branch (latest is 030). Clarify which branch contains migrations 031-037, or confirm the agent should git pull latest main before starting.
[BODY] Ensure acceptance criteria are explicitly listed per template-issue-bug (fresh DB upgrade, existing DB upgrade with workaround applied, CI passes).

Blast radius is low -- 5 existing enum ADD VALUE migrations all use the isolated pattern. Fix restores established convention. Single file, under 5 minutes.

## Scope Review: NEEDS_REFINEMENT Review note: `review-889-2026-04-07` Well-scoped bug with a known fix pattern, but three items need attention before dispatch: - **[SCOPE]** Architecture note `arch-basketball-api` does not exist in pal-e-docs. Needs creation for traceability. - **[BODY]** Migration 037 does not exist on local `main` branch (latest is 030). Clarify which branch contains migrations 031-037, or confirm the agent should `git pull` latest main before starting. - **[BODY]** Ensure acceptance criteria are explicitly listed per `template-issue-bug` (fresh DB upgrade, existing DB upgrade with workaround applied, CI passes). Blast radius is low -- 5 existing enum ADD VALUE migrations all use the isolated pattern. Fix restores established convention. Single file, under 5 minutes.

forgejo_admin commented

2026-04-07 19:19:50 +00:00

Author

Owner

Scope refinement (post review-889-2026-04-07):

Clarified that migrations 031-037 are on remote main (recently merged)
Added explicit AC: fresh DB, existing DB with workaround, CI passes
Referenced migrations 011/012/018/027 as existing correct pattern implementations

**Scope refinement (post review-889-2026-04-07):** - Clarified that migrations 031-037 are on remote main (recently merged) - Added explicit AC: fresh DB, existing DB with workaround, CI passes - Referenced migrations 011/012/018/027 as existing correct pattern implementations

forgejo_admin referenced this issue

2026-04-07 19:20:47 +00:00

feat: add declined status to ContractStatus enum #380

forgejo_admin commented

2026-04-07 19:22:24 +00:00

Author

Owner

Scope Review: READY (re-review)

Review note: review-889-2026-04-07

All three recommendations from the initial NEEDS_REFINEMENT review have been resolved:

File target provenance: Clarified that migrations 031-037 are on remote main; agent must git pull. Verified local checkout stops at 030.
Acceptance criteria: Fresh DB, existing DB with workaround, CI passes -- all agent-verifiable.
Pattern references: Verified migrations 011/012/018/027 in the codebase. None combine ADD VALUE with INSERT. 037 is the sole offender.

Remaining non-blocking [SCOPE] item: Create architecture note arch-basketball-api in pal-e-docs (broad organizational gap, not ticket-specific).

Ticket is ready for dispatch.

## Scope Review: READY (re-review) Review note: `review-889-2026-04-07` All three recommendations from the initial NEEDS_REFINEMENT review have been resolved: - **File target provenance**: Clarified that migrations 031-037 are on remote main; agent must git pull. Verified local checkout stops at 030. - **Acceptance criteria**: Fresh DB, existing DB with workaround, CI passes -- all agent-verifiable. - **Pattern references**: Verified migrations 011/012/018/027 in the codebase. None combine ADD VALUE with INSERT. 037 is the sole offender. Remaining non-blocking [SCOPE] item: Create architecture note `arch-basketball-api` in pal-e-docs (broad organizational gap, not ticket-specific). Ticket is ready for dispatch.

forgejo_admin referenced this issue from a commit

2026-04-07 19:23:29 +00:00

fix: commit enum ADD VALUE before INSERT in migration 037

forgejo_admin referenced this issue from a pull request that will close it,

2026-04-07 19:23:47 +00:00

fix: commit enum ADD VALUE before INSERT in migration 037 #386

forgejo_admin closed this issue

2026-04-07 19:28:59 +00:00

forgejo_admin referenced this issue

2026-04-08 20:57:03 +00:00

feat: add declined status to ContractStatus enum #380