1use super::*;
2
3fn configure_sqlite_connection(connection: &mut Connection) -> Result<(), ReceiptStoreError> {
4 connection.execute_batch(
5 r#"
6 PRAGMA journal_mode = WAL;
7 PRAGMA synchronous = FULL;
8 PRAGMA busy_timeout = 5000;
9 "#,
10 )?;
11 Ok(())
12}
13
14impl SqliteReceiptStore {
15 pub fn open(path: impl AsRef<Path>) -> Result<Self, ReceiptStoreError> {
16 let path = path.as_ref();
17 if let Some(parent) = path.parent() {
18 fs::create_dir_all(parent)?;
19 }
20
21 let mut connection = Connection::open(path)?;
22 configure_sqlite_connection(&mut connection)?;
23 connection.execute_batch(
24 r#"
25 CREATE TABLE IF NOT EXISTS chio_tool_receipts (
26 seq INTEGER PRIMARY KEY AUTOINCREMENT,
27 receipt_id TEXT NOT NULL UNIQUE,
28 timestamp INTEGER NOT NULL,
29 capability_id TEXT NOT NULL,
30 subject_key TEXT,
31 issuer_key TEXT,
32 grant_index INTEGER,
33 tool_server TEXT NOT NULL,
34 tool_name TEXT NOT NULL,
35 decision_kind TEXT NOT NULL,
36 policy_hash TEXT NOT NULL,
37 content_hash TEXT NOT NULL,
38 raw_json TEXT NOT NULL
39 );
40
41 CREATE INDEX IF NOT EXISTS idx_chio_tool_receipts_timestamp
42 ON chio_tool_receipts(timestamp);
43 CREATE INDEX IF NOT EXISTS idx_chio_tool_receipts_capability
44 ON chio_tool_receipts(capability_id);
45 CREATE INDEX IF NOT EXISTS idx_chio_tool_receipts_subject
46 ON chio_tool_receipts(subject_key);
47 CREATE INDEX IF NOT EXISTS idx_chio_tool_receipts_grant
48 ON chio_tool_receipts(capability_id, grant_index);
49 CREATE INDEX IF NOT EXISTS idx_chio_tool_receipts_tool
50 ON chio_tool_receipts(tool_server, tool_name);
51 CREATE INDEX IF NOT EXISTS idx_chio_tool_receipts_decision
52 ON chio_tool_receipts(decision_kind);
53
54 CREATE TABLE IF NOT EXISTS settlement_reconciliations (
55 receipt_id TEXT PRIMARY KEY REFERENCES chio_tool_receipts(receipt_id) ON DELETE CASCADE,
56 reconciliation_state TEXT NOT NULL,
57 note TEXT,
58 updated_at INTEGER NOT NULL
59 );
60 CREATE INDEX IF NOT EXISTS idx_settlement_reconciliations_updated_at
61 ON settlement_reconciliations(updated_at);
62
63 CREATE TABLE IF NOT EXISTS metered_billing_reconciliations (
64 receipt_id TEXT PRIMARY KEY REFERENCES chio_tool_receipts(receipt_id) ON DELETE CASCADE,
65 adapter_kind TEXT NOT NULL,
66 evidence_id TEXT NOT NULL,
67 observed_units INTEGER NOT NULL,
68 billed_cost_units INTEGER NOT NULL,
69 billed_cost_currency TEXT NOT NULL,
70 evidence_sha256 TEXT,
71 recorded_at INTEGER NOT NULL,
72 reconciliation_state TEXT NOT NULL,
73 note TEXT,
74 updated_at INTEGER NOT NULL,
75 UNIQUE (adapter_kind, evidence_id)
76 );
77 CREATE INDEX IF NOT EXISTS idx_metered_billing_reconciliations_updated_at
78 ON metered_billing_reconciliations(updated_at);
79
80 CREATE TABLE IF NOT EXISTS underwriting_decisions (
81 decision_id TEXT PRIMARY KEY,
82 issued_at INTEGER NOT NULL,
83 capability_id TEXT,
84 subject_key TEXT,
85 tool_server TEXT,
86 tool_name TEXT,
87 outcome TEXT NOT NULL,
88 lifecycle_state TEXT NOT NULL,
89 review_state TEXT NOT NULL,
90 risk_class TEXT NOT NULL,
91 supersedes_decision_id TEXT REFERENCES underwriting_decisions(decision_id),
92 superseded_by_decision_id TEXT REFERENCES underwriting_decisions(decision_id),
93 premium_units INTEGER,
94 raw_json TEXT NOT NULL,
95 signer_key TEXT NOT NULL,
96 signature TEXT NOT NULL
97 );
98 CREATE INDEX IF NOT EXISTS idx_underwriting_decisions_issued_at
99 ON underwriting_decisions(issued_at);
100 CREATE INDEX IF NOT EXISTS idx_underwriting_decisions_capability
101 ON underwriting_decisions(capability_id);
102 CREATE INDEX IF NOT EXISTS idx_underwriting_decisions_subject
103 ON underwriting_decisions(subject_key);
104 CREATE INDEX IF NOT EXISTS idx_underwriting_decisions_tool
105 ON underwriting_decisions(tool_server, tool_name);
106 CREATE INDEX IF NOT EXISTS idx_underwriting_decisions_outcome
107 ON underwriting_decisions(outcome);
108 CREATE INDEX IF NOT EXISTS idx_underwriting_decisions_lifecycle
109 ON underwriting_decisions(lifecycle_state);
110
111 CREATE TABLE IF NOT EXISTS underwriting_appeals (
112 appeal_id TEXT PRIMARY KEY,
113 decision_id TEXT NOT NULL REFERENCES underwriting_decisions(decision_id) ON DELETE CASCADE,
114 requested_by TEXT NOT NULL,
115 reason TEXT NOT NULL,
116 status TEXT NOT NULL,
117 note TEXT,
118 created_at INTEGER NOT NULL,
119 updated_at INTEGER NOT NULL,
120 resolved_by TEXT,
121 replacement_decision_id TEXT REFERENCES underwriting_decisions(decision_id)
122 );
123 CREATE INDEX IF NOT EXISTS idx_underwriting_appeals_decision
124 ON underwriting_appeals(decision_id);
125 CREATE INDEX IF NOT EXISTS idx_underwriting_appeals_status
126 ON underwriting_appeals(status);
127 CREATE INDEX IF NOT EXISTS idx_underwriting_appeals_updated_at
128 ON underwriting_appeals(updated_at);
129
130 CREATE TABLE IF NOT EXISTS credit_facilities (
131 facility_id TEXT PRIMARY KEY,
132 issued_at INTEGER NOT NULL,
133 expires_at INTEGER NOT NULL,
134 capability_id TEXT,
135 subject_key TEXT,
136 tool_server TEXT,
137 tool_name TEXT,
138 disposition TEXT NOT NULL,
139 lifecycle_state TEXT NOT NULL,
140 supersedes_facility_id TEXT REFERENCES credit_facilities(facility_id),
141 superseded_by_facility_id TEXT REFERENCES credit_facilities(facility_id),
142 raw_json TEXT NOT NULL,
143 signer_key TEXT NOT NULL,
144 signature TEXT NOT NULL
145 );
146 CREATE INDEX IF NOT EXISTS idx_credit_facilities_issued_at
147 ON credit_facilities(issued_at);
148 CREATE INDEX IF NOT EXISTS idx_credit_facilities_expires_at
149 ON credit_facilities(expires_at);
150 CREATE INDEX IF NOT EXISTS idx_credit_facilities_capability
151 ON credit_facilities(capability_id);
152 CREATE INDEX IF NOT EXISTS idx_credit_facilities_subject
153 ON credit_facilities(subject_key);
154 CREATE INDEX IF NOT EXISTS idx_credit_facilities_tool
155 ON credit_facilities(tool_server, tool_name);
156 CREATE INDEX IF NOT EXISTS idx_credit_facilities_disposition
157 ON credit_facilities(disposition);
158 CREATE INDEX IF NOT EXISTS idx_credit_facilities_lifecycle
159 ON credit_facilities(lifecycle_state);
160
161 CREATE TABLE IF NOT EXISTS credit_bonds (
162 bond_id TEXT PRIMARY KEY,
163 issued_at INTEGER NOT NULL,
164 expires_at INTEGER NOT NULL,
165 facility_id TEXT,
166 capability_id TEXT,
167 subject_key TEXT,
168 tool_server TEXT,
169 tool_name TEXT,
170 disposition TEXT NOT NULL,
171 lifecycle_state TEXT NOT NULL,
172 supersedes_bond_id TEXT REFERENCES credit_bonds(bond_id),
173 superseded_by_bond_id TEXT REFERENCES credit_bonds(bond_id),
174 raw_json TEXT NOT NULL,
175 signer_key TEXT NOT NULL,
176 signature TEXT NOT NULL
177 );
178 CREATE INDEX IF NOT EXISTS idx_credit_bonds_issued_at
179 ON credit_bonds(issued_at);
180 CREATE INDEX IF NOT EXISTS idx_credit_bonds_expires_at
181 ON credit_bonds(expires_at);
182 CREATE INDEX IF NOT EXISTS idx_credit_bonds_facility
183 ON credit_bonds(facility_id);
184 CREATE INDEX IF NOT EXISTS idx_credit_bonds_capability
185 ON credit_bonds(capability_id);
186 CREATE INDEX IF NOT EXISTS idx_credit_bonds_subject
187 ON credit_bonds(subject_key);
188 CREATE INDEX IF NOT EXISTS idx_credit_bonds_tool
189 ON credit_bonds(tool_server, tool_name);
190 CREATE INDEX IF NOT EXISTS idx_credit_bonds_disposition
191 ON credit_bonds(disposition);
192 CREATE INDEX IF NOT EXISTS idx_credit_bonds_lifecycle
193 ON credit_bonds(lifecycle_state);
194
195 CREATE TABLE IF NOT EXISTS liability_providers (
196 provider_record_id TEXT PRIMARY KEY,
197 issued_at INTEGER NOT NULL,
198 provider_id TEXT NOT NULL,
199 lifecycle_state TEXT NOT NULL,
200 supersedes_provider_record_id TEXT REFERENCES liability_providers(provider_record_id),
201 superseded_by_provider_record_id TEXT REFERENCES liability_providers(provider_record_id),
202 raw_json TEXT NOT NULL,
203 signer_key TEXT NOT NULL,
204 signature TEXT NOT NULL
205 );
206 CREATE INDEX IF NOT EXISTS idx_liability_providers_issued_at
207 ON liability_providers(issued_at);
208 CREATE INDEX IF NOT EXISTS idx_liability_providers_provider_id
209 ON liability_providers(provider_id);
210 CREATE INDEX IF NOT EXISTS idx_liability_providers_lifecycle
211 ON liability_providers(lifecycle_state);
212
213 CREATE TABLE IF NOT EXISTS liability_quote_requests (
214 quote_request_id TEXT PRIMARY KEY,
215 issued_at INTEGER NOT NULL,
216 provider_id TEXT NOT NULL,
217 jurisdiction TEXT NOT NULL,
218 coverage_class TEXT NOT NULL,
219 currency TEXT NOT NULL,
220 subject_key TEXT NOT NULL,
221 raw_json TEXT NOT NULL,
222 signer_key TEXT NOT NULL,
223 signature TEXT NOT NULL
224 );
225 CREATE INDEX IF NOT EXISTS idx_liability_quote_requests_issued_at
226 ON liability_quote_requests(issued_at);
227 CREATE INDEX IF NOT EXISTS idx_liability_quote_requests_provider
228 ON liability_quote_requests(provider_id);
229 CREATE INDEX IF NOT EXISTS idx_liability_quote_requests_subject
230 ON liability_quote_requests(subject_key);
231
232 CREATE TABLE IF NOT EXISTS liability_quote_responses (
233 quote_response_id TEXT PRIMARY KEY,
234 issued_at INTEGER NOT NULL,
235 quote_request_id TEXT NOT NULL REFERENCES liability_quote_requests(quote_request_id),
236 provider_id TEXT NOT NULL,
237 disposition TEXT NOT NULL,
238 expires_at INTEGER,
239 supersedes_quote_response_id TEXT REFERENCES liability_quote_responses(quote_response_id),
240 superseded_by_quote_response_id TEXT REFERENCES liability_quote_responses(quote_response_id),
241 raw_json TEXT NOT NULL,
242 signer_key TEXT NOT NULL,
243 signature TEXT NOT NULL
244 );
245 CREATE INDEX IF NOT EXISTS idx_liability_quote_responses_issued_at
246 ON liability_quote_responses(issued_at);
247 CREATE INDEX IF NOT EXISTS idx_liability_quote_responses_request
248 ON liability_quote_responses(quote_request_id);
249 CREATE INDEX IF NOT EXISTS idx_liability_quote_responses_provider
250 ON liability_quote_responses(provider_id);
251
252 CREATE TABLE IF NOT EXISTS liability_pricing_authorities (
253 authority_id TEXT PRIMARY KEY,
254 issued_at INTEGER NOT NULL,
255 quote_request_id TEXT NOT NULL REFERENCES liability_quote_requests(quote_request_id),
256 provider_id TEXT NOT NULL,
257 facility_id TEXT NOT NULL,
258 underwriting_decision_id TEXT NOT NULL,
259 expires_at INTEGER NOT NULL,
260 raw_json TEXT NOT NULL,
261 signer_key TEXT NOT NULL,
262 signature TEXT NOT NULL
263 );
264 CREATE UNIQUE INDEX IF NOT EXISTS idx_liability_pricing_authorities_request
265 ON liability_pricing_authorities(quote_request_id);
266 CREATE INDEX IF NOT EXISTS idx_liability_pricing_authorities_provider
267 ON liability_pricing_authorities(provider_id);
268 CREATE INDEX IF NOT EXISTS idx_liability_pricing_authorities_facility
269 ON liability_pricing_authorities(facility_id);
270
271 CREATE TABLE IF NOT EXISTS liability_placements (
272 placement_id TEXT PRIMARY KEY,
273 issued_at INTEGER NOT NULL,
274 quote_request_id TEXT NOT NULL REFERENCES liability_quote_requests(quote_request_id),
275 quote_response_id TEXT NOT NULL REFERENCES liability_quote_responses(quote_response_id),
276 provider_id TEXT NOT NULL,
277 raw_json TEXT NOT NULL,
278 signer_key TEXT NOT NULL,
279 signature TEXT NOT NULL
280 );
281 CREATE UNIQUE INDEX IF NOT EXISTS idx_liability_placements_request
282 ON liability_placements(quote_request_id);
283 CREATE UNIQUE INDEX IF NOT EXISTS idx_liability_placements_response
284 ON liability_placements(quote_response_id);
285 CREATE INDEX IF NOT EXISTS idx_liability_placements_provider
286 ON liability_placements(provider_id);
287
288 CREATE TABLE IF NOT EXISTS liability_bound_coverages (
289 bound_coverage_id TEXT PRIMARY KEY,
290 issued_at INTEGER NOT NULL,
291 quote_request_id TEXT NOT NULL REFERENCES liability_quote_requests(quote_request_id),
292 quote_response_id TEXT NOT NULL REFERENCES liability_quote_responses(quote_response_id),
293 placement_id TEXT NOT NULL REFERENCES liability_placements(placement_id),
294 provider_id TEXT NOT NULL,
295 raw_json TEXT NOT NULL,
296 signer_key TEXT NOT NULL,
297 signature TEXT NOT NULL
298 );
299 CREATE UNIQUE INDEX IF NOT EXISTS idx_liability_bound_coverages_request
300 ON liability_bound_coverages(quote_request_id);
301 CREATE UNIQUE INDEX IF NOT EXISTS idx_liability_bound_coverages_response
302 ON liability_bound_coverages(quote_response_id);
303 CREATE UNIQUE INDEX IF NOT EXISTS idx_liability_bound_coverages_placement
304 ON liability_bound_coverages(placement_id);
305 CREATE INDEX IF NOT EXISTS idx_liability_bound_coverages_provider
306 ON liability_bound_coverages(provider_id);
307
308 CREATE TABLE IF NOT EXISTS liability_auto_bind_decisions (
309 decision_id TEXT PRIMARY KEY,
310 issued_at INTEGER NOT NULL,
311 quote_request_id TEXT NOT NULL REFERENCES liability_quote_requests(quote_request_id),
312 quote_response_id TEXT NOT NULL REFERENCES liability_quote_responses(quote_response_id),
313 authority_id TEXT NOT NULL REFERENCES liability_pricing_authorities(authority_id),
314 provider_id TEXT NOT NULL,
315 disposition TEXT NOT NULL,
316 raw_json TEXT NOT NULL,
317 signer_key TEXT NOT NULL,
318 signature TEXT NOT NULL
319 );
320 CREATE UNIQUE INDEX IF NOT EXISTS idx_liability_auto_bind_decisions_response
321 ON liability_auto_bind_decisions(quote_response_id);
322 CREATE INDEX IF NOT EXISTS idx_liability_auto_bind_decisions_request
323 ON liability_auto_bind_decisions(quote_request_id);
324 CREATE INDEX IF NOT EXISTS idx_liability_auto_bind_decisions_authority
325 ON liability_auto_bind_decisions(authority_id);
326
327 CREATE TABLE IF NOT EXISTS liability_claim_packages (
328 claim_id TEXT PRIMARY KEY,
329 issued_at INTEGER NOT NULL,
330 provider_id TEXT NOT NULL,
331 policy_number TEXT NOT NULL,
332 jurisdiction TEXT NOT NULL,
333 subject_key TEXT NOT NULL,
334 claim_event_at INTEGER NOT NULL,
335 raw_json TEXT NOT NULL,
336 signer_key TEXT NOT NULL,
337 signature TEXT NOT NULL
338 );
339 CREATE INDEX IF NOT EXISTS idx_liability_claim_packages_issued_at
340 ON liability_claim_packages(issued_at);
341 CREATE INDEX IF NOT EXISTS idx_liability_claim_packages_provider
342 ON liability_claim_packages(provider_id);
343 CREATE INDEX IF NOT EXISTS idx_liability_claim_packages_policy_number
344 ON liability_claim_packages(policy_number);
345 CREATE INDEX IF NOT EXISTS idx_liability_claim_packages_subject
346 ON liability_claim_packages(subject_key);
347
348 CREATE TABLE IF NOT EXISTS liability_claim_responses (
349 claim_response_id TEXT PRIMARY KEY,
350 issued_at INTEGER NOT NULL,
351 claim_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_packages(claim_id),
352 provider_id TEXT NOT NULL,
353 disposition TEXT NOT NULL,
354 raw_json TEXT NOT NULL,
355 signer_key TEXT NOT NULL,
356 signature TEXT NOT NULL
357 );
358 CREATE INDEX IF NOT EXISTS idx_liability_claim_responses_issued_at
359 ON liability_claim_responses(issued_at);
360 CREATE INDEX IF NOT EXISTS idx_liability_claim_responses_provider
361 ON liability_claim_responses(provider_id);
362
363 CREATE TABLE IF NOT EXISTS liability_claim_disputes (
364 dispute_id TEXT PRIMARY KEY,
365 issued_at INTEGER NOT NULL,
366 claim_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_packages(claim_id),
367 claim_response_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_responses(claim_response_id),
368 provider_id TEXT NOT NULL,
369 raw_json TEXT NOT NULL,
370 signer_key TEXT NOT NULL,
371 signature TEXT NOT NULL
372 );
373 CREATE INDEX IF NOT EXISTS idx_liability_claim_disputes_issued_at
374 ON liability_claim_disputes(issued_at);
375 CREATE INDEX IF NOT EXISTS idx_liability_claim_disputes_provider
376 ON liability_claim_disputes(provider_id);
377
378 CREATE TABLE IF NOT EXISTS liability_claim_adjudications (
379 adjudication_id TEXT PRIMARY KEY,
380 issued_at INTEGER NOT NULL,
381 claim_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_packages(claim_id),
382 dispute_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_disputes(dispute_id),
383 outcome TEXT NOT NULL,
384 raw_json TEXT NOT NULL,
385 signer_key TEXT NOT NULL,
386 signature TEXT NOT NULL
387 );
388 CREATE INDEX IF NOT EXISTS idx_liability_claim_adjudications_issued_at
389 ON liability_claim_adjudications(issued_at);
390
391 CREATE TABLE IF NOT EXISTS liability_claim_payout_instructions (
392 payout_instruction_id TEXT PRIMARY KEY,
393 issued_at INTEGER NOT NULL,
394 claim_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_packages(claim_id),
395 adjudication_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_adjudications(adjudication_id),
396 payout_amount_units INTEGER NOT NULL,
397 payout_amount_currency TEXT NOT NULL,
398 raw_json TEXT NOT NULL,
399 signer_key TEXT NOT NULL,
400 signature TEXT NOT NULL
401 );
402 CREATE INDEX IF NOT EXISTS idx_liability_claim_payout_instructions_issued_at
403 ON liability_claim_payout_instructions(issued_at);
404
405 CREATE TABLE IF NOT EXISTS liability_claim_payout_receipts (
406 payout_receipt_id TEXT PRIMARY KEY,
407 issued_at INTEGER NOT NULL,
408 claim_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_packages(claim_id),
409 payout_instruction_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_payout_instructions(payout_instruction_id),
410 reconciliation_state TEXT NOT NULL,
411 raw_json TEXT NOT NULL,
412 signer_key TEXT NOT NULL,
413 signature TEXT NOT NULL
414 );
415 CREATE INDEX IF NOT EXISTS idx_liability_claim_payout_receipts_issued_at
416 ON liability_claim_payout_receipts(issued_at);
417
418 CREATE TABLE IF NOT EXISTS liability_claim_settlement_instructions (
419 settlement_instruction_id TEXT PRIMARY KEY,
420 issued_at INTEGER NOT NULL,
421 claim_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_packages(claim_id),
422 payout_receipt_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_payout_receipts(payout_receipt_id),
423 settlement_kind TEXT NOT NULL,
424 payer_role TEXT NOT NULL,
425 payer_id TEXT NOT NULL,
426 payee_role TEXT NOT NULL,
427 payee_id TEXT NOT NULL,
428 settlement_amount_units INTEGER NOT NULL,
429 settlement_amount_currency TEXT NOT NULL,
430 raw_json TEXT NOT NULL,
431 signer_key TEXT NOT NULL,
432 signature TEXT NOT NULL
433 );
434 CREATE INDEX IF NOT EXISTS idx_liability_claim_settlement_instructions_issued_at
435 ON liability_claim_settlement_instructions(issued_at);
436
437 CREATE TABLE IF NOT EXISTS liability_claim_settlement_receipts (
438 settlement_receipt_id TEXT PRIMARY KEY,
439 issued_at INTEGER NOT NULL,
440 claim_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_packages(claim_id),
441 settlement_instruction_id TEXT NOT NULL UNIQUE REFERENCES liability_claim_settlement_instructions(settlement_instruction_id),
442 reconciliation_state TEXT NOT NULL,
443 raw_json TEXT NOT NULL,
444 signer_key TEXT NOT NULL,
445 signature TEXT NOT NULL
446 );
447 CREATE INDEX IF NOT EXISTS idx_liability_claim_settlement_receipts_issued_at
448 ON liability_claim_settlement_receipts(issued_at);
449
450 CREATE TABLE IF NOT EXISTS credit_loss_lifecycle (
451 event_id TEXT PRIMARY KEY,
452 issued_at INTEGER NOT NULL,
453 bond_id TEXT NOT NULL REFERENCES credit_bonds(bond_id),
454 facility_id TEXT,
455 capability_id TEXT,
456 subject_key TEXT,
457 tool_server TEXT,
458 tool_name TEXT,
459 event_kind TEXT NOT NULL,
460 projected_bond_lifecycle_state TEXT NOT NULL,
461 raw_json TEXT NOT NULL,
462 signer_key TEXT NOT NULL,
463 signature TEXT NOT NULL
464 );
465 CREATE INDEX IF NOT EXISTS idx_credit_loss_lifecycle_issued_at
466 ON credit_loss_lifecycle(issued_at);
467 CREATE INDEX IF NOT EXISTS idx_credit_loss_lifecycle_bond
468 ON credit_loss_lifecycle(bond_id);
469 CREATE INDEX IF NOT EXISTS idx_credit_loss_lifecycle_facility
470 ON credit_loss_lifecycle(facility_id);
471 CREATE INDEX IF NOT EXISTS idx_credit_loss_lifecycle_capability
472 ON credit_loss_lifecycle(capability_id);
473 CREATE INDEX IF NOT EXISTS idx_credit_loss_lifecycle_subject
474 ON credit_loss_lifecycle(subject_key);
475 CREATE INDEX IF NOT EXISTS idx_credit_loss_lifecycle_tool
476 ON credit_loss_lifecycle(tool_server, tool_name);
477 CREATE INDEX IF NOT EXISTS idx_credit_loss_lifecycle_kind
478 ON credit_loss_lifecycle(event_kind);
479
480 CREATE TABLE IF NOT EXISTS chio_child_receipts (
481 seq INTEGER PRIMARY KEY AUTOINCREMENT,
482 receipt_id TEXT NOT NULL UNIQUE,
483 timestamp INTEGER NOT NULL,
484 session_id TEXT NOT NULL,
485 parent_request_id TEXT NOT NULL,
486 request_id TEXT NOT NULL,
487 operation_kind TEXT NOT NULL,
488 terminal_state TEXT NOT NULL,
489 policy_hash TEXT NOT NULL,
490 outcome_hash TEXT NOT NULL,
491 raw_json TEXT NOT NULL
492 );
493
494 CREATE INDEX IF NOT EXISTS idx_chio_child_receipts_timestamp
495 ON chio_child_receipts(timestamp);
496 CREATE INDEX IF NOT EXISTS idx_chio_child_receipts_session
497 ON chio_child_receipts(session_id);
498 CREATE INDEX IF NOT EXISTS idx_chio_child_receipts_parent
499 ON chio_child_receipts(parent_request_id);
500 CREATE INDEX IF NOT EXISTS idx_chio_child_receipts_request
501 ON chio_child_receipts(request_id);
502
503 CREATE TABLE IF NOT EXISTS claim_receipt_log_entries (
504 entry_seq INTEGER PRIMARY KEY AUTOINCREMENT,
505 receipt_id TEXT NOT NULL UNIQUE,
506 receipt_kind TEXT NOT NULL,
507 source_seq INTEGER NOT NULL,
508 timestamp INTEGER NOT NULL,
509 capability_id TEXT,
510 session_id TEXT,
511 parent_request_id TEXT,
512 request_id TEXT,
513 subject_key TEXT,
514 issuer_key TEXT,
515 tool_server TEXT,
516 tool_name TEXT,
517 raw_json TEXT NOT NULL,
518 CHECK (receipt_kind IN ('tool_receipt', 'child_receipt')),
519 CHECK (
520 (receipt_kind = 'tool_receipt'
521 AND capability_id IS NOT NULL
522 AND session_id IS NULL
523 AND parent_request_id IS NULL
524 AND request_id IS NULL
525 AND tool_server IS NOT NULL
526 AND tool_name IS NOT NULL)
527 OR
528 (receipt_kind = 'child_receipt'
529 AND capability_id IS NULL
530 AND session_id IS NOT NULL
531 AND parent_request_id IS NOT NULL
532 AND request_id IS NOT NULL
533 AND tool_server IS NULL
534 AND tool_name IS NULL)
535 )
536 );
537 CREATE UNIQUE INDEX IF NOT EXISTS idx_claim_receipt_log_kind_source
538 ON claim_receipt_log_entries(receipt_kind, source_seq);
539 CREATE INDEX IF NOT EXISTS idx_claim_receipt_log_timestamp
540 ON claim_receipt_log_entries(timestamp, entry_seq);
541 CREATE INDEX IF NOT EXISTS idx_claim_receipt_log_tool
542 ON claim_receipt_log_entries(tool_server, tool_name, timestamp)
543 WHERE receipt_kind = 'tool_receipt';
544 CREATE INDEX IF NOT EXISTS idx_claim_receipt_log_child_request
545 ON claim_receipt_log_entries(session_id, request_id, timestamp)
546 WHERE receipt_kind = 'child_receipt';
547
548 CREATE TRIGGER IF NOT EXISTS chio_tool_receipts_project_claim_log_entry
549 AFTER INSERT ON chio_tool_receipts
550 BEGIN
551 INSERT OR IGNORE INTO claim_receipt_log_entries (
552 receipt_id,
553 receipt_kind,
554 source_seq,
555 timestamp,
556 capability_id,
557 session_id,
558 parent_request_id,
559 request_id,
560 subject_key,
561 issuer_key,
562 tool_server,
563 tool_name,
564 raw_json
565 ) VALUES (
566 NEW.receipt_id,
567 'tool_receipt',
568 NEW.seq,
569 NEW.timestamp,
570 NEW.capability_id,
571 NULL,
572 NULL,
573 NULL,
574 NEW.subject_key,
575 NEW.issuer_key,
576 NEW.tool_server,
577 NEW.tool_name,
578 NEW.raw_json
579 );
580 END;
581
582 CREATE TRIGGER IF NOT EXISTS chio_child_receipts_project_claim_log_entry
583 AFTER INSERT ON chio_child_receipts
584 BEGIN
585 INSERT OR IGNORE INTO claim_receipt_log_entries (
586 receipt_id,
587 receipt_kind,
588 source_seq,
589 timestamp,
590 capability_id,
591 session_id,
592 parent_request_id,
593 request_id,
594 subject_key,
595 issuer_key,
596 tool_server,
597 tool_name,
598 raw_json
599 ) VALUES (
600 NEW.receipt_id,
601 'child_receipt',
602 NEW.seq,
603 NEW.timestamp,
604 NULL,
605 NEW.session_id,
606 NEW.parent_request_id,
607 NEW.request_id,
608 NULL,
609 NULL,
610 NULL,
611 NULL,
612 NEW.raw_json
613 );
614 END;
615
616 CREATE TABLE IF NOT EXISTS session_anchors (
617 anchor_id TEXT PRIMARY KEY,
618 session_id TEXT NOT NULL,
619 auth_context_fingerprint TEXT NOT NULL,
620 issued_at INTEGER NOT NULL,
621 supersedes_anchor_id TEXT REFERENCES session_anchors(anchor_id),
622 is_current INTEGER NOT NULL DEFAULT 1,
623 source_kind TEXT NOT NULL,
624 json_sha256 TEXT NOT NULL,
625 raw_json TEXT NOT NULL
626 );
627 CREATE INDEX IF NOT EXISTS idx_session_anchors_session
628 ON session_anchors(session_id, issued_at DESC);
629 CREATE INDEX IF NOT EXISTS idx_session_anchors_supersedes
630 ON session_anchors(supersedes_anchor_id);
631 CREATE UNIQUE INDEX IF NOT EXISTS idx_session_anchors_current
632 ON session_anchors(session_id)
633 WHERE is_current = 1;
634
635 CREATE TABLE IF NOT EXISTS request_lineage (
636 session_id TEXT NOT NULL,
637 request_id TEXT NOT NULL,
638 parent_request_id TEXT,
639 session_anchor_id TEXT REFERENCES session_anchors(anchor_id),
640 recorded_at INTEGER NOT NULL,
641 request_fingerprint TEXT,
642 source_kind TEXT NOT NULL,
643 json_sha256 TEXT NOT NULL,
644 raw_json TEXT NOT NULL,
645 PRIMARY KEY (session_id, request_id)
646 );
647 CREATE INDEX IF NOT EXISTS idx_request_lineage_parent
648 ON request_lineage(session_id, parent_request_id);
649 CREATE INDEX IF NOT EXISTS idx_request_lineage_anchor
650 ON request_lineage(session_anchor_id);
651 CREATE UNIQUE INDEX IF NOT EXISTS idx_request_lineage_fingerprint
652 ON request_lineage(session_id, COALESCE(session_anchor_id, ''), request_fingerprint)
653 WHERE request_fingerprint IS NOT NULL;
654
655 CREATE TABLE IF NOT EXISTS receipt_lineage_statements (
656 receipt_id TEXT PRIMARY KEY,
657 statement_id TEXT,
658 request_id TEXT,
659 session_id TEXT,
660 session_anchor_id TEXT REFERENCES session_anchors(anchor_id),
661 chain_id TEXT,
662 parent_request_id TEXT,
663 parent_receipt_id TEXT,
664 evidence_class TEXT,
665 evidence_sources_json TEXT,
666 verified_session_anchor INTEGER NOT NULL DEFAULT 0,
667 verified_parent_request INTEGER NOT NULL DEFAULT 0,
668 verified_parent_receipt INTEGER NOT NULL DEFAULT 0,
669 replay_protected INTEGER NOT NULL DEFAULT 0,
670 recorded_at INTEGER NOT NULL,
671 source_kind TEXT NOT NULL,
672 json_sha256 TEXT NOT NULL,
673 raw_json TEXT NOT NULL
674 );
675 CREATE INDEX IF NOT EXISTS idx_receipt_lineage_request
676 ON receipt_lineage_statements(session_id, request_id);
677 CREATE UNIQUE INDEX IF NOT EXISTS idx_receipt_lineage_statement_id
678 ON receipt_lineage_statements(statement_id)
679 WHERE statement_id IS NOT NULL;
680 CREATE INDEX IF NOT EXISTS idx_receipt_lineage_parent_request
681 ON receipt_lineage_statements(session_id, parent_request_id);
682 CREATE INDEX IF NOT EXISTS idx_receipt_lineage_parent_receipt
683 ON receipt_lineage_statements(parent_receipt_id);
684 CREATE INDEX IF NOT EXISTS idx_receipt_lineage_anchor
685 ON receipt_lineage_statements(session_anchor_id);
686 CREATE UNIQUE INDEX IF NOT EXISTS idx_receipt_lineage_request_anchor
687 ON receipt_lineage_statements(session_id, COALESCE(session_anchor_id, ''), request_id)
688 WHERE session_id IS NOT NULL
689 AND request_id IS NOT NULL;
690
691 CREATE TABLE IF NOT EXISTS kernel_checkpoints (
692 id INTEGER PRIMARY KEY AUTOINCREMENT,
693 checkpoint_seq INTEGER NOT NULL UNIQUE,
694 batch_start_seq INTEGER NOT NULL,
695 batch_end_seq INTEGER NOT NULL,
696 tree_size INTEGER NOT NULL,
697 merkle_root TEXT NOT NULL,
698 issued_at INTEGER NOT NULL,
699 statement_json TEXT NOT NULL,
700 signature TEXT NOT NULL,
701 kernel_key TEXT NOT NULL
702 );
703 CREATE INDEX IF NOT EXISTS idx_kernel_checkpoints_batch_end
704 ON kernel_checkpoints(batch_end_seq);
705
706 CREATE TABLE IF NOT EXISTS checkpoint_tree_heads (
707 checkpoint_seq INTEGER PRIMARY KEY
708 REFERENCES kernel_checkpoints(checkpoint_seq) ON DELETE CASCADE,
709 batch_start_seq INTEGER NOT NULL,
710 batch_end_seq INTEGER NOT NULL,
711 tree_size INTEGER NOT NULL,
712 merkle_root TEXT NOT NULL,
713 issued_at INTEGER NOT NULL,
714 kernel_key TEXT NOT NULL,
715 previous_checkpoint_sha256 TEXT,
716 statement_json TEXT NOT NULL,
717 signature TEXT NOT NULL
718 );
719 CREATE INDEX IF NOT EXISTS idx_checkpoint_tree_heads_tree_size
720 ON checkpoint_tree_heads(tree_size);
721 CREATE INDEX IF NOT EXISTS idx_checkpoint_tree_heads_previous
722 ON checkpoint_tree_heads(previous_checkpoint_sha256);
723
724 CREATE TABLE IF NOT EXISTS checkpoint_predecessor_witnesses (
725 predecessor_checkpoint_seq INTEGER NOT NULL
726 REFERENCES checkpoint_tree_heads(checkpoint_seq) ON DELETE CASCADE,
727 witness_checkpoint_seq INTEGER PRIMARY KEY
728 REFERENCES checkpoint_tree_heads(checkpoint_seq) ON DELETE CASCADE,
729 previous_checkpoint_sha256 TEXT NOT NULL,
730 witnessed_at INTEGER NOT NULL,
731 witness_statement_json TEXT NOT NULL
732 );
733 CREATE INDEX IF NOT EXISTS idx_checkpoint_predecessor_witnesses_predecessor
734 ON checkpoint_predecessor_witnesses(predecessor_checkpoint_seq);
735 CREATE INDEX IF NOT EXISTS idx_checkpoint_predecessor_witnesses_previous
736 ON checkpoint_predecessor_witnesses(previous_checkpoint_sha256);
737
738 CREATE TABLE IF NOT EXISTS checkpoint_publication_metadata (
739 checkpoint_seq INTEGER PRIMARY KEY
740 REFERENCES kernel_checkpoints(checkpoint_seq) ON DELETE CASCADE,
741 publication_schema TEXT NOT NULL,
742 merkle_root TEXT NOT NULL,
743 published_at INTEGER NOT NULL,
744 kernel_key TEXT NOT NULL,
745 log_tree_size INTEGER NOT NULL,
746 entry_start_seq INTEGER NOT NULL,
747 entry_end_seq INTEGER NOT NULL,
748 previous_checkpoint_sha256 TEXT
749 );
750 CREATE INDEX IF NOT EXISTS idx_checkpoint_publication_metadata_published_at
751 ON checkpoint_publication_metadata(published_at);
752 CREATE INDEX IF NOT EXISTS idx_checkpoint_publication_metadata_log_tree_size
753 ON checkpoint_publication_metadata(log_tree_size);
754 CREATE INDEX IF NOT EXISTS idx_checkpoint_publication_metadata_previous
755 ON checkpoint_publication_metadata(previous_checkpoint_sha256);
756
757 CREATE TABLE IF NOT EXISTS checkpoint_publication_trust_anchor_bindings (
758 checkpoint_seq INTEGER PRIMARY KEY
759 REFERENCES kernel_checkpoints(checkpoint_seq) ON DELETE CASCADE,
760 binding_json TEXT NOT NULL
761 );
762
763 CREATE TRIGGER IF NOT EXISTS kernel_checkpoints_project_tree_head
764 AFTER INSERT ON kernel_checkpoints
765 BEGIN
766 INSERT OR IGNORE INTO checkpoint_tree_heads (
767 checkpoint_seq,
768 batch_start_seq,
769 batch_end_seq,
770 tree_size,
771 merkle_root,
772 issued_at,
773 kernel_key,
774 previous_checkpoint_sha256,
775 statement_json,
776 signature
777 ) VALUES (
778 NEW.checkpoint_seq,
779 NEW.batch_start_seq,
780 NEW.batch_end_seq,
781 NEW.tree_size,
782 NEW.merkle_root,
783 NEW.issued_at,
784 NEW.kernel_key,
785 CAST(json_extract(NEW.statement_json, '$.previous_checkpoint_sha256') AS TEXT),
786 NEW.statement_json,
787 NEW.signature
788 );
789
790 INSERT OR IGNORE INTO checkpoint_predecessor_witnesses (
791 predecessor_checkpoint_seq,
792 witness_checkpoint_seq,
793 previous_checkpoint_sha256,
794 witnessed_at,
795 witness_statement_json
796 )
797 SELECT
798 NEW.checkpoint_seq - 1,
799 NEW.checkpoint_seq,
800 CAST(json_extract(NEW.statement_json, '$.previous_checkpoint_sha256') AS TEXT),
801 NEW.issued_at,
802 NEW.statement_json
803 WHERE json_extract(NEW.statement_json, '$.previous_checkpoint_sha256') IS NOT NULL;
804
805 INSERT OR IGNORE INTO checkpoint_publication_metadata (
806 checkpoint_seq,
807 publication_schema,
808 merkle_root,
809 published_at,
810 kernel_key,
811 log_tree_size,
812 entry_start_seq,
813 entry_end_seq,
814 previous_checkpoint_sha256
815 ) VALUES (
816 NEW.checkpoint_seq,
817 'chio.checkpoint_publication.v1',
818 NEW.merkle_root,
819 NEW.issued_at,
820 NEW.kernel_key,
821 NEW.batch_end_seq,
822 NEW.batch_start_seq,
823 NEW.batch_end_seq,
824 CAST(json_extract(NEW.statement_json, '$.previous_checkpoint_sha256') AS TEXT)
825 );
826 END;
827
828 CREATE TABLE IF NOT EXISTS capability_lineage (
829 capability_id TEXT PRIMARY KEY,
830 subject_key TEXT NOT NULL,
831 issuer_key TEXT NOT NULL,
832 issued_at INTEGER NOT NULL,
833 expires_at INTEGER NOT NULL,
834 grants_json TEXT NOT NULL,
835 delegation_depth INTEGER NOT NULL DEFAULT 0,
836 parent_capability_id TEXT REFERENCES capability_lineage(capability_id)
837 );
838 CREATE INDEX IF NOT EXISTS idx_capability_lineage_subject
839 ON capability_lineage(subject_key);
840 CREATE INDEX IF NOT EXISTS idx_capability_lineage_issuer
841 ON capability_lineage(issuer_key);
842 CREATE INDEX IF NOT EXISTS idx_capability_lineage_issued_at
843 ON capability_lineage(issued_at);
844 CREATE INDEX IF NOT EXISTS idx_capability_lineage_parent
845 ON capability_lineage(parent_capability_id);
846
847 CREATE TABLE IF NOT EXISTS federated_lineage_bridges (
848 local_capability_id TEXT PRIMARY KEY REFERENCES capability_lineage(capability_id) ON DELETE CASCADE,
849 parent_capability_id TEXT NOT NULL,
850 share_id TEXT REFERENCES federated_evidence_shares(share_id)
851 );
852 CREATE INDEX IF NOT EXISTS idx_federated_lineage_bridges_parent
853 ON federated_lineage_bridges(parent_capability_id);
854
855 CREATE TABLE IF NOT EXISTS federated_evidence_shares (
856 share_id TEXT PRIMARY KEY,
857 manifest_hash TEXT NOT NULL,
858 imported_at INTEGER NOT NULL,
859 exported_at INTEGER NOT NULL,
860 issuer TEXT NOT NULL,
861 partner TEXT NOT NULL,
862 signer_public_key TEXT NOT NULL,
863 require_proofs INTEGER NOT NULL DEFAULT 0,
864 query_json TEXT NOT NULL
865 );
866 CREATE INDEX IF NOT EXISTS idx_federated_evidence_shares_imported_at
867 ON federated_evidence_shares(imported_at);
868
869 CREATE TABLE IF NOT EXISTS federated_share_tool_receipts (
870 share_id TEXT NOT NULL REFERENCES federated_evidence_shares(share_id) ON DELETE CASCADE,
871 seq INTEGER NOT NULL,
872 receipt_id TEXT NOT NULL,
873 timestamp INTEGER NOT NULL,
874 capability_id TEXT NOT NULL,
875 subject_key TEXT,
876 issuer_key TEXT,
877 raw_json TEXT NOT NULL,
878 PRIMARY KEY (share_id, seq),
879 UNIQUE (share_id, receipt_id)
880 );
881 CREATE INDEX IF NOT EXISTS idx_federated_share_receipts_capability
882 ON federated_share_tool_receipts(capability_id);
883 CREATE INDEX IF NOT EXISTS idx_federated_share_receipts_subject
884 ON federated_share_tool_receipts(subject_key);
885
886 CREATE TABLE IF NOT EXISTS federated_share_capability_lineage (
887 share_id TEXT NOT NULL REFERENCES federated_evidence_shares(share_id) ON DELETE CASCADE,
888 capability_id TEXT NOT NULL,
889 subject_key TEXT NOT NULL,
890 issuer_key TEXT NOT NULL,
891 issued_at INTEGER NOT NULL,
892 expires_at INTEGER NOT NULL,
893 grants_json TEXT NOT NULL,
894 delegation_depth INTEGER NOT NULL DEFAULT 0,
895 parent_capability_id TEXT,
896 PRIMARY KEY (share_id, capability_id)
897 );
898 CREATE INDEX IF NOT EXISTS idx_federated_share_lineage_capability
899 ON federated_share_capability_lineage(capability_id);
900 CREATE INDEX IF NOT EXISTS idx_federated_share_lineage_subject
901 ON federated_share_capability_lineage(subject_key);
902 "#,
903 )?;
904 ensure_tool_receipt_attribution_columns(&connection)?;
905 super::support::ensure_receipt_lineage_statement_columns(&connection)?;
906 backfill_tool_receipt_attribution_columns(&connection)?;
907 super::support::backfill_provenance_lineage_tables(&mut connection)?;
908 super::support::backfill_claim_receipt_log_entries(&mut connection)?;
909 super::support::backfill_checkpoint_transparency_projections(&mut connection)?;
910 super::support::ensure_transparency_projection_guards(&connection)?;
911
912 drop(connection);
913
914 let manager = SqliteConnectionManager::file(path).with_init(|connection| {
915 configure_sqlite_connection(connection).map_err(|error| match error {
916 ReceiptStoreError::Sqlite(error) => error,
917 other => rusqlite::Error::InvalidParameterName(other.to_string()),
918 })
919 });
920 let pool = Pool::builder()
921 .max_size(8)
922 .build(manager)
923 .map_err(|error| ReceiptStoreError::Pool(error.to_string()))?;
924
925 Ok(Self {
926 pool,
927 strict_tenant_isolation: std::sync::atomic::AtomicBool::new(true),
928 })
929 }
930
931 pub fn tool_receipt_count(&self) -> Result<u64, ReceiptStoreError> {
932 let count =
933 self.connection()?
934 .query_row("SELECT COUNT(*) FROM chio_tool_receipts", [], |row| {
935 row.get::<_, i64>(0)
936 })?;
937 sqlite_u64(count, "chio_tool_receipts count")
938 }
939
940 pub fn child_receipt_count(&self) -> Result<u64, ReceiptStoreError> {
941 let count = self.connection()?.query_row(
942 "SELECT COUNT(*) FROM chio_child_receipts",
943 [],
944 |row| row.get::<_, i64>(0),
945 )?;
946 sqlite_u64(count, "chio_child_receipts count")
947 }
948
949 pub(crate) fn load_underwriting_appeals_by_decision(
950 &self,
951 ) -> Result<BTreeMap<String, Vec<UnderwritingAppealRecord>>, ReceiptStoreError> {
952 let mut appeals_by_decision = BTreeMap::new();
953 let connection = self.connection()?;
954 for appeal in load_underwriting_appeal_rows(&connection)? {
955 appeals_by_decision
956 .entry(appeal.decision_id.clone())
957 .or_insert_with(Vec::new)
958 .push(appeal);
959 }
960 Ok(appeals_by_decision)
961 }
962
963 pub fn list_tool_receipts(
964 &self,
965 limit: usize,
966 capability_id: Option<&str>,
967 tool_server: Option<&str>,
968 tool_name: Option<&str>,
969 decision_kind: Option<&str>,
970 ) -> Result<Vec<ChioReceipt>, ReceiptStoreError> {
971 let connection = self.connection()?;
972 let mut statement = connection.prepare(
973 r#"
974 SELECT seq, raw_json
975 FROM chio_tool_receipts
976 WHERE (?1 IS NULL OR capability_id = ?1)
977 AND (?2 IS NULL OR tool_server = ?2)
978 AND (?3 IS NULL OR tool_name = ?3)
979 AND (?4 IS NULL OR decision_kind = ?4)
980 ORDER BY seq DESC
981 LIMIT ?5
982 "#,
983 )?;
984 let rows = statement.query_map(
985 params![
986 capability_id,
987 tool_server,
988 tool_name,
989 decision_kind,
990 limit as i64,
991 ],
992 |row| Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?)),
993 )?;
994
995 rows.map(|row| {
996 let (seq, raw_json) = row?;
997 decode_verified_chio_receipt(
998 &raw_json,
999 "persisted tool receipt",
1000 Some(seq.max(0) as u64),
1001 )
1002 })
1003 .collect()
1004 }
1005
1006 pub fn list_tool_receipts_for_subject(
1011 &self,
1012 subject_key: &str,
1013 ) -> Result<Vec<ChioReceipt>, ReceiptStoreError> {
1014 let connection = self.connection()?;
1015 let mut statement = connection.prepare(
1016 r#"
1017 SELECT r.seq, r.raw_json
1018 FROM chio_tool_receipts r
1019 LEFT JOIN capability_lineage cl ON r.capability_id = cl.capability_id
1020 WHERE COALESCE(r.subject_key, cl.subject_key) = ?1
1021 ORDER BY r.timestamp ASC, r.seq ASC
1022 "#,
1023 )?;
1024 let rows = statement.query_map(params![subject_key], |row| {
1025 Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
1026 })?;
1027
1028 rows.map(|row| {
1029 let (seq, raw_json) = row?;
1030 decode_verified_chio_receipt(
1031 &raw_json,
1032 "persisted tool receipt",
1033 Some(seq.max(0) as u64),
1034 )
1035 })
1036 .collect()
1037 }
1038
1039 pub fn list_tool_receipts_after_seq(
1040 &self,
1041 after_seq: u64,
1042 limit: usize,
1043 ) -> Result<Vec<StoredToolReceipt>, ReceiptStoreError> {
1044 let connection = self.connection()?;
1045 let mut statement = connection.prepare(
1046 r#"
1047 SELECT seq, raw_json
1048 FROM chio_tool_receipts
1049 WHERE seq > ?1
1050 ORDER BY seq ASC
1051 LIMIT ?2
1052 "#,
1053 )?;
1054 let rows = statement.query_map(params![after_seq as i64, limit as i64], |row| {
1055 Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
1056 })?;
1057 rows.map(|row| {
1058 let (seq, raw_json) = row?;
1059 let seq = seq.max(0) as u64;
1060 Ok(StoredToolReceipt {
1061 seq,
1062 receipt: decode_verified_chio_receipt(
1063 &raw_json,
1064 "persisted tool receipt",
1065 Some(seq),
1066 )?,
1067 })
1068 })
1069 .collect()
1070 }
1071
1072 pub fn list_child_receipts(
1073 &self,
1074 limit: usize,
1075 session_id: Option<&str>,
1076 parent_request_id: Option<&str>,
1077 request_id: Option<&str>,
1078 operation_kind: Option<&str>,
1079 terminal_state: Option<&str>,
1080 ) -> Result<Vec<ChildRequestReceipt>, ReceiptStoreError> {
1081 let connection = self.connection()?;
1082 let mut statement = connection.prepare(
1083 r#"
1084 SELECT seq, raw_json
1085 FROM chio_child_receipts
1086 WHERE (?1 IS NULL OR session_id = ?1)
1087 AND (?2 IS NULL OR parent_request_id = ?2)
1088 AND (?3 IS NULL OR request_id = ?3)
1089 AND (?4 IS NULL OR operation_kind = ?4)
1090 AND (?5 IS NULL OR terminal_state = ?5)
1091 ORDER BY seq DESC
1092 LIMIT ?6
1093 "#,
1094 )?;
1095 let rows = statement.query_map(
1096 params![
1097 session_id,
1098 parent_request_id,
1099 request_id,
1100 operation_kind,
1101 terminal_state,
1102 limit as i64,
1103 ],
1104 |row| Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?)),
1105 )?;
1106
1107 rows.map(|row| {
1108 let (seq, raw_json) = row?;
1109 decode_verified_child_receipt(
1110 &raw_json,
1111 "persisted child receipt",
1112 Some(seq.max(0) as u64),
1113 )
1114 })
1115 .collect()
1116 }
1117
1118 pub fn list_child_receipts_after_seq(
1119 &self,
1120 after_seq: u64,
1121 limit: usize,
1122 ) -> Result<Vec<StoredChildReceipt>, ReceiptStoreError> {
1123 let connection = self.connection()?;
1124 let mut statement = connection.prepare(
1125 r#"
1126 SELECT seq, raw_json
1127 FROM chio_child_receipts
1128 WHERE seq > ?1
1129 ORDER BY seq ASC
1130 LIMIT ?2
1131 "#,
1132 )?;
1133 let rows = statement.query_map(params![after_seq as i64, limit as i64], |row| {
1134 Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
1135 })?;
1136 rows.map(|row| {
1137 let (seq, raw_json) = row?;
1138 let seq = seq.max(0) as u64;
1139 Ok(StoredChildReceipt {
1140 seq,
1141 receipt: decode_verified_child_receipt(
1142 &raw_json,
1143 "persisted child receipt",
1144 Some(seq),
1145 )?,
1146 })
1147 })
1148 .collect()
1149 }
1150
1151 pub fn import_federated_evidence_share(
1152 &mut self,
1153 import: &FederatedEvidenceShareImport,
1154 ) -> Result<FederatedEvidenceShareSummary, ReceiptStoreError> {
1155 let imported_at = SystemTime::now()
1156 .duration_since(UNIX_EPOCH)
1157 .map(|duration| duration.as_secs())
1158 .unwrap_or(0);
1159 let mut connection = self.connection()?;
1160 let tx = connection.transaction()?;
1161 tx.execute(
1162 r#"
1163 INSERT INTO federated_evidence_shares (
1164 share_id,
1165 manifest_hash,
1166 imported_at,
1167 exported_at,
1168 issuer,
1169 partner,
1170 signer_public_key,
1171 require_proofs,
1172 query_json
1173 ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
1174 ON CONFLICT(share_id) DO UPDATE SET
1175 manifest_hash = excluded.manifest_hash,
1176 imported_at = excluded.imported_at,
1177 exported_at = excluded.exported_at,
1178 issuer = excluded.issuer,
1179 partner = excluded.partner,
1180 signer_public_key = excluded.signer_public_key,
1181 require_proofs = excluded.require_proofs,
1182 query_json = excluded.query_json
1183 "#,
1184 params![
1185 import.share_id,
1186 import.manifest_hash,
1187 imported_at as i64,
1188 import.exported_at as i64,
1189 import.issuer,
1190 import.partner,
1191 import.signer_public_key,
1192 if import.require_proofs { 1_i64 } else { 0_i64 },
1193 import.query_json,
1194 ],
1195 )?;
1196
1197 let lineage_by_capability = import
1198 .capability_lineage
1199 .iter()
1200 .map(|snapshot| (snapshot.capability_id.as_str(), snapshot))
1201 .collect::<BTreeMap<_, _>>();
1202
1203 for snapshot in &import.capability_lineage {
1204 tx.execute(
1205 r#"
1206 INSERT INTO federated_share_capability_lineage (
1207 share_id,
1208 capability_id,
1209 subject_key,
1210 issuer_key,
1211 issued_at,
1212 expires_at,
1213 grants_json,
1214 delegation_depth,
1215 parent_capability_id
1216 ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
1217 ON CONFLICT(share_id, capability_id) DO UPDATE SET
1218 subject_key = excluded.subject_key,
1219 issuer_key = excluded.issuer_key,
1220 issued_at = excluded.issued_at,
1221 expires_at = excluded.expires_at,
1222 grants_json = excluded.grants_json,
1223 delegation_depth = excluded.delegation_depth,
1224 parent_capability_id = excluded.parent_capability_id
1225 "#,
1226 params![
1227 import.share_id,
1228 snapshot.capability_id,
1229 snapshot.subject_key,
1230 snapshot.issuer_key,
1231 snapshot.issued_at as i64,
1232 snapshot.expires_at as i64,
1233 snapshot.grants_json,
1234 snapshot.delegation_depth as i64,
1235 snapshot.parent_capability_id,
1236 ],
1237 )?;
1238 }
1239
1240 for record in &import.tool_receipts {
1241 let attribution = extract_receipt_attribution(&record.receipt);
1242 let lineage_subject = lineage_by_capability
1243 .get(record.receipt.capability_id.as_str())
1244 .map(|snapshot| snapshot.subject_key.as_str());
1245 let lineage_issuer = lineage_by_capability
1246 .get(record.receipt.capability_id.as_str())
1247 .map(|snapshot| snapshot.issuer_key.as_str());
1248 tx.execute(
1249 r#"
1250 INSERT INTO federated_share_tool_receipts (
1251 share_id,
1252 seq,
1253 receipt_id,
1254 timestamp,
1255 capability_id,
1256 subject_key,
1257 issuer_key,
1258 raw_json
1259 ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
1260 ON CONFLICT(share_id, seq) DO UPDATE SET
1261 receipt_id = excluded.receipt_id,
1262 timestamp = excluded.timestamp,
1263 capability_id = excluded.capability_id,
1264 subject_key = excluded.subject_key,
1265 issuer_key = excluded.issuer_key,
1266 raw_json = excluded.raw_json
1267 "#,
1268 params![
1269 import.share_id,
1270 record.seq as i64,
1271 record.receipt.id,
1272 record.receipt.timestamp as i64,
1273 record.receipt.capability_id,
1274 attribution
1275 .subject_key
1276 .or_else(|| lineage_subject.map(ToOwned::to_owned)),
1277 attribution
1278 .issuer_key
1279 .or_else(|| lineage_issuer.map(ToOwned::to_owned)),
1280 serde_json::to_string(&record.receipt)?,
1281 ],
1282 )?;
1283 }
1284
1285 tx.commit()?;
1286
1287 Ok(FederatedEvidenceShareSummary {
1288 share_id: import.share_id.clone(),
1289 manifest_hash: import.manifest_hash.clone(),
1290 imported_at,
1291 exported_at: import.exported_at,
1292 issuer: import.issuer.clone(),
1293 partner: import.partner.clone(),
1294 signer_public_key: import.signer_public_key.clone(),
1295 require_proofs: import.require_proofs,
1296 tool_receipts: import.tool_receipts.len() as u64,
1297 capability_lineage: import.capability_lineage.len() as u64,
1298 })
1299 }
1300
1301 pub fn get_federated_share_for_capability(
1302 &self,
1303 capability_id: &str,
1304 ) -> Result<Option<(FederatedEvidenceShareSummary, CapabilitySnapshot)>, ReceiptStoreError>
1305 {
1306 let row = self
1307 .connection()?
1308 .query_row(
1309 r#"
1310 SELECT
1311 s.share_id,
1312 s.manifest_hash,
1313 s.imported_at,
1314 s.exported_at,
1315 s.issuer,
1316 s.partner,
1317 s.signer_public_key,
1318 s.require_proofs,
1319 (SELECT COUNT(*) FROM federated_share_tool_receipts r WHERE r.share_id = s.share_id),
1320 (SELECT COUNT(*) FROM federated_share_capability_lineage c WHERE c.share_id = s.share_id),
1321 l.capability_id,
1322 l.subject_key,
1323 l.issuer_key,
1324 l.issued_at,
1325 l.expires_at,
1326 l.grants_json,
1327 l.delegation_depth,
1328 l.parent_capability_id
1329 FROM federated_share_capability_lineage l
1330 INNER JOIN federated_evidence_shares s ON s.share_id = l.share_id
1331 WHERE l.capability_id = ?1
1332 ORDER BY s.imported_at DESC, s.share_id DESC
1333 LIMIT 1
1334 "#,
1335 params![capability_id],
1336 |row| {
1337 Ok((
1338 FederatedEvidenceShareSummary {
1339 share_id: row.get::<_, String>(0)?,
1340 manifest_hash: row.get::<_, String>(1)?,
1341 imported_at: row.get::<_, i64>(2)?.max(0) as u64,
1342 exported_at: row.get::<_, i64>(3)?.max(0) as u64,
1343 issuer: row.get::<_, String>(4)?,
1344 partner: row.get::<_, String>(5)?,
1345 signer_public_key: row.get::<_, String>(6)?,
1346 require_proofs: row.get::<_, i64>(7)? != 0,
1347 tool_receipts: row.get::<_, i64>(8)?.max(0) as u64,
1348 capability_lineage: row.get::<_, i64>(9)?.max(0) as u64,
1349 },
1350 CapabilitySnapshot {
1351 capability_id: row.get::<_, String>(10)?,
1352 subject_key: row.get::<_, String>(11)?,
1353 issuer_key: row.get::<_, String>(12)?,
1354 issued_at: row.get::<_, i64>(13)?.max(0) as u64,
1355 expires_at: row.get::<_, i64>(14)?.max(0) as u64,
1356 grants_json: row.get::<_, String>(15)?,
1357 delegation_depth: row.get::<_, i64>(16)?.max(0) as u64,
1358 parent_capability_id: row.get::<_, Option<String>>(17)?,
1359 },
1360 ))
1361 },
1362 )
1363 .optional()?;
1364 Ok(row)
1365 }
1366
1367 pub fn list_federated_share_subject_corpora(
1368 &self,
1369 subject_key: &str,
1370 since: Option<u64>,
1371 until: Option<u64>,
1372 ) -> Result<Vec<FederatedShareSubjectCorpus>, ReceiptStoreError> {
1373 let mut share_ids = self
1374 .connection()?
1375 .prepare(
1376 r#"
1377 SELECT DISTINCT share_id
1378 FROM federated_share_tool_receipts
1379 WHERE subject_key = ?1
1380 AND (?2 IS NULL OR timestamp >= ?2)
1381 AND (?3 IS NULL OR timestamp <= ?3)
1382 ORDER BY share_id
1383 "#,
1384 )?
1385 .query_map(
1386 params![
1387 subject_key,
1388 since.map(|value| value as i64),
1389 until.map(|value| value as i64)
1390 ],
1391 |row| row.get::<_, String>(0),
1392 )?
1393 .collect::<Result<Vec<_>, _>>()?;
1394
1395 share_ids.sort();
1396 let mut results = Vec::new();
1397 for share_id in share_ids {
1398 let summary = self
1399 .connection()?
1400 .query_row(
1401 r#"
1402 SELECT
1403 share_id,
1404 manifest_hash,
1405 imported_at,
1406 exported_at,
1407 issuer,
1408 partner,
1409 signer_public_key,
1410 require_proofs,
1411 (SELECT COUNT(*) FROM federated_share_tool_receipts r WHERE r.share_id = s.share_id),
1412 (SELECT COUNT(*) FROM federated_share_capability_lineage c WHERE c.share_id = s.share_id)
1413 FROM federated_evidence_shares s
1414 WHERE share_id = ?1
1415 "#,
1416 params![share_id],
1417 |row| {
1418 Ok(FederatedEvidenceShareSummary {
1419 share_id: row.get::<_, String>(0)?,
1420 manifest_hash: row.get::<_, String>(1)?,
1421 imported_at: row.get::<_, i64>(2)?.max(0) as u64,
1422 exported_at: row.get::<_, i64>(3)?.max(0) as u64,
1423 issuer: row.get::<_, String>(4)?,
1424 partner: row.get::<_, String>(5)?,
1425 signer_public_key: row.get::<_, String>(6)?,
1426 require_proofs: row.get::<_, i64>(7)? != 0,
1427 tool_receipts: row.get::<_, i64>(8)?.max(0) as u64,
1428 capability_lineage: row.get::<_, i64>(9)?.max(0) as u64,
1429 })
1430 },
1431 )?;
1432
1433 let receipts = self
1434 .connection()?
1435 .prepare(
1436 r#"
1437 SELECT seq, raw_json
1438 FROM federated_share_tool_receipts
1439 WHERE share_id = ?1
1440 AND subject_key = ?2
1441 AND (?3 IS NULL OR timestamp >= ?3)
1442 AND (?4 IS NULL OR timestamp <= ?4)
1443 ORDER BY seq ASC
1444 "#,
1445 )?
1446 .query_map(
1447 params![
1448 summary.share_id,
1449 subject_key,
1450 since.map(|value| value as i64),
1451 until.map(|value| value as i64)
1452 ],
1453 |row| {
1454 let raw_json = row.get::<_, String>(1)?;
1455 let seq = row.get::<_, i64>(0)?.max(0) as u64;
1456 Ok(StoredToolReceipt {
1457 seq,
1458 receipt: decode_verified_chio_receipt(
1459 &raw_json,
1460 "federated share tool receipt",
1461 Some(seq),
1462 )
1463 .map_err(|error| {
1464 rusqlite::Error::FromSqlConversionFailure(
1465 raw_json.len(),
1466 rusqlite::types::Type::Text,
1467 Box::new(error),
1468 )
1469 })?,
1470 })
1471 },
1472 )?
1473 .collect::<Result<Vec<_>, _>>()?;
1474
1475 let capabilities = self
1476 .connection()?
1477 .prepare(
1478 r#"
1479 SELECT
1480 capability_id,
1481 subject_key,
1482 issuer_key,
1483 issued_at,
1484 expires_at,
1485 grants_json,
1486 delegation_depth,
1487 parent_capability_id
1488 FROM federated_share_capability_lineage
1489 WHERE share_id = ?1
1490 AND (subject_key = ?2 OR issuer_key = ?2)
1491 ORDER BY issued_at ASC, capability_id ASC
1492 "#,
1493 )?
1494 .query_map(params![summary.share_id, subject_key], |row| {
1495 Ok(CapabilitySnapshot {
1496 capability_id: row.get::<_, String>(0)?,
1497 subject_key: row.get::<_, String>(1)?,
1498 issuer_key: row.get::<_, String>(2)?,
1499 issued_at: row.get::<_, i64>(3)?.max(0) as u64,
1500 expires_at: row.get::<_, i64>(4)?.max(0) as u64,
1501 grants_json: row.get::<_, String>(5)?,
1502 delegation_depth: row.get::<_, i64>(6)?.max(0) as u64,
1503 parent_capability_id: row.get::<_, Option<String>>(7)?,
1504 })
1505 })?
1506 .collect::<Result<Vec<_>, _>>()?;
1507
1508 results.push((summary, receipts, capabilities));
1509 }
1510
1511 Ok(results)
1512 }
1513
1514 pub fn record_federated_lineage_bridge(
1515 &mut self,
1516 local_capability_id: &str,
1517 parent_capability_id: &str,
1518 share_id: Option<&str>,
1519 ) -> Result<(), ReceiptStoreError> {
1520 self.connection()?.execute(
1521 r#"
1522 INSERT INTO federated_lineage_bridges (
1523 local_capability_id,
1524 parent_capability_id,
1525 share_id
1526 ) VALUES (?1, ?2, ?3)
1527 ON CONFLICT(local_capability_id) DO UPDATE SET
1528 parent_capability_id = excluded.parent_capability_id,
1529 share_id = excluded.share_id
1530 "#,
1531 params![local_capability_id, parent_capability_id, share_id],
1532 )?;
1533 Ok(())
1534 }
1535
1536 pub(crate) fn federated_lineage_bridge_parent(
1537 &self,
1538 local_capability_id: &str,
1539 ) -> Result<Option<String>, ReceiptStoreError> {
1540 self.connection()?
1541 .query_row(
1542 r#"
1543 SELECT parent_capability_id
1544 FROM federated_lineage_bridges
1545 WHERE local_capability_id = ?1
1546 "#,
1547 params![local_capability_id],
1548 |row| row.get::<_, String>(0),
1549 )
1550 .optional()
1551 .map_err(Into::into)
1552 }
1553
1554 pub fn get_combined_lineage(
1555 &self,
1556 capability_id: &str,
1557 ) -> Result<Option<chio_kernel::CapabilitySnapshot>, ReceiptStoreError> {
1558 if let Some(mut snapshot) =
1559 self.get_lineage(capability_id)
1560 .map_err(|error| match error {
1561 chio_kernel::CapabilityLineageError::ReceiptStore(error) => error,
1562 chio_kernel::CapabilityLineageError::Sqlite(error) => {
1563 ReceiptStoreError::Sqlite(error)
1564 }
1565 chio_kernel::CapabilityLineageError::Json(error) => {
1566 ReceiptStoreError::Json(error)
1567 }
1568 })?
1569 {
1570 if snapshot.parent_capability_id.is_none() {
1571 snapshot.parent_capability_id =
1572 self.federated_lineage_bridge_parent(&snapshot.capability_id)?;
1573 }
1574 return Ok(Some(snapshot));
1575 }
1576 Ok(self
1577 .get_federated_share_for_capability(capability_id)?
1578 .map(|(_, snapshot)| snapshot))
1579 }
1580
1581 pub fn get_combined_delegation_chain(
1582 &self,
1583 capability_id: &str,
1584 ) -> Result<Vec<chio_kernel::CapabilitySnapshot>, ReceiptStoreError> {
1585 let mut chain = Vec::new();
1586 let mut current = Some(capability_id.to_string());
1587 let mut seen = BTreeSet::new();
1588
1589 while let Some(current_capability_id) = current.take() {
1590 if !seen.insert(current_capability_id.clone()) || chain.len() >= 32 {
1591 break;
1592 }
1593 let Some(snapshot) = self.get_combined_lineage(¤t_capability_id)? else {
1594 break;
1595 };
1596 current = snapshot.parent_capability_id.clone();
1597 chain.push(snapshot);
1598 }
1599
1600 chain.reverse();
1601 Ok(chain)
1602 }
1603}