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

systemprompt_analytics/repository/agents/
detail_queries.rs
