systemprompt_analytics/repository/core_stats/
activity.rs

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

systemprompt_analytics/repository/core_stats/
activity.rs
