systemprompt_analytics/repository/
cli_sessions.rs

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

systemprompt_analytics/repository/
cli_sessions.rs
