systemprompt_analytics/repository/core_stats/
overview.rs

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

systemprompt_analytics/repository/core_stats/
overview.rs
