Summary

Two alembic migrations have compatibility issues that block PostgreSQL upgrades from BETA-2 → current. Found during upgrade validation testing (PR #2943), manually verified using docker-compose.

Reproduction

# 1. Fresh BETA-2 install (--no-build is critical, otherwise compose rebuilds from local source)
IMAGE_LOCAL=ghcr.io/ibm/mcp-context-forge:1.0.0-BETA-2 \
  docker compose --profile testing up -d --no-build

# 2. Stop, keep data volumes
make testing-down

# 3. Start with current image
docker compose --profile testing up -d --no-build

# 4. All gateway replicas crash-loop:
#    ERROR [mcpgateway.bootstrap_db] Migration/Bootstrap failed:
#    TypeError: the JSON object must be str, bytes or bytearray, not list

Root cause

psycopg (Python PostgreSQL driver) auto-deserializes JSON/JSONB columns into native Python objects. SELECT permissions FROM roles returns a Python list, not a JSON string.

The migration chain runs 7 steps successfully before crashing:

f1a2b3c4d5e6 → 4e6273136e56 → b1b2b3b4b5b6 → 04cda6733305
  → v1a2b3c4d5e6 → c1c2c3c4c5c6 → 207d8bbddd61
  → a31c6ffc2239  💥 CRASH (json.loads gets a list, not a string)

Transactional DDL rolls back cleanly — DB stays at f1a2b3c4d5e6, no corruption.

Migration a31c6ffc2239 — token permissions for team_admin

Problem: json.loads(row[1]) on line 69 fails on PostgreSQL where JSON columns return native Python lists.

# Crashes on PostgreSQL:
current_permissions = json.loads(row[1]) if row[1] else []
# row[1] is ['teams.read', ...] (list), not '["teams.read", ...]' (string)

Fix needed: Replace with a helper that handles both:

• SQLite: JSON string → json.loads()
• PostgreSQL: native list → pass through
• Handle bytes, None, invalid JSON, non-string items

Affects both upgrade() and downgrade().

Migration ba202ac1665f — configurable role defaults

Problem 1: Phase 2 backfill uses granted_by='system_migration' but user_roles.granted_by has FK to email_users.email. On PostgreSQL this INSERT fails.

Fix needed: Use granted_by=user_email (self-grant) to satisfy FK constraint.

Problem 2: Downgrade tries DELETE FROM user_roles WHERE granted_by = 'system_migration' — dead code after fixing Problem 1.

Fix needed: Remove dead DELETE, update docstring.

Found during

PR #2943 — Alembic upgrade validation CI