systemprompt_analytics/repository/agents/
list_queries.rs

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

systemprompt_analytics/repository/agents/
list_queries.rs
