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
                ),
            }