systemprompt_analytics/repository/agents/
detail_queries.rs

1//! Single-agent analytics detail queries.
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::{AgentErrorRow, AgentHourlyRow, AgentStatusBreakdownRow, AgentSummaryRow};
11
12impl AgentAnalyticsRepository {
13    pub async fn agent_exists(
14        &self,
15        agent_name: &str,
16        start: DateTime<Utc>,
17        end: DateTime<Utc>,
18    ) -> Result<i64> {
19        let pattern = format!("%{}%", agent_name);
20        let count = sqlx::query_scalar!(
21            r#"SELECT COUNT(*)::bigint as "count!" FROM agent_tasks WHERE agent_name ILIKE $1 AND started_at >= $2 AND started_at < $3"#,
22            pattern,
23            start,
24            end
25        )
26        .fetch_one(&*self.pool)
27        .await?;
28        Ok(count)
29    }
30
31    pub async fn get_agent_summary(
32        &self,
33        agent_name: &str,
34        start: DateTime<Utc>,
35        end: DateTime<Utc>,
36    ) -> Result<AgentSummaryRow> {
37        let pattern = format!("%{}%", agent_name);
38        sqlx::query_as!(
39            AgentSummaryRow,
40            r#"
41            SELECT
42                COUNT(*)::bigint as "total_tasks!",
43                COUNT(*) FILTER (WHERE status = 'TASK_STATE_COMPLETED')::bigint as "completed!",
44                COUNT(*) FILTER (WHERE status = 'TASK_STATE_FAILED')::bigint as "failed!",
45                COALESCE(AVG(execution_time_ms)::float8, 0) as "avg_time!"
46            FROM agent_tasks
47            WHERE agent_name ILIKE $1
48              AND started_at >= $2 AND started_at < $3
49            "#,
50            pattern,
51            start,
52            end
53        )
54        .fetch_one(&*self.pool)
55        .await
56        .map_err(Into::into)
57    }
58
59    pub async fn get_status_breakdown(
60        &self,
61        agent_name: &str,
62        start: DateTime<Utc>,
63        end: DateTime<Utc>,
64    ) -> Result<Vec<AgentStatusBreakdownRow>> {
65        let pattern = format!("%{}%", agent_name);
66        sqlx::query_as!(
67            AgentStatusBreakdownRow,
68            r#"
69            SELECT status as "status!", COUNT(*)::bigint as "status_count!"
70            FROM agent_tasks
71            WHERE agent_name ILIKE $1
72              AND started_at >= $2 AND started_at < $3
73            GROUP BY status
74            ORDER BY 2 DESC
75            "#,
76            pattern,
77            start,
78            end
79        )
80        .fetch_all(&*self.pool)
81        .await
82        .map_err(Into::into)
83    }
84
85    pub async fn get_top_errors(
86        &self,
87        agent_name: &str,
88        start: DateTime<Utc>,
89        end: DateTime<Utc>,
90    ) -> Result<Vec<AgentErrorRow>> {
91        let pattern = format!("%{}%", agent_name);
92        sqlx::query_as!(
93            AgentErrorRow,
94            r#"
95            SELECT
96                COALESCE(
97                    SUBSTRING(l.message FROM 1 FOR 100),
98                    'Unknown error'
99                ) as "error_type",
100                COUNT(*)::bigint as "error_count!"
101            FROM agent_tasks at
102            LEFT JOIN logs l ON l.task_id = at.task_id AND l.level = 'ERROR'
103            WHERE at.agent_name ILIKE $1
104              AND at.started_at >= $2 AND at.started_at < $3
105              AND at.status = 'TASK_STATE_FAILED'
106            GROUP BY SUBSTRING(l.message FROM 1 FOR 100)
107            ORDER BY 2 DESC
108            LIMIT 10
109            "#,
110            pattern,
111            start,
112            end
113        )
114        .fetch_all(&*self.pool)
115        .await
116        .map_err(Into::into)
117    }
118
119    pub async fn get_hourly_distribution(
120        &self,
121        agent_name: &str,
122        start: DateTime<Utc>,
123        end: DateTime<Utc>,
124    ) -> Result<Vec<AgentHourlyRow>> {
125        let pattern = format!("%{}%", agent_name);
126        sqlx::query_as!(
127            AgentHourlyRow,
128            r#"
129            SELECT
130                EXTRACT(HOUR FROM started_at)::INTEGER as "task_hour!",
131                COUNT(*)::bigint as "task_count!"
132            FROM agent_tasks
133            WHERE agent_name ILIKE $1
134              AND started_at >= $2 AND started_at < $3
135            GROUP BY EXTRACT(HOUR FROM started_at)
136            ORDER BY 1
137            "#,
138            pattern,
139            start,
140            end
141        )
142        .fetch_all(&*self.pool)
143        .await
144        .map_err(Into::into)
145    }
146}
systemprompt_analytics/repository/agents/detail_queries.rs

systemprompt_analytics/repository/agents/
detail_queries.rs
