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

systemprompt_analytics/repository/
cli_sessions.rs
