systemprompt_analytics/repository/tools/
detail_queries.rs

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

systemprompt_analytics/repository/tools/
detail_queries.rs
