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" => sqlx::query_as!(
43                ToolListRow,
44                r#"
45                SELECT
46                    tool_name as "tool_name!",
47                    server_name as "server_name!",
48                    COUNT(*)::bigint as "execution_count!",
49                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
50                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
51                    MAX(created_at) as "last_used!"
52                FROM mcp_tool_executions
53                WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
54                GROUP BY tool_name, server_name
55                ORDER BY CASE WHEN COUNT(*) > 0
56                    THEN COUNT(*) FILTER (WHERE status = 'success')::float / COUNT(*)::float
57                    ELSE 0 END DESC
58                LIMIT $4
59                "#,
60                start,
61                end,
62                pattern,
63                limit
64            )
65            .fetch_all(&*self.pool)
66            .await
67            .map_err(Into::into),
68            "avg_time" => sqlx::query_as!(
69                ToolListRow,
70                r#"
71                SELECT
72                    tool_name as "tool_name!",
73                    server_name as "server_name!",
74                    COUNT(*)::bigint as "execution_count!",
75                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
76                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
77                    MAX(created_at) as "last_used!"
78                FROM mcp_tool_executions
79                WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
80                GROUP BY tool_name, server_name
81                ORDER BY COALESCE(AVG(execution_time_ms), 0) 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            _ => sqlx::query_as!(
93                ToolListRow,
94                r#"
95                SELECT
96                    tool_name as "tool_name!",
97                    server_name as "server_name!",
98                    COUNT(*)::bigint as "execution_count!",
99                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
100                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
101                    MAX(created_at) as "last_used!"
102                FROM mcp_tool_executions
103                WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
104                GROUP BY tool_name, server_name
105                ORDER BY COUNT(*) DESC
106                LIMIT $4
107                "#,
108                start,
109                end,
110                pattern,
111                limit
112            )
113            .fetch_all(&*self.pool)
114            .await
115            .map_err(Into::into),
116        }
117    }
118
119    async fn list_tools_unfiltered(&self, params: &ToolListParams<'_>) -> Result<Vec<ToolListRow>> {
120        let ToolListParams {
121            start,
122            end,
123            limit,
124            sort_order,
125            ..
126        } = *params;
127        match sort_order {
128            "success_rate" => sqlx::query_as!(
129                ToolListRow,
130                r#"
131                SELECT
132                    tool_name as "tool_name!",
133                    server_name as "server_name!",
134                    COUNT(*)::bigint as "execution_count!",
135                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
136                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
137                    MAX(created_at) as "last_used!"
138                FROM mcp_tool_executions
139                WHERE created_at >= $1 AND created_at < $2
140                GROUP BY tool_name, server_name
141                ORDER BY CASE WHEN COUNT(*) > 0
142                    THEN COUNT(*) FILTER (WHERE status = 'success')::float / COUNT(*)::float
143                    ELSE 0 END DESC
144                LIMIT $3
145                "#,
146                start,
147                end,
148                limit
149            )
150            .fetch_all(&*self.pool)
151            .await
152            .map_err(Into::into),
153            "avg_time" => sqlx::query_as!(
154                ToolListRow,
155                r#"
156                SELECT
157                    tool_name as "tool_name!",
158                    server_name as "server_name!",
159                    COUNT(*)::bigint as "execution_count!",
160                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
161                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
162                    MAX(created_at) as "last_used!"
163                FROM mcp_tool_executions
164                WHERE created_at >= $1 AND created_at < $2
165                GROUP BY tool_name, server_name
166                ORDER BY COALESCE(AVG(execution_time_ms), 0) DESC
167                LIMIT $3
168                "#,
169                start,
170                end,
171                limit
172            )
173            .fetch_all(&*self.pool)
174            .await
175            .map_err(Into::into),
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 COUNT(*) DESC
190                LIMIT $3
191                "#,
192                start,
193                end,
194                limit
195            )
196            .fetch_all(&*self.pool)
197            .await
198            .map_err(Into::into),
199        }
200    }
201}
systemprompt_analytics/repository/tools/list_queries.rs

systemprompt_analytics/repository/tools/
list_queries.rs
