systemprompt_analytics/repository/tools/
list_queries.rs

1use anyhow::Result;
2use chrono::{DateTime, Utc};
3
4use super::ToolAnalyticsRepository;
5use crate::models::cli::ToolListRow;
6
7#[derive(Debug)]
8pub struct ToolListParams<'a> {
9    pub start: DateTime<Utc>,
10    pub end: DateTime<Utc>,
11    pub limit: i64,
12    pub server_filter: Option<&'a str>,
13    pub sort_order: &'a str,
14}
15
16impl ToolAnalyticsRepository {
17    pub async fn list_tools(&self, params: ToolListParams<'_>) -> Result<Vec<ToolListRow>> {
18        let ToolListParams {
19            start,
20            end,
21            limit,
22            server_filter,
23            sort_order,
24        } = params;
25        if let Some(server) = server_filter {
26            let pattern = format!("%{}%", server);
27            self.list_tools_with_filter(start, end, limit, &pattern, sort_order)
28                .await
29        } else {
30            self.list_tools_unfiltered(start, end, limit, sort_order)
31                .await
32        }
33    }
34
35    #[allow(clippy::too_many_arguments)]
36    async fn list_tools_with_filter(
37        &self,
38        start: DateTime<Utc>,
39        end: DateTime<Utc>,
40        limit: i64,
41        pattern: &str,
42        sort_order: &str,
43    ) -> Result<Vec<ToolListRow>> {
44        match sort_order {
45            "success_rate" => sqlx::query_as!(
46                ToolListRow,
47                r#"
48                SELECT
49                    tool_name as "tool_name!",
50                    server_name as "server_name!",
51                    COUNT(*)::bigint as "execution_count!",
52                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
53                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
54                    MAX(created_at) as "last_used!"
55                FROM mcp_tool_executions
56                WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
57                GROUP BY tool_name, server_name
58                ORDER BY CASE WHEN COUNT(*) > 0
59                    THEN COUNT(*) FILTER (WHERE status = 'success')::float / COUNT(*)::float
60                    ELSE 0 END DESC
61                LIMIT $4
62                "#,
63                start,
64                end,
65                pattern,
66                limit
67            )
68            .fetch_all(&*self.pool)
69            .await
70            .map_err(Into::into),
71            "avg_time" => sqlx::query_as!(
72                ToolListRow,
73                r#"
74                SELECT
75                    tool_name as "tool_name!",
76                    server_name as "server_name!",
77                    COUNT(*)::bigint as "execution_count!",
78                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
79                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
80                    MAX(created_at) as "last_used!"
81                FROM mcp_tool_executions
82                WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
83                GROUP BY tool_name, server_name
84                ORDER BY COALESCE(AVG(execution_time_ms), 0) DESC
85                LIMIT $4
86                "#,
87                start,
88                end,
89                pattern,
90                limit
91            )
92            .fetch_all(&*self.pool)
93            .await
94            .map_err(Into::into),
95            _ => sqlx::query_as!(
96                ToolListRow,
97                r#"
98                SELECT
99                    tool_name as "tool_name!",
100                    server_name as "server_name!",
101                    COUNT(*)::bigint as "execution_count!",
102                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
103                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
104                    MAX(created_at) as "last_used!"
105                FROM mcp_tool_executions
106                WHERE created_at >= $1 AND created_at < $2 AND server_name ILIKE $3
107                GROUP BY tool_name, server_name
108                ORDER BY COUNT(*) DESC
109                LIMIT $4
110                "#,
111                start,
112                end,
113                pattern,
114                limit
115            )
116            .fetch_all(&*self.pool)
117            .await
118            .map_err(Into::into),
119        }
120    }
121
122    async fn list_tools_unfiltered(
123        &self,
124        start: DateTime<Utc>,
125        end: DateTime<Utc>,
126        limit: i64,
127        sort_order: &str,
128    ) -> Result<Vec<ToolListRow>> {
129        match sort_order {
130            "success_rate" => sqlx::query_as!(
131                ToolListRow,
132                r#"
133                SELECT
134                    tool_name as "tool_name!",
135                    server_name as "server_name!",
136                    COUNT(*)::bigint as "execution_count!",
137                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
138                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
139                    MAX(created_at) as "last_used!"
140                FROM mcp_tool_executions
141                WHERE created_at >= $1 AND created_at < $2
142                GROUP BY tool_name, server_name
143                ORDER BY CASE WHEN COUNT(*) > 0
144                    THEN COUNT(*) FILTER (WHERE status = 'success')::float / COUNT(*)::float
145                    ELSE 0 END DESC
146                LIMIT $3
147                "#,
148                start,
149                end,
150                limit
151            )
152            .fetch_all(&*self.pool)
153            .await
154            .map_err(Into::into),
155            "avg_time" => sqlx::query_as!(
156                ToolListRow,
157                r#"
158                SELECT
159                    tool_name as "tool_name!",
160                    server_name as "server_name!",
161                    COUNT(*)::bigint as "execution_count!",
162                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
163                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
164                    MAX(created_at) as "last_used!"
165                FROM mcp_tool_executions
166                WHERE created_at >= $1 AND created_at < $2
167                GROUP BY tool_name, server_name
168                ORDER BY COALESCE(AVG(execution_time_ms), 0) DESC
169                LIMIT $3
170                "#,
171                start,
172                end,
173                limit
174            )
175            .fetch_all(&*self.pool)
176            .await
177            .map_err(Into::into),
178            _ => sqlx::query_as!(
179                ToolListRow,
180                r#"
181                SELECT
182                    tool_name as "tool_name!",
183                    server_name as "server_name!",
184                    COUNT(*)::bigint as "execution_count!",
185                    COUNT(*) FILTER (WHERE status = 'success')::bigint as "success_count!",
186                    COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!",
187                    MAX(created_at) as "last_used!"
188                FROM mcp_tool_executions
189                WHERE created_at >= $1 AND created_at < $2
190                GROUP BY tool_name, server_name
191                ORDER BY COUNT(*) 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}
systemprompt_analytics/repository/tools/list_queries.rs

systemprompt_analytics/repository/tools/
list_queries.rs
