ResearchOS / src /api /db.py
Kagzy's picture
feedback persistence; probe traffic excluded from v2 metrics
273dddc verified
Raw History Blame Contribute Delete
14.1 kB
"""SQLite logging: queries, citations, clicks, votes and failures.
This is the instrumentation that makes a small deployment worth running. With
several people using it, the interesting question is not "did it work" but
"where is it failing, and on what kind of question" - which needs the failures
recorded as deliberately as the successes.
Four signals, in ascending order of how much they tell you:
clicks which citation someone opened. Weak, plentiful, unbiased.
votes an explicit thumbs up/down. Strong, scarce, self-selected.
low queries the retriever itself flagged as out-of-corpus. Free, and the
fastest route to "what do people ask that this cannot answer".
errors exceptions, with endpoint and query. Silent otherwise.
"""
from __future__ import annotations
import sqlite3
import traceback
from datetime import datetime, timezone
from pathlib import Path
from typing import Any
SCHEMA = """
CREATE TABLE IF NOT EXISTS queries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ts TEXT NOT NULL,
query TEXT NOT NULL,
answer TEXT,
latency_ms REAL,
backend TEXT
);
CREATE TABLE IF NOT EXISTS citations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
query_id INTEGER NOT NULL REFERENCES queries(id),
doc_id TEXT NOT NULL,
rank INTEGER NOT NULL,
score REAL,
title TEXT
);
CREATE TABLE IF NOT EXISTS citation_clicks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ts TEXT NOT NULL,
query_id INTEGER NOT NULL REFERENCES queries(id),
doc_id TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS votes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ts TEXT NOT NULL,
query_id INTEGER NOT NULL REFERENCES queries(id),
helpful INTEGER NOT NULL,
comment TEXT
);
CREATE TABLE IF NOT EXISTS errors (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ts TEXT NOT NULL,
endpoint TEXT NOT NULL,
query TEXT,
error_type TEXT NOT NULL,
message TEXT,
stack TEXT
);
CREATE INDEX IF NOT EXISTS idx_citations_query ON citations(query_id);
CREATE INDEX IF NOT EXISTS idx_clicks_query ON citation_clicks(query_id);
CREATE INDEX IF NOT EXISTS idx_votes_query ON votes(query_id);
CREATE INDEX IF NOT EXISTS idx_errors_ts ON errors(ts);
"""
# Columns added after the first release. SQLite has no "ADD COLUMN IF NOT
# EXISTS", so they are applied one at a time and duplicates ignored.
MIGRATIONS = [
("queries", "mode", "TEXT"),
("queries", "confident", "INTEGER"),
("queries", "top_score", "REAL"),
("queries", "mean_top_score", "REAL"),
("queries", "n_results", "INTEGER"),
("queries", "generated", "INTEGER"),
# v2: what the diagnosis said, and whether a query was a retry of one.
("queries", "source", "TEXT"),
("queries", "diagnosis_location", "TEXT"),
("queries", "suggested_query", "TEXT"),
("queries", "retry_of", "INTEGER"),
("queries", "probe", "INTEGER"), # monitoring traffic, not a user
]
def _now() -> str:
return datetime.now(timezone.utc).isoformat(timespec="seconds")
class FeedbackLog:
"""Thin wrapper over a SQLite file. Safe to construct per request."""
def __init__(self, path: str = "results/feedback.db") -> None:
self.path = Path(path)
self.path.parent.mkdir(parents=True, exist_ok=True)
with self._connect() as conn:
conn.executescript(SCHEMA)
for table, column, coltype in MIGRATIONS:
try:
conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {coltype}")
except sqlite3.OperationalError:
pass # already present
def _connect(self) -> sqlite3.Connection:
conn = sqlite3.connect(self.path, timeout=10.0)
conn.row_factory = sqlite3.Row
return conn
# ------------------------------------------------------------- recording
def log_query(
self,
query: str,
answer: str | None,
latency_ms: float,
backend: str,
citations: list[dict[str, Any]],
mode: str = "ask",
confident: bool | None = None,
top_score: float | None = None,
mean_top_score: float | None = None,
generated: bool | None = None,
source: str | None = None,
diagnosis: dict[str, Any] | None = None,
retry_of: int | None = None,
probe: bool = False,
) -> int:
"""Record a query, its answer, the passages shown, and any v2 diagnosis."""
with self._connect() as conn:
if retry_of is not None and conn.execute(
"SELECT 1 FROM queries WHERE id = ?", (retry_of,)).fetchone() is None:
retry_of = None # a stale or forged id must not corrupt the funnel
cursor = conn.execute(
"INSERT INTO queries (ts, query, answer, latency_ms, backend, mode, "
"confident, top_score, mean_top_score, n_results, generated, source, "
"diagnosis_location, suggested_query, retry_of, probe) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
(
_now(), query, answer, latency_ms, backend, mode,
None if confident is None else int(confident),
top_score, mean_top_score, len(citations),
None if generated is None else int(generated),
source,
(diagnosis or {}).get("location"),
(diagnosis or {}).get("suggested_query"),
retry_of,
int(probe),
),
)
query_id = int(cursor.lastrowid)
conn.executemany(
"INSERT INTO citations (query_id, doc_id, rank, score, title) VALUES (?, ?, ?, ?, ?)",
[
(query_id, c.get("doc_id", ""), rank, c.get("score"), c.get("title"))
for rank, c in enumerate(citations, start=1)
],
)
return query_id
def log_click(self, query_id: int, doc_id: str) -> None:
with self._connect() as conn:
conn.execute(
"INSERT INTO citation_clicks (ts, query_id, doc_id) VALUES (?, ?, ?)",
(_now(), query_id, doc_id),
)
def log_vote(self, query_id: int, helpful: bool, comment: str | None = None) -> None:
"""Explicit thumbs up/down. The scarcest and most valuable signal."""
with self._connect() as conn:
conn.execute(
"INSERT INTO votes (ts, query_id, helpful, comment) VALUES (?, ?, ?, ?)",
(_now(), query_id, int(helpful), comment),
)
def log_error(
self, endpoint: str, error: BaseException, query: str | None = None
) -> None:
"""Record an exception. Without this, failures are invisible."""
with self._connect() as conn:
conn.execute(
"INSERT INTO errors (ts, endpoint, query, error_type, message, stack) "
"VALUES (?, ?, ?, ?, ?, ?)",
(
_now(), endpoint, query, type(error).__name__, str(error)[:2000],
"".join(traceback.format_exception(type(error), error, error.__traceback__))[-4000:],
),
)
# ------------------------------------------------------------- reporting
def stats(self) -> dict[str, int]:
with self._connect() as conn:
def count(sql: str) -> int:
return int(conn.execute(sql).fetchone()[0])
return {
"queries": count("SELECT COUNT(*) FROM queries"),
"citations": count("SELECT COUNT(*) FROM citations"),
"clicks": count("SELECT COUNT(*) FROM citation_clicks"),
"votes_up": count("SELECT COUNT(*) FROM votes WHERE helpful=1"),
"votes_down": count("SELECT COUNT(*) FROM votes WHERE helpful=0"),
"errors": count("SELECT COUNT(*) FROM errors"),
"low_confidence": count("SELECT COUNT(*) FROM queries WHERE confident=0"),
}
def _user_view(self) -> sqlite3.Connection:
"""A connection where `user_queries` is every query except probes."""
conn = self._connect()
conn.execute("CREATE TEMP VIEW IF NOT EXISTS user_queries AS "
"SELECT * FROM queries WHERE COALESCE(probe, 0) = 0")
return conn
def v2_metrics(self) -> dict[str, Any]:
"""The live v2 funnel: diagnosis shown -> retry clicked -> result used.
Offline evaluation can only ask whether the target paper ranked; this
asks whether real users found the suggestion worth following, and
whether following it led to a paper they opened. The baseline is
gate-fired queries nobody retried: did those users open anything?
"""
with self._user_view() as conn:
def one(sql: str) -> float:
return float(conn.execute(sql).fetchone()[0] or 0)
shown = one("SELECT COUNT(*) FROM user_queries WHERE diagnosis_location IS NOT NULL")
with_query = one("SELECT COUNT(*) FROM user_queries WHERE suggested_query IS NOT NULL")
retries = one("SELECT COUNT(*) FROM user_queries WHERE retry_of IS NOT NULL")
retry_clicked = one(
"SELECT COUNT(DISTINCT q.id) FROM user_queries q JOIN citation_clicks c "
"ON c.query_id = q.id WHERE q.retry_of IS NOT NULL")
fired_not_retried = one(
"SELECT COUNT(*) FROM user_queries q WHERE q.confident = 0 AND q.retry_of IS NULL "
"AND NOT EXISTS (SELECT 1 FROM user_queries r WHERE r.retry_of = q.id)")
fired_not_retried_clicked = one(
"SELECT COUNT(DISTINCT q.id) FROM user_queries q JOIN citation_clicks c "
"ON c.query_id = q.id WHERE q.confident = 0 AND q.retry_of IS NULL "
"AND NOT EXISTS (SELECT 1 FROM user_queries r WHERE r.retry_of = q.id)")
by_location = {
(r[0] or "none").split(":")[0]: r[1] for r in conn.execute(
"SELECT diagnosis_location, COUNT(*) FROM user_queries "
"WHERE diagnosis_location IS NOT NULL GROUP BY 1")
}
by_source = {r[0] or "corpus": r[1] for r in conn.execute(
"SELECT source, COUNT(*) FROM user_queries GROUP BY 1")}
def rate(a: float, b: float) -> float | None:
return round(a / b, 4) if b else None
return {
"diagnoses_shown": int(shown),
"diagnoses_with_query": int(with_query),
"retries": int(retries),
"retry_rate": rate(retries, with_query),
"retry_click_through": rate(retry_clicked, retries),
"baseline_click_through": rate(fired_not_retried_clicked, fired_not_retried),
"baseline_queries": int(fired_not_retried),
"diagnosis_locations": by_location,
"answer_sources": by_source,
}
def v2_labels(self, limit: int = 500) -> list[dict[str, Any]]:
"""Retried-then-clicked pairs: free supervision for a failure detector.
Each row says "the original search did not satisfy this user, the
suggested query did, and here is the paper they opened" - exactly the
signal the offline gate could not see.
"""
with self._user_view() as conn:
return [dict(r) for r in conn.execute(
"SELECT o.id AS original_id, o.query AS original_query, "
"o.mean_top_score AS original_mean_score, o.diagnosis_location, "
"r.id AS retry_id, r.query AS retry_query, c.doc_id AS clicked_doc, "
"r.ts AS ts FROM user_queries r JOIN user_queries o ON o.id = r.retry_of "
"JOIN citation_clicks c ON c.query_id = r.id "
"ORDER BY r.id DESC LIMIT ?", (limit,)).fetchall()]
def recent_failures(self, limit: int = 50) -> dict[str, list[dict[str, Any]]]:
"""Everything worth reading when asking 'how is this failing?'."""
with self._connect() as conn:
def rows(sql: str, *args: Any) -> list[dict[str, Any]]:
return [dict(r) for r in conn.execute(sql, args).fetchall()]
return {
"errors": rows(
"SELECT ts, endpoint, query, error_type, message FROM errors "
"ORDER BY id DESC LIMIT ?", limit
),
"downvoted": rows(
"SELECT v.ts, q.query, q.mode, v.comment FROM votes v "
"JOIN queries q ON q.id = v.query_id WHERE v.helpful = 0 "
"ORDER BY v.id DESC LIMIT ?", limit
),
"out_of_corpus": rows(
"SELECT ts, query, mode, mean_top_score FROM queries "
"WHERE confident = 0 ORDER BY id DESC LIMIT ?", limit
),
"slowest": rows(
"SELECT ts, query, mode, latency_ms FROM queries "
"WHERE latency_ms IS NOT NULL ORDER BY latency_ms DESC LIMIT ?", limit
),
"never_clicked": rows(
"SELECT q.ts, q.query, q.mode FROM queries q "
"LEFT JOIN citation_clicks c ON c.query_id = q.id "
"WHERE c.id IS NULL AND q.confident = 1 ORDER BY q.id DESC LIMIT ?", limit
),
}