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

systemprompt_analytics/repository/tools/
list_queries.rs
