systemprompt_analytics/repository/core_stats/
overview.rs

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

systemprompt_analytics/repository/core_stats/
overview.rs
