Documentation / Data model

Data model

Everything the app computes lives in plain Delta tables in your Unity Catalog — a medallion architecture (bronze → silver → gold), W3C getStats-compliant and ObserveRTC-compatible, and shaped so that continuous collection lands in exactly the same rows as an uploaded dump.

Tables

Bronze — raw input ledger

Table	What it holds
`bronze_uploads`	One row per uploaded file: name, size, SHA-256 (dedup), parse status and error.

Silver — normalized, source-agnostic

Table	What it holds
`silver_sessions`	One row per client session (one dump / one SDK client): origin URL, browser, OS, duration.
`silver_peer_connections`	One row per RTCPeerConnection: final states, time-to-connected, ICE restarts.
`silver_pc_events`	Connection lifecycle events (ICE/connection/signaling state changes, errors) for timeline overlays.
`silver_rtp_samples`	Per-second RTP stream stats: W3C cumulative counters as reported, plus parser-derived per-interval rates (bitrate, loss %, fps), with remote-inbound feedback folded into outbound rows.
`silver_ice_candidates`	Local/remote ICE candidate dimension (type, protocol, network type; addresses maskable).
`silver_ice_candidate_pair_samples`	Per-second candidate-pair stats — the source of transport RTT and available bandwidth.
`silver_media_devices`	Every getUserMedia and getDisplayMedia call: the constraints asked for (echo cancellation, automatic gain control, noise suppression, resolution), the device actually obtained, and any permission or device error.
`silver_device_pressure`	Compute Pressure API samples — how loaded the device was, independent of anything WebRTC reports. SDK sessions only; a browser dump has no equivalent.

Gold — QoE aggregates the app reads

Table	What it holds
`gold_stream_qoe`	Per-stream rollup: bitrate/loss/ jitter/RTT stats with percentiles, freezes, concealment, MOS.
`gold_session_qoe`	Call-level rollup driving the session list — denormalized dimensions (browser, network, TURN) for filtering, plus MOS, degraded %, the QoE grade, the experience score with its three penalty terms, and `call_id` / `client_id`.
`gold_qoe_timeline`	Fixed 10-second windows per kind/direction with per-window MOS and a degraded flag.
`gold_session_observations`	One row per finding: rule id, category, severity, the human-readable detail, what it applies to, the measured evidence, the points it cost the score, and its knowledge-base key.
`gold_session_diagnosis`	Append-only history of AI diagnoses (severity, summary, evidence-citing markdown, token usage).
`gold_daily_qoe`	Daily rollup by source/browser/OS for trends and the bundled AI/BI dashboard.

Column naming follows W3C getStats identifiers in snake_case, with units suffixed where converted (_ms, _s, _kbps, _pct). ObserveRTC identity fields (call_id, client_id, …) are kept so SDK samples map one-to-one.

The same shape from either source. An uploaded dump and the browser collection SDK land in identical rows — not by convention, but because the SDK parser reuses the dump parser's row builders outright, so the mapping from W3C names to columns exists in exactly one place. A test describes one call both ways and requires the resulting rows to match. Everything above silver is written once and works on either.

Two columns only continuous collection can fill: call_id ties every participant of one call together, and client_id is the key you join to your own customer, plan and revenue tables. A browser dump has no way to know either. Continuous collection is not generally available yet — get in touch if you want it.

QoE scoring

MOS is estimated with the wideband E-model, ITU-T G.107.1: delay impairment (Idd) per the standard’s equations, taking one-way delay as RTT/2 + 2×jitter (the 2×jitter term approximates the jitter-buffer delay a receiver adds to smooth out packet-timing variation), and equipment impairment (Ie,eff,WB) from packet loss with codec-specific robustness — wideband values per G.113 Appendix IV, narrowband codecs (PCMU/A, G.722) carrying the standard’s wideband conversion penalty so Opus is no longer under-scored. The result is a wideband R-factor (0–129), normalised per Annex A and converted to a 1.0–4.5 MOS estimate; the R value shown in the app carries its ITU-T G.109 class (Best/High/Medium/Low/Poor).
The intermediate R factor is stored alongside MOS (r_factor_avg) and shown in the app with its ITU-T G.109 quality class (Best ≥ 90, High ≥ 80, Medium ≥ 70, Low ≥ 60, Poor < 60) — useful when talking to telecom teams who reason in R values.
Scoring happens at three grains: per stream, per 10-second window, and per session.
A window is flagged degraded when MOS < 3.6, packet loss > 5%, or RTT > 400 ms.
Sessions are graded good / acceptable / poor from average MOS and the share of degraded time.

MOS values are estimates for triage and comparison — in ITU-T P.800.1 terms they are MOS-CQE (conversational quality, estimated by the E-model planning model), not a measured user opinion score from a subjective listening test (P.800).

Experience score

MOS only describes audio. The experience score covers the whole call in one number from 0 to 100:

score = 100 − audio_penalty − video_penalty − observation_penalty

Term	Cap	Where it comes from
`experience_audio_penalty`	40	Continuous. Average MOS, the worst window's MOS, and the share of audio the decoder had to conceal.
`experience_video_penalty`	30	Continuous. Freeze time, lowest frame rate, sustained bitrate starvation.
`experience_observation_penalty`	40	Discrete. Connectivity, CPU, configuration, device and session-level findings, weighted by severity.

The three terms cover disjoint ground, so nothing is charged twice — audio and video findings contribute zero to the third term precisely because the first two already priced them. All three are stored, so the app can always show why a call scored what it did.

Bands: excellent (≥85), good (≥70), fair (≥50), poor.

Observations

65 rules run during ingestion and write to gold_session_observations, each row stamped with the rules_version in force when it was evaluated (bumped on any rule or threshold change; stored sessions can be re-evaluated from silver alone). They are pure functions of the parsed rows, so every finding is reproducible from the data and every threshold is a named constant rather than a judgement call made at query time. Categories: connectivity (16), audio (11), video (11), CPU (5), configuration (6), devices (10), session (6). Alongside problems, a set of info-severity findings records how the call was built — IPv6 path, peer-discovered addresses, unused streams and data channels, USB audio, hand-edited SDP — without charging the score.

Rules are deliberately conservative: a missing value never fires one. Browser dumps are sparse, and a finding you cannot trust is worse than no finding.

Building on the tables

Show SQL: every view in the app exposes its exact query, fully qualified, ready for the SQL editor or a notebook.
JOIN with your data: gold_session_qoe.origin_url, timestamps, and (with the SDK) ObserveRTC ids give you join keys to your business tables.
AI/BI dashboard: a bundled Lakeview template (dashboards/lakertc_fleet.lvdash.json) charts daily MOS, grade mix, browser/network breakdowns, and the worst sessions.
Roadmap: the phase-2 TypeScript collection SDK writes to the same silver/gold shape — anything you build today keeps working.

← Screens