Agent pids are not unique: 756 pids shared by 1,543 Agent rows (case/whitespace variants) #28

🤖 rbotyee (Claude, operated by @rdhyee) — Raymond's intent: record data-quality defects found during the reproducibility work. What I did: found this while making sql_converter deterministic (PR #26); measured it on a full conversion of the April 2025 export; not fixed in that PR (it changes entity counts, so it deserves its own decision).

Defect

Agent pids are case- and whitespace-folded ('agent:' || LOWER(TRIM(name)) [|| ':' || role]), but the de-duplication that creates Agent entities is SELECT DISTINCT pid, agent_name, agent_role — so two spellings of one name yield two Agent rows with the same pid:

agent:abby uehling:event registrant   [' Abby Uehling', 'Abby Uehling']
agent:ariimoeana maopi:collector      [' Ariimoeana Maopi', 'Ariimoeana Maopi']
agent:no voucher:curator              ['No Voucher', 'no voucher']

Full export (6,680,932 rows): 756 pids shared by 1,543 Agent rows. No other entity type has duplicate pids.

Consequences

• pid is not unique for otype='Agent' (it is for every other type), which breaks the "pid is the key" assumption users are told to rely on.
• Every edge or p__* array that resolves an agent by pid gets both rows: p__responsibility / p__registrant lists carry two ids for one person, and the narrow format emits two responsibility edges per (event, position).
• This is also why plain ordinal ordering was not enough for determinism — PR sql_converter: byte-deterministic output; fix narrow edge row_id collisions #26 tie-breaks on the joined row_id so the output is at least stable.

Options (needs a decision)

1. One Agent per pid, canonical name = the first by a documented rule (e.g., lowest source row, or the most frequent spelling); label keeps the canonical name. Cleanest; changes Agent counts (−787) and every affected p__* array.
2. Keep both rows but make pid unique by appending a discriminator — preserves counts, but agent:… pids stop being human-predictable.
3. Trim/normalise the name at source (TRIM(name)) and keep case variants separate — fixes the whitespace half only.

Recommend 1, in a follow-up PR with its own before/after counts.