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

systemprompt_analytics/repository/
overview.rs
