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

systemprompt_analytics/repository/costs/
platform.rs
