Investigate linking issues in DC pludgy and pludgy

Triage: explorer_identity has duplicate Keycloak->Minecraft links for pludgy (and 49 others)

Player: pludgy -> single Minecraft UUID C5001F28-52FD-4FCB-B5E7-817B0564A5EE (one row in firm_players, no name collision). The "pludgy and pludgy" in the title = the same MC account linked twice/thrice, not two different players.

Data finding

explorer_identity has 3 rows for pludgy's UUID, each a different keycloak_sub:

keycloak_sub	minecraft_name	linked_at	linked_by
42967193-...ffe83	pludgy	2026-06-07 18:29:06	in-game:pludgy
7947e246-...d5916	pludgy	2026-05-31 01:16:52	in-game:pludgy
99ac4518-...6c059	pludgy	2026-06-07 18:30:47	in-game:pludgy

No explorer_link_code, explorer_group_member, or explorer_audit rows exist for this UUID/subs.

Root cause (schema-level, systemic)

explorer_identity PK is keycloak_sub; player_uuid_bin is only a non-unique index (idx_identity_player). There is no uniqueness constraint on player_uuid_bin, so the same Minecraft account can be linked from any number of distinct Keycloak/Discord logins. Every /link re-issued from a new (or re-provisioned) Keycloak account inserts a brand-new identity row instead of updating/replacing the existing link.

This is not isolated to pludgy. Across the table: 331 identity rows / 249 distinct MC players / 331 distinct subs, and 50 players (20%) have 2+ subs mapped to one UUID (e.g. unalign and loxuk_ have 7 each; PelleS10, smirkymyjens, Paradaux have 5). The ticket happened to be filed under one example (pludgy).

Why it's a user-visible bug

When pludgy logs into the explorer via Keycloak, the resolved login can land on any of the 3 subs. If permissions/groups are attached per-keycloak_sub (not per-UUID), the player sees inconsistent identity/state depending on which sub the session resolves to. (pludgy currently has no group rows, so no privilege impact for this user yet — but the same drift affects staff like Paradaux, x5.)

Recommended next step

This is a confirmed data-integrity bug, but needs a product decision before remediation — specifically: is one MC UUID allowed multiple Keycloak links (intentional multi-login), or must the link be 1:1? That determines the fix:

• If 1:1 (most likely intended): make the link replace-on-relink. App-side: on /link, delete prior rows for that player_uuid_bin before insert (or upsert keyed on UUID). Schema-side: add a partial/secondary path so only the latest sub survives, then a UNIQUE index on player_uuid_bin (via an economy-schema V<n>__*.sql migration per load-bearing rule 5 — do not edit schema.sql).
• Remediation SQL (run only after confirming which sub is authoritative — keeps the most recent linked_at per player, scoped narrowly):

-- PREVIEW the rows that would be removed (older duplicate subs):
SELECT ei.keycloak_sub, bin_to_uuid(ei.player_uuid_bin) AS uuid, ei.minecraft_name, ei.linked_at
FROM explorer_identity ei
JOIN (
  SELECT player_uuid_bin, MAX(linked_at) AS keep_at
  FROM explorer_identity GROUP BY player_uuid_bin HAVING COUNT(*) > 1
) k ON k.player_uuid_bin = ei.player_uuid_bin
WHERE ei.linked_at < k.keep_at;
-- (then DELETE the same set once approved; if two rows share MAX(linked_at), tie-break by keycloak_sub)

Pludgy-only narrow fix would keep 99ac4518-... (2026-06-07 18:30:47) and drop 42967193-... + 7947e246-....

Info still needed to finalize

1. Intended cardinality: 1 MC UUID -> 1 Keycloak sub, or many allowed?
2. Where are explorer permissions/groups keyed — on keycloak_sub or player_uuid_bin? (Confirms blast radius.)
3. The actual symptom pludgy reported (expected vs actual on login) — not captured in the ticket; would confirm which sub should be authoritative.

Classification: confirmed-bug (duplicate linking rows reproducibly present; missing uniqueness constraint), with a product decision gating the exact remediation.