systemprompt_analytics/repository/agents/
list_queries.rs

1//! Agent analytics listings ordered by success rate, cost, or recency.
2//!
3//! Task counts, completion and execution time come from the hourly task
4//! rollup (`report_agent_task_hourly`) and cost from the hourly AI usage
5//! rollup (`report_ai_usage_hourly`) under the agent each request's task had
6//! when it was written, both widened to whole UTC hours at the window's start.
7//! The two are aggregated per agent before they are joined: joining requests
8//! onto tasks row by row (as these listings once did) repeats a task once per
9//! request, so `task_count` counted requests and the average execution time
10//! was weighted by them.
11//!
12//! Copyright (c) systemprompt.io — Business Source License 1.1.
13//! See <https://systemprompt.io> for licensing details.
14
15use crate::Result;
16use chrono::{DateTime, Utc};
17
18use super::AgentAnalyticsRepository;
19use crate::models::AgentListDbRow;
20use crate::models::reporting::AgentListRow;
21
22impl AgentAnalyticsRepository {
23    pub async fn list_agents(
24        &self,
25        start: DateTime<Utc>,
26        end: DateTime<Utc>,
27        limit: i64,
28        sort_order: &str,
29    ) -> Result<Vec<AgentListRow>> {
30        let rows = match sort_order {
31            "success_rate" => self.list_by_success_rate(start, end, limit).await,
32            "cost" => self.list_by_cost(start, end, limit).await,
33            "last_active" => self.list_by_last_active(start, end, limit).await,
34            _ => self.list_by_task_count(start, end, limit).await,
35        }?;
36        Ok(rows.into_iter().map(AgentListRow::from).collect())
37    }
38
39    async fn list_by_success_rate(
40        &self,
41        start: DateTime<Utc>,
42        end: DateTime<Utc>,
43        limit: i64,
44    ) -> Result<Vec<AgentListDbRow>> {
45        sqlx::query_as!(
46            AgentListDbRow,
47            r#"
48            WITH tasks AS (
49                SELECT agent_name, SUM(task_count) AS task_count,
50                       COALESCE(SUM(task_count) FILTER (WHERE status = 'TASK_STATE_COMPLETED'), 0) AS completed_count,
51                       SUM(exec_sum_ms) AS exec_sum_ms, SUM(exec_n) AS exec_n,
52                       MAX(last_started_at) AS last_active
53                FROM report_agent_task_hourly
54                WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2 AND agent_name <> ''
55                GROUP BY agent_name
56            ), costs AS (
57                SELECT agent_name, SUM(cost_microdollars) AS cost
58                FROM report_ai_usage_hourly
59                WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2 AND agent_name <> ''
60                GROUP BY agent_name
61            )
62            SELECT
63                t.agent_name as "agent_name!",
64                t.task_count::bigint as "task_count!",
65                t.completed_count::bigint as "completed_count!",
66                COALESCE(t.exec_sum_ms::numeric / NULLIF(t.exec_n, 0), 0)::bigint as "avg_execution_time_ms!",
67                COALESCE(c.cost, 0)::bigint as "total_cost_microdollars!",
68                t.last_active as "last_active!"
69            FROM tasks t
70            LEFT JOIN costs c ON c.agent_name = t.agent_name
71            ORDER BY CASE WHEN t.task_count > 0
72                THEN t.completed_count::float / t.task_count::float
73                ELSE 0 END DESC
74            LIMIT $3
75            "#,
76            start,
77            end,
78            limit
79        )
80        .fetch_all(&*self.pool)
81        .await
82        .map_err(Into::into)
83    }
84
85    async fn list_by_cost(
86        &self,
87        start: DateTime<Utc>,
88        end: DateTime<Utc>,
89        limit: i64,
90    ) -> Result<Vec<AgentListDbRow>> {
91        sqlx::query_as!(
92            AgentListDbRow,
93            r#"
94            WITH tasks AS (
95                SELECT agent_name, SUM(task_count) AS task_count,
96                       COALESCE(SUM(task_count) FILTER (WHERE status = 'TASK_STATE_COMPLETED'), 0) AS completed_count,
97                       SUM(exec_sum_ms) AS exec_sum_ms, SUM(exec_n) AS exec_n,
98                       MAX(last_started_at) AS last_active
99                FROM report_agent_task_hourly
100                WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2 AND agent_name <> ''
101                GROUP BY agent_name
102            ), costs AS (
103                SELECT agent_name, SUM(cost_microdollars) AS cost
104                FROM report_ai_usage_hourly
105                WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2 AND agent_name <> ''
106                GROUP BY agent_name
107            )
108            SELECT
109                t.agent_name as "agent_name!",
110                t.task_count::bigint as "task_count!",
111                t.completed_count::bigint as "completed_count!",
112                COALESCE(t.exec_sum_ms::numeric / NULLIF(t.exec_n, 0), 0)::bigint as "avg_execution_time_ms!",
113                COALESCE(c.cost, 0)::bigint as "total_cost_microdollars!",
114                t.last_active as "last_active!"
115            FROM tasks t
116            LEFT JOIN costs c ON c.agent_name = t.agent_name
117            ORDER BY COALESCE(c.cost, 0) 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_last_active(
130        &self,
131        start: DateTime<Utc>,
132        end: DateTime<Utc>,
133        limit: i64,
134    ) -> Result<Vec<AgentListDbRow>> {
135        sqlx::query_as!(
136            AgentListDbRow,
137            r#"
138            WITH tasks AS (
139                SELECT agent_name, SUM(task_count) AS task_count,
140                       COALESCE(SUM(task_count) FILTER (WHERE status = 'TASK_STATE_COMPLETED'), 0) AS completed_count,
141                       SUM(exec_sum_ms) AS exec_sum_ms, SUM(exec_n) AS exec_n,
142                       MAX(last_started_at) AS last_active
143                FROM report_agent_task_hourly
144                WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2 AND agent_name <> ''
145                GROUP BY agent_name
146            ), costs AS (
147                SELECT agent_name, SUM(cost_microdollars) AS cost
148                FROM report_ai_usage_hourly
149                WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2 AND agent_name <> ''
150                GROUP BY agent_name
151            )
152            SELECT
153                t.agent_name as "agent_name!",
154                t.task_count::bigint as "task_count!",
155                t.completed_count::bigint as "completed_count!",
156                COALESCE(t.exec_sum_ms::numeric / NULLIF(t.exec_n, 0), 0)::bigint as "avg_execution_time_ms!",
157                COALESCE(c.cost, 0)::bigint as "total_cost_microdollars!",
158                t.last_active as "last_active!"
159            FROM tasks t
160            LEFT JOIN costs c ON c.agent_name = t.agent_name
161            ORDER BY t.last_active DESC
162            LIMIT $3
163            "#,
164            start,
165            end,
166            limit
167        )
168        .fetch_all(&*self.pool)
169        .await
170        .map_err(Into::into)
171    }
172
173    async fn list_by_task_count(
174        &self,
175        start: DateTime<Utc>,
176        end: DateTime<Utc>,
177        limit: i64,
178    ) -> Result<Vec<AgentListDbRow>> {
179        sqlx::query_as!(
180            AgentListDbRow,
181            r#"
182            WITH tasks AS (
183                SELECT agent_name, SUM(task_count) AS task_count,
184                       COALESCE(SUM(task_count) FILTER (WHERE status = 'TASK_STATE_COMPLETED'), 0) AS completed_count,
185                       SUM(exec_sum_ms) AS exec_sum_ms, SUM(exec_n) AS exec_n,
186                       MAX(last_started_at) AS last_active
187                FROM report_agent_task_hourly
188                WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2 AND agent_name <> ''
189                GROUP BY agent_name
190            ), costs AS (
191                SELECT agent_name, SUM(cost_microdollars) AS cost
192                FROM report_ai_usage_hourly
193                WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2 AND agent_name <> ''
194                GROUP BY agent_name
195            )
196            SELECT
197                t.agent_name as "agent_name!",
198                t.task_count::bigint as "task_count!",
199                t.completed_count::bigint as "completed_count!",
200                COALESCE(t.exec_sum_ms::numeric / NULLIF(t.exec_n, 0), 0)::bigint as "avg_execution_time_ms!",
201                COALESCE(c.cost, 0)::bigint as "total_cost_microdollars!",
202                t.last_active as "last_active!"
203            FROM tasks t
204            LEFT JOIN costs c ON c.agent_name = t.agent_name
205            ORDER BY t.task_count DESC
206            LIMIT $3
207            "#,
208            start,
209            end,
210            limit
211        )
212        .fetch_all(&*self.pool)
213        .await
214        .map_err(Into::into)
215    }
216}
systemprompt_analytics/repository/agents/list_queries.rs

systemprompt_analytics/repository/agents/
list_queries.rs
