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

systemprompt_analytics/repository/tools/
detail_queries.rs
