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