systemprompt_analytics/repository/costs/
platform.rs

1//! Platform-wide cost queries for `CostAnalyticsRepository`.
2//!
3//! Aggregates spend, tokens, and request counts across all users from
4//! `ai_requests`, with breakdowns by model, provider, and agent and a trend
5//! series for the platform cost dashboard.
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};
13
14use crate::models::reporting::{CostBreakdownRow, CostSummaryRow, CostTrendRow, PreviousCostRow};
15
16impl CostAnalyticsRepository {
17    pub async fn get_summary(
18        &self,
19        start: DateTime<Utc>,
20        end: DateTime<Utc>,
21    ) -> Result<CostSummaryRow> {
22        sqlx::query_as!(
23            CostSummaryRow,
24            r#"
25            SELECT
26                COUNT(*)::bigint as "requests!",
27                SUM(cost_microdollars)::bigint as "cost",
28                SUM(tokens_used)::bigint as "tokens"
29            FROM ai_requests
30            WHERE created_at >= $1 AND created_at < $2
31              AND NOT synthetic
32            "#,
33            start,
34            end
35        )
36        .fetch_one(&*self.pool)
37        .await
38        .map_err(Into::into)
39    }
40
41    pub async fn get_previous_cost(
42        &self,
43        start: DateTime<Utc>,
44        end: DateTime<Utc>,
45    ) -> Result<PreviousCostRow> {
46        sqlx::query_as!(
47            PreviousCostRow,
48            r#"
49            SELECT SUM(cost_microdollars)::bigint as "cost"
50            FROM ai_requests
51            WHERE created_at >= $1 AND created_at < $2
52              AND NOT synthetic
53            "#,
54            start,
55            end
56        )
57        .fetch_one(&*self.pool)
58        .await
59        .map_err(Into::into)
60    }
61
62    pub async fn get_breakdown_by_model(
63        &self,
64        start: DateTime<Utc>,
65        end: DateTime<Utc>,
66        limit: i64,
67    ) -> Result<Vec<CostBreakdownRow>> {
68        sqlx::query_as!(
69            CostBreakdownRow,
70            r#"
71            SELECT
72                model as "name!",
73                COALESCE(SUM(cost_microdollars), 0)::bigint as "cost!",
74                COUNT(*)::bigint as "requests!",
75                COALESCE(SUM(tokens_used), 0)::bigint as "tokens!"
76            FROM ai_requests
77            WHERE created_at >= $1 AND created_at < $2
78              AND NOT synthetic
79              AND model IS NOT NULL
80            GROUP BY model
81            ORDER BY SUM(cost_microdollars) DESC NULLS LAST
82            LIMIT $3
83            "#,
84            start,
85            end,
86            limit
87        )
88        .fetch_all(&*self.pool)
89        .await
90        .map_err(Into::into)
91    }
92
93    pub async fn get_breakdown_by_provider(
94        &self,
95        start: DateTime<Utc>,
96        end: DateTime<Utc>,
97        limit: i64,
98    ) -> Result<Vec<CostBreakdownRow>> {
99        sqlx::query_as!(
100            CostBreakdownRow,
101            r#"
102            SELECT
103                provider as "name!",
104                COALESCE(SUM(cost_microdollars), 0)::bigint as "cost!",
105                COUNT(*)::bigint as "requests!",
106                COALESCE(SUM(tokens_used), 0)::bigint as "tokens!"
107            FROM ai_requests
108            WHERE created_at >= $1 AND created_at < $2
109              AND NOT synthetic
110              AND provider IS NOT NULL
111            GROUP BY provider
112            ORDER BY SUM(cost_microdollars) DESC NULLS LAST
113            LIMIT $3
114            "#,
115            start,
116            end,
117            limit
118        )
119        .fetch_all(&*self.pool)
120        .await
121        .map_err(Into::into)
122    }
123
124    pub async fn get_breakdown_by_agent(
125        &self,
126        start: DateTime<Utc>,
127        end: DateTime<Utc>,
128        limit: i64,
129    ) -> Result<Vec<CostBreakdownRow>> {
130        sqlx::query_as!(
131            CostBreakdownRow,
132            r#"
133            (
134                SELECT
135                    at.agent_name as "name!",
136                    COALESCE(SUM(r.cost_microdollars), 0)::bigint as "cost!",
137                    COUNT(*)::bigint as "requests!",
138                    COALESCE(SUM(r.tokens_used), 0)::bigint as "tokens!"
139                FROM ai_requests r
140                INNER JOIN agent_tasks at ON at.task_id = r.task_id
141                WHERE r.created_at >= $1 AND r.created_at < $2
142                  AND NOT r.synthetic
143                  AND at.agent_name IS NOT NULL
144                GROUP BY at.agent_name
145                ORDER BY SUM(r.cost_microdollars) DESC NULLS LAST
146                LIMIT $3
147            )
148            UNION ALL
149            (
150                SELECT
151                    'unattributed' as "name!",
152                    COALESCE(SUM(r.cost_microdollars), 0)::bigint as "cost!",
153                    COUNT(*)::bigint as "requests!",
154                    COALESCE(SUM(r.tokens_used), 0)::bigint as "tokens!"
155                FROM ai_requests r
156                LEFT JOIN agent_tasks at ON at.task_id = r.task_id
157                WHERE r.created_at >= $1 AND r.created_at < $2
158                  AND NOT r.synthetic
159                  AND (r.task_id IS NULL OR at.agent_name IS NULL)
160                HAVING COUNT(*) > 0
161            )
162            "#,
163            start,
164            end,
165            limit
166        )
167        .fetch_all(&*self.pool)
168        .await
169        .map_err(Into::into)
170    }
171
172    pub async fn get_costs_for_trends(
173        &self,
174        start: DateTime<Utc>,
175        end: DateTime<Utc>,
176    ) -> Result<Vec<CostTrendRow>> {
177        sqlx::query_as!(
178            CostTrendRow,
179            r#"
180            SELECT
181                created_at as "created_at!",
182                cost_microdollars,
183                tokens_used
184            FROM ai_requests
185            WHERE created_at >= $1 AND created_at < $2
186              AND NOT synthetic
187            ORDER BY created_at
188            "#,
189            start,
190            end
191        )
192        .fetch_all(&*self.pool)
193        .await
194        .map_err(Into::into)
195    }
196}
systemprompt_analytics/repository/costs/platform.rs

systemprompt_analytics/repository/costs/
platform.rs
