Skip to main content

firstpass_proxy/
store.rs

1//! Tamper-evident trace storage: a background SQLite writer fed by a bounded channel.
2//!
3//! The hot request path never blocks on disk: it `try_send`s a [`Trace`] down a bounded channel
4//! and returns immediately (dropping the trace if the writer has fallen far enough behind to fill
5//! the buffer — bounded memory over a guaranteed write). A single background task owns the SQLite
6//! connection, assigns each trace's `prev_hash` from the current chain head, computes its `hash`,
7//! and appends it (SPEC §9: the hash chain is only meaningful if every writer agrees on chain
8//! order, which a single writer task guarantees for free).
9//!
10//! In [`crate::config::ReceiptsMode::Durable`] mode the channel-full path spills traces to
11//! `<db_path>.spill.jsonl` (one JSON line per trace, synced to disk) instead of dropping them.
12//! The writer drains the spill file at startup and whenever the channel empties, inserting spilled
13//! traces BEFORE new channel arrivals so the hash chain stays append-only and valid.
14
15use std::io::{BufRead, BufReader, Seek, SeekFrom, Write};
16use std::path::Path;
17use std::sync::Arc;
18use std::time::Duration;
19
20use firstpass_core::{DeferredVerdict, GENESIS_HASH, Score, Trace, Verdict};
21use rusqlite::{Connection, OptionalExtension as _};
22use tokio::sync::mpsc;
23use tokio::task::JoinHandle;
24
25use crate::config::ReceiptsMode;
26
27/// Open a connection with WAL + a busy timeout, so the background writer and short-lived
28/// feedback/read connections can share the file without "database is locked" errors.
29fn connect(db_path: impl AsRef<Path>) -> Result<Connection, StoreError> {
30    let conn = Connection::open(db_path.as_ref())?;
31    conn.busy_timeout(Duration::from_secs(5))?;
32    Ok(conn)
33}
34
35/// Errors from the trace store.
36#[derive(Debug, thiserror::Error)]
37pub enum StoreError {
38    /// The SQLite database could not be opened, migrated, or queried.
39    #[error("sqlite error: {0}")]
40    Sqlite(#[from] rusqlite::Error),
41    /// A trace could not be hashed or (de)serialized.
42    #[error("trace error: {0}")]
43    Trace(#[from] firstpass_core::Error),
44    /// A stored row was not valid trace JSON.
45    #[error("json error: {0}")]
46    Json(#[from] serde_json::Error),
47    /// An I/O error from the spill file.
48    #[error("spill I/O error: {0}")]
49    Io(#[from] std::io::Error),
50}
51
52/// A shared, mutex-guarded handle to the spill file used in durable mode.
53///
54/// Multiple async tasks may hit a full channel simultaneously; the `Mutex` serialises their
55/// appends without data loss.
56///
57/// ponytail: global Mutex over the file — upgrade to a dedicated spill-writer task if append
58/// throughput under sustained backpressure becomes the bottleneck.
59pub type SpillHandle = Arc<std::sync::Mutex<std::fs::File>>;
60
61/// Derive the spill-file path from the main DB path: `<db_path>.spill.jsonl`.
62pub(crate) fn spill_path(db_path: &Path) -> std::path::PathBuf {
63    let mut s = db_path.as_os_str().to_owned();
64    s.push(".spill.jsonl");
65    std::path::PathBuf::from(s)
66}
67
68/// Serialize `trace` as one JSON line and append it to the spill file, flushing to disk.
69///
70/// Called from the hot async path only when the writer channel is full (durable mode). This
71/// blocks the calling tokio task on the disk write — the deliberate tradeoff of durable mode;
72/// it only fires under sustained backpressure.
73///
74/// # Errors
75/// Returns [`StoreError::Json`] on serialization failure, [`StoreError::Io`] on disk failure.
76pub fn append_to_spill(handle: &SpillHandle, trace: &Trace) -> Result<(), StoreError> {
77    let line = serde_json::to_string(trace)?;
78    // Recover from a poisoned mutex rather than failing: a previous writer's panic doesn't
79    // corrupt the file, so we can safely continue appending.
80    let mut guard = handle.lock().unwrap_or_else(|e| e.into_inner());
81    writeln!(guard, "{line}")?;
82    // sync_data flushes the write to stable storage — the whole point of durable mode.
83    guard.sync_data()?;
84    Ok(())
85}
86
87/// Sending half of the trace channel; cheap to clone, safe to share across request handlers.
88/// Sending is fire-and-forget via `try_send` — the hot path never awaits the writer, and a bounded
89/// buffer means a stalled writer sheds load (drops traces) instead of growing memory without limit.
90pub type TraceSender = mpsc::Sender<Trace>;
91
92/// Trace buffer depth. Deep enough to absorb normal write bursts; bounded so a wedged writer (disk
93/// stall) can't OOM the process — excess traces are dropped with a warning, not queued forever.
94pub const TRACE_CHANNEL_CAP: usize = 8192;
95
96/// Open (creating if needed) the SQLite trace database in best-effort mode, migrate its schema,
97/// and spawn the background writer task.
98///
99/// Returns a [`TraceSender`] for the hot path and the writer's [`JoinHandle`]. The writer
100/// exits cleanly once every clone of the sender is dropped.
101///
102/// This is the backward-compatible convenience wrapper. Use [`open_with_receipts`] when you need
103/// durable (never-drop) mode.
104///
105/// # Errors
106/// Returns [`StoreError::Sqlite`] if the database cannot be opened or migrated.
107pub fn open(db_path: impl AsRef<Path>) -> Result<(TraceSender, JoinHandle<()>), StoreError> {
108    let (tx, _spill, handle) = open_with_receipts(db_path, ReceiptsMode::BestEffort)?;
109    Ok((tx, handle))
110}
111
112/// Open the SQLite trace database with the given receipts mode, migrate its schema, and spawn
113/// the background writer task.
114///
115/// Returns a [`TraceSender`], an optional [`SpillHandle`] (present only in durable mode, for use
116/// by [`append_to_spill`] on channel-full), and the writer's [`JoinHandle`].
117///
118/// In [`ReceiptsMode::Durable`] mode the writer drains any existing spill file at startup and
119/// whenever the channel empties, inserting spilled traces BEFORE new channel arrivals so the hash
120/// chain stays append-only and valid across crashes and restarts.
121///
122/// # Errors
123/// Returns [`StoreError::Sqlite`] if the database cannot be opened or migrated, or
124/// [`StoreError::Io`] if the spill file cannot be created in durable mode.
125pub fn open_with_receipts(
126    db_path: impl AsRef<Path>,
127    receipts_mode: ReceiptsMode,
128) -> Result<(TraceSender, Option<SpillHandle>, JoinHandle<()>), StoreError> {
129    let db_path = db_path.as_ref();
130    let conn = connect(db_path)?;
131    migrate(&conn)?;
132
133    let spill = if receipts_mode == ReceiptsMode::Durable {
134        let path = spill_path(db_path);
135        let file = std::fs::OpenOptions::new()
136            .create(true)
137            .append(true)
138            .read(true)
139            .open(&path)?;
140        Some(Arc::new(std::sync::Mutex::new(file)))
141    } else {
142        None
143    };
144
145    let (tx, rx) = mpsc::channel::<Trace>(TRACE_CHANNEL_CAP);
146    let spill_writer = spill.clone();
147    let handle = tokio::task::spawn_blocking(move || writer_loop(conn, rx, spill_writer));
148    Ok((tx, spill, handle))
149}
150
151fn migrate(conn: &Connection) -> Result<(), StoreError> {
152    // WAL: lets the background writer and short-lived feedback/read connections share the file
153    // concurrently. `journal_mode` is persisted in the file header once set by any connection.
154    conn.pragma_update(None, "journal_mode", "WAL")?;
155    conn.execute_batch(
156        "CREATE TABLE IF NOT EXISTS traces (
157            seq INTEGER PRIMARY KEY AUTOINCREMENT,
158            trace_id TEXT NOT NULL,
159            ts TEXT NOT NULL,
160            prev_hash TEXT NOT NULL,
161            hash TEXT NOT NULL,
162            tenant TEXT NOT NULL,
163            session TEXT NOT NULL,
164            body TEXT NOT NULL
165        );
166        CREATE INDEX IF NOT EXISTS traces_session_idx ON traces(session);
167        -- Tenant-scoped reads (ADR 0004 §D3) filter on `tenant`; index it with `seq` so a
168        -- per-tenant scan stays ordered and cheap. Existing rows keep their `tenant` value.
169        CREATE INDEX IF NOT EXISTS traces_tenant_seq_idx ON traces(tenant, seq);
170        -- Deferred verdicts live in their OWN table, keyed by trace_id. They are NEVER folded
171        -- into the sealed, hashed `traces.body`, so a late outcome can't alter a past record and
172        -- the tamper-evident chain stays valid. They are merged onto a trace only on read.
173        CREATE TABLE IF NOT EXISTS deferred_verdicts (
174            id INTEGER PRIMARY KEY AUTOINCREMENT,
175            trace_id TEXT NOT NULL,
176            gate_id TEXT NOT NULL,
177            verdict TEXT NOT NULL,
178            score REAL,
179            reported_at TEXT NOT NULL,
180            reporter TEXT NOT NULL
181        );
182        CREATE INDEX IF NOT EXISTS deferred_trace_idx ON deferred_verdicts(trace_id);
183        -- Paired-shadow experiment pairs (ADR 0014 Stage 2): one row per paired evaluation.
184        -- Gate verdicts from the incumbent (x_inc) and challenger (x_ch) are stored here so the
185        -- experiment can be rebuilt deterministically at startup or CLI replay: arrival order is
186        -- the row id, and replaying in that order yields the same e-process trajectory.
187        CREATE TABLE IF NOT EXISTS experiment_pairs (
188            id INTEGER PRIMARY KEY AUTOINCREMENT,
189            tenant TEXT NOT NULL,
190            route_ix INTEGER NOT NULL,
191            fingerprint TEXT NOT NULL,
192            trace_id TEXT NOT NULL,
193            x_inc REAL NOT NULL,
194            x_ch REAL NOT NULL,
195            challenger_cost_usd REAL NOT NULL,
196            ch_error INTEGER NOT NULL DEFAULT 0,
197            ch_truncated INTEGER NOT NULL DEFAULT 0,
198            ts TEXT NOT NULL,
199            -- DEFAULT 2 = InLogStatus::Unknown: a missed write surfaces as Unknown,
200            -- not as InLog (1) or NotInLog (0), so tests can detect missing explicit writes.
201            -- Production code always writes this field explicitly.
202            in_log INTEGER NOT NULL DEFAULT 2
203        );
204        CREATE INDEX IF NOT EXISTS exp_pairs_tenant_idx ON experiment_pairs(tenant, route_ix, fingerprint);",
205    )?;
206    // Idempotent migrations: each column is checked and added independently.
207    // PRAGMA table_info returns one row per column; column name is at index 1.
208    // SQLite does not support "ALTER TABLE … ADD COLUMN IF NOT EXISTS", so we check first.
209    let has_column = |name: &str| -> Result<bool, rusqlite::Error> {
210        let mut stmt = conn.prepare("PRAGMA table_info(experiment_pairs)")?;
211        let mut rows = stmt.query([])?;
212        while let Some(row) = rows.next()? {
213            let col: String = row.get(1)?;
214            if col == name {
215                return Ok(true);
216            }
217        }
218        Ok(false)
219    };
220    if !has_column("ch_error")? {
221        conn.execute_batch(
222            "ALTER TABLE experiment_pairs ADD COLUMN ch_error INTEGER NOT NULL DEFAULT 0;",
223        )?;
224    }
225    if !has_column("ch_truncated")? {
226        conn.execute_batch(
227            "ALTER TABLE experiment_pairs ADD COLUMN ch_truncated INTEGER NOT NULL DEFAULT 0;",
228        )?;
229    }
230    // Legacy rows (before the in_log column): stored as Unknown (2) because we cannot
231    // determine whether they reached a shared log. At startup with shared_log=true,
232    // Unknown rows contribute spend toward the cap (safe: stricter) but not evidence
233    // (safe: only delays a decision). With shared_log=false they replay normally.
234    if !has_column("in_log")? {
235        conn.execute_batch(
236            "ALTER TABLE experiment_pairs ADD COLUMN in_log INTEGER NOT NULL DEFAULT 2;",
237        )?;
238    }
239    Ok(())
240}
241
242/// Assign `prev_hash`, compute `hash`, and insert one trace. Updates `head` on success.
243/// Logs and skips on hash or insert failure — one bad record must not wedge the pipeline.
244fn write_trace(conn: &Connection, trace: &mut Trace, head: &mut String) {
245    trace.prev_hash = head.clone();
246    let hash = match trace.hash() {
247        Ok(h) => h,
248        Err(err) => {
249            tracing::error!(%err, trace_id = %trace.trace_id, "trace writer: hash failed, dropping");
250            return;
251        }
252    };
253    if let Err(err) = insert(conn, trace, &hash) {
254        tracing::error!(%err, trace_id = %trace.trace_id, "trace writer: insert failed, dropping");
255        return;
256    }
257    *head = hash;
258}
259
260/// Drain the spill file into the store, inserting traces in file order BEFORE any new channel
261/// arrivals. Truncates the file after a successful drain. Errors on individual lines are logged
262/// and skipped so one bad spill line can't stall the recovery.
263///
264/// ponytail: reads the entire file into memory; the spill file is bounded by the channel size
265/// and trace sizes, so this is fine in practice. Stream-parse if traces grow very large.
266fn drain_spill(conn: &Connection, spill: &SpillHandle, head: &mut String) {
267    // Hold the lock for the entire drain: prevents concurrent spill writers from appending
268    // new lines mid-drain, which would corrupt ordering.
269    let mut guard = spill.lock().unwrap_or_else(|e| e.into_inner());
270
271    // Seek to start before reading.
272    if let Err(e) = guard.seek(SeekFrom::Start(0)) {
273        tracing::error!(%e, "drain_spill: seek failed");
274        return;
275    }
276
277    // Collect all non-empty lines while holding the lock (reader borrows from guard).
278    let lines: Vec<String> = {
279        let reader = BufReader::new(&*guard);
280        reader
281            .lines()
282            .filter_map(|l| match l {
283                Ok(s) if !s.trim().is_empty() => Some(s),
284                Ok(_) => None,
285                Err(e) => {
286                    tracing::error!(%e, "drain_spill: read error");
287                    None
288                }
289            })
290            .collect()
291    };
292
293    if lines.is_empty() {
294        return;
295    }
296
297    let n = lines.len();
298    for line in &lines {
299        let mut trace: Trace = match serde_json::from_str(line) {
300            Ok(t) => t,
301            Err(e) => {
302                tracing::error!(%e, "drain_spill: bad JSON line, skipping");
303                continue;
304            }
305        };
306        write_trace(conn, &mut trace, head);
307    }
308    tracing::info!(drained = n, "spill file drained into trace store");
309
310    // Truncate: all lines have been processed (failures are skipped and logged above).
311    if let Err(e) = guard.seek(SeekFrom::Start(0)) {
312        tracing::error!(%e, "drain_spill: post-drain seek failed");
313        return;
314    }
315    if let Err(e) = guard.set_len(0) {
316        tracing::error!(%e, "drain_spill: truncate failed — spill file may replay on next boot");
317    }
318}
319
320/// The writer's main loop: runs on a blocking-pool thread for the lifetime of the store.
321/// Never panics on a bad trace — logs and drops it, so one malformed record can't wedge the
322/// whole audit pipeline.
323///
324/// In durable mode (`spill` is `Some`): drains the spill file at startup and whenever the
325/// channel empties, so spilled traces always land BEFORE later channel traces in the chain.
326fn writer_loop(conn: Connection, mut rx: mpsc::Receiver<Trace>, spill: Option<SpillHandle>) {
327    let mut head = match current_head(&conn) {
328        Ok(h) => h,
329        Err(err) => {
330            tracing::error!(%err, "trace writer: failed to load chain head, stopping");
331            return;
332        }
333    };
334
335    // Startup drain: recover any traces spilled during a previous run's backpressure.
336    if let Some(ref s) = spill {
337        drain_spill(&conn, s, &mut head);
338    }
339
340    loop {
341        let Some(mut trace) = rx.blocking_recv() else {
342            break;
343        };
344        write_trace(&conn, &mut trace, &mut head);
345
346        // Fast-drain: pull any immediately available channel items, then drain spill when
347        // the channel is momentarily empty so spilled traces land before the next wave.
348        loop {
349            match rx.try_recv() {
350                Ok(mut t) => write_trace(&conn, &mut t, &mut head),
351                Err(mpsc::error::TryRecvError::Empty) => {
352                    if let Some(ref s) = spill {
353                        drain_spill(&conn, s, &mut head);
354                    }
355                    break;
356                }
357                Err(mpsc::error::TryRecvError::Disconnected) => return,
358            }
359        }
360    }
361
362    // Channel closed: one final spill drain to capture any last-moment spills.
363    if let Some(ref s) = spill {
364        drain_spill(&conn, s, &mut head);
365    }
366}
367
368fn current_head(conn: &Connection) -> Result<String, StoreError> {
369    let mut stmt = conn.prepare("SELECT hash FROM traces ORDER BY seq DESC LIMIT 1")?;
370    let mut rows = stmt.query([])?;
371    match rows.next()? {
372        Some(row) => Ok(row.get(0)?),
373        None => Ok(GENESIS_HASH.to_owned()),
374    }
375}
376
377fn insert(conn: &Connection, trace: &Trace, hash: &str) -> Result<(), StoreError> {
378    let body = serde_json::to_string(trace)?;
379    conn.execute(
380        "INSERT INTO traces (trace_id, ts, prev_hash, hash, tenant, session, body)
381         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
382        rusqlite::params![
383            trace.trace_id.to_string(),
384            trace.ts.to_string(),
385            trace.prev_hash,
386            hash,
387            trace.tenant_id,
388            trace.session_id,
389            body,
390        ],
391    )?;
392    Ok(())
393}
394
395/// Load every trace from the database in insertion (chain) order — used by tests and
396/// operators to verify the hash chain with [`firstpass_core::verify_chain`].
397///
398/// **Operator-wide** read: every trace across ALL tenants, in `seq` order.
399///
400/// This deliberately crosses tenant boundaries and must stay reserved for operator-scoped work
401/// where a global view is intrinsic — namely verifying the single hash-chain, which spans every
402/// tenant's traces in one sequence (ADR 0004 §D3). For tenant-facing reads use
403/// [`load_tenant_traces`].
404///
405/// # Errors
406/// Returns [`StoreError::Sqlite`] on a database error, or [`StoreError::Json`] if a stored
407/// row is not valid trace JSON.
408pub fn load_all_traces(db_path: impl AsRef<Path>) -> Result<Vec<Trace>, StoreError> {
409    let conn = connect(db_path.as_ref())?;
410    let mut stmt = conn.prepare("SELECT body FROM traces ORDER BY seq ASC")?;
411    let rows = stmt.query_map([], |row| row.get::<_, String>(0))?;
412    let mut traces = Vec::new();
413    for row in rows {
414        traces.push(serde_json::from_str(&row?)?);
415    }
416    Ok(traces)
417}
418
419/// **Tenant-scoped** read: only traces owned by `tenant`, in `seq` order (ADR 0004 §D3). Tenant A
420/// can never see tenant B's traces through this path.
421///
422/// # Errors
423/// Returns [`StoreError::Sqlite`] on a database error, or [`StoreError::Json`] if a stored
424/// row is not valid trace JSON.
425pub fn load_tenant_traces(
426    db_path: impl AsRef<Path>,
427    tenant: &str,
428) -> Result<Vec<Trace>, StoreError> {
429    let conn = connect(db_path.as_ref())?;
430    let mut stmt = conn.prepare("SELECT body FROM traces WHERE tenant = ?1 ORDER BY seq ASC")?;
431    let rows = stmt.query_map([tenant], |row| row.get::<_, String>(0))?;
432    let mut traces = Vec::new();
433    for row in rows {
434        traces.push(serde_json::from_str(&row?)?);
435    }
436    Ok(traces)
437}
438
439/// **Tenant-scoped** page: the tenant's `limit` most recent traces, newest first. Reads only the
440/// rows it returns (the `(tenant, seq)` index serves the order), so a panel polling a long-lived
441/// store pays for one page, not for the whole history.
442///
443/// # Errors
444/// Returns [`StoreError::Sqlite`] on a database error, or [`StoreError::Json`] if a stored
445/// row is not valid trace JSON.
446pub fn load_recent_tenant_traces(
447    db_path: impl AsRef<Path>,
448    tenant: &str,
449    limit: usize,
450) -> Result<Vec<Trace>, StoreError> {
451    let conn = connect(db_path.as_ref())?;
452    let mut stmt =
453        conn.prepare("SELECT body FROM traces WHERE tenant = ?1 ORDER BY seq DESC LIMIT ?2")?;
454    let limit = i64::try_from(limit).unwrap_or(i64::MAX);
455    let rows = stmt.query_map(rusqlite::params![tenant, limit], |row| {
456        row.get::<_, String>(0)
457    })?;
458    let mut traces = Vec::new();
459    for row in rows {
460        traces.push(serde_json::from_str(&row?)?);
461    }
462    Ok(traces)
463}
464
465/// All-time totals over a tenant's receipts, for the panel's overview tiles.
466#[derive(Debug, Clone, Default, PartialEq)]
467pub struct ReceiptsSummary {
468    /// Number of receipts.
469    pub requests: u64,
470    /// Sum of `final.total_cost_usd`.
471    pub spent_usd: f64,
472    /// Sum of `final.counterfactual_baseline_usd`.
473    pub baseline_usd: f64,
474    /// Receipts served at rung 0.
475    pub first_pass: u64,
476    /// Receipts that climbed at least one rung.
477    pub escalated: u64,
478    /// Gate verdicts across every attempt: pass, fail, abstain.
479    pub gates: [u64; 3],
480    /// Attempts whose answer stopped at the output-token limit.
481    pub truncated: u64,
482}
483
484impl ReceiptsSummary {
485    fn merged(&self, d: &Self) -> Self {
486        Self {
487            requests: self.requests + d.requests,
488            spent_usd: self.spent_usd + d.spent_usd,
489            baseline_usd: self.baseline_usd + d.baseline_usd,
490            first_pass: self.first_pass + d.first_pass,
491            escalated: self.escalated + d.escalated,
492            gates: [
493                self.gates[0] + d.gates[0],
494                self.gates[1] + d.gates[1],
495                self.gates[2] + d.gates[2],
496            ],
497            truncated: self.truncated + d.truncated,
498        }
499    }
500}
501
502/// Per-(store, tenant) running totals and the last `seq` folded into them.
503type SummaryCache = std::collections::HashMap<(std::path::PathBuf, String), (i64, ReceiptsSummary)>;
504static SUMMARY_CACHE: std::sync::LazyLock<std::sync::Mutex<SummaryCache>> =
505    std::sync::LazyLock::new(Default::default);
506
507/// **Tenant-scoped** aggregate over every stored receipt, computed inside SQLite and kept
508/// incrementally.
509///
510/// The panel polls this every two seconds, so it must not re-read the whole history each time.
511/// Receipts are append-only (the hash chain forbids rewriting one), so the totals up to a `seq`
512/// never change: each call folds in only the rows after the last `seq` it saw. A store whose
513/// newest `seq` is below the cached one was replaced, and is recounted from zero.
514///
515/// # Errors
516/// Returns [`StoreError::Sqlite`] on a database error.
517pub fn tenant_receipts_summary(
518    db_path: impl AsRef<Path>,
519    tenant: &str,
520) -> Result<ReceiptsSummary, StoreError> {
521    let conn = connect(db_path.as_ref())?;
522    let key = (db_path.as_ref().to_path_buf(), tenant.to_owned());
523    let hi: i64 = conn.query_row(
524        "SELECT coalesce(max(seq), 0) FROM traces WHERE tenant = ?1",
525        [tenant],
526        |r| r.get(0),
527    )?;
528    let cached = SUMMARY_CACHE
529        .lock()
530        .ok()
531        .and_then(|c| c.get(&key).cloned())
532        .filter(|(seen, _)| *seen <= hi);
533    let (after, base) = cached.unwrap_or_default();
534    if after == hi {
535        return Ok(base);
536    }
537    let total = base.merged(&summary_between(&conn, tenant, after, hi)?);
538    if let Ok(mut c) = SUMMARY_CACHE.lock() {
539        // Bounded: dropping the cache only costs one full recount per tenant, never correctness.
540        if c.len() >= 4096 {
541            c.clear();
542        }
543        c.insert(key, (hi, total.clone()));
544    }
545    Ok(total)
546}
547
548/// Totals over the tenant's rows with `after < seq <= hi`. Both queries share the same upper
549/// bound, so a write landing between them cannot be counted in one and not the other.
550fn summary_between(
551    conn: &Connection,
552    tenant: &str,
553    after: i64,
554    hi: i64,
555) -> Result<ReceiptsSummary, StoreError> {
556    let mut s = conn.query_row(
557        "SELECT count(*),
558                total(json_extract(body, '$.final.total_cost_usd')),
559                total(json_extract(body, '$.final.counterfactual_baseline_usd')),
560                coalesce(sum(json_extract(body, '$.final.served_rung') = 0), 0),
561                coalesce(sum(json_extract(body, '$.final.escalations') > 0), 0)
562         FROM traces WHERE tenant = ?1 AND seq > ?2 AND seq <= ?3",
563        rusqlite::params![tenant, after, hi],
564        |r| {
565            Ok(ReceiptsSummary {
566                requests: u64::try_from(r.get::<_, i64>(0)?).unwrap_or(0),
567                spent_usd: r.get(1)?,
568                baseline_usd: r.get(2)?,
569                first_pass: u64::try_from(r.get::<_, i64>(3)?).unwrap_or(0),
570                escalated: u64::try_from(r.get::<_, i64>(4)?).unwrap_or(0),
571                gates: [0; 3],
572                truncated: 0,
573            })
574        },
575    )?;
576    let truncated: i64 = conn.query_row(
577        "SELECT count(*) FROM traces t, json_each(t.body, '$.attempts') a
578         WHERE t.tenant = ?1 AND t.seq > ?2 AND t.seq <= ?3
579           AND json_extract(a.value, '$.truncated') = 1",
580        rusqlite::params![tenant, after, hi],
581        |r| r.get(0),
582    )?;
583    s.truncated = u64::try_from(truncated).unwrap_or(0);
584    let mut stmt = conn.prepare(
585        "SELECT json_extract(g.value, '$.verdict'), count(*)
586         FROM traces t, json_each(t.body, '$.attempts') a, json_each(a.value, '$.gates') g
587         WHERE t.tenant = ?1 AND t.seq > ?2 AND t.seq <= ?3 GROUP BY 1",
588    )?;
589    let rows = stmt.query_map(rusqlite::params![tenant, after, hi], |r| {
590        Ok((r.get::<_, Option<String>>(0)?, r.get::<_, i64>(1)?))
591    })?;
592    for row in rows {
593        let (verdict, n) = row?;
594        let slot = match verdict.as_deref() {
595            Some("pass") => 0,
596            Some("fail") => 1,
597            Some("abstain") => 2,
598            _ => continue,
599        };
600        s.gates[slot] += u64::try_from(n).unwrap_or(0);
601    }
602    Ok(s)
603}
604
605/// Whether a trace with `trace_id` exists **and is owned by `tenant`** — used to reject feedback
606/// for unknown traces and, crucially, to deny cross-tenant feedback (ADR 0004 §D3/§D4). A trace
607/// owned by another tenant is indistinguishable from a non-existent one here, so the caller can
608/// return a `404` with no existence oracle.
609///
610/// # Errors
611/// Returns [`StoreError::Sqlite`] on a database error.
612pub fn trace_exists(
613    db_path: impl AsRef<Path>,
614    tenant: &str,
615    trace_id: &str,
616) -> Result<bool, StoreError> {
617    let conn = connect(db_path.as_ref())?;
618    let n: i64 = conn.query_row(
619        "SELECT COUNT(1) FROM traces WHERE tenant = ?1 AND trace_id = ?2",
620        [tenant, trace_id],
621        |row| row.get(0),
622    )?;
623    Ok(n > 0)
624}
625
626/// The route index recorded on `trace_id`, scoped to `tenant`.
627///
628/// `Ok(None)` means the trace is not visible to this tenant (or does not exist) — the same
629/// ownership check [`trace_exists`] makes, so this cannot be used to probe another tenant's
630/// traces. `Ok(Some(None))` means the trace exists but predates route recording.
631///
632/// # Errors
633/// On a store read failure.
634/// The decision a trace's answer was originally proven by, if it was itself a cache replay.
635///
636/// A cache hit is recorded under a NEW trace id, with `cache_source` naming the decision whose gate
637/// passed. So feedback arriving on a replay must be applied to that source, not to the replay's own
638/// id — otherwise a Fail reported against a replayed answer retracts nothing and the disproven
639/// entry keeps serving until TTL.
640///
641/// Returns `Ok(None)` for an unknown trace, `Ok(Some(None))` for one that ran the ladder itself.
642///
643/// # Errors
644/// [`StoreError`] when the database cannot be opened or queried.
645pub fn trace_cache_source(
646    db_path: impl AsRef<Path>,
647    tenant: &str,
648    trace_id: &str,
649) -> Result<Option<Option<uuid::Uuid>>, StoreError> {
650    let conn = connect(db_path.as_ref())?;
651    let body: Option<String> = conn
652        .query_row(
653            "SELECT body FROM traces WHERE tenant = ?1 AND trace_id = ?2",
654            [tenant, trace_id],
655            |row| row.get(0),
656        )
657        .optional()?;
658    Ok(body.map(|b: String| {
659        serde_json::from_str::<firstpass_core::Trace>(&b)
660            .ok()
661            .and_then(|t| t.final_.cache_source)
662    }))
663}
664
665pub fn trace_route(
666    db_path: impl AsRef<Path>,
667    tenant: &str,
668    trace_id: &str,
669) -> Result<Option<Option<u32>>, StoreError> {
670    let conn = connect(db_path.as_ref())?;
671    let body: Option<String> = conn
672        .query_row(
673            "SELECT body FROM traces WHERE tenant = ?1 AND trace_id = ?2",
674            [tenant, trace_id],
675            |row| row.get(0),
676        )
677        .optional()?;
678    Ok(body.map(|b: String| {
679        serde_json::from_str::<firstpass_core::Trace>(&b)
680            .ok()
681            .and_then(|t| t.route_ix)
682    }))
683}
684
685/// Append a deferred verdict for `trace_id` (a downstream outcome or async gate result). This
686/// writes ONLY to the `deferred_verdicts` table; the sealed trace and its hash are untouched, so
687/// the audit chain remains verifiable (SPEC §8.3.4 — the outcome-feedback loop).
688///
689/// # Errors
690/// Returns [`StoreError::Sqlite`] on a database error.
691pub fn append_deferred(
692    db_path: impl AsRef<Path>,
693    trace_id: &str,
694    v: &DeferredVerdict,
695) -> Result<(), StoreError> {
696    let conn = connect(db_path.as_ref())?;
697    conn.execute(
698        "INSERT INTO deferred_verdicts (trace_id, gate_id, verdict, score, reported_at, reporter)
699         VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
700        rusqlite::params![
701            trace_id,
702            v.gate_id,
703            v.verdict.as_str(),
704            v.score.map(Score::value),
705            v.reported_at.to_string(),
706            v.reporter,
707        ],
708    )?;
709    Ok(())
710}
711
712/// Load the deferred verdicts recorded for `trace_id`, oldest first. Malformed stored rows are
713/// skipped (logged), never fatal — a corrupt late outcome must not break reading the trace.
714///
715/// # Errors
716/// Returns [`StoreError::Sqlite`] on a database error.
717pub fn load_deferred(
718    db_path: impl AsRef<Path>,
719    trace_id: &str,
720) -> Result<Vec<DeferredVerdict>, StoreError> {
721    let conn = connect(db_path.as_ref())?;
722    let mut stmt = conn.prepare(
723        "SELECT gate_id, verdict, score, reported_at, reporter
724         FROM deferred_verdicts WHERE trace_id = ?1 ORDER BY id ASC",
725    )?;
726    let rows = stmt.query_map([trace_id], |row| {
727        Ok((
728            row.get::<_, String>(0)?,
729            row.get::<_, String>(1)?,
730            row.get::<_, Option<f64>>(2)?,
731            row.get::<_, String>(3)?,
732            row.get::<_, String>(4)?,
733        ))
734    })?;
735
736    let mut out = Vec::new();
737    for row in rows {
738        let (gate_id, verdict_s, score, reported_s, reporter) = row?;
739        let verdict = match verdict_s.as_str() {
740            "pass" => Verdict::Pass,
741            "fail" => Verdict::Fail,
742            "abstain" => Verdict::Abstain,
743            other => {
744                tracing::warn!(verdict = %other, %trace_id, "skipping deferred row with bad verdict");
745                continue;
746            }
747        };
748        let Ok(reported_at) = reported_s.parse::<jiff::Timestamp>() else {
749            tracing::warn!(%trace_id, "skipping deferred row with bad timestamp");
750            continue;
751        };
752        out.push(DeferredVerdict {
753            gate_id,
754            verdict,
755            score: score.and_then(|s| Score::new(s).ok()),
756            reported_at,
757            reporter,
758        });
759    }
760    Ok(out)
761}
762
763/// Every deferred verdict in the store as `(trace_id, verdict)`, in the order the feedback
764/// arrived. **Operator-wide**: rows are not tenant-scoped here, so callers must join them to
765/// tenant-scoped traces (an id that matches no loaded trace is simply ignored). Rows with an
766/// unparseable id or verdict are skipped, as in [`load_deferred`].
767///
768/// # Errors
769/// Returns [`StoreError::Sqlite`] on a database error.
770pub fn load_all_deferred(
771    db_path: impl AsRef<Path>,
772) -> Result<Vec<(uuid::Uuid, Verdict)>, StoreError> {
773    let conn = connect(db_path.as_ref())?;
774    let mut stmt =
775        conn.prepare("SELECT trace_id, verdict FROM deferred_verdicts ORDER BY id ASC")?;
776    let rows = stmt.query_map([], |row| {
777        Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))
778    })?;
779    let mut out = Vec::new();
780    for row in rows {
781        let (id, verdict) = row?;
782        let verdict = match verdict.as_str() {
783            "pass" => Verdict::Pass,
784            "fail" => Verdict::Fail,
785            "abstain" => Verdict::Abstain,
786            _ => continue,
787        };
788        if let Ok(id) = id.parse::<uuid::Uuid>() {
789            out.push((id, verdict));
790        }
791    }
792    Ok(out)
793}
794
795/// What the feedback path needs to attribute an outcome to a certified-selection experiment:
796/// the trace's session, its route, and its experiment record. `Ok(None)` when the trace is not
797/// visible to `tenant`, or carries no experiment record. Tenant-scoped like [`trace_route`].
798///
799/// # Errors
800/// On a store read failure.
801pub fn trace_experiment(
802    db_path: impl AsRef<Path>,
803    tenant: &str,
804    trace_id: &str,
805) -> Result<Option<(String, usize, firstpass_core::trace::ExperimentRecord, f64)>, StoreError> {
806    let conn = connect(db_path.as_ref())?;
807    let body: Option<String> = conn
808        .query_row(
809            "SELECT body FROM traces WHERE tenant = ?1 AND trace_id = ?2",
810            [tenant, trace_id],
811            |row| row.get(0),
812        )
813        .optional()?;
814    let Some(body) = body else { return Ok(None) };
815    let trace: Trace = serde_json::from_str(&body)?;
816    let cost_usd = trace.final_.total_cost_usd;
817    Ok(trace.experiment.map(|rec| {
818        (
819            trace.session_id,
820            // Same fallback as the guardrail: a trace with no recorded route is route 0.
821            trace.route_ix.map_or(0, |r| r as usize),
822            rec,
823            cost_usd,
824        )
825    }))
826}
827
828/// Load a single trace by id **scoped to `tenant`**, with its deferred verdicts merged into
829/// `deferred` — the **view** for display/inspection (ADR 0004 §D3). A trace owned by another
830/// tenant returns `None`, exactly like a missing one, so an inspecting agent can never read across
831/// tenants. This is deliberately separate from [`load_all_traces`]: merging deferred verdicts
832/// changes the record, so a merged trace must NOT be fed to `verify_chain` (chain verification
833/// always runs on the sealed bodies from [`load_all_traces`]).
834///
835/// # Errors
836/// Returns [`StoreError::Sqlite`] / [`StoreError::Json`] on database or decode errors.
837pub fn load_trace_view(
838    db_path: impl AsRef<Path>,
839    tenant: &str,
840    trace_id: &str,
841) -> Result<Option<Trace>, StoreError> {
842    let conn = connect(db_path.as_ref())?;
843    let body: Option<String> = conn
844        .query_row(
845            "SELECT body FROM traces WHERE tenant = ?1 AND trace_id = ?2",
846            [tenant, trace_id],
847            |row| row.get(0),
848        )
849        .ok();
850    let Some(body) = body else { return Ok(None) };
851    let mut trace: Trace = serde_json::from_str(&body)?;
852    trace.deferred = load_deferred(db_path, trace_id)?;
853    Ok(Some(trace))
854}
855
856/// Whether a paired-mode row's evidence was successfully pushed to the shared experiment log.
857///
858/// This is a tri-state because we can have three situations at record time:
859/// - `NotInLog` (0): the shared log append failed (Redis outage); the row is local-only and
860///   must be replayed locally on restart with a shared log configured.
861/// - `InLog` (1): the append succeeded; the record lives in Redis and will be re-applied by
862///   `sync_from_backend` on restart — do not replay evidence from SQLite.
863/// - `Unknown` (2): legacy row from before this column was added, or written in single-replica
864///   mode before a shared log was attached; cannot determine where it lives.
865///
866/// ## Restart rules
867/// With a shared log:
868///   - `NotInLog` rows → replay spend AND evidence locally (came from an outage).
869///   - `InLog` rows → replay neither; they arrive from the log on the first sync.
870///   - `Unknown` rows → replay spend only (count toward cap but withhold evidence);
871///     double-counting spend only makes the cap stricter (safe), withholding evidence
872///     only delays a decision (safe).
873///
874/// Without a shared log: all rows → replay everything (original behaviour).
875#[derive(Debug, Clone, Copy, PartialEq, Eq)]
876pub enum InLogStatus {
877    /// Never reached the shared log (written during an outage or when no log was configured).
878    NotInLog = 0,
879    /// Successfully appended to the shared log at record time.
880    InLog = 1,
881    /// Legacy row: cannot determine whether it was in the log.
882    Unknown = 2,
883}
884
885impl InLogStatus {
886    fn from_i64(v: i64) -> Self {
887        match v {
888            0 => Self::NotInLog,
889            1 => Self::InLog,
890            _ => Self::Unknown,
891        }
892    }
893}
894
895/// One row from the `experiment_pairs` table.
896///
897/// Used to rebuild paired-mode experiments at startup and in the CLI, so the e-process trajectory
898/// is identical whether derived live or replayed. Arrival order = row `id` (insertion order).
899#[derive(Debug, Clone, PartialEq)]
900pub struct ExperimentPairRow {
901    /// Tenant that owns this pair.
902    pub tenant: String,
903    /// Route index the challenger belongs to.
904    pub route_ix: usize,
905    /// Fingerprint of the experiment config (incumbent + challenger ladders, margin, alpha).
906    pub fingerprint: String,
907    /// Trace id of the challenger request that produced this pair (used for cross-source dedup).
908    pub trace_id: uuid::Uuid,
909    /// Incumbent gate verdict: 1.0 = gate passed, 0.0 = gate failed / best-attempt.
910    pub x_inc: f64,
911    /// Challenger gate verdict: 1.0 = gate passed, 0.0 = gate failed.
912    pub x_ch: f64,
913    /// USD spent by the challenger on this pair.
914    pub challenger_cost_usd: f64,
915    /// Whether the challenger call ended in an error with no output.
916    pub ch_error: bool,
917    /// Number of challenger attempts whose answer was cut off at the output-token limit.
918    pub ch_truncated: u64,
919    /// When the pair was recorded.
920    pub ts: jiff::Timestamp,
921    /// Whether this row's evidence reached the shared log. See [`InLogStatus`].
922    pub in_log: InLogStatus,
923}
924
925/// Append one experiment pair to the `experiment_pairs` table.
926///
927/// # Errors
928/// Returns [`StoreError::Sqlite`] on a database write failure.
929pub fn append_experiment_pair(
930    db_path: impl AsRef<std::path::Path>,
931    row: &ExperimentPairRow,
932) -> Result<(), StoreError> {
933    let conn = connect(db_path.as_ref())?;
934    conn.execute(
935        "INSERT INTO experiment_pairs \
936         (tenant, route_ix, fingerprint, trace_id, x_inc, x_ch, challenger_cost_usd, \
937          ch_error, ch_truncated, ts, in_log) \
938         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)",
939        rusqlite::params![
940            row.tenant,
941            row.route_ix as i64,
942            row.fingerprint,
943            row.trace_id.to_string(),
944            row.x_inc,
945            row.x_ch,
946            row.challenger_cost_usd,
947            i64::from(row.ch_error),
948            row.ch_truncated as i64,
949            row.ts.to_string(),
950            row.in_log as i64,
951        ],
952    )?;
953    Ok(())
954}
955
956/// Load every experiment pair in insertion order. Used at startup (rebuild) and CLI replay.
957///
958/// Rows with an unparseable `trace_id` or `ts` are skipped with a warning rather than failing the
959/// read — a single corrupt row must not prevent an otherwise healthy experiment from being restored.
960///
961/// # Errors
962/// Returns [`StoreError::Sqlite`] on a database read failure.
963pub fn load_all_experiment_pairs(
964    db_path: impl AsRef<std::path::Path>,
965) -> Result<Vec<ExperimentPairRow>, StoreError> {
966    let conn = connect(db_path.as_ref())?;
967    let mut stmt = conn.prepare(
968        "SELECT tenant, route_ix, fingerprint, trace_id, x_inc, x_ch, challenger_cost_usd, \
969                ch_error, ch_truncated, ts, in_log \
970         FROM experiment_pairs ORDER BY id ASC",
971    )?;
972    let rows = stmt.query_map([], |row| {
973        Ok((
974            row.get::<_, String>(0)?,
975            row.get::<_, i64>(1)?,
976            row.get::<_, String>(2)?,
977            row.get::<_, String>(3)?,
978            row.get::<_, f64>(4)?,
979            row.get::<_, f64>(5)?,
980            row.get::<_, f64>(6)?,
981            row.get::<_, i64>(7)?,
982            row.get::<_, i64>(8)?,
983            row.get::<_, String>(9)?,
984            row.get::<_, i64>(10)?,
985        ))
986    })?;
987    let mut out = Vec::new();
988    for row in rows {
989        let (
990            tenant,
991            route_ix,
992            fingerprint,
993            trace_id_s,
994            x_inc,
995            x_ch,
996            ch_cost,
997            ch_error_i,
998            ch_truncated_i,
999            ts_s,
1000            in_log_i,
1001        ) = row?;
1002        let Ok(trace_id) = trace_id_s.parse::<uuid::Uuid>() else {
1003            tracing::warn!(
1004                %trace_id_s,
1005                "experiment_pairs: skipping row with unparseable trace_id"
1006            );
1007            continue;
1008        };
1009        let Ok(ts) = ts_s.parse::<jiff::Timestamp>() else {
1010            tracing::warn!(
1011                %trace_id_s,
1012                "experiment_pairs: skipping row with unparseable ts"
1013            );
1014            continue;
1015        };
1016        out.push(ExperimentPairRow {
1017            tenant,
1018            route_ix: route_ix as usize,
1019            fingerprint,
1020            trace_id,
1021            x_inc,
1022            x_ch,
1023            challenger_cost_usd: ch_cost,
1024            ch_error: ch_error_i != 0,
1025            ch_truncated: u64::try_from(ch_truncated_i).unwrap_or(0),
1026            ts,
1027            in_log: InLogStatus::from_i64(in_log_i),
1028        });
1029    }
1030    Ok(out)
1031}
1032
1033/// Open the database, run all schema migrations, and close.
1034///
1035/// Use this when you need the schema to be up to date (e.g. before calling
1036/// [`load_all_experiment_pairs`] in a context where the store was not opened via [`open`]),
1037/// without spawning a background writer task.
1038///
1039/// # Errors
1040/// Returns [`StoreError::Sqlite`] if the database cannot be opened or migrated.
1041pub fn ensure_migrated(db_path: impl AsRef<Path>) -> Result<(), StoreError> {
1042    let conn = connect(db_path)?;
1043    migrate(&conn)
1044}
1045
1046#[cfg(test)]
1047mod tests {
1048    use firstpass_core::{
1049        Attempt, Features, FinalOutcome, GENESIS_HASH, PolicyRef, RequestInfo, ServedFrom,
1050        TaskKind, Verdict, verify_chain,
1051    };
1052
1053    use super::*;
1054
1055    fn sample_trace(tenant: &str, session: &str) -> Trace {
1056        let attempt = Attempt {
1057            rung: 0,
1058            model: "claude-haiku-4-5".to_owned(),
1059            provider: "anthropic".to_owned(),
1060            in_tokens: 10,
1061            cache_write_tokens: 0,
1062            cache_read_tokens: 0,
1063            out_tokens: 5,
1064            cost_usd: 0.001,
1065            latency_ms: 12,
1066            gates: vec![],
1067            verdict: Verdict::Pass,
1068            truncated: false,
1069        };
1070        let mut trace = Trace {
1071            trace_id: uuid::Uuid::now_v7(),
1072            prev_hash: GENESIS_HASH.to_owned(),
1073            tenant_id: tenant.to_owned(),
1074            session_id: session.to_owned(),
1075            ts: jiff::Timestamp::now(),
1076            mode: firstpass_core::Mode::Observe,
1077            policy: PolicyRef {
1078                id: "observe-passthrough@v0".to_owned(),
1079                explore: false,
1080                propensity: None,
1081                mode_profile: None,
1082            },
1083            request: RequestInfo {
1084                api: "anthropic.messages".to_owned(),
1085                prompt_hash: "deadbeef".to_owned(),
1086                features: Features::new(TaskKind::Other),
1087            },
1088            attempts: vec![attempt],
1089            deferred: Vec::new(),
1090            final_: FinalOutcome {
1091                served_rung: Some(0),
1092                served_from: ServedFrom::Attempt,
1093                total_cost_usd: 0.001,
1094                gate_cost_usd: 0.0,
1095                total_latency_ms: 12,
1096                escalations: 0,
1097                counterfactual_baseline_usd: 0.001,
1098                savings_usd: 0.0,
1099                cache_source: None,
1100            },
1101            probe: None,
1102            rollout: None,
1103            shadow: None,
1104            route_ix: None,
1105            predicted_pass: None,
1106            decision_prior: None,
1107            elastic: None,
1108            risk: None,
1109            experiment: None,
1110            upstream_provider: String::new(),
1111        };
1112        trace.recompute_savings();
1113        trace
1114    }
1115
1116    /// The SQL aggregate must equal a plain Rust fold over the same receipts, including traces
1117    /// with no served rung, escalations, mixed gate verdicts, and another tenant's rows mixed in.
1118    /// The page must be the newest `limit` rows of this tenant only, newest first.
1119    #[tokio::test]
1120    async fn summary_aggregate_matches_a_rust_fold_and_page_is_newest_first() {
1121        use firstpass_core::GateResult;
1122        let db_path =
1123            std::env::temp_dir().join(format!("firstpass-store-sum-{}.db", uuid::Uuid::now_v7()));
1124        let gate = |v: Verdict| GateResult {
1125            gate_id: "tests".to_owned(),
1126            verdict: v,
1127            score: None,
1128            cost_usd: 0.0,
1129            ms: 1,
1130            reason: None,
1131            evidence_ref: None,
1132        };
1133        let mut mine = Vec::new();
1134        for i in 0..23u32 {
1135            let mut t = sample_trace("tenant-a", &format!("s{i}"));
1136            t.final_.total_cost_usd = 0.01 * f64::from(i + 1);
1137            t.final_.counterfactual_baseline_usd = 0.05;
1138            t.final_.escalations = i % 3;
1139            t.final_.served_rung = match i % 5 {
1140                0 => None,
1141                k => Some(k % 3), // rung 0, 1 and 2 in unequal counts
1142            };
1143            t.attempts[0].gates = (0..i % 4)
1144                .map(|k| {
1145                    gate([Verdict::Pass, Verdict::Fail, Verdict::Abstain][(k + i) as usize % 3])
1146                })
1147                .collect();
1148            t.attempts[0].truncated = i % 3 == 0;
1149            if i % 2 == 0 {
1150                let mut second = t.attempts[0].clone();
1151                second.rung = 1;
1152                second.gates = vec![gate(Verdict::Pass)];
1153                second.truncated = i % 4 == 0;
1154                t.attempts.push(second);
1155            }
1156            mine.push(t);
1157        }
1158        let (tx, handle) = open(&db_path).unwrap();
1159        for (i, t) in mine.iter().enumerate() {
1160            tx.send(t.clone()).await.unwrap();
1161            if i % 4 == 0 {
1162                let mut other = sample_trace("tenant-b", "x");
1163                other.final_.total_cost_usd = 100.0;
1164                other.attempts[0].gates = vec![gate(Verdict::Fail)];
1165                other.attempts[0].truncated = true;
1166                tx.send(other).await.unwrap();
1167            }
1168        }
1169        drop(tx);
1170        handle.await.unwrap();
1171
1172        let mut want = ReceiptsSummary {
1173            requests: mine.len() as u64,
1174            ..Default::default()
1175        };
1176        for t in &mine {
1177            want.spent_usd += t.final_.total_cost_usd;
1178            want.baseline_usd += t.final_.counterfactual_baseline_usd;
1179            want.first_pass += u64::from(t.final_.served_rung == Some(0));
1180            want.escalated += u64::from(t.final_.escalations > 0);
1181            want.truncated += t.attempts.iter().filter(|a| a.truncated).count() as u64;
1182            for g in t.attempts.iter().flat_map(|a| &a.gates) {
1183                want.gates[match g.verdict {
1184                    Verdict::Pass => 0,
1185                    Verdict::Fail => 1,
1186                    Verdict::Abstain => 2,
1187                }] += 1;
1188            }
1189        }
1190        let got = tenant_receipts_summary(&db_path, "tenant-a").unwrap();
1191        assert_eq!(got.requests, want.requests);
1192        assert_eq!(got.first_pass, want.first_pass);
1193        assert_eq!(got.escalated, want.escalated);
1194        assert_eq!(got.gates, want.gates);
1195        assert_eq!(
1196            got.truncated, want.truncated,
1197            "only this tenant's cut-off attempts"
1198        );
1199        assert!(want.truncated > 0);
1200        assert!(want.first_pass > 0 && want.escalated > 0 && want.gates.iter().all(|&n| n > 0));
1201        assert!(
1202            (got.spent_usd - want.spent_usd).abs() < 1e-9,
1203            "{got:?} {want:?}"
1204        );
1205        assert!((got.baseline_usd - want.baseline_usd).abs() < 1e-9);
1206        assert_eq!(
1207            tenant_receipts_summary(&db_path, "nobody").unwrap(),
1208            ReceiptsSummary::default()
1209        );
1210
1211        let page = load_recent_tenant_traces(&db_path, "tenant-a", 5).unwrap();
1212        let ids: Vec<_> = page.iter().map(|t| t.trace_id).collect();
1213        let newest: Vec<_> = mine.iter().rev().take(5).map(|t| t.trace_id).collect();
1214        assert_eq!(ids, newest);
1215
1216        // Incremental: rows appended after a read are folded in exactly once.
1217        let (tx, handle) = open(&db_path).unwrap();
1218        for _ in 0..4 {
1219            let mut t = sample_trace("tenant-a", "late");
1220            t.final_.served_rung = Some(0);
1221            t.attempts[0].gates = vec![gate(Verdict::Abstain)];
1222            tx.send(t).await.unwrap();
1223        }
1224        drop(tx);
1225        handle.await.unwrap();
1226        let again = tenant_receipts_summary(&db_path, "tenant-a").unwrap();
1227        assert_eq!(again.requests, want.requests + 4);
1228        assert_eq!(again.first_pass, want.first_pass + 4);
1229        assert_eq!(
1230            again.gates,
1231            [want.gates[0], want.gates[1], want.gates[2] + 4]
1232        );
1233        assert_eq!(
1234            tenant_receipts_summary(&db_path, "tenant-a").unwrap(),
1235            again
1236        );
1237
1238        // A store replaced at the same path (newest seq below the cached one) is recounted.
1239        let _ = std::fs::remove_file(&db_path);
1240        let (tx, handle) = open(&db_path).unwrap();
1241        tx.send(sample_trace("tenant-a", "fresh")).await.unwrap();
1242        drop(tx);
1243        handle.await.unwrap();
1244        assert_eq!(
1245            tenant_receipts_summary(&db_path, "tenant-a")
1246                .unwrap()
1247                .requests,
1248            1
1249        );
1250        let _ = std::fs::remove_file(&db_path);
1251    }
1252
1253    #[tokio::test]
1254    async fn writer_assigns_prev_hash_and_forms_a_valid_chain() {
1255        let db_path =
1256            std::env::temp_dir().join(format!("firstpass-store-test-{}.db", uuid::Uuid::now_v7()));
1257        let (tx, handle) = open(&db_path).unwrap();
1258
1259        tx.try_send(sample_trace("tenant-a", "session-1")).unwrap();
1260        tx.try_send(sample_trace("tenant-a", "session-1")).unwrap();
1261        drop(tx);
1262        handle.await.unwrap();
1263
1264        let traces = load_all_traces(&db_path).unwrap();
1265        assert_eq!(traces.len(), 2);
1266        assert_eq!(traces[0].prev_hash, GENESIS_HASH);
1267        assert_eq!(traces[1].prev_hash, traces[0].hash().unwrap());
1268        verify_chain(&traces, GENESIS_HASH).unwrap();
1269
1270        let _ = std::fs::remove_file(&db_path);
1271    }
1272
1273    /// D7 cross-tenant isolation, at the store layer: with rows for tenants A and B, every
1274    /// tenant-scoped read for A returns only A's data, and vice-versa. The operator-wide
1275    /// [`load_all_traces`] still sees both (for chain verification).
1276    #[tokio::test]
1277    async fn tenant_scoped_reads_never_cross_the_boundary() {
1278        let db_path =
1279            std::env::temp_dir().join(format!("firstpass-isolation-{}.db", uuid::Uuid::now_v7()));
1280        let (tx, handle) = open(&db_path).unwrap();
1281
1282        // Two traces for A, one for B.
1283        let a0 = sample_trace("tenant-a", "sa-0");
1284        let a1 = sample_trace("tenant-a", "sa-1");
1285        let b0 = sample_trace("tenant-b", "sb-0");
1286        let (a0_id, a1_id, b0_id) = (
1287            a0.trace_id.to_string(),
1288            a1.trace_id.to_string(),
1289            b0.trace_id.to_string(),
1290        );
1291        tx.try_send(a0).unwrap();
1292        tx.try_send(b0).unwrap();
1293        tx.try_send(a1).unwrap();
1294        drop(tx);
1295        handle.await.unwrap();
1296
1297        // Scoped list: A sees exactly its two, B sees exactly its one.
1298        let a_traces = load_tenant_traces(&db_path, "tenant-a").unwrap();
1299        assert_eq!(a_traces.len(), 2, "A must see only A's traces");
1300        assert!(a_traces.iter().all(|t| t.tenant_id == "tenant-a"));
1301        let b_traces = load_tenant_traces(&db_path, "tenant-b").unwrap();
1302        assert_eq!(b_traces.len(), 1, "B must see only B's trace");
1303        assert!(b_traces.iter().all(|t| t.tenant_id == "tenant-b"));
1304
1305        // A can prove its own trace exists but cannot see B's, and vice-versa.
1306        assert!(trace_exists(&db_path, "tenant-a", &a0_id).unwrap());
1307        assert!(!trace_exists(&db_path, "tenant-a", &b0_id).unwrap());
1308        assert!(trace_exists(&db_path, "tenant-b", &b0_id).unwrap());
1309        assert!(!trace_exists(&db_path, "tenant-b", &a1_id).unwrap());
1310
1311        // The view is likewise scoped: cross-tenant reads are indistinguishable from a miss.
1312        assert!(
1313            load_trace_view(&db_path, "tenant-a", &a0_id)
1314                .unwrap()
1315                .is_some()
1316        );
1317        assert!(
1318            load_trace_view(&db_path, "tenant-a", &b0_id)
1319                .unwrap()
1320                .is_none()
1321        );
1322        assert!(
1323            load_trace_view(&db_path, "tenant-b", &a0_id)
1324                .unwrap()
1325                .is_none()
1326        );
1327
1328        // A tenant that owns nothing sees nothing.
1329        assert!(load_tenant_traces(&db_path, "ghost").unwrap().is_empty());
1330
1331        // Operator-wide read still spans both, and the global chain stays valid.
1332        let all = load_all_traces(&db_path).unwrap();
1333        assert_eq!(all.len(), 3);
1334        verify_chain(&all, GENESIS_HASH).unwrap();
1335
1336        let _ = std::fs::remove_file(&db_path);
1337    }
1338
1339    #[tokio::test]
1340    async fn deferred_verdicts_attach_on_read_without_breaking_the_chain() {
1341        let db_path = std::env::temp_dir().join(format!(
1342            "firstpass-deferred-test-{}.db",
1343            uuid::Uuid::now_v7()
1344        ));
1345        let (tx, handle) = open(&db_path).unwrap();
1346        let t0 = sample_trace("acme", "run-1");
1347        let t1 = sample_trace("acme", "run-1");
1348        let (id0, id1) = (t0.trace_id.to_string(), t1.trace_id.to_string());
1349        tx.try_send(t0).unwrap();
1350        tx.try_send(t1).unwrap();
1351        drop(tx);
1352        handle.await.unwrap();
1353
1354        // A downstream outcome arrives for the first trace (e.g. "tests passed an hour later").
1355        let dv = DeferredVerdict {
1356            gate_id: "tests".to_owned(),
1357            verdict: Verdict::Pass,
1358            score: Some(Score::new(1.0).unwrap()),
1359            reported_at: jiff::Timestamp::now(),
1360            reporter: "ci".to_owned(),
1361        };
1362        append_deferred(&db_path, &id0, &dv).unwrap();
1363        assert!(trace_exists(&db_path, "acme", &id0).unwrap());
1364        assert!(!trace_exists(&db_path, "acme", "no-such-trace").unwrap());
1365        // Cross-tenant: the same real trace id is invisible to a different tenant.
1366        assert!(!trace_exists(&db_path, "other-tenant", &id0).unwrap());
1367
1368        // The view surfaces the deferred verdict...
1369        let view = load_trace_view(&db_path, "acme", &id0).unwrap().unwrap();
1370        assert_eq!(view.deferred.len(), 1);
1371        assert_eq!(view.deferred[0].gate_id, "tests");
1372        assert_eq!(view.deferred[0].verdict, Verdict::Pass);
1373        // ...the second trace has none.
1374        assert!(
1375            load_trace_view(&db_path, "acme", &id1)
1376                .unwrap()
1377                .unwrap()
1378                .deferred
1379                .is_empty()
1380        );
1381        // Cross-tenant: another tenant cannot read this trace's view at all.
1382        assert!(
1383            load_trace_view(&db_path, "other-tenant", &id0)
1384                .unwrap()
1385                .is_none()
1386        );
1387
1388        // THE INVARIANT: the sealed bodies are untouched, so the chain still verifies. A late
1389        // outcome can never alter a past decision's hash.
1390        let traces = load_all_traces(&db_path).unwrap();
1391        assert!(
1392            traces.iter().all(|t| t.deferred.is_empty()),
1393            "sealed records stay deferred-free"
1394        );
1395        verify_chain(&traces, GENESIS_HASH).unwrap();
1396
1397        let _ = std::fs::remove_file(&db_path);
1398    }
1399
1400    // ── Durable-receipts tests ────────────────────────────────────────────────
1401
1402    /// Test (2): `append_to_spill` writes valid JSON lines to the spill file.
1403    #[test]
1404    fn durable_append_to_spill_writes_valid_json_line() {
1405        let db_path =
1406            std::env::temp_dir().join(format!("firstpass-spill-write-{}.db", uuid::Uuid::now_v7()));
1407        let spill_p = spill_path(&db_path);
1408        let file = std::fs::OpenOptions::new()
1409            .create(true)
1410            .append(true)
1411            .read(true)
1412            .open(&spill_p)
1413            .unwrap();
1414        let handle = Arc::new(std::sync::Mutex::new(file));
1415
1416        let t = sample_trace("t", "s");
1417        append_to_spill(&handle, &t).unwrap();
1418
1419        let content = std::fs::read_to_string(&spill_p).unwrap();
1420        let lines: Vec<&str> = content.lines().collect();
1421        assert_eq!(lines.len(), 1, "one line per spilled trace");
1422        let parsed: Trace = serde_json::from_str(lines[0]).unwrap();
1423        assert_eq!(parsed.trace_id, t.trace_id);
1424
1425        let _ = std::fs::remove_file(&spill_p);
1426    }
1427
1428    /// Test (3): on boot with a pre-populated spill file, the writer drains it and
1429    /// `verify_chain` passes over the full store.
1430    #[tokio::test]
1431    async fn drain_on_boot_restores_spilled_traces_and_chain_is_valid() {
1432        let db_path =
1433            std::env::temp_dir().join(format!("firstpass-drain-boot-{}.db", uuid::Uuid::now_v7()));
1434        let spill_p = spill_path(&db_path);
1435
1436        // Pre-populate spill file with 2 traces (simulating backpressure from a previous run).
1437        let t1 = sample_trace("t", "s1");
1438        let t2 = sample_trace("t", "s2");
1439        {
1440            let file = std::fs::OpenOptions::new()
1441                .create(true)
1442                .append(true)
1443                .read(true)
1444                .open(&spill_p)
1445                .unwrap();
1446            let h = Arc::new(std::sync::Mutex::new(file));
1447            append_to_spill(&h, &t1).unwrap();
1448            append_to_spill(&h, &t2).unwrap();
1449        }
1450
1451        // Open in durable mode — startup drain fires before any channel traffic.
1452        let (tx, _spill, handle) = open_with_receipts(&db_path, ReceiptsMode::Durable).unwrap();
1453        drop(tx); // close the channel immediately
1454        handle.await.unwrap();
1455
1456        let traces = load_all_traces(&db_path).unwrap();
1457        assert_eq!(traces.len(), 2, "both spilled traces recovered");
1458        verify_chain(&traces, GENESIS_HASH).unwrap();
1459
1460        // Spill file must be empty after a successful drain.
1461        let remaining = std::fs::read_to_string(&spill_p).unwrap();
1462        assert!(
1463            remaining.trim().is_empty(),
1464            "spill file must be empty after drain"
1465        );
1466
1467        let _ = std::fs::remove_file(&db_path);
1468        let _ = std::fs::remove_file(&spill_p);
1469    }
1470
1471    /// Test (4): spilled traces land before later channel traces in the store, preserving the
1472    /// hash chain's append-only ordering.
1473    #[tokio::test]
1474    async fn spilled_traces_land_before_later_channel_traces() {
1475        let db_path =
1476            std::env::temp_dir().join(format!("firstpass-spill-order-{}.db", uuid::Uuid::now_v7()));
1477        let spill_p = spill_path(&db_path);
1478
1479        // Pre-populate the spill file (as if these traces were spilled under backpressure).
1480        let spilled = sample_trace("t", "spilled");
1481        {
1482            let file = std::fs::OpenOptions::new()
1483                .create(true)
1484                .append(true)
1485                .read(true)
1486                .open(&spill_p)
1487                .unwrap();
1488            let h = Arc::new(std::sync::Mutex::new(file));
1489            append_to_spill(&h, &spilled).unwrap();
1490        }
1491
1492        // Open in durable mode: startup drain inserts the spilled trace first.
1493        let (tx, _spill, handle) = open_with_receipts(&db_path, ReceiptsMode::Durable).unwrap();
1494        // Send a new channel trace after the store is open (arrives after startup drain).
1495        let channel_trace = sample_trace("t", "channel");
1496        tx.try_send(channel_trace.clone()).unwrap();
1497        drop(tx);
1498        handle.await.unwrap();
1499
1500        let traces = load_all_traces(&db_path).unwrap();
1501        assert_eq!(traces.len(), 2, "spilled + channel trace");
1502        // Spilled trace must come first (lower seq).
1503        assert_eq!(
1504            traces[0].trace_id, spilled.trace_id,
1505            "spilled trace must precede channel trace"
1506        );
1507        assert_eq!(
1508            traces[1].trace_id, channel_trace.trace_id,
1509            "channel trace must follow spilled trace"
1510        );
1511        verify_chain(&traces, GENESIS_HASH).unwrap();
1512
1513        let _ = std::fs::remove_file(&db_path);
1514        let _ = std::fs::remove_file(&spill_p);
1515    }
1516
1517    /// An existing `experiment_pairs` table without `ch_error`/`ch_truncated` columns gets
1518    /// those columns added by `migrate()`, and old rows load with defaults of `false`/`0`.
1519    ///
1520    /// Mutation test: if the `ALTER TABLE` branch is removed from `migrate`, the load panics on
1521    /// a missing column or returns a wrong index — either way this assertion fails.
1522    #[tokio::test]
1523    async fn experiment_pairs_migration_adds_columns_to_old_schema() {
1524        let db_path = std::env::temp_dir().join(format!(
1525            "firstpass-pairs-migrate-{}.db",
1526            uuid::Uuid::now_v7()
1527        ));
1528        // Create an old-schema DB (no ch_error / ch_truncated).
1529        {
1530            let conn = rusqlite::Connection::open(&db_path).unwrap();
1531            conn.execute_batch(
1532                "CREATE TABLE experiment_pairs (
1533                    id INTEGER PRIMARY KEY AUTOINCREMENT,
1534                    tenant TEXT NOT NULL,
1535                    route_ix INTEGER NOT NULL,
1536                    fingerprint TEXT NOT NULL,
1537                    trace_id TEXT NOT NULL,
1538                    x_inc REAL NOT NULL,
1539                    x_ch REAL NOT NULL,
1540                    challenger_cost_usd REAL NOT NULL,
1541                    ts TEXT NOT NULL
1542                );",
1543            )
1544            .unwrap();
1545            conn.execute(
1546                "INSERT INTO experiment_pairs \
1547                 (tenant, route_ix, fingerprint, trace_id, x_inc, x_ch, challenger_cost_usd, ts) \
1548                 VALUES ('t', 0, 'fp', '00000000-0000-0000-0000-000000000001', 0.5, 0.5, 0.01, \
1549                         '2026-01-01T00:00:00Z')",
1550                [],
1551            )
1552            .unwrap();
1553        }
1554        // Open via `open()` so `migrate()` runs and adds the missing columns, then close.
1555        let (tx, handle) = open(&db_path).unwrap();
1556        drop(tx);
1557        handle.await.unwrap();
1558        // Now load: old row must have default values for the new columns.
1559        let rows = load_all_experiment_pairs(&db_path).unwrap();
1560        assert_eq!(rows.len(), 1, "old row must be readable after migration");
1561        assert!(!rows[0].ch_error, "old row defaults ch_error = false");
1562        assert_eq!(rows[0].ch_truncated, 0, "old row defaults ch_truncated = 0");
1563        // Legacy rows (no in_log column before migration) default to Unknown = 2.
1564        assert_eq!(
1565            rows[0].in_log,
1566            InLogStatus::Unknown,
1567            "legacy row defaults to Unknown"
1568        );
1569        let _ = std::fs::remove_file(&db_path);
1570    }
1571
1572    /// `ensure_migrated` + `load_all_experiment_pairs` — the path the CLI uses — must work on a
1573    /// pre-`in_log` database (schema without that column) without panicking or returning an error.
1574    ///
1575    /// Mutation test: if `ensure_migrated` does not call `migrate()`, `load_all_experiment_pairs`
1576    /// fails because the column does not exist — the test catches that as an assertion error.
1577    #[test]
1578    fn ensure_migrated_makes_pre_in_log_db_loadable() {
1579        let db_path = std::env::temp_dir().join(format!(
1580            "firstpass-ensure-migrated-{}.db",
1581            uuid::Uuid::now_v7()
1582        ));
1583        // Create a schema that lacks the in_log column (but has ch_error / ch_truncated).
1584        {
1585            let conn = rusqlite::Connection::open(&db_path).unwrap();
1586            conn.execute_batch(
1587                "CREATE TABLE experiment_pairs (
1588                    id INTEGER PRIMARY KEY AUTOINCREMENT,
1589                    tenant TEXT NOT NULL,
1590                    route_ix INTEGER NOT NULL,
1591                    fingerprint TEXT NOT NULL,
1592                    trace_id TEXT NOT NULL,
1593                    x_inc REAL NOT NULL,
1594                    x_ch REAL NOT NULL,
1595                    challenger_cost_usd REAL NOT NULL,
1596                    ch_error INTEGER NOT NULL DEFAULT 0,
1597                    ch_truncated INTEGER NOT NULL DEFAULT 0,
1598                    ts TEXT NOT NULL
1599                );",
1600            )
1601            .unwrap();
1602            conn.execute(
1603                "INSERT INTO experiment_pairs \
1604                 (tenant, route_ix, fingerprint, trace_id, x_inc, x_ch, challenger_cost_usd, ts) \
1605                 VALUES ('t', 0, 'fp', '00000000-0000-0000-0000-000000000002', 0.8, 0.4, 0.05, \
1606                         '2026-06-01T00:00:00Z')",
1607                [],
1608            )
1609            .unwrap();
1610        }
1611        // ensure_migrated is the same path the CLI takes before loading pairs.
1612        ensure_migrated(&db_path).expect("ensure_migrated must succeed");
1613        let rows = load_all_experiment_pairs(&db_path).expect("load must succeed after migration");
1614        assert_eq!(rows.len(), 1, "pre-migration row must be readable");
1615        assert_eq!(
1616            rows[0].in_log,
1617            InLogStatus::Unknown,
1618            "pre-in_log row defaults to Unknown after migration"
1619        );
1620        let _ = std::fs::remove_file(&db_path);
1621    }
1622}