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 analytics_report_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 analytics_report_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 analytics_report_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(SUBSTRING(error_message FROM 1 FOR 100), 'Unknown error') as "error_type",
99                COUNT(*)::bigint as "error_count!"
100            FROM analytics_report_agent_tasks
101            WHERE agent_name ILIKE $1
102              AND started_at >= $2 AND started_at < $3
103              AND status = 'TASK_STATE_FAILED'
104            GROUP BY SUBSTRING(error_message FROM 1 FOR 100)
105            ORDER BY 2 DESC
106            LIMIT 10
107            "#,
108            pattern,
109            start,
110            end
111        )
112        .fetch_all(&*self.pool)
113        .await
114        .map_err(Into::into)
115    }
116
117    pub async fn get_hourly_distribution(
118        &self,
119        agent_name: &str,
120        start: DateTime<Utc>,
121        end: DateTime<Utc>,
122    ) -> Result<Vec<AgentHourlyRow>> {
123        let pattern = format!("%{}%", agent_name);
124        sqlx::query_as!(
125            AgentHourlyRow,
126            r#"
127            SELECT
128                EXTRACT(HOUR FROM started_at)::INTEGER as "task_hour!",
129                COUNT(*)::bigint as "task_count!"
130            FROM analytics_report_agent_tasks
131            WHERE agent_name ILIKE $1
132              AND started_at >= $2 AND started_at < $3
133            GROUP BY EXTRACT(HOUR FROM started_at)
134            ORDER BY 1
135            "#,
136            pattern,
137            start,
138            end
139        )
140        .fetch_all(&*self.pool)
141        .await
142        .map_err(Into::into)
143    }
144}
systemprompt_analytics/repository/agents/detail_queries.rs

systemprompt_analytics/repository/agents/
detail_queries.rs
