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 v_clean_traffic 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                GROUP BY ee.content_id
46            )
47            SELECT
48                cs.content_id as "content_id!: ContentId",
49                mc.slug as "slug?",
50                mc.title as "title?",
51                mc.source_id as "source_id?: SourceId",
52                cs.total_views as "total_views!",
53                cs.unique_visitors as "unique_visitors!",
54                cs.avg_time_on_page_seconds::float8 as "avg_time_on_page_seconds",
55                NULL::text as "trend_direction"
56            FROM content_stats cs
57            LEFT JOIN markdown_content mc ON cs.content_id = mc.id
58            ORDER BY cs.total_views DESC
59            LIMIT $3
60            "#,
61            start,
62            end,
63            limit
64        )
65        .fetch_all(&*self.pool)
66        .await
67        .map_err(Into::into)
68    }
69
70    pub async fn get_stats(
71        &self,
72        start: DateTime<Utc>,
73        end: DateTime<Utc>,
74    ) -> Result<ContentStatsRow> {
75        sqlx::query_as!(
76            ContentStatsRow,
77            r#"
78            SELECT
79                COUNT(*)::bigint as "total_views!",
80                COUNT(DISTINCT ee.session_id)::bigint as "unique_visitors!",
81                COALESCE(AVG(LEAST(ee.time_on_page_ms, 1800000)) / 1000.0, 0)::float8 as "avg_time_on_page_seconds",
82                COALESCE(AVG(ee.max_scroll_depth), 0)::float8 as "avg_scroll_depth",
83                COALESCE(SUM(ee.click_count), 0)::bigint as "total_clicks!"
84            FROM engagement_events ee
85            INNER JOIN v_clean_traffic us ON ee.session_id = us.session_id
86            WHERE ee.created_at >= $1 AND ee.created_at < $2            "#,
87            start,
88            end
89        )
90        .fetch_one(&*self.pool)
91        .await
92        .map_err(Into::into)
93    }
94
95    pub async fn get_content_for_trends(
96        &self,
97        start: DateTime<Utc>,
98        end: DateTime<Utc>,
99    ) -> Result<Vec<ContentTrendRow>> {
100        sqlx::query_as!(
101            ContentTrendRow,
102            r#"
103            WITH date_series AS (
104                SELECT generate_series(
105                    date_trunc('day', $1::timestamptz),
106                    date_trunc('day', $2::timestamptz) - interval '1 day',
107                    '1 day'::interval
108                ) as day
109            ),
110            daily_stats AS (
111                SELECT
112                    date_trunc('day', ee.created_at) as day,
113                    COUNT(*)::bigint as views,
114                    COUNT(DISTINCT ee.session_id)::bigint as unique_visitors
115                FROM engagement_events ee
116                INNER JOIN v_clean_traffic us ON ee.session_id = us.session_id
117                WHERE ee.created_at >= $1 AND ee.created_at < $2                GROUP BY date_trunc('day', ee.created_at)
118            )
119            SELECT
120                ds.day as "timestamp!",
121                COALESCE(s.views, 0)::bigint as "views!",
122                COALESCE(s.unique_visitors, 0)::bigint as "unique_visitors!"
123            FROM date_series ds
124            LEFT JOIN daily_stats s ON ds.day = s.day
125            ORDER BY ds.day
126            "#,
127            start,
128            end
129        )
130        .fetch_all(&*self.pool)
131        .await
132        .map_err(Into::into)
133    }
134}
systemprompt_analytics/repository/content_analytics.rs

systemprompt_analytics/repository/
content_analytics.rs
