systemprompt_analytics/repository/tools/
list_queries.rs

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

systemprompt_analytics/repository/tools/
list_queries.rs
