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

systemprompt_analytics/repository/
overview.rs
