Documentation

SQLite Storage

Local trace database powered by SQLite.

SQLite

Storage

TraceLLM uses SQLite for trace storage. The database is automatically created at ~/.tracellm/traces.db.

No configuration required:

No database setup required

Tracey Guide

SQLite is bundled with Python and auto-creates ~/.tracellm/traces.db on first run. No setup required.

TraceLLM uses SQLite as its persistent store for all trace records, project records, and API keys. The database file is automatically created at ~/.tracellm/traces.dbon first run. The async connection is managed via the aiosqlite driver, which integrates natively with FastAPI's async event loop.

Auto-Setup

SQLite requires zero configuration. On first run, TraceLLM automatically:

Creates the ~/.tracellm/ directory if it does not exist
Creates the traces.db SQLite database file
Creates all required tables (traces, projects, api_keys)
Creates indexes for efficient querying

Info

No external services needed. Everything runs locally in a single file.

Tables & Schema

Three SQLite tables store all TraceLLM data:

Table	Schema Model	Purpose	Key Indexes
traces	TraceSchema	Full trace documents with steps, metadata, status	trace_id, created_at, status, model_name, project_id, environment
projects	ProjectSchema	Project records with name, description, timestamps	project_id (unique), name (unique)
api_keys	ApiKeySchema	API key records with key hash, project, environment	key (unique), project_id, environment

Each trace document follows the TraceSchema Pydantic model, which enforces field types, defaults, and validators at both write and read boundaries:

Schema definitionsCopy

python

TraceSchema:
  trace_id: str           # UUID4, prefixed "tr_"
  prompt: str              # Input prompt or operation name
  response: Optional[str]  # LLM or system response text
  latency: float           # Total execution time in ms (>= 0)
  token_count: int         # Estimated or actual tokens (>= 0)
  model_name: Optional[str]# Model identifier (e.g. gpt-4o)
  project_id: str          # Project grouping ("default")
  project_name: Optional[str]
  api_key: Optional[str]   # Stored for audit purposes
  environment: str         # "development", "staging", "production"
  status: Literal["success", "warning", "failed"]
  steps: list[StepSchema]  # Ordered execution steps
  retry_count: int         # Number of retries
  slow_request: bool       # True if latency >= 1500ms
  failure_reason: Optional[str]
  created_at: datetime     # Execution start (UTC)
  updated_at: datetime     # Persistence time (UTC)

StepSchema:
  step_id: str             # UUID4
  tool_name: str           # e.g. "vector_retrieval"
  input: dict              # Input parameters
  output: dict             # Returned result
  duration: float          # Wall-clock time in ms (>= 0)
  success: bool            # Completed without error
  timestamp: datetime      # Execution time (UTC)

Index Strategy

Indexes are created automatically during the FastAPI startup event via the on_event("startup") handler. The creation functions are idempotent and safe to call on every restart:

SQLite indexesCopy

sql

# Indexes are created automatically on first run:
# traces table
CREATE INDEX idx_traces_id ON traces(trace_id);
CREATE INDEX idx_traces_created ON traces(created_at);
CREATE INDEX idx_traces_status ON traces(status);
CREATE INDEX idx_traces_model ON traces(model_name);
CREATE INDEX idx_traces_project ON traces(project_id);
CREATE INDEX idx_traces_env ON traces(environment);

# projects table
CREATE UNIQUE INDEX idx_projects_id ON projects(project_id);
CREATE UNIQUE INDEX idx_projects_name ON projects(name);

# api_keys table
CREATE UNIQUE INDEX idx_keys_key ON api_keys(key);
CREATE INDEX idx_keys_project ON api_keys(project_id);
CREATE INDEX idx_keys_env ON api_keys(environment);

Info

The traces table indexes support all filter combinations used by the dashboard: status + project, model + environment, latency range + status, and time-sorted queries for the analytics time-series charts.

Trace Normalization Pipeline

Before insertion, every trace document passes through a normalization pipeline in normalize_trace_document() (in trace_service.py):

Normalization pipelineCopy

text

Input: raw trace dict from @trace/CLI
  │
  ├── 1. Parse created_at ──► _coerce_datetime()
  │      Supports datetime objects, ISO strings, or falls back to utcnow()
  │
  ├── 2. Normalize steps ──► _normalize_steps()
  │      Maps input/input_data, output/output_data keys
  │      Validates each step against StepSchema
  │      Generates step_id if missing
  │
  ├── 3. Infer retry count ──► _infer_retry_count()
  │      Counts duplicate tool_name occurrences in step list
  │      Uses explicit retry_count if provided
  │
  ├── 4. Infer status ──► _infer_status()
  │      explicit status > any failed step > failure_reason/retries > success
  │
  ├── 5. Infer failure_reason ──► _infer_failure_reason()
  │      explicit message > first failed step's output.error > tool_name
  │
  ├── 6. Set slow_request flag
  │      True if latency >= SLOW_TRACE_THRESHOLD_MS (1500ms)
  │
  └── 7. Validate ──► TraceSchema.model_dump(mode="python")
         Pydantic validation catches negative values, wrong types, etc.

Output: clean SQLite record

Common Query Patterns

The trace service provides these query patterns used by the API and dashboard:

SQLite query patternsCopy

sql

-- List traces with filters
SELECT * FROM traces
WHERE status = 'failed'
  AND project_id = 'my-app'
  AND environment = 'production'
  AND latency BETWEEN 100 AND 5000
  AND token_count >= 50
ORDER BY created_at DESC
LIMIT 50;

-- Get single trace
SELECT * FROM traces WHERE trace_id = 'tr_2kf9q3m1';

-- Analytics - all traces in date order
SELECT * FROM traces ORDER BY created_at ASC;

-- Failures - recent failed/retry/slow traces
SELECT * FROM traces
WHERE status = 'failed'
   OR retry_count > 0
   OR slow_request = 1
ORDER BY created_at DESC
LIMIT 25;

SQLite Auto-Creation

SQLite requires no setup. The database is automatically created on first run:

SQLite verificationCopy

bash

# The database is auto-created — nothing to install or configure
# Database location: ~/.tracellm/traces.db

# Verify the database exists:
ls -la ~/.tracellm/traces.db

# Inspect tables using sqlite3:
sqlite3 ~/.tracellm/traces.db ".tables"
sqlite3 ~/.tracellm/traces.db ".schema traces"

Tip

SQLite is bundled with Python 3.x — no additional installation needed. The database file is created at ~/.tracellm/traces.db automatically when you run tracellm start for the first time.