systemprompt_analytics/repository/
engagement.rs

1//! Persistence for page-level engagement telemetry.
2//!
3//! [`EngagementRepository`] records [`EngagementEvent`]s (scroll, click,
4//! focus, and reading-pattern metrics) and reads them back per session or
5//! user, plus the aggregated [`SessionEngagementSummary`]. Writes go to the
6//! write pool; reads to the read pool.
7
8use std::sync::Arc;
9
10use crate::Result;
11use sqlx::PgPool;
12use systemprompt_database::DbPool;
13use systemprompt_identifiers::{ContentId, EngagementEventId, SessionId, UserId};
14
15use crate::models::{CreateEngagementEventInput, EngagementEvent};
16
17#[derive(Clone, Debug)]
18pub struct EngagementRepository {
19    pool: Arc<PgPool>,
20    write_pool: Arc<PgPool>,
21}
22
23impl EngagementRepository {
24    pub fn new(db: &DbPool) -> Result<Self> {
25        let pool = db.pool_arc()?;
26        let write_pool = db.write_pool_arc()?;
27        Ok(Self { pool, write_pool })
28    }
29
30    pub async fn create_engagement(
31        &self,
32        session_id: &SessionId,
33        user_id: &UserId,
34        content_id: Option<&ContentId>,
35        input: &CreateEngagementEventInput,
36    ) -> Result<EngagementEventId> {
37        let id = EngagementEventId::generate();
38
39        sqlx::query!(
40            r#"
41            INSERT INTO engagement_events (
42                id, session_id, user_id, page_url, content_id, event_type,
43                time_on_page_ms, max_scroll_depth, click_count,
44                time_to_first_interaction_ms, time_to_first_scroll_ms,
45                scroll_velocity_avg, scroll_direction_changes,
46                mouse_move_distance_px, keyboard_events, copy_events,
47                focus_time_ms, blur_count, tab_switches, visible_time_ms, hidden_time_ms,
48                is_rage_click, is_dead_click, reading_pattern, event_data
49            )
50            VALUES (
51                $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,
52                $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25
53            )
54            "#,
55            id.as_str(),
56            session_id.as_str(),
57            user_id.as_str(),
58            input.page_url,
59            content_id.map(ContentId::as_str),
60            input.event_type.as_str(),
61            input.time_on_page_ms,
62            input.max_scroll_depth,
63            input.click_count,
64            input.optional_metrics.time_to_first_interaction_ms,
65            input.optional_metrics.time_to_first_scroll_ms,
66            input.optional_metrics.scroll_velocity_avg,
67            input.optional_metrics.scroll_direction_changes,
68            input.optional_metrics.mouse_move_distance_px,
69            input.optional_metrics.keyboard_events,
70            input.optional_metrics.copy_events,
71            input.optional_metrics.focus_time_ms.unwrap_or(0),
72            input.optional_metrics.blur_count.unwrap_or(0),
73            input.optional_metrics.tab_switches.unwrap_or(0),
74            input.optional_metrics.visible_time_ms.unwrap_or(0),
75            input.optional_metrics.hidden_time_ms.unwrap_or(0),
76            input.optional_metrics.is_rage_click,
77            input.optional_metrics.is_dead_click,
78            input.optional_metrics.reading_pattern,
79            input.event_data.clone()
80        )
81        .execute(&*self.write_pool)
82        .await?;
83
84        Ok(id)
85    }
86
87    pub async fn find_by_id(&self, id: &EngagementEventId) -> Result<Option<EngagementEvent>> {
88        let event = sqlx::query_as!(
89            EngagementEvent,
90            r#"
91            SELECT
92                id as "id: EngagementEventId", session_id, user_id, page_url,
93                content_id as "content_id: ContentId",
94                event_type,
95                time_on_page_ms, time_to_first_interaction_ms, time_to_first_scroll_ms,
96                max_scroll_depth, scroll_velocity_avg, scroll_direction_changes,
97                click_count, mouse_move_distance_px, keyboard_events, copy_events,
98                focus_time_ms as "focus_time_ms!",
99                blur_count as "blur_count!",
100                tab_switches as "tab_switches!",
101                visible_time_ms as "visible_time_ms!",
102                hidden_time_ms as "hidden_time_ms!",
103                is_rage_click, is_dead_click, reading_pattern,
104                created_at, updated_at
105            FROM engagement_events
106            WHERE id = $1
107            "#,
108            id.as_str()
109        )
110        .fetch_optional(&*self.pool)
111        .await?;
112
113        Ok(event)
114    }
115
116    pub async fn list_by_session(&self, session_id: &SessionId) -> Result<Vec<EngagementEvent>> {
117        let events = sqlx::query_as!(
118            EngagementEvent,
119            r#"
120            SELECT
121                id as "id: EngagementEventId", session_id, user_id, page_url,
122                content_id as "content_id: ContentId",
123                event_type,
124                time_on_page_ms as "time_on_page_ms!", time_to_first_interaction_ms, time_to_first_scroll_ms,
125                max_scroll_depth as "max_scroll_depth!", scroll_velocity_avg, scroll_direction_changes,
126                click_count as "click_count!", mouse_move_distance_px, keyboard_events, copy_events,
127                focus_time_ms as "focus_time_ms!",
128                blur_count as "blur_count!",
129                tab_switches as "tab_switches!",
130                visible_time_ms as "visible_time_ms!",
131                hidden_time_ms as "hidden_time_ms!",
132                is_rage_click, is_dead_click, reading_pattern,
133                created_at, updated_at
134            FROM engagement_events
135            WHERE session_id = $1
136            ORDER BY created_at ASC
137            "#,
138            session_id.as_str()
139        )
140        .fetch_all(&*self.pool)
141        .await?;
142
143        Ok(events)
144    }
145
146    pub async fn list_by_user(&self, user_id: &UserId, limit: i64) -> Result<Vec<EngagementEvent>> {
147        let events = sqlx::query_as!(
148            EngagementEvent,
149            r#"
150            SELECT
151                id as "id: EngagementEventId", session_id, user_id, page_url,
152                content_id as "content_id: ContentId",
153                event_type,
154                time_on_page_ms as "time_on_page_ms!", time_to_first_interaction_ms, time_to_first_scroll_ms,
155                max_scroll_depth as "max_scroll_depth!", scroll_velocity_avg, scroll_direction_changes,
156                click_count as "click_count!", mouse_move_distance_px, keyboard_events, copy_events,
157                focus_time_ms as "focus_time_ms!",
158                blur_count as "blur_count!",
159                tab_switches as "tab_switches!",
160                visible_time_ms as "visible_time_ms!",
161                hidden_time_ms as "hidden_time_ms!",
162                is_rage_click, is_dead_click, reading_pattern,
163                created_at, updated_at
164            FROM engagement_events
165            WHERE user_id = $1
166            ORDER BY created_at DESC
167            LIMIT $2
168            "#,
169            user_id.as_str(),
170            limit
171        )
172        .fetch_all(&*self.pool)
173        .await?;
174
175        Ok(events)
176    }
177
178    pub async fn get_session_engagement_summary(
179        &self,
180        session_id: &SessionId,
181    ) -> Result<Option<SessionEngagementSummary>> {
182        let summary = sqlx::query_as!(
183            SessionEngagementSummary,
184            r#"
185            SELECT
186                session_id,
187                COUNT(*)::BIGINT as page_count,
188                SUM(time_on_page_ms)::BIGINT as total_time_on_page_ms,
189                AVG(max_scroll_depth)::REAL as avg_scroll_depth,
190                MAX(max_scroll_depth) as max_scroll_depth,
191                SUM(click_count)::BIGINT as total_clicks,
192                COUNT(*) FILTER (WHERE is_rage_click = true)::BIGINT as rage_click_pages,
193                MIN(created_at) as first_engagement,
194                MAX(created_at) as last_engagement
195            FROM engagement_events
196            WHERE session_id = $1
197            GROUP BY session_id
198            "#,
199            session_id.as_str()
200        )
201        .fetch_optional(&*self.pool)
202        .await?;
203
204        Ok(summary)
205    }
206}
207
208#[derive(Debug, Clone, sqlx::FromRow)]
209pub struct SessionEngagementSummary {
210    pub session_id: SessionId,
211    pub page_count: Option<i64>,
212    pub total_time_on_page_ms: Option<i64>,
213    pub avg_scroll_depth: Option<f32>,
214    pub max_scroll_depth: Option<i32>,
215    pub total_clicks: Option<i64>,
216    pub rage_click_pages: Option<i64>,
217    pub first_engagement: Option<chrono::DateTime<chrono::Utc>>,
218    pub last_engagement: Option<chrono::DateTime<chrono::Utc>>,
219}
systemprompt_analytics/repository/engagement.rs

systemprompt_analytics/repository/
engagement.rs
