systemprompt_analytics/repository/
overview.rs

1use anyhow::Result;
2use chrono::{DateTime, Utc};
3use sqlx::PgPool;
4use std::sync::Arc;
5use systemprompt_database::DbPool;
6
7use crate::models::cli::{OverviewAgentRow, OverviewCostRow, OverviewRequestRow, OverviewToolRow};
8
9#[derive(Debug)]
10pub struct OverviewAnalyticsRepository {
11    pool: Arc<PgPool>,
12}
13
14impl OverviewAnalyticsRepository {
15    pub fn new(db: &DbPool) -> Result<Self> {
16        let pool = db.pool_arc()?;
17        Ok(Self { pool })
18    }
19
20    pub async fn get_conversation_count(
21        &self,
22        start: DateTime<Utc>,
23        end: DateTime<Utc>,
24    ) -> Result<i64> {
25        let count = sqlx::query_scalar!(
26            r#"SELECT COUNT(*)::bigint as "count!" FROM user_contexts WHERE created_at >= $1 AND created_at < $2"#,
27            start,
28            end
29        )
30        .fetch_one(&*self.pool)
31        .await?;
32        Ok(count)
33    }
34
35    pub async fn get_agent_metrics(
36        &self,
37        start: DateTime<Utc>,
38        end: DateTime<Utc>,
39    ) -> Result<OverviewAgentRow> {
40        sqlx::query_as!(
41            OverviewAgentRow,
42            r#"
43            SELECT
44                COUNT(DISTINCT agent_name)::bigint as "active_agents!",
45                COUNT(*)::bigint as "total_tasks!",
46                COUNT(*) FILTER (WHERE status = 'completed')::bigint as "completed_tasks!"
47            FROM agent_tasks
48            WHERE started_at >= $1 AND started_at < $2
49            "#,
50            start,
51            end
52        )
53        .fetch_one(&*self.pool)
54        .await
55        .map_err(Into::into)
56    }
57
58    pub async fn get_request_metrics(
59        &self,
60        start: DateTime<Utc>,
61        end: DateTime<Utc>,
62    ) -> Result<OverviewRequestRow> {
63        sqlx::query_as!(
64            OverviewRequestRow,
65            r#"
66            SELECT
67                COUNT(*)::bigint as "total!",
68                SUM(tokens_used)::bigint as "total_tokens",
69                AVG(latency_ms)::float8 as "avg_latency"
70            FROM ai_requests
71            WHERE created_at >= $1 AND created_at < $2
72            "#,
73            start,
74            end
75        )
76        .fetch_one(&*self.pool)
77        .await
78        .map_err(Into::into)
79    }
80
81    pub async fn get_tool_metrics(
82        &self,
83        start: DateTime<Utc>,
84        end: DateTime<Utc>,
85    ) -> Result<OverviewToolRow> {
86        sqlx::query_as!(
87            OverviewToolRow,
88            r#"
89            SELECT
90                COUNT(*)::bigint as "total!",
91                COUNT(*) FILTER (WHERE status = 'success')::bigint as "successful!"
92            FROM mcp_tool_executions
93            WHERE created_at >= $1 AND created_at < $2
94            "#,
95            start,
96            end
97        )
98        .fetch_one(&*self.pool)
99        .await
100        .map_err(Into::into)
101    }
102
103    pub async fn get_active_session_count(&self, since: DateTime<Utc>) -> Result<i64> {
104        let count = sqlx::query_scalar!(
105            r#"
106            SELECT COUNT(*)::bigint as "count!"
107            FROM user_sessions
108            WHERE ended_at IS NULL
109              AND last_activity_at >= $1
110              AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false
111            "#,
112            since
113        )
114        .fetch_one(&*self.pool)
115        .await?;
116        Ok(count)
117    }
118
119    pub async fn get_total_session_count(
120        &self,
121        start: DateTime<Utc>,
122        end: DateTime<Utc>,
123    ) -> Result<i64> {
124        let count = sqlx::query_scalar!(
125            r#"SELECT COUNT(*)::bigint as "count!" FROM user_sessions WHERE started_at >= $1 AND started_at < $2 AND is_bot = false AND is_behavioral_bot = false AND is_scanner = false"#,
126            start,
127            end
128        )
129        .fetch_one(&*self.pool)
130        .await?;
131        Ok(count)
132    }
133
134    pub async fn get_cost(
135        &self,
136        start: DateTime<Utc>,
137        end: DateTime<Utc>,
138    ) -> Result<OverviewCostRow> {
139        sqlx::query_as!(
140            OverviewCostRow,
141            r#"
142            SELECT SUM(cost_cents)::bigint as "cost"
143            FROM ai_requests
144            WHERE created_at >= $1 AND created_at < $2
145            "#,
146            start,
147            end
148        )
149        .fetch_one(&*self.pool)
150        .await
151        .map_err(Into::into)
152    }
153}
systemprompt_analytics/repository/overview.rs

systemprompt_analytics/repository/
overview.rs
