Skip to content

Personal AI, On Personal Devices

Python
17.1 KB
Raw Blame
"""SQLite-backed telemetry storage."""

from __future__ import annotations

import json
import logging
import sqlite3
import threading
import time
from pathlib import Path
from typing import Any

from openjarvis.core.events import Event, EventBus, EventType
from openjarvis.core.types import TelemetryRecord

logger = logging.getLogger(__name__)

_CREATE_TABLE = """\
CREATE TABLE IF NOT EXISTS telemetry (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp       REAL    NOT NULL,
    model_id        TEXT    NOT NULL,
    engine          TEXT    NOT NULL DEFAULT '',
    agent           TEXT    NOT NULL DEFAULT '',
    prompt_tokens   INTEGER NOT NULL DEFAULT 0,
    prompt_tokens_evaluated INTEGER NOT NULL DEFAULT 0,
    completion_tokens INTEGER NOT NULL DEFAULT 0,
    total_tokens    INTEGER NOT NULL DEFAULT 0,
    latency_seconds REAL    NOT NULL DEFAULT 0.0,
    ttft            REAL    NOT NULL DEFAULT 0.0,
    cost_usd        REAL    NOT NULL DEFAULT 0.0,
    energy_joules   REAL    NOT NULL DEFAULT 0.0,
    power_watts     REAL    NOT NULL DEFAULT 0.0,
    gpu_utilization_pct  REAL NOT NULL DEFAULT 0.0,
    gpu_memory_used_gb   REAL NOT NULL DEFAULT 0.0,
    gpu_temperature_c    REAL NOT NULL DEFAULT 0.0,
    throughput_tok_per_sec REAL NOT NULL DEFAULT 0.0,
    prefill_latency_seconds REAL NOT NULL DEFAULT 0.0,
    decode_latency_seconds  REAL NOT NULL DEFAULT 0.0,
    energy_method   TEXT    NOT NULL DEFAULT '',
    energy_vendor   TEXT    NOT NULL DEFAULT '',
    batch_id        TEXT    NOT NULL DEFAULT '',
    is_warmup       INTEGER NOT NULL DEFAULT 0,
    cpu_energy_joules    REAL NOT NULL DEFAULT 0.0,
    gpu_energy_joules    REAL NOT NULL DEFAULT 0.0,
    dram_energy_joules   REAL NOT NULL DEFAULT 0.0,
    ane_energy_joules    REAL NOT NULL DEFAULT 0.0,
    soc_energy_joules    REAL NOT NULL DEFAULT 0.0,
    -- Rail set the derived per-token/per-watt columns were computed from:
    -- 'soc' on Apple Silicon, 'gpu' elsewhere. '' on rows written before the
    -- column existed, which are GPU-basis by construction.
    energy_basis    TEXT    NOT NULL DEFAULT '',
    tokens_per_joule     REAL NOT NULL DEFAULT 0.0,
    energy_per_output_token_joules REAL NOT NULL DEFAULT 0.0,
    throughput_per_watt  REAL NOT NULL DEFAULT 0.0,
    prefill_energy_joules REAL NOT NULL DEFAULT 0.0,
    decode_energy_joules REAL NOT NULL DEFAULT 0.0,
    mean_itl_ms          REAL NOT NULL DEFAULT 0.0,
    median_itl_ms        REAL NOT NULL DEFAULT 0.0,
    p90_itl_ms           REAL NOT NULL DEFAULT 0.0,
    p95_itl_ms           REAL NOT NULL DEFAULT 0.0,
    p99_itl_ms           REAL NOT NULL DEFAULT 0.0,
    std_itl_ms           REAL NOT NULL DEFAULT 0.0,
    is_streaming         INTEGER NOT NULL DEFAULT 0,
    -- token_counting_version: nullable on purpose. Records inserted by
    -- pre-fix builds left this NULL; the leaderboard aggregator treats
    -- NULL as legacy and excludes those rows from per-token efficiency
    -- sums (so the bimodal-Wh/token leaderboard population disappears).
    token_counting_version INTEGER,
    mining_session_id    TEXT,
    metadata        TEXT    NOT NULL DEFAULT '{}'
);
"""

_CREATE_MINING_STATS_TABLE = """\
CREATE TABLE IF NOT EXISTS mining_stats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    recorded_at REAL NOT NULL,
    provider_id TEXT NOT NULL,
    shares_submitted INTEGER NOT NULL DEFAULT 0,
    shares_accepted INTEGER NOT NULL DEFAULT 0,
    blocks_found INTEGER NOT NULL DEFAULT 0,
    hashrate REAL NOT NULL DEFAULT 0,
    uptime_seconds REAL NOT NULL DEFAULT 0,
    last_share_at REAL,
    last_error TEXT,
    payout_target TEXT NOT NULL DEFAULT 'solo',
    fees_owed INTEGER NOT NULL DEFAULT 0
);
"""

_INSERT = """\
INSERT INTO telemetry (
    timestamp, model_id, engine, agent,
    prompt_tokens, prompt_tokens_evaluated, completion_tokens, total_tokens,
    latency_seconds, ttft, cost_usd, energy_joules, power_watts,
    gpu_utilization_pct, gpu_memory_used_gb, gpu_temperature_c,
    throughput_tok_per_sec, prefill_latency_seconds, decode_latency_seconds,
    energy_method, energy_vendor, batch_id, is_warmup,
    cpu_energy_joules, gpu_energy_joules, dram_energy_joules,
    ane_energy_joules, soc_energy_joules, energy_basis,
    tokens_per_joule,
    energy_per_output_token_joules, throughput_per_watt,
    prefill_energy_joules, decode_energy_joules,
    mean_itl_ms, median_itl_ms, p90_itl_ms, p95_itl_ms, p99_itl_ms, std_itl_ms,
    is_streaming,
    token_counting_version,
    mining_session_id,
    metadata
) VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?
)
"""

_INSERT_MINING = """\
INSERT INTO mining_stats (
    recorded_at, provider_id, shares_submitted, shares_accepted, blocks_found,
    hashrate, uptime_seconds, last_share_at, last_error, payout_target, fees_owed
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""

_MIGRATE_COLUMNS = [
    ("gpu_utilization_pct", "REAL NOT NULL DEFAULT 0.0"),
    ("gpu_memory_used_gb", "REAL NOT NULL DEFAULT 0.0"),
    ("gpu_temperature_c", "REAL NOT NULL DEFAULT 0.0"),
    ("throughput_tok_per_sec", "REAL NOT NULL DEFAULT 0.0"),
    ("prefill_latency_seconds", "REAL NOT NULL DEFAULT 0.0"),
    ("decode_latency_seconds", "REAL NOT NULL DEFAULT 0.0"),
    ("energy_method", "TEXT NOT NULL DEFAULT ''"),
    ("energy_vendor", "TEXT NOT NULL DEFAULT ''"),
    ("batch_id", "TEXT NOT NULL DEFAULT ''"),
    ("is_warmup", "INTEGER NOT NULL DEFAULT 0"),
    ("cpu_energy_joules", "REAL NOT NULL DEFAULT 0.0"),
    ("gpu_energy_joules", "REAL NOT NULL DEFAULT 0.0"),
    ("dram_energy_joules", "REAL NOT NULL DEFAULT 0.0"),
    ("ane_energy_joules", "REAL NOT NULL DEFAULT 0.0"),
    ("soc_energy_joules", "REAL NOT NULL DEFAULT 0.0"),
    ("energy_basis", "TEXT NOT NULL DEFAULT ''"),
    ("tokens_per_joule", "REAL NOT NULL DEFAULT 0.0"),
    ("energy_per_output_token_joules", "REAL NOT NULL DEFAULT 0.0"),
    ("throughput_per_watt", "REAL NOT NULL DEFAULT 0.0"),
    ("prefill_energy_joules", "REAL NOT NULL DEFAULT 0.0"),
    ("decode_energy_joules", "REAL NOT NULL DEFAULT 0.0"),
    ("mean_itl_ms", "REAL NOT NULL DEFAULT 0.0"),
    ("median_itl_ms", "REAL NOT NULL DEFAULT 0.0"),
    ("p90_itl_ms", "REAL NOT NULL DEFAULT 0.0"),
    ("p95_itl_ms", "REAL NOT NULL DEFAULT 0.0"),
    ("p99_itl_ms", "REAL NOT NULL DEFAULT 0.0"),
    ("std_itl_ms", "REAL NOT NULL DEFAULT 0.0"),
    ("is_streaming", "INTEGER NOT NULL DEFAULT 0"),
    ("prompt_tokens_evaluated", "INTEGER NOT NULL DEFAULT 0"),
    # `token_counting_version` is nullable on purpose — rows that existed
    # before this migration ran predate per-record versioning and the
    # aggregator filter treats them as legacy.
    ("token_counting_version", "INTEGER"),
    ("mining_session_id", "TEXT"),
]


class TelemetryStore:
    """Append-only SQLite store for inference telemetry records.

    Writes are batched in memory and flushed to SQLite when a batch reaches
    ``batch_size``, when ``flush_interval_seconds`` elapses (a background
    flusher thread guarantees this even with no further writes), on any read
    through this store, and on ``close()``. Readers that open their OWN
    connection to the database file (e.g. ``TelemetryAggregator``) therefore
    see new rows within ``flush_interval_seconds`` at the latest; call
    ``flush()`` first for immediate visibility. Pass
    ``flush_interval_seconds=0`` to disable time-based flushing (batch-size
    and read/close flushes still apply).
    """

    def __init__(
        self,
        db_path: str | Path,
        batch_size: int = 50,
        flush_interval_seconds: float = 5.0,
    ) -> None:
        if batch_size < 1:
            raise ValueError("batch_size must be >= 1")
        if flush_interval_seconds < 0:
            raise ValueError("flush_interval_seconds must be >= 0")

        self._db_path = str(db_path)
        if self._db_path != ":memory:":
            from openjarvis.security.file_utils import secure_create

            secure_create(Path(self._db_path))
        self._conn = sqlite3.connect(self._db_path, check_same_thread=False)
        self._lock = threading.Lock()
        self._conn.execute("PRAGMA journal_mode=WAL")
        self._conn.execute("PRAGMA synchronous=NORMAL")
        self._conn.execute("PRAGMA busy_timeout=5000")
        self._conn.execute(_CREATE_TABLE)
        self._conn.execute(_CREATE_MINING_STATS_TABLE)
        self._conn.commit()
        self._migrate_schema()

        self._batch_size = batch_size
        self._flush_interval_seconds = flush_interval_seconds
        self._last_flush_time = time.monotonic()
        self._telemetry_batch: list[tuple[Any, ...]] = []
        self._mining_batch: list[tuple[Any, ...]] = []
        self._closed = False
        self._event_bus: EventBus | None = None

        # Background flusher: without it, a partial batch written just before
        # traffic stops would stay invisible to other connections until the
        # NEXT write arrived (the stale check in ``_maybe_flush_unlocked``
        # only runs inside record calls). Daemon so it never blocks exit.
        self._stop_flusher = threading.Event()
        self._flusher: threading.Thread | None = None
        if flush_interval_seconds > 0:
            self._flusher = threading.Thread(
                target=self._flush_loop,
                name="telemetry-store-flusher",
                daemon=True,
            )
            self._flusher.start()

    def _flush_loop(self) -> None:
        """Periodically flush pending batches until ``close()`` stops us."""
        while not self._stop_flusher.wait(self._flush_interval_seconds):
            with self._lock:
                # ``close()`` sets the event BEFORE taking the lock, so seeing
                # it unset here means the connection is still open.
                if self._stop_flusher.is_set():
                    break
                if self._telemetry_batch or self._mining_batch:
                    self._flush_unlocked()

    def _migrate_schema(self) -> None:
        """Add new columns to existing databases (idempotent)."""
        for col_name, col_def in _MIGRATE_COLUMNS:
            try:
                self._conn.execute(
                    f"ALTER TABLE telemetry ADD COLUMN {col_name} {col_def}",
                )
            except sqlite3.OperationalError:
                pass  # Column already exists — safe to ignore
        self._conn.commit()

    def record(self, rec: TelemetryRecord) -> None:
        """Persist a single telemetry record."""
        row = (
            rec.timestamp,
            rec.model_id,
            rec.engine,
            rec.agent,
            rec.prompt_tokens,
            rec.prompt_tokens_evaluated,
            rec.completion_tokens,
            rec.total_tokens,
            rec.latency_seconds,
            rec.ttft,
            rec.cost_usd,
            rec.energy_joules,
            rec.power_watts,
            rec.gpu_utilization_pct,
            rec.gpu_memory_used_gb,
            rec.gpu_temperature_c,
            rec.throughput_tok_per_sec,
            rec.prefill_latency_seconds,
            rec.decode_latency_seconds,
            rec.energy_method,
            rec.energy_vendor,
            rec.batch_id,
            1 if rec.is_warmup else 0,
            rec.cpu_energy_joules,
            rec.gpu_energy_joules,
            rec.dram_energy_joules,
            rec.ane_energy_joules,
            rec.soc_energy_joules,
            rec.energy_basis,
            rec.tokens_per_joule,
            rec.energy_per_output_token_joules,
            rec.throughput_per_watt,
            rec.prefill_energy_joules,
            rec.decode_energy_joules,
            rec.mean_itl_ms,
            rec.median_itl_ms,
            rec.p90_itl_ms,
            rec.p95_itl_ms,
            rec.p99_itl_ms,
            rec.std_itl_ms,
            1 if rec.is_streaming else 0,
            rec.token_counting_version,
            rec.mining_session_id,
            json.dumps(rec.metadata),
        )
        with self._lock:
            if self._closed:
                raise RuntimeError("TelemetryStore is closed")
            self._telemetry_batch.append(row)
            self._maybe_flush_unlocked()

    def record_mining_stats(self, stats: Any) -> None:
        """Persist one mining stats snapshot.

        ``stats`` is duck-typed to keep telemetry usable without importing the
        optional mining package at module import time.
        """
        row = (
            time.time(),
            stats.provider_id,
            stats.shares_submitted,
            stats.shares_accepted,
            stats.blocks_found,
            stats.hashrate,
            stats.uptime_seconds,
            stats.last_share_at,
            stats.last_error,
            stats.payout_target,
            stats.fees_owed,
        )
        with self._lock:
            if self._closed:
                raise RuntimeError("TelemetryStore is closed")
            self._mining_batch.append(row)
            self._maybe_flush_unlocked()

    def flush(self) -> None:
        """Write all pending records to the database."""
        with self._lock:
            self._flush_unlocked()

    def _flush_unlocked(self) -> None:
        if self._telemetry_batch:
            self._conn.executemany(_INSERT, self._telemetry_batch)
            self._telemetry_batch.clear()
        if self._mining_batch:
            self._conn.executemany(_INSERT_MINING, self._mining_batch)
            self._mining_batch.clear()
        self._conn.commit()
        self._last_flush_time = time.monotonic()

    def _maybe_flush_unlocked(self) -> None:
        """Flush when the batch is full or has been pending too long."""
        if not self._telemetry_batch and not self._mining_batch:
            return
        batch_full = (
            len(self._telemetry_batch) >= self._batch_size
            or len(self._mining_batch) >= self._batch_size
        )
        stale = (
            self._flush_interval_seconds > 0
            and time.monotonic() - self._last_flush_time >= self._flush_interval_seconds
        )
        if batch_full or stale:
            self._flush_unlocked()

    def list_recent(self, limit: int = 50) -> list[dict[str, Any]]:
        """Return recent telemetry rows as dictionaries."""
        with self._lock:
            self._flush_unlocked()
            return self._select_dicts_unlocked(
                "SELECT * FROM telemetry ORDER BY timestamp DESC LIMIT ?",
                (limit,),
            )

    def list_recent_mining_stats(self, limit: int = 50) -> list[dict[str, Any]]:
        """Return recent mining stats snapshots as dictionaries."""
        with self._lock:
            self._flush_unlocked()
            return self._select_dicts_unlocked(
                "SELECT * FROM mining_stats ORDER BY recorded_at DESC LIMIT ?",
                (limit,),
            )

    def subscribe_to_bus(self, bus: EventBus) -> None:
        """Subscribe to ``TELEMETRY_RECORD`` events on *bus*."""
        with self._lock:
            if self._closed:
                raise RuntimeError("TelemetryStore is closed")
            if self._event_bus is bus:
                return
            if self._event_bus is not None:
                self._event_bus.unsubscribe(EventType.TELEMETRY_RECORD, self._on_event)
            bus.subscribe(EventType.TELEMETRY_RECORD, self._on_event)
            self._event_bus = bus

    def _on_event(self, event: Event) -> None:
        rec = event.data.get("record")
        if isinstance(rec, TelemetryRecord):
            try:
                self.record(rec)
            except Exception as exc:
                logger.debug("Failed to record telemetry event: %s", exc)

    def close(self) -> None:
        """Flush pending records and close the underlying SQLite connection."""
        # Set the stop event BEFORE taking the lock: a flusher iteration
        # already waiting on the lock re-checks the event after acquiring it
        # and exits instead of touching the closed connection.
        self._stop_flusher.set()
        with self._lock:
            if self._closed:
                return
            if self._event_bus is not None:
                self._event_bus.unsubscribe(
                    EventType.TELEMETRY_RECORD,
                    self._on_event,
                )
                self._event_bus = None
            self._flush_unlocked()
            self._conn.close()
            self._closed = True
        if self._flusher is not None:
            self._flusher.join(timeout=1.0)
            self._flusher = None

    # -- helpers for querying (used by tests) --------------------------------

    def _fetchall(self, sql: str = "SELECT * FROM telemetry") -> list:
        with self._lock:
            self._flush_unlocked()
            return self._conn.execute(sql).fetchall()

    def _select_dicts(self, sql: str, params: tuple[Any, ...]) -> list[dict[str, Any]]:
        with self._lock:
            self._flush_unlocked()
            return self._select_dicts_unlocked(sql, params)

    def _select_dicts_unlocked(
        self, sql: str, params: tuple[Any, ...]
    ) -> list[dict[str, Any]]:
        cur = self._conn.execute(sql, params)
        columns = [desc[0] for desc in cur.description]
        return [dict(zip(columns, row)) for row in cur.fetchall()]


__all__ = ["TelemetryStore"]