systemprompt_analytics/repository/costs/
per_user.rs

1//! Per-user cost queries for `CostAnalyticsRepository`.
2//!
3//! Scopes spend, token, and conversation reads from `ai_requests` to a single
4//! [`UserId`], including model/agent breakdowns and recent-context summaries
5//! used for per-user billing and usage views.
6//!
7//! Copyright (c) systemprompt.io — Business Source License 1.1.
8//! See <https://systemprompt.io> for licensing details.
9
10use super::CostAnalyticsRepository;
11use crate::Result;
12use chrono::{DateTime, Utc};
13use systemprompt_identifiers::{ContextId, UserId};
14
15use crate::models::cli::{
16    ContextGroupRow, ContextSummaryRow, CostBreakdownRow, CostSummaryRow, PreviousCostRow,
17    RecentContextRow,
18};
19
20impl CostAnalyticsRepository {
21    pub async fn get_summary_for_user(
22        &self,
23        user_id: &UserId,
24        start: DateTime<Utc>,
25        end: DateTime<Utc>,
26    ) -> Result<CostSummaryRow> {
27        sqlx::query_as!(
28            CostSummaryRow,
29            r#"
30            SELECT
31                COUNT(*)::bigint as "requests!",
32                SUM(cost_microdollars)::bigint as "cost",
33                SUM(tokens_used)::bigint as "tokens"
34            FROM ai_requests
35            WHERE created_at >= $1 AND created_at < $2 AND user_id = $3
36            "#,
37            start,
38            end,
39            user_id.as_str()
40        )
41        .fetch_one(&*self.pool)
42        .await
43        .map_err(Into::into)
44    }
45
46    pub async fn get_previous_cost_for_user(
47        &self,
48        user_id: &UserId,
49        start: DateTime<Utc>,
50        end: DateTime<Utc>,
51    ) -> Result<PreviousCostRow> {
52        sqlx::query_as!(
53            PreviousCostRow,
54            r#"
55            SELECT SUM(cost_microdollars)::bigint as "cost"
56            FROM ai_requests
57            WHERE created_at >= $1 AND created_at < $2 AND user_id = $3
58            "#,
59            start,
60            end,
61            user_id.as_str()
62        )
63        .fetch_one(&*self.pool)
64        .await
65        .map_err(Into::into)
66    }
67
68    pub async fn get_breakdown_by_model_for_user(
69        &self,
70        user_id: &UserId,
71        start: DateTime<Utc>,
72        end: DateTime<Utc>,
73        limit: i64,
74    ) -> Result<Vec<CostBreakdownRow>> {
75        sqlx::query_as!(
76            CostBreakdownRow,
77            r#"
78            SELECT
79                model as "name!",
80                COALESCE(SUM(cost_microdollars), 0)::bigint as "cost!",
81                COUNT(*)::bigint as "requests!",
82                COALESCE(SUM(tokens_used), 0)::bigint as "tokens!"
83            FROM ai_requests
84            WHERE created_at >= $1 AND created_at < $2 AND user_id = $4
85            GROUP BY model
86            ORDER BY SUM(cost_microdollars) DESC NULLS LAST
87            LIMIT $3
88            "#,
89            start,
90            end,
91            limit,
92            user_id.as_str()
93        )
94        .fetch_all(&*self.pool)
95        .await
96        .map_err(Into::into)
97    }
98
99    pub async fn get_context_summary_for_user(
100        &self,
101        user_id: &UserId,
102        start: DateTime<Utc>,
103        end: DateTime<Utc>,
104    ) -> Result<ContextSummaryRow> {
105        sqlx::query_as!(
106            ContextSummaryRow,
107            r#"
108            SELECT
109                COUNT(DISTINCT context_id)::bigint as "conversations!",
110                COUNT(*)::bigint as "ai_requests!"
111            FROM ai_requests
112            WHERE created_at >= $1 AND created_at < $2
113              AND user_id = $3
114              AND context_id IS NOT NULL
115            "#,
116            start,
117            end,
118            user_id.as_str()
119        )
120        .fetch_one(&*self.pool)
121        .await
122        .map_err(Into::into)
123    }
124
125    pub async fn get_contexts_by_model_for_user(
126        &self,
127        user_id: &UserId,
128        start: DateTime<Utc>,
129        end: DateTime<Utc>,
130        limit: i64,
131    ) -> Result<Vec<ContextGroupRow>> {
132        sqlx::query_as!(
133            ContextGroupRow,
134            r#"
135            SELECT
136                model as "name!",
137                COUNT(DISTINCT context_id)::bigint as "conversations!",
138                COUNT(*)::bigint as "ai_requests!"
139            FROM ai_requests
140            WHERE created_at >= $1 AND created_at < $2
141              AND user_id = $3
142              AND context_id IS NOT NULL
143            GROUP BY model
144            ORDER BY COUNT(DISTINCT context_id) DESC
145            LIMIT $4
146            "#,
147            start,
148            end,
149            user_id.as_str(),
150            limit
151        )
152        .fetch_all(&*self.pool)
153        .await
154        .map_err(Into::into)
155    }
156
157    pub async fn get_contexts_by_agent_for_user(
158        &self,
159        user_id: &UserId,
160        start: DateTime<Utc>,
161        end: DateTime<Utc>,
162        limit: i64,
163    ) -> Result<Vec<ContextGroupRow>> {
164        sqlx::query_as!(
165            ContextGroupRow,
166            r#"
167            SELECT
168                COALESCE(at.agent_name, 'unattributed') as "name!",
169                COUNT(DISTINCT r.context_id)::bigint as "conversations!",
170                COUNT(*)::bigint as "ai_requests!"
171            FROM ai_requests r
172            LEFT JOIN agent_tasks at ON at.task_id = r.task_id
173            WHERE r.created_at >= $1 AND r.created_at < $2
174              AND r.user_id = $3
175              AND r.context_id IS NOT NULL
176            GROUP BY COALESCE(at.agent_name, 'unattributed')
177            ORDER BY COUNT(DISTINCT r.context_id) DESC
178            LIMIT $4
179            "#,
180            start,
181            end,
182            user_id.as_str(),
183            limit
184        )
185        .fetch_all(&*self.pool)
186        .await
187        .map_err(Into::into)
188    }
189
190    pub async fn get_recent_contexts_for_user(
191        &self,
192        user_id: &UserId,
193        end: DateTime<Utc>,
194        limit: i64,
195    ) -> Result<Vec<RecentContextRow>> {
196        sqlx::query_as!(
197            RecentContextRow,
198            r#"
199            SELECT
200                ctx.context_id as "context_id!: ContextId",
201                ctx.last_activity as "last_activity!",
202                ctx.ai_requests as "ai_requests!",
203                last_req.model,
204                last_task.agent_name
205            FROM (
206                SELECT
207                    r.context_id,
208                    MAX(r.created_at) AS last_activity,
209                    COUNT(*) AS ai_requests
210                FROM ai_requests r
211                WHERE r.user_id = $1
212                  AND r.created_at < $2
213                  AND r.context_id IS NOT NULL
214                GROUP BY r.context_id
215                ORDER BY MAX(r.created_at) DESC
216                LIMIT $3
217            ) ctx
218            LEFT JOIN LATERAL (
219                SELECT model, task_id FROM ai_requests
220                WHERE context_id = ctx.context_id
221                ORDER BY created_at DESC
222                LIMIT 1
223            ) last_req ON TRUE
224            LEFT JOIN agent_tasks last_task ON last_task.task_id = last_req.task_id
225            ORDER BY ctx.last_activity DESC
226            "#,
227            user_id.as_str(),
228            end,
229            limit
230        )
231        .fetch_all(&*self.pool)
232        .await
233        .map_err(Into::into)
234    }
235}
systemprompt_analytics/repository/costs/per_user.rs

systemprompt_analytics/repository/costs/
per_user.rs
