chio_store_sqlite/receipt_store/reports/
settlement.rs

1// Settlement reconciliation report query.
2
3use super::*;
4
5impl SqliteReceiptStore {
6    pub fn query_settlement_reconciliation_report(
7        &self,
8        query: &OperatorReportQuery,
9    ) -> Result<SettlementReconciliationReport, ReceiptStoreError> {
10        require_admin_receipt_read_context(
11            query.read_context.as_ref(),
12            "settlement reconciliation report",
13        )?;
14        let capability_id = query.capability_id.as_deref();
15        let tool_server = query.tool_server.as_deref();
16        let tool_name = query.tool_name.as_deref();
17        let since = query.since.map(|value| value as i64);
18        let until = query.until.map(|value| value as i64);
19        let agent_subject = query.agent_subject.as_deref();
20        let row_limit = query.settlement_limit_or_default();
21
22        let summary_sql = r#"
23            SELECT
24                COUNT(*) AS matching_receipts,
25                COALESCE(SUM(
26                    CASE
27                        WHEN json_extract(r.raw_json, '$.metadata.financial.settlement_status') = 'pending' THEN 1
28                        ELSE 0
29                    END
30                ), 0) AS pending_receipts,
31                COALESCE(SUM(
32                    CASE
33                        WHEN json_extract(r.raw_json, '$.metadata.financial.settlement_status') = 'failed' THEN 1
34                        ELSE 0
35                    END
36                ), 0) AS failed_receipts,
37                COALESCE(SUM(
38                    CASE
39                        WHEN COALESCE(sr.reconciliation_state, 'open') NOT IN ('reconciled', 'ignored') THEN 1
40                        ELSE 0
41                    END
42                ), 0) AS actionable_receipts,
43                COALESCE(SUM(
44                    CASE
45                        WHEN COALESCE(sr.reconciliation_state, 'open') = 'reconciled' THEN 1
46                        ELSE 0
47                    END
48                ), 0) AS reconciled_receipts
49            FROM chio_tool_receipts r
50            LEFT JOIN capability_lineage cl ON r.capability_id = cl.capability_id
51            LEFT JOIN settlement_reconciliations sr ON r.receipt_id = sr.receipt_id
52            WHERE json_extract(r.raw_json, '$.metadata.financial.settlement_status') IN ('pending', 'failed')
53              AND (?1 IS NULL OR r.capability_id = ?1)
54              AND (?2 IS NULL OR r.tool_server = ?2)
55              AND (?3 IS NULL OR r.tool_name = ?3)
56              AND (?4 IS NULL OR r.timestamp >= ?4)
57              AND (?5 IS NULL OR r.timestamp <= ?5)
58              AND (?6 IS NULL OR COALESCE(r.subject_key, cl.subject_key) = ?6)
59        "#;
60
61        let (
62            matching_receipts,
63            pending_receipts,
64            failed_receipts,
65            actionable_receipts,
66            reconciled_receipts,
67        ) = self.connection()?.query_row(
68            summary_sql,
69            params![
70                capability_id,
71                tool_server,
72                tool_name,
73                since,
74                until,
75                agent_subject
76            ],
77            |row| {
78                Ok((
79                    row.get::<_, i64>(0)?.max(0) as u64,
80                    row.get::<_, i64>(1)?.max(0) as u64,
81                    row.get::<_, i64>(2)?.max(0) as u64,
82                    row.get::<_, i64>(3)?.max(0) as u64,
83                    row.get::<_, i64>(4)?.max(0) as u64,
84                ))
85            },
86        )?;
87
88        let rows_sql = r#"
89            SELECT
90                r.seq,
91                r.receipt_id,
92                r.timestamp,
93                r.capability_id,
94                COALESCE(r.subject_key, cl.subject_key),
95                r.tool_server,
96                r.tool_name,
97                json_extract(r.raw_json, '$.metadata.financial.payment_reference'),
98                json_extract(r.raw_json, '$.metadata.financial.settlement_status'),
99                CAST(json_extract(r.raw_json, '$.metadata.financial.cost_charged') AS INTEGER),
100                json_extract(r.raw_json, '$.metadata.financial.currency'),
101                COALESCE(sr.reconciliation_state, 'open'),
102                sr.note,
103                sr.updated_at,
104                r.raw_json
105            FROM chio_tool_receipts r
106            LEFT JOIN capability_lineage cl ON r.capability_id = cl.capability_id
107            LEFT JOIN settlement_reconciliations sr ON r.receipt_id = sr.receipt_id
108            WHERE json_extract(r.raw_json, '$.metadata.financial.settlement_status') IN ('pending', 'failed')
109              AND (?1 IS NULL OR r.capability_id = ?1)
110              AND (?2 IS NULL OR r.tool_server = ?2)
111              AND (?3 IS NULL OR r.tool_name = ?3)
112              AND (?4 IS NULL OR r.timestamp >= ?4)
113              AND (?5 IS NULL OR r.timestamp <= ?5)
114              AND (?6 IS NULL OR COALESCE(r.subject_key, cl.subject_key) = ?6)
115            ORDER BY r.timestamp DESC, r.seq DESC
116            LIMIT ?7
117        "#;
118
119        let connection = self.connection()?;
120        let mut stmt = connection.prepare(rows_sql)?;
121        let rows = stmt.query_map(
122            params![
123                capability_id,
124                tool_server,
125                tool_name,
126                since,
127                until,
128                agent_subject,
129                row_limit as i64
130            ],
131            |row| {
132                Ok((
133                    row.get::<_, i64>(0)?,
134                    row.get::<_, String>(1)?,
135                    row.get::<_, i64>(2)?,
136                    row.get::<_, String>(3)?,
137                    row.get::<_, Option<String>>(4)?,
138                    row.get::<_, String>(5)?,
139                    row.get::<_, String>(6)?,
140                    row.get::<_, Option<String>>(7)?,
141                    row.get::<_, String>(8)?,
142                    row.get::<_, Option<i64>>(9)?,
143                    row.get::<_, Option<String>>(10)?,
144                    row.get::<_, String>(11)?,
145                    row.get::<_, Option<String>>(12)?,
146                    row.get::<_, Option<i64>>(13)?,
147                    row.get::<_, String>(14)?,
148                ))
149            },
150        )?;
151
152        let mut receipts = Vec::new();
153        for row in rows {
154            let (
155                seq,
156                receipt_id,
157                timestamp,
158                capability_id,
159                subject_key,
160                tool_server,
161                tool_name,
162                payment_reference,
163                settlement_status_text,
164                cost_charged,
165                currency,
166                reconciliation_state_text,
167                note,
168                updated_at,
169                raw_json,
170            ) = row?;
171            let receipt = decode_verified_chio_receipt(
172                &raw_json,
173                "persisted tool receipt",
174                Some(seq.max(0) as u64),
175            )?;
176            let settlement_status = parse_settlement_status(&settlement_status_text)?;
177            let reconciliation_state =
178                parse_settlement_reconciliation_state(&reconciliation_state_text)?;
179            let action_required = settlement_reconciliation_action_required(
180                settlement_status.clone(),
181                reconciliation_state,
182            );
183            receipts.push(SettlementReconciliationRow {
184                receipt_id,
185                timestamp: timestamp.max(0) as u64,
186                capability_id,
187                subject_key,
188                tool_server,
189                tool_name,
190                payment_reference,
191                settlement_status,
192                cost_charged: cost_charged.map(|value| value.max(0) as u64),
193                currency,
194                budget_authority: receipt.financial_budget_authority_metadata(),
195                reconciliation_state,
196                action_required,
197                note,
198                updated_at: updated_at.map(|value| value.max(0) as u64),
199            });
200        }
201
202        Ok(SettlementReconciliationReport {
203            summary: SettlementReconciliationSummary {
204                matching_receipts,
205                returned_receipts: receipts.len() as u64,
206                pending_receipts,
207                failed_receipts,
208                actionable_receipts,
209                reconciled_receipts,
210                truncated: matching_receipts > receipts.len() as u64,
211            },
212            receipts,
213        })
214    }
215}
chio_store_sqlite/receipt_store/reports/settlement.rs

chio_store_sqlite/receipt_store/reports/
settlement.rs
