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