tuitbot_core/storage/
x_api_usage.rs

1//! X API usage tracking — stores per-call endpoint, method, status, and estimated cost.
2
3use crate::error::StorageError;
4
5use super::accounts::DEFAULT_ACCOUNT_ID;
6use super::DbPool;
7
8/// Known per-call costs for X API endpoints (pay-per-use pricing, Feb 2026).
9///
10/// Source: <https://developer.x.com/#pricing>
11/// - Reading a post: $0.005
12/// - User profile lookup: $0.010
13/// - Creating a post: $0.010
14///
15/// Endpoints not yet priced default to $0.0 — update as X publishes more rates.
16pub fn estimate_cost(endpoint: &str, method: &str) -> f64 {
17    match (method, endpoint) {
18        // Post reads
19        ("GET", e) if e.starts_with("/tweets/search") => 0.005,
20        ("GET", e) if e.starts_with("/tweets/") || e == "/tweets" => 0.005,
21        ("GET", e) if e.contains("/mentions") => 0.005,
22        ("GET", e) if e.contains("/tweets") && e.starts_with("/users/") => 0.005,
23
24        // Bookmarks reads
25        ("GET", e) if e.contains("/bookmarks") => 0.005,
26        // Liked tweets reads
27        ("GET", e) if e.contains("/liked_tweets") => 0.005,
28        // Liking users reads
29        ("GET", e) if e.contains("/liking_users") => 0.005,
30
31        // User profile lookups
32        ("GET", "/users/me") => 0.010,
33        ("GET", "/users") => 0.010, // batch user lookup
34        ("GET", e) if e.starts_with("/users/by/username/") => 0.010,
35        ("GET", e)
36            if e.starts_with("/users/") && !e.contains("/tweets") && !e.contains("/mentions") =>
37        {
38            0.010
39        }
40
41        // Post creation (tweet or reply)
42        ("POST", "/tweets") => 0.010,
43
44        // Like/unlike a tweet
45        ("POST", e) if e.contains("/likes") => 0.010,
46        ("DELETE", e) if e.contains("/likes") => 0.010,
47
48        // Follow/unfollow a user
49        ("POST", e) if e.contains("/following") => 0.010,
50        ("DELETE", e) if e.contains("/following") => 0.010,
51
52        // Bookmark/unbookmark a tweet
53        ("POST", e) if e.contains("/bookmarks") => 0.010,
54        ("DELETE", e) if e.contains("/bookmarks") => 0.010,
55
56        // Unknown — default to zero until pricing is published
57        _ => 0.0,
58    }
59}
60
61/// Summary of X API usage across multiple time windows.
62#[derive(Debug, serde::Serialize)]
63pub struct XApiUsageSummary {
64    pub cost_today: f64,
65    pub cost_7d: f64,
66    pub cost_30d: f64,
67    pub cost_all_time: f64,
68    pub calls_today: i64,
69    pub calls_7d: i64,
70    pub calls_30d: i64,
71    pub calls_all_time: i64,
72}
73
74/// Daily X API usage aggregation for chart data.
75#[derive(Debug, serde::Serialize)]
76pub struct DailyXApiUsage {
77    pub date: String,
78    pub calls: i64,
79    pub cost: f64,
80}
81
82/// Breakdown of X API usage by endpoint + method.
83#[derive(Debug, serde::Serialize)]
84pub struct EndpointBreakdown {
85    pub endpoint: String,
86    pub method: String,
87    pub calls: i64,
88    pub cost: f64,
89    pub error_count: i64,
90}
91
92/// Insert a new X API usage record for a specific account.
93pub async fn insert_x_api_usage_for(
94    pool: &DbPool,
95    account_id: &str,
96    endpoint: &str,
97    method: &str,
98    status_code: i32,
99    cost_usd: f64,
100) -> Result<(), StorageError> {
101    sqlx::query(
102        "INSERT INTO x_api_usage (account_id, endpoint, method, status_code, cost_usd)
103         VALUES (?1, ?2, ?3, ?4, ?5)",
104    )
105    .bind(account_id)
106    .bind(endpoint)
107    .bind(method)
108    .bind(status_code)
109    .bind(cost_usd)
110    .execute(pool)
111    .await
112    .map_err(|e| StorageError::Query { source: e })?;
113    Ok(())
114}
115
116/// Insert a new X API usage record.
117pub async fn insert_x_api_usage(
118    pool: &DbPool,
119    endpoint: &str,
120    method: &str,
121    status_code: i32,
122    cost_usd: f64,
123) -> Result<(), StorageError> {
124    insert_x_api_usage_for(
125        pool,
126        DEFAULT_ACCOUNT_ID,
127        endpoint,
128        method,
129        status_code,
130        cost_usd,
131    )
132    .await
133}
134
135/// Get usage summary across time windows for a specific account.
136pub async fn get_usage_summary_for(
137    pool: &DbPool,
138    account_id: &str,
139) -> Result<XApiUsageSummary, StorageError> {
140    let row: (f64, i64, f64, i64, f64, i64, f64, i64) = sqlx::query_as(
141        "SELECT
142            COALESCE(SUM(CASE WHEN created_at >= date('now') THEN cost_usd ELSE 0.0 END), 0.0),
143            COALESCE(SUM(CASE WHEN created_at >= date('now') THEN 1 ELSE 0 END), 0),
144            COALESCE(SUM(CASE WHEN created_at >= date('now', '-7 days') THEN cost_usd ELSE 0.0 END), 0.0),
145            COALESCE(SUM(CASE WHEN created_at >= date('now', '-7 days') THEN 1 ELSE 0 END), 0),
146            COALESCE(SUM(CASE WHEN created_at >= date('now', '-30 days') THEN cost_usd ELSE 0.0 END), 0.0),
147            COALESCE(SUM(CASE WHEN created_at >= date('now', '-30 days') THEN 1 ELSE 0 END), 0),
148            COALESCE(SUM(cost_usd), 0.0),
149            COUNT(*)
150        FROM x_api_usage
151        WHERE account_id = ?",
152    )
153    .bind(account_id)
154    .fetch_one(pool)
155    .await
156    .map_err(|e| StorageError::Query { source: e })?;
157
158    Ok(XApiUsageSummary {
159        cost_today: row.0,
160        calls_today: row.1,
161        cost_7d: row.2,
162        calls_7d: row.3,
163        cost_30d: row.4,
164        calls_30d: row.5,
165        cost_all_time: row.6,
166        calls_all_time: row.7,
167    })
168}
169
170/// Get usage summary across time windows.
171pub async fn get_usage_summary(pool: &DbPool) -> Result<XApiUsageSummary, StorageError> {
172    get_usage_summary_for(pool, DEFAULT_ACCOUNT_ID).await
173}
174
175/// Get daily usage aggregation for chart data for a specific account.
176pub async fn get_daily_usage_for(
177    pool: &DbPool,
178    account_id: &str,
179    days: u32,
180) -> Result<Vec<DailyXApiUsage>, StorageError> {
181    let rows: Vec<(String, i64, f64)> = sqlx::query_as(
182        "SELECT
183            date(created_at) as day,
184            COUNT(*),
185            COALESCE(SUM(cost_usd), 0.0)
186        FROM x_api_usage
187        WHERE account_id = ? AND created_at >= date('now', '-' || ? || ' days')
188        GROUP BY day
189        ORDER BY day",
190    )
191    .bind(account_id)
192    .bind(days)
193    .fetch_all(pool)
194    .await
195    .map_err(|e| StorageError::Query { source: e })?;
196
197    Ok(rows
198        .into_iter()
199        .map(|(date, calls, cost)| DailyXApiUsage { date, calls, cost })
200        .collect())
201}
202
203/// Get daily usage aggregation for chart data.
204pub async fn get_daily_usage(
205    pool: &DbPool,
206    days: u32,
207) -> Result<Vec<DailyXApiUsage>, StorageError> {
208    get_daily_usage_for(pool, DEFAULT_ACCOUNT_ID, days).await
209}
210
211/// Get usage breakdown by endpoint + method for a specific account.
212pub async fn get_endpoint_breakdown_for(
213    pool: &DbPool,
214    account_id: &str,
215    days: u32,
216) -> Result<Vec<EndpointBreakdown>, StorageError> {
217    let rows: Vec<(String, String, i64, f64, i64)> = sqlx::query_as(
218        "SELECT
219            endpoint,
220            method,
221            COUNT(*),
222            COALESCE(SUM(cost_usd), 0.0),
223            COALESCE(SUM(CASE WHEN status_code >= 400 THEN 1 ELSE 0 END), 0)
224        FROM x_api_usage
225        WHERE account_id = ? AND created_at >= date('now', '-' || ? || ' days')
226        GROUP BY endpoint, method
227        ORDER BY COUNT(*) DESC",
228    )
229    .bind(account_id)
230    .bind(days)
231    .fetch_all(pool)
232    .await
233    .map_err(|e| StorageError::Query { source: e })?;
234
235    Ok(rows
236        .into_iter()
237        .map(
238            |(endpoint, method, calls, cost, error_count)| EndpointBreakdown {
239                endpoint,
240                method,
241                calls,
242                cost,
243                error_count,
244            },
245        )
246        .collect())
247}
248
249/// Get usage breakdown by endpoint + method.
250pub async fn get_endpoint_breakdown(
251    pool: &DbPool,
252    days: u32,
253) -> Result<Vec<EndpointBreakdown>, StorageError> {
254    get_endpoint_breakdown_for(pool, DEFAULT_ACCOUNT_ID, days).await
255}
256
257#[cfg(test)]
258mod tests {
259    use super::*;
260    use crate::storage::init_test_db;
261
262    #[tokio::test]
263    async fn insert_and_query_summary() {
264        let pool = init_test_db().await.expect("init db");
265
266        insert_x_api_usage(&pool, "/tweets/search/recent", "GET", 200, 0.005)
267            .await
268            .expect("insert");
269
270        insert_x_api_usage(&pool, "/tweets", "POST", 201, 0.010)
271            .await
272            .expect("insert");
273
274        let summary = get_usage_summary(&pool).await.expect("summary");
275        assert_eq!(summary.calls_all_time, 2);
276        assert!((summary.cost_all_time - 0.015).abs() < f64::EPSILON);
277    }
278
279    #[tokio::test]
280    async fn endpoint_breakdown_groups_correctly() {
281        let pool = init_test_db().await.expect("init db");
282
283        insert_x_api_usage(&pool, "/tweets/search/recent", "GET", 200, 0.005)
284            .await
285            .expect("insert");
286        insert_x_api_usage(&pool, "/tweets/search/recent", "GET", 200, 0.005)
287            .await
288            .expect("insert");
289        insert_x_api_usage(&pool, "/tweets", "POST", 201, 0.010)
290            .await
291            .expect("insert");
292        insert_x_api_usage(&pool, "/tweets/search/recent", "GET", 429, 0.0)
293            .await
294            .expect("insert error");
295
296        let breakdown = get_endpoint_breakdown(&pool, 30).await.expect("breakdown");
297        assert_eq!(breakdown.len(), 2);
298
299        let search = breakdown
300            .iter()
301            .find(|b| b.endpoint == "/tweets/search/recent")
302            .unwrap();
303        assert_eq!(search.calls, 3);
304        assert_eq!(search.error_count, 1);
305
306        let post = breakdown.iter().find(|b| b.method == "POST").unwrap();
307        assert_eq!(post.calls, 1);
308        assert_eq!(post.error_count, 0);
309    }
310
311    #[tokio::test]
312    async fn empty_table_returns_zero_summary() {
313        let pool = init_test_db().await.expect("init db");
314
315        let summary = get_usage_summary(&pool).await.expect("summary");
316        assert_eq!(summary.calls_all_time, 0);
317        assert!(summary.cost_all_time.abs() < f64::EPSILON);
318    }
319
320    #[tokio::test]
321    async fn estimate_cost_known_endpoints() {
322        assert!((estimate_cost("/tweets/search/recent", "GET") - 0.005).abs() < f64::EPSILON);
323        assert!((estimate_cost("/tweets/12345", "GET") - 0.005).abs() < f64::EPSILON);
324        assert!((estimate_cost("/users/me", "GET") - 0.010).abs() < f64::EPSILON);
325        assert!((estimate_cost("/users/by/username/jack", "GET") - 0.010).abs() < f64::EPSILON);
326        assert!((estimate_cost("/tweets", "POST") - 0.010).abs() < f64::EPSILON);
327        assert!((estimate_cost("/unknown", "DELETE") - 0.0).abs() < f64::EPSILON);
328    }
329
330    #[tokio::test]
331    async fn daily_usage_returns_data() {
332        let pool = init_test_db().await.expect("init db");
333
334        insert_x_api_usage(&pool, "/tweets/search/recent", "GET", 200, 0.005)
335            .await
336            .expect("insert");
337
338        let daily = get_daily_usage(&pool, 30).await.expect("daily");
339        assert_eq!(daily.len(), 1);
340        assert_eq!(daily[0].calls, 1);
341    }
342}
tuitbot_core/storage/x_api_usage.rs

tuitbot_core/storage/
x_api_usage.rs
