Skip to main content

auth_framework/storage/
postgres.rs

1/// PostgreSQL storage implementation for auth-framework.
2/// This module provides a production-ready PostgreSQL backend for storing
3/// authentication tokens, sessions, and audit logs.
4use crate::errors::{AuthError, Result};
5use crate::storage::{AuthStorage, SessionData};
6use crate::tokens::AuthToken;
7use async_trait::async_trait;
8use sqlx::PgPool;
9use sqlx::Row;
10// use std::time::Duration;
11
12/// PostgreSQL storage backend
13pub struct PostgresStorage {
14    pool: PgPool,
15}
16
17/// Advisory-lock key serialising `PostgresStorage::migrate` across connections ("authfmwk" as ASCII).
18const MIGRATION_LOCK_KEY: i64 = 0x6175_7468_666d_776b;
19
20impl PostgresStorage {
21    /// Create a new PostgreSQL storage instance.
22    ///
23    /// # Example
24    /// ```rust,ignore
25    /// let pool = PgPool::connect("postgres://user:pass@localhost/db").await?;
26    /// let storage = PostgresStorage::new(pool);
27    /// ```
28    pub fn new(pool: PgPool) -> Self {
29        Self { pool }
30    }
31
32    /// The token's profile as a JSONB value; `None` is stored as SQL NULL.
33    fn profile_json(token: &AuthToken) -> Option<serde_json::Value> {
34        token
35            .user_profile
36            .as_ref()
37            .and_then(|profile| serde_json::to_value(profile).ok())
38    }
39
40    /// Initialize database tables.
41    ///
42    /// Creates the `auth_tokens`, `sessions`, and `kv_store` tables together with
43    /// their secondary indexes if they do not already exist.  Safe to call on every
44    /// application startup (`IF NOT EXISTS` / `IF NOT EXISTS` guards are idempotent).
45    ///
46    /// # Errors
47    /// Returns an error if any DDL statement fails (e.g. insufficient privileges).
48    pub async fn migrate(&self) -> Result<()> {
49        // `CREATE TABLE IF NOT EXISTS` is not safe under concurrency: two connections can both pass
50        // the existence check and then collide on Postgres's own `pg_type` catalog (every table
51        // creates a composite row type there). Run the whole migration in one transaction behind a
52        // transaction-scoped advisory lock, so concurrent callers (several instances starting at
53        // once) take turns and the later ones see the finished schema. The lock is released by the
54        // commit or rollback; there is no unlock to forget.
55        let mut tx = self.pool.begin().await.map_err(|e| {
56            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
57                "Migration failed (begin): {e}"
58            )))
59        })?;
60        sqlx::query("SELECT pg_advisory_xact_lock($1)")
61            .bind(MIGRATION_LOCK_KEY)
62            .execute(&mut *tx)
63            .await
64            .map_err(|e| {
65                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
66                    "Migration failed (lock): {e}"
67                )))
68            })?;
69
70        // Each statement is executed separately because sqlx::query() accepts
71        // exactly one SQL statement per call.
72
73        // --- auth_tokens table ---
74        sqlx::query(
75            r#"
76            CREATE TABLE IF NOT EXISTS auth_tokens (
77                token_id    VARCHAR(255) PRIMARY KEY,
78                user_id     VARCHAR(255) NOT NULL,
79                access_token TEXT        NOT NULL UNIQUE,
80                refresh_token TEXT,
81                token_type  VARCHAR(50),
82                expires_at  TIMESTAMPTZ  NOT NULL,
83                scopes      TEXT[],
84                issued_at   TIMESTAMPTZ  NOT NULL,
85                auth_method VARCHAR(100) NOT NULL,
86                subject     VARCHAR(255),
87                issuer      VARCHAR(255),
88                client_id   VARCHAR(255),
89                metadata    JSONB,
90                created_at  TIMESTAMPTZ  DEFAULT NOW()
91            )
92            "#,
93        )
94        .execute(&mut *tx)
95        .await
96        .map_err(|e| {
97            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
98                "Migration failed (auth_tokens): {e}"
99            )))
100        })?;
101
102        // Secondary indexes for auth_tokens (PostgreSQL requires separate CREATE INDEX statements)
103        for stmt in [
104            "CREATE INDEX IF NOT EXISTS idx_auth_tokens_user_id ON auth_tokens (user_id)",
105            "CREATE INDEX IF NOT EXISTS idx_auth_tokens_access_token ON auth_tokens (access_token)",
106            "CREATE INDEX IF NOT EXISTS idx_auth_tokens_expires_at ON auth_tokens (expires_at)",
107        ] {
108            sqlx::query(stmt).execute(&mut *tx).await.map_err(|e| {
109                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
110                    "Migration failed (index): {e}"
111                )))
112            })?;
113        }
114
115        // Columns added after the first release of this schema (#93): `AuthToken` derives `FromRow`, so
116        // a row must carry a column for every field. `ADD COLUMN IF NOT EXISTS` upgrades existing tables.
117        for stmt in [
118            "ALTER TABLE auth_tokens ADD COLUMN IF NOT EXISTS user_profile JSONB",
119            "ALTER TABLE auth_tokens ADD COLUMN IF NOT EXISTS permissions TEXT[] NOT NULL DEFAULT '{}'",
120            "ALTER TABLE auth_tokens ADD COLUMN IF NOT EXISTS roles TEXT[] NOT NULL DEFAULT '{}'",
121        ] {
122            sqlx::query(stmt).execute(&mut *tx).await.map_err(|e| {
123                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
124                    "Migration failed (auth_tokens column): {e}"
125                )))
126            })?;
127        }
128
129        // --- sessions table ---
130        sqlx::query(
131            r#"
132            CREATE TABLE IF NOT EXISTS sessions (
133                session_id VARCHAR(255) PRIMARY KEY,
134                user_id    VARCHAR(255) NOT NULL,
135                data       JSONB        NOT NULL,
136                expires_at TIMESTAMPTZ,
137                created_at TIMESTAMPTZ  DEFAULT NOW()
138            )
139            "#,
140        )
141        .execute(&mut *tx)
142        .await
143        .map_err(|e| {
144            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
145                "Migration failed (sessions): {e}"
146            )))
147        })?;
148
149        for stmt in [
150            "CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions (user_id)",
151            "CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions (expires_at)",
152        ] {
153            sqlx::query(stmt).execute(&mut *tx).await.map_err(|e| {
154                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
155                    "Migration failed (index): {e}"
156                )))
157            })?;
158        }
159
160        // --- kv_store table ---
161        sqlx::query(
162            r#"
163            CREATE TABLE IF NOT EXISTS kv_store (
164                key        VARCHAR(512) PRIMARY KEY,
165                value      BYTEA        NOT NULL,
166                expires_at TIMESTAMPTZ,
167                created_at TIMESTAMPTZ  DEFAULT NOW()
168            )
169            "#,
170        )
171        .execute(&mut *tx)
172        .await
173        .map_err(|e| {
174            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
175                "Migration failed (kv_store): {e}"
176            )))
177        })?;
178
179        sqlx::query("CREATE INDEX IF NOT EXISTS idx_kv_store_expires_at ON kv_store (expires_at)")
180            .execute(&mut *tx)
181            .await
182            .map_err(|e| {
183                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
184                    "Migration failed (index idx_kv_store_expires_at): {e}"
185                )))
186            })?;
187
188        tx.commit().await.map_err(|e| {
189            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
190                "Migration failed (commit): {e}"
191            )))
192        })?;
193
194        Ok(())
195    }
196}
197
198#[async_trait]
199impl AuthStorage for PostgresStorage {
200    async fn store_token(&self, token: &AuthToken) -> Result<()> {
201        sqlx::query(
202            r#"
203            INSERT INTO auth_tokens (
204                token_id, user_id, access_token, refresh_token, token_type,
205                expires_at, scopes, issued_at, auth_method, subject, issuer,
206                client_id, metadata, user_profile, permissions, roles
207            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
208            ON CONFLICT (token_id) DO UPDATE SET
209                access_token = EXCLUDED.access_token,
210                refresh_token = EXCLUDED.refresh_token,
211                expires_at = EXCLUDED.expires_at,
212                user_profile = EXCLUDED.user_profile,
213                permissions = EXCLUDED.permissions,
214                roles = EXCLUDED.roles
215            "#,
216        )
217        .bind(&token.token_id)
218        .bind(&token.user_id)
219        .bind(&token.access_token)
220        .bind(&token.refresh_token)
221        .bind(&token.token_type)
222        .bind(token.expires_at)
223        .bind(&token.scopes)
224        .bind(token.issued_at)
225        .bind(&token.auth_method)
226        .bind(&token.subject)
227        .bind(&token.issuer)
228        .bind(&token.client_id)
229        .bind(serde_json::to_value(&token.metadata).unwrap_or_default())
230        .bind(Self::profile_json(token))
231        .bind(&token.permissions)
232        .bind(&token.roles)
233        .execute(&self.pool)
234        .await
235        .map_err(|e| {
236            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
237                "Failed to store token: {}",
238                e
239            )))
240        })?;
241
242        Ok(())
243    }
244
245    // ... implement other AuthStorage methods
246    async fn get_token(&self, token_id: &str) -> Result<Option<AuthToken>> {
247        let row =
248            sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE token_id = $1"#)
249                .bind(token_id)
250                .fetch_optional(&self.pool)
251                .await
252                .map_err(|e| {
253                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
254                        "Failed to fetch token: {}",
255                        e
256                    )))
257                })?;
258        // An expired token is not returned (the trait asks backends to honour expiry).
259        Ok(row.filter(|t| !t.is_expired()))
260    }
261
262    async fn get_token_by_access_token(&self, access_token: &str) -> Result<Option<AuthToken>> {
263        let row =
264            sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE access_token = $1"#)
265                .bind(access_token)
266                .fetch_optional(&self.pool)
267                .await
268                .map_err(|e| {
269                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
270                        "Failed to fetch token by access_token: {}",
271                        e
272                    )))
273                })?;
274        Ok(row.filter(|t| !t.is_expired()))
275    }
276
277    async fn update_token(&self, token: &AuthToken) -> Result<()> {
278        sqlx::query(
279            r#"
280            UPDATE auth_tokens SET
281                access_token = $1,
282                refresh_token = $2,
283                token_type = $3,
284                expires_at = $4,
285                scopes = $5,
286                issued_at = $6,
287                auth_method = $7,
288                subject = $8,
289                issuer = $9,
290                client_id = $10,
291                metadata = $11,
292                user_profile = $12,
293                permissions = $13,
294                roles = $14
295            WHERE token_id = $15
296            "#,
297        )
298        .bind(&token.access_token)
299        .bind(&token.refresh_token)
300        .bind(&token.token_type)
301        .bind(token.expires_at)
302        .bind(&token.scopes)
303        .bind(token.issued_at)
304        .bind(&token.auth_method)
305        .bind(&token.subject)
306        .bind(&token.issuer)
307        .bind(&token.client_id)
308        .bind(serde_json::to_value(&token.metadata).unwrap_or_default())
309        .bind(Self::profile_json(token))
310        .bind(&token.permissions)
311        .bind(&token.roles)
312        .bind(&token.token_id)
313        .execute(&self.pool)
314        .await
315        .map_err(|e| {
316            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
317                "Failed to update token: {}",
318                e
319            )))
320        })?;
321        Ok(())
322    }
323
324    async fn delete_token(&self, token_id: &str) -> Result<()> {
325        sqlx::query(r#"DELETE FROM auth_tokens WHERE token_id = $1"#)
326            .bind(token_id)
327            .execute(&self.pool)
328            .await
329            .map_err(|e| {
330                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
331                    "Failed to delete token: {}",
332                    e
333                )))
334            })?;
335        Ok(())
336    }
337
338    async fn list_user_tokens(&self, user_id: &str) -> Result<Vec<AuthToken>> {
339        let tokens =
340            sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE user_id = $1"#)
341                .bind(user_id)
342                .fetch_all(&self.pool)
343                .await
344                .map_err(|e| {
345                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
346                        "Failed to list user tokens: {}",
347                        e
348                    )))
349                })?;
350        Ok(tokens)
351    }
352
353    async fn store_session(
354        &self,
355        session_id: &str,
356        data: &crate::storage::core::SessionData,
357    ) -> Result<()> {
358        sqlx::query(
359            r#"
360            INSERT INTO sessions (session_id, user_id, data, expires_at)
361            VALUES ($1, $2, $3, $4)
362            ON CONFLICT (session_id) DO UPDATE SET
363                data = EXCLUDED.data,
364                expires_at = EXCLUDED.expires_at
365            "#,
366        )
367        .bind(session_id)
368        .bind(&data.user_id)
369        .bind(serde_json::to_value(data).unwrap_or_default())
370        .bind(data.expires_at)
371        .execute(&self.pool)
372        .await
373        .map_err(|e| {
374            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
375                "Failed to store session: {}",
376                e
377            )))
378        })?;
379        Ok(())
380    }
381
382    async fn get_session(
383        &self,
384        session_id: &str,
385    ) -> Result<Option<crate::storage::core::SessionData>> {
386        let row = sqlx::query(r#"SELECT data FROM sessions WHERE session_id = $1"#)
387            .bind(session_id)
388            .fetch_optional(&self.pool)
389            .await
390            .map_err(|e| {
391                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
392                    "Failed to fetch session: {}",
393                    e
394                )))
395            })?;
396        if let Some(row) = row {
397            let data: serde_json::Value = row.try_get("data").map_err(|e| {
398                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
399                    "Failed to deserialize session data: {}",
400                    e
401                )))
402            })?;
403            let session: crate::storage::core::SessionData =
404                serde_json::from_value(data).map_err(|e| {
405                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
406                        "Failed to parse session data: {}",
407                        e
408                    )))
409                })?;
410            Ok(Some(session).filter(|s| s.expires_at > chrono::Utc::now()))
411        } else {
412            Ok(None)
413        }
414    }
415
416    async fn delete_session(&self, session_id: &str) -> Result<()> {
417        sqlx::query(r#"DELETE FROM sessions WHERE session_id = $1"#)
418            .bind(session_id)
419            .execute(&self.pool)
420            .await
421            .map_err(|e| {
422                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
423                    "Failed to delete session: {}",
424                    e
425                )))
426            })?;
427        Ok(())
428    }
429
430    async fn store_kv(
431        &self,
432        key: &str,
433        value: &[u8],
434        ttl: Option<std::time::Duration>,
435    ) -> Result<()> {
436        let expires_at = ttl.map(|d| {
437            chrono::Utc::now()
438                + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
439        });
440        sqlx::query(
441            r#"
442            INSERT INTO kv_store (key, value, expires_at)
443            VALUES ($1, $2, $3)
444            ON CONFLICT (key) DO UPDATE SET
445                value = EXCLUDED.value,
446                expires_at = EXCLUDED.expires_at
447            "#,
448        )
449        .bind(key)
450        .bind(value)
451        .bind(expires_at)
452        .execute(&self.pool)
453        .await
454        .map_err(|e| {
455            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
456                "Failed to store kv: {}",
457                e
458            )))
459        })?;
460        Ok(())
461    }
462
463    async fn store_kv_if_absent(
464        &self,
465        key: &str,
466        value: &[u8],
467        ttl: Option<std::time::Duration>,
468    ) -> Result<bool> {
469        let expires_at = ttl.map(|d| {
470            chrono::Utc::now()
471                + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
472        });
473        // One statement: insert, or take over an EXPIRED row; a live row is left alone.
474        let result = sqlx::query(
475            r#"
476            INSERT INTO kv_store (key, value, expires_at)
477            VALUES ($1, $2, $3)
478            ON CONFLICT (key) DO UPDATE SET
479                value = EXCLUDED.value,
480                expires_at = EXCLUDED.expires_at
481            WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
482            "#,
483        )
484        .bind(key)
485        .bind(value)
486        .bind(expires_at)
487        .execute(&self.pool)
488        .await
489        .map_err(|e| {
490            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
491                "Failed to store kv if absent: {}",
492                e
493            )))
494        })?;
495        Ok(result.rows_affected() == 1)
496    }
497
498    async fn swap_kv(
499        &self,
500        remove: &[String],
501        key: &str,
502        value: &[u8],
503        ttl: Option<std::time::Duration>,
504    ) -> Result<bool> {
505        if remove.is_empty() {
506            return Ok(false);
507        }
508        let expires_at = ttl.map(|d| {
509            chrono::Utc::now()
510                + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
511        });
512        let fail = |what: &str, e: sqlx::Error| {
513            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
514                "Failed to swap kv ({what}): {e}"
515            )))
516        };
517        let mut tx = self.pool.begin().await.map_err(|e| fail("begin", e))?;
518
519        // Deleting takes the row locks: a competing swap of the same key waits here, then finds
520        // the row gone and loses.
521        let removed = sqlx::query(
522            r#"
523            DELETE FROM kv_store
524            WHERE key = ANY($1) AND (expires_at IS NULL OR expires_at > NOW())
525            "#,
526        )
527        .bind(remove)
528        .execute(&mut *tx)
529        .await
530        .map_err(|e| fail("remove", e))?
531        .rows_affected();
532        if removed == 0 {
533            tx.rollback().await.map_err(|e| fail("rollback", e))?;
534            return Ok(false);
535        }
536
537        // Insert, or take over an EXPIRED row; a live row refuses and rolls the delete back.
538        let inserted = sqlx::query(
539            r#"
540            INSERT INTO kv_store (key, value, expires_at)
541            VALUES ($1, $2, $3)
542            ON CONFLICT (key) DO UPDATE SET
543                value = EXCLUDED.value,
544                expires_at = EXCLUDED.expires_at
545            WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
546            "#,
547        )
548        .bind(key)
549        .bind(value)
550        .bind(expires_at)
551        .execute(&mut *tx)
552        .await
553        .map_err(|e| fail("store", e))?
554        .rows_affected();
555        if inserted != 1 {
556            tx.rollback().await.map_err(|e| fail("rollback", e))?;
557            return Ok(false);
558        }
559        tx.commit().await.map_err(|e| fail("commit", e))?;
560        Ok(true)
561    }
562
563    fn supports_atomic_swap(&self) -> bool {
564        true
565    }
566
567    async fn get_kv(&self, key: &str) -> Result<Option<Vec<u8>>> {
568        let row = sqlx::query(
569            r#"SELECT value FROM kv_store WHERE key = $1 AND (expires_at IS NULL OR expires_at > NOW())"#
570        )
571        .bind(key)
572        .fetch_optional(&self.pool)
573        .await
574        .map_err(|e| {
575            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
576                "Failed to fetch kv: {}", e
577            )))
578        })?;
579        if let Some(row) = row {
580            let value: Vec<u8> = row.try_get("value").map_err(|e| {
581                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
582                    "Failed to deserialize kv value: {}",
583                    e
584                )))
585            })?;
586            Ok(Some(value))
587        } else {
588            Ok(None)
589        }
590    }
591
592    async fn get_kv_ttl(&self, key: &str) -> Result<Option<std::time::Duration>> {
593        let row = sqlx::query(
594            r#"SELECT EXTRACT(EPOCH FROM (expires_at - NOW()))::float8 AS seconds
595               FROM kv_store
596               WHERE key = $1 AND expires_at IS NOT NULL AND expires_at > NOW()"#,
597        )
598        .bind(key)
599        .fetch_optional(&self.pool)
600        .await
601        .map_err(|e| {
602            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
603                "Failed to fetch kv ttl: {}",
604                e
605            )))
606        })?;
607        Ok(row
608            .and_then(|row| row.try_get::<f64, _>("seconds").ok())
609            .filter(|seconds| *seconds > 0.0)
610            .map(std::time::Duration::from_secs_f64))
611    }
612
613    fn tracks_kv_ttl(&self) -> bool {
614        true
615    }
616
617    async fn delete_kv(&self, key: &str) -> Result<()> {
618        sqlx::query(r#"DELETE FROM kv_store WHERE key = $1"#)
619            .bind(key)
620            .execute(&self.pool)
621            .await
622            .map_err(|e| {
623                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
624                    "Failed to delete kv: {}",
625                    e
626                )))
627            })?;
628        Ok(())
629    }
630
631    async fn list_kv_keys(&self, prefix: &str) -> Result<Vec<String>> {
632        let rows = sqlx::query(
633            r#"
634            SELECT key
635            FROM kv_store
636            WHERE key LIKE $1 AND (expires_at IS NULL OR expires_at > NOW())
637            ORDER BY key
638            "#,
639        )
640        .bind(format!("{prefix}%"))
641        .fetch_all(&self.pool)
642        .await
643        .map_err(|e| {
644            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
645                "Failed to list kv keys: {}",
646                e
647            )))
648        })?;
649
650        rows.into_iter()
651            .map(|row| {
652                row.try_get("key").map_err(|e| {
653                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
654                        "Failed to decode kv key: {}",
655                        e
656                    )))
657                })
658            })
659            .collect()
660    }
661
662    async fn cleanup_expired(&self) -> Result<()> {
663        // Remove expired tokens
664        sqlx::query("DELETE FROM auth_tokens WHERE expires_at < NOW()")
665            .execute(&self.pool)
666            .await
667            .map_err(|e| {
668                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
669                    "Failed to cleanup expired tokens: {}",
670                    e
671                )))
672            })?;
673
674        // Remove expired sessions
675        sqlx::query("DELETE FROM sessions WHERE expires_at IS NOT NULL AND expires_at < NOW()")
676            .execute(&self.pool)
677            .await
678            .map_err(|e| {
679                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
680                    "Failed to cleanup expired sessions: {}",
681                    e
682                )))
683            })?;
684
685        // Remove expired kv entries
686        sqlx::query("DELETE FROM kv_store WHERE expires_at IS NOT NULL AND expires_at < NOW()")
687            .execute(&self.pool)
688            .await
689            .map_err(|e| {
690                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
691                    "Failed to cleanup expired kv: {}",
692                    e
693                )))
694            })?;
695
696        Ok(())
697    }
698
699    async fn list_user_sessions(&self, user_id: &str) -> Result<Vec<SessionData>> {
700        // `data` holds the whole session (as `store_session` writes it and `get_session` reads
701        // it); the table has no `last_activity`, `ip_address` or `user_agent` columns.
702        let rows = sqlx::query(
703            r#"
704            SELECT data
705            FROM sessions
706            WHERE user_id = $1 AND (expires_at IS NULL OR expires_at > NOW())
707            "#,
708        )
709        .bind(user_id)
710        .fetch_all(&self.pool)
711        .await
712        .map_err(|e| {
713            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
714                "Failed to list user sessions: {}",
715                e
716            )))
717        })?;
718
719        rows.into_iter()
720            .map(|row| {
721                let data: serde_json::Value = row.try_get("data").map_err(|e| {
722                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
723                        "Failed to read session data: {}",
724                        e
725                    )))
726                })?;
727                serde_json::from_value::<SessionData>(data).map_err(|e| {
728                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
729                        "Failed to parse session data: {}",
730                        e
731                    )))
732                })
733            })
734            .collect()
735    }
736
737    async fn count_active_sessions(&self) -> Result<u64> {
738        let row = sqlx::query(
739            "SELECT COUNT(*) as count FROM sessions WHERE expires_at IS NULL OR expires_at > NOW()",
740        )
741        .fetch_one(&self.pool)
742        .await
743        .map_err(|e| {
744            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
745                "Failed to count active sessions: {}",
746                e
747            )))
748        })?;
749
750        let count: i64 = row.try_get("count").map_err(|e| {
751            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
752                "Failed to parse session count: {}",
753                e
754            )))
755        })?;
756
757        Ok(count as u64)
758    }
759}