Data Model

Dashcam stores everything in a single SQLite database at ~/.dashcam/dashcam.db. The schema is designed for one thing above all else: fast timeline queries. Scrubbing through a 5-hour session with 10,000 events at 60fps demands that range queries on (session_id, timestamp) resolve in single-digit milliseconds. Every table, index, and denormalized field exists to serve that goal.

Database Configuration

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

WAL mode (Write-Ahead Logging) enables concurrent reads and writes. The dashboard can query historical sessions while the collector inserts new events without contention. WAL is checkpointed on clean shutdown via dashcam stop.

Tables

`windows`

Tracks 5-hour Claude Code subscription windows.

CREATE TABLE windows (
  id TEXT PRIMARY KEY,                  -- ULID
  started_at TEXT NOT NULL,             -- ISO 8601
  ended_at TEXT,                        -- ISO 8601 (NULL if active)
  duration_seconds INTEGER,             -- Computed on close
  session_count INTEGER DEFAULT 0,
  event_count INTEGER DEFAULT 0,
  total_cost_usd REAL DEFAULT 0.0,
  is_active INTEGER DEFAULT 0           -- Boolean
);

A window begins when the first SessionStart hook fires after a cooldown. It ends when the 5-hour duration expires or the daemon shuts down cleanly. The is_active flag is indexed for fast "current window" lookups.

`sessions`

Individual agent sessions within a window. A single window may contain multiple sessions (sequential or parallel).

CREATE TABLE sessions (
  id TEXT PRIMARY KEY,                  -- ULID or Claude Code session ID
  window_id TEXT REFERENCES windows(id),
  project_path TEXT NOT NULL,
  started_at TEXT NOT NULL,
  ended_at TEXT,
  duration_seconds INTEGER,
  agent_id TEXT,                        -- Unique agent identifier (multi-agent)
  agent_type TEXT,                      -- 'claude-code', 'loki-frontend', etc.
  agent_source TEXT,                    -- 'claude-code', 'loki-mode', 'claude-squad'
  model TEXT,                           -- 'claude-opus-4-6', 'claude-sonnet-4-6'
  summary TEXT,                         -- Auto-generated session summary
  thumbnail_path TEXT,                  -- Path to session thumbnail
  status TEXT DEFAULT 'active',         -- 'active', 'completed', 'error', 'abandoned'
  total_events INTEGER DEFAULT 0,
  total_tokens_in INTEGER DEFAULT 0,
  total_tokens_out INTEGER DEFAULT 0,
  estimated_cost_usd REAL DEFAULT 0.0
);

The summary field is auto-generated from the session's events — a one-line description like "Built auth system with JWT tokens and user dashboard" that appears on the session card in the browser.

`events`

The core event stream. Every hook event, JSONL entry, and bridge event lands here.

CREATE TABLE events (
  id TEXT PRIMARY KEY,                  -- ULID
  session_id TEXT NOT NULL REFERENCES sessions(id),
  timestamp TEXT NOT NULL,              -- ISO 8601, microsecond precision
  event_type TEXT NOT NULL,             -- See event types below
  agent_id TEXT,                        -- For multi-agent swim lanes
  data TEXT NOT NULL,                   -- Full JSON payload
  summary TEXT,                         -- One-line auto-generated summary
  parent_event_id TEXT,                 -- Threading (tool_use → tool_result)

  -- Denormalized for fast timeline queries
  tool_name TEXT,                       -- 'Write', 'Edit', 'Bash', etc.
  file_path TEXT,                       -- Primary file affected
  tokens_in INTEGER,
  tokens_out INTEGER,
  duration_ms INTEGER                   -- For tool executions
);

Event types:

Type	Source	Description
`user_message`	JSONL	User prompt to the agent
`assistant_message`	JSONL	Agent response
`thinking`	JSONL	Agent thinking/reasoning block
`tool_use`	Hook / JSONL	Tool invocation (Write, Edit, Bash, etc.)
`tool_result`	Hook / JSONL	Tool execution result
`decision`	Extracted	Architectural decision with reasoning
`error`	Hook / JSONL	Error or failure
`milestone`	Extracted	Task or phase completion
`session_start`	Hook	Session began
`session_end`	Hook	Session ended
`subagent_start`	Hook	Subagent spawned
`subagent_stop`	Hook	Subagent finished
`worktree_create`	Hook	Git worktree created
`worktree_remove`	Hook	Git worktree removed
`rarv_reason`	Loki bridge	RARV Reason phase
`rarv_act`	Loki bridge	RARV Act phase
`rarv_reflect`	Loki bridge	RARV Reflect phase
`rarv_verify`	Loki bridge	RARV Verify phase
`quality_gate`	Loki bridge	Quality gate result
`test_run`	Extracted	Test execution with results

The data column stores the full JSON payload for deep inspection. The denormalized columns (tool_name, file_path, tokens_in, tokens_out, duration_ms) exist so timeline queries can filter and display events without parsing JSON.

`captures`

Screenshots and videos linked to events and sessions.

CREATE TABLE captures (
  id TEXT PRIMARY KEY,                  -- ULID
  event_id TEXT REFERENCES events(id),  -- Triggering event
  session_id TEXT NOT NULL REFERENCES sessions(id),
  timestamp TEXT NOT NULL,
  capture_type TEXT NOT NULL,           -- 'screenshot', 'video', 'terminal'
  file_path TEXT NOT NULL,              -- Path to PNG/WebM
  thumbnail_path TEXT,                  -- Path to thumbnail
  file_size_bytes INTEGER,
  width INTEGER,
  height INTEGER,
  duration_ms INTEGER,                  -- Videos only
  perceptual_hash TEXT,                 -- Average hash hex for dedup
  capture_url TEXT,                     -- URL that was captured
  metadata TEXT                         -- JSON: additional metadata
);

The perceptual_hash column enables deduplication — if a new capture has the same hash as the previous one, it is discarded without writing to disk.

`decisions`

Architectural decisions extracted from agent thinking blocks.

CREATE TABLE decisions (
  id TEXT PRIMARY KEY,                  -- ULID
  event_id TEXT REFERENCES events(id),
  session_id TEXT NOT NULL REFERENCES sessions(id),
  timestamp TEXT NOT NULL,
  category TEXT,                        -- 'architecture', 'library', 'pattern', 'refactor', 'bugfix'
  title TEXT NOT NULL,
  reasoning TEXT NOT NULL,
  alternatives TEXT,                    -- JSON array of alternatives considered
  outcome TEXT,                         -- What was actually done
  confidence TEXT                       -- 'high', 'medium', 'low'
);

Decisions are extracted by parsing assistant messages and thinking blocks for decision-like patterns ("I chose X because Y", "After considering A and B, I went with C"). They provide a searchable log of the agent's reasoning at each choice point.

`git_snapshots`

Point-in-time captures of git state during a session.

CREATE TABLE git_snapshots (
  id TEXT PRIMARY KEY,                  -- ULID
  event_id TEXT REFERENCES events(id),
  session_id TEXT NOT NULL,
  timestamp TEXT NOT NULL,
  commit_hash TEXT,
  branch TEXT,
  diff_summary TEXT,                    -- "+42 -17 across 5 files"
  changed_files TEXT,                   -- JSON array of file paths
  worktree_path TEXT                    -- If using a git worktree
);

Git snapshots power the Code panel. When you scrub the playhead to a specific moment, the Code panel queries git_snapshots for the nearest snapshot and renders the diff.

Key Indexes

Every index is designed for a specific query pattern:

-- Timeline scrubbing: "give me events for this session in this time range"
CREATE INDEX idx_events_session_time ON events(session_id, timestamp);

-- Swim lane rendering: "give me events for this agent in this time range"
CREATE INDEX idx_events_agent_time ON events(agent_id, timestamp);

-- Event filtering: "show only tool_use events"
CREATE INDEX idx_events_type ON events(event_type);

-- Tool filtering: "show only Write events" (partial index, skips NULLs)
CREATE INDEX idx_events_tool ON events(tool_name) WHERE tool_name IS NOT NULL;

-- Session browser: "show active sessions" or "sessions for this date"
CREATE INDEX idx_sessions_started ON sessions(started_at);
CREATE INDEX idx_sessions_status ON sessions(status);
CREATE INDEX idx_sessions_project ON sessions(project_path);

-- Window queries: "current active window"
CREATE INDEX idx_windows_active ON windows(is_active) WHERE is_active = 1;
CREATE INDEX idx_windows_started ON windows(started_at);

-- Capture timeline: "screenshots for this session"
CREATE INDEX idx_captures_session ON captures(session_id, timestamp);

The compound index idx_events_session_time is the most critical. It enables the timeline to query a time range of events for a session in O(log n) time, which is essential for smooth 60fps scrubbing across sessions with tens of thousands of events.

Full-Text Search

Dashcam uses SQLite's FTS5 extension for full-text search across event summaries and data:

CREATE VIRTUAL TABLE events_fts USING fts5(
  summary,
  data,
  content=events,
  content_rowid=rowid
);

-- Trigger keeps FTS index in sync with events table
CREATE TRIGGER events_ai AFTER INSERT ON events BEGIN
  INSERT INTO events_fts(rowid, summary, data)
  VALUES (new.rowid, new.summary, new.data);
END;

This powers the search bar in the dashboard (Cmd+K / Ctrl+K) and the GET /api/search endpoint. Search queries match against event summaries and full JSON payloads, returning results with session context and timeline positions.

Cost Estimation

Token costs are calculated per event using model-specific rates:

const MODEL_COSTS = {
  "claude-opus-4-6":   { input: 15.00, output: 75.00 },  // per 1M tokens
  "claude-sonnet-4-6": { input: 3.00,  output: 15.00 },
  "claude-haiku-4-5":  { input: 0.80,  output: 4.00 },
} as const;

Cost is derived from tokens_in and tokens_out on each event, then aggregated up to sessions and windows. The Cost panel in the dashboard renders a cumulative cost graph aligned with the timeline, broken down by model.

The entire database typically stays under 50MB even after months of use. Events are compact (roughly 1KB each), and screenshots are stored as files on disk, not in the database. Only file paths and metadata live in SQLite.

Data Model

On this page