ApexLab — Data Dictionary

This document traces every piece of data in the system: what it is, why it exists, where it comes from, how it is transformed, where it is stored, and when it is read or deleted.

---

Contents

1. Telemetry Data (live capture)
2. Session & Lap containers (in-memory)
3. Analysis Data (computed, in-memory)
4. Local Storage — Session Catalog (SQLite)
5. Local Storage — Session Files (CSV)
6. Local Storage — Track Profiles (SQLite)
7. Local Storage — User Settings (JSON)
8. Local Storage — Auth Tokens (encrypted file)
9. API — Users table
10. API — Sessions table
11. API — Laps table
12. API — Tracks table
13. API — Track Versions table
14. API — Leaderboard Entries table
15. API — User Achievements table
16. API — Feed Entries table
17. API — Refresh Tokens table
18. API — Outbox Messages table
19. API — Track Processing Jobs table
20. Auth & JWT Tokens
21. Redis Cache
22. Transport DTOs (App ↔ API)
23. Data Lifecycle Summary

---

1. Telemetry Data (live capture)

What it is: A stream of point-in-time snapshots of the car's state, sampled at 40 Hz during live recording. Each sample is one TelemetryPoint.

Where it comes from: Simulator shared memory (LMU/rF2, ACC, iRacing, AMS2) or UDP packets (AC Classic, F1, Forza), read by the respective IGameLiveSampler implementation.

TelemetryPoint fields

Field	Type	Unit	Why it exists	How it's used
Distance	float	metres	Primary axis for lap comparison — distance is stable across laps; time is not	Key used in LapResampler to align two laps at identical track positions; x-axis on all telemetry plots
Time	float	seconds since lap start	Absolute time reference for lap duration	Lap time derivation; cumulative time calculation in delta
Speed	float	km/h	Core performance metric	Speed comparison plot; slow corner exit detection; minimum speed threshold for delta accumulation
Throttle	float	0.0–1.0	Measures driver input (power application)	Throttle comparison plot; late throttle detection; input stability score
Brake	float	0.0–1.0	Measures driver input (deceleration)	Brake comparison plot; brake-too-early detection
Steering	float	degrees (positive = any direction)	Identifies corners vs. straights	TrackSegmentation two-threshold hysteresis; corner detection
Gear	int	gear number	Vehicle context for insights	Stored in CSV; available for future analysis (not used in V1 engine)
X, Y	float	world-space units	2D car position on track	Track outline SVG generation; track processing pipeline (centerline fusion); spatial cursor sync on map
Extras	IReadOnlyDictionary<string,float>?	varies	Extension point for game-specific channels (e.g. tyre temps, fuel load)	Not consumed by V1 analysis engine; preserved in memory, not written to CSV

Transformation chain:

Simulator SHM / UDP
  → IGameLiveSampler.TrySample()           raw game struct
  → LmuLiveRecorder / GenericLiveRecorder  accumulate into buffer[]
  → Lap(lapIndex, points[], lapTimeSeconds) on lap counter change
  → CsvTelemetryWriter                     write to disk as CSV
  → [read back by] CsvTelemetryReader      restore as TelemetryPoint[]
  → [consumed by] LapResampler             resample to N=512 distance points
  → ResampledLapComparison                 distance, speed, throttle, brake, delta arrays

Storage: Written to %LocalAppData%\ApexLab\Sessions\sess_{guid}.csv. Never uploaded to the API — only summaries (lap times, quality scores) go to the cloud.

Lifetime: Permanent on local disk. Never automatically deleted (no retention policy for local CSV files in V1).

---

2. Session & Lap containers (in-memory)

These are the in-memory representations after reading a CSV file. They are never persisted directly — SessionData is reconstructed from CSV on demand.

SessionData

Field	Type	Where it comes from	Why it exists
Laps	IReadOnlyList<Lap>	CSV reader builds Lap objects on lap-counter changes	Container for all lap data; source for analysis
SourcePath	string?	Set by CSV reader to the file path	Allows the UI to show file location and re-read if needed
Metadata	TelemetrySessionMetadata?	Parsed from CSV comment headers	Track name, vehicle, weather — shown in UI, uploaded to API, cached in SQLite catalog
BestValidLap	computed	Derived from Laps filtered by IsValid && CountsForSessionTiming, sorted by LapTimeSeconds	Used as the default reference lap; leaderboard submission target

TelemetrySessionMetadata

Field	Type	Where it comes from	Why it exists
TrackName	string?	CSV # Track: header or SHM	Identifies the venue; used for catalog search, leaderboard grouping, API session upload
TrackLayout	string?	CSV # TrackLayout: header	Distinguishes circuit variants (e.g. "GP" vs "Full"); part of the (game, track, layout) unique key on the leaderboard
VehicleName	string?	CSV # Vehicle: header	Optional leaderboard filter; shown in UI
GameSourceId	GameSourceId	CSV # GameSource: header	Identifies simulator; drives connector selection
RecordedAt	DateTimeOffset?	CSV # Recorded: header	Session timestamp shown in catalog; sent to API as recorded_at
Weather	WeatherSnapshot?	CSV # WeatherAmbientC: / # WeatherTrackC:	Context for AI coach; displayed in session overview; achievement triggers (e.g. "night race")
SetupSnapshot	SetupSnapshot?	SHM (TC, ABS, brake balance, map)	Available for future coaching features; not used in V1 analysis

Lap

Field	Type	Where it comes from	Why it exists
LapIndex	int	CSV Lap column change counter (0-based)	Ordering; display in lap list
Points	IReadOnlyList<TelemetryPoint>	All rows with the same lap index	The full telemetry for this lap — source for all analysis
LapTimeSeconds	float	Derived: OfficialLapTime column → Time delta → speed/distance estimate	Used for lap ranking, delta calculation, API upload
IsValid	bool	CSV OfficialLapTime > 0.05s heuristic; or game validity flag	Filters out installation laps, pit exits, damaged laps from best-lap calculations
CountsForSessionTiming	bool	CSV InPits column or game scoring flag	Separates outlaps / inlaps from race/quali laps; only true laps contribute to best time
LapTimeSource	LapTimeSource	Enum set by CSV reader based on derivation path	Audit trail — lets UI/analysis know how reliable the lap time is
ScoringNumPenalties	short?	CSV NumPenalties column	Available for leaderboard quality gating; uploaded to API
ScoringNumPitstops	short?	CSV NumPitstops column	Flags stop-go or drive-through events
ScoringInPitsAtLapEnd	bool?	CSV InPits column	Marks pit laps; propagates to CountsForSessionTiming = false
LapLengthMeters	computed	Points[^1].Distance	Track length for display; used by analysis to validate lap completeness

---

3. Analysis Data (computed, in-memory)

All analysis data is computed on the fly by AnalysisEngine.Analyze() and held in AnalysisState. It is never persisted — it is recomputed whenever the lap selection changes.

ResampledLapComparison

Field	Type	How it's computed	Where it's used
DistanceMeters	double[N]	Uniform grid from 0 to lap length (N = AnalysisOptions.ResamplePointCount, default 512)	X-axis for all ScottPlot curves
SpeedReference / SpeedCurrent	float[N]	Linear interpolation of each lap's speed at each grid distance	Speed comparison curve
ThrottleReference / ThrottleCurrent	float[N]	Linear interpolation of throttle	Throttle comparison curve
BrakeReference / BrakeCurrent	float[N]	Linear interpolation of brake	Brake comparison curve
CumulativeTimeReference / CumulativeTimeCurrent	double[N]	Integrated time along distance (only accumulated when speed > MinSpeedForDeltaMs)	Input to delta calculation
DeltaSecondsCurrentMinusRef	double[N]	CumulativeTimeCurrent[i] - CumulativeTimeReference[i]	Delta curve (positive = current losing time); colored by sign on track map

AnalysisInsight

Field	Why it exists	How it's generated
Code (InsightCode enum)	Stable key for localization and filtering	Emitted by each detector in AnalysisEngine (e.g. BrakeTooEarly, SlowCornerExit)
Message	Localized French description shown in UI	Formatted string from I18n.cs with numeric detail embedded
Severity (Error/Warning/Info)	Controls visual emphasis and sort order	Set per insight type (e.g. BrakeTooEarly = Warning, ResampleFailed = Error)
AtDistance	Track position for map highlighting	Distance in metres where the insight occurs
SegmentOrdinal	Links insight to a specific corner	Corner number from TrackSegmentation
DetailValue	Numeric metric for the insight	e.g. metres of brake offset, km/h speed deficit

TrackSegment

Field	Why it exists
Kind (Corner/Straight)	Drives corner numbering and analysis scope
Ordinal	Human-readable corner/straight number (1-based, separate sequences)
DistanceStart / DistanceEnd	Bounds for filtering telemetry points to this segment

---

4. Local Storage — Session Catalog (SQLite)

File: %LocalAppData%\ApexLab\sessions.db

Why it exists: CSV files contain all data but reading them is slow. The catalog caches key metadata so the session list UI loads instantly without re-parsing every file.

sessions table

Column	Type	Why it exists	How it's set	When it changes
id	INTEGER PK AUTOINCREMENT	Stable local key for settings (LastSessionCatalogId)	Auto-assigned on insert	Never
track	TEXT	UI display; search filter	From TelemetrySessionMetadata.TrackName on import	On UpdateSessionSnapshot() if metadata improves
vehicle	TEXT	UI display; search filter	From TelemetrySessionMetadata.VehicleName	On snapshot update
file_path	TEXT UNIQUE	Locates the CSV file for loading	Set at RegisterFile() time; unique constraint prevents duplicates	Never (file moves break the link)
imported_at	TEXT	Sort order; "catalogued" timestamp in UI	DateTime.UtcNow at RegisterFile()	Never
notes	TEXT	User-authored session notes	Set on import dialog; editable from session detail	On user edit
game	TEXT	Game filter in session library	From GameSourceId.ToString()	On snapshot update
is_favorite	INTEGER (0/1)	★ filtering and display	Set via ToggleFavorite() command	On user toggle
tags	TEXT	Future tag-based filtering (V2)	Not yet populated	Reserved
lap_count	INTEGER	Shown in session list without reading CSV	From SessionData.Laps.Count on import	On snapshot update
metadata_json	TEXT	Full TelemetrySessionMetadata for dashboard subtitle and weather display	JSON-serialized on import	On snapshot update
best_lap_seconds	REAL	Personal best comparison; shown as subtitle	From SessionData.BestValidLap.LapTimeSeconds	On snapshot update
synced_at	TEXT	Indicates whether session has been uploaded to cloud	Set by SyncService.MarkSynced() after successful API upload	Once, on first successful sync
cloud_session_id	TEXT	Required to submit this session to the leaderboard	Returned by POST /v1/sessions; stored by MarkSynced()	Once, on sync

---

5. Local Storage — Session Files (CSV)

Location: %LocalAppData%\ApexLab\Sessions\sess_{guid}.csv

Why it exists: Full telemetry cannot be reconstructed from summaries. The CSV is the ground truth for all analysis and playback.

Comment header (metadata lines before the column header)

Header	Example	Why it's written
# Track:	Spa-Francorchamps	Human readability; re-read by CsvTelemetryReader to populate TelemetrySessionMetadata
# TrackLayout:	Full	Distinguishes circuit variants
# Vehicle:	Ferrari 499P	Session context for coaching
# Recorded:	2026-04-19T20:00:00Z	Session timestamp
# Source:	ApexLab 1.0	App version that wrote the file (audit)
# GameSource:	LmuRf2	Which connector produced this data
# WeatherAmbientC:	18.5	Ambient temperature (°C)
# WeatherTrackC:	32.1	Track surface temperature (°C)

Data columns

Column	Type	Required?	Why it exists
Time	float (seconds)	Yes	Lap time derivation; cumulative time for delta
Distance	float (metres)	Yes	Primary alignment axis for lap comparison
Speed	float (km/h)	Yes	Core performance metric
Throttle	float (0–1)	Yes	Driver input analysis
Brake	float (0–1)	Yes	Driver input analysis
Steering	float (degrees)	Yes	Corner detection
Gear	int	Yes	Vehicle context
X	float	Yes	Track map rendering; centerline fusion
Y	float	Yes	Track map rendering; centerline fusion
Lap	int	Yes	Lap boundary detection (changes = new lap)
OfficialLapTime	float (seconds)	Optional	Best-quality lap time source; preferred over time-delta method
NumPenalties	int	Optional	Uploaded to API as lap quality context
NumPitstops	int	Optional	Flags pit stops within the lap
InPits	int (0/1)	Optional	Marks pit laps; sets CountsForSessionTiming = false

---

6. Local Storage — Track Profiles (SQLite)

File: %LocalAppData%\ApexLab\track_profiles.db

track_profiles table

Column	Why it exists
slug (PK)	URL-safe identifier for the track (e.g. spa-lmu-complete); used as SVG cache key
game	Which game the profile came from
layout_label	Track variant label
geometry_hash	Hash of the track centerline; changing it invalidates the SVG cache
track_name_hint	Original game name for display
created_at_utc	Audit
notes	Admin notes

Why this is separate from sessions.db: Track profiles represent canonical track geometry, not user sessions. They are shared across all sessions on the same track.

---

7. Local Storage — User Settings (JSON)

File: %LocalAppData%\ApexLab\settings.json

Why it exists: Persists user preferences across app restarts without a database.

Setting	Default	Why it exists
ApiBaseUrl	https://simcopilot-production.up.railway.app	Allows switching to a staging or local API
SentryDsn	null	Optional crash reporting; null = disabled
AcUdpHost / AcUdpPort	127.0.0.1:9996	Assetto Corsa UDP listener address
F1UdpPort	20777	F1 Codemasters/EA UDP port
ForzaUdpPort	5300	Forza Data Out UDP port
LiveAutoCaptureLmu	true	Start LMU recording automatically when SHM provides data
LiveAutoListenAcUdp	true	Start AC UDP listener at app launch
MinimizeToTray	true	Minimize to system tray icon instead of taskbar
HideLiveDeltaMapOverlay	false	Hide Δ overlay on track map during recording
HideLiveDeltaStatusBar	false	Hide Δ/prediction in status bar
HideLiveDeltaInMainWhenHudOpen	false	Suppress main-window Δ when HUD window is open (avoid triple display)
LastSessionCatalogId	null	Restore last-opened session on next launch
MainWindow*	null	Window position/size restoration
HudWindow*	null	HUD overlay position restoration
LapPlaybackSpeedMultiplier	1.0	Playback speed (0.5×, 1×, 2×)
LapPlaybackLoop	false	Auto-loop lap playback
LapPlaybackMapFollow	false	Pan map to track cursor during playback
StartWithWindows	false	Windows Run registry key
UiLanguage	null (= system)	Force "fr" or "en"
HasSeenOnboarding	false	Skip onboarding screen after first run
Analysis*	null	Override default AnalysisOptions values (brake margin, throttle margin, etc.)
SectorBoundariesByTrackKey	null	Custom mini-sector boundaries per track (track key → list of distance bounds in metres)

Read at: app startup. Written at: any settings change; window close (position/size).

---

8. Local Storage — Auth Tokens (encrypted file)

File: %LocalAppData%\ApexLab\auth.dat

Why it exists: JWT tokens must survive app restarts so the user does not need to log in every session.

Field stored	Why
AccessToken	JWT Bearer token sent with every authenticated API request
RefreshToken	Long-lived token (30 days) used to obtain a new access token without re-login
ExpiresAt	Expiry of the access token; checked before each API call to decide whether to refresh
DisplayName	Shown in the UI header without an API round-trip
AvatarUrl	Shown in the UI header without an API round-trip

Encryption: The file is protected using the Windows Data Protection API (DPAPI) — encrypted to the current Windows user account. The raw token values are never stored in plaintext.

Lifecycle: Written on successful login or token refresh. Cleared on Logout(). If missing or unreadable at startup, the user is treated as not authenticated.

---

9. API — users table

Column	Type	Constraints	Why it exists
id	UUID	PK	Stable opaque identifier for the user; referenced by all other tables
email	varchar(256)	nullable	Used for email/password auth and profile display; not required for OAuth-only accounts
display_name	varchar(128)	not null	Shown on leaderboards and feed; comes from OAuth profile at login
avatar_url	varchar(1024)	nullable	Profile image URL from OAuth provider; shown on leaderboard entries
oauth_provider	varchar(32)	not null	"google" | "discord" | "steam" | "email" — identifies which flow authenticated this account
oauth_subject	varchar(256)	not null	Provider-specific user ID (e.g. Google sub claim); combined with oauth_provider to find/create users
password_hash	varchar(256)	nullable	BCrypt hash; null for OAuth users; used only for email/password login
created_at	timestamptz	not null	Account age; not shown publicly

Unique index: (oauth_provider, oauth_subject) — ensures one account per provider identity, prevents duplicate accounts on re-login.

How a user is created: AuthController.OAuthCallback() calls FindOrCreateUserAsync(). If (provider, subject) already exists, returns existing user. Otherwise inserts a new row and returns it. Email/password registration uses POST /v1/auth/register.

How it's updated: Display name and avatar are refreshed from OAuth claims on each login if they differ. PATCH /v1/profile allows manual updates.

---

10. API — sessions table

Column	Type	Constraints	Why it exists
id	UUID	PK	Server-assigned ID; returned to client as cloudSessionId; required for leaderboard submission
user_id	UUID	FK → users, cascade delete	Links session to account
game_id	varchar(64)	not null	Simulator identifier; used to filter leaderboard entries
track_name	varchar(256)	nullable	Track name as reported by the game; denormalized for quick leaderboard queries
track_layout	varchar(128)	nullable	Track variant; part of the canonical track key
vehicle_name	varchar(256)	nullable	Vehicle; optional leaderboard filter
recorded_at	timestamptz	not null	When the session was recorded (client time); shown in feed
lap_count	int	not null	Number of laps in the session
best_lap_ms	int	nullable	Best valid lap time in milliseconds; used by leaderboard submission as the candidate time
weather_summary	varchar(256)	nullable	E.g. "18°C / 32°C track" — context for AI coaching prompts
session_phase	varchar(64)	nullable	Practice / Qualifying / Race — informs achievement detection
client_session_id	varchar(1024)	not null	Client-generated key (UUID); combined with user_id for idempotency — prevents duplicate uploads on retry
received_at	timestamptz	not null	Server-side receipt timestamp
updated_at	timestamptz	not null	Last modification

Unique index: (user_id, client_session_id) — idempotency guarantee.

How it's created: POST /v1/sessions (desktop sync). The client sends SessionUploadDto; the controller checks idempotency, then inserts. No raw telemetry is ever sent.

How it's used downstream: Referenced by laps, leaderboard_entries, feed_entries, track_processing_jobs. The id is returned to the client and stored in sessions.db as cloud_session_id.

---

11. API — laps table

Column	Type	Constraints	Why it exists
id	UUID	PK	Stable lap row identity
session_id	UUID	FK → sessions, cascade delete	Groups laps under their session
lap_index	int	not null	0-based lap number within the session
lap_time_ms	int	not null	Lap time in milliseconds; used for leaderboard comparison and display
is_valid	bool	not null	Whether the game / app considered this lap valid
counts_timing	bool	not null	Whether this lap counts toward session timing (false for out-laps, pit laps)
quality_combined	float (0–1)	not null	Overall quality score — must be ≥ 0.8 for leaderboard eligibility
quality_coherence	float (0–1)	not null	Measures internal consistency of telemetry channels (e.g. speed/distance coherence)
quality_stability	float (0–1)	not null	Measures lap-to-lap repeatability within the session
quality_completeness	float (0–1)	not null	Fraction of expected telemetry points actually received
num_penalties	int	nullable	Penalty count at lap end; informs quality gating
num_pitstops	int	nullable	Pit stop count
in_pits_at_lap_end	bool	nullable	True if the lap ended in the pits

Why four quality dimensions: A lap can be complete but unstable (noisy connection), or stable but incomplete (partial lap at session start). Breaking quality into dimensions allows targeted filtering — e.g. the leaderboard gate uses quality_combined, but future analysis could gate on quality_coherence specifically.

How quality is computed: By TelemetryQualityScorer (in SimCopilot.Core / SimCopilot.Tests) before the session is uploaded. The scores are included in LapSummaryDto and stored server-side for filtering without re-reading telemetry.

---

12. API — tracks table

Column	Type	Why it exists
id	UUID PK	Stable identifier for a (game, track, layout) combination; referenced by leaderboard entries
game_id	varchar(64)	Part of the canonical track key
track_name	varchar(256)	Part of the canonical track key
track_layout	varchar(128)	Part of the canonical track key (nullable = no layout variant)
status	varchar(32)	Processing pipeline state: pending → building → ready → frozen
confidence	float (0–1)	Convergence metric — how close successive submissions are to each other
sample_count	int	How many lap position datasets have been ingested; frozen at ≥ 50 or confidence ≥ 0.95
canonical_version_id	UUID nullable	FK to the current frozen TrackVersion used for leaderboard validation
drift_flagged	bool	True when post-freeze submissions diverge beyond the drift threshold — triggers a rebuild review
drift_error_margin	float nullable	Average point error in metres against the frozen centerline
last_drift_check_at	timestamptz	When drift was last evaluated
created_at / updated_at	timestamptz	Audit

Unique index: (game_id, track_name, track_layout) — ensures one canonical track row per venue/variant/game combination.

How it's created: SessionsController.Upload() finds-or-creates the track row when a session is uploaded. TrackProcessingService updates status, confidence, sample_count, and canonical_version_id as centerline submissions are processed.

---

13. API — track_versions table

Column	Type	Why it exists
id	UUID PK	Immutable version identity; stored on leaderboard entries at submission time
track_id	UUID FK → tracks	Links version to its canonical track
version	int	Incrementing version number within a track
is_frozen	bool	True = no further updates; leaderboard entries reference this version for comparison
centerline_json	text	JSON array of {x, y} normalized, resampled (1000 points) track centerline
error_margin	float nullable	Average per-point error in metres at time of freeze
sample_count	int	Number of lap submissions that contributed
source_session_ids_json	text	JSON array of session UUIDs that contributed; audit trail
processing_manifest_json	text	Algorithm versions used (normalization, alignment, resampling parameters) for reproducibility
confidence_breakdown_json	text	Structured scores: geometry_stability, data_density, noise_level, multi_source_agreement (each 0–1)
parent_version_id	UUID nullable	FK to the version this one was derived from (null for v1)
created_at	timestamptz	When this version was frozen

Why track versions are immutable: Leaderboard entries store the track_version_id at submission time. If the centerline is later improved (new version), older entries can be flagged for re-evaluation without losing the historical benchmark.

---

14. API — leaderboard_entries table

Column	Type	Constraints	Why it exists
id	UUID	PK	Row identity
track_id	UUID	FK → tracks, cascade delete	Groups entries by track
user_id	UUID	FK → users, cascade delete	Links entry to account
session_id	UUID	FK → sessions	Provides access to session context (vehicle, weather) for display
lap_time_ms	int	not null	The ranked metric — milliseconds for precise comparison
game_id	varchar(64)	not null	Denormalized for per-game filtering
vehicle_name	varchar(256)	nullable	Denormalized for per-vehicle filtering
quality_score	float (0–1)	not null	Must be ≥ 0.8 to qualify; prevents submitting invalid or noisy laps
track_version_id	UUID nullable	FK → track_versions	The canonical track version at submission time; used to flag entries predating a track rebuild
submitted_at	timestamptz	not null	First submission timestamp
updated_at	timestamptz	not null	Set when a faster lap replaces the previous entry

Unique index: (track_id, user_id) — enforces one entry per user per track. On new submission: if the new lap is faster, the row is updated (not inserted); if slower, it is discarded.

Index for ranking: (track_id, lap_time_ms) — supports ORDER BY lap_time_ms efficiently.

Redis caching: Leaderboard query results are cached under key lb:{trackId}:{vehicleFilter}:{page}:{pageSize} for 5 minutes. Cache is invalidated on new submission.

---

15. API — user_achievements table

Column	Type	Why it exists
id	UUID PK	Row identity
user_id	UUID FK → users	Links achievement to account
achievement_id	varchar(64)	Stable string key from AchievementCatalog (e.g. "leaderboard_top3")
earned_at	timestamptz	When the achievement was granted; shown in feed and profile

Why it exists: Achievements are granted once and recorded permanently. The table is checked before granting to prevent duplicates (TryEarn() returns false if already present).

Achievement catalog

ID	Trigger	How checked
first_session	First session upload	Session count = 1
sessions_10	10 sessions uploaded	Session count = 10
sessions_50	50 sessions	Session count = 50
leaderboard_debut	First leaderboard entry	Any rank on any track
leaderboard_top10	Top 10 on a track	Rank ≤ 10 after submission
leaderboard_top3	Top 3 on a track (Podium)	Rank ≤ 3 after submission
quality_high	High-quality lap submitted	quality_combined ≥ 0.9
multi_game	Used 3+ different simulators	Distinct game_id count ≥ 3
night_owl	Session recorded late at night	recorded_at hour ≥ 23:00 local
early_bird	Session recorded early morning	recorded_at hour < 07:00 local

---

16. API — feed_entries table

Column	Type	Why it exists
id	UUID PK	Row identity
user_id	UUID FK → users	Whose activity this is
event_type	varchar(64)	session_uploaded | achievement_earned | leaderboard_new | leaderboard_improved
title	varchar(256)	Primary headline (e.g. "Tour en 1:46.234 sur Spa")
subtitle	varchar(512)	Supporting detail (e.g. achievement name, vehicle)
session_id	UUID nullable FK → sessions SET NULL	Links feed entry to the related session for context; nullable because sessions can be deleted
created_at	timestamptz	Event timestamp; used for feed ordering

Deduplication: FeedService skips writing a new entry if an identical (user_id, event_type, title) entry exists within the past hour. This prevents duplicate entries from upload retries.

How it's populated: Only by server-side services (OutboxDispatcher → FeedService, AchievementService). The client app never writes directly to the feed.

---

17. API — refresh_tokens table

Column	Type	Why it exists
id	UUID PK	Row identity
user_id	UUID FK → users, cascade delete	Links token to account; allows revoking all tokens for a user
token_hash	varchar(64)	SHA-256 hex digest of the raw token — never stored in plaintext
expires_at	timestamptz	30-day TTL; checked on rotation; expired tokens are rejected
created_at	timestamptz	Issuance time
revoked_at	timestamptz nullable	Null = active. Set to now() when the token is used (rotation) or explicitly revoked (logout). Revoked entries are retained for 30 days for audit, then purged by DataRetentionService.

Unique index on token_hash: Fast lookup; ensures no two active tokens share a hash.

Rotation strategy: When the client calls POST /v1/auth/refresh, the server: 1. Finds the row by SHA-256 hash of the presented token 2. Verifies it is not revoked and not expired 3. Sets revoked_at = now() on the old row 4. Issues a new token, inserts a new row 5. Returns the new token

If a revoked token is presented (replay attack), the entire token family should be revoked. This is not yet implemented in V1 but the revoked_at audit trail supports it.

---

18. API — outbox_messages table

Column	Type	Why it exists
id	UUID PK	Row identity
event_type	varchar(64)	"session.uploaded" | "leaderboard.submitted"
payload	text (JSON)	Event data consumed by handlers (session ID, user ID, rank, etc.)
created_at	timestamptz	Insertion time; used for poll ordering
processed_at	timestamptz nullable	Null = pending. Set when successfully processed by OutboxDispatcher.
retry_count	int	Incremented on failure; processing stops at 5 retries
last_error	varchar(2048)	Last failure message for debugging

Why it exists (transactional outbox pattern): When a session upload is received, both the SessionEntity INSERT and the OutboxMessageEntity INSERT happen in the same database transaction. If the process crashes between the INSERT and the downstream call (FeedService, AchievementService), the outbox message is still there on restart and will be processed. Without this, side effects could be lost.

OutboxDispatcher poll cycle: Every 5 seconds, fetches rows where processed_at IS NULL AND retry_count < 5, dispatches to handlers, marks processed_at on success or increments retry_count on failure.

---

19. API — track_processing_jobs table

Column	Type	Why it exists
id	UUID PK	Row identity
session_id	UUID	The source session (for audit)
game_id	varchar(64)	Game context for the track
track_name	varchar(256)	Identifies which canonical track to update
track_layout	varchar(128)	Layout variant
track_points_json	text (JSON)	Serialized List<TrackPointDto> — the X/Y/Distance samples from the session's telemetry
status	varchar(16)	pending → processing → done | failed
created_at	timestamptz	Enqueue time
started_at	timestamptz nullable	When TrackProcessingService picked it up
completed_at	timestamptz nullable	When processing finished
retry_count	int	Processing failures; capped at 3
last_error	varchar(2048)	Last failure message

Why it's durable (not in-memory queue): Track processing involves compute-heavy geometry operations (PCA, Procrustes alignment, resampling, weighted averaging). If the process restarts mid-job, the work is not lost — TrackProcessingService picks up any pending or processing (orphaned) jobs on next startup.

What track_points_json contains: Raw {x, y, distance} points from the session's uploaded telemetry. These are world-space coordinates from the game, not normalized. Normalization (PCA centering) is the first step of processing.

---

20. Auth & JWT Tokens

Access Token (JWT)

Issued by: JwtService.IssueAccessToken(user)

TTL: 60 minutes (configurable via Jwt__ExpiryMinutes)

Claims:

Claim	Value	Why it's included
sub	User UUID	Primary identity; used to look up user in all authorized endpoints
email	User email	Profile display without extra DB query
display_name	User display name	Leaderboard entries use this without joining users table
avatar_url	Avatar URL	Feed entries use this without joining
jti	New UUID per token	JWT ID for future token revocation lists
iss	"apexlab-api"	Validated against Jwt__Issuer config; must match exactly
aud	"apexlab-client"	Audience validation
exp	Unix timestamp	Token expiry

Storage on client: Encrypted in %LocalAppData%\ApexLab\auth.dat via DPAPI.

Usage: Sent as Authorization: Bearer {token} header on every authenticated API request. The client checks ExpiresAt before each call and calls POST /v1/auth/refresh if the token is expired or close to expiry.

Refresh Token

Format: Cryptographically secure random 32-byte value (256-bit entropy), returned as base64url string.

Storage on server: SHA-256 hash stored in refresh_tokens.token_hash — never the raw value.

Storage on client: Raw value stored encrypted in auth.dat.

Rotation: Every use produces a new token; the old one is revoked. This means a stolen refresh token can only be used once before the legitimate client detects the revocation on next refresh.

PKCE Code (one-time exchange code)

What it is: A short-lived code generated by the server after OAuth callback, passed back to the desktop app via the local redirect URI.

Why it's needed: The desktop app cannot receive an OAuth callback directly (it's not a web server). Instead, it starts a temporary HTTP listener on a random local port, passes that as the redirect URI, and waits for the code to arrive.

TTL: ~5 minutes (held in IAuthCodeStore, an in-memory dictionary).

How it's used: AuthService.ExchangeCodeAsync(code) sends the code to POST /v1/auth/exchange, which looks it up in IAuthCodeStore, returns the JWT and refresh token, and deletes the code entry.

---

21. Redis Cache

Why it exists: Leaderboard queries involve an ORDER BY lap_time_ms across potentially thousands of entries, with a JOIN to users. Caching the result avoids repeated database reads for a ranking that changes infrequently.

Cache key	Value	TTL	Invalidation
lb:{trackId}:{vehicleFilter}:{page}:{pageSize}	JSON-serialized list of leaderboard entry DTOs	5 minutes	On new leaderboard submission for that track (explicit delete of matching key prefix)

What the cached value contains: The rendered DTO list — display names, avatar URLs, lap times, vehicle names, ranks. This means user profile changes (display name updates) are not immediately reflected on leaderboards; they take effect after the next cache miss.

---

22. Transport DTOs (App ↔ API)

These are the data structures that cross the network boundary. Defined in SimCopilot.Shared and used by both the desktop app and the API.

SessionUploadDto

Sent by SyncService to POST /v1/sessions.

Field	Why it's sent
GameId	Required to find/create the canonical track
TrackName / TrackLayout	Canonical track lookup key
VehicleName	Display and filtering
RecordedAt	Client-side timestamp — server stores this rather than received_at for accurate feed display
Laps (list of LapSummaryDto)	Lap times and quality scores — the only per-point data sent
BestLapMs	Denormalized best lap for quick leaderboard submission
WeatherSummary	AI coaching context
SessionPhase	Achievement detection (night race, quali session, etc.)
ClientSessionId	Idempotency key — allows safe retry on network failure
TrackPoints?	Optional X/Y/Distance samples for track centerline processing

LapSummaryDto

Sub-object inside SessionUploadDto. One per lap.

Field	Why it's sent
LapIndex	Ordering
LapTimeMs	The time to store
IsValid / CountsForSessionTiming	Quality filtering
QualityCombined / QualityCoherence / QualityStability / QualityCompleteness	Server-side leaderboard gating; no need to re-read telemetry
NumPenalties / NumPitstops / InPitsAtLapEnd	Additional validity context

LeaderboardSubmitDto

Sent by the client to POST /v1/leaderboards/submit.

Field	Why it's sent
SessionId	Server-side UUID — the session must already exist (uploaded first)
LapTimeMs	The time to rank
QualityScore	Must be ≥ 0.8; server validates this independently

AiInsightRequestDto / AiChatRequestDto

Sent to POST /v1/ai/insights and POST /v1/ai/chat.

Field	Why it's sent
GameId / TrackName / TrackLayout / VehicleName	Session context for the AI system prompt
RefLapMs / CurLapMs	Lap time delta — the AI knows how much time was lost
Insights[]	AnalysisInsight objects from AnalysisEngine — the AI explains these in plain language
SegmentDeltas[]	Per-corner time deltas — the AI can identify the biggest problem corners
Messages[] (chat only)	Full conversation history — sent each turn so the model has context

Note: Raw telemetry is never sent to the AI endpoint. The analysis engine runs entirely client-side; only the computed findings travel to the server.

---

23. Data Lifecycle Summary

Data	Created	Updated	Deleted
CSV session file	On live recording auto-save or manual import	Never	Never automatically; user can delete manually
Local catalog row (sessions.db)	On RegisterFile()	On UpdateSessionSnapshot(), on sync, on favorite toggle	On user delete from session library
User settings (settings.json)	First launch (defaults)	On any preference change; on window close	Never
Auth tokens (auth.dat)	On successful login	On token refresh	On Logout()
API user row	On first OAuth login or email registration	On PATCH /v1/profile; display name / avatar refreshed on re-login	On account deletion (not yet implemented in V1)
API session row	On POST /v1/sessions	Never (immutable after upload)	On user deletion (cascade) or DataRetentionService retention window
API lap rows	With their parent session	Never	With their parent session (cascade)
Track row	On first session upload for that (game, track, layout)	As TrackProcessingService processes submissions	Never
Track version	When TrackProcessingService freezes a new centerline	Never (immutable)	Never
Leaderboard entry	On POST /v1/leaderboards/submit	When a faster lap is submitted by the same user for the same track	On user deletion (cascade) or track deletion (cascade)
User achievement	When AchievementService.TryEarn() grants it	Never	On user deletion (cascade)
Feed entry	By FeedService via OutboxDispatcher	Never	On user deletion (cascade); DataRetentionService may trim old entries
Refresh token (DB)	On login, registration, or token rotation	revoked_at set on use or logout	DataRetentionService purges revoked tokens after 30 days
Outbox message	In same transaction as the business entity it accompanies	processed_at set on success; retry_count incremented on failure	DataRetentionService purges processed messages after a configurable window
Track processing job	On POST /v1/sessions when TrackPoints are provided	status, started_at, completed_at, retry_count	DataRetentionService purges completed jobs after a configurable window
Redis leaderboard cache	On first leaderboard GET for a track	On cache miss (TTL expiry)	On new leaderboard submission (explicit invalidation)
Analysis data (in-memory)	On lap selection change in the UI	On re-selection	On session close or app exit

---

Appendix — Data that never leaves the desktop

The following data is generated and consumed entirely within the desktop client. It is never transmitted to the API or any external service:

• Full telemetry point arrays (Distance, Time, Speed, Throttle, Brake, Steering, Gear, X, Y)
• ResampledLapComparison arrays (resampled curves, cumulative time, delta)
• TrackSegment corner boundaries
• Track outline GeoJSON (cached from OSM, not user data)
• Window positions and layout preferences
• AI coach conversation history (stored only in CoachMemoryStore / coach.db locally; the API receives the full message history on each chat call but does not store it)