systemprompt_analytics/repository/
overview.rs

1//! Top-line dashboard metrics across all analytics domains.
2//!
3//! [`OverviewAnalyticsRepository`] reads the headline counts shown on the
4//! analytics overview — conversations, agent/task activity, AI requests,
5//! tool executions, sessions, and cost — each scoped to a time window.
6
7use crate::Result;
8use chrono::{DateTime, Utc};
9use sqlx::PgPool;
10use std::sync::Arc;
11use systemprompt_database::DbPool;
12use systemprompt_models::ContextKind;
13
14use crate::models::cli::{OverviewAgentRow, OverviewCostRow, OverviewRequestRow, OverviewToolRow};
15
16#[derive(Debug)]
17pub struct OverviewAnalyticsRepository {
18    pool: Arc<PgPool>,
19}
20
21impl OverviewAnalyticsRepository {
22    pub fn new(db: &DbPool) -> Result<Self> {
23        let pool = db.pool_arc()?;
24        Ok(Self { pool })
25    }
26
27    pub async fn get_conversation_count(
28        &self,
29        start: DateTime<Utc>,
30        end: DateTime<Utc>,
31    ) -> Result<i64> {
32        let count = sqlx::query_scalar!(
33            r#"SELECT COUNT(*)::bigint as "count!" FROM user_contexts WHERE created_at >= $1 AND created_at < $2 AND kind = $3"#,
34            start,
35            end,
36            ContextKind::User.as_str()
37        )
38        .fetch_one(&*self.pool)
39        .await?;
40        Ok(count)
41    }
42
43    pub async fn get_agent_metrics(
44        &self,
45        start: DateTime<Utc>,
46        end: DateTime<Utc>,
47    ) -> Result<OverviewAgentRow> {
48        sqlx::query_as!(
49            OverviewAgentRow,
50            r#"
51            SELECT
52                COUNT(DISTINCT agent_name)::bigint as "active_agents!",
53                COUNT(*)::bigint as "total_tasks!",
54                COUNT(*) FILTER (WHERE status = 'TASK_STATE_COMPLETED')::bigint as "completed_tasks!"
55            FROM agent_tasks
56            WHERE started_at >= $1 AND started_at < $2
57            "#,
58            start,
59            end
60        )
61        .fetch_one(&*self.pool)
62        .await
63        .map_err(Into::into)
64    }
65
66    pub async fn get_request_metrics(
67        &self,
68        start: DateTime<Utc>,
69        end: DateTime<Utc>,
70    ) -> Result<OverviewRequestRow> {
71        sqlx::query_as!(
72            OverviewRequestRow,
73            r#"
74            SELECT
75                COUNT(*)::bigint as "total!",
76                SUM(tokens_used)::bigint as "total_tokens",
77                AVG(latency_ms)::float8 as "avg_latency"
78            FROM ai_requests
79            WHERE created_at >= $1 AND created_at < $2
80            "#,
81            start,
82            end
83        )
84        .fetch_one(&*self.pool)
85        .await
86        .map_err(Into::into)
87    }
88
89    pub async fn get_tool_metrics(
90        &self,
91        start: DateTime<Utc>,
92        end: DateTime<Utc>,
93    ) -> Result<OverviewToolRow> {
94        sqlx::query_as!(
95            OverviewToolRow,
96            r#"
97            SELECT
98                COUNT(*)::bigint as "total!",
99                COUNT(*) FILTER (WHERE status = 'success')::bigint as "successful!"
100            FROM mcp_tool_executions
101            WHERE created_at >= $1 AND created_at < $2
102            "#,
103            start,
104            end
105        )
106        .fetch_one(&*self.pool)
107        .await
108        .map_err(Into::into)
109    }
110
111    pub async fn get_active_session_count(&self, since: DateTime<Utc>) -> Result<i64> {
112        let count = sqlx::query_scalar!(
113            r#"
114            SELECT COUNT(*)::bigint as "count!"
115            FROM user_sessions
116            WHERE ended_at IS NULL
117              AND last_activity_at >= $1
118              AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
119            "#,
120            since
121        )
122        .fetch_one(&*self.pool)
123        .await?;
124        Ok(count)
125    }
126
127    pub async fn get_total_session_count(
128        &self,
129        start: DateTime<Utc>,
130        end: DateTime<Utc>,
131    ) -> Result<i64> {
132        let count = sqlx::query_scalar!(
133            r#"SELECT COUNT(*)::bigint as "count!" FROM user_sessions WHERE started_at >= $1 AND started_at < $2 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false"#,
134            start,
135            end
136        )
137        .fetch_one(&*self.pool)
138        .await?;
139        Ok(count)
140    }
141
142    pub async fn get_cost(
143        &self,
144        start: DateTime<Utc>,
145        end: DateTime<Utc>,
146    ) -> Result<OverviewCostRow> {
147        sqlx::query_as!(
148            OverviewCostRow,
149            r#"
150            SELECT SUM(cost_microdollars)::bigint as "cost"
151            FROM ai_requests
152            WHERE created_at >= $1 AND created_at < $2
153            "#,
154            start,
155            end
156        )
157        .fetch_one(&*self.pool)
158        .await
159        .map_err(Into::into)
160    }
161}
systemprompt_analytics/repository/overview.rs

systemprompt_analytics/repository/
overview.rs
