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            ts TEXT NOT NULL
197        );
198        CREATE INDEX IF NOT EXISTS exp_pairs_tenant_idx ON experiment_pairs(tenant, route_ix, fingerprint);",
199    )?;
200    Ok(())
201}
202
203/// Assign `prev_hash`, compute `hash`, and insert one trace. Updates `head` on success.
204/// Logs and skips on hash or insert failure — one bad record must not wedge the pipeline.
205fn write_trace(conn: &Connection, trace: &mut Trace, head: &mut String) {
206    trace.prev_hash = head.clone();
207    let hash = match trace.hash() {
208        Ok(h) => h,
209        Err(err) => {
210            tracing::error!(%err, trace_id = %trace.trace_id, "trace writer: hash failed, dropping");
211            return;
212        }
213    };
214    if let Err(err) = insert(conn, trace, &hash) {
215        tracing::error!(%err, trace_id = %trace.trace_id, "trace writer: insert failed, dropping");
216        return;
217    }
218    *head = hash;
219}
220
221/// Drain the spill file into the store, inserting traces in file order BEFORE any new channel
222/// arrivals. Truncates the file after a successful drain. Errors on individual lines are logged
223/// and skipped so one bad spill line can't stall the recovery.
224///
225/// ponytail: reads the entire file into memory; the spill file is bounded by the channel size
226/// and trace sizes, so this is fine in practice. Stream-parse if traces grow very large.
227fn drain_spill(conn: &Connection, spill: &SpillHandle, head: &mut String) {
228    // Hold the lock for the entire drain: prevents concurrent spill writers from appending
229    // new lines mid-drain, which would corrupt ordering.
230    let mut guard = spill.lock().unwrap_or_else(|e| e.into_inner());
231
232    // Seek to start before reading.
233    if let Err(e) = guard.seek(SeekFrom::Start(0)) {
234        tracing::error!(%e, "drain_spill: seek failed");
235        return;
236    }
237
238    // Collect all non-empty lines while holding the lock (reader borrows from guard).
239    let lines: Vec<String> = {
240        let reader = BufReader::new(&*guard);
241        reader
242            .lines()
243            .filter_map(|l| match l {
244                Ok(s) if !s.trim().is_empty() => Some(s),
245                Ok(_) => None,
246                Err(e) => {
247                    tracing::error!(%e, "drain_spill: read error");
248                    None
249                }
250            })
251            .collect()
252    };
253
254    if lines.is_empty() {
255        return;
256    }
257
258    let n = lines.len();
259    for line in &lines {
260        let mut trace: Trace = match serde_json::from_str(line) {
261            Ok(t) => t,
262            Err(e) => {
263                tracing::error!(%e, "drain_spill: bad JSON line, skipping");
264                continue;
265            }
266        };
267        write_trace(conn, &mut trace, head);
268    }
269    tracing::info!(drained = n, "spill file drained into trace store");
270
271    // Truncate: all lines have been processed (failures are skipped and logged above).
272    if let Err(e) = guard.seek(SeekFrom::Start(0)) {
273        tracing::error!(%e, "drain_spill: post-drain seek failed");
274        return;
275    }
276    if let Err(e) = guard.set_len(0) {
277        tracing::error!(%e, "drain_spill: truncate failed — spill file may replay on next boot");
278    }
279}
280
281/// The writer's main loop: runs on a blocking-pool thread for the lifetime of the store.
282/// Never panics on a bad trace — logs and drops it, so one malformed record can't wedge the
283/// whole audit pipeline.
284///
285/// In durable mode (`spill` is `Some`): drains the spill file at startup and whenever the
286/// channel empties, so spilled traces always land BEFORE later channel traces in the chain.
287fn writer_loop(conn: Connection, mut rx: mpsc::Receiver<Trace>, spill: Option<SpillHandle>) {
288    let mut head = match current_head(&conn) {
289        Ok(h) => h,
290        Err(err) => {
291            tracing::error!(%err, "trace writer: failed to load chain head, stopping");
292            return;
293        }
294    };
295
296    // Startup drain: recover any traces spilled during a previous run's backpressure.
297    if let Some(ref s) = spill {
298        drain_spill(&conn, s, &mut head);
299    }
300
301    loop {
302        let Some(mut trace) = rx.blocking_recv() else {
303            break;
304        };
305        write_trace(&conn, &mut trace, &mut head);
306
307        // Fast-drain: pull any immediately available channel items, then drain spill when
308        // the channel is momentarily empty so spilled traces land before the next wave.
309        loop {
310            match rx.try_recv() {
311                Ok(mut t) => write_trace(&conn, &mut t, &mut head),
312                Err(mpsc::error::TryRecvError::Empty) => {
313                    if let Some(ref s) = spill {
314                        drain_spill(&conn, s, &mut head);
315                    }
316                    break;
317                }
318                Err(mpsc::error::TryRecvError::Disconnected) => return,
319            }
320        }
321    }
322
323    // Channel closed: one final spill drain to capture any last-moment spills.
324    if let Some(ref s) = spill {
325        drain_spill(&conn, s, &mut head);
326    }
327}
328
329fn current_head(conn: &Connection) -> Result<String, StoreError> {
330    let mut stmt = conn.prepare("SELECT hash FROM traces ORDER BY seq DESC LIMIT 1")?;
331    let mut rows = stmt.query([])?;
332    match rows.next()? {
333        Some(row) => Ok(row.get(0)?),
334        None => Ok(GENESIS_HASH.to_owned()),
335    }
336}
337
338fn insert(conn: &Connection, trace: &Trace, hash: &str) -> Result<(), StoreError> {
339    let body = serde_json::to_string(trace)?;
340    conn.execute(
341        "INSERT INTO traces (trace_id, ts, prev_hash, hash, tenant, session, body)
342         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
343        rusqlite::params![
344            trace.trace_id.to_string(),
345            trace.ts.to_string(),
346            trace.prev_hash,
347            hash,
348            trace.tenant_id,
349            trace.session_id,
350            body,
351        ],
352    )?;
353    Ok(())
354}
355
356/// Load every trace from the database in insertion (chain) order — used by tests and
357/// operators to verify the hash chain with [`firstpass_core::verify_chain`].
358///
359/// **Operator-wide** read: every trace across ALL tenants, in `seq` order.
360///
361/// This deliberately crosses tenant boundaries and must stay reserved for operator-scoped work
362/// where a global view is intrinsic — namely verifying the single hash-chain, which spans every
363/// tenant's traces in one sequence (ADR 0004 §D3). For tenant-facing reads use
364/// [`load_tenant_traces`].
365///
366/// # Errors
367/// Returns [`StoreError::Sqlite`] on a database error, or [`StoreError::Json`] if a stored
368/// row is not valid trace JSON.
369pub fn load_all_traces(db_path: impl AsRef<Path>) -> Result<Vec<Trace>, StoreError> {
370    let conn = connect(db_path.as_ref())?;
371    let mut stmt = conn.prepare("SELECT body FROM traces ORDER BY seq ASC")?;
372    let rows = stmt.query_map([], |row| row.get::<_, String>(0))?;
373    let mut traces = Vec::new();
374    for row in rows {
375        traces.push(serde_json::from_str(&row?)?);
376    }
377    Ok(traces)
378}
379
380/// **Tenant-scoped** read: only traces owned by `tenant`, in `seq` order (ADR 0004 §D3). Tenant A
381/// can never see tenant B's traces through this path.
382///
383/// # Errors
384/// Returns [`StoreError::Sqlite`] on a database error, or [`StoreError::Json`] if a stored
385/// row is not valid trace JSON.
386pub fn load_tenant_traces(
387    db_path: impl AsRef<Path>,
388    tenant: &str,
389) -> Result<Vec<Trace>, StoreError> {
390    let conn = connect(db_path.as_ref())?;
391    let mut stmt = conn.prepare("SELECT body FROM traces WHERE tenant = ?1 ORDER BY seq ASC")?;
392    let rows = stmt.query_map([tenant], |row| row.get::<_, String>(0))?;
393    let mut traces = Vec::new();
394    for row in rows {
395        traces.push(serde_json::from_str(&row?)?);
396    }
397    Ok(traces)
398}
399
400/// **Tenant-scoped** page: the tenant's `limit` most recent traces, newest first. Reads only the
401/// rows it returns (the `(tenant, seq)` index serves the order), so a panel polling a long-lived
402/// store pays for one page, not for the whole history.
403///
404/// # Errors
405/// Returns [`StoreError::Sqlite`] on a database error, or [`StoreError::Json`] if a stored
406/// row is not valid trace JSON.
407pub fn load_recent_tenant_traces(
408    db_path: impl AsRef<Path>,
409    tenant: &str,
410    limit: usize,
411) -> Result<Vec<Trace>, StoreError> {
412    let conn = connect(db_path.as_ref())?;
413    let mut stmt =
414        conn.prepare("SELECT body FROM traces WHERE tenant = ?1 ORDER BY seq DESC LIMIT ?2")?;
415    let limit = i64::try_from(limit).unwrap_or(i64::MAX);
416    let rows = stmt.query_map(rusqlite::params![tenant, limit], |row| {
417        row.get::<_, String>(0)
418    })?;
419    let mut traces = Vec::new();
420    for row in rows {
421        traces.push(serde_json::from_str(&row?)?);
422    }
423    Ok(traces)
424}
425
426/// All-time totals over a tenant's receipts, for the panel's overview tiles.
427#[derive(Debug, Clone, Default, PartialEq)]
428pub struct ReceiptsSummary {
429    /// Number of receipts.
430    pub requests: u64,
431    /// Sum of `final.total_cost_usd`.
432    pub spent_usd: f64,
433    /// Sum of `final.counterfactual_baseline_usd`.
434    pub baseline_usd: f64,
435    /// Receipts served at rung 0.
436    pub first_pass: u64,
437    /// Receipts that climbed at least one rung.
438    pub escalated: u64,
439    /// Gate verdicts across every attempt: pass, fail, abstain.
440    pub gates: [u64; 3],
441    /// Attempts whose answer stopped at the output-token limit.
442    pub truncated: u64,
443}
444
445impl ReceiptsSummary {
446    fn merged(&self, d: &Self) -> Self {
447        Self {
448            requests: self.requests + d.requests,
449            spent_usd: self.spent_usd + d.spent_usd,
450            baseline_usd: self.baseline_usd + d.baseline_usd,
451            first_pass: self.first_pass + d.first_pass,
452            escalated: self.escalated + d.escalated,
453            gates: [
454                self.gates[0] + d.gates[0],
455                self.gates[1] + d.gates[1],
456                self.gates[2] + d.gates[2],
457            ],
458            truncated: self.truncated + d.truncated,
459        }
460    }
461}
462
463/// Per-(store, tenant) running totals and the last `seq` folded into them.
464type SummaryCache = std::collections::HashMap<(std::path::PathBuf, String), (i64, ReceiptsSummary)>;
465static SUMMARY_CACHE: std::sync::LazyLock<std::sync::Mutex<SummaryCache>> =
466    std::sync::LazyLock::new(Default::default);
467
468/// **Tenant-scoped** aggregate over every stored receipt, computed inside SQLite and kept
469/// incrementally.
470///
471/// The panel polls this every two seconds, so it must not re-read the whole history each time.
472/// Receipts are append-only (the hash chain forbids rewriting one), so the totals up to a `seq`
473/// never change: each call folds in only the rows after the last `seq` it saw. A store whose
474/// newest `seq` is below the cached one was replaced, and is recounted from zero.
475///
476/// # Errors
477/// Returns [`StoreError::Sqlite`] on a database error.
478pub fn tenant_receipts_summary(
479    db_path: impl AsRef<Path>,
480    tenant: &str,
481) -> Result<ReceiptsSummary, StoreError> {
482    let conn = connect(db_path.as_ref())?;
483    let key = (db_path.as_ref().to_path_buf(), tenant.to_owned());
484    let hi: i64 = conn.query_row(
485        "SELECT coalesce(max(seq), 0) FROM traces WHERE tenant = ?1",
486        [tenant],
487        |r| r.get(0),
488    )?;
489    let cached = SUMMARY_CACHE
490        .lock()
491        .ok()
492        .and_then(|c| c.get(&key).cloned())
493        .filter(|(seen, _)| *seen <= hi);
494    let (after, base) = cached.unwrap_or_default();
495    if after == hi {
496        return Ok(base);
497    }
498    let total = base.merged(&summary_between(&conn, tenant, after, hi)?);
499    if let Ok(mut c) = SUMMARY_CACHE.lock() {
500        // Bounded: dropping the cache only costs one full recount per tenant, never correctness.
501        if c.len() >= 4096 {
502            c.clear();
503        }
504        c.insert(key, (hi, total.clone()));
505    }
506    Ok(total)
507}
508
509/// Totals over the tenant's rows with `after < seq <= hi`. Both queries share the same upper
510/// bound, so a write landing between them cannot be counted in one and not the other.
511fn summary_between(
512    conn: &Connection,
513    tenant: &str,
514    after: i64,
515    hi: i64,
516) -> Result<ReceiptsSummary, StoreError> {
517    let mut s = conn.query_row(
518        "SELECT count(*),
519                total(json_extract(body, '$.final.total_cost_usd')),
520                total(json_extract(body, '$.final.counterfactual_baseline_usd')),
521                coalesce(sum(json_extract(body, '$.final.served_rung') = 0), 0),
522                coalesce(sum(json_extract(body, '$.final.escalations') > 0), 0)
523         FROM traces WHERE tenant = ?1 AND seq > ?2 AND seq <= ?3",
524        rusqlite::params![tenant, after, hi],
525        |r| {
526            Ok(ReceiptsSummary {
527                requests: u64::try_from(r.get::<_, i64>(0)?).unwrap_or(0),
528                spent_usd: r.get(1)?,
529                baseline_usd: r.get(2)?,
530                first_pass: u64::try_from(r.get::<_, i64>(3)?).unwrap_or(0),
531                escalated: u64::try_from(r.get::<_, i64>(4)?).unwrap_or(0),
532                gates: [0; 3],
533                truncated: 0,
534            })
535        },
536    )?;
537    let truncated: i64 = conn.query_row(
538        "SELECT count(*) FROM traces t, json_each(t.body, '$.attempts') a
539         WHERE t.tenant = ?1 AND t.seq > ?2 AND t.seq <= ?3
540           AND json_extract(a.value, '$.truncated') = 1",
541        rusqlite::params![tenant, after, hi],
542        |r| r.get(0),
543    )?;
544    s.truncated = u64::try_from(truncated).unwrap_or(0);
545    let mut stmt = conn.prepare(
546        "SELECT json_extract(g.value, '$.verdict'), count(*)
547         FROM traces t, json_each(t.body, '$.attempts') a, json_each(a.value, '$.gates') g
548         WHERE t.tenant = ?1 AND t.seq > ?2 AND t.seq <= ?3 GROUP BY 1",
549    )?;
550    let rows = stmt.query_map(rusqlite::params![tenant, after, hi], |r| {
551        Ok((r.get::<_, Option<String>>(0)?, r.get::<_, i64>(1)?))
552    })?;
553    for row in rows {
554        let (verdict, n) = row?;
555        let slot = match verdict.as_deref() {
556            Some("pass") => 0,
557            Some("fail") => 1,
558            Some("abstain") => 2,
559            _ => continue,
560        };
561        s.gates[slot] += u64::try_from(n).unwrap_or(0);
562    }
563    Ok(s)
564}
565
566/// Whether a trace with `trace_id` exists **and is owned by `tenant`** — used to reject feedback
567/// for unknown traces and, crucially, to deny cross-tenant feedback (ADR 0004 §D3/§D4). A trace
568/// owned by another tenant is indistinguishable from a non-existent one here, so the caller can
569/// return a `404` with no existence oracle.
570///
571/// # Errors
572/// Returns [`StoreError::Sqlite`] on a database error.
573pub fn trace_exists(
574    db_path: impl AsRef<Path>,
575    tenant: &str,
576    trace_id: &str,
577) -> Result<bool, StoreError> {
578    let conn = connect(db_path.as_ref())?;
579    let n: i64 = conn.query_row(
580        "SELECT COUNT(1) FROM traces WHERE tenant = ?1 AND trace_id = ?2",
581        [tenant, trace_id],
582        |row| row.get(0),
583    )?;
584    Ok(n > 0)
585}
586
587/// The route index recorded on `trace_id`, scoped to `tenant`.
588///
589/// `Ok(None)` means the trace is not visible to this tenant (or does not exist) — the same
590/// ownership check [`trace_exists`] makes, so this cannot be used to probe another tenant's
591/// traces. `Ok(Some(None))` means the trace exists but predates route recording.
592///
593/// # Errors
594/// On a store read failure.
595/// The decision a trace's answer was originally proven by, if it was itself a cache replay.
596///
597/// A cache hit is recorded under a NEW trace id, with `cache_source` naming the decision whose gate
598/// passed. So feedback arriving on a replay must be applied to that source, not to the replay's own
599/// id — otherwise a Fail reported against a replayed answer retracts nothing and the disproven
600/// entry keeps serving until TTL.
601///
602/// Returns `Ok(None)` for an unknown trace, `Ok(Some(None))` for one that ran the ladder itself.
603///
604/// # Errors
605/// [`StoreError`] when the database cannot be opened or queried.
606pub fn trace_cache_source(
607    db_path: impl AsRef<Path>,
608    tenant: &str,
609    trace_id: &str,
610) -> Result<Option<Option<uuid::Uuid>>, StoreError> {
611    let conn = connect(db_path.as_ref())?;
612    let body: Option<String> = conn
613        .query_row(
614            "SELECT body FROM traces WHERE tenant = ?1 AND trace_id = ?2",
615            [tenant, trace_id],
616            |row| row.get(0),
617        )
618        .optional()?;
619    Ok(body.map(|b: String| {
620        serde_json::from_str::<firstpass_core::Trace>(&b)
621            .ok()
622            .and_then(|t| t.final_.cache_source)
623    }))
624}
625
626pub fn trace_route(
627    db_path: impl AsRef<Path>,
628    tenant: &str,
629    trace_id: &str,
630) -> Result<Option<Option<u32>>, StoreError> {
631    let conn = connect(db_path.as_ref())?;
632    let body: Option<String> = conn
633        .query_row(
634            "SELECT body FROM traces WHERE tenant = ?1 AND trace_id = ?2",
635            [tenant, trace_id],
636            |row| row.get(0),
637        )
638        .optional()?;
639    Ok(body.map(|b: String| {
640        serde_json::from_str::<firstpass_core::Trace>(&b)
641            .ok()
642            .and_then(|t| t.route_ix)
643    }))
644}
645
646/// Append a deferred verdict for `trace_id` (a downstream outcome or async gate result). This
647/// writes ONLY to the `deferred_verdicts` table; the sealed trace and its hash are untouched, so
648/// the audit chain remains verifiable (SPEC §8.3.4 — the outcome-feedback loop).
649///
650/// # Errors
651/// Returns [`StoreError::Sqlite`] on a database error.
652pub fn append_deferred(
653    db_path: impl AsRef<Path>,
654    trace_id: &str,
655    v: &DeferredVerdict,
656) -> Result<(), StoreError> {
657    let conn = connect(db_path.as_ref())?;
658    conn.execute(
659        "INSERT INTO deferred_verdicts (trace_id, gate_id, verdict, score, reported_at, reporter)
660         VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
661        rusqlite::params![
662            trace_id,
663            v.gate_id,
664            v.verdict.as_str(),
665            v.score.map(Score::value),
666            v.reported_at.to_string(),
667            v.reporter,
668        ],
669    )?;
670    Ok(())
671}
672
673/// Load the deferred verdicts recorded for `trace_id`, oldest first. Malformed stored rows are
674/// skipped (logged), never fatal — a corrupt late outcome must not break reading the trace.
675///
676/// # Errors
677/// Returns [`StoreError::Sqlite`] on a database error.
678pub fn load_deferred(
679    db_path: impl AsRef<Path>,
680    trace_id: &str,
681) -> Result<Vec<DeferredVerdict>, StoreError> {
682    let conn = connect(db_path.as_ref())?;
683    let mut stmt = conn.prepare(
684        "SELECT gate_id, verdict, score, reported_at, reporter
685         FROM deferred_verdicts WHERE trace_id = ?1 ORDER BY id ASC",
686    )?;
687    let rows = stmt.query_map([trace_id], |row| {
688        Ok((
689            row.get::<_, String>(0)?,
690            row.get::<_, String>(1)?,
691            row.get::<_, Option<f64>>(2)?,
692            row.get::<_, String>(3)?,
693            row.get::<_, String>(4)?,
694        ))
695    })?;
696
697    let mut out = Vec::new();
698    for row in rows {
699        let (gate_id, verdict_s, score, reported_s, reporter) = row?;
700        let verdict = match verdict_s.as_str() {
701            "pass" => Verdict::Pass,
702            "fail" => Verdict::Fail,
703            "abstain" => Verdict::Abstain,
704            other => {
705                tracing::warn!(verdict = %other, %trace_id, "skipping deferred row with bad verdict");
706                continue;
707            }
708        };
709        let Ok(reported_at) = reported_s.parse::<jiff::Timestamp>() else {
710            tracing::warn!(%trace_id, "skipping deferred row with bad timestamp");
711            continue;
712        };
713        out.push(DeferredVerdict {
714            gate_id,
715            verdict,
716            score: score.and_then(|s| Score::new(s).ok()),
717            reported_at,
718            reporter,
719        });
720    }
721    Ok(out)
722}
723
724/// Every deferred verdict in the store as `(trace_id, verdict)`, in the order the feedback
725/// arrived. **Operator-wide**: rows are not tenant-scoped here, so callers must join them to
726/// tenant-scoped traces (an id that matches no loaded trace is simply ignored). Rows with an
727/// unparseable id or verdict are skipped, as in [`load_deferred`].
728///
729/// # Errors
730/// Returns [`StoreError::Sqlite`] on a database error.
731pub fn load_all_deferred(
732    db_path: impl AsRef<Path>,
733) -> Result<Vec<(uuid::Uuid, Verdict)>, StoreError> {
734    let conn = connect(db_path.as_ref())?;
735    let mut stmt =
736        conn.prepare("SELECT trace_id, verdict FROM deferred_verdicts ORDER BY id ASC")?;
737    let rows = stmt.query_map([], |row| {
738        Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))
739    })?;
740    let mut out = Vec::new();
741    for row in rows {
742        let (id, verdict) = row?;
743        let verdict = match verdict.as_str() {
744            "pass" => Verdict::Pass,
745            "fail" => Verdict::Fail,
746            "abstain" => Verdict::Abstain,
747            _ => continue,
748        };
749        if let Ok(id) = id.parse::<uuid::Uuid>() {
750            out.push((id, verdict));
751        }
752    }
753    Ok(out)
754}
755
756/// What the feedback path needs to attribute an outcome to a certified-selection experiment:
757/// the trace's session, its route, and its experiment record. `Ok(None)` when the trace is not
758/// visible to `tenant`, or carries no experiment record. Tenant-scoped like [`trace_route`].
759///
760/// # Errors
761/// On a store read failure.
762pub fn trace_experiment(
763    db_path: impl AsRef<Path>,
764    tenant: &str,
765    trace_id: &str,
766) -> Result<Option<(String, usize, firstpass_core::trace::ExperimentRecord, f64)>, StoreError> {
767    let conn = connect(db_path.as_ref())?;
768    let body: Option<String> = conn
769        .query_row(
770            "SELECT body FROM traces WHERE tenant = ?1 AND trace_id = ?2",
771            [tenant, trace_id],
772            |row| row.get(0),
773        )
774        .optional()?;
775    let Some(body) = body else { return Ok(None) };
776    let trace: Trace = serde_json::from_str(&body)?;
777    let cost_usd = trace.final_.total_cost_usd;
778    Ok(trace.experiment.map(|rec| {
779        (
780            trace.session_id,
781            // Same fallback as the guardrail: a trace with no recorded route is route 0.
782            trace.route_ix.map_or(0, |r| r as usize),
783            rec,
784            cost_usd,
785        )
786    }))
787}
788
789/// Load a single trace by id **scoped to `tenant`**, with its deferred verdicts merged into
790/// `deferred` — the **view** for display/inspection (ADR 0004 §D3). A trace owned by another
791/// tenant returns `None`, exactly like a missing one, so an inspecting agent can never read across
792/// tenants. This is deliberately separate from [`load_all_traces`]: merging deferred verdicts
793/// changes the record, so a merged trace must NOT be fed to `verify_chain` (chain verification
794/// always runs on the sealed bodies from [`load_all_traces`]).
795///
796/// # Errors
797/// Returns [`StoreError::Sqlite`] / [`StoreError::Json`] on database or decode errors.
798pub fn load_trace_view(
799    db_path: impl AsRef<Path>,
800    tenant: &str,
801    trace_id: &str,
802) -> Result<Option<Trace>, StoreError> {
803    let conn = connect(db_path.as_ref())?;
804    let body: Option<String> = conn
805        .query_row(
806            "SELECT body FROM traces WHERE tenant = ?1 AND trace_id = ?2",
807            [tenant, trace_id],
808            |row| row.get(0),
809        )
810        .ok();
811    let Some(body) = body else { return Ok(None) };
812    let mut trace: Trace = serde_json::from_str(&body)?;
813    trace.deferred = load_deferred(db_path, trace_id)?;
814    Ok(Some(trace))
815}
816
817/// One row from the `experiment_pairs` table.
818///
819/// Used to rebuild paired-mode experiments at startup and in the CLI, so the e-process trajectory
820/// is identical whether derived live or replayed. Arrival order = row `id` (insertion order).
821#[derive(Debug, Clone, PartialEq)]
822pub struct ExperimentPairRow {
823    /// Tenant that owns this pair.
824    pub tenant: String,
825    /// Route index the challenger belongs to.
826    pub route_ix: usize,
827    /// Fingerprint of the experiment config (incumbent + challenger ladders, margin, alpha).
828    pub fingerprint: String,
829    /// Trace id of the served (incumbent) request that triggered this pair.
830    pub trace_id: uuid::Uuid,
831    /// Incumbent gate verdict: 1.0 = gate passed, 0.0 = gate failed / best-attempt.
832    pub x_inc: f64,
833    /// Challenger gate verdict: 1.0 = gate passed, 0.0 = gate failed.
834    pub x_ch: f64,
835    /// USD spent by the challenger on this pair.
836    pub challenger_cost_usd: f64,
837    /// When the pair was recorded.
838    pub ts: jiff::Timestamp,
839}
840
841/// Append one experiment pair to the `experiment_pairs` table.
842///
843/// # Errors
844/// Returns [`StoreError::Sqlite`] on a database write failure.
845pub fn append_experiment_pair(
846    db_path: impl AsRef<std::path::Path>,
847    row: &ExperimentPairRow,
848) -> Result<(), StoreError> {
849    let conn = connect(db_path.as_ref())?;
850    conn.execute(
851        "INSERT INTO experiment_pairs \
852         (tenant, route_ix, fingerprint, trace_id, x_inc, x_ch, challenger_cost_usd, ts) \
853         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
854        rusqlite::params![
855            row.tenant,
856            row.route_ix as i64,
857            row.fingerprint,
858            row.trace_id.to_string(),
859            row.x_inc,
860            row.x_ch,
861            row.challenger_cost_usd,
862            row.ts.to_string(),
863        ],
864    )?;
865    Ok(())
866}
867
868/// Load every experiment pair in insertion order. Used at startup (rebuild) and CLI replay.
869///
870/// Rows with an unparseable `trace_id` or `ts` are skipped with a warning rather than failing the
871/// read — a single corrupt row must not prevent an otherwise healthy experiment from being restored.
872///
873/// # Errors
874/// Returns [`StoreError::Sqlite`] on a database read failure.
875pub fn load_all_experiment_pairs(
876    db_path: impl AsRef<std::path::Path>,
877) -> Result<Vec<ExperimentPairRow>, StoreError> {
878    let conn = connect(db_path.as_ref())?;
879    let mut stmt = conn.prepare(
880        "SELECT tenant, route_ix, fingerprint, trace_id, x_inc, x_ch, challenger_cost_usd, ts \
881         FROM experiment_pairs ORDER BY id ASC",
882    )?;
883    let rows = stmt.query_map([], |row| {
884        Ok((
885            row.get::<_, String>(0)?,
886            row.get::<_, i64>(1)?,
887            row.get::<_, String>(2)?,
888            row.get::<_, String>(3)?,
889            row.get::<_, f64>(4)?,
890            row.get::<_, f64>(5)?,
891            row.get::<_, f64>(6)?,
892            row.get::<_, String>(7)?,
893        ))
894    })?;
895    let mut out = Vec::new();
896    for row in rows {
897        let (tenant, route_ix, fingerprint, trace_id_s, x_inc, x_ch, ch_cost, ts_s) = row?;
898        let Ok(trace_id) = trace_id_s.parse::<uuid::Uuid>() else {
899            tracing::warn!(
900                %trace_id_s,
901                "experiment_pairs: skipping row with unparseable trace_id"
902            );
903            continue;
904        };
905        let Ok(ts) = ts_s.parse::<jiff::Timestamp>() else {
906            tracing::warn!(
907                %trace_id_s,
908                "experiment_pairs: skipping row with unparseable ts"
909            );
910            continue;
911        };
912        out.push(ExperimentPairRow {
913            tenant,
914            route_ix: route_ix as usize,
915            fingerprint,
916            trace_id,
917            x_inc,
918            x_ch,
919            challenger_cost_usd: ch_cost,
920            ts,
921        });
922    }
923    Ok(out)
924}
925
926#[cfg(test)]
927mod tests {
928    use firstpass_core::{
929        Attempt, Features, FinalOutcome, GENESIS_HASH, PolicyRef, RequestInfo, ServedFrom,
930        TaskKind, Verdict, verify_chain,
931    };
932
933    use super::*;
934
935    fn sample_trace(tenant: &str, session: &str) -> Trace {
936        let attempt = Attempt {
937            rung: 0,
938            model: "claude-haiku-4-5".to_owned(),
939            provider: "anthropic".to_owned(),
940            in_tokens: 10,
941            cache_write_tokens: 0,
942            cache_read_tokens: 0,
943            out_tokens: 5,
944            cost_usd: 0.001,
945            latency_ms: 12,
946            gates: vec![],
947            verdict: Verdict::Pass,
948            truncated: false,
949        };
950        let mut trace = Trace {
951            trace_id: uuid::Uuid::now_v7(),
952            prev_hash: GENESIS_HASH.to_owned(),
953            tenant_id: tenant.to_owned(),
954            session_id: session.to_owned(),
955            ts: jiff::Timestamp::now(),
956            mode: firstpass_core::Mode::Observe,
957            policy: PolicyRef {
958                id: "observe-passthrough@v0".to_owned(),
959                explore: false,
960                propensity: None,
961                mode_profile: None,
962            },
963            request: RequestInfo {
964                api: "anthropic.messages".to_owned(),
965                prompt_hash: "deadbeef".to_owned(),
966                features: Features::new(TaskKind::Other),
967            },
968            attempts: vec![attempt],
969            deferred: Vec::new(),
970            final_: FinalOutcome {
971                served_rung: Some(0),
972                served_from: ServedFrom::Attempt,
973                total_cost_usd: 0.001,
974                gate_cost_usd: 0.0,
975                total_latency_ms: 12,
976                escalations: 0,
977                counterfactual_baseline_usd: 0.001,
978                savings_usd: 0.0,
979                cache_source: None,
980            },
981            probe: None,
982            rollout: None,
983            shadow: None,
984            route_ix: None,
985            predicted_pass: None,
986            decision_prior: None,
987            elastic: None,
988            risk: None,
989            experiment: None,
990        };
991        trace.recompute_savings();
992        trace
993    }
994
995    /// The SQL aggregate must equal a plain Rust fold over the same receipts, including traces
996    /// with no served rung, escalations, mixed gate verdicts, and another tenant's rows mixed in.
997    /// The page must be the newest `limit` rows of this tenant only, newest first.
998    #[tokio::test]
999    async fn summary_aggregate_matches_a_rust_fold_and_page_is_newest_first() {
1000        use firstpass_core::GateResult;
1001        let db_path =
1002            std::env::temp_dir().join(format!("firstpass-store-sum-{}.db", uuid::Uuid::now_v7()));
1003        let gate = |v: Verdict| GateResult {
1004            gate_id: "tests".to_owned(),
1005            verdict: v,
1006            score: None,
1007            cost_usd: 0.0,
1008            ms: 1,
1009            reason: None,
1010            evidence_ref: None,
1011        };
1012        let mut mine = Vec::new();
1013        for i in 0..23u32 {
1014            let mut t = sample_trace("tenant-a", &format!("s{i}"));
1015            t.final_.total_cost_usd = 0.01 * f64::from(i + 1);
1016            t.final_.counterfactual_baseline_usd = 0.05;
1017            t.final_.escalations = i % 3;
1018            t.final_.served_rung = match i % 5 {
1019                0 => None,
1020                k => Some(k % 3), // rung 0, 1 and 2 in unequal counts
1021            };
1022            t.attempts[0].gates = (0..i % 4)
1023                .map(|k| {
1024                    gate([Verdict::Pass, Verdict::Fail, Verdict::Abstain][(k + i) as usize % 3])
1025                })
1026                .collect();
1027            t.attempts[0].truncated = i % 3 == 0;
1028            if i % 2 == 0 {
1029                let mut second = t.attempts[0].clone();
1030                second.rung = 1;
1031                second.gates = vec![gate(Verdict::Pass)];
1032                second.truncated = i % 4 == 0;
1033                t.attempts.push(second);
1034            }
1035            mine.push(t);
1036        }
1037        let (tx, handle) = open(&db_path).unwrap();
1038        for (i, t) in mine.iter().enumerate() {
1039            tx.send(t.clone()).await.unwrap();
1040            if i % 4 == 0 {
1041                let mut other = sample_trace("tenant-b", "x");
1042                other.final_.total_cost_usd = 100.0;
1043                other.attempts[0].gates = vec![gate(Verdict::Fail)];
1044                other.attempts[0].truncated = true;
1045                tx.send(other).await.unwrap();
1046            }
1047        }
1048        drop(tx);
1049        handle.await.unwrap();
1050
1051        let mut want = ReceiptsSummary {
1052            requests: mine.len() as u64,
1053            ..Default::default()
1054        };
1055        for t in &mine {
1056            want.spent_usd += t.final_.total_cost_usd;
1057            want.baseline_usd += t.final_.counterfactual_baseline_usd;
1058            want.first_pass += u64::from(t.final_.served_rung == Some(0));
1059            want.escalated += u64::from(t.final_.escalations > 0);
1060            want.truncated += t.attempts.iter().filter(|a| a.truncated).count() as u64;
1061            for g in t.attempts.iter().flat_map(|a| &a.gates) {
1062                want.gates[match g.verdict {
1063                    Verdict::Pass => 0,
1064                    Verdict::Fail => 1,
1065                    Verdict::Abstain => 2,
1066                }] += 1;
1067            }
1068        }
1069        let got = tenant_receipts_summary(&db_path, "tenant-a").unwrap();
1070        assert_eq!(got.requests, want.requests);
1071        assert_eq!(got.first_pass, want.first_pass);
1072        assert_eq!(got.escalated, want.escalated);
1073        assert_eq!(got.gates, want.gates);
1074        assert_eq!(
1075            got.truncated, want.truncated,
1076            "only this tenant's cut-off attempts"
1077        );
1078        assert!(want.truncated > 0);
1079        assert!(want.first_pass > 0 && want.escalated > 0 && want.gates.iter().all(|&n| n > 0));
1080        assert!(
1081            (got.spent_usd - want.spent_usd).abs() < 1e-9,
1082            "{got:?} {want:?}"
1083        );
1084        assert!((got.baseline_usd - want.baseline_usd).abs() < 1e-9);
1085        assert_eq!(
1086            tenant_receipts_summary(&db_path, "nobody").unwrap(),
1087            ReceiptsSummary::default()
1088        );
1089
1090        let page = load_recent_tenant_traces(&db_path, "tenant-a", 5).unwrap();
1091        let ids: Vec<_> = page.iter().map(|t| t.trace_id).collect();
1092        let newest: Vec<_> = mine.iter().rev().take(5).map(|t| t.trace_id).collect();
1093        assert_eq!(ids, newest);
1094
1095        // Incremental: rows appended after a read are folded in exactly once.
1096        let (tx, handle) = open(&db_path).unwrap();
1097        for _ in 0..4 {
1098            let mut t = sample_trace("tenant-a", "late");
1099            t.final_.served_rung = Some(0);
1100            t.attempts[0].gates = vec![gate(Verdict::Abstain)];
1101            tx.send(t).await.unwrap();
1102        }
1103        drop(tx);
1104        handle.await.unwrap();
1105        let again = tenant_receipts_summary(&db_path, "tenant-a").unwrap();
1106        assert_eq!(again.requests, want.requests + 4);
1107        assert_eq!(again.first_pass, want.first_pass + 4);
1108        assert_eq!(
1109            again.gates,
1110            [want.gates[0], want.gates[1], want.gates[2] + 4]
1111        );
1112        assert_eq!(
1113            tenant_receipts_summary(&db_path, "tenant-a").unwrap(),
1114            again
1115        );
1116
1117        // A store replaced at the same path (newest seq below the cached one) is recounted.
1118        let _ = std::fs::remove_file(&db_path);
1119        let (tx, handle) = open(&db_path).unwrap();
1120        tx.send(sample_trace("tenant-a", "fresh")).await.unwrap();
1121        drop(tx);
1122        handle.await.unwrap();
1123        assert_eq!(
1124            tenant_receipts_summary(&db_path, "tenant-a")
1125                .unwrap()
1126                .requests,
1127            1
1128        );
1129        let _ = std::fs::remove_file(&db_path);
1130    }
1131
1132    #[tokio::test]
1133    async fn writer_assigns_prev_hash_and_forms_a_valid_chain() {
1134        let db_path =
1135            std::env::temp_dir().join(format!("firstpass-store-test-{}.db", uuid::Uuid::now_v7()));
1136        let (tx, handle) = open(&db_path).unwrap();
1137
1138        tx.try_send(sample_trace("tenant-a", "session-1")).unwrap();
1139        tx.try_send(sample_trace("tenant-a", "session-1")).unwrap();
1140        drop(tx);
1141        handle.await.unwrap();
1142
1143        let traces = load_all_traces(&db_path).unwrap();
1144        assert_eq!(traces.len(), 2);
1145        assert_eq!(traces[0].prev_hash, GENESIS_HASH);
1146        assert_eq!(traces[1].prev_hash, traces[0].hash().unwrap());
1147        verify_chain(&traces, GENESIS_HASH).unwrap();
1148
1149        let _ = std::fs::remove_file(&db_path);
1150    }
1151
1152    /// D7 cross-tenant isolation, at the store layer: with rows for tenants A and B, every
1153    /// tenant-scoped read for A returns only A's data, and vice-versa. The operator-wide
1154    /// [`load_all_traces`] still sees both (for chain verification).
1155    #[tokio::test]
1156    async fn tenant_scoped_reads_never_cross_the_boundary() {
1157        let db_path =
1158            std::env::temp_dir().join(format!("firstpass-isolation-{}.db", uuid::Uuid::now_v7()));
1159        let (tx, handle) = open(&db_path).unwrap();
1160
1161        // Two traces for A, one for B.
1162        let a0 = sample_trace("tenant-a", "sa-0");
1163        let a1 = sample_trace("tenant-a", "sa-1");
1164        let b0 = sample_trace("tenant-b", "sb-0");
1165        let (a0_id, a1_id, b0_id) = (
1166            a0.trace_id.to_string(),
1167            a1.trace_id.to_string(),
1168            b0.trace_id.to_string(),
1169        );
1170        tx.try_send(a0).unwrap();
1171        tx.try_send(b0).unwrap();
1172        tx.try_send(a1).unwrap();
1173        drop(tx);
1174        handle.await.unwrap();
1175
1176        // Scoped list: A sees exactly its two, B sees exactly its one.
1177        let a_traces = load_tenant_traces(&db_path, "tenant-a").unwrap();
1178        assert_eq!(a_traces.len(), 2, "A must see only A's traces");
1179        assert!(a_traces.iter().all(|t| t.tenant_id == "tenant-a"));
1180        let b_traces = load_tenant_traces(&db_path, "tenant-b").unwrap();
1181        assert_eq!(b_traces.len(), 1, "B must see only B's trace");
1182        assert!(b_traces.iter().all(|t| t.tenant_id == "tenant-b"));
1183
1184        // A can prove its own trace exists but cannot see B's, and vice-versa.
1185        assert!(trace_exists(&db_path, "tenant-a", &a0_id).unwrap());
1186        assert!(!trace_exists(&db_path, "tenant-a", &b0_id).unwrap());
1187        assert!(trace_exists(&db_path, "tenant-b", &b0_id).unwrap());
1188        assert!(!trace_exists(&db_path, "tenant-b", &a1_id).unwrap());
1189
1190        // The view is likewise scoped: cross-tenant reads are indistinguishable from a miss.
1191        assert!(
1192            load_trace_view(&db_path, "tenant-a", &a0_id)
1193                .unwrap()
1194                .is_some()
1195        );
1196        assert!(
1197            load_trace_view(&db_path, "tenant-a", &b0_id)
1198                .unwrap()
1199                .is_none()
1200        );
1201        assert!(
1202            load_trace_view(&db_path, "tenant-b", &a0_id)
1203                .unwrap()
1204                .is_none()
1205        );
1206
1207        // A tenant that owns nothing sees nothing.
1208        assert!(load_tenant_traces(&db_path, "ghost").unwrap().is_empty());
1209
1210        // Operator-wide read still spans both, and the global chain stays valid.
1211        let all = load_all_traces(&db_path).unwrap();
1212        assert_eq!(all.len(), 3);
1213        verify_chain(&all, GENESIS_HASH).unwrap();
1214
1215        let _ = std::fs::remove_file(&db_path);
1216    }
1217
1218    #[tokio::test]
1219    async fn deferred_verdicts_attach_on_read_without_breaking_the_chain() {
1220        let db_path = std::env::temp_dir().join(format!(
1221            "firstpass-deferred-test-{}.db",
1222            uuid::Uuid::now_v7()
1223        ));
1224        let (tx, handle) = open(&db_path).unwrap();
1225        let t0 = sample_trace("acme", "run-1");
1226        let t1 = sample_trace("acme", "run-1");
1227        let (id0, id1) = (t0.trace_id.to_string(), t1.trace_id.to_string());
1228        tx.try_send(t0).unwrap();
1229        tx.try_send(t1).unwrap();
1230        drop(tx);
1231        handle.await.unwrap();
1232
1233        // A downstream outcome arrives for the first trace (e.g. "tests passed an hour later").
1234        let dv = DeferredVerdict {
1235            gate_id: "tests".to_owned(),
1236            verdict: Verdict::Pass,
1237            score: Some(Score::new(1.0).unwrap()),
1238            reported_at: jiff::Timestamp::now(),
1239            reporter: "ci".to_owned(),
1240        };
1241        append_deferred(&db_path, &id0, &dv).unwrap();
1242        assert!(trace_exists(&db_path, "acme", &id0).unwrap());
1243        assert!(!trace_exists(&db_path, "acme", "no-such-trace").unwrap());
1244        // Cross-tenant: the same real trace id is invisible to a different tenant.
1245        assert!(!trace_exists(&db_path, "other-tenant", &id0).unwrap());
1246
1247        // The view surfaces the deferred verdict...
1248        let view = load_trace_view(&db_path, "acme", &id0).unwrap().unwrap();
1249        assert_eq!(view.deferred.len(), 1);
1250        assert_eq!(view.deferred[0].gate_id, "tests");
1251        assert_eq!(view.deferred[0].verdict, Verdict::Pass);
1252        // ...the second trace has none.
1253        assert!(
1254            load_trace_view(&db_path, "acme", &id1)
1255                .unwrap()
1256                .unwrap()
1257                .deferred
1258                .is_empty()
1259        );
1260        // Cross-tenant: another tenant cannot read this trace's view at all.
1261        assert!(
1262            load_trace_view(&db_path, "other-tenant", &id0)
1263                .unwrap()
1264                .is_none()
1265        );
1266
1267        // THE INVARIANT: the sealed bodies are untouched, so the chain still verifies. A late
1268        // outcome can never alter a past decision's hash.
1269        let traces = load_all_traces(&db_path).unwrap();
1270        assert!(
1271            traces.iter().all(|t| t.deferred.is_empty()),
1272            "sealed records stay deferred-free"
1273        );
1274        verify_chain(&traces, GENESIS_HASH).unwrap();
1275
1276        let _ = std::fs::remove_file(&db_path);
1277    }
1278
1279    // ── Durable-receipts tests ────────────────────────────────────────────────
1280
1281    /// Test (2): `append_to_spill` writes valid JSON lines to the spill file.
1282    #[test]
1283    fn durable_append_to_spill_writes_valid_json_line() {
1284        let db_path =
1285            std::env::temp_dir().join(format!("firstpass-spill-write-{}.db", uuid::Uuid::now_v7()));
1286        let spill_p = spill_path(&db_path);
1287        let file = std::fs::OpenOptions::new()
1288            .create(true)
1289            .append(true)
1290            .read(true)
1291            .open(&spill_p)
1292            .unwrap();
1293        let handle = Arc::new(std::sync::Mutex::new(file));
1294
1295        let t = sample_trace("t", "s");
1296        append_to_spill(&handle, &t).unwrap();
1297
1298        let content = std::fs::read_to_string(&spill_p).unwrap();
1299        let lines: Vec<&str> = content.lines().collect();
1300        assert_eq!(lines.len(), 1, "one line per spilled trace");
1301        let parsed: Trace = serde_json::from_str(lines[0]).unwrap();
1302        assert_eq!(parsed.trace_id, t.trace_id);
1303
1304        let _ = std::fs::remove_file(&spill_p);
1305    }
1306
1307    /// Test (3): on boot with a pre-populated spill file, the writer drains it and
1308    /// `verify_chain` passes over the full store.
1309    #[tokio::test]
1310    async fn drain_on_boot_restores_spilled_traces_and_chain_is_valid() {
1311        let db_path =
1312            std::env::temp_dir().join(format!("firstpass-drain-boot-{}.db", uuid::Uuid::now_v7()));
1313        let spill_p = spill_path(&db_path);
1314
1315        // Pre-populate spill file with 2 traces (simulating backpressure from a previous run).
1316        let t1 = sample_trace("t", "s1");
1317        let t2 = sample_trace("t", "s2");
1318        {
1319            let file = std::fs::OpenOptions::new()
1320                .create(true)
1321                .append(true)
1322                .read(true)
1323                .open(&spill_p)
1324                .unwrap();
1325            let h = Arc::new(std::sync::Mutex::new(file));
1326            append_to_spill(&h, &t1).unwrap();
1327            append_to_spill(&h, &t2).unwrap();
1328        }
1329
1330        // Open in durable mode — startup drain fires before any channel traffic.
1331        let (tx, _spill, handle) = open_with_receipts(&db_path, ReceiptsMode::Durable).unwrap();
1332        drop(tx); // close the channel immediately
1333        handle.await.unwrap();
1334
1335        let traces = load_all_traces(&db_path).unwrap();
1336        assert_eq!(traces.len(), 2, "both spilled traces recovered");
1337        verify_chain(&traces, GENESIS_HASH).unwrap();
1338
1339        // Spill file must be empty after a successful drain.
1340        let remaining = std::fs::read_to_string(&spill_p).unwrap();
1341        assert!(
1342            remaining.trim().is_empty(),
1343            "spill file must be empty after drain"
1344        );
1345
1346        let _ = std::fs::remove_file(&db_path);
1347        let _ = std::fs::remove_file(&spill_p);
1348    }
1349
1350    /// Test (4): spilled traces land before later channel traces in the store, preserving the
1351    /// hash chain's append-only ordering.
1352    #[tokio::test]
1353    async fn spilled_traces_land_before_later_channel_traces() {
1354        let db_path =
1355            std::env::temp_dir().join(format!("firstpass-spill-order-{}.db", uuid::Uuid::now_v7()));
1356        let spill_p = spill_path(&db_path);
1357
1358        // Pre-populate the spill file (as if these traces were spilled under backpressure).
1359        let spilled = sample_trace("t", "spilled");
1360        {
1361            let file = std::fs::OpenOptions::new()
1362                .create(true)
1363                .append(true)
1364                .read(true)
1365                .open(&spill_p)
1366                .unwrap();
1367            let h = Arc::new(std::sync::Mutex::new(file));
1368            append_to_spill(&h, &spilled).unwrap();
1369        }
1370
1371        // Open in durable mode: startup drain inserts the spilled trace first.
1372        let (tx, _spill, handle) = open_with_receipts(&db_path, ReceiptsMode::Durable).unwrap();
1373        // Send a new channel trace after the store is open (arrives after startup drain).
1374        let channel_trace = sample_trace("t", "channel");
1375        tx.try_send(channel_trace.clone()).unwrap();
1376        drop(tx);
1377        handle.await.unwrap();
1378
1379        let traces = load_all_traces(&db_path).unwrap();
1380        assert_eq!(traces.len(), 2, "spilled + channel trace");
1381        // Spilled trace must come first (lower seq).
1382        assert_eq!(
1383            traces[0].trace_id, spilled.trace_id,
1384            "spilled trace must precede channel trace"
1385        );
1386        assert_eq!(
1387            traces[1].trace_id, channel_trace.trace_id,
1388            "channel trace must follow spilled trace"
1389        );
1390        verify_chain(&traces, GENESIS_HASH).unwrap();
1391
1392        let _ = std::fs::remove_file(&db_path);
1393        let _ = std::fs::remove_file(&spill_p);
1394    }
1395}