tuitbot_core/storage/
action_log.rs

1//! Append-only action log for auditing and status reporting.
2//!
3//! Records every action taken by the agent with timestamps,
4//! status, and optional metadata in JSON format.
5
6use super::accounts::DEFAULT_ACCOUNT_ID;
7use super::DbPool;
8use crate::error::StorageError;
9use std::collections::HashMap;
10
11/// An entry in the action audit log.
12#[derive(Debug, Clone, sqlx::FromRow, serde::Serialize)]
13pub struct ActionLogEntry {
14    /// Internal auto-generated ID.
15    pub id: i64,
16    /// Action type: search, reply, tweet, thread, mention_check, cleanup, auth_refresh.
17    pub action_type: String,
18    /// Status: success, failure, or skipped.
19    pub status: String,
20    /// Human-readable description.
21    pub message: Option<String>,
22    /// JSON blob for flexible extra data.
23    pub metadata: Option<String>,
24    /// ISO-8601 UTC timestamp.
25    pub created_at: String,
26}
27
28/// Insert a new action log entry for a specific account.
29///
30/// The `metadata` parameter is a pre-serialized JSON string; the caller
31/// is responsible for serialization. The `created_at` field uses the SQL default.
32pub async fn log_action_for(
33    pool: &DbPool,
34    account_id: &str,
35    action_type: &str,
36    status: &str,
37    message: Option<&str>,
38    metadata: Option<&str>,
39) -> Result<(), StorageError> {
40    sqlx::query(
41        "INSERT INTO action_log (account_id, action_type, status, message, metadata) \
42         VALUES (?, ?, ?, ?, ?)",
43    )
44    .bind(account_id)
45    .bind(action_type)
46    .bind(status)
47    .bind(message)
48    .bind(metadata)
49    .execute(pool)
50    .await
51    .map_err(|e| StorageError::Query { source: e })?;
52
53    Ok(())
54}
55
56/// Insert a new action log entry.
57///
58/// The `metadata` parameter is a pre-serialized JSON string; the caller
59/// is responsible for serialization. The `created_at` field uses the SQL default.
60pub async fn log_action(
61    pool: &DbPool,
62    action_type: &str,
63    status: &str,
64    message: Option<&str>,
65    metadata: Option<&str>,
66) -> Result<(), StorageError> {
67    log_action_for(
68        pool,
69        DEFAULT_ACCOUNT_ID,
70        action_type,
71        status,
72        message,
73        metadata,
74    )
75    .await
76}
77
78/// Fetch action log entries since a given timestamp for a specific account,
79/// optionally filtered by type.
80///
81/// Results are ordered by `created_at` ascending.
82pub async fn get_actions_since_for(
83    pool: &DbPool,
84    account_id: &str,
85    since: &str,
86    action_type: Option<&str>,
87) -> Result<Vec<ActionLogEntry>, StorageError> {
88    match action_type {
89        Some(at) => sqlx::query_as::<_, ActionLogEntry>(
90            "SELECT * FROM action_log WHERE created_at >= ? AND action_type = ? \
91                 AND account_id = ? ORDER BY created_at ASC",
92        )
93        .bind(since)
94        .bind(at)
95        .bind(account_id)
96        .fetch_all(pool)
97        .await
98        .map_err(|e| StorageError::Query { source: e }),
99        None => sqlx::query_as::<_, ActionLogEntry>(
100            "SELECT * FROM action_log WHERE created_at >= ? \
101                 AND account_id = ? ORDER BY created_at ASC",
102        )
103        .bind(since)
104        .bind(account_id)
105        .fetch_all(pool)
106        .await
107        .map_err(|e| StorageError::Query { source: e }),
108    }
109}
110
111/// Fetch action log entries since a given timestamp, optionally filtered by type.
112///
113/// Results are ordered by `created_at` ascending.
114pub async fn get_actions_since(
115    pool: &DbPool,
116    since: &str,
117    action_type: Option<&str>,
118) -> Result<Vec<ActionLogEntry>, StorageError> {
119    get_actions_since_for(pool, DEFAULT_ACCOUNT_ID, since, action_type).await
120}
121
122/// Get counts of each action type since a given timestamp for a specific account.
123///
124/// Returns a HashMap mapping action types to their counts.
125pub async fn get_action_counts_since_for(
126    pool: &DbPool,
127    account_id: &str,
128    since: &str,
129) -> Result<HashMap<String, i64>, StorageError> {
130    let rows: Vec<(String, i64)> = sqlx::query_as(
131        "SELECT action_type, COUNT(*) as count FROM action_log \
132         WHERE created_at >= ? AND account_id = ? GROUP BY action_type",
133    )
134    .bind(since)
135    .bind(account_id)
136    .fetch_all(pool)
137    .await
138    .map_err(|e| StorageError::Query { source: e })?;
139
140    Ok(rows.into_iter().collect())
141}
142
143/// Get counts of each action type since a given timestamp.
144///
145/// Returns a HashMap mapping action types to their counts.
146pub async fn get_action_counts_since(
147    pool: &DbPool,
148    since: &str,
149) -> Result<HashMap<String, i64>, StorageError> {
150    get_action_counts_since_for(pool, DEFAULT_ACCOUNT_ID, since).await
151}
152
153/// Get the most recent action log entries for a specific account, newest first.
154pub async fn get_recent_actions_for(
155    pool: &DbPool,
156    account_id: &str,
157    limit: u32,
158) -> Result<Vec<ActionLogEntry>, StorageError> {
159    sqlx::query_as::<_, ActionLogEntry>(
160        "SELECT * FROM action_log WHERE account_id = ? ORDER BY created_at DESC LIMIT ?",
161    )
162    .bind(account_id)
163    .bind(limit)
164    .fetch_all(pool)
165    .await
166    .map_err(|e| StorageError::Query { source: e })
167}
168
169/// Get the most recent action log entries, newest first.
170pub async fn get_recent_actions(
171    pool: &DbPool,
172    limit: u32,
173) -> Result<Vec<ActionLogEntry>, StorageError> {
174    get_recent_actions_for(pool, DEFAULT_ACCOUNT_ID, limit).await
175}
176
177/// Fetch paginated action log entries for a specific account with optional
178/// type and status filters.
179///
180/// Results are ordered by `created_at` descending (newest first).
181pub async fn get_actions_paginated_for(
182    pool: &DbPool,
183    account_id: &str,
184    limit: u32,
185    offset: u32,
186    action_type: Option<&str>,
187    status: Option<&str>,
188) -> Result<Vec<ActionLogEntry>, StorageError> {
189    let mut sql = String::from("SELECT * FROM action_log WHERE 1=1 AND account_id = ?");
190    if action_type.is_some() {
191        sql.push_str(" AND action_type = ?");
192    }
193    if status.is_some() {
194        sql.push_str(" AND status = ?");
195    }
196    sql.push_str(" ORDER BY created_at DESC LIMIT ? OFFSET ?");
197
198    let mut query = sqlx::query_as::<_, ActionLogEntry>(&sql);
199    query = query.bind(account_id);
200    if let Some(at) = action_type {
201        query = query.bind(at);
202    }
203    if let Some(st) = status {
204        query = query.bind(st);
205    }
206    query = query.bind(limit).bind(offset);
207
208    query
209        .fetch_all(pool)
210        .await
211        .map_err(|e| StorageError::Query { source: e })
212}
213
214/// Fetch paginated action log entries with optional type and status filters.
215///
216/// Results are ordered by `created_at` descending (newest first).
217pub async fn get_actions_paginated(
218    pool: &DbPool,
219    limit: u32,
220    offset: u32,
221    action_type: Option<&str>,
222    status: Option<&str>,
223) -> Result<Vec<ActionLogEntry>, StorageError> {
224    get_actions_paginated_for(pool, DEFAULT_ACCOUNT_ID, limit, offset, action_type, status).await
225}
226
227/// Get total count of action log entries for a specific account with optional
228/// type and status filters.
229pub async fn get_actions_count_for(
230    pool: &DbPool,
231    account_id: &str,
232    action_type: Option<&str>,
233    status: Option<&str>,
234) -> Result<i64, StorageError> {
235    let mut sql = String::from("SELECT COUNT(*) FROM action_log WHERE 1=1 AND account_id = ?");
236    if action_type.is_some() {
237        sql.push_str(" AND action_type = ?");
238    }
239    if status.is_some() {
240        sql.push_str(" AND status = ?");
241    }
242
243    let mut query = sqlx::query_as::<_, (i64,)>(&sql);
244    query = query.bind(account_id);
245    if let Some(at) = action_type {
246        query = query.bind(at);
247    }
248    if let Some(st) = status {
249        query = query.bind(st);
250    }
251
252    let (count,) = query
253        .fetch_one(pool)
254        .await
255        .map_err(|e| StorageError::Query { source: e })?;
256    Ok(count)
257}
258
259/// Get total count of action log entries with optional type and status filters.
260pub async fn get_actions_count(
261    pool: &DbPool,
262    action_type: Option<&str>,
263    status: Option<&str>,
264) -> Result<i64, StorageError> {
265    get_actions_count_for(pool, DEFAULT_ACCOUNT_ID, action_type, status).await
266}
267
268#[cfg(test)]
269mod tests {
270    use super::*;
271    use crate::storage::init_test_db;
272
273    #[tokio::test]
274    async fn log_and_retrieve_action() {
275        let pool = init_test_db().await.expect("init db");
276
277        log_action(&pool, "search", "success", Some("Found 10 tweets"), None)
278            .await
279            .expect("log");
280
281        let actions = get_actions_since(&pool, "2000-01-01T00:00:00Z", None)
282            .await
283            .expect("get");
284
285        assert_eq!(actions.len(), 1);
286        assert_eq!(actions[0].action_type, "search");
287        assert_eq!(actions[0].status, "success");
288        assert_eq!(actions[0].message.as_deref(), Some("Found 10 tweets"));
289    }
290
291    #[tokio::test]
292    async fn filter_by_action_type() {
293        let pool = init_test_db().await.expect("init db");
294
295        log_action(&pool, "search", "success", None, None)
296            .await
297            .expect("log");
298        log_action(&pool, "reply", "success", None, None)
299            .await
300            .expect("log");
301        log_action(&pool, "search", "failure", None, None)
302            .await
303            .expect("log");
304
305        let searches = get_actions_since(&pool, "2000-01-01T00:00:00Z", Some("search"))
306            .await
307            .expect("get");
308        assert_eq!(searches.len(), 2);
309
310        let replies = get_actions_since(&pool, "2000-01-01T00:00:00Z", Some("reply"))
311            .await
312            .expect("get");
313        assert_eq!(replies.len(), 1);
314    }
315
316    #[tokio::test]
317    async fn action_counts_aggregation() {
318        let pool = init_test_db().await.expect("init db");
319
320        log_action(&pool, "search", "success", None, None)
321            .await
322            .expect("log");
323        log_action(&pool, "search", "success", None, None)
324            .await
325            .expect("log");
326        log_action(&pool, "reply", "success", None, None)
327            .await
328            .expect("log");
329        log_action(&pool, "tweet", "failure", None, None)
330            .await
331            .expect("log");
332
333        let counts = get_action_counts_since(&pool, "2000-01-01T00:00:00Z")
334            .await
335            .expect("get counts");
336
337        assert_eq!(counts.get("search"), Some(&2));
338        assert_eq!(counts.get("reply"), Some(&1));
339        assert_eq!(counts.get("tweet"), Some(&1));
340    }
341
342    #[tokio::test]
343    async fn log_with_metadata() {
344        let pool = init_test_db().await.expect("init db");
345
346        let metadata = r#"{"tweet_id": "123", "score": 85}"#;
347        log_action(
348            &pool,
349            "reply",
350            "success",
351            Some("Replied to tweet"),
352            Some(metadata),
353        )
354        .await
355        .expect("log");
356
357        let actions = get_actions_since(&pool, "2000-01-01T00:00:00Z", Some("reply"))
358            .await
359            .expect("get");
360
361        assert_eq!(actions[0].metadata.as_deref(), Some(metadata));
362    }
363
364    #[tokio::test]
365    async fn empty_counts_returns_empty_map() {
366        let pool = init_test_db().await.expect("init db");
367
368        let counts = get_action_counts_since(&pool, "2000-01-01T00:00:00Z")
369            .await
370            .expect("get counts");
371
372        assert!(counts.is_empty());
373    }
374
375    #[tokio::test]
376    async fn paginated_actions_with_offset() {
377        let pool = init_test_db().await.expect("init db");
378
379        for i in 0..10 {
380            log_action(
381                &pool,
382                "search",
383                "success",
384                Some(&format!("Action {i}")),
385                None,
386            )
387            .await
388            .expect("log");
389        }
390
391        let page1 = get_actions_paginated(&pool, 3, 0, None, None)
392            .await
393            .expect("page 1");
394        assert_eq!(page1.len(), 3);
395
396        let page2 = get_actions_paginated(&pool, 3, 3, None, None)
397            .await
398            .expect("page 2");
399        assert_eq!(page2.len(), 3);
400
401        // Pages should not overlap
402        let ids1: Vec<i64> = page1.iter().map(|a| a.id).collect();
403        let ids2: Vec<i64> = page2.iter().map(|a| a.id).collect();
404        assert!(ids1.iter().all(|id| !ids2.contains(id)));
405    }
406
407    #[tokio::test]
408    async fn paginated_actions_with_type_filter() {
409        let pool = init_test_db().await.expect("init db");
410
411        log_action(&pool, "search", "success", None, None)
412            .await
413            .expect("log");
414        log_action(&pool, "reply", "success", None, None)
415            .await
416            .expect("log");
417        log_action(&pool, "search", "success", None, None)
418            .await
419            .expect("log");
420
421        let searches = get_actions_paginated(&pool, 10, 0, Some("search"), None)
422            .await
423            .expect("get");
424        assert_eq!(searches.len(), 2);
425
426        let count = get_actions_count(&pool, Some("search"), None)
427            .await
428            .expect("count");
429        assert_eq!(count, 2);
430    }
431
432    #[tokio::test]
433    async fn paginated_actions_with_status_filter() {
434        let pool = init_test_db().await.expect("init db");
435
436        log_action(&pool, "search", "success", None, None)
437            .await
438            .expect("log");
439        log_action(&pool, "reply", "failure", Some("Rate limited"), None)
440            .await
441            .expect("log");
442        log_action(&pool, "tweet", "failure", Some("API error"), None)
443            .await
444            .expect("log");
445
446        let failures = get_actions_paginated(&pool, 10, 0, None, Some("failure"))
447            .await
448            .expect("get");
449        assert_eq!(failures.len(), 2);
450
451        let count = get_actions_count(&pool, None, Some("failure"))
452            .await
453            .expect("count");
454        assert_eq!(count, 2);
455    }
456
457    #[tokio::test]
458    async fn paginated_actions_combined_filters() {
459        let pool = init_test_db().await.expect("init db");
460
461        log_action(&pool, "reply", "success", None, None)
462            .await
463            .expect("log");
464        log_action(&pool, "reply", "failure", None, None)
465            .await
466            .expect("log");
467        log_action(&pool, "tweet", "failure", None, None)
468            .await
469            .expect("log");
470
471        let reply_failures = get_actions_paginated(&pool, 10, 0, Some("reply"), Some("failure"))
472            .await
473            .expect("get");
474        assert_eq!(reply_failures.len(), 1);
475
476        let count = get_actions_count(&pool, Some("reply"), Some("failure"))
477            .await
478            .expect("count");
479        assert_eq!(count, 1);
480    }
481
482    #[tokio::test]
483    async fn actions_count_no_filter() {
484        let pool = init_test_db().await.expect("init db");
485
486        log_action(&pool, "search", "success", None, None)
487            .await
488            .expect("log");
489        log_action(&pool, "reply", "success", None, None)
490            .await
491            .expect("log");
492
493        let count = get_actions_count(&pool, None, None).await.expect("count");
494        assert_eq!(count, 2);
495    }
496}
tuitbot_core/storage/action_log.rs

tuitbot_core/storage/
action_log.rs
