systemprompt_analytics/repository/
queries.rs

1//! Analytics aggregate queries (traffic, sources, devices).
2//!
3//! Copyright (c) systemprompt.io — Business Source License 1.1.
4//! See <https://systemprompt.io> for licensing details.
5
6use crate::Result;
7use serde::Serialize;
8use sqlx::PgPool;
9use std::sync::Arc;
10use systemprompt_database::DbPool;
11use systemprompt_identifiers::UserId;
12
13#[derive(Debug, Clone)]
14pub struct AnalyticsQueryRepository {
15    pool: Arc<PgPool>,
16}
17
18impl AnalyticsQueryRepository {
19    pub fn new(db: &DbPool) -> Result<Self> {
20        let pool = db.pool_arc()?;
21        Ok(Self { pool })
22    }
23
24    pub async fn get_ai_provider_usage(
25        &self,
26        days: i32,
27        user_id: Option<&UserId>,
28    ) -> Result<Vec<ProviderUsage>> {
29        let user_filter = user_id.map(UserId::as_str);
30
31        sqlx::query_as!(
32            ProviderUsage,
33            r#"
34            SELECT
35                provider AS "provider!",
36                model AS "model!",
37                COUNT(*)::int AS "request_count!",
38                SUM(tokens_used)::int AS "total_tokens",
39                SUM(cost_microdollars)::bigint AS "total_cost_microdollars",
40                AVG(latency_ms)::float8 AS "avg_latency_ms",
41                COUNT(DISTINCT user_id)::int AS "unique_users!",
42                COUNT(DISTINCT session_id)::int AS "unique_sessions!"
43            FROM ai_requests
44            WHERE created_at >= NOW() - INTERVAL '1 day' * $1::int
45              AND ($2::text IS NULL OR user_id = $2)
46            GROUP BY provider, model
47            ORDER BY COUNT(*) DESC
48            "#,
49            days,
50            user_filter,
51        )
52        .fetch_all(&*self.pool)
53        .await
54        .map_err(Into::into)
55    }
56}
57
58#[derive(Debug, Serialize)]
59pub struct ProviderUsage {
60    pub provider: String,
61    pub model: String,
62    pub request_count: i32,
63    pub total_tokens: Option<i32>,
64    pub total_cost_microdollars: Option<i64>,
65    pub avg_latency_ms: Option<f64>,
66    pub unique_users: i32,
67    pub unique_sessions: i32,
68}
systemprompt_analytics/repository/queries.rs

systemprompt_analytics/repository/
queries.rs
