Pre-Event Data Hygiene

1. Overview

Between registration opening and event day, two classes of data debt accumulate that the platform does not yet prevent:

  • Duplicate orders — a parent retries a checkout that did not visibly confirm, minting a second (or eighth) sales_order against the same participant. Nothing blocks a second order on an EP that already has one.

  • Duplicate persons/participants — the same human registered under two wp_users records (new account per season, ID-number typo, guardian variations), sometimes producing two active EPs in the same event.

Both must be swept after registration has largely stabilised and before Procedure 4 (carry-over) and seeding run, because person merges change which boards and points the carry-over and seeding joins can see. The sweeps are incremental and safe to re-run as late registrations arrive.

First executed for event 143 (Malmesbury, 2026-08-22); the worked session is journalled in design-journal/2026-08/event-143-malmesbury-operations.adoc.

2. Duplicate-order sweep

2.1. Reading sales_order.status

The codes are not first-letter mnemonics. Decode via za.co.idealogic.event.enumeration.OrderStatus: U = UNPAID, D = PENDING, P = PAID, R = REFUNDED, C = CANCELLED. payment_date_time is not a reliable paid indicator — status is the source of truth. Order amounts live on the WooCommerce side; sales_order.number is the WooCommerce order number.

2.2. Detection

Orders link to participants via order_line_item (not event_participant.order_id). One order can cover several siblings, so the unit of action is the order, not the EP.

SELECT ep.id AS ep_id, ep.first_name, ep.last_name,
       COUNT(DISTINCT oli.order_id) AS orders,
       GROUP_CONCAT(DISTINCT CONCAT(so.number,'=',so.status)
                    ORDER BY so.transaction_date_time SEPARATOR ' | ') AS order_detail
FROM event_participant ep
JOIN order_line_item oli ON oli.event_participant_id = ep.id
JOIN sales_order so ON so.id = oli.order_id
WHERE ep.event_id = :EVENT_ID AND ep.active = 1
GROUP BY ep.id, ep.first_name, ep.last_name
HAVING COUNT(DISTINCT oli.order_id) > 1
ORDER BY SUM(so.status='P') DESC, ep.last_name;

2.3. Classification and actions

Pattern Meaning Action

Two (or more) P

Double payment

Refund the later payment on the WooCommerce store, then set the local order to R. Keep the first.

One P + one or more D

Payment retried until one stuck; stale pendings remain

Cancel the pending orders (status='C', cancel_date_time=NOW(3)) — but only when safe (below).

One P + C only

Correct end state

Nothing.

D only / C only

Genuinely unpaid, or no live order at all

Not a duplicate problem. Desk / organiser follow-up. C-only means the EP has no live order.

Two D covering the same children

Tomorrow’s double payment

Consider cancelling the older pending — but a parent may hold the older checkout link.

Safe-to-cancel rule. A pending order may be cancelled only when every line on it is an active EP of the target event that is already covered by a PAID order. A single line for an uncovered sibling, another event, or a non-EP purchase (e.g. membership) disqualifies the order — cancelling it would strand that item. Enforce the rule in the apply query; do not cancel from the detection listing by eye.

Use a tmp_order_cancel_<event> audit table (explicit auto-increment PK — group replication requires one) capturing order_id, order_no, old_status before the update. Worked apply script: .local/wcsc-e143/cancel-stale-pending-orders-143.sql (event 143: 87 orders cancelled, 2026-08-18).

Cancelling the local order does not cancel any WooCommerce-side counterpart. If the parent later pays the store order, the payment callback can resurrect the mismatch. Where possible, cancel unpaid WooCommerce orders for the event at the same time.

After direct SQL, evict caches on both production admin-service instances (DELETE /management/cache-ops/evict-all) — they share the schema but run separate Hazelcast clusters.

3. Duplicate-participant and person-merge sweep

3.1. Detection queries

Run all three; they catch different shapes. Same-person_id duplicates cannot occur (the platform prevents them), so match on names and identity numbers.

(a) Same name within the target event — catches double registrations:

SELECT UPPER(TRIM(ep.last_name)), UPPER(TRIM(ep.first_name)), COUNT(*) eps,
       GROUP_CONCAT(ep.id), GROUP_CONCAT(ep.person_id),
       GROUP_CONCAT(COALESCE(ep.identity_number,'-') SEPARATOR ' | '),
       GROUP_CONCAT(COALESCE(ep.date_of_birth,'-') SEPARATOR ' | ')
FROM event_participant ep
WHERE ep.event_id = :EVENT_ID AND ep.active = 1
GROUP BY 1,2 HAVING COUNT(*) > 1;

(b) Same identity number, different persons, across the current series — the classic re-registration duplicate:

SELECT ep.identity_number, COUNT(DISTINCT ep.person_id) persons,
       GROUP_CONCAT(DISTINCT CONCAT(ep.person_id,':',ep.first_name,' ',ep.last_name) SEPARATOR ' | '),
       GROUP_CONCAT(DISTINCT ep.event_id ORDER BY ep.event_id)
FROM event_participant ep JOIN event e ON e.id = ep.event_id
WHERE e.series_id = :SERIES_ID AND ep.active = 1
  AND ep.identity_number IS NOT NULL AND ep.identity_number <> ''
  AND CHAR_LENGTH(ep.identity_number) = 13
GROUP BY ep.identity_number
HAVING COUNT(DISTINCT ep.person_id) > 1;

(c) Same name, different persons and different identity numbers, across the series — catches the ID-typo class that (b) cannot (a Luhn-valid digit transposition still checksums). Filter survivors by DoB: same DoB is a strong duplicate signal; a 2+ year DoB gap usually means two genuinely different children (verify before dismissing).

3.2. Evidence before merging

A name+DoB match alone is not proof. Corroborate with:

  • wp_usermeta contact details (person_email, contact_number) — same phone in different formats is decisive

  • Order emails (sales_order.email) — a payment for person A’s EP made from person B’s email is decisive

  • School (custom list value) and category progression across events

  • Event interleaving — two person records for one child rarely appear in the same event

Twins produce adjacent-sequence identity numbers with identical DoB — not duplicates. Shared junk identity values (short codes, 0000000 suffixes) across different names are data-quality defects, not duplicates; exclude such values from every identity-based join (P5 recovery, seeding) or the two riders cross-contaminate.

A shared identity is not always obviously junk. At event 143 a well-formed, Luhn-valid SA ID (1404305564083) was held by two person records in the same series category, and passed straight through an exclusion list built for short codes. Hand-maintained exclusions cannot find these. The seeding two-pass below detects them structurally instead — see Seeding score population.

3.3. Merge workflow

Merge via the admin API on the current (v2) instance — never by hand-editing FK rows:

POST /api/admin/person-merge   {"sourcePersonId": <dup>, "targetPersonId": <survivor>}

PersonMergeService migrates 14 FK tables (EPs, boards, tags, results, order lines, …), merges person fields with provenance (a Luhn-valid SA ID wins from either side), soft-deletes and anonymises the source, and writes a person_merge_log row. Two behaviours worth relying on:

  • Same-event EP conflict: if both persons hold an EP in the same event, the source EP’s children are migrated and the source EP is deleted — a double registration collapses to one EP without a separate clean-up step.

  • Denormalised refresh: migrated EPs get identity_number / date_of_birth / age (and name fields) refreshed from the survivor — this is what makes later identity-joined steps (P5, seeding) see one consistent identity.

Direction: prefer the record with the richer history as target, but weigh which account the family actively uses; the identity-field logic keeps the valid ID either way.

External-reference meta rows (epref_rs_*, e.g. EntryNinja ids) are not migrated by the merge. Re-point them to the survivor afterwards: UPDATE wp_usermeta SET user_id = :TARGET WHERE meta_key = 'epref_rs_EN1' AND user_id = :SOURCE;

4. Seeding score population

Seeding writes each participant’s accumulated series points into event_participant.number_seq, which drives start-list order. Full design: design-journal/2026-03/seeding-score-prepopulation.adoc.

4.1. Run it as two passes, person first

Join on person_id first, then on identity_number, with both passes filling only rows where number_seq is still NULL.

A single-key run is wrong in one direction or the other, and the two failure modes are opposites:

Key How it fails alone

identity_number

Over-counts when two person records share an identity — one rider is credited with the other’s points. Also skips entirely any rider whose identity_number is NULL or '' (riders with no SA ID carry id_type OTHER), leaving them on zero.

person_id

Under-counts when one human’s records are fragmented across several wp_users rows — each fragment is scored separately and only one is seen.

Person-first ordering means the more trustworthy key answers wherever it can, and the identity pass only fills what it left behind.

4.2. Why this ordering, from event 143

All four failure shapes occurred in one event:

  • Identity over-counting — William Loftie-Eaton was seeded 124 against 52 earned, because a second person record carrying his ID (and renamed to a parent’s details) contributed 72 points. It resolved not by excluding the ID but by merging the records: the points genuinely were his, so 124 was right all along.

  • Identity skipping — Oliver van Putten (identity_number NULL) and Nicolas Bester ('') were both reported by parents as showing zero points on the start list. They were missed by two different clauses of the same WHERE.

  • Person fragmentation — the same William Loftie-Eaton had three person rows; a person-only pass credited him 52 of 124.

  • Late arrivals — an entry moved into the event after seeding ran had no score. Seeding is incremental, so re-running it close to event day sweeps these up.

4.3. Verify with the disagreement detector

After seeding, re-derive every seeded row’s total by the other key and list the rows that disagree:

-- rows where the person-derived total differs from the stored seed
SELECT ep_t.id, ep_t.first_name, ep_t.last_name, ep_t.number_seq AS seed, p.total_points AS by_person
FROM event_participant ep_t
JOIN event_category ec_t ON ec_t.id = ep_t.category_id
LEFT JOIN ( /* SUM(points) per person_id + series_category over prior events */ ) p
       ON p.person_id = ep_t.person_id AND p.series_category_id = ec_t.series_category_id
WHERE ep_t.event_id = :EVENT AND ep_t.active = 1 AND ep_t.number_seq IS NOT NULL
  AND ep_t.number_seq <> COALESCE(p.total_points, 0);
This is a detector, not a correction. Every hit means a person record is either shared or fragmented, and which side is broken is a human judgement. At event 143 the single hit looked like an inflated seed; it was actually a fragmented person, and the seed was correct. Do not "fix" a disagreement by trusting one key — resolve the underlying person records (merge them), then re-run and confirm the detector returns zero rows.

A clean run returns no rows, which is also the state in which either key would have been safe.

5. Ordering relative to other pre-event steps

  1. Duplicate-order sweep (independent, any time)

  2. Person merges — must precede carry-over and seeding

  3. Procedures 4 and 5 (carry-over + identity recovery)

  4. Seeding score population — two passes, person_id then identity_number

All steps are incremental (they touch only unassigned/unseeded rows), so the sequence can be re-run close to event day to sweep up late registrations.

6. References

  • Number & Tag Operational Runbook — Procedures 1–5

  • design-journal/2026-08/event-143-malmesbury-operations.adoc — first worked execution (event 143), including the order-sweep classification for 96 EPs and seven person merges

  • admin-service PersonMergeService / PersonMergeResource — merge semantics

  • database za.co.idealogic.event.enumeration.OrderStatus — status code decode

  • ADO Bug #991 — prefix-aware bib matching on result import/export

  • ADO Bug #995 — person merge blanks the survivor’s user_email when the source’s is empty; check the survivor after each merge until fixed

  • .local/wcsc-e143/e143-seeding-part1b-two-pass.sql — reference implementation of the two-pass seeding described above