Skip to content

Refactor TelemetryCollector to persist LLM usage data in SQLite #603

Description

@haiphucnguyen

Summary

Currently, TelemetryCollector stores aggregated metrics in a flat JSON file via
TelemetryPersistenceManager. This approach only supports simple counters (totals,
averages) and cannot answer time-scoped queries like:

  • "How many tokens did I use this month?"
  • "Which model did I use most last week?"
  • "What is my daily usage trend over the past 30 days?"

To support these queries, we need to persist individual LLM call records to the
SQLite database managed by DatabaseManager.

Proposed Changes

1. New llm_usage_records table (in DatabaseManager.initializeTables())

CREATE TABLE llm_usage_records (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp      INTEGER NOT NULL,         -- epoch ms
    provider       TEXT    NOT NULL,
    model          TEXT    NOT NULL,
    instance_id    TEXT,                     -- nullable, "$instanceId:$model" key
    prompt_tokens  INTEGER NOT NULL DEFAULT 0,
    output_tokens  INTEGER NOT NULL DEFAULT 0,
    total_tokens   INTEGER NOT NULL DEFAULT 0,
    duration_ms    INTEGER NOT NULL DEFAULT 0,
    is_error       INTEGER NOT NULL DEFAULT 0  -- 0=success, 1=error
);

2. New LlmUsageRepository

Extends AbstractSQLiteRepository
Methods:
insert(record: LlmUsageRecord)
queryByDateRange(from: Instant, to: Instant): List
queryByProvider(provider: String): List
sumTokensByPeriod(from: Instant, to: Instant): Long
groupByDay(from: Instant, to: Instant): Map<LocalDate, Long>

Use DatabaseManager to create the new table

3. Refactor TelemetryCollector

Inject LlmUsageRepository (or a nullable optional for CLI/stateless mode)
In recordLLMCall(): insert a row into llm_usage_records in addition to updating the in-memory counters
In recordLLMError(): insert a row with is_error = 1
Keep the in-memory aggregates for live/reactive UI (metricsFlow)
Deprecate TelemetryPersistenceManager flat-file save for LLM metrics. Remove the RAG support too

4. Update getMetrics() / TelemetryMetrics

Add computed properties sourced from DB queries:
tokensThisMonth: Long
tokensThisWeek: Long
callsThisMonth: Int
Keep existing aggregated fields for backward compatibility

5. Wiring

Register LlmUsageRepository in Koin (DesktopModule) and inject into TelemetryCollector

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or requesthelp wantedExtra attention is needed

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions