Recording Spine

Plain-language summary. This is the heart of Playroll. A player records a chunk of gameplay; that recording is a session; the session produces uploads (a fixed set of files); we record which machine it ran on; and the files then flow through a GPU pipeline that turns raw captures into training-ready data. Every reward, every support investigation, and every investor KPI is computed from the tables on this page.

For the behavioral contract (states, gates, validation) see Recording Session Lifecycle and the Upload Validation Pipeline. This page is the data view of the same story.

Map

recording_sessions — the unit of everything

One row per start-to-stop recording attempt, keyed by a string session_id of the form rec-<epoch-ms> (the same id used by the local SQLite DB, every capture.v2.* telemetry event, the on-disk folder, and the S3 key segment — so a session maps 1:1 from the player's disk to the cloud).

Key columns by purpose:

Purpose	Columns
Identity & timing	session_id, user_id, game_name, game_slug, recording_started_at, recording_ended_at, duration_seconds
Lane	mode — quest or free_play, decided by context (EC-547, refined by EC-549): quest only when a catalog (eligible_games) title is recorded inside a party whose lobby carries a quest binding (lobbies.quest_id set — the core reads it from /api/lobby/set and gates on LobbySnapshot::questBound()); solo play and casual (no-quest) parties are free_play even for catalog games, and detection-lane free-play games (library, store-root, tagged) are always free_play. See Recording lanes. Default quest. Wire path (EC-552): the core decides the lane, persists it locally (SQLite sessions.mode, EC-475), and sends it as mode on both the session-started and session-ended notifications; the auth_presigned Lambda validates it (quest / free_play only) and writes the column — started sets it at row creation, ended re-sends it so a row created late by the ended upsert (client offline at start) still gets the lane. Older cores omit the field and land on the column default quest.
Quest linkage	quest_id (FK → quests.id, ON DELETE SET NULL). Quest-lane rows inherit the party's binding: a BEFORE INSERT OR UPDATE trigger copies lobbies.quest_id onto mode = 'quest' rows with a lobby_id (EC-549, migration 20260707150000); free_play rows are auto-attributed to the canonical free-play quest (slug free-play, seeded with status = ended so no launcher build renders it) by an earlier-firing BEFORE trigger — so every session is quest-shaped regardless of writer: core, upload Lambda, or backfill (EC-540, migration 20260706150000). A second AFTER trigger upserts the matching quest_participations row (status recording; NULL-lobby rows deduped by the partial unique index quest_participations_solo_uq), so the pipeline sees the full quest shape (migration 20260707100000).
Status	status, uploadability_status, block_reason, dismissed
Capture facts	chunk_count, detected_resolution (jsonb), required_artifact_mode (mp4_csv_json / mp4_csv), transcript_expected, transcript_skipped
Provenance	lobby_id (by value — no FK), hw_snapshot_id (FK → device_hw_snapshots.id), core_version, ui_version, installer_version
Server lifecycle	client_ended_at, server_verified_at, created_at, updated_at

status defaults to uploading at session start; uploadability_status is a strict uploadable / blocked / NULL. The declared foreign keys on this table are hw_snapshot_id and quest_id; lobby_id is a bare by-value link (see below).

Two invariants worth memorizing

1. duration_seconds is the canonical clock. Recording hours — for payment, for KPIs — come from this validated session timing, not from counting uploaded files. The investor view (investor_daily_recording_kpis_vc_view) is built on it.
2. lobby_id is optional metadata and not a foreign key. A session is identified by its own session_id; the lobby just pairs simultaneous sessions. A solo recording has a null lobby_id and is no less valid. The lobby↔game binding is enforced by a trigger on s3_uploads (below), not by an FK on this column.

s3_uploads — the per-file upload evidence

One row per uploaded file, not per session — so a finished session has several rows sharing one session_id. What gets uploaded is not a fixed 3-file triplet:

Artifact	file_type / artifact_kind	Gates upload?
Video	mp4 / video	Yes — required by the validator.
Input log	csv / input (_input.csv)	Yes — required.
Metadata	json / meta (_meta.json)	Yes — required.
Mic audio	_mic.aac	No — optional sidecar (EC-259).
Transcript	json / transcript (_transcript.json)	No — optional, generated on/after upload.
Audio (legacy)	ogg	No — removed in EC-173; present only on historical rows.

"All-or-nothing" is a property of the gate, not the upload

The atomic guarantee is at the local validation gate, not across the files in S3. RecordingArtifactValidator requires MP4 and _input.csv and _meta.json to all pass; if validation returns blocked, the finalizer registers none of them for upload (the only exception being the mp4_no_frames case, which deletes the footage entirely). That is the all-or-nothing point.

After the gate passes, the finalizer registers each artifact only if it exists on disk (registerFileIfPresent for mp4, csv, json — independently), and the optional mic/transcript sidecars are never part of the gate at all. So s3_uploads row counts per file_type are not equal across sessions — older sessions predate _meta.json, ogg-era rows exist, and sidecars come and go. Do not assume "3 rows ⇒ one complete session" or "every session has a JSON". See Upload Validation Pipeline for the exact validator contract.

complete_triplets is the legacy view, not the modern set

Despite the name, the complete_triplets view requires mp4 ≥ 1 AND ogg ≥ 1 AND csv ≥ 1 — its "audio" term is OGG, with no _meta.json term at all. Because OGG was removed in EC-173, it matches only historical OGG-era sessions and returns nothing for modern MP4 + CSV + JSON recordings. Don't use it as "the validated set". To find sessions with the current validated artifacts, query s3_uploads for the mp4/csv/json+meta rows directly.

Columns of note:

• s3_key, s3_bucket (default playroll-captures), filename, file_type, artifact_kind — what and where the object is. artifact_kind has no CHECK (EC-289): it's derived from the filename suffix (video / input / meta / transcript), and NULL means unclassified (e.g. legacy ogg rows).
• upload_year / upload_month / upload_day — denormalized partition keys matching the S3 prefix layout user/game/date/<sessionId>/<filename> (constrained: month 1–12, day 1–31).
• game_slug, lobby_id — copied onto the upload so the game-boundary trigger (s3_uploads_enforce_lobby_game_match, BEFORE INSERT OR UPDATE) can enforce them. It only acts on lobby-tagged uploads — if lobby_id IS NULL (a solo recording) it returns immediately and never checks. When a recording_sessions row exists, that row's (lobby_id, game_slug) is the ground truth; lobbies.game_slug is only a fallback when no session row is present. The hard boundary the quests model extends.
• size_bytes, etag, uploaded_at, status (uploaded [default] / processing / completed / failed) — object integrity and state.

The link to recording_sessions is by value on session_id (both are text), not a declared foreign key. The only declared FK on s3_uploads is lobby_id → lobbies.id (and it is marked NOT VALID).

The transcript — a first-class artifact

The _transcript.json is not an afterthought; it has its own lifecycle, its own state columns on recording_sessions, and a player-writable workflow. It is worth understanding in full because "is this session complete?" depends on it.

How it works, end to end:

• Owes a transcript iff voice was captured. At session end (EC-407) the core sets transcript_expected = true only when a _mic.aac sidecar exists — the same signal the transcription observer uses to enqueue a job. No mic ⇒ no transcript owed.
• Generation is local and NVIDIA-GPU-only. transcription_planner.cpp runs Whisper on the user's machine, picking a model tier by RAM/VRAM. On a non-NVIDIA machine it returns NO_NVIDIA_GPU and nothing is generated.
• The mic sidecar dies once the transcript exists (EC-356): a valid _transcript.json replaces it, so raw voice audio doesn't outlive its transcript.
• The player can skip or edit. transcript_skipped (EC-407/EC-409) is written directly by the authenticated client — a column-level GRANT UPDATE(transcript_skipped) plus an RLS policy (auth.uid()::text = user_id) lets the app set it with no service-role round-trip. The in-app editor (EC-384) edits {stem}_transcript.json in place before it uploads.
• It uploads as its own row (EC-289): file_type = 'json', artifact_kind = 'transcript', distinct from the meta JSON.
• "Transcript done" is therefore NOT transcript_expected OR transcript_skipped OR transcript_count > 0 — exactly what the player_recording_sessions view surfaces.

dismissed (EC-411) follows the same player-writable pattern: a scoped GRANT lets the client set it true to hide a session from the Activity view (reversal is support-only).

device_hw_snapshots — what it ran on

Per-(user, device) hardware inventory with a (user_id, hw_fingerprint) dedup key, so a driver / RAM / GPU change naturally produces a new row — giving us free before/after cohorts for performance regressions (see EC-20). The source column records which of two writers produced the row:

• ui_login — the register-device-hw-snapshot edge function writes a snapshot on every successful login. This is the dominant path (live data: ~18 ui_login rows for every 1 core_meta_json).
• core_meta_json — the on_upload Lambda back-fills from the _meta.json::hardware block of an uploaded session.

Both share the same dedup key, so the two paths converge on one row per distinct hardware fingerprint.

Flattened, queryable columns — cpu_model, cpu_architecture, logical_processors, physical_memory_mib, os_release, primary_gpu_vendor, primary_gpu_name, primary_gpu_driver, primary_gpu_driver_nvidia — sit alongside the full snapshot jsonb. A session points at the exact snapshot it ran on via recording_sessions.hw_snapshot_id. See Device HW Inventory.

The GPU pipeline

After upload, the data-filtering GPU box processes sessions. Two tables track that:

• pipeline_sessions — the pipeline's index of "what exists in S3 for this session": s3_prefix, current stage (a smallint), status, the resolved artifact keys (mp4_key, csv_key, ogg_key), size_bytes_total, and verified_at. Its primary key is composite — (session_id, environment), with environment ∈ (dev, prod) (default prod): the same session can be processed independently in dev and prod, producing two rows. status walks pending → processing → verified | failed.
• pipeline_runs — one row per execution over a session: the pipeline_name, trigger_type (default manual), status (running / pass / fail / skipped), the steps_executed array, a step_durations_s jsonb, params_used, the prefect_flow_run_id, and output_prefix. A session can have many runs (reprocessing, different pipelines).

Both link to a session by value on session_id. See Data Filtering Overview.

Diagnostics

Two tables explain why a recording struggled, both fed from the core's NVML probe:

• nvenc_known_contenders — an aggregated registry of third-party processes observed holding an NVENC (GPU hardware encoder) session on user machines while Playroll recorded. Keyed by process_name, with total_events, total_users, sample_codecs, sample_resolutions, and first/last-seen timestamps. This is our fleet-wide view of what competes with Playroll for the encoder (Discord, OBS, browser HW acceleration, other recorders).
• nvenc_contender_user_links — the per-(contender, user) link table backing the distinct-user count above. RLS-scoped so a user sees only their own links.

bug_reports is the in-app report channel (submitted via the report-bug edge function, service-role insert, rate-limited 2/min/user). Each report carries the title (1–200 chars) / description (1–10000) / category (ui / recording / performance / onboarding / other), status (logged [default] / in_progress / fixed / verified / wont_fix), the attached images, and full context (hw_config, connection, electron_runtime jsonb, version columns).

The linear_ticket link is not filled in manually. A bug_reports_to_linear trigger (AFTER INSERT, SECURITY DEFINER) fires on every insert and POSTs to the bug-report-to-linear edge function, which creates/links the Linear ticket and writes linear_ticket back onto the row. A separate bug_reports_set_updated_at trigger maintains updated_at. See Bug Reporting.

---

See also: full column reference for these tables.