Participant Identity & External Identifiers (XID)

Related reading

  • Event Participant Import Design — how EP import consumes this model

  • Results Import Design — how result import resolves participants by XID

  • design-journal/2026-05/participant-identity-correlation.adoc — the working design record (alternatives weighed, decisions dated, ADO Feature #686)

  • This document — the durable "how it is built and why" for identity correlation

1. Why this exists

Events are fed by external systems — a legacy WPCA registration database, a timing provider, a partner federation. Each of those systems has its own primary keys for the same human being and the same entry. When we import participants or results we must answer two questions without creating duplicates and without trusting a number blindly:

  1. Which person is this? — the same athlete may enter through several source systems over years, each with a different internal id.

  2. Which entry (EventParticipant) is this? — within one event, a source system identifies its own registration row by its own key.

The model below pins both answers to durable, queryable identifiers rather than to fragile matching on names or bib numbers.

2. Vocabulary: the two XIDs

"XID" is shorthand for external identifier — an id minted by a system other than ours. There are exactly two, and keeping them distinct is the single most important thing in this design.

Term Grain Stored as Means

Person-XID

Person (across all events)

PersonExternalReference.external_uid, scoped by registration_system_id

"In source system S, this human being is known as U." A person accumulates many Person-XIDs — one per source system they have ever come through. 1:N.

Participant-XID

EventParticipant (one event)

EventParticipant.registrationId

"In the source system, this entry into this event is row R." Scoped to the event; the same value may recur in a different event without collision. 1:1 with the EP.

The distinction is not academic. A result file that carries the source system’s person id needs the Person-XID path (resolve person → that event’s EP). A file that carries the source system’s entry id needs the Participant-XID path (resolve the EP directly). Picking the wrong one silently fails to resolve — see the mode-mismatch guard.

3. Schema

person_external_reference
  id                       bigint PK
  person_id                bigint FK → wp_users(id)            (the Person / User)
  registration_system_id   bigint FK → registration_system(id) (the source system S)
  external_uid             varchar                              (the Person-XID U)
  UNIQUE (registration_system_id, external_uid)                (U is unique *within* S, never globally)

event_participant
  ...
  registration_id          varchar                              (the Participant-XID, source EP key)
  UNIQUE (event_id, person_id)                                  (one entry per person per event)

event
  ...
  source_registration_system_id  bigint FK → registration_system(id)  (US #769; nullable)

Three points carry the design:

  • PersonExternalReference is 1:N, not a column on wp_users. A single column could only hold one source id; an athlete who came through WPCA in 2019 and a partner federation in 2024 has two, both valid, both needed for future resolution.

  • Uniqueness is (registration_system_id, external_uid), never external_uid alone. Two unrelated source systems are free to mint the same string; only the pair identifies a person.

  • Event.source_registration_system_id records which external system this event’s field came from, so result import can default and scope Person-XID resolution without the operator re-typing it every time (US #769 — see Event source registration system (US #769)).

4. The four-field identity DTO

Participant import accepts up to four identity inputs per row; the importer uses the first that resolves, in trust order. This replaced an earlier single-"externalId" column that conflated person identity with entry identity.

DTO field XID Resolves

ourEventParticipantId

(ours)

Directly to EventParticipant.id — our own PK, fully trusted.

ourPersonId

(ours)

To Person/User.id — our own PK; the EP is then the person’s entry in the event.

sourceSystemParticipantId

Participant-XID

To the EP via registrationId within the event (upsert key). Written onto EventParticipant.registrationId.

sourceSystemId + person fields

Person-XID

sourceSystemId names the source system S; on a confirmed person match the source’s person id is written back as a PersonExternalReference (Person-XID), so the next import resolves in one hop.

4.1. Trust matrix: (is_self, trustPKs)

Whether a supplied PK is trusted depends on whose system it is and whether the operator vouched for it:

RegistrationSystem.is_self trustPKs Behaviour

true (the id is ours)

—

ourEventParticipantId / ourPersonId are trusted directly — they are our own keys.

false (a foreign system)

true

The operator asserts the foreign PKs in the file are reliable; resolve by them, write back the Person-XID.

false

false (default)

Treat foreign PKs as hints only; fall through to identity matching (ID number, then name+DOB) and a sample-based fingerprint check before trusting the link.

The fingerprint check (a sampled field-by-field comparison of the incoming row against the stored person) guards against a bad foreign mapping silently merging two different humans. The full matrix, the fingerprint scoring, and the merge-candidate path are recorded in the design journal (participant-identity-correlation.adoc).

5. Event source registration system (US #769)

Event.sourceRegistrationSystem is set once, the first time participants are imported from a non-self source (set-if-absent during EP import). It then becomes the default for that event’s result imports:

  • Result import defaults its source-system selector from the event; the operator does not re-pick it on every file.

  • The operator may override it on a result import. Changing it warns and asks for confirmation, then persists the new value back onto the event — the design is deliberately a hybrid of "remembered on the event" and "settable per import" so a correction sticks without forcing a separate event-edit step.

  • When EXTERNAL_PERSON_UID resolution runs (see below), the source system scopes the Person-XID lookup: PersonExternalReference WHERE registration_system_id = <event/import source> AND external_uid = <file value>.

6. How the importers consume this

Importer Identity use

Event Participant import

Reads the four-field DTO; matches/creates the Person; writes the Participant-XID onto registrationId; on a confirmed foreign match writes back the Person-XID; sets Event.sourceRegistrationSystem if absent.

Result import

Resolves the finisher to an EP by one of four modes. EXTERNAL_PERSON_UID is the Person-XID path (source system + file value → PersonExternalReference → person → that event’s EP); REGISTRATION_ID is the Participant-XID path; EPID / PERSON_ID use our own keys.

7. Design rationale — alternatives weighed

Rejected alternative Why this design instead

Single external_id column on Person

Holds only one source system; can’t represent an athlete known to several. Conflates person identity with per-event entry identity. The 1:N PersonExternalReference + separate Participant-XID splits the two grains cleanly.

Globally-unique external_uid

Different source systems legitimately reuse ids. Uniqueness must be the (registration_system_id, external_uid) pair, or imports from a second system would collide.

Trust every foreign PK in the file

A single bad mapping merges two people irreversibly. The (is_self, trustPKs) matrix plus the fingerprint check make trust explicit and verified.

Re-prompt the source system on every result import

Tedious and error-prone across many category files for one event. Event.sourceRegistrationSystem remembers it; the per-import override + confirm keeps it correctable.

8. Related Documentation