chio_store_sqlite/receipt_store/reports/
analytics.rs

1// Receipt analytics report query.
2
3use super::*;
4
5impl SqliteReceiptStore {
6    pub fn query_receipt_analytics(
7        &self,
8        query: &ReceiptAnalyticsQuery,
9    ) -> Result<ReceiptAnalyticsResponse, ReceiptStoreError> {
10        require_admin_receipt_read_context(
11            query.read_context.as_ref(),
12            "receipt analytics report",
13        )?;
14        let group_limit = query
15            .group_limit
16            .unwrap_or(50)
17            .clamp(1, MAX_ANALYTICS_GROUP_LIMIT);
18        let time_bucket = query.time_bucket.unwrap_or(AnalyticsTimeBucket::Day);
19        let bucket_width = time_bucket.width_secs() as i64;
20
21        let capability_id = query.capability_id.as_deref();
22        let tool_server = query.tool_server.as_deref();
23        let tool_name = query.tool_name.as_deref();
24        let since = query.since.map(|value| value as i64);
25        let until = query.until.map(|value| value as i64);
26        let agent_subject = query.agent_subject.as_deref();
27
28        let summary_sql = r#"
29            SELECT
30                COUNT(*) AS total_receipts,
31                COALESCE(SUM(CASE WHEN r.decision_kind = 'allow' THEN 1 ELSE 0 END), 0) AS allow_count,
32                COALESCE(SUM(CASE WHEN r.decision_kind = 'deny' THEN 1 ELSE 0 END), 0) AS deny_count,
33                COALESCE(SUM(CASE WHEN r.decision_kind = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
34                COALESCE(SUM(CASE WHEN r.decision_kind = 'incomplete' THEN 1 ELSE 0 END), 0) AS incomplete_count,
35                COALESCE(SUM(CAST(COALESCE(json_extract(r.raw_json, '$.metadata.financial.cost_charged'), 0) AS INTEGER)), 0) AS total_cost_charged,
36                COALESCE(SUM(CAST(COALESCE(json_extract(r.raw_json, '$.metadata.financial.attempted_cost'), 0) AS INTEGER)), 0) AS total_attempted_cost
37            FROM chio_tool_receipts r
38            LEFT JOIN capability_lineage cl ON r.capability_id = cl.capability_id
39            WHERE (?1 IS NULL OR r.capability_id = ?1)
40              AND (?2 IS NULL OR r.tool_server = ?2)
41              AND (?3 IS NULL OR r.tool_name = ?3)
42              AND (?4 IS NULL OR r.timestamp >= ?4)
43              AND (?5 IS NULL OR r.timestamp <= ?5)
44              AND (?6 IS NULL OR COALESCE(r.subject_key, cl.subject_key) = ?6)
45        "#;
46        let summary = self.connection()?.query_row(
47            summary_sql,
48            params![
49                capability_id,
50                tool_server,
51                tool_name,
52                since,
53                until,
54                agent_subject
55            ],
56            |row| {
57                Ok(ReceiptAnalyticsMetrics::from_raw(
58                    row.get::<_, i64>(0)?.max(0) as u64,
59                    row.get::<_, i64>(1)?.max(0) as u64,
60                    row.get::<_, i64>(2)?.max(0) as u64,
61                    row.get::<_, i64>(3)?.max(0) as u64,
62                    row.get::<_, i64>(4)?.max(0) as u64,
63                    row.get::<_, i64>(5)?.max(0) as u64,
64                    row.get::<_, i64>(6)?.max(0) as u64,
65                ))
66            },
67        )?;
68
69        let by_agent_sql = r#"
70            SELECT
71                COALESCE(r.subject_key, cl.subject_key) AS subject_key,
72                COUNT(*) AS total_receipts,
73                COALESCE(SUM(CASE WHEN r.decision_kind = 'allow' THEN 1 ELSE 0 END), 0) AS allow_count,
74                COALESCE(SUM(CASE WHEN r.decision_kind = 'deny' THEN 1 ELSE 0 END), 0) AS deny_count,
75                COALESCE(SUM(CASE WHEN r.decision_kind = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
76                COALESCE(SUM(CASE WHEN r.decision_kind = 'incomplete' THEN 1 ELSE 0 END), 0) AS incomplete_count,
77                COALESCE(SUM(CAST(COALESCE(json_extract(r.raw_json, '$.metadata.financial.cost_charged'), 0) AS INTEGER)), 0) AS total_cost_charged,
78                COALESCE(SUM(CAST(COALESCE(json_extract(r.raw_json, '$.metadata.financial.attempted_cost'), 0) AS INTEGER)), 0) AS total_attempted_cost
79            FROM chio_tool_receipts r
80            LEFT JOIN capability_lineage cl ON r.capability_id = cl.capability_id
81            WHERE (?1 IS NULL OR r.capability_id = ?1)
82              AND (?2 IS NULL OR r.tool_server = ?2)
83              AND (?3 IS NULL OR r.tool_name = ?3)
84              AND (?4 IS NULL OR r.timestamp >= ?4)
85              AND (?5 IS NULL OR r.timestamp <= ?5)
86              AND (?6 IS NULL OR COALESCE(r.subject_key, cl.subject_key) = ?6)
87              AND COALESCE(r.subject_key, cl.subject_key) IS NOT NULL
88            GROUP BY COALESCE(r.subject_key, cl.subject_key)
89            ORDER BY total_receipts DESC, subject_key ASC
90            LIMIT ?7
91        "#;
92        let by_agent = self
93            .connection()?
94            .prepare(by_agent_sql)?
95            .query_map(
96                params![
97                    capability_id,
98                    tool_server,
99                    tool_name,
100                    since,
101                    until,
102                    agent_subject,
103                    group_limit as i64
104                ],
105                |row| {
106                    Ok(AgentAnalyticsRow {
107                        subject_key: row.get(0)?,
108                        metrics: ReceiptAnalyticsMetrics::from_raw(
109                            row.get::<_, i64>(1)?.max(0) as u64,
110                            row.get::<_, i64>(2)?.max(0) as u64,
111                            row.get::<_, i64>(3)?.max(0) as u64,
112                            row.get::<_, i64>(4)?.max(0) as u64,
113                            row.get::<_, i64>(5)?.max(0) as u64,
114                            row.get::<_, i64>(6)?.max(0) as u64,
115                            row.get::<_, i64>(7)?.max(0) as u64,
116                        ),
117                    })
118                },
119            )?
120            .collect::<Result<Vec<_>, _>>()?;
121
122        let by_tool_sql = r#"
123            SELECT
124                r.tool_server,
125                r.tool_name,
126                COUNT(*) AS total_receipts,
127                COALESCE(SUM(CASE WHEN r.decision_kind = 'allow' THEN 1 ELSE 0 END), 0) AS allow_count,
128                COALESCE(SUM(CASE WHEN r.decision_kind = 'deny' THEN 1 ELSE 0 END), 0) AS deny_count,
129                COALESCE(SUM(CASE WHEN r.decision_kind = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
130                COALESCE(SUM(CASE WHEN r.decision_kind = 'incomplete' THEN 1 ELSE 0 END), 0) AS incomplete_count,
131                COALESCE(SUM(CAST(COALESCE(json_extract(r.raw_json, '$.metadata.financial.cost_charged'), 0) AS INTEGER)), 0) AS total_cost_charged,
132                COALESCE(SUM(CAST(COALESCE(json_extract(r.raw_json, '$.metadata.financial.attempted_cost'), 0) AS INTEGER)), 0) AS total_attempted_cost
133            FROM chio_tool_receipts r
134            LEFT JOIN capability_lineage cl ON r.capability_id = cl.capability_id
135            WHERE (?1 IS NULL OR r.capability_id = ?1)
136              AND (?2 IS NULL OR r.tool_server = ?2)
137              AND (?3 IS NULL OR r.tool_name = ?3)
138              AND (?4 IS NULL OR r.timestamp >= ?4)
139              AND (?5 IS NULL OR r.timestamp <= ?5)
140              AND (?6 IS NULL OR COALESCE(r.subject_key, cl.subject_key) = ?6)
141            GROUP BY r.tool_server, r.tool_name
142            ORDER BY total_receipts DESC, r.tool_server ASC, r.tool_name ASC
143            LIMIT ?7
144        "#;
145        let by_tool = self
146            .connection()?
147            .prepare(by_tool_sql)?
148            .query_map(
149                params![
150                    capability_id,
151                    tool_server,
152                    tool_name,
153                    since,
154                    until,
155                    agent_subject,
156                    group_limit as i64
157                ],
158                |row| {
159                    Ok(ToolAnalyticsRow {
160                        tool_server: row.get(0)?,
161                        tool_name: row.get(1)?,
162                        metrics: ReceiptAnalyticsMetrics::from_raw(
163                            row.get::<_, i64>(2)?.max(0) as u64,
164                            row.get::<_, i64>(3)?.max(0) as u64,
165                            row.get::<_, i64>(4)?.max(0) as u64,
166                            row.get::<_, i64>(5)?.max(0) as u64,
167                            row.get::<_, i64>(6)?.max(0) as u64,
168                            row.get::<_, i64>(7)?.max(0) as u64,
169                            row.get::<_, i64>(8)?.max(0) as u64,
170                        ),
171                    })
172                },
173            )?
174            .collect::<Result<Vec<_>, _>>()?;
175
176        let by_time_sql = r#"
177            SELECT
178                CAST((r.timestamp / ?7) * ?7 AS INTEGER) AS bucket_start,
179                COUNT(*) AS total_receipts,
180                COALESCE(SUM(CASE WHEN r.decision_kind = 'allow' THEN 1 ELSE 0 END), 0) AS allow_count,
181                COALESCE(SUM(CASE WHEN r.decision_kind = 'deny' THEN 1 ELSE 0 END), 0) AS deny_count,
182                COALESCE(SUM(CASE WHEN r.decision_kind = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
183                COALESCE(SUM(CASE WHEN r.decision_kind = 'incomplete' THEN 1 ELSE 0 END), 0) AS incomplete_count,
184                COALESCE(SUM(CAST(COALESCE(json_extract(r.raw_json, '$.metadata.financial.cost_charged'), 0) AS INTEGER)), 0) AS total_cost_charged,
185                COALESCE(SUM(CAST(COALESCE(json_extract(r.raw_json, '$.metadata.financial.attempted_cost'), 0) AS INTEGER)), 0) AS total_attempted_cost
186            FROM chio_tool_receipts r
187            LEFT JOIN capability_lineage cl ON r.capability_id = cl.capability_id
188            WHERE (?1 IS NULL OR r.capability_id = ?1)
189              AND (?2 IS NULL OR r.tool_server = ?2)
190              AND (?3 IS NULL OR r.tool_name = ?3)
191              AND (?4 IS NULL OR r.timestamp >= ?4)
192              AND (?5 IS NULL OR r.timestamp <= ?5)
193              AND (?6 IS NULL OR COALESCE(r.subject_key, cl.subject_key) = ?6)
194            GROUP BY bucket_start
195            ORDER BY bucket_start ASC
196            LIMIT ?8
197        "#;
198        let by_time = self
199            .connection()?
200            .prepare(by_time_sql)?
201            .query_map(
202                params![
203                    capability_id,
204                    tool_server,
205                    tool_name,
206                    since,
207                    until,
208                    agent_subject,
209                    bucket_width,
210                    group_limit as i64
211                ],
212                |row| {
213                    let bucket_start = row.get::<_, i64>(0)?.max(0) as u64;
214                    Ok(TimeAnalyticsRow {
215                        bucket_start,
216                        bucket_end: bucket_start
217                            .saturating_add(bucket_width.max(1) as u64)
218                            .saturating_sub(1),
219                        metrics: ReceiptAnalyticsMetrics::from_raw(
220                            row.get::<_, i64>(1)?.max(0) as u64,
221                            row.get::<_, i64>(2)?.max(0) as u64,
222                            row.get::<_, i64>(3)?.max(0) as u64,
223                            row.get::<_, i64>(4)?.max(0) as u64,
224                            row.get::<_, i64>(5)?.max(0) as u64,
225                            row.get::<_, i64>(6)?.max(0) as u64,
226                            row.get::<_, i64>(7)?.max(0) as u64,
227                        ),
228                    })
229                },
230            )?
231            .collect::<Result<Vec<_>, _>>()?;
232
233        Ok(ReceiptAnalyticsResponse {
234            summary,
235            by_agent,
236            by_tool,
237            by_time,
238        })
239    }
240}
chio_store_sqlite/receipt_store/reports/analytics.rs

chio_store_sqlite/receipt_store/reports/
analytics.rs
