systemprompt_analytics/repository/
content_analytics.rs

1//! Repository for per-content view/engagement aggregates.
2//!
3//! Copyright (c) systemprompt.io — Business Source License 1.1.
4//! See <https://systemprompt.io> for licensing details.
5
6use crate::Result;
7use chrono::{DateTime, Utc};
8use sqlx::PgPool;
9use std::sync::Arc;
10use systemprompt_database::DbPool;
11
12use systemprompt_identifiers::{ContentId, SourceId};
13
14use crate::models::cli::{ContentStatsRow, ContentTrendRow, TopContentRow};
15
16#[derive(Debug)]
17pub struct ContentAnalyticsRepository {
18    pool: Arc<PgPool>,
19}
20
21impl ContentAnalyticsRepository {
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_top_content(
28        &self,
29        start: DateTime<Utc>,
30        end: DateTime<Utc>,
31        limit: i64,
32    ) -> Result<Vec<TopContentRow>> {
33        sqlx::query_as!(
34            TopContentRow,
35            r#"
36            WITH content_stats AS (
37                SELECT
38                    ee.content_id,
39                    COUNT(*)::bigint as total_views,
40                    COUNT(DISTINCT ee.session_id)::bigint as unique_visitors,
41                    (AVG(LEAST(ee.time_on_page_ms, 1800000)) / 1000.0)::float8 as avg_time_on_page_seconds
42                FROM engagement_events ee
43                INNER JOIN user_sessions us ON ee.session_id = us.session_id
44                WHERE ee.created_at >= $1 AND ee.created_at < $2
45                    AND ee.content_id IS NOT NULL
46                    AND us.is_bot = false AND us.is_behavioral_bot = false
47                GROUP BY ee.content_id
48            )
49            SELECT
50                cs.content_id as "content_id!: ContentId",
51                mc.slug as "slug?",
52                mc.title as "title?",
53                mc.source_id as "source_id?: SourceId",
54                cs.total_views as "total_views!",
55                cs.unique_visitors as "unique_visitors!",
56                cs.avg_time_on_page_seconds::float8 as "avg_time_on_page_seconds",
57                NULL::text as "trend_direction"
58            FROM content_stats cs
59            LEFT JOIN markdown_content mc ON cs.content_id = mc.id
60            ORDER BY cs.total_views DESC
61            LIMIT $3
62            "#,
63            start,
64            end,
65            limit
66        )
67        .fetch_all(&*self.pool)
68        .await
69        .map_err(Into::into)
70    }
71
72    pub async fn get_stats(
73        &self,
74        start: DateTime<Utc>,
75        end: DateTime<Utc>,
76    ) -> Result<ContentStatsRow> {
77        sqlx::query_as!(
78            ContentStatsRow,
79            r#"
80            SELECT
81                COUNT(*)::bigint as "total_views!",
82                COUNT(DISTINCT ee.session_id)::bigint as "unique_visitors!",
83                COALESCE(AVG(LEAST(ee.time_on_page_ms, 1800000)) / 1000.0, 0)::float8 as "avg_time_on_page_seconds",
84                COALESCE(AVG(ee.max_scroll_depth), 0)::float8 as "avg_scroll_depth",
85                COALESCE(SUM(ee.click_count), 0)::bigint as "total_clicks!"
86            FROM engagement_events ee
87            INNER JOIN user_sessions us ON ee.session_id = us.session_id
88            WHERE ee.created_at >= $1 AND ee.created_at < $2
89                AND us.is_bot = false AND us.is_behavioral_bot = false
90            "#,
91            start,
92            end
93        )
94        .fetch_one(&*self.pool)
95        .await
96        .map_err(Into::into)
97    }
98
99    pub async fn get_content_for_trends(
100        &self,
101        start: DateTime<Utc>,
102        end: DateTime<Utc>,
103    ) -> Result<Vec<ContentTrendRow>> {
104        sqlx::query_as!(
105            ContentTrendRow,
106            r#"
107            WITH date_series AS (
108                SELECT generate_series(
109                    date_trunc('day', $1::timestamptz),
110                    date_trunc('day', $2::timestamptz) - interval '1 day',
111                    '1 day'::interval
112                ) as day
113            ),
114            daily_stats AS (
115                SELECT
116                    date_trunc('day', ee.created_at) as day,
117                    COUNT(*)::bigint as views,
118                    COUNT(DISTINCT ee.session_id)::bigint as unique_visitors
119                FROM engagement_events ee
120                INNER JOIN user_sessions us ON ee.session_id = us.session_id
121                WHERE ee.created_at >= $1 AND ee.created_at < $2
122                    AND us.is_bot = false AND us.is_behavioral_bot = false
123                GROUP BY date_trunc('day', ee.created_at)
124            )
125            SELECT
126                ds.day as "timestamp!",
127                COALESCE(s.views, 0)::bigint as "views!",
128                COALESCE(s.unique_visitors, 0)::bigint as "unique_visitors!"
129            FROM date_series ds
130            LEFT JOIN daily_stats s ON ds.day = s.day
131            ORDER BY ds.day
132            "#,
133            start,
134            end
135        )
136        .fetch_all(&*self.pool)
137        .await
138        .map_err(Into::into)
139    }
140}
systemprompt_analytics/repository/content_analytics.rs

systemprompt_analytics/repository/
content_analytics.rs
