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

systemprompt_analytics/repository/costs/
platform.rs
