systemprompt_database/repository/
cleanup.rs

1//! Periodic cleanup queries for orphaned rows and expired tokens.
2
3use sqlx::PgPool;
4
5use crate::error::DatabaseResult;
6
7#[derive(Debug)]
8pub struct CleanupRepository {
9    write_pool: PgPool,
10}
11
12impl CleanupRepository {
13    pub const fn new(pool: PgPool) -> Self {
14        Self { write_pool: pool }
15    }
16
17    pub const fn new_with_write_pool(write_pool: PgPool) -> Self {
18        Self { write_pool }
19    }
20
21    pub async fn delete_orphaned_logs(&self) -> DatabaseResult<u64> {
22        let result = sqlx::query!(
23            r#"
24            DELETE FROM logs
25            WHERE user_id IS NOT NULL
26              AND user_id NOT IN (SELECT id FROM users)
27            "#
28        )
29        .execute(&self.write_pool)
30        .await?;
31        Ok(result.rows_affected())
32    }
33
34    pub async fn delete_orphaned_mcp_executions(&self) -> DatabaseResult<u64> {
35        let result = sqlx::query!(
36            r#"
37            DELETE FROM mcp_tool_executions
38            WHERE context_id IS NOT NULL
39              AND context_id NOT IN (SELECT context_id FROM user_contexts)
40            "#
41        )
42        .execute(&self.write_pool)
43        .await?;
44        Ok(result.rows_affected())
45    }
46
47    pub async fn delete_old_logs(&self, days: i32) -> DatabaseResult<u64> {
48        let cutoff = chrono::Utc::now() - chrono::Duration::days(i64::from(days));
49        let result = sqlx::query!("DELETE FROM logs WHERE timestamp < $1", cutoff)
50            .execute(&self.write_pool)
51            .await?;
52        Ok(result.rows_affected())
53    }
54
55    pub async fn count_old_logs(&self, days: i32) -> DatabaseResult<i64> {
56        let cutoff = chrono::Utc::now() - chrono::Duration::days(i64::from(days));
57        let count = sqlx::query_scalar!(
58            r#"SELECT COUNT(*) as "count!" FROM logs WHERE timestamp < $1"#,
59            cutoff
60        )
61        .fetch_one(&self.write_pool)
62        .await?;
63        Ok(count)
64    }
65
66    pub async fn delete_expired_oauth_tokens(&self) -> DatabaseResult<u64> {
67        let result = sqlx::query!("DELETE FROM oauth_refresh_tokens WHERE expires_at < NOW()")
68            .execute(&self.write_pool)
69            .await?;
70        Ok(result.rows_affected())
71    }
72
73    pub async fn delete_expired_oauth_codes(&self) -> DatabaseResult<u64> {
74        let result = sqlx::query!(
75            "DELETE FROM oauth_auth_codes WHERE expires_at < NOW() OR used_at IS NOT NULL"
76        )
77        .execute(&self.write_pool)
78        .await?;
79        Ok(result.rows_affected())
80    }
81}
systemprompt_database/repository/cleanup.rs

systemprompt_database/repository/
cleanup.rs
