systemprompt_analytics/repository/
queries.rs

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

systemprompt_analytics/repository/
queries.rs
