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 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 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 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 mcp_tool_executions
106            WHERE created_at >= $1 AND created_at < $2
107            "#,
108            start,
109            end
110        )
111        .fetch_one(&*self.pool)
112        .await
113        .map_err(Into::into)
114    }
115
116    pub async fn get_active_session_count(&self, since: DateTime<Utc>) -> Result<i64> {
117        let count = sqlx::query_scalar!(
118            r#"
119            SELECT COUNT(*)::bigint as "count!"
120            FROM v_clean_traffic
121            WHERE ended_at IS NULL
122              AND last_activity_at >= $1            "#,
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 v_clean_traffic WHERE started_at >= $1 AND started_at < $2"#,
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
