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, designed so a future collection SDK feeds the exact same shape.
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. |
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 %, and the QoE grade. |
gold_qoe_timeline | Fixed 10-second windows per kind/direction with per-window MOS and a degraded flag. |
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 later.
QoE scoring
- MOS is estimated with a simplified ITU-T G.107 E-model: delay impairment from RTT/2 + 2×jitter + a codec allowance, equipment impairment from packet loss with codec-specific robustness (Opus, PCMU/A), combined into an R-factor and converted to a 1.0–4.5 MOS estimate.
- 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 — they
are not a measured user opinion score.
Building on the tables
- Show SQL: every fleet 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/webrtc_qoe_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.