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 (one definition, shared with the CLI's migrations; issue #147) ---
130        sqlx::raw_sql(crate::migrations::SESSIONS_SCHEMA_SQL)
131            .execute(&mut *tx)
132            .await
133            .map_err(|e| {
134                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
135                    "Migration failed (sessions): {e}"
136                )))
137            })?;
138
139        // --- kv_store table ---
140        sqlx::query(
141            r#"
142            CREATE TABLE IF NOT EXISTS kv_store (
143                key        VARCHAR(512) PRIMARY KEY,
144                value      BYTEA        NOT NULL,
145                expires_at TIMESTAMPTZ,
146                created_at TIMESTAMPTZ  DEFAULT NOW()
147            )
148            "#,
149        )
150        .execute(&mut *tx)
151        .await
152        .map_err(|e| {
153            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
154                "Migration failed (kv_store): {e}"
155            )))
156        })?;
157
158        sqlx::query("CREATE INDEX IF NOT EXISTS idx_kv_store_expires_at ON kv_store (expires_at)")
159            .execute(&mut *tx)
160            .await
161            .map_err(|e| {
162                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
163                    "Migration failed (index idx_kv_store_expires_at): {e}"
164                )))
165            })?;
166
167        tx.commit().await.map_err(|e| {
168            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
169                "Migration failed (commit): {e}"
170            )))
171        })?;
172
173        Ok(())
174    }
175}
176
177#[async_trait]
178impl AuthStorage for PostgresStorage {
179    async fn store_token(&self, token: &AuthToken) -> Result<()> {
180        sqlx::query(
181            r#"
182            INSERT INTO auth_tokens (
183                token_id, user_id, access_token, refresh_token, token_type,
184                expires_at, scopes, issued_at, auth_method, subject, issuer,
185                client_id, metadata, user_profile, permissions, roles
186            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
187            ON CONFLICT (token_id) DO UPDATE SET
188                access_token = EXCLUDED.access_token,
189                refresh_token = EXCLUDED.refresh_token,
190                expires_at = EXCLUDED.expires_at,
191                user_profile = EXCLUDED.user_profile,
192                permissions = EXCLUDED.permissions,
193                roles = EXCLUDED.roles
194            "#,
195        )
196        .bind(&token.token_id)
197        .bind(&token.user_id)
198        .bind(&token.access_token)
199        .bind(&token.refresh_token)
200        .bind(&token.token_type)
201        .bind(token.expires_at)
202        .bind(&token.scopes)
203        .bind(token.issued_at)
204        .bind(&token.auth_method)
205        .bind(&token.subject)
206        .bind(&token.issuer)
207        .bind(&token.client_id)
208        .bind(serde_json::to_value(&token.metadata).unwrap_or_default())
209        .bind(Self::profile_json(token))
210        .bind(&token.permissions)
211        .bind(&token.roles)
212        .execute(&self.pool)
213        .await
214        .map_err(|e| {
215            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
216                "Failed to store token: {}",
217                e
218            )))
219        })?;
220
221        Ok(())
222    }
223
224    // ... implement other AuthStorage methods
225    async fn get_token(&self, token_id: &str) -> Result<Option<AuthToken>> {
226        let row =
227            sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE token_id = $1"#)
228                .bind(token_id)
229                .fetch_optional(&self.pool)
230                .await
231                .map_err(|e| {
232                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
233                        "Failed to fetch token: {}",
234                        e
235                    )))
236                })?;
237        // An expired token is not returned (the trait asks backends to honour expiry).
238        Ok(row.filter(|t| !t.is_expired()))
239    }
240
241    async fn get_token_by_access_token(&self, access_token: &str) -> Result<Option<AuthToken>> {
242        let row =
243            sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE access_token = $1"#)
244                .bind(access_token)
245                .fetch_optional(&self.pool)
246                .await
247                .map_err(|e| {
248                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
249                        "Failed to fetch token by access_token: {}",
250                        e
251                    )))
252                })?;
253        Ok(row.filter(|t| !t.is_expired()))
254    }
255
256    async fn update_token(&self, token: &AuthToken) -> Result<()> {
257        sqlx::query(
258            r#"
259            UPDATE auth_tokens SET
260                access_token = $1,
261                refresh_token = $2,
262                token_type = $3,
263                expires_at = $4,
264                scopes = $5,
265                issued_at = $6,
266                auth_method = $7,
267                subject = $8,
268                issuer = $9,
269                client_id = $10,
270                metadata = $11,
271                user_profile = $12,
272                permissions = $13,
273                roles = $14
274            WHERE token_id = $15
275            "#,
276        )
277        .bind(&token.access_token)
278        .bind(&token.refresh_token)
279        .bind(&token.token_type)
280        .bind(token.expires_at)
281        .bind(&token.scopes)
282        .bind(token.issued_at)
283        .bind(&token.auth_method)
284        .bind(&token.subject)
285        .bind(&token.issuer)
286        .bind(&token.client_id)
287        .bind(serde_json::to_value(&token.metadata).unwrap_or_default())
288        .bind(Self::profile_json(token))
289        .bind(&token.permissions)
290        .bind(&token.roles)
291        .bind(&token.token_id)
292        .execute(&self.pool)
293        .await
294        .map_err(|e| {
295            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
296                "Failed to update token: {}",
297                e
298            )))
299        })?;
300        Ok(())
301    }
302
303    async fn delete_token(&self, token_id: &str) -> Result<()> {
304        sqlx::query(r#"DELETE FROM auth_tokens WHERE token_id = $1"#)
305            .bind(token_id)
306            .execute(&self.pool)
307            .await
308            .map_err(|e| {
309                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
310                    "Failed to delete token: {}",
311                    e
312                )))
313            })?;
314        Ok(())
315    }
316
317    async fn list_user_tokens(&self, user_id: &str) -> Result<Vec<AuthToken>> {
318        let tokens =
319            sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE user_id = $1"#)
320                .bind(user_id)
321                .fetch_all(&self.pool)
322                .await
323                .map_err(|e| {
324                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
325                        "Failed to list user tokens: {}",
326                        e
327                    )))
328                })?;
329        Ok(tokens)
330    }
331
332    async fn store_session(
333        &self,
334        session_id: &str,
335        data: &crate::storage::core::SessionData,
336    ) -> Result<()> {
337        sqlx::query(
338            r#"
339            INSERT INTO sessions (session_id, user_id, data, expires_at)
340            VALUES ($1, $2, $3, $4)
341            ON CONFLICT (session_id) DO UPDATE SET
342                data = EXCLUDED.data,
343                expires_at = EXCLUDED.expires_at
344            "#,
345        )
346        .bind(session_id)
347        .bind(&data.user_id)
348        .bind(serde_json::to_value(data).unwrap_or_default())
349        .bind(data.expires_at)
350        .execute(&self.pool)
351        .await
352        .map_err(|e| {
353            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
354                "Failed to store session: {}",
355                e
356            )))
357        })?;
358        Ok(())
359    }
360
361    async fn get_session(
362        &self,
363        session_id: &str,
364    ) -> Result<Option<crate::storage::core::SessionData>> {
365        let row = sqlx::query(r#"SELECT data FROM sessions WHERE session_id = $1"#)
366            .bind(session_id)
367            .fetch_optional(&self.pool)
368            .await
369            .map_err(|e| {
370                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
371                    "Failed to fetch session: {}",
372                    e
373                )))
374            })?;
375        if let Some(row) = row {
376            let data: serde_json::Value = row.try_get("data").map_err(|e| {
377                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
378                    "Failed to deserialize session data: {}",
379                    e
380                )))
381            })?;
382            let session: crate::storage::core::SessionData =
383                serde_json::from_value(data).map_err(|e| {
384                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
385                        "Failed to parse session data: {}",
386                        e
387                    )))
388                })?;
389            Ok(Some(session).filter(|s| s.expires_at > chrono::Utc::now()))
390        } else {
391            Ok(None)
392        }
393    }
394
395    async fn delete_session(&self, session_id: &str) -> Result<()> {
396        sqlx::query(r#"DELETE FROM sessions WHERE session_id = $1"#)
397            .bind(session_id)
398            .execute(&self.pool)
399            .await
400            .map_err(|e| {
401                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
402                    "Failed to delete session: {}",
403                    e
404                )))
405            })?;
406        Ok(())
407    }
408
409    async fn store_kv(
410        &self,
411        key: &str,
412        value: &[u8],
413        ttl: Option<std::time::Duration>,
414    ) -> Result<()> {
415        let expires_at = ttl.map(|d| {
416            chrono::Utc::now()
417                + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
418        });
419        sqlx::query(
420            r#"
421            INSERT INTO kv_store (key, value, expires_at)
422            VALUES ($1, $2, $3)
423            ON CONFLICT (key) DO UPDATE SET
424                value = EXCLUDED.value,
425                expires_at = EXCLUDED.expires_at
426            "#,
427        )
428        .bind(key)
429        .bind(value)
430        .bind(expires_at)
431        .execute(&self.pool)
432        .await
433        .map_err(|e| {
434            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
435                "Failed to store kv: {}",
436                e
437            )))
438        })?;
439        Ok(())
440    }
441
442    async fn store_kv_if_absent(
443        &self,
444        key: &str,
445        value: &[u8],
446        ttl: Option<std::time::Duration>,
447    ) -> Result<bool> {
448        let expires_at = ttl.map(|d| {
449            chrono::Utc::now()
450                + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
451        });
452        // One statement: insert, or take over an EXPIRED row; a live row is left alone.
453        let result = sqlx::query(
454            r#"
455            INSERT INTO kv_store (key, value, expires_at)
456            VALUES ($1, $2, $3)
457            ON CONFLICT (key) DO UPDATE SET
458                value = EXCLUDED.value,
459                expires_at = EXCLUDED.expires_at
460            WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
461            "#,
462        )
463        .bind(key)
464        .bind(value)
465        .bind(expires_at)
466        .execute(&self.pool)
467        .await
468        .map_err(|e| {
469            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
470                "Failed to store kv if absent: {}",
471                e
472            )))
473        })?;
474        Ok(result.rows_affected() == 1)
475    }
476
477    async fn swap_kv(
478        &self,
479        remove: &[String],
480        key: &str,
481        value: &[u8],
482        ttl: Option<std::time::Duration>,
483    ) -> Result<bool> {
484        if remove.is_empty() {
485            return Ok(false);
486        }
487        let expires_at = ttl.map(|d| {
488            chrono::Utc::now()
489                + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
490        });
491        let fail = |what: &str, e: sqlx::Error| {
492            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
493                "Failed to swap kv ({what}): {e}"
494            )))
495        };
496        let mut tx = self.pool.begin().await.map_err(|e| fail("begin", e))?;
497
498        // Deleting takes the row locks: a competing swap of the same key waits here, then finds
499        // the row gone and loses.
500        let removed = sqlx::query(
501            r#"
502            DELETE FROM kv_store
503            WHERE key = ANY($1) AND (expires_at IS NULL OR expires_at > NOW())
504            "#,
505        )
506        .bind(remove)
507        .execute(&mut *tx)
508        .await
509        .map_err(|e| fail("remove", e))?
510        .rows_affected();
511        if removed == 0 {
512            tx.rollback().await.map_err(|e| fail("rollback", e))?;
513            return Ok(false);
514        }
515
516        // Insert, or take over an EXPIRED row; a live row refuses and rolls the delete back.
517        let inserted = sqlx::query(
518            r#"
519            INSERT INTO kv_store (key, value, expires_at)
520            VALUES ($1, $2, $3)
521            ON CONFLICT (key) DO UPDATE SET
522                value = EXCLUDED.value,
523                expires_at = EXCLUDED.expires_at
524            WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
525            "#,
526        )
527        .bind(key)
528        .bind(value)
529        .bind(expires_at)
530        .execute(&mut *tx)
531        .await
532        .map_err(|e| fail("store", e))?
533        .rows_affected();
534        if inserted != 1 {
535            tx.rollback().await.map_err(|e| fail("rollback", e))?;
536            return Ok(false);
537        }
538        tx.commit().await.map_err(|e| fail("commit", e))?;
539        Ok(true)
540    }
541
542    fn supports_atomic_swap(&self) -> bool {
543        true
544    }
545
546    async fn get_kv(&self, key: &str) -> Result<Option<Vec<u8>>> {
547        let row = sqlx::query(
548            r#"SELECT value FROM kv_store WHERE key = $1 AND (expires_at IS NULL OR expires_at > NOW())"#
549        )
550        .bind(key)
551        .fetch_optional(&self.pool)
552        .await
553        .map_err(|e| {
554            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
555                "Failed to fetch kv: {}", e
556            )))
557        })?;
558        if let Some(row) = row {
559            let value: Vec<u8> = row.try_get("value").map_err(|e| {
560                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
561                    "Failed to deserialize kv value: {}",
562                    e
563                )))
564            })?;
565            Ok(Some(value))
566        } else {
567            Ok(None)
568        }
569    }
570
571    async fn get_kv_ttl(&self, key: &str) -> Result<Option<std::time::Duration>> {
572        let row = sqlx::query(
573            r#"SELECT EXTRACT(EPOCH FROM (expires_at - NOW()))::float8 AS seconds
574               FROM kv_store
575               WHERE key = $1 AND expires_at IS NOT NULL AND expires_at > NOW()"#,
576        )
577        .bind(key)
578        .fetch_optional(&self.pool)
579        .await
580        .map_err(|e| {
581            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
582                "Failed to fetch kv ttl: {}",
583                e
584            )))
585        })?;
586        Ok(row
587            .and_then(|row| row.try_get::<f64, _>("seconds").ok())
588            .filter(|seconds| *seconds > 0.0)
589            .map(std::time::Duration::from_secs_f64))
590    }
591
592    fn tracks_kv_ttl(&self) -> bool {
593        true
594    }
595
596    async fn delete_kv(&self, key: &str) -> Result<()> {
597        sqlx::query(r#"DELETE FROM kv_store WHERE key = $1"#)
598            .bind(key)
599            .execute(&self.pool)
600            .await
601            .map_err(|e| {
602                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
603                    "Failed to delete kv: {}",
604                    e
605                )))
606            })?;
607        Ok(())
608    }
609
610    async fn list_kv_keys(&self, prefix: &str) -> Result<Vec<String>> {
611        let rows = sqlx::query(
612            r#"
613            SELECT key
614            FROM kv_store
615            WHERE key LIKE $1 AND (expires_at IS NULL OR expires_at > NOW())
616            ORDER BY key
617            "#,
618        )
619        .bind(format!("{prefix}%"))
620        .fetch_all(&self.pool)
621        .await
622        .map_err(|e| {
623            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
624                "Failed to list kv keys: {}",
625                e
626            )))
627        })?;
628
629        rows.into_iter()
630            .map(|row| {
631                row.try_get("key").map_err(|e| {
632                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
633                        "Failed to decode kv key: {}",
634                        e
635                    )))
636                })
637            })
638            .collect()
639    }
640
641    async fn cleanup_expired(&self) -> Result<()> {
642        // Remove expired tokens
643        sqlx::query("DELETE FROM auth_tokens WHERE expires_at < NOW()")
644            .execute(&self.pool)
645            .await
646            .map_err(|e| {
647                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
648                    "Failed to cleanup expired tokens: {}",
649                    e
650                )))
651            })?;
652
653        // Remove expired sessions
654        sqlx::query("DELETE FROM sessions WHERE expires_at IS NOT NULL AND expires_at < NOW()")
655            .execute(&self.pool)
656            .await
657            .map_err(|e| {
658                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
659                    "Failed to cleanup expired sessions: {}",
660                    e
661                )))
662            })?;
663
664        // Remove expired kv entries
665        sqlx::query("DELETE FROM kv_store WHERE expires_at IS NOT NULL AND expires_at < NOW()")
666            .execute(&self.pool)
667            .await
668            .map_err(|e| {
669                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
670                    "Failed to cleanup expired kv: {}",
671                    e
672                )))
673            })?;
674
675        Ok(())
676    }
677
678    async fn list_user_sessions(&self, user_id: &str) -> Result<Vec<SessionData>> {
679        // `data` holds the whole session (as `store_session` writes it and `get_session` reads
680        // it); the table has no `last_activity`, `ip_address` or `user_agent` columns.
681        let rows = sqlx::query(
682            r#"
683            SELECT data
684            FROM sessions
685            WHERE user_id = $1 AND (expires_at IS NULL OR expires_at > NOW())
686            "#,
687        )
688        .bind(user_id)
689        .fetch_all(&self.pool)
690        .await
691        .map_err(|e| {
692            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
693                "Failed to list user sessions: {}",
694                e
695            )))
696        })?;
697
698        rows.into_iter()
699            .map(|row| {
700                let data: serde_json::Value = row.try_get("data").map_err(|e| {
701                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
702                        "Failed to read session data: {}",
703                        e
704                    )))
705                })?;
706                serde_json::from_value::<SessionData>(data).map_err(|e| {
707                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
708                        "Failed to parse session data: {}",
709                        e
710                    )))
711                })
712            })
713            .collect()
714    }
715
716    async fn count_active_sessions(&self) -> Result<u64> {
717        let row = sqlx::query(
718            "SELECT COUNT(*) as count FROM sessions WHERE expires_at IS NULL OR expires_at > NOW()",
719        )
720        .fetch_one(&self.pool)
721        .await
722        .map_err(|e| {
723            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
724                "Failed to count active sessions: {}",
725                e
726            )))
727        })?;
728
729        let count: i64 = row.try_get("count").map_err(|e| {
730            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
731                "Failed to parse session count: {}",
732                e
733            )))
734        })?;
735
736        Ok(count as u64)
737    }
738}