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 analytics_report_v_clean_traffic WHERE started_at > $1
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 analytics_report_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 analytics_report_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 analytics_report_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 analytics_report_mcp_tool_executions WHERE created_at > $1
50                  AND server_name NOT IN ('in_process', 'proxy', 'gateway', 'hook_claude_code', 'hook_opencode')
51                GROUP BY 1
52            ) e ON e.day = date_trunc('day', gs.date)
53            ORDER BY date ASC
54            "#,
55            cutoff,
56            ContextKind::User.as_str()
57        )
58        .fetch_all(&*self.pool)
59        .await
60        .map_err(Into::into)
61    }
62
63    pub async fn get_recent_conversations(&self, limit: i64) -> Result<Vec<RecentConversation>> {
64        sqlx::query_as!(
65            RecentConversation,
66            r#"
67            SELECT
68                uc.context_id as "context_id!: systemprompt_identifiers::ContextId",
69                COALESCE(at.agent_name, 'unknown') as "agent_name!",
70                COALESCE(u.name, 'anonymous') as "user_name!",
71                COALESCE(at.status, 'unknown') as "status!",
72                COALESCE((
73                    SELECT COUNT(*)
74                    FROM analytics_report_task_messages tm
75                    JOIN analytics_report_agent_tasks at2 ON tm.task_id = at2.task_id
76                    WHERE at2.context_id = uc.context_id
77                ), 0) as "message_count!",
78                uc.created_at as "started_at!"
79            FROM analytics_report_user_contexts uc
80            LEFT JOIN analytics_report_agent_tasks at ON at.context_id = uc.context_id
81            LEFT JOIN analytics_report_users u ON u.id = uc.user_id
82            WHERE uc.kind = $2
83            ORDER BY uc.created_at DESC
84            LIMIT $1
85            "#,
86            limit,
87            ContextKind::User.as_str()
88        )
89        .fetch_all(&*self.pool)
90        .await
91        .map_err(Into::into)
92    }
93
94    pub async fn get_content_stats(&self, limit: i64) -> Result<Vec<ContentStat>> {
95        sqlx::query_as!(
96            ContentStat,
97            r#"
98            SELECT
99                mc.title as "title!",
100                mc.slug as "slug!",
101                COUNT(ae.id) FILTER (WHERE ae.timestamp >= NOW() - INTERVAL '5 minutes') as "views_5m!",
102                COUNT(ae.id) FILTER (WHERE ae.timestamp >= NOW() - INTERVAL '1 hour') as "views_1h!",
103                COUNT(ae.id) FILTER (WHERE ae.timestamp >= NOW() - INTERVAL '1 day') as "views_1d!",
104                COUNT(ae.id) FILTER (WHERE ae.timestamp >= NOW() - INTERVAL '7 days') as "views_7d!",
105                COUNT(ae.id) FILTER (WHERE ae.timestamp >= NOW() - INTERVAL '30 days') as "views_30d!"
106            FROM analytics_report_markdown_content mc
107            LEFT JOIN analytics_report_analytics_events ae ON ae.endpoint = 'GET /' || mc.source_id || '/' || mc.slug
108                AND ae.event_type = 'page_view'
109            GROUP BY mc.id, mc.title, mc.slug
110            ORDER BY "views_7d!" DESC NULLS LAST
111            LIMIT $1
112            "#,
113            limit
114        )
115        .fetch_all(&*self.pool)
116        .await
117        .map_err(Into::into)
118    }
119}
systemprompt_analytics/repository/core_stats/activity.rs

systemprompt_analytics/repository/core_stats/
activity.rs
