systemprompt_analytics/repository/tools/
list_queries.rs

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

systemprompt_analytics/repository/tools/
list_queries.rs
