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(LEAST(
33                    COALESCE(
34                        duration_seconds,
35                        EXTRACT(EPOCH FROM (COALESCE(ended_at, last_activity_at) - started_at))::INTEGER
36                    ),
37                    1800
38                ))::float8 as "avg_duration",
39                AVG(request_count)::float8 as "avg_requests",
40                COUNT(*) FILTER (WHERE converted_at IS NOT NULL)::bigint as "conversions!"
41            FROM user_sessions
42            WHERE started_at >= $1 AND started_at < $2
43              AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
44            "#,
45            start,
46            end
47        )
48        .fetch_one(&*self.pool)
49        .await
50        .map_err(Into::into)
51    }
52
53    pub async fn get_active_session_count(&self, since: DateTime<Utc>) -> Result<i64> {
54        let count = sqlx::query_scalar!(
55            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"#,
56            since
57        )
58        .fetch_one(&*self.pool)
59        .await?;
60        Ok(count)
61    }
62
63    pub async fn get_live_sessions(
64        &self,
65        cutoff: DateTime<Utc>,
66        limit: i64,
67    ) -> Result<Vec<LiveSessionRow>> {
68        sqlx::query_as!(
69            LiveSessionRow,
70            r#"
71            SELECT
72                session_id as "session_id!",
73                COALESCE(user_type, 'unknown') as "user_type",
74                started_at as "started_at!",
75                duration_seconds,
76                request_count,
77                last_activity_at as "last_activity_at!"
78            FROM user_sessions
79            WHERE ended_at IS NULL
80              AND last_activity_at >= $1
81              AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
82            ORDER BY last_activity_at DESC
83            LIMIT $2
84            "#,
85            cutoff,
86            limit
87        )
88        .fetch_all(&*self.pool)
89        .await
90        .map_err(Into::into)
91    }
92
93    pub async fn get_active_count(&self, cutoff: DateTime<Utc>) -> Result<i64> {
94        let count = sqlx::query_scalar!(
95            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"#,
96            cutoff
97        )
98        .fetch_one(&*self.pool)
99        .await?;
100        Ok(count)
101    }
102
103    pub async fn get_sessions_for_trends(
104        &self,
105        start: DateTime<Utc>,
106        end: DateTime<Utc>,
107    ) -> Result<Vec<SessionTrendRow>> {
108        sqlx::query_as!(
109            SessionTrendRow,
110            r#"
111            SELECT
112                started_at as "started_at!",
113                user_id as "user_id: UserId",
114                duration_seconds
115            FROM user_sessions
116            WHERE started_at >= $1 AND started_at < $2
117              AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
118            ORDER BY started_at
119            "#,
120            start,
121            end
122        )
123        .fetch_all(&*self.pool)
124        .await
125        .map_err(Into::into)
126    }
127
128    pub async fn get_active_count_since(&self, start: DateTime<Utc>) -> Result<i64> {
129        let count = sqlx::query_scalar!(
130            r#"
131            SELECT COUNT(*)::bigint as "count!"
132            FROM user_sessions
133            WHERE ended_at IS NULL
134              AND last_activity_at >= $1
135              AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
136            "#,
137            start
138        )
139        .fetch_one(&*self.pool)
140        .await?;
141        Ok(count)
142    }
143
144    pub async fn get_total_count(&self, start: DateTime<Utc>, end: DateTime<Utc>) -> Result<i64> {
145        let count = sqlx::query_scalar!(
146            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"#,
147            start,
148            end
149        )
150        .fetch_one(&*self.pool)
151        .await?;
152        Ok(count)
153    }
154}
systemprompt_analytics/repository/cli_sessions.rs

systemprompt_analytics/repository/
cli_sessions.rs
