Database
Manage DB
Browse collections and tables, and watch the CQRS timing pipeline.
Person External Profilesperson_external_profiles
External platform accounts (UltraSignup, Strava, DUV, ITRA) linked to an individual person.
π‘
Operational Purpose & Edge Invariants
Maintains authenticated or discovered external profiles across endurance and timing services. Tracks verification state (auto-matched vs organizer-confirmed), scraping status, match confidence percentage, and raw external API/DOM payloads.
Relational Connections (2)
Table Columns & Types (14)
person_external_profiles| Column | PostgreSQL Type | Constraints | Default | Documentation |
|---|---|---|---|---|
| id | uuid | NOT NULLPK | gen_random_uuid() | Unique external profile identifier. |
| personId | uuid | NOT NULLFK -> people.idON DELETE CASCADE | β | Reference to the local person. |
| platform | externalPlatformEnum | NOT NULLEnum | β | Target platform service (ultrasignup, strava, duv, itra, athlinks, custom). |
| externalIdentifier | text | NOT NULLUnique with platform | β | Platform-specific identifier (e.g. UltraSignup athlete ID or Strava athlete numeric ID). |
| profileUrl | text | nullable | β | Direct canonical HTTP URL to the athlete profile page. |
| verificationStatus | profileVerificationStatusEnum | NOT NULLEnum | 'unverified' | Verification state: unverified, candidate_matched, auto_verified, manual_verified, rejected, conflict. |
| matchConfidence | double precision | nullable | β | Computed Multi-Factor Disambiguation Algorithm (MFDA) score (0.0 - 100.0). |
| matchMetadata | jsonb | nullable | β | Sub-score breakdowns (name Levenshtein, age delta, geography, phonetic score). |
| scrapeStatus | scrapeStatusEnum | NOT NULLEnum | 'pending' | Queue worker lifecycle state: pending, queued, in_progress, succeeded, failed, rate_limited, stale. |
| lastScrapedAt | timestamp with time zone | nullable | β | Timestamp of last successful crawl or API fetch. |
| rawPayload | jsonb | nullable | β | Unmodified JSON response snapshot or parsed DOM tree for debugging. |
| errorMessage | text | nullable | β | Last ingestion failure error message or HTTP status code. |
| createdAt | timestamp with time zone | NOT NULL | now() | Creation timestamp. |
| updatedAt | timestamp with time zone | NOT NULL | now() | Last record update timestamp. |
PostgreSQL Enums (3)
enum "external_platform"
| Value | Label | Domain Meaning |
|---|---|---|
| 'ultrasignup' | UltraSignup | North American trail and ultramarathon registration and results database. |
| 'strava' | Strava | Social fitness telemetry, GPS elevation, and training load API. |
| 'duv' | DUV Ultra Marathon | Deutsche Ultramarathon-Vereinigung international ultra statistics. |
| 'itra' | ITRA | International Trail Running Association performance index and points. |
| 'athlinks' | Athlinks | Multi-sport mass-participation road race database. |
| 'custom' | Custom / Manual | Custom platform or organizer-supplied external link. |
enum "profile_verification_status"
| Value | Label | Domain Meaning |
|---|---|---|
| 'unverified' | Unverified | Newly discovered profile awaiting disambiguation. |
| 'candidate_matched' | Candidate Matched (60-84%) | Probable match requiring organizer confirmation in Steward Admin. |
| 'auto_verified' | Auto-Verified (>= 85%) | High-confidence deterministic match verified by MFDA algorithm. |
| 'manual_verified' | Manual Verified | Explicitly confirmed or bound by race director / timing official. |
| 'rejected' | Rejected / Blacklisted | Excluded match candidate confirmed not to be this athlete. |
| 'conflict' | Identity Conflict | Multiple candidates or conflicting historical claims requiring manual resolution. |
enum "scrape_status"
| Value | Label | Domain Meaning |
|---|---|---|
| 'pending' | Pending | Awaiting enqueue into worker fetch queue. |
| 'queued' | Queued | Enqueued in Redis BullMQ fetch queue. |
| 'in_progress' | In Progress | Currently being fetched by Railway background worker. |
| 'succeeded' | Succeeded | Payload successfully retrieved and race history records parsed. |
| 'failed' | Failed | Scrape attempt failed after retries. |
| 'rate_limited' | Rate Limited (429) | Worker received rate limit backoff signal; leased back with delay. |
| 'stale' | Stale | Profile has not been updated within freshness window. |
Database Indexes (3)
| Index Name | Columns | Type | Query Performance Rationale |
|---|---|---|---|
| person_external_profiles_platform_identifier_idx | (platform, externalIdentifier) | UNIQUE | Guarantees external platform identities are unique across the tenant database. |
| person_external_profiles_person_idx | (personId) | INDEX | Accelerates lookups of all external profiles for a given person. |
| person_external_profiles_scrape_status_idx | (scrapeStatus) | INDEX | Optimizes background worker polling for pending and queued scraping tasks. |