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

systemprompt_analytics/repository/tools/
list_queries.rs
