systemprompt_analytics/repository/tools/
detail_queries.rs

1//! Per-tool detail queries for `ToolAnalyticsRepository`.
2//!
3//! Drills into a single MCP tool (matched by name substring): summary stats,
4//! status breakdown, top error messages, usage by agent, and the execution
5//! series for trend charts. The existence check and the status breakdown read
6//! the hourly rollup (`report_tool_usage_hourly`), widening the window to
7//! whole UTC hours at its start. The rest stay on `report_mcp_tool_executions`
8//! for their window: summary stats carry a p95, top errors group by message
9//! text, usage by agent tells a direct call from an unlinked task, and the
10//! trend series is per execution — none of which a rollup can add up.
11//!
12//! Copyright (c) systemprompt.io — Business Source License 1.1.
13//! See <https://systemprompt.io> for licensing details.
14
15use crate::Result;
16use chrono::{DateTime, Utc};
17
18use super::ToolAnalyticsRepository;
19use crate::models::ToolAgentUsageDbRow;
20use crate::models::reporting::{
21    ToolAgentUsageRow, ToolErrorRow, ToolExecutionRow, ToolStatsRow, ToolStatusBreakdownRow,
22    ToolSummaryRow,
23};
24
25impl ToolAnalyticsRepository {
26    pub async fn get_stats(
27        &self,
28        start: DateTime<Utc>,
29        end: DateTime<Utc>,
30        tool_filter: Option<&str>,
31    ) -> Result<ToolStatsRow> {
32        if let Some(tool) = tool_filter {
33            let pattern = format!("%{}%", tool);
34            sqlx::query_as!(
35                ToolStatsRow,
36                r#"
37                SELECT
38                    COUNT(DISTINCT tool_name)::bigint as "total_tools!",
39                    COUNT(*)::bigint as "total_executions!",
40                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "successful!",
41                    COUNT(*) FILTER (WHERE status = 'failed')::bigint as "failed!",
42                    COUNT(*) FILTER (WHERE status = 'timeout')::bigint as "timeout!",
43                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
44                    COALESCE(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time_ms)::float8, 0) as "p95_time!"
45                FROM report_mcp_tool_executions
46                WHERE created_at >= $1 AND created_at < $2 AND tool_name ILIKE $3
47                "#,
48                start, end, pattern
49            )
50            .fetch_one(&*self.pool)
51            .await
52            .map_err(Into::into)
53        } else {
54            sqlx::query_as!(
55                ToolStatsRow,
56                r#"
57                SELECT
58                    COUNT(DISTINCT tool_name)::bigint as "total_tools!",
59                    COUNT(*)::bigint as "total_executions!",
60                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "successful!",
61                    COUNT(*) FILTER (WHERE status = 'failed')::bigint as "failed!",
62                    COUNT(*) FILTER (WHERE status = 'timeout')::bigint as "timeout!",
63                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
64                    COALESCE(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time_ms)::float8, 0) as "p95_time!"
65                FROM report_mcp_tool_executions
66                WHERE created_at >= $1 AND created_at < $2
67                "#,
68                start, end
69            )
70            .fetch_one(&*self.pool)
71            .await
72            .map_err(Into::into)
73        }
74    }
75
76    pub async fn tool_exists(
77        &self,
78        tool_filter: &str,
79        start: DateTime<Utc>,
80        end: DateTime<Utc>,
81    ) -> Result<i64> {
82        let pattern = format!("%{}%", tool_filter);
83        let count = sqlx::query_scalar!(
84            r#"SELECT COALESCE(SUM(execution_count), 0)::bigint as "count!" FROM report_tool_usage_hourly WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2 AND tool_name ILIKE $3"#,
85            start,
86            end,
87            pattern
88        )
89        .fetch_one(&*self.pool)
90        .await?;
91        Ok(count)
92    }
93
94    pub async fn get_tool_summary(
95        &self,
96        tool_filter: &str,
97        start: DateTime<Utc>,
98        end: DateTime<Utc>,
99    ) -> Result<ToolSummaryRow> {
100        let pattern = format!("%{}%", tool_filter);
101        sqlx::query_as!(
102            ToolSummaryRow,
103            r#"
104            SELECT
105                COUNT(*)::bigint as "total!",
106                COUNT(*) FILTER (WHERE status = 'success')::bigint as "successful!",
107                COUNT(*) FILTER (WHERE status = 'failed')::bigint as "failed!",
108                COUNT(*) FILTER (WHERE status = 'timeout')::bigint as "timeout!",
109                COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
110                COALESCE(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time_ms)::float8, 0) as "p95_time!"
111            FROM report_mcp_tool_executions
112            WHERE tool_name ILIKE $1 AND created_at >= $2 AND created_at < $3
113            "#,
114            pattern, start, end
115        )
116        .fetch_one(&*self.pool)
117        .await
118        .map_err(Into::into)
119    }
120
121    pub async fn get_status_breakdown(
122        &self,
123        tool_filter: &str,
124        start: DateTime<Utc>,
125        end: DateTime<Utc>,
126    ) -> Result<Vec<ToolStatusBreakdownRow>> {
127        let pattern = format!("%{}%", tool_filter);
128        sqlx::query_as!(
129            ToolStatusBreakdownRow,
130            r#"
131            SELECT status as "status!", SUM(execution_count)::bigint as "status_count!"
132            FROM report_tool_usage_hourly
133            WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2 AND tool_name ILIKE $3
134            GROUP BY status
135            ORDER BY 2 DESC
136            "#,
137            start,
138            end,
139            pattern
140        )
141        .fetch_all(&*self.pool)
142        .await
143        .map_err(Into::into)
144    }
145
146    pub async fn get_top_errors(
147        &self,
148        tool_filter: &str,
149        start: DateTime<Utc>,
150        end: DateTime<Utc>,
151    ) -> Result<Vec<ToolErrorRow>> {
152        let pattern = format!("%{}%", tool_filter);
153        sqlx::query_as!(
154            ToolErrorRow,
155            r#"
156            SELECT
157                COALESCE(SUBSTRING(error_message FROM 1 FOR 100), 'Unknown error') as "error_msg",
158                COUNT(*)::bigint as "error_count!"
159            FROM report_mcp_tool_executions
160            WHERE tool_name ILIKE $1 AND created_at >= $2 AND created_at < $3 AND status = 'failed'
161            GROUP BY SUBSTRING(error_message FROM 1 FOR 100)
162            ORDER BY 2 DESC
163            LIMIT 10
164            "#,
165            pattern,
166            start,
167            end
168        )
169        .fetch_all(&*self.pool)
170        .await
171        .map_err(Into::into)
172    }
173
174    pub async fn get_usage_by_agent(
175        &self,
176        tool_filter: &str,
177        start: DateTime<Utc>,
178        end: DateTime<Utc>,
179    ) -> Result<Vec<ToolAgentUsageRow>> {
180        let pattern = format!("%{}%", tool_filter);
181        let rows = sqlx::query_as!(
182            ToolAgentUsageDbRow,
183            r#"
184            SELECT
185                COALESCE(at.agent_name, CASE WHEN mte.task_id IS NULL THEN 'Direct Call' ELSE 'Unlinked Task' END) as "agent_name",
186                COUNT(*)::bigint as "usage_count!"
187            FROM report_mcp_tool_executions mte
188            LEFT JOIN report_agent_tasks at ON at.task_id = mte.task_id
189            WHERE mte.tool_name ILIKE $1 AND mte.created_at >= $2 AND mte.created_at < $3
190            GROUP BY COALESCE(at.agent_name, CASE WHEN mte.task_id IS NULL THEN 'Direct Call' ELSE 'Unlinked Task' END)
191            ORDER BY 2 DESC
192            LIMIT 10
193            "#,
194            pattern, start, end
195        )
196        .fetch_all(&*self.pool)
197        .await?;
198        Ok(rows.into_iter().map(ToolAgentUsageRow::from).collect())
199    }
200
201    pub async fn get_executions_for_trends(
202        &self,
203        start: DateTime<Utc>,
204        end: DateTime<Utc>,
205        tool_filter: Option<&str>,
206    ) -> Result<Vec<ToolExecutionRow>> {
207        if let Some(tool) = tool_filter {
208            let pattern = format!("%{}%", tool);
209            sqlx::query_as!(
210                ToolExecutionRow,
211                r#"
212                SELECT
213                    created_at as "created_at!",
214                    status,
215                    execution_time_ms
216                FROM report_mcp_tool_executions
217                WHERE created_at >= $1 AND created_at < $2 AND tool_name ILIKE $3
218                ORDER BY created_at
219                "#,
220                start,
221                end,
222                pattern
223            )
224            .fetch_all(&*self.pool)
225            .await
226            .map_err(Into::into)
227        } else {
228            sqlx::query_as!(
229                ToolExecutionRow,
230                r#"
231                SELECT
232                    created_at as "created_at!",
233                    status,
234                    execution_time_ms
235                FROM report_mcp_tool_executions
236                WHERE created_at >= $1 AND created_at < $2
237                ORDER BY created_at
238                "#,
239                start,
240                end
241            )
242            .fetch_all(&*self.pool)
243            .await
244            .map_err(Into::into)
245        }
246    }
247}
systemprompt_analytics/repository/tools/detail_queries.rs

systemprompt_analytics/repository/tools/
detail_queries.rs
