SQLite Schema
The tables, migration scheme, and indexes used by tinyreplay.
Everything lives in one SQLite file (tinyreplay.db) opened in WAL mode with
foreign keys on. The schema is three tables and a handful of indexes.
sessions
One row per recorded session. Counters are maintained as batches arrive.
CREATE TABLE sessions (
id TEXT PRIMARY KEY,
project_id TEXT NOT NULL,
started_at INTEGER NOT NULL,
ended_at INTEGER,
duration_ms INTEGER GENERATED ALWAYS AS (
CASE WHEN ended_at IS NOT NULL THEN ended_at - started_at ELSE NULL END
) VIRTUAL,
url TEXT NOT NULL,
user_agent TEXT,
viewport_w INTEGER,
viewport_h INTEGER,
device_type TEXT,
page_count INTEGER NOT NULL DEFAULT 1,
event_count INTEGER NOT NULL DEFAULT 0,
error_count INTEGER NOT NULL DEFAULT 0,
created_at INTEGER NOT NULL DEFAULT (unixepoch('now') * 1000)
);duration_ms is a generated column - it is never written, only derived from
started_at and ended_at.
events
The raw rrweb stream, stored as JSON batches keyed by session and sequence.
CREATE TABLE events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
seq INTEGER NOT NULL,
events_json TEXT NOT NULL,
received_at INTEGER NOT NULL DEFAULT (unixepoch('now') * 1000)
);Cascade delete
ON DELETE CASCADE means deleting a session row removes all of its event
batches automatically - this is what makes retention
a single DELETE on sessions.
Indexes
CREATE INDEX idx_events_session_id ON events(session_id);
CREATE INDEX idx_sessions_started_at ON sessions(started_at DESC);
CREATE INDEX idx_sessions_project_id ON sessions(project_id);These cover the hot paths: load a session's events, list sessions newest-first, and filter by project.
settings
Small dashboard settings are stored by key.
CREATE TABLE settings (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);The current server uses this table for values such as a persisted retention override.
Migrations
Schema changes use SQLite's PRAGMA user_version as a monotonic counter. On
open, every migration whose 1-based index exceeds the stored version runs inside
a transaction, then the version is stamped.
V1 creates sessions, events, and the indexes.
V2 adds error_count and backfills it in pure SQL via JSON1, guarded by
json_valid so a corrupt old batch can't abort the deploy.
V3 creates settings for persisted dashboard settings.
The rule: append new migrations, never edit shipped ones.
Next
How those JSON batches become a playable session: the replay engine.