Database Schema

SQLite schema for sessions and events.

AgentLens uses better-sqlite3 for embedded, zero-configuration persistence. The database file (agentlens.db) is created automatically in the backend/ directory.

Pragmas

PRAGMA journal_mode = WAL;     -- Write-Ahead Logging for concurrent reads/writes
PRAGMA foreign_keys = ON;      -- Enforce foreign key constraints

Sessions Table

CREATE TABLE sessions (
    session_id      TEXT PRIMARY KEY,
    agent_name      TEXT NOT NULL DEFAULT 'default-agent',
    started_at      TEXT NOT NULL,          -- ISO 8601 timestamp
    ended_at        TEXT,                   -- NULL while active
    metadata        TEXT DEFAULT '{}',      -- JSON string
    total_tokens_in INTEGER DEFAULT 0,
    total_tokens_out INTEGER DEFAULT 0,
    status          TEXT DEFAULT 'active'   -- active | completed | error
);

Events Table

CREATE TABLE events (
    event_id        TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL,
    event_type      TEXT NOT NULL DEFAULT 'generic',
    timestamp       TEXT NOT NULL,          -- ISO 8601
    input_data      TEXT,                   -- JSON string
    output_data     TEXT,                   -- JSON string
    model           TEXT,
    tokens_in       INTEGER DEFAULT 0,
    tokens_out      INTEGER DEFAULT 0,
    tool_call       TEXT,                   -- JSON string
    decision_trace  TEXT,                   -- JSON string
    duration_ms     REAL,
    FOREIGN KEY (session_id) REFERENCES sessions(session_id)
);

Indexes

CREATE INDEX idx_events_session   ON events(session_id);    -- Fast event lookup by session
CREATE INDEX idx_events_timestamp ON events(timestamp);     -- Time-range queries
CREATE INDEX idx_sessions_status  ON sessions(status);      -- Filter by status

Data Flow

When the /events endpoint receives a batch:

1. Transaction: All events in the batch are processed in a single SQLite transaction for atomicity.
2. Session auto-creation: If an event references a session_id that doesn't exist, a session record is created automatically with INSERT OR IGNORE.
3. Token aggregation: After each event insert, the session's total_tokens_in and total_tokens_out are incremented.
4. JSON fields: input_data, output_data, tool_call, decision_trace, and metadata are stored as JSON strings and parsed on read.

Backup

To back up the database, simply copy the agentlens.db file (and the -wal and -shm files if they exist):

cp backend/agentlens.db backup/agentlens-$(date +%Y%m%d).db