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

systemprompt_analytics/repository/tools/
list_queries.rs
