Skip to content

Local UI to run and train LLMs and diffusion models. Supports GGUF, MLX, Qwen3.8, DeepSeek-V4.1, MiniMax-H3, Gemma 4, FLUX and more.

28.5 KB
Raw Blame
# SPDX-License-Identifier: AGPL-3.0-only
# Copyright 2026-present the Unsloth AI Inc. team. All rights reserved. See /studio/LICENSE.AGPL-3.0

"""Read-only usage stats from studio.db; chat and training are install-wide, API receipts per subject"""

import csv
import io
import json
import threading
import time
from datetime import date, datetime, timedelta, timezone
from zoneinfo import ZoneInfo, ZoneInfoNotFoundError
from typing import Any, Callable, Optional

from loggers import get_logger

from storage.api_usage_db import canonical_api_subject
from storage.studio_db import count_chat_message_attachments, get_connection
from utils.account_context import current_account_id
from utils.paths import studio_db_path

logger = get_logger(__name__)

# Gap that ends a session, so a thread reopened a week later is not a week-long chat.
SESSION_GAP_SECONDS = 30 * 60
# Cap on the daily activity series handed to the UI (the heatmap draws a year).
MAX_DAILY_DAYS = 366
# Widest real UTC offset is 14h; anything beyond that is a bad client value.
MAX_TZ_OFFSET_MINUTES = 14 * 60
# Top-N lists returned to the client.
TOP_MODELS = 8
RECENT_RUNS = 5
# TTL even with an unchanged fingerprint, so a mid-stream chat still refreshes.
CACHE_TTL_SECONDS = 20.0

USAGE_CSV_COLUMNS = (
    "time",
    "date",
    "source",
    "model",
    "prompt_tokens",
    "completion_tokens",
    "total_tokens",
    "cached_tokens",
)

# (created_at_ms, source, model_id, prompt, completion, total, cached) for each counted response.
UsageSink = Callable[[int, str, str, int, int, int, int], None]

_cache_lock = threading.Lock()
_cache: dict[str, dict[str, Any]] = {}


def _as_float(value: Any) -> Optional[float]:
    """float() overflows past ~1e308 on huge JSON ints; one such counter must not 500 the panel."""
    if isinstance(value, bool) or value is None:
        return None
    if isinstance(value, (int, float)):
        try:
            number = float(value)
        except (OverflowError, ValueError):
            return None
        return number if number == number and number not in (float("inf"), float("-inf")) else None
    return None


def _as_int(value: Any) -> int:
    number = _as_float(value)
    if number is None or number < 0:
        return 0
    return int(number)


def _iso(day: date) -> str:
    return day.isoformat()


def _clean_str(value: Any) -> str:
    return value.strip() if isinstance(value, str) else ""


def _resolve_zone(tz_name: str, tz_offset_minutes: int):
    """Prefers the IANA name: a fixed offset is wrong half the year and can shift dates past midnight."""
    if tz_name:
        try:
            return ZoneInfo(tz_name)
        except (ValueError, ZoneInfoNotFoundError, OSError):
            logger.debug("unknown timezone %r, falling back to fixed offset", tz_name)
    return timezone(timedelta(minutes = -tz_offset_minutes))


def _local_stamp(created_at_ms: int, zone) -> Optional[datetime]:
    """Wall-clock time in the caller's timezone, not the server's; a client-supplied created_at
    outside datetime's range is dropped, not raised"""
    if created_at_ms <= 0:
        return None
    try:
        return datetime.fromtimestamp(created_at_ms / 1000, tz = zone).replace(tzinfo = None)
    except (ValueError, OverflowError, OSError):
        return None


def _streaks(days: set[date], today: date) -> dict[str, Any]:
    """Current streak survives an unused today; rows dated after today are dropped so a skewed client clock cannot pad the longest streak."""
    days = {day for day in days if day <= today}
    if not days:
        return {"current": 0, "longest": 0, "lastActiveDay": None}

    ordered = sorted(days)
    longest = 1
    running = 1
    for previous, current in zip(ordered, ordered[1:]):
        running = running + 1 if current - previous == timedelta(days = 1) else 1
        longest = max(longest, running)

    last = ordered[-1]
    current_streak = 0
    if today - last <= timedelta(days = 1):
        current_streak = 1
        cursor = last
        while cursor - timedelta(days = 1) in days:
            cursor -= timedelta(days = 1)
            current_streak += 1

    return {"current": current_streak, "longest": longest, "lastActiveDay": _iso(last)}


def _model_label(model_id: str) -> str:
    """Last path segment of a repo id, e.g. ``unsloth/gpt-oss-20b`` -> ``gpt-oss-20b``."""
    cleaned = model_id.strip().replace("\\", "/")
    tail = cleaned.rstrip("/").split("/")[-1]
    return tail or cleaned


class _MessageFold:
    """Accumulators for the single streaming pass over chat messages."""

    def __init__(self) -> None:
        self.threads: set[str] = set()
        self.messages = 0
        self.user_messages = 0
        self.assistant_messages = 0
        self.prompt_tokens = 0
        self.completion_tokens = 0
        self.total_tokens = 0
        self.cached_tokens = 0
        self.tool_calls = 0
        self.attachments = 0
        self.session_seconds = 0.0
        self.longest_chat: dict[str, Any] = {
            "threadId": None,
            "title": None,
            "seconds": 0.0,
            "messages": 0,
        }
        self.by_day: dict[date, dict[str, Any]] = {}
        self.models: dict[str, dict[str, Any]] = {}
        self.speed_samples: list[float] = []
        self.best_speed = 0.0
        self.best_speed_model: Optional[str] = None
        self.response_ms: list[float] = []
        self.first_token_ms: list[float] = []

    def note_model(self, model_id: str, tokens: int) -> None:
        entry = self.models.setdefault(
            model_id, {"id": model_id, "label": _model_label(model_id), "messages": 0, "tokens": 0}
        )
        entry["messages"] += 1
        entry["tokens"] += tokens

    def note_day(self, day: date, tokens: int, thread_id: str) -> None:
        bucket = self.by_day.setdefault(day, {"tokens": 0, "messages": 0, "threads": set()})
        bucket["tokens"] += tokens
        bucket["messages"] += 1
        bucket["threads"].add(thread_id)


class _ApiUsageFold:
    """Scalar-only API receipts folded separately from chat-only metrics."""

    def __init__(self) -> None:
        self.prompt_tokens = 0
        self.completion_tokens = 0
        self.total_tokens = 0
        self.by_day: dict[date, int] = {}
        self.requests_by_day: dict[date, int] = {}
        self.models: dict[str, dict[str, Any]] = {}
        self.requests = 0


def _fold_api_usage(
    conn,
    zone,
    subject: str,
    on_usage: Optional[UsageSink] = None,
) -> _ApiUsageFold:
    fold = _ApiUsageFold()
    rows = conn.execute(
        """
        SELECT model, prompt_tokens, completion_tokens, total_tokens, created_at
        FROM api_usage_events
        WHERE subject = ?
        ORDER BY created_at
        """,
        (subject,),
    )
    for row in rows:
        prompt_tokens = _as_int(row["prompt_tokens"])
        completion_tokens = _as_int(row["completion_tokens"])
        total_tokens = _as_int(row["total_tokens"])
        fold.prompt_tokens += prompt_tokens
        fold.completion_tokens += completion_tokens
        # Preserve the provider's authoritative total even when it differs from the input/output sum.
        fold.total_tokens += total_tokens
        fold.requests += 1

        created_at = _as_int(row["created_at"])
        stamp = _local_stamp(created_at, zone)
        if stamp is not None:
            day = stamp.date()
            fold.by_day[day] = fold.by_day.get(day, 0) + total_tokens
            fold.requests_by_day[day] = fold.requests_by_day.get(day, 0) + 1

        model_id = _clean_str(row["model"])
        if on_usage is not None:
            on_usage(created_at, "api", model_id, prompt_tokens, completion_tokens, total_tokens, 0)
        if model_id:
            model = fold.models.setdefault(
                model_id,
                {"id": model_id, "label": _model_label(model_id), "messages": 0, "tokens": 0},
            )
            # One terminal API request represents one model response in the combined leaderboard.
            model["messages"] += 1
            model["tokens"] += total_tokens
    return fold


def _merge_api_activity(chat: _MessageFold, api: _ApiUsageFold) -> None:
    """Merge only combined activity surfaces, leaving chat counters intact."""
    for day, tokens in api.by_day.items():
        bucket = chat.by_day.setdefault(day, {"tokens": 0, "messages": 0, "threads": set()})
        bucket["tokens"] += tokens
        bucket["apiRequests"] = bucket.get("apiRequests", 0) + api.requests_by_day.get(day, 0)
    for model_id, api_model in api.models.items():
        model = chat.models.setdefault(
            model_id,
            {"id": model_id, "label": api_model["label"], "messages": 0, "tokens": 0},
        )
        model["messages"] += api_model["messages"]
        model["tokens"] += api_model["tokens"]


def _fork_keepers(conn) -> dict[tuple[str, int, str], str]:
    """Elects one clone per message, since a per-fork winner would drop rows only a losing fork has."""
    rows = conn.execute(
        """
        SELECT m.thread_id, m.created_at, m.role,
               t.forked_from_thread_id AS source_id
        FROM chat_messages m
        JOIN chat_threads t ON t.id = m.thread_id
        WHERE t.forked_from_thread_id IS NOT NULL
          AND m.created_at < t.created_at
        """
    )

    best: dict[tuple[str, int, str], str] = {}
    for row in rows:
        key = (row["source_id"], _as_int(row["created_at"]), row["role"])
        thread_id = row["thread_id"]
        current = best.get(key)
        # Any stable winner works; lowest id keeps the choice reproducible.
        if current is None or thread_id < current:
            best[key] = thread_id
    return best


def _surviving_original_keys(conn) -> set[tuple[str, int, str]]:
    """Keys (thread_id, created_at, role) for surviving originals, since forked clones get fresh ids."""
    rows = conn.execute(
        """
        SELECT m.thread_id, m.created_at, m.role
        FROM chat_messages m
        WHERE m.thread_id IN (
            SELECT DISTINCT forked_from_thread_id FROM chat_threads
            WHERE forked_from_thread_id IS NOT NULL
        )
        """
    )
    return {(row["thread_id"], _as_int(row["created_at"]), row["role"]) for row in rows}


def _fold_messages(
    conn,
    zone,
    on_usage: Optional[UsageSink] = None,
) -> _MessageFold:
    fold = _MessageFold()
    keepers = _fork_keepers(conn)
    surviving = _surviving_original_keys(conn)
    rows = conn.execute(
        """
        SELECT m.thread_id, m.role, m.content_json, m.metadata_json,
               m.attachments_json, m.created_at,
               t.title, t.model_id, t.model_type, t.pair_id,
               t.created_at AS thread_created_at, t.forked_from_thread_id
        FROM chat_messages m
        LEFT JOIN chat_threads t ON t.id = m.thread_id
        ORDER BY m.thread_id, m.created_at
        """
    )

    current_thread: Optional[str] = None
    thread_title: Optional[str] = None
    thread_seconds = 0.0
    thread_messages = 0
    previous_created: Optional[int] = None

    def close_thread() -> None:
        if current_thread is None:
            return
        fold.session_seconds += thread_seconds
        if thread_seconds > fold.longest_chat["seconds"]:
            fold.longest_chat = {
                "threadId": current_thread,
                "title": thread_title,
                "seconds": thread_seconds,
                "messages": thread_messages,
            }

    for row in rows:
        thread_id = row["thread_id"]
        created_at = _as_int(row["created_at"])
        if thread_id != current_thread:
            close_thread()
            current_thread = thread_id
            thread_title = row["title"]
            thread_seconds = 0.0
            thread_messages = 0
            previous_created = None

        # Compare-mode panes share a pair_id and show as one conversation.
        conversation_id = row["pair_id"] or thread_id

        # A fork is its own visible conversation, so it counts toward the chat total as soon as it exists.
        fold.threads.add(conversation_id)

        # Forks clone ancestry with timestamps; skip clones while the original row still counts.
        source_id = row["forked_from_thread_id"]
        if source_id and created_at < _as_int(row["thread_created_at"]):
            original = (source_id, created_at, row["role"])
            if original in surviving or keepers.get(original) != thread_id:
                continue

        fold.messages += 1
        thread_messages += 1

        if previous_created is not None:
            gap = (created_at - previous_created) / 1000
            if 0 < gap <= SESSION_GAP_SECONDS:
                thread_seconds += gap
        previous_created = created_at

        stamp = _local_stamp(created_at, zone)
        role = row["role"]
        if role == "user":
            fold.user_messages += 1

        fold.attachments += count_chat_message_attachments(
            row["attachments_json"],
            row["content_json"],
        )

        message_tokens = 0
        metadata: Any = None
        if role == "assistant":
            fold.assistant_messages += 1
            raw_metadata = row["metadata_json"]
            if raw_metadata:
                try:
                    metadata = json.loads(raw_metadata)
                except (json.JSONDecodeError, TypeError):
                    metadata = None

        if isinstance(metadata, dict):
            usage = metadata.get("contextUsage")
            timing = metadata.get("timing")
            usage = usage if isinstance(usage, dict) else {}
            timing = timing if isinstance(timing, dict) else {}

            # llama.cpp reports serverTimings when no usage chunk is sent.
            server = metadata.get("serverTimings")
            server = server if isinstance(server, dict) else {}

            # prompt_n is only what llama.cpp evaluated; the usage chunk's promptTokens includes the cache hits.
            prompt_tokens = _as_int(usage.get("promptTokens")) or (
                _as_int(server.get("prompt_n")) + _as_int(server.get("cache_n"))
            )
            completion_tokens = _as_int(usage.get("completionTokens")) or _as_int(
                server.get("predicted_n")
            )
            total_tokens = _as_int(usage.get("totalTokens"))
            if completion_tokens == 0:
                # Local engines occasionally omit the usage chunk; the adapter's
                completion_tokens = _as_int(timing.get("tokenCount"))
            if total_tokens == 0:
                total_tokens = prompt_tokens + completion_tokens

            cached_tokens = _as_int(usage.get("cachedTokens")) or _as_int(server.get("cache_n"))
            fold.prompt_tokens += prompt_tokens
            fold.completion_tokens += completion_tokens
            fold.total_tokens += total_tokens
            fold.cached_tokens += cached_tokens
            fold.tool_calls += _as_int(timing.get("toolCallCount"))
            message_tokens = total_tokens

            # responseDetails names the model that actually answered (provider routing/aliases).
            details = metadata.get("responseDetails")
            details = details if isinstance(details, dict) else {}
            model_id = _clean_str(details.get("responseModelId")) or _clean_str(
                usage.get("modelId")
            )
            if model_id:
                fold.note_model(model_id, message_tokens)
            if on_usage is not None:
                on_usage(
                    created_at,
                    "chat",
                    model_id,
                    prompt_tokens,
                    completion_tokens,
                    total_tokens,
                    cached_tokens,
                )

            speed = _as_float(timing.get("tokensPerSecond"))
            # llama.cpp reports absurd rates on no-op turns; ignore those.
            if speed is not None and 0 < speed < 100_000:
                fold.speed_samples.append(speed)
                if speed > fold.best_speed:
                    fold.best_speed = speed
                    fold.best_speed_model = _model_label(model_id) if model_id else None

            stream_ms = _as_float(timing.get("totalStreamTime"))
            if stream_ms is not None and stream_ms > 0:
                fold.response_ms.append(stream_ms)
            # firstTokenTime is already an elapsed duration, not a timestamp.
            first_token = _as_float(timing.get("firstTokenTime"))
            if first_token is not None and first_token > 0:
                fold.first_token_ms.append(first_token)

        if stamp is not None:
            fold.note_day(stamp.date(), message_tokens, conversation_id)

    close_thread()
    return fold


def _daily_series(fold: _MessageFold, today: date, days: int) -> list[dict[str, Any]]:
    """Dense day-by-day series so the heatmap can index straight into it."""
    start = today - timedelta(days = days - 1)
    series: list[dict[str, Any]] = []
    for offset in range(days):
        day = start + timedelta(days = offset)
        bucket = fold.by_day.get(day)
        series.append(
            {
                "date": _iso(day),
                "tokens": int(bucket["tokens"]) if bucket else 0,
                "messages": int(bucket["messages"]) if bucket else 0,
                "chats": len(bucket["threads"]) if bucket else 0,
                "apiRequests": int(bucket.get("apiRequests", 0)) if bucket else 0,
            }
        )
    return series


def _superseded(prefix: str = "r.") -> str:
    """resume_blocked alone cannot tell resumed from cancelled, so a continuation must exist; prefix must qualify the outer row, not the EXISTS subquery."""
    return f"""
        {prefix}resume_blocked = 1
        AND EXISTS (
            SELECT 1 FROM training_runs continuation
            WHERE (
                continuation.resumed_from_run_id = {prefix}id
                OR (
                    continuation.resumed_from_run_id IS NULL
                    AND {prefix}output_dir IS NOT NULL
                    AND continuation.output_dir = {prefix}output_dir
                    AND continuation.started_at > {prefix}started_at
                )
            )
            AND continuation.id <> {prefix}id
            AND COALESCE(continuation.final_step, 0)
                >= COALESCE({prefix}final_step, 0)
        )
    """


def _training_stats(conn) -> dict[str, Any]:
    row = conn.execute(
        """
        SELECT COUNT(*) AS runs,
               SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
               SUM(COALESCE(duration_seconds, 0)) AS seconds,
               COUNT(DISTINCT model_name) AS models,
               COUNT(DISTINCT dataset_name) AS datasets,
               MIN(final_loss) AS best_loss
        FROM training_runs
        """
    ).fetchone()

    # Only runs superseded by a resume are dropped: resume claims keep output_dir while cancels clear it, so cancelled runs keep their work.
    steps = conn.execute(
        f"SELECT COALESCE(SUM(r.final_step), 0) FROM training_runs r WHERE NOT ({_superseded()})"
    ).fetchone()[0]

    # num_tokens is a running total, so take each run's final value rather than summing.
    tokens = conn.execute(
        f"""
        SELECT COALESCE(SUM(run_tokens), 0) FROM (
            SELECT MAX(m.num_tokens) AS run_tokens
            FROM training_metrics m
            JOIN training_runs r ON r.id = m.run_id
            WHERE NOT ({_superseded("r.")})
            GROUP BY m.run_id
        )
        """
    ).fetchone()[0]

    recent = conn.execute(
        """
        SELECT id, display_name, model_name, dataset_name,
               status, final_loss, final_step, duration_seconds, started_at
        FROM training_runs
        ORDER BY started_at DESC
        LIMIT ?
        """,
        (RECENT_RUNS,),
    ).fetchall()

    return {
        "runs": _as_int(row["runs"]),
        "completed": _as_int(row["completed"]),
        "steps": _as_int(steps),
        "tokens": _as_int(tokens),
        "seconds": _as_float(row["seconds"]) or 0.0,
        "models": _as_int(row["models"]),
        "datasets": _as_int(row["datasets"]),
        "bestLoss": _as_float(row["best_loss"]),
        "recent": [
            {
                "id": item["id"],
                # Renamed runs keep the user's name; else use the short model label, not the full repo id.
                "name": _clean_str(item["display_name"]) or _model_label(item["model_name"] or ""),
                "modelLabel": _model_label(item["model_name"] or ""),
                "datasetLabel": _model_label(item["dataset_name"] or ""),
                "status": item["status"],
                "finalLoss": _as_float(item["final_loss"]),
                "steps": _as_int(item["final_step"]),
                "seconds": _as_float(item["duration_seconds"]) or 0.0,
                "startedAt": item["started_at"],
            }
            for item in recent
        ],
    }


def _fingerprint(conn, subject: str) -> tuple:
    message_row = conn.execute(
        "SELECT COUNT(*), COALESCE(MAX(created_at), 0) FROM chat_messages"
    ).fetchone()
    run_row = conn.execute(
        "SELECT COUNT(*), COALESCE(MAX(started_at), '') FROM training_runs"
    ).fetchone()
    api_row = conn.execute(
        """
        SELECT COUNT(*), COALESCE(MAX(created_at), 0)
        FROM api_usage_events
        WHERE subject = ?
        """,
        (subject,),
    ).fetchone()
    return (
        message_row[0],
        message_row[1],
        run_row[0],
        run_row[1],
        subject,
        api_row[0],
        api_row[1],
    )


def compute_profile_stats(
    days: int = MAX_DAILY_DAYS,
    tz_offset_minutes: int = 0,
    tz_name: str = "",
    *,
    subject: str = "",
) -> dict[str, Any]:
    """API usage is subject-scoped; an empty subject sees no API receipts, keeping non-route callers
    fail-closed; legacy chat and training history is install-wide"""
    days = max(1, min(int(days), MAX_DAILY_DAYS))
    tz_offset_minutes = max(
        -MAX_TZ_OFFSET_MINUTES, min(int(tz_offset_minutes), MAX_TZ_OFFSET_MINUTES)
    )
    zone = _resolve_zone(tz_name, tz_offset_minutes)
    subject = canonical_api_subject(subject)
    conn = get_connection()
    try:
        # The path is in the fingerprint, so a reused account id cannot read another database.
        fingerprint = (
            str(studio_db_path()),
            _fingerprint(conn, subject),
            days,
            tz_offset_minutes,
            tz_name,
        )
        now = time.monotonic()
        account_id = current_account_id()
        with _cache_lock:
            cached = _cache.get(account_id)
            if (
                cached is not None
                and cached["fingerprint"] == fingerprint
                and cached["expires_at"] > now
            ):
                return cached["payload"]

        started = time.perf_counter()
        fold = _fold_messages(conn, zone)
        api_fold = _fold_api_usage(conn, zone, subject)
        _merge_api_activity(fold, api_fold)
        training = _training_stats(conn)

        # 'Today' must use the same zone as the buckets or streaks drift by a day.
        today = (_local_stamp(int(time.time() * 1000), zone) or datetime.now()).date()
        streak = _streaks(set(fold.by_day.keys()), today)
        daily = _daily_series(fold, today, days)

        # Ignore future days so a skewed client clock cannot name a peak outside the chart.
        past_days = {day: bucket for day, bucket in fold.by_day.items() if day <= today}
        peak_day = max(past_days.items(), key = lambda item: item[1]["tokens"], default = None)
        models = sorted(
            fold.models.values(), key = lambda item: (item["tokens"], item["messages"]), reverse = True
        )[:TOP_MODELS]

        speed_samples = fold.speed_samples
        payload = {
            "generatedAt": int(time.time() * 1000),
            "days": days,
            "totals": {
                "threads": len(fold.threads),
                "messages": fold.messages,
                "userMessages": fold.user_messages,
                "assistantMessages": fold.assistant_messages,
                "promptTokens": fold.prompt_tokens + api_fold.prompt_tokens,
                "completionTokens": fold.completion_tokens + api_fold.completion_tokens,
                "totalTokens": fold.total_tokens + api_fold.total_tokens,
                "chatPromptTokens": fold.prompt_tokens,
                "chatCompletionTokens": fold.completion_tokens,
                "chatTokens": fold.total_tokens,
                "apiPromptTokens": api_fold.prompt_tokens,
                "apiCompletionTokens": api_fold.completion_tokens,
                "apiTokens": api_fold.total_tokens,
                "cachedTokens": fold.cached_tokens,
                "toolCalls": fold.tool_calls,
                "attachments": fold.attachments,
                "activeDays": len(past_days),
                "chatSeconds": round(fold.session_seconds),
            },
            "streak": streak,
            "peakDay": (
                {"date": _iso(peak_day[0]), "tokens": int(peak_day[1]["tokens"])}
                if peak_day and peak_day[1]["tokens"] > 0
                else None
            ),
            "longestChat": (
                {
                    "threadId": fold.longest_chat["threadId"],
                    "title": fold.longest_chat["title"],
                    "seconds": round(fold.longest_chat["seconds"]),
                    "messages": fold.longest_chat["messages"],
                }
                if fold.longest_chat["seconds"] > 0
                else None
            ),
            "daily": daily,
            "models": models,
            "speed": {
                "averageTokensPerSecond": (
                    sum(speed_samples) / len(speed_samples) if speed_samples else None
                ),
                "bestTokensPerSecond": fold.best_speed or None,
                "bestTokensPerSecondModel": fold.best_speed_model,
                "averageResponseMs": (
                    sum(fold.response_ms) / len(fold.response_ms) if fold.response_ms else None
                ),
                "averageFirstTokenMs": (
                    sum(fold.first_token_ms) / len(fold.first_token_ms)
                    if fold.first_token_ms
                    else None
                ),
                "samples": len(speed_samples),
            },
            "training": training,
        }

        logger.debug(
            "profile stats computed in %.1f ms (%d messages, %d API requests)",
            (time.perf_counter() - started) * 1000,
            fold.messages,
            api_fold.requests,
        )

        with _cache_lock:
            _cache[account_id] = {
                "fingerprint": fingerprint,
                "expires_at": time.monotonic() + CACHE_TTL_SECONDS,
                "payload": payload,
            }
        return payload
    finally:
        conn.close()


def invalidate_profile_stats_cache() -> None:
    with _cache_lock:
        _cache.pop(current_account_id(), None)


def _csv_text(value: str) -> str:
    """Model ids are client-supplied; a leading formula character would run as a formula in a spreadsheet."""
    return "'" + value if value[:1] in ("=", "+", "-", "@", "\t", "\r") else value


def export_usage_csv(
    tz_offset_minutes: int = 0,
    tz_name: str = "",
    *,
    subject: str = "",
) -> str:
    """One CSV row per counted chat response or API request, oldest first, in the caller's timezone.
    Counted by the same folds as ``compute_profile_stats`` with the same scoping (chat install-wide,
    API receipts only for ``subject``), so the totals match the Profile tab."""
    tz_offset_minutes = max(
        -MAX_TZ_OFFSET_MINUTES, min(int(tz_offset_minutes), MAX_TZ_OFFSET_MINUTES)
    )
    zone = _resolve_zone(tz_name, tz_offset_minutes)
    subject = canonical_api_subject(subject)
    rows: list[tuple[int, str, str, int, int, int, int]] = []

    def collect(*row) -> None:
        if row[3] or row[4] or row[5]:
            rows.append(row)

    conn = get_connection()
    try:
        _fold_messages(conn, zone, on_usage = collect)
        _fold_api_usage(conn, zone, subject, on_usage = collect)
    finally:
        conn.close()
    rows.sort(key = lambda row: row[0])

    out = io.StringIO()
    writer = csv.writer(out, lineterminator = "\n")
    writer.writerow(USAGE_CSV_COLUMNS)
    for created_at, source, model_id, prompt, completion, total, cached in rows:
        stamp = _local_stamp(created_at, zone)
        writer.writerow(
            [
                stamp.strftime("%Y-%m-%d %H:%M:%S") if stamp else "",
                _iso(stamp.date()) if stamp else "",
                source,
                _csv_text(model_id),
                prompt,
                completion,
                total,
                cached,
            ]
        )
    return out.getvalue()