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

systemprompt_analytics/repository/costs/
platform.rs
