systemprompt_analytics/repository/core_stats/
activity.rs

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

systemprompt_analytics/repository/core_stats/
activity.rs
