systemprompt_analytics/repository/
cli_sessions.rs

1use anyhow::Result;
2use chrono::{DateTime, Utc};
3use sqlx::PgPool;
4use std::sync::Arc;
5use systemprompt_database::DbPool;
6use systemprompt_identifiers::UserId;
7
8use crate::models::cli::{LiveSessionRow, SessionStatsRow, SessionTrendRow};
9
10#[derive(Debug)]
11pub struct CliSessionAnalyticsRepository {
12    pool: Arc<PgPool>,
13}
14
15impl CliSessionAnalyticsRepository {
16    pub fn new(db: &DbPool) -> Result<Self> {
17        let pool = db.pool_arc()?;
18        Ok(Self { pool })
19    }
20
21    pub async fn get_stats(
22        &self,
23        start: DateTime<Utc>,
24        end: DateTime<Utc>,
25    ) -> Result<SessionStatsRow> {
26        sqlx::query_as!(
27            SessionStatsRow,
28            r#"
29            SELECT
30                COUNT(*)::bigint as "total_sessions!",
31                COUNT(DISTINCT user_id)::bigint as "unique_users!",
32                AVG(COALESCE(
33                    duration_seconds,
34                    EXTRACT(EPOCH FROM (COALESCE(ended_at, last_activity_at) - started_at))::INTEGER
35                ))::float8 as "avg_duration",
36                AVG(request_count)::float8 as "avg_requests",
37                COUNT(*) FILTER (WHERE converted_at IS NOT NULL)::bigint as "conversions!"
38            FROM user_sessions
39            WHERE started_at >= $1 AND started_at < $2
40              AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
41            "#,
42            start,
43            end
44        )
45        .fetch_one(&*self.pool)
46        .await
47        .map_err(Into::into)
48    }
49
50    pub async fn get_active_session_count(&self, since: DateTime<Utc>) -> Result<i64> {
51        let count = sqlx::query_scalar!(
52            r#"SELECT COUNT(*)::bigint as "count!" FROM user_sessions WHERE ended_at IS NULL AND last_activity_at >= $1 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false"#,
53            since
54        )
55        .fetch_one(&*self.pool)
56        .await?;
57        Ok(count)
58    }
59
60    pub async fn get_live_sessions(
61        &self,
62        cutoff: DateTime<Utc>,
63        limit: i64,
64    ) -> Result<Vec<LiveSessionRow>> {
65        sqlx::query_as!(
66            LiveSessionRow,
67            r#"
68            SELECT
69                session_id as "session_id!",
70                COALESCE(user_type, 'unknown') as "user_type",
71                started_at as "started_at!",
72                duration_seconds,
73                request_count,
74                last_activity_at as "last_activity_at!"
75            FROM user_sessions
76            WHERE ended_at IS NULL
77              AND last_activity_at >= $1
78              AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
79            ORDER BY last_activity_at DESC
80            LIMIT $2
81            "#,
82            cutoff,
83            limit
84        )
85        .fetch_all(&*self.pool)
86        .await
87        .map_err(Into::into)
88    }
89
90    pub async fn get_active_count(&self, cutoff: DateTime<Utc>) -> Result<i64> {
91        let count = sqlx::query_scalar!(
92            r#"SELECT COUNT(*)::bigint as "count!" FROM user_sessions WHERE ended_at IS NULL AND last_activity_at >= $1 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false"#,
93            cutoff
94        )
95        .fetch_one(&*self.pool)
96        .await?;
97        Ok(count)
98    }
99
100    pub async fn get_sessions_for_trends(
101        &self,
102        start: DateTime<Utc>,
103        end: DateTime<Utc>,
104    ) -> Result<Vec<SessionTrendRow>> {
105        sqlx::query_as!(
106            SessionTrendRow,
107            r#"
108            SELECT
109                started_at as "started_at!",
110                user_id as "user_id: UserId",
111                duration_seconds
112            FROM user_sessions
113            WHERE started_at >= $1 AND started_at < $2
114              AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
115            ORDER BY started_at
116            "#,
117            start,
118            end
119        )
120        .fetch_all(&*self.pool)
121        .await
122        .map_err(Into::into)
123    }
124
125    pub async fn get_active_count_since(&self, start: DateTime<Utc>) -> Result<i64> {
126        let count = sqlx::query_scalar!(
127            r#"
128            SELECT COUNT(*)::bigint as "count!"
129            FROM user_sessions
130            WHERE ended_at IS NULL
131              AND last_activity_at >= $1
132              AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
133            "#,
134            start
135        )
136        .fetch_one(&*self.pool)
137        .await?;
138        Ok(count)
139    }
140
141    pub async fn get_total_count(&self, start: DateTime<Utc>, end: DateTime<Utc>) -> Result<i64> {
142        let count = sqlx::query_scalar!(
143            r#"SELECT COUNT(*)::bigint as "count!" FROM user_sessions WHERE started_at >= $1 AND started_at < $2 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false"#,
144            start,
145            end
146        )
147        .fetch_one(&*self.pool)
148        .await?;
149        Ok(count)
150    }
151}
systemprompt_analytics/repository/cli_sessions.rs

systemprompt_analytics/repository/
cli_sessions.rs
