systemprompt_analytics/repository/tools/
list_queries.rs

1//! Tool execution leaderboard queries (filtered + unfiltered) for the
2//! analytics CLI surface. A host-native tool a client hook reported (Bash,
3//! Read, …) is recorded under the vantage point's own name as its server;
4//! those pseudo-servers are not MCP tools and stay out of every list.
5//!
6//! Copyright (c) systemprompt.io — Business Source License 1.1.
7//! See <https://systemprompt.io> for licensing details.
8
9use crate::Result;
10use chrono::{DateTime, Utc};
11
12use super::ToolAnalyticsRepository;
13use crate::models::reporting::ToolListRow;
14
15#[derive(Debug)]
16pub struct ToolListParams<'a> {
17    pub start: DateTime<Utc>,
18    pub end: DateTime<Utc>,
19    pub limit: i64,
20    pub server_filter: Option<&'a str>,
21    pub sort_order: &'a str,
22}
23
24impl ToolAnalyticsRepository {
25    pub async fn list_tools(&self, params: ToolListParams<'_>) -> Result<Vec<ToolListRow>> {
26        if let Some(server) = params.server_filter {
27            let pattern = format!("%{}%", server);
28            self.list_tools_with_filter(&params, &pattern).await
29        } else {
30            self.list_tools_unfiltered(&params).await
31        }
32    }
33
34    async fn list_tools_with_filter(
35        &self,
36        params: &ToolListParams<'_>,
37        pattern: &str,
38    ) -> Result<Vec<ToolListRow>> {
39        let ToolListParams {
40            start,
41            end,
42            limit,
43            sort_order,
44            ..
45        } = *params;
46        match sort_order {
47            "success_rate" => {
48                self.filtered_by_success_rate(start, end, pattern, limit)
49                    .await
50            },
51            "avg_time" => self.filtered_by_avg_time(start, end, pattern, limit).await,
52            _ => self.filtered_by_count(start, end, pattern, limit).await,
53        }
54    }
55
56    async fn filtered_by_success_rate(
57        &self,
58        start: DateTime<Utc>,
59        end: DateTime<Utc>,
60        pattern: &str,
61        limit: i64,
62    ) -> Result<Vec<ToolListRow>> {
63        sqlx::query_as!(
64            ToolListRow,
65            r#"
66            SELECT
67                tool_name as "tool_name!",
68                server_name as "server_name!",
69                COUNT(*)::bigint as "execution_count!",
70                COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
71                COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
72                MAX(created_at) as "last_used!"
73            FROM analytics_report_mcp_tool_executions
74            WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
75              AND server_name NOT IN ('in_process', 'proxy', 'gateway', 'hook_claude_code', 'hook_opencode')
76            GROUP BY tool_name, server_name
77            ORDER BY CASE WHEN COUNT(*) > 0
78                THEN COUNT(*) FILTER (WHERE status = 'success')::float / COUNT(*)::float
79                ELSE 0 END DESC
80            LIMIT $4
81            "#,
82            start,
83            end,
84            pattern,
85            limit
86        )
87        .fetch_all(&*self.pool)
88        .await
89        .map_err(Into::into)
90    }
91
92    async fn filtered_by_avg_time(
93        &self,
94        start: DateTime<Utc>,
95        end: DateTime<Utc>,
96        pattern: &str,
97        limit: i64,
98    ) -> Result<Vec<ToolListRow>> {
99        sqlx::query_as!(
100            ToolListRow,
101            r#"
102            SELECT
103                tool_name as "tool_name!",
104                server_name as "server_name!",
105                COUNT(*)::bigint as "execution_count!",
106                COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
107                COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
108                MAX(created_at) as "last_used!"
109            FROM analytics_report_mcp_tool_executions
110            WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
111              AND server_name NOT IN ('in_process', 'proxy', 'gateway', 'hook_claude_code', 'hook_opencode')
112            GROUP BY tool_name, server_name
113            ORDER BY COALESCE(AVG(execution_time_ms), 0) DESC
114            LIMIT $4
115            "#,
116            start,
117            end,
118            pattern,
119            limit
120        )
121        .fetch_all(&*self.pool)
122        .await
123        .map_err(Into::into)
124    }
125
126    async fn filtered_by_count(
127        &self,
128        start: DateTime<Utc>,
129        end: DateTime<Utc>,
130        pattern: &str,
131        limit: i64,
132    ) -> Result<Vec<ToolListRow>> {
133        sqlx::query_as!(
134            ToolListRow,
135            r#"
136            SELECT
137                tool_name as "tool_name!",
138                server_name as "server_name!",
139                COUNT(*)::bigint as "execution_count!",
140                COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
141                COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
142                MAX(created_at) as "last_used!"
143            FROM analytics_report_mcp_tool_executions
144            WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
145              AND server_name NOT IN ('in_process', 'proxy', 'gateway', 'hook_claude_code', 'hook_opencode')
146            GROUP BY tool_name, server_name
147            ORDER BY COUNT(*) DESC
148            LIMIT $4
149            "#,
150            start,
151            end,
152            pattern,
153            limit
154        )
155        .fetch_all(&*self.pool)
156        .await
157        .map_err(Into::into)
158    }
159
160    async fn list_tools_unfiltered(&self, params: &ToolListParams<'_>) -> Result<Vec<ToolListRow>> {
161        let ToolListParams {
162            start,
163            end,
164            limit,
165            sort_order,
166            ..
167        } = *params;
168        match sort_order {
169            "success_rate" => self.unfiltered_by_success_rate(start, end, limit).await,
170            "avg_time" => self.unfiltered_by_avg_time(start, end, limit).await,
171            _ => self.unfiltered_by_count(start, end, limit).await,
172        }
173    }
174
175    async fn unfiltered_by_success_rate(
176        &self,
177        start: DateTime<Utc>,
178        end: DateTime<Utc>,
179        limit: i64,
180    ) -> Result<Vec<ToolListRow>> {
181        sqlx::query_as!(
182            ToolListRow,
183            r#"
184            SELECT
185                tool_name as "tool_name!",
186                server_name as "server_name!",
187                COUNT(*)::bigint as "execution_count!",
188                COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
189                COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
190                MAX(created_at) as "last_used!"
191            FROM analytics_report_mcp_tool_executions
192            WHERE created_at >= $1 AND created_at < $2
193              AND server_name NOT IN ('in_process', 'proxy', 'gateway', 'hook_claude_code', 'hook_opencode')
194            GROUP BY tool_name, server_name
195            ORDER BY CASE WHEN COUNT(*) > 0
196                THEN COUNT(*) FILTER (WHERE status = 'success')::float / COUNT(*)::float
197                ELSE 0 END DESC
198            LIMIT $3
199            "#,
200            start,
201            end,
202            limit
203        )
204        .fetch_all(&*self.pool)
205        .await
206        .map_err(Into::into)
207    }
208
209    async fn unfiltered_by_avg_time(
210        &self,
211        start: DateTime<Utc>,
212        end: DateTime<Utc>,
213        limit: i64,
214    ) -> Result<Vec<ToolListRow>> {
215        sqlx::query_as!(
216            ToolListRow,
217            r#"
218            SELECT
219                tool_name as "tool_name!",
220                server_name as "server_name!",
221                COUNT(*)::bigint as "execution_count!",
222                COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
223                COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
224                MAX(created_at) as "last_used!"
225            FROM analytics_report_mcp_tool_executions
226            WHERE created_at >= $1 AND created_at < $2
227              AND server_name NOT IN ('in_process', 'proxy', 'gateway', 'hook_claude_code', 'hook_opencode')
228            GROUP BY tool_name, server_name
229            ORDER BY COALESCE(AVG(execution_time_ms), 0) DESC
230            LIMIT $3
231            "#,
232            start,
233            end,
234            limit
235        )
236        .fetch_all(&*self.pool)
237        .await
238        .map_err(Into::into)
239    }
240
241    async fn unfiltered_by_count(
242        &self,
243        start: DateTime<Utc>,
244        end: DateTime<Utc>,
245        limit: i64,
246    ) -> Result<Vec<ToolListRow>> {
247        sqlx::query_as!(
248            ToolListRow,
249            r#"
250            SELECT
251                tool_name as "tool_name!",
252                server_name as "server_name!",
253                COUNT(*)::bigint as "execution_count!",
254                COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
255                COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
256                MAX(created_at) as "last_used!"
257            FROM analytics_report_mcp_tool_executions
258            WHERE created_at >= $1 AND created_at < $2
259              AND server_name NOT IN ('in_process', 'proxy', 'gateway', 'hook_claude_code', 'hook_opencode')
260            GROUP BY tool_name, server_name
261            ORDER BY COUNT(*) DESC
262            LIMIT $3
263            "#,
264            start,
265            end,
266            limit
267        )
268        .fetch_all(&*self.pool)
269        .await
270        .map_err(Into::into)
271    }
272}
systemprompt_analytics/repository/tools/list_queries.rs

systemprompt_analytics/repository/tools/
list_queries.rs
