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                SUM(reasoning_tokens)::bigint as "reasoning_tokens",
30                SUM(cache_read_tokens)::bigint as "cache_read_tokens",
31                SUM(cache_creation_tokens)::bigint as "cache_creation_tokens"
32            FROM ai_requests
33            WHERE created_at >= $1 AND created_at < $2
34              AND NOT synthetic
35            "#,
36            start,
37            end
38        )
39        .fetch_one(&*self.pool)
40        .await
41        .map_err(Into::into)
42    }
43
44    pub async fn get_previous_cost(
45        &self,
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
55              AND NOT synthetic
56            "#,
57            start,
58            end
59        )
60        .fetch_one(&*self.pool)
61        .await
62        .map_err(Into::into)
63    }
64
65    pub async fn get_breakdown_by_model(
66        &self,
67        start: DateTime<Utc>,
68        end: DateTime<Utc>,
69        limit: i64,
70    ) -> Result<Vec<CostBreakdownRow>> {
71        sqlx::query_as!(
72            CostBreakdownRow,
73            r#"
74            SELECT
75                model as "name!",
76                COALESCE(SUM(cost_microdollars), 0)::bigint as "cost!",
77                COUNT(*)::bigint as "requests!",
78                COALESCE(SUM(tokens_used), 0)::bigint as "tokens!"
79            FROM ai_requests
80            WHERE created_at >= $1 AND created_at < $2
81              AND NOT synthetic
82              AND model IS NOT NULL
83            GROUP BY model
84            ORDER BY SUM(cost_microdollars) DESC NULLS LAST
85            LIMIT $3
86            "#,
87            start,
88            end,
89            limit
90        )
91        .fetch_all(&*self.pool)
92        .await
93        .map_err(Into::into)
94    }
95
96    pub async fn get_breakdown_by_provider(
97        &self,
98        start: DateTime<Utc>,
99        end: DateTime<Utc>,
100        limit: i64,
101    ) -> Result<Vec<CostBreakdownRow>> {
102        sqlx::query_as!(
103            CostBreakdownRow,
104            r#"
105            SELECT
106                provider as "name!",
107                COALESCE(SUM(cost_microdollars), 0)::bigint as "cost!",
108                COUNT(*)::bigint as "requests!",
109                COALESCE(SUM(tokens_used), 0)::bigint as "tokens!"
110            FROM ai_requests
111            WHERE created_at >= $1 AND created_at < $2
112              AND NOT synthetic
113              AND provider IS NOT NULL
114            GROUP BY provider
115            ORDER BY SUM(cost_microdollars) DESC NULLS LAST
116            LIMIT $3
117            "#,
118            start,
119            end,
120            limit
121        )
122        .fetch_all(&*self.pool)
123        .await
124        .map_err(Into::into)
125    }
126
127    pub async fn get_breakdown_by_agent(
128        &self,
129        start: DateTime<Utc>,
130        end: DateTime<Utc>,
131        limit: i64,
132    ) -> Result<Vec<CostBreakdownRow>> {
133        sqlx::query_as!(
134            CostBreakdownRow,
135            r#"
136            (
137                SELECT
138                    at.agent_name as "name!",
139                    COALESCE(SUM(r.cost_microdollars), 0)::bigint as "cost!",
140                    COUNT(*)::bigint as "requests!",
141                    COALESCE(SUM(r.tokens_used), 0)::bigint as "tokens!"
142                FROM ai_requests r
143                INNER JOIN agent_tasks at ON at.task_id = r.task_id
144                WHERE r.created_at >= $1 AND r.created_at < $2
145                  AND NOT r.synthetic
146                  AND at.agent_name IS NOT NULL
147                GROUP BY at.agent_name
148                ORDER BY SUM(r.cost_microdollars) DESC NULLS LAST
149                LIMIT $3
150            )
151            UNION ALL
152            (
153                SELECT
154                    'unattributed' as "name!",
155                    COALESCE(SUM(r.cost_microdollars), 0)::bigint as "cost!",
156                    COUNT(*)::bigint as "requests!",
157                    COALESCE(SUM(r.tokens_used), 0)::bigint as "tokens!"
158                FROM ai_requests r
159                LEFT JOIN agent_tasks at ON at.task_id = r.task_id
160                WHERE r.created_at >= $1 AND r.created_at < $2
161                  AND NOT r.synthetic
162                  AND (r.task_id IS NULL OR at.agent_name IS NULL)
163                HAVING COUNT(*) > 0
164            )
165            "#,
166            start,
167            end,
168            limit
169        )
170        .fetch_all(&*self.pool)
171        .await
172        .map_err(Into::into)
173    }
174
175    pub async fn get_costs_for_trends(
176        &self,
177        start: DateTime<Utc>,
178        end: DateTime<Utc>,
179    ) -> Result<Vec<CostTrendRow>> {
180        sqlx::query_as!(
181            CostTrendRow,
182            r#"
183            SELECT
184                created_at as "created_at!",
185                cost_microdollars,
186                tokens_used
187            FROM ai_requests
188            WHERE created_at >= $1 AND created_at < $2
189              AND NOT synthetic
190            ORDER BY created_at
191            "#,
192            start,
193            end
194        )
195        .fetch_all(&*self.pool)
196        .await
197        .map_err(Into::into)
198    }
199}
systemprompt_analytics/repository/costs/platform.rs

systemprompt_analytics/repository/costs/
platform.rs
