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

systemprompt_analytics/repository/agents/
list_queries.rs
