Person Deduplication & Merge

Overview

The Person Deduplication & Merge feature combines duplicate Person records in the system into a single authoritative record. It updates all foreign key references, merges metadata intelligently, and provides an audit trail of all merge operations.

This feature builds on the existing Progressive Person Matching scoring infrastructure (PersonMatchConfig) and is a prerequisite for deploying progressive matching in production — duplicate records cause legitimate matches to return AMBIGUOUS.

Terminology

Term Definition

Source Person

The duplicate Person record that will be absorbed and soft-deleted after merge.

Target Person

The surviving Person record that receives all merged data and FK references.

PersonWrapper

Wraps User (WordPress wp_users) with typed metadata accessors for person fields. The canonical interface for reading/writing person data.

PersonMergeLog

Audit record capturing merge timestamp, source/target IDs, field provenance, trigger type, and operator.

PersonMergeCandidate

A detected potential duplicate pair awaiting review or auto-merge, with match score and status.

Target Selection

The target is the Person record that survives the merge. The caller chooses it. A manual merge names the source and the target; approving a detected candidate names the surviving person. Nothing in the service infers a survivor, and the order in which a candidate stores its pair is a storage rule — the lower person id is stored first — which carries no survivor meaning.

Guidance for whoever chooses, in priority order:

  1. The person with the most recently edited data (last_edited meta).

  2. If equal, the person with richer relationship data — more LinkedPerson, EventParticipant or Membership records.

Both are advice to the operator rather than behaviour of the service.

Merging Person Properties (UserMeta)

The merge uses PersonWrapper-aware logic rather than blind metadata key stacking. For each field:

  1. Prefer valid over invalid — e.g., a Luhn-valid SA ID over an invalid one, a real email over a fake email (@member.wpcycling.com)

  2. Prefer non-null over null — fill gaps from the source where the target has no value

  3. Prefer most recently edited — when both have valid, non-null values of equal quality, keep the most recent

  4. Record provenance — for each field, record which person (source or target) contributed the surviving value

PersonWrapper Fields Subject to Merge

Identity

first_name, last_name, person_email, title, language, date_of_birth, id_number, id_type, id_country, nationality_country, gender, contact_number

Emergency / Medical

ice_name, ice_number, iceRelationship, doctor_name, doctor_number, scheme_name, scheme_principle, scheme_policy_number, medication, medical_conditions, medical_allergies

Guardian

parent_name, parent_email, parent_number, parent_relationship

Other

school, club_name, other_number, external_ref, main_member_id

Billing

billing_address_1, billing_city, billing_country, billing_postcode

Fields NOT Merged (System-Managed)

user_key, uuid, managed_by (deprecated), last_edited (reset on merge), created_on, merge_date_time (set on source), _wpca_sync_on, _wpca_sync_by

Foreign Key Migration

All database foreign keys referencing wp_users.ID must be updated from source to target. The merge is transactional — all updates succeed or all roll back.

Table Column Records (prod) Notes

event_participant

person_id

24,073

Conflict resolution needed if both source and target have participation in the same event (see Conflict Resolution)

membership

person_id

38,543

Conflict resolution for same MembershipType

tag

person_id

41,624

tag_assignment

person_id

2,028

Deprecated — migrate but mark code @Deprecated

race_number

person_id

30,343

race_pack_barcode

person_id

188

race_result

person_id

14,235

race_number_assignment

person_id

0

order_line_item

person_id

12,904

process_data

person_id

18,367

process_instance__person

person_id

6,406

Join table — update or deduplicate

linked_person

linked_person_id

10,823

Deduplicate if target already linked to same principal

org_user

person_id

21,702

See OrgUser Principal Merge

wp_users_ext

ID

3

1:1 extension — migrate or merge row

match_token

user_id

808

Application-level reference (no FK constraint)

person_external_reference

person_id

The external-system identity mappings follow the surviving person; left behind, they would strand a system-of-record claim on a deleted record

person_merge_candidate

source_person_id, target_person_id

Open candidates naming the absorbed person are re-pointed at the survivor, or closed where both sides now resolve to one person. A rejection carries across, so a reviewer is not asked the same question twice

Conflict Resolution

EventParticipant

When both source and target have an EventParticipant for the same Event:

  • Same EventCategory: Keep target’s record, delete source’s. Migrate any associated ProcessData, OrderLineItem, RaceResult, and StartGroupParticipant from source’s record to target’s if they don’t conflict.

  • Different EventCategory: Flag for manual review — both participations may be legitimate.

Membership

A person may have multiple Membership records for the same MembershipType across different years (annual renewals). The conflict key is MembershipType + MembershipPeriod — a person should appear in each period at most once.

When both source and target have a Membership for the same MembershipType and MembershipPeriod:

  • Keep the record with the most recent renewal or richer financial history.

  • Delete the other after migrating any linked financial records.

When they have the same MembershipType but different MembershipPeriod values, both records are legitimate (different renewal years) — simply re-point the source’s record to the target.

LinkedPerson

When both source and target are linked to the same OrgUser principal:

  • Keep the LinkedPerson record pointing to target (it already exists).

  • Delete the LinkedPerson record pointing to source.

OrgUser Principal Merge

If the source Person is OrgUser.person (i.e., someone’s account principal):

  1. Re-point org_user.person_id from source to target.

  2. Multiple OrgUser records pointing to the same Person is allowed — different session identifiers (e.g., anonymous UUID sessions, OIDC logins) may independently resolve to the same deduplicated person. No manual review needed for this case.

  3. Deduplicate LinkedPerson records if target is now both a principal’s person and a linked person under the same principal.

Relationship Model Migration

managed_by Meta Retirement

The legacy managed_by UserMeta is replaced by LinkedPerson + OrgUser:

Aspect Legacy (managed_by) Current (LinkedPerson + OrgUser)

Storage

wp_usermeta multi-value

linked_person JPA entity

Principal identification

Implicit (user with login)

Explicit (OrgUser.person)

Access control

None

LinkedPerson.accessLevel (READ, READ_WRITE)

Relationship type

None

LinkedPerson.linkType (FAMILY, GUARDIAN, COACH, etc.)

Temporal validity

None

LinkedPerson.validFrom / validTo

Security integration

None

SecurityDimensionService dynamic resolution

The new merge service operates entirely on LinkedPerson and OrgUser. It does not read or write managed_by meta. After merge migration is complete, managed_by will be deprecated and removed.

Merge Audit Trail

Source Person Metadata

The source (merged-away) person receives:

  • merge_date_time — ISO timestamp of the merge

  • PersonWrapper.setPersonDeleted(true) — soft-delete via User.userStatus bitmask (PERSON_DELETE_STATUS_MASK = 0x01)

PersonMergeLog Table

A dedicated audit table captures the full merge history:

Column Type Description

id

BIGINT PK

Auto-generated

merge_date_time

DATETIME

When the merge was executed

source_person_id

BIGINT

The absorbed (now deleted) person

target_person_id

BIGINT

The surviving person

trigger_type

VARCHAR

ADMIN_MANUAL — a person decided. AUTO_EXACT_ID — reserved for a machine acting on an exact, Luhn-valid shared identity number; nothing writes it, because no automated merge exists. AUTO_CONFIDENT_MATCH — reserved and never to be written: it names a machine merging on resemblance, which ADR-0012 forbids. It survives so that historical rows still deserialise

operator

VARCHAR

Who performed the merge, or SYSTEM for an automated one

field_provenance

JSON

Per-field record of which person contributed each surviving value

fk_updates

JSON

Summary of FK tables and record counts updated

Match Classification

No merge happens without a person deciding it. Detection raises candidates; it never merges. That is ADR-0012's rule — a machine may act on identity, never on resemblance — applied at its strictest: automated merging was removed entirely rather than kept for the identity-grade subset.

Outcome Evidence Action

Raised for review

A collision or typo signal, or a fuzzy score at or above the review threshold

A PersonMergeCandidate is filed and waits for an operator. Signals include two records sharing an identity number, an external-system identifier resolving to a different record than the identity number does, one identity number that looks like a typo of another on the same birth date, a name-only or birth-date-and-gender resemblance from an import, and a scored name, birth-date and gender resemblance.

No action

Below the review threshold

Nothing is filed.

Detection reason

Every candidate carries one reason, from a closed set shared by every producer — the detection service and the import paths alike, so the review queue can be filtered and the producers cannot drift onto free text: SAID_COLLISION, EXTERNAL_UID_COLLISION, LOW_CONFIDENCE_MATCH, DOB_GENDER, FUZZY_MATCH, INVALID_SAID_DOB_MATCH.

A candidate raised by a collision or typo signal carries no score: the signal fired, rather than a comparison being scored. Absent is not zero.

Eligibility assessment

Separately from why a pair was raised, the service will assess whether the two records look like the same human, and report which details agree and which disagree across identity number, email, date of birth, name and gender. A detail missing on either side counts as neither agreement nor disagreement.

The assessment informs; it does not decide. A pair that does not assess as eligible can still be merged, but only with an explicit confirmation, which is recorded. The integrity refusals are not confirmable: the same person on both sides, or a source or target that has already been absorbed.

API

Manual Merge

POST /api/admin/person-merge
Content-Type: application/json

Request:
{
  "sourcePersonId": 12345,
  "targetPersonId": 67890,
  "confirmed": false
}

Response:
{
  "mergeLogId": 101,
  "targetPersonId": 67890,
  "fieldsUpdated": 14,
  "fkTablesUpdated": 12,
  "totalRecordsMigrated": 47
}

confirmed is the caller’s acceptance of a merge the eligibility assessment does not support. Without it, such a pair is refused and the response carries the assessment, so the caller can see what would have to be confirmed.

Merge Candidates

GET /api/admin/person-merge-candidates?status=PENDING&page=0&size=20&sort=score,desc

Response:
{
  "content": [
    {
      "id": 1,
      "sourcePersonId": 12345,
      "sourcePersonName": "John Smith",
      "targetPersonId": 67890,
      "targetPersonName": "J. Smith",
      "score": 72,
      "detectionReason": "DOB_GENDER",
      "status": "PENDING",
      "detectedAt": "2026-03-30T10:00:00Z"
    }
  ]
}

GET /api/admin/person-merge-candidates/{id}
POST /api/admin/person-merge-candidates/{id}/approve
POST /api/admin/person-merge-candidates/{id}/reject
POST /api/admin/person-merge-candidates/bulk-approve

The single-candidate response adds both records' comparable fields, the reject reason where there is one, and the eligibility assessment:

{
  "id": 1,
  "sourcePerson": { "id": 12345, "firstName": "…", "lastName": "…", "dateOfBirth": "…",
                    "gender": "…", "identityNumber": "…", "identityType": "…",
                    "identityCountry": "…", "contactNumber": "…", "email": "…",
                    "createdOn": "…", "lastEdited": "…" },
  "targetPerson": { … },
  "rejectReason": null,
  "eligibility": {
    "eligible": false,
    "agreeing": ["NAME", "GENDER"],
    "disagreeing": ["DATE_OF_BIRTH"]
  }
}

Approve names the survivor and carries the operator’s confirmation. survivorPersonId must be one of the pair; omitting it keeps the stored target, which is the higher person id and carries no survivor meaning:

POST /api/admin/person-merge-candidates/{id}/approve
{ "survivorPersonId": 67890, "confirmed": false }

Reject takes an optional reason. Bulk approve merges only the pairs that assess as eligible and returns the rest, each with its assessment, for individual review. Acting on a candidate that is no longer pending is refused as a conflict.

Admin UI

The operator-facing screens are specified as a use case: S02 Duplicate People — the review queue and the candidate detail, where the survivor is chosen and the merge confirmed.

They are platform screens, not tenant ones, because a merge rewrites the absorbed person’s records at every organisation, and the comparison discloses personal detail across the organisation boundary. Person lookup is global by design; the organisation boundary governs disclosure, not matching.

Three further screens are described but not specified, and each waits on a decision rather than on effort:

  • Merge history and audit log — a list of completed merges from person_merge_log, with per-field provenance. What such a screen can honestly show is limited by Limitations of the current design.

  • Manual merge of any two records found by search, bypassing the queue and reusing the comparison.

  • Bulk approval from the queue. The API supports it; S02 deliberately does not, because the judgement is per pair.

Limitations of the current design

Stated as properties of the implementation, because each one shapes what a screen or an audit can show.

  • An approval is recorded as an automated merge. Approving a candidate writes the trigger type reserved for a machine acting on confidence — the value ADR-0012 forbids — rather than recording that a person decided. The audit trail therefore misattributes every reviewed merge.

  • No merge records who performed it. The operator is a fixed literal rather than the authenticated caller, so the log cannot answer "who merged these two".

  • The absorbed record’s details are cleared by the merge, for data minimisation. The audit row keeps both person ids, but a name read from the absorbed record afterwards is empty — so a history screen cannot show who was absorbed without the log itself retaining a description at merge time.

  • Provenance records which side won each field, not the values. It answers "did this value come from the survivor or the absorbed record", never "what was the value before".

  • A merge cannot repair a wrong link. The tooling serves the duplicate case, two records for one human. Where two records are different humans and a participation was attached to the wrong one, merging them would compound the error; recovery is re-pointing the records by hand.

Related Documentation