Skip to content

Personal AI, On Personal Devices

Python
11.5 KB
Raw Blame
"""SQLite-backed trace storage."""

from __future__ import annotations

import json
import re
import sqlite3
import threading
from functools import wraps
from pathlib import Path
from typing import Any, List, Optional

from openjarvis.core.events import Event, EventBus, EventType
from openjarvis.core.types import StepType, Trace, TraceStep

_CREATE_TRACES = """\
CREATE TABLE IF NOT EXISTS traces (
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    trace_id             TEXT    NOT NULL UNIQUE,
    query                TEXT    NOT NULL DEFAULT '',
    agent                TEXT    NOT NULL DEFAULT '',
    model                TEXT    NOT NULL DEFAULT '',
    engine               TEXT    NOT NULL DEFAULT '',
    result               TEXT    NOT NULL DEFAULT '',
    outcome              TEXT,
    feedback             REAL,
    started_at           REAL    NOT NULL DEFAULT 0.0,
    ended_at             REAL    NOT NULL DEFAULT 0.0,
    total_tokens         INTEGER NOT NULL DEFAULT 0,
    total_latency_seconds REAL   NOT NULL DEFAULT 0.0,
    metadata             TEXT    NOT NULL DEFAULT '{}',
    messages             TEXT    NOT NULL DEFAULT '[]'
);
"""

_CREATE_STEPS = """\
CREATE TABLE IF NOT EXISTS trace_steps (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    trace_id         TEXT    NOT NULL,
    step_index       INTEGER NOT NULL,
    step_type        TEXT    NOT NULL,
    timestamp        REAL    NOT NULL DEFAULT 0.0,
    duration_seconds REAL    NOT NULL DEFAULT 0.0,
    input            TEXT    NOT NULL DEFAULT '{}',
    output           TEXT    NOT NULL DEFAULT '{}',
    metadata         TEXT    NOT NULL DEFAULT '{}',
    FOREIGN KEY (trace_id) REFERENCES traces(trace_id)
);
"""

_CREATE_FTS = """\
CREATE VIRTUAL TABLE IF NOT EXISTS traces_fts USING fts5(
    trace_id, query, result, agent,
    content='traces',
    content_rowid='rowid',
    tokenize='unicode61'
);
"""

_FTS_SYNC_INSERT = """\
CREATE TRIGGER IF NOT EXISTS traces_fts_ai AFTER INSERT ON traces BEGIN
    INSERT INTO traces_fts(rowid, trace_id, query, result, agent)
    VALUES (new.rowid, new.trace_id, new.query, new.result, new.agent);
END;
"""

_INSERT_TRACE = """\
INSERT INTO traces (
    trace_id, query, agent, model, engine, result,
    outcome, feedback, started_at, ended_at,
    total_tokens, total_latency_seconds, metadata, messages
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""

_INSERT_STEP = """\
INSERT INTO trace_steps (
    trace_id, step_index, step_type, timestamp,
    duration_seconds, input, output, metadata
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
"""


def _db_locked(func):
    """Serialize access to the connection shared by trace worker threads."""

    @wraps(func)
    def wrapped(self, *args, **kwargs):
        with self._db_lock:
            return func(self, *args, **kwargs)

    return wrapped


class TraceStore:
    """Append-only SQLite store for interaction traces."""

    def __init__(self, db_path: str | Path) -> None:
        self._db_path = (
            ":memory:"
            if str(db_path) == ":memory:"
            else str(Path(db_path).expanduser())
        )
        if self._db_path != ":memory:":
            from openjarvis.security.file_utils import secure_create

            secure_create(Path(self._db_path))
        # The AgenticRunner dispatches agent work to a ThreadPoolExecutor
        # (for Playwright compat), so trace writes may originate from a
        # different thread than the one that opened the connection.
        # check_same_thread=False permits that use, but WAL does not serialize
        # operations on one connection, so use a lock too.
        self._db_lock = threading.RLock()
        self._conn = sqlite3.connect(self._db_path, check_same_thread=False)
        self._conn.execute("PRAGMA journal_mode=WAL")
        self._conn.execute(_CREATE_TRACES)
        self._conn.execute(_CREATE_STEPS)
        self._conn.execute(_CREATE_FTS)
        self._conn.execute(_FTS_SYNC_INSERT)
        # Migrate: add messages column if missing (pre-existing databases)
        try:
            self._conn.execute(
                "ALTER TABLE traces ADD COLUMN messages TEXT NOT NULL DEFAULT '[]'"
            )
        except sqlite3.OperationalError:
            pass  # Column already exists
        self._conn.commit()

    @_db_locked
    def save(self, trace: Trace) -> None:
        """Persist a complete trace with all its steps.

        ``trace_id`` is a primary key: saving a second, different trace under
        an existing id raises ``sqlite3.IntegrityError`` (the external-corpus
        adapter relies on this to surface duplicate record ids). The server
        avoids re-saving the same trace by keeping the ``TraceCollector`` the
        single writer — see ``server/app.py`` — rather than swallowing
        collisions here.
        """
        self._conn.execute(
            _INSERT_TRACE,
            (
                trace.trace_id,
                trace.query,
                trace.agent,
                trace.model,
                trace.engine,
                trace.result,
                trace.outcome,
                trace.feedback,
                trace.started_at,
                trace.ended_at,
                trace.total_tokens,
                trace.total_latency_seconds,
                json.dumps(trace.metadata),
                json.dumps(trace.messages),
            ),
        )
        for idx, step in enumerate(trace.steps):
            self._conn.execute(
                _INSERT_STEP,
                (
                    trace.trace_id,
                    idx,
                    step.step_type.value
                    if isinstance(step.step_type, StepType)
                    else step.step_type,
                    step.timestamp,
                    step.duration_seconds,
                    json.dumps(step.input),
                    json.dumps(step.output),
                    json.dumps(step.metadata),
                ),
            )
        self._conn.commit()

    @_db_locked
    def get(self, trace_id: str) -> Optional[Trace]:
        """Retrieve a trace by id, or ``None`` if not found."""
        row = self._conn.execute(
            "SELECT * FROM traces WHERE trace_id = ?", (trace_id,)
        ).fetchone()
        if row is None:
            return None
        return self._row_to_trace(row)

    @_db_locked
    def list_traces(
        self,
        *,
        agent: Optional[str] = None,
        model: Optional[str] = None,
        outcome: Optional[str] = None,
        since: Optional[float] = None,
        until: Optional[float] = None,
        limit: int = 100,
    ) -> List[Trace]:
        """Query traces with optional filters."""
        clauses: List[str] = []
        params: List[Any] = []
        if agent is not None:
            clauses.append("agent = ?")
            params.append(agent)
        if model is not None:
            clauses.append("model = ?")
            params.append(model)
        if outcome is not None:
            clauses.append("outcome = ?")
            params.append(outcome)
        if since is not None:
            clauses.append("started_at >= ?")
            params.append(since)
        if until is not None:
            clauses.append("started_at <= ?")
            params.append(until)
        where = " AND ".join(clauses) if clauses else "1=1"
        sql = f"SELECT * FROM traces WHERE {where} ORDER BY started_at DESC LIMIT ?"
        params.append(limit)
        rows = self._conn.execute(sql, params).fetchall()
        return [self._row_to_trace(r) for r in rows]

    @_db_locked
    def count(self) -> int:
        """Return the total number of stored traces."""
        row = self._conn.execute("SELECT COUNT(*) FROM traces").fetchone()
        return row[0] if row else 0

    @_db_locked
    def search(
        self,
        query: str,
        *,
        agent: str | None = None,
        limit: int = 20,
    ) -> list[dict[str, Any]]:
        """Full-text search across traces. Optionally filter by agent.

        Parameters
        ----------
        query: Plain-text search query (FTS5 punctuation is handled internally).
        agent: Restrict results to traces recorded by this agent.
        limit: Maximum number of results to return.
        """
        terms = re.findall(r"\w+", query)
        if not terms:
            return []
        fts_query = " OR ".join(f'"{term}"' for term in terms)

        sql = (
            "SELECT t.trace_id, t.query, t.result, t.agent, t.model, t.outcome,"
            " t.started_at "
            "FROM traces_fts f JOIN traces t ON f.rowid = t.rowid "
            "WHERE traces_fts MATCH ?"
        )
        params: list[Any] = [fts_query]
        if agent:
            sql += " AND t.agent = ?"
            params.append(agent)
        sql += " ORDER BY rank LIMIT ?"
        params.append(limit)
        rows = self._conn.execute(sql, params).fetchall()
        return [
            {
                "trace_id": r[0],
                "query": r[1],
                "result": r[2],
                "agent": r[3],
                "model": r[4],
                "outcome": r[5],
                "started_at": r[6],
            }
            for r in rows
        ]

    def subscribe_to_bus(self, bus: EventBus) -> None:
        """Subscribe to ``TRACE_COMPLETE`` events on *bus*."""
        bus.subscribe(EventType.TRACE_COMPLETE, self._on_event)

    def _on_event(self, event: Event) -> None:
        trace = event.data.get("trace")
        if isinstance(trace, Trace):
            self.save(trace)

    @_db_locked
    def update_feedback(self, trace_id: str, score: float) -> bool:
        """Update the feedback score for a trace.

        Returns True if the trace was found and updated, False otherwise.
        """
        cursor = self._conn.execute(
            "UPDATE traces SET feedback = ? WHERE trace_id = ?",
            (score, trace_id),
        )
        self._conn.commit()
        return cursor.rowcount > 0

    @_db_locked
    def close(self) -> None:
        """Close the underlying SQLite connection."""
        self._conn.close()

    # -- internal helpers ------------------------------------------------------

    @_db_locked
    def _row_to_trace(self, row: tuple) -> Trace:
        """Convert a traces table row + its steps into a Trace object."""
        trace_id = row[1]
        step_rows = self._conn.execute(
            "SELECT * FROM trace_steps WHERE trace_id = ? ORDER BY step_index",
            (trace_id,),
        ).fetchall()
        steps = [
            TraceStep(
                step_type=StepType(sr[3]),
                timestamp=sr[4],
                duration_seconds=sr[5],
                input=json.loads(sr[6]),
                output=json.loads(sr[7]),
                metadata=json.loads(sr[8]),
            )
            for sr in step_rows
        ]
        # messages column (index 14) was added after metadata; handle
        # databases created before the column existed.
        messages_raw = row[14] if len(row) > 14 else "[]"
        return Trace(
            trace_id=trace_id,
            query=row[2],
            agent=row[3],
            model=row[4],
            engine=row[5],
            result=row[6],
            outcome=row[7],
            feedback=row[8],
            started_at=row[9],
            ended_at=row[10],
            total_tokens=row[11],
            total_latency_seconds=row[12],
            metadata=json.loads(row[13]),
            messages=json.loads(messages_raw),
            steps=steps,
        )

    @_db_locked
    def _fetchall(self, sql: str = "SELECT * FROM traces") -> list:
        return self._conn.execute(sql).fetchall()


__all__ = ["TraceStore"]