Spaces:
Running on Zero
Running on Zero
File size: 14,058 Bytes
a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 273dddc a3c7ac6 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 | """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
),
}
|