[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 — REGISTRATION_ID mode + EPs with null registrationId. Pre-export validation surfaces a warning listing affected EPs. Operator either (a) cancels and assigns registrationId per EP, (b) switches mode (overrides lock), or (c) proceeds (those rows export with blank ExternalReferenceID and won’t match on import — explicit acceptance).

  • 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).

  • Audit log entry written per export call.

  • AF-2 warning surfaces correctly (REGISTRATION_ID + null registrationId).

  • 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 Bi-Directional Data Sync.

  • 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

  • Three identifier modes (2026-05-04). Per Participant Identity Correlation § 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.

  • 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 warning for REGISTRATION_ID + null registrationId (2026-05-04). Per F13 (US #698). Pre-export validation lists affected EPs and lets the operator choose to assign registrationId per EP, switch mode, or proceed (with explicit acceptance that those rows won’t round-trip-match). 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 Participant Identity Correlation:

  • 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)