tuitbot_core/storage/
approval_queue.rs

1//! Storage operations for the approval queue.
2//!
3//! Provides CRUD operations for queuing posts for human review
4//! when `approval_mode` is enabled.
5
6use super::DbPool;
7use crate::error::StorageError;
8
9/// Row type for approval queue queries.
10type ApprovalRow = (
11    i64,
12    String,
13    String,
14    String,
15    String,
16    String,
17    String,
18    f64,
19    String,
20    String,
21);
22
23/// A pending item in the approval queue.
24#[derive(Debug, Clone, serde::Serialize)]
25pub struct ApprovalItem {
26    pub id: i64,
27    pub action_type: String,
28    pub target_tweet_id: String,
29    pub target_author: String,
30    pub generated_content: String,
31    pub topic: String,
32    pub archetype: String,
33    pub score: f64,
34    pub status: String,
35    pub created_at: String,
36}
37
38impl From<ApprovalRow> for ApprovalItem {
39    fn from(r: ApprovalRow) -> Self {
40        Self {
41            id: r.0,
42            action_type: r.1,
43            target_tweet_id: r.2,
44            target_author: r.3,
45            generated_content: r.4,
46            topic: r.5,
47            archetype: r.6,
48            score: r.7,
49            status: r.8,
50            created_at: r.9,
51        }
52    }
53}
54
55/// Insert a new item into the approval queue.
56#[allow(clippy::too_many_arguments)]
57pub async fn enqueue(
58    pool: &DbPool,
59    action_type: &str,
60    target_tweet_id: &str,
61    target_author: &str,
62    generated_content: &str,
63    topic: &str,
64    archetype: &str,
65    score: f64,
66) -> Result<i64, StorageError> {
67    let result = sqlx::query(
68        "INSERT INTO approval_queue (action_type, target_tweet_id, target_author, generated_content, topic, archetype, score)
69         VALUES (?, ?, ?, ?, ?, ?, ?)",
70    )
71    .bind(action_type)
72    .bind(target_tweet_id)
73    .bind(target_author)
74    .bind(generated_content)
75    .bind(topic)
76    .bind(archetype)
77    .bind(score)
78    .execute(pool)
79    .await
80    .map_err(|e| StorageError::Query { source: e })?;
81
82    Ok(result.last_insert_rowid())
83}
84
85/// Get all pending approval items, ordered by creation time (oldest first).
86pub async fn get_pending(pool: &DbPool) -> Result<Vec<ApprovalItem>, StorageError> {
87    let rows: Vec<ApprovalRow> = sqlx::query_as(
88        "SELECT id, action_type, target_tweet_id, target_author, generated_content, topic, archetype, score, status, created_at
89         FROM approval_queue
90         WHERE status = 'pending'
91         ORDER BY created_at ASC",
92    )
93    .fetch_all(pool)
94    .await
95    .map_err(|e| StorageError::Query { source: e })?;
96
97    Ok(rows.into_iter().map(ApprovalItem::from).collect())
98}
99
100/// Get the count of pending items.
101pub async fn pending_count(pool: &DbPool) -> Result<i64, StorageError> {
102    let row: (i64,) =
103        sqlx::query_as("SELECT COUNT(*) FROM approval_queue WHERE status = 'pending'")
104            .fetch_one(pool)
105            .await
106            .map_err(|e| StorageError::Query { source: e })?;
107
108    Ok(row.0)
109}
110
111/// Update the status of an approval item.
112pub async fn update_status(pool: &DbPool, id: i64, status: &str) -> Result<(), StorageError> {
113    sqlx::query(
114        "UPDATE approval_queue SET status = ?, reviewed_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id = ?",
115    )
116    .bind(status)
117    .bind(id)
118    .execute(pool)
119    .await
120    .map_err(|e| StorageError::Query { source: e })?;
121
122    Ok(())
123}
124
125/// Update the content and status of an approval item (for edit-then-approve).
126pub async fn update_content_and_approve(
127    pool: &DbPool,
128    id: i64,
129    new_content: &str,
130) -> Result<(), StorageError> {
131    sqlx::query(
132        "UPDATE approval_queue SET generated_content = ?, status = 'approved', reviewed_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id = ?",
133    )
134    .bind(new_content)
135    .bind(id)
136    .execute(pool)
137    .await
138    .map_err(|e| StorageError::Query { source: e })?;
139
140    Ok(())
141}
142
143/// Get a single approval item by ID.
144pub async fn get_by_id(pool: &DbPool, id: i64) -> Result<Option<ApprovalItem>, StorageError> {
145    let row: Option<ApprovalRow> = sqlx::query_as(
146        "SELECT id, action_type, target_tweet_id, target_author, generated_content, topic, archetype, score, status, created_at
147         FROM approval_queue
148         WHERE id = ?",
149    )
150    .bind(id)
151    .fetch_optional(pool)
152    .await
153    .map_err(|e| StorageError::Query { source: e })?;
154
155    Ok(row.map(ApprovalItem::from))
156}
157
158/// Expire old pending items (older than the specified hours).
159pub async fn expire_old_items(pool: &DbPool, hours: u32) -> Result<u64, StorageError> {
160    let result = sqlx::query(
161        "UPDATE approval_queue SET status = 'expired', reviewed_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
162         WHERE status = 'pending'
163         AND created_at < strftime('%Y-%m-%dT%H:%M:%SZ', 'now', ?)",
164    )
165    .bind(format!("-{hours} hours"))
166    .execute(pool)
167    .await
168    .map_err(|e| StorageError::Query { source: e })?;
169
170    Ok(result.rows_affected())
171}
172
173#[cfg(test)]
174mod tests {
175    use super::*;
176    use crate::storage::init_test_db;
177
178    #[tokio::test]
179    async fn enqueue_and_get_pending() {
180        let pool = init_test_db().await.expect("init db");
181
182        let id = enqueue(
183            &pool,
184            "reply",
185            "tweet123",
186            "@testuser",
187            "Great point about Rust!",
188            "Rust",
189            "AgreeAndExpand",
190            85.0,
191        )
192        .await
193        .expect("enqueue");
194
195        assert!(id > 0);
196
197        let pending = get_pending(&pool).await.expect("get pending");
198        assert_eq!(pending.len(), 1);
199        assert_eq!(pending[0].action_type, "reply");
200        assert_eq!(pending[0].target_tweet_id, "tweet123");
201        assert_eq!(pending[0].generated_content, "Great point about Rust!");
202    }
203
204    #[tokio::test]
205    async fn pending_count_works() {
206        let pool = init_test_db().await.expect("init db");
207
208        assert_eq!(pending_count(&pool).await.expect("count"), 0);
209
210        enqueue(&pool, "tweet", "", "", "Hello world", "General", "", 0.0)
211            .await
212            .expect("enqueue");
213        enqueue(&pool, "reply", "t1", "@u", "Nice!", "Rust", "", 50.0)
214            .await
215            .expect("enqueue");
216
217        assert_eq!(pending_count(&pool).await.expect("count"), 2);
218    }
219
220    #[tokio::test]
221    async fn update_status_marks_approved() {
222        let pool = init_test_db().await.expect("init db");
223
224        let id = enqueue(&pool, "tweet", "", "", "Hello", "General", "", 0.0)
225            .await
226            .expect("enqueue");
227
228        update_status(&pool, id, "approved").await.expect("update");
229
230        let pending = get_pending(&pool).await.expect("get pending");
231        assert!(pending.is_empty());
232
233        let item = get_by_id(&pool, id).await.expect("get").expect("found");
234        assert_eq!(item.status, "approved");
235    }
236
237    #[tokio::test]
238    async fn update_status_marks_rejected() {
239        let pool = init_test_db().await.expect("init db");
240
241        let id = enqueue(&pool, "tweet", "", "", "Hello", "General", "", 0.0)
242            .await
243            .expect("enqueue");
244
245        update_status(&pool, id, "rejected").await.expect("update");
246
247        let item = get_by_id(&pool, id).await.expect("get").expect("found");
248        assert_eq!(item.status, "rejected");
249    }
250
251    #[tokio::test]
252    async fn update_content_and_approve_works() {
253        let pool = init_test_db().await.expect("init db");
254
255        let id = enqueue(&pool, "tweet", "", "", "Draft", "General", "", 0.0)
256            .await
257            .expect("enqueue");
258
259        update_content_and_approve(&pool, id, "Final version")
260            .await
261            .expect("update");
262
263        let item = get_by_id(&pool, id).await.expect("get").expect("found");
264        assert_eq!(item.status, "approved");
265        assert_eq!(item.generated_content, "Final version");
266    }
267
268    #[tokio::test]
269    async fn get_by_id_not_found() {
270        let pool = init_test_db().await.expect("init db");
271        let item = get_by_id(&pool, 99999).await.expect("get");
272        assert!(item.is_none());
273    }
274
275    #[tokio::test]
276    async fn pending_ordered_by_creation_time() {
277        let pool = init_test_db().await.expect("init db");
278
279        enqueue(&pool, "tweet", "", "", "First", "A", "", 0.0)
280            .await
281            .expect("enqueue");
282        enqueue(&pool, "tweet", "", "", "Second", "B", "", 0.0)
283            .await
284            .expect("enqueue");
285        enqueue(&pool, "tweet", "", "", "Third", "C", "", 0.0)
286            .await
287            .expect("enqueue");
288
289        let pending = get_pending(&pool).await.expect("get pending");
290        assert_eq!(pending.len(), 3);
291        assert_eq!(pending[0].generated_content, "First");
292        assert_eq!(pending[1].generated_content, "Second");
293        assert_eq!(pending[2].generated_content, "Third");
294    }
295}
tuitbot_core/storage/approval_queue.rs

tuitbot_core/storage/
approval_queue.rs
