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 v_clean_traffic
119            WHERE ended_at IS NULL
120              AND last_activity_at >= $1            "#,
121            since
122        )
123        .fetch_one(&*self.pool)
124        .await?;
125        Ok(count)
126    }
127
128    pub async fn get_total_session_count(
129        &self,
130        start: DateTime<Utc>,
131        end: DateTime<Utc>,
132    ) -> Result<i64> {
133        let count = sqlx::query_scalar!(
134            r#"SELECT COUNT(*)::bigint as "count!" FROM v_clean_traffic WHERE started_at >= $1 AND started_at < $2"#,
135            start,
136            end
137        )
138        .fetch_one(&*self.pool)
139        .await?;
140        Ok(count)
141    }
142
143    pub async fn get_cost(
144        &self,
145        start: DateTime<Utc>,
146        end: DateTime<Utc>,
147    ) -> Result<OverviewCostRow> {
148        sqlx::query_as!(
149            OverviewCostRow,
150            r#"
151            SELECT SUM(cost_microdollars)::bigint as "cost"
152            FROM ai_requests
153            WHERE created_at >= $1 AND created_at < $2
154            "#,
155            start,
156            end
157        )
158        .fetch_one(&*self.pool)
159        .await
160        .map_err(Into::into)
161    }
162}
systemprompt_analytics/repository/overview.rs

systemprompt_analytics/repository/
overview.rs
