[E11] Export Timing System CSV

Summary

CSV export targeting the timing-system import format. Replaces the current direct-SQL workflow with a first-class endpoint that:

  1. Emits a narrow canonical column set the timing system expects.

  2. Populates the ExternalReferenceID column per the Event’s preferredTimingIdentifier setting.

  3. Locks Event.preferredTimingIdentifier on first call (with explicit-override path) so subsequent timing-system upserts key off a stable identifier.

The round-trip is closed by E08: the same ExternalReferenceID value comes back from the timing system in the result file and resolves the EP via the matching mode.

Actor & Context

Actor: event organiser, results officer, tenant admin. Frequency: typically once per event (before race day), occasionally a second time if late additions come in. Precondition: event exists; EPs are populated; user has EVENT_MANAGER or RESULTS_OFFICER permission. Entry point: Export to timing system button on E03 Event Setup or E04 Event Results; from E05 event tile menu.

Main Flow

  1. User opens E03 / E04 / E05 and clicks Export to timing system.

  2. UI renders an Export options modal:

    • Identifier mode — read-only label sourced from Event.preferredTimingIdentifier. If _locked_at is null (first export), shows an editable dropdown (EPID / PERSON_ID / REGISTRATION_ID); confirming locks the choice.

    • Audit confirmation — checkbox "I understand the timing system will use this identifier as its stable foreign key for this event." Required; export disabled until ticked.

  3. User clicks Generate CSV.

  4. Browser triggers GET /api/events/{id}/timing-export?format=csv.

  5. Backend:

    • Reads Event.preferredTimingIdentifier (lock if first call by setting _locked_at = NOW()).

    • Streams CSV with the timing-system canonical columns (Number / Name / Category / ExternalReferenceID / etc.).

    • Writes audit log entry: (eventId, timestamp, identifierMode, rowCount, userId).

  6. File downloads; user uploads to timing system per their workflow.

Alternative Flows

  • AF-1 — _locked_at already set, operator wants a different mode. UI shows the locked label with Unlock to change affordance. Click triggers a confirmation modal explaining the consequence (timing-system upsert key drift → potential duplicate ExternalReferenceID rows on their side). Confirm sets _locked_at = NULL, audit-logs the override + previous value.

  • AF-2 — selected mode cannot produce an identifier for every EP. Applies to REGISTRATION_ID with a null registrationId, and to EXTERNAL_PERSON_UID where the event has no source registration system or an EP has no reference in it. The export is refused with 409 Conflict, naming the mode, the affected count and a sample of EP ids. Operator either (a) assigns the missing identifiers, or (b) switches mode (overrides lock). Proceeding with blanks is not offered: a blank ExternalReferenceID is a row that silently fails to match on the way back, which is the failure this use case exists to prevent.

  • AF-3 — Empty result set. Endpoint returns header-only CSV.

  • AF-4 — Permission denied. 403, UI button disabled.

Acceptance Criteria

  • Endpoint GET /api/events/{id}/timing-export?format=csv returns a valid CSV stream.

  • Permission: EVENT_MANAGER or RESULTS_OFFICER on the event’s organisation.

  • First call sets Event.preferred_timing_identifier_locked_at = NOW() (per F12 US #697).

  • ExternalReferenceID column populated per the locked identifier mode (EP.id / EP.person.id / EP.registrationId / the person’s UID in the event’s source registration system).

  • Audit log entry written per export call.

  • AF-2 refusal surfaces correctly (REGISTRATION_ID + null registrationId; EXTERNAL_PERSON_UID + missing reference).

  • Override path requires explicit confirmation; previous value captured in audit log.

  • Round-trip integration test: timing-export → simulated timing-system result → E08 import resolves all EPs via the same mode.

API Surface

Call Purpose

GET /api/events/{id}/timing-export?format=csv

Stream the timing-system CSV. Sets _locked_at on first call.

GET /api/events/{id}

Reads preferredTimingIdentifier and preferred_timing_identifier_locked_at for the modal.

PATCH /api/events/{id} (field: preferredTimingIdentifier, with acknowledgeOverride=true)

Override the locked identifier mode. Audit-logged; previous value retained.

Out of Scope

  • Bi-directional timing-system sync — different problem class, see design-journal/2026-01/bi-directional-data-sync.adoc.

  • Direct push to timing-system API — file-based handoff only for v1.

  • Multi-leg / multi-result-per-EP timing round-trip (Bug #417) — separate.

  • Timing-system result import — that’s E08.

Design Anchors

  • Portal Pattern

  • UI Design Principles

  • design-journal/2026-05/participant-identity-correlation.adoc — canonical design (F12 US #697, F13 US #698)

  • design-journal/2026-04/result-import-improvements.adoc — adjacent identifier-mode work (ParticipantIdMode enum)

  • E08 — round-trip target (the same ExternalReferenceID comes back here)

  • E03 / E04 — natural callers

Design Decisions

  • Four identifier modes (three 2026-05-04; EXTERNAL_PERSON_UID added later by US #769 and made exportable by Bug #1139). Per design-journal/2026-05/participant-identity-correlation.adoc § Decisions Made (Timing round-trip):

    • EPID — EP.id. Stable, internal, opaque. Use when our system is the system of record for the event lifecycle and there’s no upstream identifier to honour.

    • PERSON_ID — EP.person.id. Default for new events. Stable across multiple events for the same person — useful when timing system runs multi-event series and wants person continuity.

    • REGISTRATION_ID — EP.registrationId. The source registration system’s EP UID. Use when EPs were imported from an external system and the timing system should round-trip their identifier back. Often paired with WPCA Legacy / Entry Ninja imports.

    • EXTERNAL_PERSON_UID — the person’s UID in the event’s sourceRegistrationSystem, via PersonExternalReference. Use for cross-system person correlation, as distinct from REGISTRATION_ID’s per-entry identifier. Requires the event to carry a source registration system. See `common-design:imports/participant-identity.adoc for the Person-XID vs Participant-XID distinction.

  • Set-once-with-override on Event.preferredTimingIdentifier (2026-05-04). Per F12 (US #697). DB column is mutable; UI guard is software-level. Locked on first timing-export call. Override path requires explicit confirmation explaining the consequence (timing-system upsert key drift → potential duplicate ExternalReferenceID rows on their side); audit-logged with previous value. Rationale: changing identifier mid-event after the timing system has rows for the prior identifier creates duplicate-foreign-key chaos on their side that we can’t reverse from our side.

  • AF-2 refuses rather than warns (revised 2026-09-07, Bug #1139; originally 2026-05-04 as a warning). Per F13 (US #698). An incomplete identifier set fails the export with 409 Conflict instead of emitting blanks. The original design offered "proceed with explicit acceptance", but there is no modal and no acknowledge parameter through which acceptance can be expressed, and a blank identifier is indistinguishable at import time from a mode mismatch. Avoids silent corruption of the timing system’s foreign-key set.

  • File-based handoff, no direct push (2026-05-04). v1 ships file-download only. Direct push to timing-system APIs is a future enhancement; current operational reality is the operator manually uploads via the timing-system UI, and that workflow is well-understood. Keeps blast radius small; we avoid taking a hard dependency on any specific timing-system API surface.

  • Modal-based mode lock confirmation (2026-05-04). The "I understand the timing system will use this identifier as its stable foreign key for this event" confirmation is mandatory on first export to avoid operators accidentally locking-in a poor choice. Designed for operator clarity; backend enforces the lock regardless.

Notes

Today’s workflow is direct SQL → manual CSV. Operators have no in-system audit trail of what was exported when, with which identifier mode. The 2026-04-22 wp_users incident bookended both ends of this gap (no first-class import tool, no first-class export tool, ambiguous identifier semantics across both); this .adoc closes the export side. E10 closes the human-Excel/re-import side.

Backend changes scoped under design-journal/2026-05/participant-identity-correlation.adoc:

  • F12 (US #697) — Event.preferredTimingIdentifier UI gate (set-once with override, audit-logged)

  • F13 (US #698) — Timing-system export endpoint

  • F14 (US #699) — Result import alignment with Event.preferredTimingIdentifier (round-trip closure)

As-built status (2026-09-07, Bug #1139)

This page specifies GET /api/events/{id}/timing-export?format=csv. What actually ships is GET /api/events/{eventId}/timing-feed, delivered under WS1b (US #476) before this page was written, and documented in procedures:number-tag-api-guide.adoc. This page was authored to replace that endpoint; until F13 lands, timing-feed is the route in production use.

F14 (the import half) shipped while F13 (the export half) did not, and that asymmetry was the defect. The import began honouring Event.preferredTimingIdentifier while the export stayed hardwired to EP.id — so for any event on the PERSON_ID default the two ends disagreed, and because event_participant.id and wp_users.ID are independent sequences in the same numeric range, the mismatched lookup found a row. Bug #1139 measured 313 of 313 results resolving to a real but wrong person on a production event, with no error raised. Shipping one half of a round-trip contract is worse than shipping neither half: before F14, both ends agreed on EPID by accident.

Bug #1139 brought three items of this specification forward onto timing-feed:

  • the identifier selection (all four modes),

  • the first-export lock on preferred_timing_identifier_locked_at,

  • AF-2, revised from a warning to a refusal.

F13 (US #698) therefore retains: the route rename to timing-export, the export-options modal with its identifier dropdown and audit confirmation, the AF-1 unlock-to-change path, and the per-export audit-log entry. The status stays design-todo on that remaining scope.