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

systemprompt_analytics/repository/costs/
per_user.rs
