1use 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
27fn 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#[derive(Debug, thiserror::Error)]
37pub enum StoreError {
38 #[error("sqlite error: {0}")]
40 Sqlite(#[from] rusqlite::Error),
41 #[error("trace error: {0}")]
43 Trace(#[from] firstpass_core::Error),
44 #[error("json error: {0}")]
46 Json(#[from] serde_json::Error),
47 #[error("spill I/O error: {0}")]
49 Io(#[from] std::io::Error),
50}
51
52pub type SpillHandle = Arc<std::sync::Mutex<std::fs::File>>;
60
61pub(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
68pub fn append_to_spill(handle: &SpillHandle, trace: &Trace) -> Result<(), StoreError> {
77 let line = serde_json::to_string(trace)?;
78 let mut guard = handle.lock().unwrap_or_else(|e| e.into_inner());
81 writeln!(guard, "{line}")?;
82 guard.sync_data()?;
84 Ok(())
85}
86
87pub type TraceSender = mpsc::Sender<Trace>;
91
92pub const TRACE_CHANNEL_CAP: usize = 8192;
95
96pub 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
112pub 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 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
203fn 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
221fn drain_spill(conn: &Connection, spill: &SpillHandle, head: &mut String) {
228 let mut guard = spill.lock().unwrap_or_else(|e| e.into_inner());
231
232 if let Err(e) = guard.seek(SeekFrom::Start(0)) {
234 tracing::error!(%e, "drain_spill: seek failed");
235 return;
236 }
237
238 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 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
281fn 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 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 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 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
356pub 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
380pub 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
400pub 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#[derive(Debug, Clone, Default, PartialEq)]
428pub struct ReceiptsSummary {
429 pub requests: u64,
431 pub spent_usd: f64,
433 pub baseline_usd: f64,
435 pub first_pass: u64,
437 pub escalated: u64,
439 pub gates: [u64; 3],
441 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
463type 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
468pub 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 if c.len() >= 4096 {
502 c.clear();
503 }
504 c.insert(key, (hi, total.clone()));
505 }
506 Ok(total)
507}
508
509fn 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
566pub 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
587pub 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
646pub 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
673pub 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
724pub 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
756pub 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 trace.route_ix.map_or(0, |r| r as usize),
783 rec,
784 cost_usd,
785 )
786 }))
787}
788
789pub 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#[derive(Debug, Clone, PartialEq)]
822pub struct ExperimentPairRow {
823 pub tenant: String,
825 pub route_ix: usize,
827 pub fingerprint: String,
829 pub trace_id: uuid::Uuid,
831 pub x_inc: f64,
833 pub x_ch: f64,
835 pub challenger_cost_usd: f64,
837 pub ts: jiff::Timestamp,
839}
840
841pub 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
868pub 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 #[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), };
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 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 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 #[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 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 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 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 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 assert!(load_tenant_traces(&db_path, "ghost").unwrap().is_empty());
1209
1210 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 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 assert!(!trace_exists(&db_path, "other-tenant", &id0).unwrap());
1246
1247 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 assert!(
1254 load_trace_view(&db_path, "acme", &id1)
1255 .unwrap()
1256 .unwrap()
1257 .deferred
1258 .is_empty()
1259 );
1260 assert!(
1262 load_trace_view(&db_path, "other-tenant", &id0)
1263 .unwrap()
1264 .is_none()
1265 );
1266
1267 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 #[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 #[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 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 let (tx, _spill, handle) = open_with_receipts(&db_path, ReceiptsMode::Durable).unwrap();
1332 drop(tx); 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 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 #[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 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 let (tx, _spill, handle) = open_with_receipts(&db_path, ReceiptsMode::Durable).unwrap();
1373 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 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}