systemprompt_analytics/repository/core_stats/
overview.rs

1//! Overview aggregates for core stats.
2//!
3//! Copyright (c) systemprompt.io — Business Source License 1.1.
4//! See <https://systemprompt.io> for licensing details.
5
6use crate::Result;
7use chrono::{Duration, Utc};
8use systemprompt_models::ContextKind;
9
10use super::CoreStatsRepository;
11use crate::models::{CostOverview, PlatformOverview, UserMetricsWithTrends};
12
13impl CoreStatsRepository {
14    pub async fn get_platform_overview(&self) -> Result<PlatformOverview> {
15        let now = Utc::now();
16        let cutoff_24h = now - Duration::hours(24);
17        let cutoff_7d = now - Duration::days(7);
18        sqlx::query_as!(
19            PlatformOverview,
20            r#"
21            SELECT
22                (SELECT COUNT(*) FROM users WHERE status != 'deleted') as "total_users!",
23                (SELECT COUNT(DISTINCT user_id) FROM user_sessions WHERE last_activity_at > $1 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false) as "active_users_24h!",
24                (SELECT COUNT(DISTINCT user_id) FROM user_sessions WHERE last_activity_at > $2 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false) as "active_users_7d!",
25                (SELECT COUNT(*) FROM user_sessions WHERE is_bot = false AND is_behavioral_bot = false AND is_scanner = false) as "total_sessions!",
26                (SELECT COUNT(*) FROM user_sessions WHERE ended_at IS NULL AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false) as "active_sessions!",
27                (SELECT COUNT(*) FROM user_contexts WHERE kind = $3) as "total_contexts!",
28                (SELECT COUNT(*) FROM agent_tasks) as "total_tasks!",
29                (SELECT COUNT(*) FROM ai_requests) as "total_ai_requests!"
30            "#,
31            cutoff_24h,
32            cutoff_7d,
33            ContextKind::User.as_str()
34        )
35        .fetch_one(&*self.pool)
36        .await
37        .map_err(Into::into)
38    }
39
40    pub async fn get_cost_overview(&self) -> Result<CostOverview> {
41        let now = Utc::now();
42        let since_hours_24 = now - Duration::hours(24);
43        let since_days_7 = now - Duration::days(7);
44        let since_days_30 = now - Duration::days(30);
45        sqlx::query_as!(
46            CostOverview,
47            r#"
48            SELECT
49                COALESCE(SUM(cost_microdollars)::float / 1000000.0, 0.0) as "total_cost!",
50                COALESCE(SUM(cost_microdollars) FILTER (WHERE created_at > $1)::float / 1000000.0, 0.0) as "cost_24h!",
51                COALESCE(SUM(cost_microdollars) FILTER (WHERE created_at > $2)::float / 1000000.0, 0.0) as "cost_7d!",
52                COALESCE(SUM(cost_microdollars) FILTER (WHERE created_at > $3)::float / 1000000.0, 0.0) as "cost_30d!",
53                COALESCE(AVG(cost_microdollars)::float / 1000000.0, 0.0) as "avg_cost_per_request!"
54            FROM ai_requests
55            "#,
56            since_hours_24,
57            since_days_7,
58            since_days_30
59        )
60        .fetch_one(&*self.pool)
61        .await
62        .map_err(Into::into)
63    }
64
65    pub async fn get_user_metrics_with_trends(&self) -> Result<UserMetricsWithTrends> {
66        let now = Utc::now();
67        let since_hours_24 = now - Duration::hours(24);
68        let since_hours_48 = now - Duration::hours(48);
69        let since_days_7 = now - Duration::days(7);
70        let since_days_14 = now - Duration::days(14);
71        let since_days_30 = now - Duration::days(30);
72        let since_days_60 = now - Duration::days(60);
73
74        sqlx::query_as!(
75            UserMetricsWithTrends,
76            r#"
77            SELECT
78                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $1) as "count_24h!",
79                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $2) as "count_7d!",
80                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $3) as "count_30d!",
81                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $4 AND last_activity_at <= $1) as "prev_24h!",
82                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $5 AND last_activity_at <= $2) as "prev_7d!",
83                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $6 AND last_activity_at <= $3) as "prev_30d!"
84            FROM user_sessions
85            WHERE is_bot = false AND is_behavioral_bot = false AND is_scanner = false
86            "#,
87            since_hours_24,
88            since_days_7,
89            since_days_30,
90            since_hours_48,
91            since_days_14,
92            since_days_60
93        )
94        .fetch_one(&*self.pool)
95        .await
96        .map_err(Into::into)
97    }
98}
systemprompt_analytics/repository/core_stats/overview.rs

systemprompt_analytics/repository/core_stats/
overview.rs
