Data Model¶

12 Postgres tables managed by EF Core migrations. See the Data Dictionary for full column-level documentation on each table.

Table Overview¶

Table	Purpose	Key constraints
`users`	Accounts	`UNIQUE(oauth_provider, oauth_subject)`
`sessions`	Uploaded sessions	`UNIQUE(user_id, client_session_id)` — idempotency
`laps`	Lap rows + quality scores	FK → sessions (cascade delete)
`tracks`	Canonical (game, track, layout) records	`UNIQUE(game_id, track_name, track_layout)`
`track_versions`	Immutable centerline snapshots	Append-only
`leaderboard_entries`	One per (track, user) — best lap	`UNIQUE(track_id, user_id)`
`user_achievements`	Grant history	One row per `(user_id, achievement_id)`
`feed_entries`	Activity stream	Deduplicated by `(user_id, event_type, title)` within 1h
`refresh_tokens`	Hashed refresh tokens	`UNIQUE(token_hash)`
`outbox_messages`	Transactional event queue	Processed by `OutboxDispatcher`
`track_processing_jobs`	Centerline processing queue	Durable, survives restarts
`share_packs`	Reserved for V2	Not yet used

SafeWatch Event Tables¶

Five additional tables populated by EventRecordingMiddleware:

Table	Populated by	Key columns
`api_request_events`	Every request via middleware	`route`, `status_code`, `duration_ms`, `user_id`, `timestamp`
`auth_events`	`AuthController`	`action` (login/register/logout), `success`, `failure_reason`
`session_events`	`SessionsController.Upload`	`action` (session_uploaded), `session_id`
`ai_events`	`AiController`	`action` (coach_query/chat), `cost_usd`, `tokens_used`
`leaderboard_events`	`LeaderboardsController.Submit`	`action` (lap_submitted), `track_id`, `lap_time_ms`

Entity Relationships¶

users ─┬─── sessions ─── laps
       ├─── leaderboard_entries ─── tracks ─── track_versions
       ├─── user_achievements
       ├─── feed_entries
       └─── refresh_tokens

outbox_messages (standalone, processed by OutboxDispatcher)
track_processing_jobs (standalone, processed by TrackProcessingService)