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

systemprompt_analytics/repository/tools/
detail_queries.rs
