systemprompt_analytics/repository/
traffic.rs1use crate::Result;
13use chrono::{DateTime, Utc};
14use sqlx::PgPool;
15use std::sync::Arc;
16use systemprompt_database::DbPool;
17
18use crate::models::cli::{BotTotalsRow, BotTypeRow, DeviceRow, GeoRow, TrafficSourceRow};
19
20#[derive(Debug)]
21pub struct TrafficAnalyticsRepository {
22 pool: Arc<PgPool>,
23}
24
25impl TrafficAnalyticsRepository {
26 pub fn new(db: &DbPool) -> Result<Self> {
27 let pool = db.pool_arc()?;
28 Ok(Self { pool })
29 }
30
31 pub async fn get_sources(
32 &self,
33 start: DateTime<Utc>,
34 end: DateTime<Utc>,
35 limit: i64,
36 engaged_only: bool,
37 ) -> Result<Vec<TrafficSourceRow>> {
38 if engaged_only {
39 sqlx::query_as!(
40 TrafficSourceRow,
41 r#"
42 SELECT
43 COALESCE(referrer_source, 'direct') as "source",
44 COUNT(*)::bigint as "count!"
45 FROM user_sessions
46 WHERE started_at >= $1 AND started_at < $2
47 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
48 AND landing_page IS NOT NULL AND request_count > 0
49 GROUP BY referrer_source
50 ORDER BY COUNT(*) DESC
51 LIMIT $3
52 "#,
53 start,
54 end,
55 limit
56 )
57 .fetch_all(&*self.pool)
58 .await
59 .map_err(Into::into)
60 } else {
61 sqlx::query_as!(
62 TrafficSourceRow,
63 r#"
64 SELECT
65 COALESCE(referrer_source, 'direct') as "source",
66 COUNT(*)::bigint as "count!"
67 FROM user_sessions
68 WHERE started_at >= $1 AND started_at < $2
69 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
70 GROUP BY referrer_source
71 ORDER BY COUNT(*) DESC
72 LIMIT $3
73 "#,
74 start,
75 end,
76 limit
77 )
78 .fetch_all(&*self.pool)
79 .await
80 .map_err(Into::into)
81 }
82 }
83
84 pub async fn get_geo_breakdown(
85 &self,
86 start: DateTime<Utc>,
87 end: DateTime<Utc>,
88 limit: i64,
89 engaged_only: bool,
90 ) -> Result<Vec<GeoRow>> {
91 if engaged_only {
92 sqlx::query_as!(
93 GeoRow,
94 r#"
95 SELECT
96 COALESCE(country, 'Unknown') as "country",
97 COUNT(*)::bigint as "count!"
98 FROM user_sessions
99 WHERE started_at >= $1 AND started_at < $2
100 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
101 AND landing_page IS NOT NULL AND request_count > 0
102 GROUP BY country
103 ORDER BY COUNT(*) DESC
104 LIMIT $3
105 "#,
106 start,
107 end,
108 limit
109 )
110 .fetch_all(&*self.pool)
111 .await
112 .map_err(Into::into)
113 } else {
114 sqlx::query_as!(
115 GeoRow,
116 r#"
117 SELECT
118 COALESCE(country, 'Unknown') as "country",
119 COUNT(*)::bigint as "count!"
120 FROM user_sessions
121 WHERE started_at >= $1 AND started_at < $2
122 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
123 GROUP BY country
124 ORDER BY COUNT(*) DESC
125 LIMIT $3
126 "#,
127 start,
128 end,
129 limit
130 )
131 .fetch_all(&*self.pool)
132 .await
133 .map_err(Into::into)
134 }
135 }
136
137 pub async fn get_device_breakdown(
138 &self,
139 start: DateTime<Utc>,
140 end: DateTime<Utc>,
141 limit: i64,
142 engaged_only: bool,
143 ) -> Result<Vec<DeviceRow>> {
144 if engaged_only {
145 sqlx::query_as!(
146 DeviceRow,
147 r#"
148 SELECT
149 COALESCE(device_type, 'unknown') as "device",
150 COALESCE(browser, 'unknown') as "browser",
151 COUNT(*)::bigint as "count!"
152 FROM user_sessions
153 WHERE started_at >= $1 AND started_at < $2
154 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
155 AND landing_page IS NOT NULL AND request_count > 0
156 GROUP BY device_type, browser
157 ORDER BY COUNT(*) DESC
158 LIMIT $3
159 "#,
160 start,
161 end,
162 limit
163 )
164 .fetch_all(&*self.pool)
165 .await
166 .map_err(Into::into)
167 } else {
168 sqlx::query_as!(
169 DeviceRow,
170 r#"
171 SELECT
172 COALESCE(device_type, 'unknown') as "device",
173 COALESCE(browser, 'unknown') as "browser",
174 COUNT(*)::bigint as "count!"
175 FROM user_sessions
176 WHERE started_at >= $1 AND started_at < $2
177 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
178 GROUP BY device_type, browser
179 ORDER BY COUNT(*) DESC
180 LIMIT $3
181 "#,
182 start,
183 end,
184 limit
185 )
186 .fetch_all(&*self.pool)
187 .await
188 .map_err(Into::into)
189 }
190 }
191
192 pub async fn get_bot_totals(
193 &self,
194 start: DateTime<Utc>,
195 end: DateTime<Utc>,
196 engaged_only: bool,
197 ) -> Result<BotTotalsRow> {
198 if engaged_only {
199 sqlx::query_as!(
200 BotTotalsRow,
201 r#"
202 SELECT
203 COUNT(*) FILTER (WHERE is_bot = false AND is_behavioral_bot = false AND is_scanner = false AND landing_page IS NOT NULL AND request_count > 0)::bigint as "human!",
204 COUNT(*) FILTER (WHERE is_bot = true OR is_behavioral_bot = true OR is_scanner = true OR landing_page IS NULL OR request_count = 0)::bigint as "bot!"
205 FROM user_sessions
206 WHERE started_at >= $1 AND started_at < $2
207 "#,
208 start,
209 end
210 )
211 .fetch_one(&*self.pool)
212 .await
213 .map_err(Into::into)
214 } else {
215 sqlx::query_as!(
216 BotTotalsRow,
217 r#"
218 SELECT
219 COUNT(*) FILTER (WHERE is_bot = false AND is_behavioral_bot = false AND is_scanner = false)::bigint as "human!",
220 COUNT(*) FILTER (WHERE is_bot = true OR is_behavioral_bot = true OR is_scanner = true)::bigint as "bot!"
221 FROM user_sessions
222 WHERE started_at >= $1 AND started_at < $2
223 "#,
224 start,
225 end
226 )
227 .fetch_one(&*self.pool)
228 .await
229 .map_err(Into::into)
230 }
231 }
232
233 pub async fn get_bot_breakdown(
234 &self,
235 start: DateTime<Utc>,
236 end: DateTime<Utc>,
237 ) -> Result<Vec<BotTypeRow>> {
238 sqlx::query_as!(
239 BotTypeRow,
240 r#"
241 SELECT
242 CASE
243 WHEN user_agent ILIKE '%googlebot%' OR user_agent ILIKE '%google-inspectiontool%' OR user_agent ILIKE '%adsbot-google%' THEN 'Google'
244 WHEN user_agent ILIKE '%bingbot%' OR user_agent ILIKE '%bingpreview%' OR user_agent ILIKE '%msnbot%' THEN 'Bing'
245 WHEN user_agent ILIKE '%chatgpt%' OR user_agent ILIKE '%gptbot%' THEN 'OpenAI'
246 WHEN user_agent ILIKE '%claude%' OR user_agent ILIKE '%anthropic%' THEN 'Anthropic'
247 WHEN user_agent ILIKE '%perplexity%' THEN 'Perplexity'
248 WHEN user_agent ILIKE '%baiduspider%' THEN 'Baidu'
249 WHEN user_agent ILIKE '%yandexbot%' THEN 'Yandex'
250 WHEN user_agent ILIKE '%facebookexternalhit%' OR user_agent ILIKE '%facebot%' OR user_agent ILIKE '%meta-externalagent%' THEN 'Meta'
251 WHEN user_agent ILIKE '%twitterbot%' THEN 'Twitter/X'
252 WHEN user_agent ILIKE '%linkedinbot%' THEN 'LinkedIn'
253 WHEN user_agent ILIKE '%semrushbot%' OR user_agent ILIKE '%ahrefsbot%' OR user_agent ILIKE '%mj12bot%' OR user_agent ILIKE '%dotbot%' THEN 'SEO Crawlers'
254 WHEN user_agent ILIKE '%bytespider%' THEN 'ByteDance'
255 WHEN user_agent ILIKE '%amazonbot%' OR user_agent ILIKE '%applebot%' THEN 'Tech Giants'
256 WHEN user_agent ILIKE '%python%' OR user_agent ILIKE '%scrapy%' OR user_agent ILIKE '%httpx%' THEN 'Python Scrapers'
257 WHEN user_agent ILIKE '%curl%' OR user_agent ILIKE '%wget%' OR user_agent ILIKE '%node-fetch%' OR user_agent ILIKE '%axios%' THEN 'CLI/HTTP Tools'
258 WHEN user_agent ILIKE '%headless%' OR user_agent ILIKE '%phantom%' OR user_agent ILIKE '%selenium%' OR user_agent ILIKE '%puppeteer%' THEN 'Headless Browsers'
259 WHEN user_agent ILIKE '%uptimerobot%' OR user_agent ILIKE '%pingdom%' OR user_agent ILIKE '%statuscake%' OR user_agent ILIKE '%lighthouse%' THEN 'Monitoring'
260 WHEN is_behavioral_bot = true THEN 'Behavioral Bot'
261 WHEN is_scanner = true THEN 'Scanner'
262 ELSE 'Other'
263 END as "bot_type",
264 COUNT(*)::bigint as "count!"
265 FROM user_sessions
266 WHERE started_at >= $1 AND started_at < $2
267 AND (is_bot = true OR is_behavioral_bot = true OR is_scanner = true)
268 GROUP BY 1
269 ORDER BY COUNT(*) DESC
270 "#,
271 start,
272 end
273 )
274 .fetch_all(&*self.pool)
275 .await
276 .map_err(Into::into)
277 }
278}