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

systemprompt_analytics/repository/
overview.rs
