systemprompt_analytics/repository/core_stats/
activity.rs

1use anyhow::Result;
2use chrono::{Duration, Utc};
3
4use super::CoreStatsRepository;
5use crate::models::{ActivityTrend, ContentStat, RecentConversation};
6
7impl CoreStatsRepository {
8    pub async fn get_activity_trend(&self, days: i32) -> Result<Vec<ActivityTrend>> {
9        let cutoff = Utc::now() - Duration::days(i64::from(days));
10        sqlx::query_as!(
11            ActivityTrend,
12            r#"
13            SELECT
14                date_trunc('day', gs.date) as "date!",
15                COALESCE(s.sessions, 0) as "sessions!",
16                COALESCE(c.contexts, 0) as "contexts!",
17                COALESCE(t.tasks, 0) as "tasks!",
18                COALESCE(a.ai_requests, 0) as "ai_requests!",
19                COALESCE(e.tool_executions, 0) as "tool_executions!"
20            FROM generate_series($1::timestamptz, NOW(), '1 day') gs(date)
21            LEFT JOIN (
22                SELECT date_trunc('day', started_at) as day, COUNT(*) as sessions
23                FROM user_sessions WHERE started_at > $1 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
24                GROUP BY 1
25            ) s ON s.day = date_trunc('day', gs.date)
26            LEFT JOIN (
27                SELECT date_trunc('day', created_at) as day, COUNT(*) as contexts
28                FROM user_contexts WHERE created_at > $1
29                GROUP BY 1
30            ) c ON c.day = date_trunc('day', gs.date)
31            LEFT JOIN (
32                SELECT date_trunc('day', created_at) as day, COUNT(*) as tasks
33                FROM agent_tasks WHERE created_at > $1
34                GROUP BY 1
35            ) t ON t.day = date_trunc('day', gs.date)
36            LEFT JOIN (
37                SELECT date_trunc('day', created_at) as day, COUNT(*) as ai_requests
38                FROM ai_requests WHERE created_at > $1
39                GROUP BY 1
40            ) a ON a.day = date_trunc('day', gs.date)
41            LEFT JOIN (
42                SELECT date_trunc('day', created_at) as day, COUNT(*) as tool_executions
43                FROM mcp_tool_executions WHERE created_at > $1
44                GROUP BY 1
45            ) e ON e.day = date_trunc('day', gs.date)
46            ORDER BY date ASC
47            "#,
48            cutoff
49        )
50        .fetch_all(&*self.pool)
51        .await
52        .map_err(Into::into)
53    }
54
55    pub async fn get_recent_conversations(&self, limit: i64) -> Result<Vec<RecentConversation>> {
56        sqlx::query_as!(
57            RecentConversation,
58            r#"
59            SELECT
60                uc.context_id as "context_id!",
61                COALESCE(at.agent_name, 'unknown') as "agent_name!",
62                COALESCE(u.name, 'anonymous') as "user_name!",
63                COALESCE(at.status, 'unknown') as "status!",
64                COALESCE((
65                    SELECT COUNT(*)
66                    FROM task_messages tm
67                    JOIN agent_tasks at2 ON tm.task_id = at2.task_id
68                    WHERE at2.context_id = uc.context_id
69                ), 0) as "message_count!",
70                uc.created_at as "started_at!"
71            FROM user_contexts uc
72            LEFT JOIN agent_tasks at ON at.context_id = uc.context_id
73            LEFT JOIN users u ON u.id = uc.user_id
74            ORDER BY uc.created_at DESC
75            LIMIT $1
76            "#,
77            limit
78        )
79        .fetch_all(&*self.pool)
80        .await
81        .map_err(Into::into)
82    }
83
84    pub async fn get_content_stats(&self, limit: i64) -> Result<Vec<ContentStat>> {
85        sqlx::query_as!(
86            ContentStat,
87            r#"
88            SELECT
89                mc.title as "title!",
90                mc.slug as "slug!",
91                COUNT(ae.id) FILTER (WHERE ae.timestamp >= NOW() - INTERVAL '5 minutes') as "views_5m!",
92                COUNT(ae.id) FILTER (WHERE ae.timestamp >= NOW() - INTERVAL '1 hour') as "views_1h!",
93                COUNT(ae.id) FILTER (WHERE ae.timestamp >= NOW() - INTERVAL '1 day') as "views_1d!",
94                COUNT(ae.id) FILTER (WHERE ae.timestamp >= NOW() - INTERVAL '7 days') as "views_7d!",
95                COUNT(ae.id) FILTER (WHERE ae.timestamp >= NOW() - INTERVAL '30 days') as "views_30d!"
96            FROM markdown_content mc
97            LEFT JOIN analytics_events ae ON ae.endpoint = 'GET /' || mc.source_id || '/' || mc.slug
98                AND ae.event_type = 'page_view'
99            GROUP BY mc.id, mc.title, mc.slug
100            ORDER BY "views_7d!" DESC NULLS LAST
101            LIMIT $1
102            "#,
103            limit
104        )
105        .fetch_all(&*self.pool)
106        .await
107        .map_err(Into::into)
108    }
109}
systemprompt_analytics/repository/core_stats/activity.rs

systemprompt_analytics/repository/core_stats/
activity.rs
