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

systemprompt_analytics/repository/tools/
detail_queries.rs
