ADR 0001: Sidecar Storage Schema (SQLite)

Context

The tuning-coach Rust sidecar ingests Forza Motorsport telemetry over UDP at ~60 Hz, runs heuristics over rolling windows, emits tuning recommendations to the SimHub overlay over WebSocket, accepts hotkey events from SimHub, and persists per-car setup state. Phase 1 of docs/PLAN.md requires a durable local store for:

Constraints

Read patterns

Reader Query shape Latency budget
Live overlay HUD “give me the most recent N recommendations for current session” < 50 ms
Heuristics engine “give me last K seconds of telemetry for current lap” < 10 ms (in-memory ring is primary; DB is fallback for replay)
Post-session report “all laps + aggregated recommendations for session X” < 1 s
Recorded session replay (Phase 9) “stream all snapshots for session X in t_ms order” sequential scan, throughput-bound
Setup form “current setup + locked params for car ordinal C” < 50 ms

Write patterns

Writer Rate Burst
Telemetry ingest ~60 rows/s during sessions bounded by packet rate
Recommendations < 1 row/s typical bursts at lap boundaries
Hotkey events event-driven; tens per session n/a
Car setups / preferences manual; rare n/a

The dominant cost is telemetry. Everything else is rounding error.

Decision

We will use SQLite (via rusqlite with the bundled feature) as the canonical persistence layer, opened in WAL mode, with the schema below.

For telemetry_snapshots we will use a hybrid layout: store the raw Forza packet as a BLOB, alongside a small set of indexed/denormalized columns needed for the most common live and analysis queries (lap_id, t_ms, speed_mps, rpm, throttle, brake, gear, is_race_on). All other fields are recovered by re-parsing the blob on demand. This pins our storage cost close to the raw-blob lower bound while preserving fast lap/time-range scans and basic filtering without a parse step.

A _migrations table tracks applied schema versions. Migration files live in sidecar/migrations/NNNN_<name>.sql and are applied in numeric order at startup, with each migration executed in its own transaction; each migration is recorded with its SHA-256 to detect tampering.

Schema

All timestamps are UTC ISO-8601 strings (TEXT) for human readability unless they’re sub-second deltas, in which case they are INTEGER milliseconds. Wall-clock fields are display-only; elapsed-time fields use monotonic deltas captured by the ingest loop.

_migrations

Tracks which migration files have been applied.

Column Type Notes
version INTEGER PRIMARY KEY matches NNNN prefix of migration file
name TEXT NOT NULL filename minus prefix and extension
sha256 TEXT NOT NULL hex digest of the migration file at apply time
applied_at TEXT NOT NULL UTC ISO-8601

Behavior:

sessions

Column Type Notes
id INTEGER PRIMARY KEY AUTOINCREMENT  
started_at TEXT NOT NULL UTC ISO-8601
ended_at TEXT NULL while in progress
car_ordinal INTEGER from packet; NULL if unknown at start
car_class INTEGER 0=D … 7=X
car_pi INTEGER performance index
drivetrain INTEGER 0=FWD, 1=RWD, 2=AWD
track_id TEXT resolved name from SimHub list (Phase 11); NULL until then
game TEXT NOT NULL DEFAULT 'forza_motorsport_2023' discriminator for future games
sidecar_version TEXT NOT NULL semver of producing build, for replay forward-compat
notes TEXT user-editable, optional

Indexes:

laps

Column Type Notes
id INTEGER PRIMARY KEY AUTOINCREMENT  
session_id INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE  
lap_number INTEGER NOT NULL from packet (LapNumber); 0 = out lap
started_t_ms INTEGER NOT NULL session-relative monotonic ms
ended_t_ms INTEGER NULL until lap closes
time_s REAL final lap time in seconds; NULL until close
valid INTEGER NOT NULL DEFAULT 1 boolean (0/1)
dirty_reason TEXT enum string: off_track | contact | grip_anomaly | manual | pit | reset | NULL
is_pit INTEGER NOT NULL DEFAULT 0 boolean
is_reset INTEGER NOT NULL DEFAULT 0 boolean (rewind detected)
best_split_s REAL optional aggregate, populated post-lap

Indexes:

telemetry_snapshots

The hot table. Hybrid layout: blob + a thin index of common-query columns.

Column Type Notes
id INTEGER PRIMARY KEY AUTOINCREMENT  
session_id INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE denormalized for session-scoped scans without join
lap_id INTEGER REFERENCES laps(id) ON DELETE SET NULL NULL for pre-lap-close packets, backfilled on lap close
t_ms INTEGER NOT NULL session-relative monotonic ms; primary time axis
is_race_on INTEGER NOT NULL boolean from packet
speed_mps REAL NOT NULL from packet
rpm REAL NOT NULL CurrentEngineRpm
throttle INTEGER NOT NULL 0..255 raw
brake INTEGER NOT NULL 0..255 raw
gear INTEGER NOT NULL 0=R, 1..N
packet BLOB NOT NULL raw Forza Dash packet (311 or 331 bytes), little-endian
packet_format INTEGER NOT NULL DEFAULT 1 discriminator: 1=forza_dash_v2_311b, 2=forza_dash_fm2023_331b

Indexes:

Notes:

recommendations

Column Type Notes
id INTEGER PRIMARY KEY AUTOINCREMENT  
session_id INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE  
lap_id INTEGER REFERENCES laps(id) ON DELETE SET NULL NULL = session-level (e.g., post-session)
created_at TEXT NOT NULL UTC ISO-8601
category TEXT NOT NULL enum: tires | gearing | alignment | anti_roll | springs | damping | aero | brakes | differential
parameter TEXT specific param key, e.g. spring_rate_front; NULL for multi-param advice
confidence TEXT NOT NULL enum: high | medium | low
delivered INTEGER NOT NULL DEFAULT 0 1 once shown in HUD
dismissed INTEGER NOT NULL DEFAULT 0 user dismissed in HUD
payload_json TEXT NOT NULL full structured rec (see race-engineer.agent.md format): symptom, mechanism, adjustment, expected outcome, caveats, style/locked-param notes
schema_version INTEGER NOT NULL DEFAULT 1 of the JSON payload, independent of DB schema

Indexes:

car_setups

One row per car ordinal — current setup state, with snapshot history kept in payload_json (small enough we don’t need a separate history table yet).

Column Type Notes
car_ordinal INTEGER PRIMARY KEY from packet
car_label TEXT display name; resolved later from SimHub list
setup_json TEXT NOT NULL full tune object, keyed by parameter
locked_params_json TEXT NOT NULL array of param keys the car can’t adjust
upgrades_json TEXT NOT NULL DEFAULT '{}' installed upgrades affecting locked set
source TEXT NOT NULL enum: manual | ocr | imported
updated_at TEXT NOT NULL UTC ISO-8601
schema_version INTEGER NOT NULL DEFAULT 1 of the JSON shape

No additional indexes — single-row lookup by PK.

user_preferences

Free-form key-value store for overlay + coach config. Typed keys are validated in code, not in the schema, so adding a preference doesn’t require a migration.

Column Type Notes
key TEXT PRIMARY KEY dot.separated.key
value_json TEXT NOT NULL JSON-encoded scalar/object
updated_at TEXT NOT NULL UTC ISO-8601

hotkey_events

Column Type Notes
id INTEGER PRIMARY KEY AUTOINCREMENT  
session_id INTEGER REFERENCES sessions(id) ON DELETE CASCADE NULL if no active session
received_at TEXT NOT NULL UTC ISO-8601 (display)
t_ms INTEGER session-relative monotonic ms (NULL if no session)
action TEXT NOT NULL enum: mark_dirty | pit_in | pit_out | snooze | request_feedback | reset_session
payload_json TEXT optional extra context

Indexes:

Connection management

Pragmas applied at startup

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA temp_store = MEMORY;

Consequences

Positive

Negative

Neutral

Alternatives Considered

A. Telemetry as raw blob only (no denormalized columns)

B. Telemetry fully denormalized (one column per packet field, no blob)

C. DuckDB instead of SQLite

D. Flat Parquet files per session

E. SQLite with one row per field (EAV)

Migration & evolution plan

Backup story

Storage estimates

Assumptions: ~331 B per FM 2023 Forza Dash packet (311 B for legacy Dash), ~60 Hz, ~150 B of denormalized columns + row overhead, ~50% index overhead on the indexed columns.

Workload Raw bytes With indexes Per session (~30 min) Per heavy week (~10 h)
Telemetry only ~71.5 MB/h ~85 MB/h ~43 MB ~850 MB
+ recommendations + laps + hotkeys +<1 MB/h +<1 MB/h ~44 MB ~860 MB

Order of magnitude: a serious user generates GB-scale data per year. We will introduce a configurable retention policy (open question in PLAN.md) before 1.0, e.g. “keep last N sessions in full; older sessions keep recommendations + laps but drop telemetry.”

Observability

References