Python
"""`recordings.db`: where the recorder's turns and steps are saved (technical design §11).
A SQLite file next to `coworker.db`, on the machine that ran the work. It is separate from
`coworker.db` on purpose: recordings are pruned after 30 days while sessions are kept, the
layout can change without migrating the sessions database, and deleting this one file resets
all recordings and nothing else.
These tables are the Python layout only. The contract between engines is the recording JSON
of recording-format.md; `load_recording()` gives it back.
Writing never makes a turn wait. `write_turn` and `write_step` only put the record in a
pending map keyed by its id, so a step written three times before the writer runs is written
once, with its latest state; one background thread, shared by every store in the process,
writes the map in a single transaction
every `FLUSH_MS`. Nothing is dropped, and no queue can grow past the number of records that
changed since the last write.
"""
from __future__ import annotations
import json
import logging
import sqlite3
import threading
import time
import weakref
import zlib
from hashlib import sha256
from pathlib import Path
from typing import Any, Iterable, Iterator, Optional
from .model_names import canonical_model
log = logging.getLogger(__name__)
FILENAME = "recordings.db"
RETENTION_DAYS = 30 # feature spec Q12
FLUSH_MS = 50
SWEEP_EVERY_S = 24 * 60 * 60
SWEEP_BATCH = 2000 # rows per transaction, so the file is never locked for long (P-8)
SWEEP_PAUSE_S = 0.05
_SCHEMA = """
CREATE TABLE IF NOT EXISTS turns (
turn_id TEXT PRIMARY KEY, session_id TEXT NOT NULL, seq INTEGER, kind TEXT, trigger TEXT,
started_at INTEGER, duration_ms INTEGER, status TEXT, incomplete INTEGER DEFAULT 0,
approval_mode TEXT, attended INTEGER, reviewer TEXT, stamp TEXT,
first_message_id TEXT, last_message_id TEXT,
schema_version INTEGER NOT NULL DEFAULT 1);
CREATE INDEX IF NOT EXISTS turns_session ON turns(session_id, seq);
CREATE INDEX IF NOT EXISTS turns_started ON turns(started_at);
CREATE TABLE IF NOT EXISTS steps (
step_id TEXT PRIMARY KEY, turn_id TEXT, session_id TEXT, parent_step_id TEXT,
seq INTEGER, kind TEXT, name TEXT, started_at INTEGER, duration_ms INTEGER, status TEXT,
call_id TEXT, call_index INTEGER, message_id TEXT,
shadow INTEGER DEFAULT 0, after_turn INTEGER DEFAULT 0,
purpose TEXT, model TEXT, usage_reported INTEGER,
input_fresh INTEGER, cache_read INTEGER, cache_write INTEGER, output INTEGER, reasoning INTEGER,
error_type TEXT, served_model TEXT, provider_request_id TEXT, context_window INTEGER,
attrs TEXT, content TEXT, provider_meta BLOB);
CREATE INDEX IF NOT EXISTS steps_turn ON steps(turn_id, seq);
CREATE INDEX IF NOT EXISTS steps_call ON steps(session_id, call_id);
CREATE INDEX IF NOT EXISTS steps_usage ON steps(session_id, kind, purpose, model);
CREATE INDEX IF NOT EXISTS steps_started ON steps(started_at);
CREATE TABLE IF NOT EXISTS exchange_pieces (
sha256 TEXT PRIMARY KEY, data BLOB NOT NULL, size INTEGER NOT NULL, first_seen INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS step_pieces (
step_id TEXT NOT NULL, sha256 TEXT NOT NULL, PRIMARY KEY (step_id, sha256));
CREATE INDEX IF NOT EXISTS step_pieces_sha ON step_pieces(sha256);
CREATE TABLE IF NOT EXISTS export_marks (
destination TEXT NOT NULL, item_id TEXT NOT NULL, turn_id TEXT, state TEXT NOT NULL, at INTEGER NOT NULL,
PRIMARY KEY (destination, item_id));
CREATE INDEX IF NOT EXISTS export_marks_turn ON export_marks(turn_id);
CREATE TABLE IF NOT EXISTS export_counters (
destination TEXT PRIMARY KEY, failed INTEGER NOT NULL DEFAULT 0, dropped INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS turn_usage (
turn_id TEXT NOT NULL, purpose TEXT, model TEXT, first INTEGER NOT NULL DEFAULT 0,
session_id TEXT, session_deleted INTEGER NOT NULL DEFAULT 0, started_at INTEGER, trigger TEXT,
wake_reason TEXT, persona_id TEXT, persona_fp TEXT, machine TEXT, team_id TEXT, team_role TEXT,
task_id TEXT, run_id TEXT, automation_trigger TEXT, status TEXT, duration_ms INTEGER,
waiting_ms INTEGER NOT NULL DEFAULT 0,
input_fresh INTEGER NOT NULL DEFAULT 0, cache_read INTEGER NOT NULL DEFAULT 0,
cache_write INTEGER NOT NULL DEFAULT 0, output INTEGER NOT NULL DEFAULT 0,
reasoning INTEGER NOT NULL DEFAULT 0, cache_read_known_input INTEGER NOT NULL DEFAULT 0,
calls INTEGER NOT NULL DEFAULT 0, unreported INTEGER NOT NULL DEFAULT 0,
failed INTEGER NOT NULL DEFAULT 0, over_limit INTEGER NOT NULL DEFAULT 0,
warn INTEGER, info INTEGER, warn_checks TEXT, approvals INTEGER NOT NULL DEFAULT 0,
auto_approved INTEGER NOT NULL DEFAULT 0, live_checks INTEGER NOT NULL DEFAULT 0,
rating TEXT, signals_version TEXT, updated_at INTEGER);
CREATE UNIQUE INDEX IF NOT EXISTS turn_usage_key ON turn_usage(turn_id, COALESCE(purpose, ''), COALESCE(model, ''));
CREATE INDEX IF NOT EXISTS turn_usage_started ON turn_usage(started_at);
CREATE INDEX IF NOT EXISTS turn_usage_session ON turn_usage(session_id);
CREATE INDEX IF NOT EXISTS turn_usage_persona ON turn_usage(persona_id, started_at);
CREATE TABLE IF NOT EXISTS feedback (
turn_id TEXT PRIMARY KEY, session_id TEXT NOT NULL, rating TEXT NOT NULL, reasons TEXT NOT NULL,
note TEXT, auto_approved TEXT NOT NULL, rated_at INTEGER NOT NULL, changed_at INTEGER);
CREATE INDEX IF NOT EXISTS feedback_session ON feedback(session_id);
CREATE TABLE IF NOT EXISTS persona_versions (
persona_id TEXT NOT NULL, fp TEXT NOT NULL, name TEXT, first_seen INTEGER NOT NULL, files TEXT NOT NULL,
PRIMARY KEY (persona_id, fp));
CREATE TABLE IF NOT EXISTS persona_files (
persona_id TEXT NOT NULL, fp TEXT NOT NULL, path TEXT NOT NULL, data BLOB NOT NULL,
PRIMARY KEY (persona_id, fp, path));
"""
# Phase 3 (TD3-5): the usage ledger holds counts only, so it is kept 90 days, whatever
# "Keep for" says, and through a session's delete (TD3-3).
LEDGER_DAYS = 90
_TURN_COLUMNS = (
"turn_id", "session_id", "seq", "kind", "trigger", "started_at", "duration_ms", "status",
"incomplete", "approval_mode", "attended", "reviewer", "stamp", "first_message_id",
"last_message_id", "schema_version", "max_iterations", "woken_by", "wake", "automation",
)
# Columns added after the first release of the store: an existing recordings.db gets them
# here (CREATE TABLE IF NOT EXISTS does not add columns). `issues` and `issues_seen` hold the
# Issues checks' result for the turn (phase-2 design §5): derived, not part of the format.
_ADDED_COLUMNS = (
("turns", "max_iterations", "INTEGER"),
("turns", "woken_by", "TEXT"),
("turns", "issues", "TEXT"),
("turns", "issues_seen", "INTEGER DEFAULT 0"),
("turns", "wake", "TEXT"), # 1.3
("turns", "automation", "TEXT"), # 1.3
)
_STEP_COLUMNS = (
"step_id", "turn_id", "session_id", "parent_step_id", "seq", "kind", "name", "started_at",
"duration_ms", "status", "call_id", "call_index", "message_id", "shadow", "after_turn",
"purpose", "model", "usage_reported", "input_fresh", "cache_read", "cache_write", "output",
"reasoning", "error_type", "served_model", "provider_request_id", "context_window", "attrs",
"content", "provider_meta",
)
_USAGE_PARTS = ("input_fresh", "cache_read", "cache_write", "output", "reasoning")
def _upsert(table: str, columns: tuple[str, ...], key: str, sticky: tuple[str, ...] = ()) -> str:
sets = ", ".join(
f"{c} = MAX({table}.{c}, excluded.{c})" if c in sticky else f"{c} = excluded.{c}"
for c in columns if c != key
)
return (
f"INSERT INTO {table} ({', '.join(columns)}) VALUES ({', '.join('?' * len(columns))}) "
f"ON CONFLICT({key}) DO UPDATE SET {sets}"
)
# `incomplete` only ever goes from 0 to 1.
_UPSERT_TURN = _upsert("turns", _TURN_COLUMNS, "turn_id", sticky=("incomplete",))
_UPSERT_STEP = _upsert("steps", _STEP_COLUMNS, "step_id")
def _js(value: Any) -> Optional[str]:
return None if value is None else json.dumps(value, ensure_ascii=False, sort_keys=True)
def _bool(value: Any) -> Optional[int]:
return None if value is None else int(bool(value))
def _turn_row(t: dict[str, Any]) -> tuple:
return (
t["turn_id"], t["session_id"], t.get("seq"), t.get("kind"), t.get("trigger"),
t.get("started_at"), t.get("duration_ms"), t.get("status"), int(bool(t.get("incomplete"))),
t.get("approval_mode"), _bool(t.get("attended")), _js(t.get("reviewer")),
_js(t.get("stamp") or {}), t.get("first_message_id"), t.get("last_message_id"),
t.get("schema_version", 1), t.get("max_iterations"), _js(t.get("woken_by")),
_js(t.get("wake")), _js(t.get("automation")),
)
def _piece_refs(value: Any) -> Iterator[str]:
"""Every piece an exchange points at: manifest `$blob`s, a whole-body `blob`, `body_blob`."""
if isinstance(value, dict):
for k, v in value.items():
if k in ("$blob", "blob", "body_blob") and isinstance(v, str):
yield v
else:
yield from _piece_refs(v)
elif isinstance(value, list):
for v in value:
yield from _piece_refs(v)
def _step_row(s: dict[str, Any]) -> tuple:
attrs = dict(s.get("attrs") or {})
# provider_meta has its own compressed column, so the light step list and the usage sums
# never read it (§5.2.1, §11).
meta = attrs.pop("provider_meta", None)
usage = attrs.get("usage") if isinstance(attrs.get("usage"), dict) else None
if s.get("kind") == "model":
# A call the provider gave no counts for is `usage: {"reported": false}` (§5.2).
reported: Optional[int] = int(bool(usage) and usage.get("reported", True) is not False)
else:
# The column is the model call's own usage. An auto-approver check's `usage_reported`
# (whether its nested call reported) stays in attrs: in the column it read as a call
# of 0 tokens to any sum over it (Phase 3 V3-06).
reported = None
facts = attrs.get("provider_facts") or {}
error = attrs.get("error") if isinstance(attrs.get("error"), dict) else {}
return (
s["step_id"], s.get("turn_id"), s.get("session_id"), s.get("parent_step_id"), s.get("seq"),
s.get("kind"), s.get("name"), s.get("started_at"), s.get("duration_ms"), s.get("status"),
s.get("call_id"), s.get("call_index"), s.get("message_id"), int(bool(s.get("shadow"))),
int(bool(s.get("after_turn"))), attrs.get("purpose"), attrs.get("model"), reported,
*((usage or {}).get(p) for p in _USAGE_PARTS),
error.get("type"), facts.get("served_model"), facts.get("provider_request_id"),
attrs.get("context_window"), _js(attrs), _js(s.get("content") or {}),
None if meta is None else zlib.compress(_js(meta).encode("utf-8")),
)
class RecordingStore:
"""`recordings.db`. One connection, guarded by a lock; one writer thread."""
def __init__(
self,
path: str | Path,
*,
retention_days: Optional[int] = RETENTION_DAYS,
background: bool = True,
) -> None:
self.path = Path(path).expanduser()
self.path.parent.mkdir(parents=True, exist_ok=True)
self.retention_days = retention_days
self._lock = threading.RLock()
self._conn = sqlite3.connect(self.path, check_same_thread=False)
self._conn.row_factory = sqlite3.Row
self._conn.execute("PRAGMA journal_mode=WAL")
self._conn.executescript(_SCHEMA)
for table, column, kind in _ADDED_COLUMNS:
try:
self._conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {kind}")
except sqlite3.OperationalError:
pass # already there
self._conn.commit()
# Records waiting to be written, by id; the latest state of each replaces the last.
self._pending_turns: dict[str, dict[str, Any]] = {}
self._pending_issues: dict[str, tuple[str, int]] = {} # turn_id -> (json, seen)
self._pending_steps: dict[str, dict[str, Any]] = {}
self._pending_refresh: set[str] = set() # turns whose ledger rows to work out again
self._pending_lock = threading.Lock()
# How many writes touched each session since start (P3B-5): a team view keys its
# cache on its members' numbers, so it is worked out again only when one changed.
self._generation: dict[str, int] = {}
self._owner = False # this process called close_open_turns: it owns the data folder
self._closed = False
self._last_sweep = 0.0
self._background = background
# OB-6: the destination being exported to and the time it was chosen, so a turn the
# retention sweep deletes before it was sent is counted as dropped (design §10.5).
self.export_destination: Optional[tuple[str, int]] = None
if background:
_writer.add(self)
@classmethod
def beside(cls, coworker_db_dir: str | Path, **kw: Any) -> "RecordingStore":
"""The store for a data folder: `recordings.db` next to its `coworker.db`."""
return cls(Path(coworker_db_dir) / FILENAME, **kw)
# -- writing (called by the recorder; never blocks on the disk) --------------------------
def write_turn(self, record: dict[str, Any]) -> None:
with self._pending_lock:
self._pending_turns[record["turn_id"]] = record
if self._background:
_writer.wake()
def write_step(self, record: dict[str, Any]) -> None:
with self._pending_lock:
self._pending_steps[record["step_id"]] = record
if self._background:
_writer.wake()
def flush(self) -> None:
"""Write everything pending now, on the calling thread."""
# The connection lock is taken before the pending map is swapped, so a session
# deleted meanwhile cannot be written back by a flush that started before it.
with self._lock:
with self._pending_lock:
turns, self._pending_turns = self._pending_turns, {}
steps, self._pending_steps = self._pending_steps, {}
issues, self._pending_issues = self._pending_issues, {}
refresh, self._pending_refresh = self._pending_refresh, set()
refresh |= set(issues)
if not turns and not steps and not issues and not refresh:
return
try:
with self._conn:
self._conn.executemany(_UPSERT_TURN, [_turn_row(t) for t in turns.values()])
self._conn.executemany(_UPSERT_STEP, [_step_row(s) for s in steps.values()])
self._conn.executemany(
"UPDATE turns SET issues = ?, issues_seen = ? WHERE turn_id = ?",
[(js, seen, tid) for tid, (js, seen) in issues.items()],
)
for s in steps.values():
refs = set(_piece_refs((s.get("content") or {}).get("exchange")))
if refs:
self._conn.executemany(
"INSERT OR IGNORE INTO step_pieces (step_id, sha256) VALUES (?, ?)",
[(s["step_id"], r) for r in refs],
)
except Exception:
log.exception("recordings: write failed (%d turns, %d steps)", len(turns), len(steps))
self._touch_sessions({t.get("session_id") for t in turns.values()} | {x.get("session_id") for x in steps.values()})
# Phase 3: the ledger rows of turns that ended or changed, from what was just
# written (the turn, its steps and its Issues result). After the recording's own
# transaction, one turn at a time: a ledger error loses that turn's rows only,
# never the recorded steps (P3B-6).
self._refresh_usage(refresh)
def _tick(self) -> None:
"""One turn of the shared writer: write what is pending, and sweep once a day."""
if self._closed:
return
self.flush()
if time.time() - self._last_sweep >= SWEEP_EVERY_S:
self._last_sweep = time.time()
try:
self.sweep()
self.sweep_ledger()
except Exception:
log.exception("recordings: retention sweep failed")
def close(self) -> None:
_writer.remove(self)
with self._lock:
if self._closed:
return
self.flush()
self._closed = True
self._conn.close()
if self._owner:
# A clean stop: a turn found open at the next start was cut by the app being
# stopped, not by a crash (recording_gap cause, OB-14).
self._marker("running").unlink(missing_ok=True)
try:
self._marker("stopped").write_text(str(int(time.time() * 1000)), encoding="utf-8")
except OSError:
pass
def _marker(self, name: str) -> Path:
return self.path.with_name(self.path.name + f".{name}")
def _last_stop(self) -> str:
"""How the previous run of the app ended: `stopped` (it closed this store), `restart`
(it was running and never closed it: a crash or a kill) or `unknown` (no word)."""
if self._marker("stopped").exists():
return "stopped"
if self._marker("running").exists():
return "restart"
return "unknown"
# -- exchange pieces (TD-17) --------------------------------------------------------------
def put_piece(self, data: bytes) -> str:
"""Store one exchange piece once, compressed, keyed by its SHA-256. Returns the key."""
key = sha256(data).hexdigest()
with self._lock, self._conn:
self._conn.execute(
"INSERT OR IGNORE INTO exchange_pieces (sha256, data, size, first_seen) "
"VALUES (?, ?, ?, ?)",
(key, zlib.compress(data), len(data), int(time.time() * 1000)),
)
return key
def get_piece(self, key: str) -> Optional[bytes]:
with self._lock:
row = self._conn.execute(
"SELECT data FROM exchange_pieces WHERE sha256 = ?", (key,)
).fetchone()
return None if row is None else zlib.decompress(row["data"])
# -- found open at start (TD-12) ------------------------------------------------------------
def close_open_turns(self) -> int:
"""Close the turns and steps a previous run of the app left open (it crashed, was
killed or restarted mid-turn): status `unknown` with no duration, the turn's steps
numbered in canonical order, and a `recording_gap` notice at the turn's last known
moment. Only the process that owns the data folder may call this, at start: a
running turn of another process would look the same. Returns the turns closed."""
from .ids import new_ulid
from .recorder import KIND_ORDER, fill_unended
with self._lock:
self.flush()
cause, until = self._last_stop(), int(time.time() * 1000)
self._owner = True
self._marker("stopped").unlink(missing_ok=True)
try:
self._marker("running").write_text(str(until), encoding="utf-8")
except OSError:
pass
open_turns = self._conn.execute(
"SELECT turn_id, session_id, started_at FROM turns WHERE status = 'running' OR status IS NULL"
).fetchall()
if not open_turns:
return 0
with self._conn:
for t in open_turns:
tid = t["turn_id"]
self._conn.execute(
"UPDATE turns SET status = 'unknown', duration_ms = NULL WHERE turn_id = ?", (tid,))
opened = self._conn.execute(
"SELECT step_id, kind, attrs FROM steps "
"WHERE turn_id = ? AND (status = 'running' OR status IS NULL)", (tid,)).fetchall()
self._conn.executemany(
"UPDATE steps SET status = 'unknown', duration_ms = NULL, attrs = ? WHERE step_id = ?",
[(json.dumps(fill_unended(o["kind"], json.loads(o["attrs"] or "{}"))), o["step_id"])
for o in opened])
steps = self._conn.execute(
"SELECT step_id, kind, name, started_at, duration_ms, call_index, call_id, shadow "
"FROM steps WHERE turn_id = ?", (tid,)).fetchall()
# The last moment known: a step's end, or the start of one still open.
last = max([t["started_at"] or 0] + [s["started_at"] + (s["duration_ms"] or 0) for s in steps
if s["started_at"] is not None])
gap = {"step_id": new_ulid(), "kind": "notice", "name": "recording_gap", "started_at": last,
"duration_ms": 0, "call_index": None, "call_id": None, "shadow": 0}
# 1.2: why the turn was found open, and the start that found it (OB-14).
self._conn.execute(
"INSERT INTO steps (step_id, turn_id, session_id, kind, name, started_at, duration_ms, "
"status, shadow, after_turn, attrs, content) VALUES (?, ?, ?, 'notice', 'recording_gap', ?, 0, "
"'ok', 0, 0, ?, '{}')",
(gap["step_id"], tid, t["session_id"], last, json.dumps({"cause": cause, "until": until})))
ordered = sorted([dict(s) for s in steps] + [gap], key=lambda s: (
s["started_at"] or 0, 0 if s["duration_ms"] == 0 else 1, KIND_ORDER.get(s["kind"], 9),
s["call_index"] or 0, s["name"] or "", s["call_id"] or "", bool(s["shadow"])))
self._conn.executemany("UPDATE steps SET seq = ? WHERE step_id = ?",
[(i, s["step_id"]) for i, s in enumerate(ordered, 1)])
log.info("recordings: closed %d turn(s) left open by an earlier run", len(open_turns))
return len(open_turns)
# -- deleting -----------------------------------------------------------------------------
def _touch_sessions(self, session_ids: Iterable[Optional[str]]) -> None:
with self._pending_lock:
for sid in session_ids:
if sid:
self._generation[sid] = self._generation.get(sid, 0) + 1
def generation(self, session_ids: Iterable[str]) -> tuple[int, ...]:
"""How many writes have touched each of these sessions since start (P3B-5). Writes
still pending are written first, so an unchanged answer means nothing new."""
self.flush()
with self._pending_lock:
return tuple(self._generation.get(sid, 0) for sid in session_ids)
def delete_session(self, session_id: str) -> None:
"""The session-delete cascade (§11): its turns, steps and the pieces only they used."""
self.flush() # the ledger rows of its turns are worked out before the steps go
self._touch_sessions({session_id})
with self._lock, self._conn:
with self._pending_lock:
self._pending_turns = {
k: v for k, v in self._pending_turns.items()
if v.get("session_id") != session_id
}
self._pending_steps = {
k: v for k, v in self._pending_steps.items()
if v.get("session_id") != session_id
}
# Phase 3 (TD3-3): the content-free ledger rows stay, marked deleted; the ratings
# (whose note is the person's own text) go, their count stays on the ledger row.
self._conn.execute("UPDATE turn_usage SET session_deleted = 1 WHERE session_id = ?", (session_id,))
self._conn.execute("DELETE FROM feedback WHERE session_id = ?", (session_id,))
self._conn.execute(
"DELETE FROM step_pieces WHERE step_id IN "
"(SELECT step_id FROM steps WHERE session_id = ?)",
(session_id,),
)
self._conn.execute(
"DELETE FROM export_marks WHERE turn_id IN (SELECT turn_id FROM turns WHERE session_id = ?)",
(session_id,),
)
self._conn.execute("DELETE FROM steps WHERE session_id = ?", (session_id,))
self._conn.execute("DELETE FROM turns WHERE session_id = ?", (session_id,))
self._delete_unused_pieces()
def sweep(self, *, now_ms: Optional[int] = None) -> int:
"""Delete turns older than the retention period, their steps, then unused pieces.
Runs in batches of `SWEEP_BATCH` rows with a short pause between them. Steps with no
known turn (proxy calls) age out by their own start time. Returns the turns deleted.
"""
if self.retention_days is None:
return 0
now = int(time.time() * 1000) if now_ms is None else now_ms
cutoff = now - self.retention_days * 24 * 60 * 60 * 1000
deleted = 0
while True:
with self._lock, self._conn:
ids = [
r["turn_id"]
for r in self._conn.execute(
"SELECT turn_id FROM turns WHERE started_at < ? LIMIT ?",
(cutoff, SWEEP_BATCH),
)
]
if ids:
marks = ", ".join("?" * len(ids))
self._count_unsent_dropped(ids, marks)
self._conn.execute(f"DELETE FROM export_marks WHERE turn_id IN ({marks})", ids)
self._conn.execute(
"DELETE FROM step_pieces WHERE step_id IN "
f"(SELECT step_id FROM steps WHERE turn_id IN ({marks}))",
ids,
)
self._conn.execute(f"DELETE FROM steps WHERE turn_id IN ({marks})", ids)
self._conn.execute(f"DELETE FROM turns WHERE turn_id IN ({marks})", ids)
deleted += len(ids)
if len(ids) < SWEEP_BATCH:
break
time.sleep(SWEEP_PAUSE_S)
while True:
with self._lock, self._conn:
ids = [
r["step_id"]
for r in self._conn.execute(
"SELECT step_id FROM steps WHERE started_at < ? AND "
"(turn_id IS NULL OR turn_id NOT IN (SELECT turn_id FROM turns)) LIMIT ?",
(cutoff, SWEEP_BATCH),
)
]
if ids:
marks = ", ".join("?" * len(ids))
self._conn.execute(f"DELETE FROM step_pieces WHERE step_id IN ({marks})", ids)
self._conn.execute(f"DELETE FROM steps WHERE step_id IN ({marks})", ids)
if len(ids) < SWEEP_BATCH:
break
time.sleep(SWEEP_PAUSE_S)
self._delete_unused_pieces()
return deleted
def _delete_unused_pieces(self) -> None:
while True:
with self._lock, self._conn:
cur = self._conn.execute(
"DELETE FROM exchange_pieces WHERE sha256 IN (SELECT sha256 FROM "
"exchange_pieces WHERE sha256 NOT IN (SELECT sha256 FROM step_pieces) "
"LIMIT ?)",
(SWEEP_BATCH,),
)
if cur.rowcount < SWEEP_BATCH:
break
time.sleep(SWEEP_PAUSE_S)
# -- export marks (OB-6, phase-2 design §10.2, §10.5) ----------------------------------------
# The queue is the store itself: a finished turn's root and steps with no mark for the
# destination are still to send. A mark is `sent`, `dropped` (refused on its own) or
# `skipped` (a notice that came after its turn's span had gone). Marks are per destination,
# so turning export off and on again never re-sends a step.
def _count_unsent_dropped(self, ids: list[str], marks: str) -> None:
"""Inside the sweep's transaction: turns about to be deleted that ended after the
destination was chosen and were never sent."""
dest = self.export_destination
if dest is None:
return
key, since = dest
n = self._conn.execute(
f"SELECT COUNT(*) AS n FROM turns t WHERE t.turn_id IN ({marks}) "
"AND t.status IS NOT NULL AND t.status != 'running' "
"AND t.started_at + COALESCE(t.duration_ms, 0) >= ? AND NOT EXISTS "
"(SELECT 1 FROM export_marks m WHERE m.destination = ? AND m.item_id = t.turn_id)",
(*ids, since, key),
).fetchone()["n"]
if n:
self._bump(key, dropped=n)
def _bump(self, destination: str, *, failed: int = 0, dropped: int = 0) -> None:
self._conn.execute(
"INSERT INTO export_counters (destination, failed, dropped) VALUES (?, ?, ?) "
"ON CONFLICT(destination) DO UPDATE SET failed = failed + excluded.failed, "
"dropped = dropped + excluded.dropped",
(destination, failed, dropped),
)
def count_export(self, destination: str, *, failed: int = 0, dropped: int = 0) -> None:
with self._lock, self._conn:
self._bump(destination, failed=failed, dropped=dropped)
def export_queue(self, destination: str, since_ms: int, limit: int = 50) -> list[str]:
"""Finished turns, ended at or after `since_ms`, with something not yet marked for
this destination: the turn itself, or a step that has ended (an `after_turn` step
is sent alone when it ends). Oldest first."""
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT t.turn_id FROM turns t "
"WHERE t.status IS NOT NULL AND t.status != 'running' "
"AND t.started_at + COALESCE(t.duration_ms, 0) >= ? AND ("
" NOT EXISTS (SELECT 1 FROM export_marks m WHERE m.destination = ? AND m.item_id = t.turn_id)"
" OR EXISTS (SELECT 1 FROM steps s WHERE s.turn_id = t.turn_id"
" AND s.status IS NOT NULL AND s.status != 'running' AND NOT EXISTS"
" (SELECT 1 FROM export_marks m WHERE m.destination = ? AND m.item_id = s.step_id))) "
"ORDER BY t.started_at, t.turn_id LIMIT ?",
(since_ms, destination, destination, limit),
).fetchall()
return [r["turn_id"] for r in rows]
def export_marked(self, destination: str, turn_id: str) -> set[str]:
with self._lock:
rows = self._conn.execute(
"SELECT item_id FROM export_marks WHERE destination = ? AND turn_id = ?",
(destination, turn_id),
).fetchall()
return {r["item_id"] for r in rows}
def mark_export(self, destination: str, items: list[tuple[str, str]], state: str) -> None:
"""`items`: (item id, turn id) pairs. A mark, once written, is kept."""
now = int(time.time() * 1000)
with self._lock, self._conn:
self._conn.executemany(
"INSERT OR IGNORE INTO export_marks (destination, item_id, turn_id, state, at) "
"VALUES (?, ?, ?, ?, ?)",
[(destination, item, turn, state, now) for item, turn in items],
)
def export_counts(self, destination: str, *, since_ms: int = 0) -> dict[str, int]:
"""Steps sent (since `since_ms`), steps dropped, and batches failed for good."""
with self._lock:
sent = self._conn.execute(
"SELECT COUNT(*) AS n FROM export_marks WHERE destination = ? AND state = 'sent' AND at >= ?",
(destination, since_ms),
).fetchone()["n"]
dropped = self._conn.execute(
"SELECT COUNT(*) AS n FROM export_marks WHERE destination = ? AND state = 'dropped'",
(destination,),
).fetchone()["n"]
row = self._conn.execute(
"SELECT failed, dropped FROM export_counters WHERE destination = ?", (destination,)
).fetchone()
last = self._conn.execute(
"SELECT MAX(at) AS at FROM export_marks WHERE destination = ? AND state = 'sent'", (destination,)
).fetchone()["at"]
return {"sent": int(sent), "dropped": int(dropped) + (int(row["dropped"]) if row else 0),
"failed": int(row["failed"]) if row else 0, "last_at": last}
def outside_calls(self, *, since_ms: int = 0) -> dict[str, int]:
"""Model calls made outside any session (purpose `proxy`: through this machine's
model address, for example by a remote machine) since `since_ms`, and their tokens.
No session's Steps or Usage shows them (fix list F-4)."""
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT attrs FROM steps WHERE kind = 'model' AND turn_id IS NULL AND started_at >= ?",
(since_ms,),
).fetchall()
calls = tokens = 0
for r in rows:
a = json.loads(r["attrs"] or "{}")
if a.get("purpose") != "proxy":
continue
calls += 1
u = a.get("usage") or {}
if u.get("reported"):
tokens += sum(int(u.get(k) or 0) for k in ("input_fresh", "cache_read", "cache_write", "output"))
return {"calls": calls, "tokens": tokens}
def load_turn(self, turn_id: str) -> Optional[tuple[dict[str, Any], list[dict[str, Any]]]]:
"""One turn and its steps (by `seq`), content included."""
self.flush()
with self._lock:
t = self._conn.execute("SELECT * FROM turns WHERE turn_id = ?", (turn_id,)).fetchone()
if t is None:
return None
steps = self._conn.execute(
"SELECT * FROM steps WHERE turn_id = ? ORDER BY seq IS NULL, seq, started_at", (turn_id,)
).fetchall()
return _turn_record(t), [_step_record(s) for s in steps]
# -- the Issues result (phase-2 design §5): derived, stored beside the turn ---------------
def write_issues(self, turn_id: str, result: dict[str, Any], *, seen: bool = False) -> None:
"""Store a turn's Issues result. `seen` is false only for a result worked out when the
turn ended: one worked out later, for an old turn, never lights the session dot."""
with self._pending_lock:
self._pending_issues[turn_id] = (json.dumps(result, ensure_ascii=False), int(seen))
if self._background:
_writer.wake()
def read_issues(self, session_id: str) -> dict[str, dict[str, Any]]:
"""The stored results of a session's turns, by turn id, each with `seen`."""
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT turn_id, issues, issues_seen FROM turns WHERE session_id = ? AND issues IS NOT NULL",
(session_id,),
).fetchall()
out = {}
for r in rows:
try:
out[r["turn_id"]] = {**json.loads(r["issues"]), "seen": bool(r["issues_seen"])}
except ValueError:
continue
return out
def sessions_needing_check(self, version: str) -> list[str]:
"""Sessions with a finished turn whose Issues result is missing or was made by
another `signals.yaml` version (P3-4): the ones the start-up re-check works on."""
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT session_id, issues FROM turns WHERE status IS NOT NULL AND status != 'running'"
).fetchall()
out: dict[str, None] = {}
for r in rows:
if r["session_id"] in out:
continue
try:
have = json.loads(r["issues"]).get("signals_version") if r["issues"] else None
except (ValueError, AttributeError):
have = None
if have != version:
out[r["session_id"]] = None
return list(out)
def mark_issues_seen(self, session_id: str) -> None:
self.flush()
with self._lock, self._conn:
self._conn.execute("UPDATE turns SET issues_seen = 1 WHERE session_id = ?", (session_id,))
def unseen_warnings(self) -> dict[str, int]:
"""Warnings not seen yet, by session: the sidebar dot (TD2-4)."""
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT session_id, issues FROM turns WHERE issues_seen = 0 AND issues IS NOT NULL"
).fetchall()
out: dict[str, int] = {}
for r in rows:
try:
warn = int(json.loads(r["issues"]).get("warn") or 0)
except (ValueError, TypeError):
continue
if warn:
out[r["session_id"]] = out.get(r["session_id"], 0) + warn
return out
# -- the usage ledger (Phase 3, E24a; attributes.yaml Part 3) -------------------------------
# One row per turn x purpose x model, content-free, worked out from the stored turn, its
# steps and its Issues result whenever one of them changes. The rollups read it; the Steps
# panel keeps reading the steps. `persona_of(session_id)` stands in for `stamp.persona_id`
# on turns recorded before 1.3 (the session's coworker, from coworker.db).
persona_of: Optional[Any] = None
def refresh_usage(self, turn_id: str) -> None:
"""Work out a turn's ledger rows again at the next write (turn end, a late step)."""
with self._pending_lock:
self._pending_refresh.add(turn_id)
if self._background:
_writer.wake()
def _refresh_usage(self, turn_ids: Iterable[str]) -> None:
"""With the connection lock held: replace each finished turn's ledger rows, each turn in
a transaction of its own. A turn whose rows cannot be worked out keeps its old rows and
is logged; the others go on (P3B-6)."""
now = int(time.time() * 1000)
for tid in turn_ids:
try:
with self._conn:
self._refresh_turn_usage(tid, now)
except Exception:
log.exception("recordings: usage ledger not updated for turn %s", tid)
def _refresh_turn_usage(self, tid: str, now: int) -> None:
from .rollups import LEDGER_COLUMNS, ledger_rows
t = self._conn.execute("SELECT * FROM turns WHERE turn_id = ?", (tid,)).fetchone()
if t is None or t["status"] in (None, "running"):
return
self._touch_sessions({t["session_id"]}) # its Issues result or ledger rows changed
steps = [_step_record(r) for r in self._conn.execute(
"SELECT * FROM steps WHERE turn_id = ? AND kind IN ('model', 'wait', 'auto_approver', 'tool')",
(tid,))]
try:
issues = json.loads(t["issues"]) if t["issues"] else None
except ValueError:
issues = None
prior = self._conn.execute(
"SELECT rating, session_deleted, persona_id FROM turn_usage WHERE turn_id = ? AND first = 1",
(tid,)).fetchone()
fb = self._conn.execute("SELECT rating FROM feedback WHERE turn_id = ?", (tid,)).fetchone()
persona = prior["persona_id"] if prior is not None else None
if persona is None and self.persona_of is not None:
try:
persona = self.persona_of(t["session_id"])
except Exception: # noqa: BLE001 - a stand-in never breaks the write
persona = None
rating = fb["rating"] if fb is not None else (prior["rating"] if prior is not None else None)
rows = ledger_rows(_turn_record(t), steps, issues, persona_id=persona, rating=rating, now_ms=now)
if prior is not None and prior["session_deleted"]:
for r in rows:
r["session_deleted"] = 1
self._conn.execute("DELETE FROM turn_usage WHERE turn_id = ?", (tid,))
self._conn.executemany(
f"INSERT INTO turn_usage ({', '.join(LEDGER_COLUMNS)}) "
f"VALUES ({', '.join('?' * len(LEDGER_COLUMNS))})",
[tuple(r.get(c) for c in LEDGER_COLUMNS) for r in rows])
def sweep_ledger(self, *, now_ms: Optional[int] = None) -> int:
"""Ledger rows older than 90 days go (TD3-5), whatever "Keep for" says."""
now = int(time.time() * 1000) if now_ms is None else now_ms
with self._lock, self._conn:
cur = self._conn.execute("DELETE FROM turn_usage WHERE started_at < ?",
(now - LEDGER_DAYS * 24 * 60 * 60 * 1000,))
return cur.rowcount
def turns_without_usage(self, limit: int = 200) -> list[str]:
"""Finished turns with no ledger rows (recorded before Phase 3): the background job
fills them in batches (TD3-8)."""
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT t.turn_id FROM turns t WHERE t.status IS NOT NULL AND t.status != 'running' "
"AND NOT EXISTS (SELECT 1 FROM turn_usage u WHERE u.turn_id = t.turn_id) "
"ORDER BY t.started_at LIMIT ?", (limit,)).fetchall()
return [r["turn_id"] for r in rows]
@staticmethod
def _filters(where: Optional[dict[str, Any]], allowed: set[str], alias: str = "") -> Optional[tuple[str, list]]:
"""Equal-to (or IN, for a list) filters on ledger columns. None: an empty list, so no row."""
clauses, params = "", []
for k, v in (where or {}).items():
if k not in allowed:
raise ValueError(f"cannot filter on {k!r}")
if isinstance(v, (list, tuple, set)):
v = list(v)
if not v:
return None
clauses += f" AND {alias}{k} IN ({', '.join('?' * len(v))})"
params += v
elif v is None:
clauses += f" AND {alias}{k} IS NULL"
else:
clauses += f" AND {alias}{k} = ?"
params.append(v)
return clauses, params
_FILTERABLE = {"persona_id", "persona_fp", "team_id", "task_id", "run_id", "session_id", "machine"}
def usage_rollup(self, group: list[str], start: int, end: int, *, days: Optional[list] = None,
where: Optional[dict[str, Any]] = None) -> list[dict[str, Any]]:
"""One GROUP BY over the ledger: the turns that started in [start, end), grouped.
`days`: the viewer's [label, start, end) boundaries (TD3-2); `where`: filters on
ledger columns (persona_id, team_id, task_id, run_id, session_id, machine)."""
from .rollups import ROLLUP_FIELDS, day_params, rollup_sql
f = self._filters(where, self._FILTERABLE, "u.")
if f is None:
return []
sql, n = rollup_sql(group, days=days, where=f[0])
self.flush()
with self._lock:
rows = self._conn.execute(sql, [*day_params(days), start, end, *f[1]]).fetchall()
out = []
for r in rows:
vals = list(r)
out.append({"key": dict(zip(group or ["all"], vals[:n])),
**{k: int(v or 0) for k, v in zip(ROLLUP_FIELDS, vals[n:])}})
return out
_SUMMED = ("input_fresh", "cache_read", "cache_write", "output", "reasoning", "cache_read_known_input",
"calls", "unreported", "failed", "over_limit")
def usage_turns(self, start: int, end: int, *, where: Optional[dict[str, Any]] = None) -> list[dict[str, Any]]:
"""Each turn of the period as one dict (its rows summed), oldest first: what the
medians, heaviest turns, coworker runs and automation runs read."""
f = self._filters(where, self._FILTERABLE)
if f is None:
return []
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT * FROM turn_usage WHERE started_at >= ? AND started_at < ?" + f[0] +
" ORDER BY started_at, turn_id, first DESC", [start, end, *f[1]]).fetchall()
out: dict[str, dict[str, Any]] = {}
for r in rows:
d = out.get(r["turn_id"])
if d is None:
d = out[r["turn_id"]] = {k: r[k] for k in r.keys() if k not in ("purpose", "model", "first")}
d["warn_checks"] = json.loads(d["warn_checks"] or "[]")
d["total"], d["models"], d["by_purpose"] = 0, [], {}
# T3-15: the models of the coworker's own work (main and helpers), without the
# auto-approver's, the title's or the summary's
d["own_models"] = []
for k in self._SUMMED:
d[k] = 0
for k in self._SUMMED:
d[k] += r[k] or 0
tot = (r["input_fresh"] or 0) + (r["cache_read"] or 0) + (r["cache_write"] or 0) + (r["output"] or 0)
d["total"] += tot
if r["purpose"] is not None:
d["by_purpose"][r["purpose"]] = d["by_purpose"].get(r["purpose"], 0) + tot
# T3-4: one name per model (openai: dropped), however the turn recorded it
model = canonical_model(r["model"]) if r["model"] else None
if model and model not in d["models"] and (r["calls"] or r["unreported"]):
d["models"].append(model)
if (model and r["purpose"] in ("main", "helper") and model not in d["own_models"]
and (r["calls"] or r["unreported"])):
d["own_models"].append(model)
for d in out.values():
d["at_least"] = d["unreported"] > 0
d["active_ms"] = None if d["duration_ms"] is None else d["duration_ms"] - (d["waiting_ms"] or 0)
return list(out.values())
def usage_kept_from(self) -> Optional[int]:
"""The oldest ledger row's start: how far back the numbers go."""
self.flush()
with self._lock:
row = self._conn.execute("SELECT MIN(started_at) AS m FROM turn_usage").fetchone()
return row["m"]
def approver_steps(self, start: int, end: int, *, persona_id: Optional[str] = None) -> list[dict[str, Any]]:
"""The auto-approver checks, and the tool steps a person let through anyway, of turns
that started in the period (the Auto-approve strip, section 7.3). Steps, not the ledger:
the strip reads Phase 1 fields that only steps hold."""
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT s.kind, s.shadow, s.duration_ms, s.attrs, s.parent_step_id, t.reviewer FROM steps s "
"JOIN turns t ON t.turn_id = s.turn_id WHERE t.started_at >= ? AND t.started_at < ? "
"AND (s.kind = 'auto_approver' OR (s.kind = 'tool' AND s.attrs LIKE '%allow_anyway%'))"
# one coworker: by the ledger's coworker id, which stands in for 1.2 turns
+ (" AND EXISTS (SELECT 1 FROM turn_usage u WHERE u.turn_id = t.turn_id AND u.persona_id = ?)"
if persona_id else ""),
(start, end, persona_id) if persona_id else (start, end)).fetchall()
return [{"kind": r["kind"], "shadow": bool(r["shadow"]), "duration_ms": r["duration_ms"],
"parent_step_id": r["parent_step_id"], "attrs": json.loads(r["attrs"] or "{}"),
"turn_reviewer": json.loads(r["reviewer"]) if r["reviewer"] else None} for r in rows]
def approver_tokens_by_reviewer(self, start: int, end: int, *,
persona_id: Optional[str] = None) -> dict[str, int]:
"""The live auto-approver model calls' tokens of turns that started in the period, by the
reviewer kind the turn ran with (T3-6, one row per reviewer). Shadow checks are left out:
the strip's total, from the ledger, has them."""
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT json_extract(t.reviewer, '$.kind') AS rkind, SUM(COALESCE(s.input_fresh,0)"
"+COALESCE(s.cache_read,0)+COALESCE(s.cache_write,0)+COALESCE(s.output,0)) AS tok "
"FROM steps s JOIN turns t ON t.turn_id = s.turn_id WHERE t.started_at >= ? AND t.started_at < ? "
"AND s.kind = 'model' AND s.shadow = 0 AND s.usage_reported = 1 "
"AND s.purpose = 'auto_approver'"
+ (" AND EXISTS (SELECT 1 FROM turn_usage u WHERE u.turn_id = t.turn_id AND u.persona_id = ?)"
if persona_id else "") + " GROUP BY rkind",
(start, end, persona_id) if persona_id else (start, end)).fetchall()
return {r["rkind"] or "unknown": int(r["tok"] or 0) for r in rows}
def outside_calls_between(self, start: int, end: int) -> dict[str, int]:
"""Model steps with no turn (proxy calls) in [start, end): in no pill, so outside the
Usage page's total, on their own line (TD3-4)."""
self.flush()
with self._lock:
r = self._conn.execute(
"SELECT COUNT(*) AS n, SUM(COALESCE(input_fresh,0)+COALESCE(cache_read,0)+COALESCE(cache_write,0)"
"+COALESCE(output,0)) AS tok FROM steps WHERE turn_id IS NULL AND kind = 'model' "
"AND usage_reported = 1 AND started_at >= ? AND started_at < ?", (start, end)).fetchone()
return {"calls": int(r["n"] or 0), "total": int(r["tok"] or 0)}
def turn_seqs(self, turn_ids: list[str]) -> dict[str, int]:
"""Each turn's number in its session ("turn 4"), for turns the store still has."""
if not turn_ids:
return {}
self.flush()
with self._lock:
rows = self._conn.execute(
f"SELECT turn_id, seq FROM turns WHERE turn_id IN ({', '.join('?' * len(turn_ids))})",
list(turn_ids)).fetchall()
return {r["turn_id"]: r["seq"] for r in rows}
def team_turns(self, session_ids: list[str]) -> list[dict[str, Any]]:
"""The member sessions' turns, oldest first, with `wake` (1.3): what TeamView's event
list and the lead-polling series read."""
if not session_ids:
return []
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT turn_id, session_id, seq, started_at, duration_ms, status, wake, stamp FROM turns "
f"WHERE session_id IN ({', '.join('?' * len(session_ids))}) ORDER BY started_at, turn_id",
list(session_ids)).fetchall()
return [{"turn_id": r["turn_id"], "session_id": r["session_id"], "seq": r["seq"],
"started_at": r["started_at"], "duration_ms": r["duration_ms"], "status": r["status"],
"wake": json.loads(r["wake"]) if r["wake"] else None,
"stamp": json.loads(r["stamp"] or "{}")} for r in rows]
def last_turn_automation(self, session_id: str) -> Optional[dict[str, Any]]:
"""The session's latest turn's `turn.automation` (1.3), or None when that turn was not
an automation run's own (a follow-up typed later, or no turn yet)."""
self.flush()
with self._lock:
r = self._conn.execute("SELECT automation FROM turns WHERE session_id = ? "
"ORDER BY started_at DESC, seq DESC LIMIT 1", (session_id,)).fetchone()
if r is None or not r["automation"]:
return None
try:
return json.loads(r["automation"])
except ValueError:
return None
def last_tool_step(self, session_id: str, name: str, *, before_ms: Optional[int] = None) -> Optional[dict[str, Any]]:
"""The session's last tool step of this name started at or before `before_ms` (any
time when None): {step_id, seq, turn_id}. What a hand-off's `transition` step is found
by, without loading the whole recording (P3B-5)."""
self.flush()
sql = "SELECT step_id, seq, turn_id FROM steps WHERE session_id = ? AND kind = 'tool' AND name = ?"
args: list[Any] = [session_id, name]
if before_ms is not None:
sql += " AND COALESCE(started_at, 0) <= ?"
args.append(before_ms)
with self._lock:
r = self._conn.execute(sql + " ORDER BY started_at DESC, seq DESC LIMIT 1", args).fetchone()
return None if r is None else {"step_id": r["step_id"], "seq": r["seq"], "turn_id": r["turn_id"]}
def team_approval_cards(self, session_ids: list[str]) -> list[dict[str, Any]]:
"""The members' approval cards in order: each wait step of kind approval with its
tool step's name and args_hash (`same_approval_twice`, signals 0.6)."""
if not session_ids:
return []
self.flush()
marks = ", ".join("?" * len(session_ids))
with self._lock:
waits = self._conn.execute(
"SELECT s.step_id, s.turn_id, s.session_id, s.started_at, s.attrs, t.seq FROM steps s "
"JOIN turns t ON t.turn_id = s.turn_id "
f"WHERE s.kind = 'wait' AND s.session_id IN ({marks}) ORDER BY s.started_at, s.step_id",
list(session_ids)).fetchall()
approvals = []
for w in waits:
a = json.loads(w["attrs"] or "{}")
if a.get("kind") == "approval" and a.get("for_call_id"):
approvals.append((w, a))
# Only the tool steps the cards ask about, by (session, call id): not every tool
# step of every member (P3B-5).
tools = {}
wanted = sorted({(w["session_id"], a["for_call_id"]) for w, a in approvals})
for i in range(0, len(wanted), 400):
part = wanted[i:i + 400]
where = " OR ".join("(session_id = ? AND call_id = ?)" for _ in part)
for r in self._conn.execute(
f"SELECT session_id, call_id, name, attrs FROM steps WHERE kind = 'tool' AND ({where})",
[v for pair in part for v in pair]).fetchall():
tools[(r["session_id"], r["call_id"])] = r
out = []
for w, a in approvals:
tool = tools.get((w["session_id"], a.get("for_call_id")))
if tool is None:
continue
out.append({"tool": tool["name"], "args_hash": json.loads(tool["attrs"] or "{}").get("args_hash"),
"session": w["session_id"], "outcome": a.get("outcome"), "turn_id": w["turn_id"],
"seq": w["seq"], "at": w["started_at"],
"step": {"step_id": w["step_id"], "turn_id": w["turn_id"]}})
return out
def scan_steps(self, session_ids: Any, *, since_ms: int = 0) -> list[dict[str, Any]]:
"""Tool steps whose `scanner.action` is `scan` (1.3), of one or more sessions, oldest
first: what a finding's evidence and the coverage checks read."""
ids = [session_ids] if isinstance(session_ids, str) else list(session_ids)
if not ids:
return []
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT s.step_id, s.turn_id, s.session_id, s.call_id, s.seq, s.started_at, s.status, s.attrs, "
"t.seq AS turn_seq FROM steps s LEFT JOIN turns t ON t.turn_id = s.turn_id "
f"WHERE s.kind = 'tool' AND s.session_id IN ({', '.join('?' * len(ids))}) AND s.started_at >= ? "
"AND s.attrs LIKE '%\"scanner\": {%' ORDER BY s.started_at, s.step_id", [*ids, since_ms]).fetchall()
out = []
for r in rows:
a = json.loads(r["attrs"] or "{}")
sc = a.get("scanner") or {}
if sc.get("action") != "scan":
continue
out.append({"step_id": r["step_id"], "turn_id": r["turn_id"], "session_id": r["session_id"],
"call_id": r["call_id"], "seq": r["seq"], "turn_seq": r["turn_seq"],
"started_at": r["started_at"], "status": r["status"], "scanner": sc,
"exit_code": a.get("exit_code"), "result_sha256": a.get("result_sha256")})
return out
def read_turn_issues(self, turn_id: str) -> Optional[dict[str, Any]]:
"""One turn's stored Issues result with `seen`, or None."""
self.flush()
with self._lock:
r = self._conn.execute("SELECT issues, issues_seen FROM turns WHERE turn_id = ?", (turn_id,)).fetchone()
if r is None or not r["issues"]:
return None
try:
return {**json.loads(r["issues"]), "seen": bool(r["issues_seen"])}
except ValueError:
return None
# -- feedback (Phase 3, E27a) ----------------------------------------------------------------
def put_feedback(self, row: dict[str, Any]) -> dict[str, Any]:
"""Store (or replace) a turn's rating. Changing a rating replaces the row; `rated_at`
stays the first rating's time and `changed_at` says when it last changed."""
now = int(time.time() * 1000)
with self._lock, self._conn:
old = self._conn.execute("SELECT rated_at FROM feedback WHERE turn_id = ?", (row["turn_id"],)).fetchone()
rated_at = old["rated_at"] if old is not None else now
changed = now if old is not None else None
self._conn.execute(
"INSERT OR REPLACE INTO feedback (turn_id, session_id, rating, reasons, note, auto_approved, "
"rated_at, changed_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
(row["turn_id"], row["session_id"], row["rating"], json.dumps(list(row.get("reasons") or [])),
row.get("note"), json.dumps(list(row.get("auto_approved") or [])), rated_at, changed))
self._conn.execute("UPDATE turn_usage SET rating = ? WHERE turn_id = ?", (row["rating"], row["turn_id"]))
return self.get_feedback(row["turn_id"]) or {}
def get_feedback(self, turn_id: str) -> Optional[dict[str, Any]]:
with self._lock:
r = self._conn.execute("SELECT * FROM feedback WHERE turn_id = ?", (turn_id,)).fetchone()
return None if r is None else _feedback_record(r)
def delete_feedback(self, turn_id: str) -> None:
with self._lock, self._conn:
self._conn.execute("DELETE FROM feedback WHERE turn_id = ?", (turn_id,))
self._conn.execute("UPDATE turn_usage SET rating = NULL WHERE turn_id = ?", (turn_id,))
def feedback_for(self, turn_ids: list[str]) -> dict[str, dict[str, Any]]:
"""The ratings of these turns, by turn id (the Usage page's Ratings card, T3-10)."""
out: dict[str, dict[str, Any]] = {}
ids = list(dict.fromkeys(turn_ids))
with self._lock:
for i in range(0, len(ids), 400):
chunk = ids[i:i + 400]
rows = self._conn.execute(
f"SELECT * FROM feedback WHERE turn_id IN ({','.join('?' * len(chunk))})", chunk).fetchall()
for r in rows:
out[r["turn_id"]] = _feedback_record(r)
return out
def session_feedback(self, session_id: str) -> list[dict[str, Any]]:
with self._lock:
rows = self._conn.execute("SELECT * FROM feedback WHERE session_id = ? ORDER BY rated_at",
(session_id,)).fetchall()
return [_feedback_record(r) for r in rows]
def feedback_since(self, since_ms: int, limit: int = 100, *,
unsent_for: Optional[str] = None) -> list[dict[str, Any]]:
"""Ratings given or changed at or after `since_ms`, oldest change first: the
exporter's queue (E27b). With `unsent_for`, only the versions with no export mark for
that destination yet (mark `feedback|<turn_id>|<changed_at or rated_at>`), so the
queue keeps moving past the first `limit` once those have gone (P3B-2)."""
sql = "SELECT * FROM feedback f WHERE COALESCE(f.changed_at, f.rated_at) >= ?"
args: list[Any] = [since_ms]
if unsent_for is not None:
sql += (" AND NOT EXISTS (SELECT 1 FROM export_marks m WHERE m.destination = ? AND m.turn_id = f.turn_id"
" AND m.item_id = 'feedback|' || f.turn_id || '|' || COALESCE(f.changed_at, f.rated_at))")
args.append(unsent_for)
sql += " ORDER BY COALESCE(f.changed_at, f.rated_at) LIMIT ?"
args.append(limit)
with self._lock:
rows = self._conn.execute(sql, args).fetchall()
return [_feedback_record(r) for r in rows]
# -- coworker versions (Phase 3, E25c) -------------------------------------------------------
def note_persona_version(self, persona_id: str, fp: str, name: Optional[str],
files: dict[str, bytes]) -> bool:
"""Keep a coworker version the first time its fingerprint is seen: its name, each
file's sha256 and line count, and a copy of the files for "Show diff" (TD3-13). Kept
locally only, never exported. Returns whether it was new. A later sighting only
updates the name, so the Usage page names a renamed coworker by its latest name."""
with self._lock, self._conn:
have = self._conn.execute("SELECT 1 FROM persona_versions WHERE persona_id = ? AND fp = ?",
(persona_id, fp)).fetchone()
if have is not None:
if name:
self._conn.execute("UPDATE persona_versions SET name = ? WHERE persona_id = ? AND fp = ?",
(name, persona_id, fp))
return False
meta = {path: {"sha256": sha256(data).hexdigest(), "lines": _line_count(data)}
for path, data in files.items()}
self._conn.execute(
"INSERT INTO persona_versions (persona_id, fp, name, first_seen, files) VALUES (?, ?, ?, ?, ?)",
(persona_id, fp, name, int(time.time() * 1000), json.dumps(meta, sort_keys=True)))
self._conn.executemany(
"INSERT OR REPLACE INTO persona_files (persona_id, fp, path, data) VALUES (?, ?, ?, ?)",
[(persona_id, fp, path, zlib.compress(data)) for path, data in files.items()])
return True
def persona_versions(self, persona_id: Optional[str] = None) -> list[dict[str, Any]]:
with self._lock:
rows = self._conn.execute(
"SELECT * FROM persona_versions" + (" WHERE persona_id = ?" if persona_id else "")
+ " ORDER BY first_seen", (persona_id,) if persona_id else ()).fetchall()
return [{"persona_id": r["persona_id"], "fp": r["fp"], "name": r["name"], "first_seen": r["first_seen"],
"files": json.loads(r["files"])} for r in rows]
def persona_file(self, persona_id: str, fp: str, path: str) -> Optional[bytes]:
with self._lock:
r = self._conn.execute("SELECT data FROM persona_files WHERE persona_id = ? AND fp = ? AND path = ?",
(persona_id, fp, path)).fetchone()
return None if r is None else zlib.decompress(r["data"])
# -- reading ------------------------------------------------------------------------------
def turn_count(self, session_id: str) -> int:
with self._lock:
row = self._conn.execute(
"SELECT COUNT(*) AS n FROM turns WHERE session_id = ?", (session_id,)
).fetchone()
return int(row["n"])
def worker_turns(self, lead_session_id: str, since_ms: int = 0) -> list[tuple[dict[str, Any], list[dict[str, Any]]]]:
"""Team workers' turns woken by this lead (`woken_by.lead_session_id`, 1.2), started at
or after `since_ms`, each with its steps: what the lead's timeline shows as worker rows
(OB-7, derived on read, never copied into the lead's recording)."""
self.flush()
with self._lock:
rows = self._conn.execute(
"SELECT * FROM turns WHERE woken_by IS NOT NULL AND started_at >= ? "
"AND json_extract(woken_by, '$.lead_session_id') = ? ORDER BY started_at, turn_id",
(since_ms, lead_session_id),
).fetchall()
ids = [r["turn_id"] for r in rows]
steps = self._conn.execute(
f"SELECT * FROM steps WHERE turn_id IN ({', '.join('?' * len(ids))}) ORDER BY seq",
ids,
).fetchall() if ids else []
by_turn: dict[str, list[dict[str, Any]]] = {}
for s in steps:
by_turn.setdefault(s["turn_id"], []).append(_step_record(s))
return [(_turn_record(r), by_turn.get(r["turn_id"], [])) for r in rows]
def load_recording(self, session_id: str) -> dict[str, Any]:
"""A session's recording as format 1.0 JSON: {"turns": [...], "steps": [...]}.
Turns by (started_at, turn_id); each turn's steps by `seq`."""
self.flush()
with self._lock:
turns = self._conn.execute(
"SELECT * FROM turns WHERE session_id = ? ORDER BY started_at, turn_id",
(session_id,),
).fetchall()
steps = self._conn.execute(
"SELECT * FROM steps WHERE session_id = ?", (session_id,)
).fetchall()
order = {t["turn_id"]: i for i, t in enumerate(turns)}
steps = sorted(
steps,
key=lambda s: (order.get(s["turn_id"], len(order)), s["seq"] is None, s["seq"] or 0,
s["started_at"] or 0),
)
return {"turns": [_turn_record(t) for t in turns], "steps": [_step_record(s) for s in steps]}
class _SharedWriter:
"""One background thread for every open store in the process. It sleeps until a store
has something to write (or an hour passes, to check the daily sweep), then writes each
store's pending records in one transaction. Stores are held weakly, so a store nobody
uses any more is not kept alive by the writer (tests make thousands of them)."""
def __init__(self) -> None:
self._stores: "weakref.WeakSet[RecordingStore]" = weakref.WeakSet()
self._lock = threading.Lock()
self._wake = threading.Event()
self._thread: Optional[threading.Thread] = None
def add(self, store: "RecordingStore") -> None:
with self._lock:
self._stores.add(store)
if self._thread is None or not self._thread.is_alive():
self._thread = threading.Thread(target=self._run, name="recordings-writer", daemon=True)
self._thread.start()
self._wake.set() # a new store's first sweep runs soon
def remove(self, store: "RecordingStore") -> None:
with self._lock:
self._stores.discard(store)
def wake(self) -> None:
self._wake.set()
def _run(self) -> None:
while True:
self._wake.wait(timeout=3600)
self._wake.clear()
with self._lock:
stores = list(self._stores)
for store in stores:
try:
store._tick()
except Exception:
log.exception("recordings: writer failed")
del stores
time.sleep(FLUSH_MS / 1000) # gather the next burst of writes into one transaction
_writer = _SharedWriter()
def _turn_record(r: sqlite3.Row) -> dict[str, Any]:
d = {k: r[k] for k in _TURN_COLUMNS}
d["incomplete"] = bool(d["incomplete"])
d["attended"] = None if d["attended"] is None else bool(d["attended"])
d["reviewer"] = None if d["reviewer"] is None else json.loads(d["reviewer"])
d["stamp"] = json.loads(d["stamp"] or "{}")
# Format 1.2 fields are left out when not recorded, so a 1.1 turn reads back unchanged.
if d.get("max_iterations") is None:
d.pop("max_iterations", None)
for key in ("woken_by", "wake", "automation"): # 1.2 and 1.3: absent when not recorded
if d.get(key) is None:
d.pop(key, None)
else:
d[key] = json.loads(d[key])
return d
def _line_count(data: bytes) -> int:
return data.count(b"\n") + (1 if data and not data.endswith(b"\n") else 0)
def _feedback_record(r: sqlite3.Row) -> dict[str, Any]:
"""A feedback row as recording-format 1.3 `feedback` (the note in `content`)."""
return {"turn_id": r["turn_id"], "session_id": r["session_id"], "rating": r["rating"],
"reasons": json.loads(r["reasons"] or "[]"), "auto_approved": json.loads(r["auto_approved"] or "[]"),
"rated_at": r["rated_at"], "changed_at": r["changed_at"], "content": {"note": r["note"]}}
def _step_record(r: sqlite3.Row) -> dict[str, Any]:
attrs = json.loads(r["attrs"] or "{}")
if r["provider_meta"] is not None:
attrs["provider_meta"] = json.loads(zlib.decompress(r["provider_meta"]))
return {
"step_id": r["step_id"], "turn_id": r["turn_id"], "session_id": r["session_id"],
"parent_step_id": r["parent_step_id"], "seq": r["seq"], "kind": r["kind"],
"name": r["name"], "started_at": r["started_at"], "duration_ms": r["duration_ms"],
"status": r["status"], "call_id": r["call_id"], "call_index": r["call_index"],
"message_id": r["message_id"], "shadow": bool(r["shadow"]),
"after_turn": bool(r["after_turn"]), "attrs": attrs,
"content": json.loads(r["content"] or "{}"),
}