systemprompt-analytics 0.1.22

Analytics module for systemprompt.io - session tracking, metrics, and reporting
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

use anyhow::Result;
use chrono::{Duration, Utc};

use super::CoreStatsRepository;
use crate::models::{CostOverview, PlatformOverview, UserMetricsWithTrends};

impl CoreStatsRepository {
    pub async fn get_platform_overview(&self) -> Result<PlatformOverview> {
        let now = Utc::now();
        let cutoff_24h = now - Duration::hours(24);
        let cutoff_7d = now - Duration::days(7);
        sqlx::query_as!(
            PlatformOverview,
            r#"
            SELECT
                (SELECT COUNT(*) FROM users WHERE status != 'deleted') as "total_users!",
                (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!",
                (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!",
                (SELECT COUNT(*) FROM user_sessions WHERE is_bot = false AND is_behavioral_bot = false AND is_scanner = false) as "total_sessions!",
                (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!",
                (SELECT COUNT(*) FROM user_contexts) as "total_contexts!",
                (SELECT COUNT(*) FROM agent_tasks) as "total_tasks!",
                (SELECT COUNT(*) FROM ai_requests) as "total_ai_requests!"
            "#,
            cutoff_24h,
            cutoff_7d
        )
        .fetch_one(&*self.pool)
        .await
        .map_err(Into::into)
    }

    pub async fn get_cost_overview(&self) -> Result<CostOverview> {
        let now = Utc::now();
        let since_hours_24 = now - Duration::hours(24);
        let since_days_7 = now - Duration::days(7);
        let since_days_30 = now - Duration::days(30);
        sqlx::query_as!(
            CostOverview,
            r#"
            SELECT
                COALESCE(SUM(cost_microdollars)::float / 1000000.0, 0.0) as "total_cost!",
                COALESCE(SUM(cost_microdollars) FILTER (WHERE created_at > $1)::float / 1000000.0, 0.0) as "cost_24h!",
                COALESCE(SUM(cost_microdollars) FILTER (WHERE created_at > $2)::float / 1000000.0, 0.0) as "cost_7d!",
                COALESCE(SUM(cost_microdollars) FILTER (WHERE created_at > $3)::float / 1000000.0, 0.0) as "cost_30d!",
                COALESCE(AVG(cost_microdollars)::float / 1000000.0, 0.0) as "avg_cost_per_request!"
            FROM ai_requests
            "#,
            since_hours_24,
            since_days_7,
            since_days_30
        )
        .fetch_one(&*self.pool)
        .await
        .map_err(Into::into)
    }

    pub async fn get_user_metrics_with_trends(&self) -> Result<UserMetricsWithTrends> {
        let now = Utc::now();
        let since_hours_24 = now - Duration::hours(24);
        let since_hours_48 = now - Duration::hours(48);
        let since_days_7 = now - Duration::days(7);
        let since_days_14 = now - Duration::days(14);
        let since_days_30 = now - Duration::days(30);
        let since_days_60 = now - Duration::days(60);

        sqlx::query_as!(
            UserMetricsWithTrends,
            r#"
            SELECT
                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $1) as "count_24h!",
                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $2) as "count_7d!",
                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $3) as "count_30d!",
                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $4 AND last_activity_at <= $1) as "prev_24h!",
                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $5 AND last_activity_at <= $2) as "prev_7d!",
                COUNT(DISTINCT fingerprint_hash) FILTER (WHERE last_activity_at > $6 AND last_activity_at <= $3) as "prev_30d!"
            FROM user_sessions
            WHERE is_bot = false AND is_behavioral_bot = false AND is_scanner = false
            "#,
            since_hours_24,
            since_days_7,
            since_days_30,
            since_hours_48,
            since_days_14,
            since_days_60
        )
        .fetch_one(&*self.pool)
        .await
        .map_err(Into::into)
    }
}