🐞 Bug Summary

oauth_tokens ends up with a UNIQUE (gateway_id, user_id) constraint (unique_gateway_user) even though the model declares uniqueness on (gateway_id, app_user_email). Since user_id is the OAuth provider's subject - not the Context Forge user - any gateway where multiple CF users resolve to the same provider user_id can only store a token for the first user; every other user's OAuth callback then fails with a UniqueViolation.

The mismatch:

• the create-table migration adds UNIQUE (gateway_id, user_id) and no later migration ever drops it;
• the migration that introduced per-user tokens adds a (gateway_id, app_user_email) unique index but leaves the old constraint in place;
• the model's uq_oauth_gateway_user name appears in zero migrations.

So whether a DB has the bug depends entirely on how its schema was first built (see "Why this surfaces on v1.0.2" below), a create_all()-from-models DB is correct; a alembic upgrade head DB carries the stale constraint.

🧩 Affected Component

Select the area of the project impacted:

• mcpgateway - API
• mcpgateway - UI (admin panel)
• mcpgateway.wrapper - stdio wrapper
• Federation or Transports
• CLI, Makefiles, or shell scripts
• Container setup (Docker/Podman/Compose)
• Other (explain below)

🔁 Steps to Reproduce

1. Do a fresh v1.0.2 install against an empty database (v1.0.2 initialises it by replaying all migrations — see below).
2. Register a gateway with OAuth2 (authorization_code) whose token response has no per-user sub/user_id/id — or whose provider returns one org/account-scoped subject. (_extract_user_id then falls back to client_id, finally the literal "unknown_user" — identical for every user.)
3. As CF user A, click Authorize → token stored.
4. As CF user B, click Authorize → UniqueViolation on unique_gateway_user; callback fails.

Schema check:

SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid='oauth_tokens'::regclass;
-- a migration-built DB shows: unique_gateway_user UNIQUE (gateway_id, user_id)  <- not declared by the model

🤔 Expected Behavior

Uniqueness should be (gateway_id, app_user_email) — one token per Context Forge user per gateway, per the model. The provider user_id should not be part of the key.

📓 Logs / Error Output

(psycopg.errors.UniqueViolation) duplicate key value violates unique constraint "unique_gateway_user"
DETAIL:  Key (gateway_id, user_id)=(<gateway>, <provider-subject>) already exists.
[SQL: INSERT INTO oauth_tokens (...) VALUES (...)]

🧠 Environment Info

You can retrieve most of this from the /version endpoint.

🧩 Additional Context (optional)

Why this surfaces on v1.0.2 but not on older databases. The table is built one of two ways, decided in bootstrap_db.py:

• ≤ v1.0.1: an empty DB is initialised with Base.metadata.create_all() + alembic stamp head - tables come from the models (correct uq_oauth_gateway_user), and migrations are stamped, not executed, so the orphan-creating migration never runs. Clean.
• v1.0.2: that branch was removed; main() now only runs command.upgrade(cfg, "head"), replaying every migration from base even on a fresh empty DB - so the orphan is created and never dropped. (The docstring still claims "create_all + stamp on an empty DB", but the code no longer does this.

Net: a DB first initialised under ≤ v1.0.1 is clean; a fresh v1.0.2 install is broken. Once a DB is at head, later boots take the at-head fast-path, so an older clean DB stays clean even after upgrading the image to v1.0.2.