systemprompt_analytics/repository/agents/
detail_queries.rs

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

systemprompt_analytics/repository/agents/
detail_queries.rs
