systemprompt_analytics/repository/traffic/
mod.rs

1//! Traffic-source, geography, device, and bot analytics.
2//!
3//! [`TrafficAnalyticsRepository`] reads `user_sessions` to break sessions
4//! down by referrer source, country, and device, and to classify human
5//! versus bot traffic (including a user-agent-driven bot taxonomy). An
6//! `engaged_only` flag restricts the human-facing breakdowns to sessions with
7//! a landing page and at least one request.
8//!
9//! Copyright (c) systemprompt.io — Business Source License 1.1.
10//! See <https://systemprompt.io> for licensing details.
11
12mod audience;
13mod pages;
14
15use crate::Result;
16use chrono::{DateTime, Utc};
17use sqlx::PgPool;
18use std::sync::Arc;
19use systemprompt_database::DbPool;
20
21use crate::models::reporting::TrafficSourceRow;
22
23#[derive(Debug, Clone, Copy)]
24pub struct PageQuery<'a> {
25    pub start: DateTime<Utc>,
26    pub end: DateTime<Utc>,
27    pub limit: i64,
28    pub engaged_only: bool,
29    pub referrer: Option<&'a str>,
30    pub path_prefix: Option<&'a str>,
31}
32
33#[derive(Debug, Clone, Copy)]
34pub struct NavigationQuery<'a> {
35    pub start: DateTime<Utc>,
36    pub end: DateTime<Utc>,
37    pub limit: i64,
38    pub path_prefix: Option<&'a str>,
39    pub internal_only: bool,
40}
41
42#[derive(Debug)]
43pub struct TrafficAnalyticsRepository {
44    pool: Arc<PgPool>,
45}
46
47impl TrafficAnalyticsRepository {
48    pub fn new(db: &DbPool) -> Result<Self> {
49        let pool = db.pool_arc()?;
50        Ok(Self { pool })
51    }
52
53    pub async fn get_sources(
54        &self,
55        start: DateTime<Utc>,
56        end: DateTime<Utc>,
57        limit: i64,
58        engaged_only: bool,
59    ) -> Result<Vec<TrafficSourceRow>> {
60        if engaged_only {
61            sqlx::query_as!(
62                TrafficSourceRow,
63                r#"
64                SELECT
65                    COALESCE(referrer_source, 'direct') as "source",
66                    COUNT(*)::bigint as "count!"
67                FROM v_engaged_traffic
68                WHERE started_at >= $1 AND started_at < $2
69                GROUP BY referrer_source
70                ORDER BY COUNT(*) DESC
71                LIMIT $3
72                "#,
73                start,
74                end,
75                limit
76            )
77            .fetch_all(&*self.pool)
78            .await
79            .map_err(Into::into)
80        } else {
81            sqlx::query_as!(
82                TrafficSourceRow,
83                r#"
84                SELECT
85                    COALESCE(referrer_source, 'direct') as "source",
86                    COUNT(*)::bigint as "count!"
87                FROM v_clean_traffic
88                WHERE started_at >= $1 AND started_at < $2
89                GROUP BY referrer_source
90                ORDER BY COUNT(*) DESC
91                LIMIT $3
92                "#,
93                start,
94                end,
95                limit
96            )
97            .fetch_all(&*self.pool)
98            .await
99            .map_err(Into::into)
100        }
101    }
102}
systemprompt_analytics/repository/traffic/mod.rs

systemprompt_analytics/repository/traffic/
mod.rs
