systemprompt_analytics/repository/agents/
list_queries.rs

1use anyhow::Result;
2use chrono::{DateTime, Utc};
3
4use super::AgentAnalyticsRepository;
5use crate::models::cli::AgentListRow;
6
7impl AgentAnalyticsRepository {
8    pub async fn list_agents(
9        &self,
10        start: DateTime<Utc>,
11        end: DateTime<Utc>,
12        limit: i64,
13        sort_order: &str,
14    ) -> Result<Vec<AgentListRow>> {
15        match sort_order {
16            "success_rate" => self.list_by_success_rate(start, end, limit).await,
17            "cost" => self.list_by_cost(start, end, limit).await,
18            "last_active" => self.list_by_last_active(start, end, limit).await,
19            _ => self.list_by_task_count(start, end, limit).await,
20        }
21    }
22
23    async fn list_by_success_rate(
24        &self,
25        start: DateTime<Utc>,
26        end: DateTime<Utc>,
27        limit: i64,
28    ) -> Result<Vec<AgentListRow>> {
29        sqlx::query_as!(
30            AgentListRow,
31            r#"
32            SELECT
33                t.agent_name as "agent_name!",
34                COUNT(*)::bigint as "task_count!",
35                COUNT(*) FILTER (WHERE t.status = 'completed')::bigint as "completed_count!",
36                COALESCE(AVG(t.execution_time_ms), 0)::bigint as "avg_execution_time_ms!",
37                COALESCE(SUM(r.cost_cents), 0)::bigint as "total_cost_cents!",
38                MAX(t.started_at) as "last_active!"
39            FROM agent_tasks t
40            LEFT JOIN ai_requests r ON r.task_id = t.task_id
41            WHERE t.started_at >= $1 AND t.started_at < $2
42              AND t.agent_name IS NOT NULL
43            GROUP BY t.agent_name
44            ORDER BY CASE WHEN COUNT(*) > 0
45                THEN COUNT(*) FILTER (WHERE t.status = 'completed')::float / COUNT(*)::float
46                ELSE 0 END DESC
47            LIMIT $3
48            "#,
49            start,
50            end,
51            limit
52        )
53        .fetch_all(&*self.pool)
54        .await
55        .map_err(Into::into)
56    }
57
58    async fn list_by_cost(
59        &self,
60        start: DateTime<Utc>,
61        end: DateTime<Utc>,
62        limit: i64,
63    ) -> Result<Vec<AgentListRow>> {
64        sqlx::query_as!(
65            AgentListRow,
66            r#"
67            SELECT
68                t.agent_name as "agent_name!",
69                COUNT(*)::bigint as "task_count!",
70                COUNT(*) FILTER (WHERE t.status = 'completed')::bigint as "completed_count!",
71                COALESCE(AVG(t.execution_time_ms), 0)::bigint as "avg_execution_time_ms!",
72                COALESCE(SUM(r.cost_cents), 0)::bigint as "total_cost_cents!",
73                MAX(t.started_at) as "last_active!"
74            FROM agent_tasks t
75            LEFT JOIN ai_requests r ON r.task_id = t.task_id
76            WHERE t.started_at >= $1 AND t.started_at < $2
77              AND t.agent_name IS NOT NULL
78            GROUP BY t.agent_name
79            ORDER BY COALESCE(SUM(r.cost_cents), 0) DESC
80            LIMIT $3
81            "#,
82            start,
83            end,
84            limit
85        )
86        .fetch_all(&*self.pool)
87        .await
88        .map_err(Into::into)
89    }
90
91    async fn list_by_last_active(
92        &self,
93        start: DateTime<Utc>,
94        end: DateTime<Utc>,
95        limit: i64,
96    ) -> Result<Vec<AgentListRow>> {
97        sqlx::query_as!(
98            AgentListRow,
99            r#"
100            SELECT
101                t.agent_name as "agent_name!",
102                COUNT(*)::bigint as "task_count!",
103                COUNT(*) FILTER (WHERE t.status = 'completed')::bigint as "completed_count!",
104                COALESCE(AVG(t.execution_time_ms), 0)::bigint as "avg_execution_time_ms!",
105                COALESCE(SUM(r.cost_cents), 0)::bigint as "total_cost_cents!",
106                MAX(t.started_at) as "last_active!"
107            FROM agent_tasks t
108            LEFT JOIN ai_requests r ON r.task_id = t.task_id
109            WHERE t.started_at >= $1 AND t.started_at < $2
110              AND t.agent_name IS NOT NULL
111            GROUP BY t.agent_name
112            ORDER BY MAX(t.started_at) DESC
113            LIMIT $3
114            "#,
115            start,
116            end,
117            limit
118        )
119        .fetch_all(&*self.pool)
120        .await
121        .map_err(Into::into)
122    }
123
124    async fn list_by_task_count(
125        &self,
126        start: DateTime<Utc>,
127        end: DateTime<Utc>,
128        limit: i64,
129    ) -> Result<Vec<AgentListRow>> {
130        sqlx::query_as!(
131            AgentListRow,
132            r#"
133            SELECT
134                t.agent_name as "agent_name!",
135                COUNT(*)::bigint as "task_count!",
136                COUNT(*) FILTER (WHERE t.status = 'completed')::bigint as "completed_count!",
137                COALESCE(AVG(t.execution_time_ms), 0)::bigint as "avg_execution_time_ms!",
138                COALESCE(SUM(r.cost_cents), 0)::bigint as "total_cost_cents!",
139                MAX(t.started_at) as "last_active!"
140            FROM agent_tasks t
141            LEFT JOIN ai_requests r ON r.task_id = t.task_id
142            WHERE t.started_at >= $1 AND t.started_at < $2
143              AND t.agent_name IS NOT NULL
144            GROUP BY t.agent_name
145            ORDER BY COUNT(*) 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    }
156}
systemprompt_analytics/repository/agents/list_queries.rs

systemprompt_analytics/repository/agents/
list_queries.rs
