systemprompt_analytics/repository/tools/
list_queries.rs

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

systemprompt_analytics/repository/tools/
list_queries.rs
