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//! Request, tool and task figures come from the hourly rollups, so a window
7//! is widened to whole UTC hours at its start: every hour that begins before
8//! `end` and does not end before `start` counts in full.
9//!
10//! Copyright (c) systemprompt.io — Business Source License 1.1.
11//! See <https://systemprompt.io> for licensing details.
12
13use crate::Result;
14use chrono::{DateTime, Utc};
15use sqlx::PgPool;
16use std::sync::Arc;
17use systemprompt_database::DbPool;
18use systemprompt_models::ContextKind;
19
20use crate::models::reporting::{
21    OverviewAgentRow, OverviewCostRow, OverviewRequestRow, OverviewToolRow,
22};
23
24#[derive(Debug)]
25pub struct OverviewAnalyticsRepository {
26    pool: Arc<PgPool>,
27}
28
29impl OverviewAnalyticsRepository {
30    pub fn new(db: &DbPool) -> Self {
31        let pool = db.pool();
32        Self { pool }
33    }
34
35    pub async fn get_conversation_count(
36        &self,
37        start: DateTime<Utc>,
38        end: DateTime<Utc>,
39    ) -> Result<i64> {
40        let count = sqlx::query_scalar!(
41            r#"SELECT COUNT(*)::bigint as "count!" FROM report_user_contexts WHERE created_at >= $1 AND created_at < $2 AND kind = $3"#,
42            start,
43            end,
44            ContextKind::User.as_str()
45        )
46        .fetch_one(&*self.pool)
47        .await?;
48        Ok(count)
49    }
50
51    pub async fn get_agent_metrics(
52        &self,
53        start: DateTime<Utc>,
54        end: DateTime<Utc>,
55    ) -> Result<OverviewAgentRow> {
56        sqlx::query_as!(
57            OverviewAgentRow,
58            r#"
59            SELECT
60                COUNT(DISTINCT NULLIF(agent_name, ''))::bigint as "active_agents!",
61                COALESCE(SUM(task_count), 0)::bigint as "total_tasks!",
62                COALESCE(SUM(task_count) FILTER (WHERE status = 'TASK_STATE_COMPLETED'), 0)::bigint as "completed_tasks!"
63            FROM report_agent_task_hourly
64            WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2
65            "#,
66            start,
67            end
68        )
69        .fetch_one(&*self.pool)
70        .await
71        .map_err(Into::into)
72    }
73
74    pub async fn get_request_metrics(
75        &self,
76        start: DateTime<Utc>,
77        end: DateTime<Utc>,
78    ) -> Result<OverviewRequestRow> {
79        sqlx::query_as!(
80            OverviewRequestRow,
81            r#"
82            SELECT
83                COALESCE(SUM(request_count), 0)::bigint as "total!",
84                SUM(tokens)::bigint as "total_tokens",
85                (SUM(latency_sum_ms)::numeric / NULLIF(SUM(latency_n), 0))::float8 as "avg_latency"
86            FROM report_ai_usage_hourly
87            WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2
88            "#,
89            start,
90            end
91        )
92        .fetch_one(&*self.pool)
93        .await
94        .map_err(Into::into)
95    }
96
97    pub async fn get_tool_metrics(
98        &self,
99        start: DateTime<Utc>,
100        end: DateTime<Utc>,
101    ) -> Result<OverviewToolRow> {
102        sqlx::query_as!(
103            OverviewToolRow,
104            r#"
105            SELECT
106                COALESCE(SUM(execution_count), 0)::bigint as "total!",
107                COALESCE(SUM(execution_count) FILTER (WHERE status = 'success'), 0)::bigint as "successful!"
108            FROM report_tool_usage_hourly
109            WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2
110              AND server_name NOT IN ('in_process', 'proxy', 'gateway', 'hook_claude_code', 'hook_opencode')
111            "#,
112            start,
113            end
114        )
115        .fetch_one(&*self.pool)
116        .await
117        .map_err(Into::into)
118    }
119
120    pub async fn get_active_session_count(&self, since: DateTime<Utc>) -> Result<i64> {
121        let count = sqlx::query_scalar!(
122            r#"
123            SELECT COUNT(*)::bigint as "count!"
124            FROM report_clean_traffic
125            WHERE ended_at IS NULL
126              AND last_activity_at >= $1            "#,
127            since
128        )
129        .fetch_one(&*self.pool)
130        .await?;
131        Ok(count)
132    }
133
134    pub async fn get_total_session_count(
135        &self,
136        start: DateTime<Utc>,
137        end: DateTime<Utc>,
138    ) -> Result<i64> {
139        let count = sqlx::query_scalar!(
140            r#"SELECT COUNT(*)::bigint as "count!" FROM report_clean_traffic WHERE started_at >= $1 AND started_at < $2"#,
141            start,
142            end
143        )
144        .fetch_one(&*self.pool)
145        .await?;
146        Ok(count)
147    }
148
149    pub async fn get_cost(
150        &self,
151        start: DateTime<Utc>,
152        end: DateTime<Utc>,
153    ) -> Result<OverviewCostRow> {
154        sqlx::query_as!(
155            OverviewCostRow,
156            r#"
157            SELECT SUM(cost_microdollars)::bigint as "cost"
158            FROM report_ai_usage_hourly
159            WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2
160            "#,
161            start,
162            end
163        )
164        .fetch_one(&*self.pool)
165        .await
166        .map_err(Into::into)
167    }
168}
systemprompt_analytics/repository/overview.rs

systemprompt_analytics/repository/
overview.rs
