Database
Manage DB
Browse collections and tables, and watch the CQRS timing pipeline.
Person Historical Summariesperson_historical_summaries
Materialized 1:1 career intelligence rollup for a person, computing experience tier and DNF risk.
π‘
Operational Purpose & Edge Invariants
Rolls up individual race history into career totals, DNF rates, max distances climbed, and external ranks (UltraSignup %, ITRA score). Classifies athletes into experience tiers (novice, intermediate, veteran, elite) and powers SAR triage alerts.
Relational Connections (1)
Table Columns & Types (16)
person_historical_summaries| Column | PostgreSQL Type | Constraints | Default | Documentation |
|---|---|---|---|---|
| id | uuid | NOT NULLPK | gen_random_uuid() | Unique historical summary identifier. |
| personId | uuid | NOT NULLFK -> people.idON DELETE CASCADEUNIQUE | β | Reference to the person (strict 1:1 relationship). |
| totalRaces | integer | NOT NULL | 0 | Total recorded career race starts. |
| totalFinishes | integer | NOT NULL | 0 | Total verified career race finishes. |
| totalDnfs | integer | NOT NULL | 0 | Total career Did Not Finish (DNF) results. |
| dnfRate | double precision | NOT NULL | 0 | Computed career DNF ratio (0.0 to 1.0). |
| maxDistanceMeters | double precision | NOT NULL | 0 | Longest single race distance completed in meters. |
| maxElevationGainMeters | double precision | NOT NULL | 0 | Maximum positive vertical ascent completed in meters. |
| avgPaceSecondsPerKm | double precision | nullable | β | Weighted career baseline pace in seconds per kilometer. |
| experienceTier | experienceTierEnum | NOT NULLEnum | 'novice' | Classified experience tier: novice, intermediate, veteran, elite. |
| ultrasignupRank | double precision | nullable | β | Official UltraSignup score percentile (e.g. 88.42%). |
| itraPerformanceIndex | integer | nullable | β | Official ITRA general performance index (0 - 1000 scale). |
| lastRaceDate | text | nullable | β | ISO date string of most recently completed event. |
| confidenceScore | double precision | NOT NULL | 1.0 | Data completeness and verification confidence weight (0.0 - 1.0). |
| safetyTriageNotes | text | nullable | β | Automated medical or SAR warnings (e.g. High DNF risk, Low altitude origin). |
| computedAt | timestamp with time zone | NOT NULL | now() | Timestamp when this intelligence rollup was computed. |
PostgreSQL Enums (1)
enum "experience_tier"
| Value | Label | Domain Meaning |
|---|---|---|
| 'novice' | Novice | 0 to 2 career finishes; first-timer at distance. |
| 'intermediate' | Intermediate | 3 to 7 career finishes with consistent pacing. |
| 'veteran' | Veteran | 8+ career finishes; proven mountain ultra experience. |
| 'elite' | Elite | Top-tier competitor (UltraSignup >= 90%, ITRA >= 750). |
Database Indexes (2)
| Index Name | Columns | Type | Query Performance Rationale |
|---|---|---|---|
| person_historical_summaries_person_idx | (personId) | UNIQUE | Guarantees a strict 1:1 summary relation per person. |
| person_historical_summaries_tier_idx | (experienceTier) | INDEX | Optimizes cohort filtering by athletic experience tier. |