systemprompt_analytics/repository/
requests.rs

1//! AI-request analytics over the `ai_requests` table.
2//!
3//! [`RequestAnalyticsRepository`] reports token, cost, latency, and
4//! cache-hit stats, per-model usage breakdowns, trend series, and a request
5//! list, each optionally filtered by a model substring.
6//!
7//! Copyright (c) systemprompt.io — Business Source License 1.1.
8//! See <https://systemprompt.io> for licensing details.
9
10use crate::Result;
11use chrono::{DateTime, Utc};
12use sqlx::PgPool;
13use std::sync::Arc;
14use systemprompt_database::DbPool;
15use systemprompt_identifiers::UserId;
16
17use crate::models::reporting::{ModelUsageRow, RequestListRow, RequestStatsRow, RequestTrendRow};
18
19#[derive(Debug)]
20pub struct RequestAnalyticsRepository {
21    pool: Arc<PgPool>,
22}
23
24impl RequestAnalyticsRepository {
25    pub fn new(db: &DbPool) -> Result<Self> {
26        let pool = db.pool_arc()?;
27        Ok(Self { pool })
28    }
29
30    pub async fn get_stats(
31        &self,
32        start: DateTime<Utc>,
33        end: DateTime<Utc>,
34        model_filter: Option<&str>,
35    ) -> Result<RequestStatsRow> {
36        if let Some(model) = model_filter {
37            let pattern = format!("%{model}%");
38            sqlx::query_as!(
39                RequestStatsRow,
40                r#"
41                SELECT
42                    COUNT(*)::bigint as "total!",
43                    SUM(tokens_used)::bigint as "total_tokens",
44                    SUM(input_tokens)::bigint as "input_tokens",
45                    SUM(output_tokens)::bigint as "output_tokens",
46                    SUM(reasoning_tokens)::bigint as "reasoning_tokens",
47                    SUM(cache_read_tokens)::bigint as "cache_read_tokens",
48                    SUM(cache_creation_tokens)::bigint as "cache_creation_tokens",
49                    SUM(cost_microdollars)::bigint as "cost",
50                    AVG(latency_ms)::float8 as "avg_latency",
51                    COUNT(*) FILTER (WHERE cache_hit = true)::bigint as "cache_hits!"
52                FROM ai_requests
53                WHERE created_at >= $1 AND created_at < $2
54                  AND model ILIKE $3
55                "#,
56                start,
57                end,
58                pattern
59            )
60            .fetch_one(&*self.pool)
61            .await
62            .map_err(Into::into)
63        } else {
64            sqlx::query_as!(
65                RequestStatsRow,
66                r#"
67                SELECT
68                    COUNT(*)::bigint as "total!",
69                    SUM(tokens_used)::bigint as "total_tokens",
70                    SUM(input_tokens)::bigint as "input_tokens",
71                    SUM(output_tokens)::bigint as "output_tokens",
72                    SUM(reasoning_tokens)::bigint as "reasoning_tokens",
73                    SUM(cache_read_tokens)::bigint as "cache_read_tokens",
74                    SUM(cache_creation_tokens)::bigint as "cache_creation_tokens",
75                    SUM(cost_microdollars)::bigint as "cost",
76                    AVG(latency_ms)::float8 as "avg_latency",
77                    COUNT(*) FILTER (WHERE cache_hit = true)::bigint as "cache_hits!"
78                FROM ai_requests
79                WHERE created_at >= $1 AND created_at < $2
80                "#,
81                start,
82                end
83            )
84            .fetch_one(&*self.pool)
85            .await
86            .map_err(Into::into)
87        }
88    }
89
90    pub async fn list_models(
91        &self,
92        start: DateTime<Utc>,
93        end: DateTime<Utc>,
94        limit: i64,
95    ) -> Result<Vec<ModelUsageRow>> {
96        sqlx::query_as!(
97            ModelUsageRow,
98            r#"
99            SELECT
100                provider as "provider!",
101                model as "model!",
102                COUNT(*)::bigint as "request_count!",
103                SUM(tokens_used)::bigint as "total_tokens",
104                SUM(cost_microdollars)::bigint as "total_cost",
105                AVG(latency_ms)::float8 as "avg_latency"
106            FROM ai_requests
107            WHERE created_at >= $1 AND created_at < $2
108              AND provider IS NOT NULL AND model IS NOT NULL
109            GROUP BY provider, model
110            ORDER BY COUNT(*) DESC
111            LIMIT $3
112            "#,
113            start,
114            end,
115            limit
116        )
117        .fetch_all(&*self.pool)
118        .await
119        .map_err(Into::into)
120    }
121
122    pub async fn get_requests_for_trends(
123        &self,
124        start: DateTime<Utc>,
125        end: DateTime<Utc>,
126    ) -> Result<Vec<RequestTrendRow>> {
127        sqlx::query_as!(
128            RequestTrendRow,
129            r#"
130            SELECT
131                created_at as "created_at!",
132                tokens_used,
133                cost_microdollars,
134                latency_ms
135            FROM ai_requests
136            WHERE created_at >= $1 AND created_at < $2
137            ORDER BY created_at
138            "#,
139            start,
140            end
141        )
142        .fetch_all(&*self.pool)
143        .await
144        .map_err(Into::into)
145    }
146
147    pub async fn list_requests(
148        &self,
149        start: DateTime<Utc>,
150        end: DateTime<Utc>,
151        limit: i64,
152        model_filter: Option<&str>,
153    ) -> Result<Vec<RequestListRow>> {
154        if let Some(model) = model_filter {
155            let pattern = format!("%{model}%");
156            sqlx::query_as!(
157                RequestListRow,
158                r#"
159                SELECT
160                    id as "id!",
161                    provider,
162                    model,
163                    input_tokens,
164                    output_tokens,
165                    cost_microdollars,
166                    latency_ms,
167                    cache_hit,
168                    created_at as "created_at!",
169                    status as "status!",
170                    error_message,
171                    user_id as "user_id!: UserId"
172                FROM ai_requests
173                WHERE created_at >= $1 AND created_at < $2
174                  AND model ILIKE $3
175                ORDER BY created_at DESC
176                LIMIT $4
177                "#,
178                start,
179                end,
180                pattern,
181                limit
182            )
183            .fetch_all(&*self.pool)
184            .await
185            .map_err(Into::into)
186        } else {
187            sqlx::query_as!(
188                RequestListRow,
189                r#"
190                SELECT
191                    id as "id!",
192                    provider,
193                    model,
194                    input_tokens,
195                    output_tokens,
196                    cost_microdollars,
197                    latency_ms,
198                    cache_hit,
199                    created_at as "created_at!",
200                    status as "status!",
201                    error_message,
202                    user_id as "user_id!: UserId"
203                FROM ai_requests
204                WHERE created_at >= $1 AND created_at < $2
205                ORDER BY created_at DESC
206                LIMIT $3
207                "#,
208                start,
209                end,
210                limit
211            )
212            .fetch_all(&*self.pool)
213            .await
214            .map_err(Into::into)
215        }
216    }
217}
systemprompt_analytics/repository/requests.rs

systemprompt_analytics/repository/
requests.rs
