Spaces:
Running on Zero
Running on Zero
Download src/api/db.py from Kagzy/ResearchOS: direct link, hf CLI and curl.
- Browser
- Download file 14.1 kB
-
https://huggingface.co/spaces/Kagzy/ResearchOS/resolve/main/src/api/db.py
- Command line
-
hf download hf://spaces/Kagzy/ResearchOS/src/api/db.py
-
curl -L -o db.py https://huggingface.co/spaces/Kagzy/ResearchOS/resolve/main/src/api/db.py
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 | |
| ), | |
| } | |