systemprompt_analytics/repository/
queries.rs

1//! AI provider/model usage aggregates over `ai_requests`.
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              AND provider IS NOT NULL AND model IS NOT NULL
47            GROUP BY provider, model
48            ORDER BY COUNT(*) DESC
49            "#,
50            days,
51            user_filter,
52        )
53        .fetch_all(&*self.pool)
54        .await
55        .map_err(Into::into)
56    }
57}
58
59#[derive(Debug, Serialize)]
60pub struct ProviderUsage {
61    pub provider: String,
62    pub model: String,
63    pub request_count: i32,
64    pub total_tokens: Option<i32>,
65    pub total_cost_microdollars: Option<i64>,
66    pub avg_latency_ms: Option<f64>,
67    pub unique_users: i32,
68    pub unique_sessions: i32,
69}
systemprompt_analytics/repository/queries.rs

systemprompt_analytics/repository/
queries.rs
