Database
Manage DB
Browse collections and tables, and watch the CQRS timing pipeline.
Raw Timing Eventsraw_timing_events
High-frequency ingestion ledger recording raw timing pulses in UTC with device telemetry.
π‘
Operational Purpose & Edge Invariants
The Write Plane of ChekkPoint's CQRS architecture. Every hardware pulse, optical beam trip, manual volunteer entry, ham radio acoustic packet, or offline PWA sync is appended here with absolute UTC precision (millisecond resolution). Raw records are immutable: they are never updated or deleted. Reconciliation workers derive standings and mark status to 'matched', 'duplicate_subordinate', or 'rejected_invalid' without altering raw telemetry.
Relational Connections (2)
Table Columns & Types (15)
raw_timing_events| Column | PostgreSQL Type | Constraints | Default | Documentation |
|---|---|---|---|---|
| id | uuid | NOT NULLPK | gen_random_uuid() | Unique raw timing event identifier. |
| eventGroupId | uuid | NOT NULLFK -> eventGroups.id | β | Weekend event group. |
| competitorNumber | text | NOT NULL | β | Bib / Plate / Car number passed into reader. |
| timingPointCode | text | NOT NULL | β | Station code (e.g. "TWIN_LAKES"). |
| subSplitKind | subSplitKindEnum | NOT NULLEnum | β | 'in' (arrival), 'out' (departure), or 'point' (flying line). |
| recordedTimeUtc | timestamp(3) with time zone | NOT NULL | β | Precision 3 (millisecond) absolute UTC pulse timestamp. |
| source | timingSourceEnum | NOT NULLEnum | β | Hardware telemetry origin (rfid, optical, manual, mobile app, ham radio, webhook). |
| sourceDeviceId | text | nullable | β | Reading hardware identifier (e.g. "ipad-station-4"). |
| recordedByPersonId | uuid | nullableFK -> people.id | β | Person who entered the time. Null for unattended sources; radio operator callsigns are kept in rawPayload. |
| syncBatchId | uuid | nullable | β | Batch UUID for offline IndexedDB bulk sync sessions. |
| withPacer | boolean | NOT NULL | false | Indicates runner was accompanied by a registered pacer. |
| rawPayload | jsonb | nullable | β | Original hardware telemetry packet (RSSI, signal strength, antenna port). |
| status | rawTimingStatusEnum | NOT NULLEnum | 'unprocessed' | Pulse validation lifecycle: 'unprocessed', 'matched', 'duplicate_subordinate', 'rejected_invalid', 'flagged_discrepancy'. |
| auditNote | text | nullable | β | Official explanation if marked invalid or flagged. |
| createdAt | timestamp with time zone | NOT NULL | now() | Ingestion timestamp. |
PostgreSQL Enums (3)
enum "sub_split_kind"
| Value | Label | Domain Meaning |
|---|---|---|
| 'in' | In (Arrival) | Checkpoint entry; aid station dwell start. |
| 'out' | Out (Departure) | Checkpoint exit; cutoff enforcement line. |
| 'point' | Point (Flying Gate) | Instantaneous single flying gate / intermediate mat. |
enum "timing_source"
| Value | Label | Domain Meaning |
|---|---|---|
| 'rfid_transponder' | RFID Transponder | Automated chip mat (RaceResult, ChronoTrack). |
| 'optical_photocell' | Optical Photocell | Laser beam photocell gate (downhill MTB finish). |
| 'manual_web_entry' | Manual Web Entry | Volunteer checkpoint web console. |
| 'mobile_offline_app' | Mobile Offline PWA | Field volunteer tablet caching in IndexedDB. |
| 'mt63_ham_radio' | MT63 Ham Radio | Digital acoustic modem packet over VHF/UHF amateur radio. |
| 'api_webhook' | API Webhook | External timing service API integration. |
enum "raw_timing_status"
| Value | Label | Domain Meaning |
|---|---|---|
| 'unprocessed' | Unprocessed | Ingested, awaiting background worker scoring. |
| 'matched' | Matched | Successfully bound to an entrant scored split. |
| 'duplicate_subordinate' | Duplicate Subordinate | Subordinate backup pulse within cluster window. |
| 'rejected_invalid' | Rejected Invalid | Steward invalidated entry (e.g. false trigger, typo). |
| 'flagged_discrepancy' | Flagged Discrepancy | Variance with primary hardware exceeds threshold. |
Database Indexes (4)
| Index Name | Columns | Type | Query Performance Rationale |
|---|---|---|---|
| raw_events_eg_comp_idx | (eventGroupId, competitorNumber) | INDEX | Accelerates chronological athlete pulse extraction for scoring reconciliation. |
| raw_events_time_idx | (recordedTimeUtc) | INDEX | Optimizes time-window replay and telemetry streaming queries. |
| raw_events_batch_idx | (syncBatchId) | INDEX | Enables fast audit tracking of offline batch sync sessions. |
| raw_events_status_idx | (status) | INDEX | Allows worker queues to rapidly poll for 'unprocessed' records. |