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        Ok(row)
259    }
260
261    async fn get_token_by_access_token(&self, access_token: &str) -> Result<Option<AuthToken>> {
262        let row =
263            sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE access_token = $1"#)
264                .bind(access_token)
265                .fetch_optional(&self.pool)
266                .await
267                .map_err(|e| {
268                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
269                        "Failed to fetch token by access_token: {}",
270                        e
271                    )))
272                })?;
273        Ok(row)
274    }
275
276    async fn update_token(&self, token: &AuthToken) -> Result<()> {
277        sqlx::query(
278            r#"
279            UPDATE auth_tokens SET
280                access_token = $1,
281                refresh_token = $2,
282                token_type = $3,
283                expires_at = $4,
284                scopes = $5,
285                issued_at = $6,
286                auth_method = $7,
287                subject = $8,
288                issuer = $9,
289                client_id = $10,
290                metadata = $11,
291                user_profile = $12,
292                permissions = $13,
293                roles = $14
294            WHERE token_id = $15
295            "#,
296        )
297        .bind(&token.access_token)
298        .bind(&token.refresh_token)
299        .bind(&token.token_type)
300        .bind(token.expires_at)
301        .bind(&token.scopes)
302        .bind(token.issued_at)
303        .bind(&token.auth_method)
304        .bind(&token.subject)
305        .bind(&token.issuer)
306        .bind(&token.client_id)
307        .bind(serde_json::to_value(&token.metadata).unwrap_or_default())
308        .bind(Self::profile_json(token))
309        .bind(&token.permissions)
310        .bind(&token.roles)
311        .bind(&token.token_id)
312        .execute(&self.pool)
313        .await
314        .map_err(|e| {
315            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
316                "Failed to update token: {}",
317                e
318            )))
319        })?;
320        Ok(())
321    }
322
323    async fn delete_token(&self, token_id: &str) -> Result<()> {
324        sqlx::query(r#"DELETE FROM auth_tokens WHERE token_id = $1"#)
325            .bind(token_id)
326            .execute(&self.pool)
327            .await
328            .map_err(|e| {
329                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
330                    "Failed to delete token: {}",
331                    e
332                )))
333            })?;
334        Ok(())
335    }
336
337    async fn list_user_tokens(&self, user_id: &str) -> Result<Vec<AuthToken>> {
338        let tokens =
339            sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE user_id = $1"#)
340                .bind(user_id)
341                .fetch_all(&self.pool)
342                .await
343                .map_err(|e| {
344                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
345                        "Failed to list user tokens: {}",
346                        e
347                    )))
348                })?;
349        Ok(tokens)
350    }
351
352    async fn store_session(
353        &self,
354        session_id: &str,
355        data: &crate::storage::core::SessionData,
356    ) -> Result<()> {
357        sqlx::query(
358            r#"
359            INSERT INTO sessions (session_id, user_id, data, expires_at)
360            VALUES ($1, $2, $3, $4)
361            ON CONFLICT (session_id) DO UPDATE SET
362                data = EXCLUDED.data,
363                expires_at = EXCLUDED.expires_at
364            "#,
365        )
366        .bind(session_id)
367        .bind(&data.user_id)
368        .bind(serde_json::to_value(data).unwrap_or_default())
369        .bind(data.expires_at)
370        .execute(&self.pool)
371        .await
372        .map_err(|e| {
373            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
374                "Failed to store session: {}",
375                e
376            )))
377        })?;
378        Ok(())
379    }
380
381    async fn get_session(
382        &self,
383        session_id: &str,
384    ) -> Result<Option<crate::storage::core::SessionData>> {
385        let row = sqlx::query(r#"SELECT data FROM sessions WHERE session_id = $1"#)
386            .bind(session_id)
387            .fetch_optional(&self.pool)
388            .await
389            .map_err(|e| {
390                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
391                    "Failed to fetch session: {}",
392                    e
393                )))
394            })?;
395        if let Some(row) = row {
396            let data: serde_json::Value = row.try_get("data").map_err(|e| {
397                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
398                    "Failed to deserialize session data: {}",
399                    e
400                )))
401            })?;
402            let session: crate::storage::core::SessionData =
403                serde_json::from_value(data).map_err(|e| {
404                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
405                        "Failed to parse session data: {}",
406                        e
407                    )))
408                })?;
409            Ok(Some(session))
410        } else {
411            Ok(None)
412        }
413    }
414
415    async fn delete_session(&self, session_id: &str) -> Result<()> {
416        sqlx::query(r#"DELETE FROM sessions WHERE session_id = $1"#)
417            .bind(session_id)
418            .execute(&self.pool)
419            .await
420            .map_err(|e| {
421                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
422                    "Failed to delete session: {}",
423                    e
424                )))
425            })?;
426        Ok(())
427    }
428
429    async fn store_kv(
430        &self,
431        key: &str,
432        value: &[u8],
433        ttl: Option<std::time::Duration>,
434    ) -> Result<()> {
435        let expires_at = ttl.map(|d| {
436            chrono::Utc::now()
437                + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
438        });
439        sqlx::query(
440            r#"
441            INSERT INTO kv_store (key, value, expires_at)
442            VALUES ($1, $2, $3)
443            ON CONFLICT (key) DO UPDATE SET
444                value = EXCLUDED.value,
445                expires_at = EXCLUDED.expires_at
446            "#,
447        )
448        .bind(key)
449        .bind(value)
450        .bind(expires_at)
451        .execute(&self.pool)
452        .await
453        .map_err(|e| {
454            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
455                "Failed to store kv: {}",
456                e
457            )))
458        })?;
459        Ok(())
460    }
461
462    async fn store_kv_if_absent(
463        &self,
464        key: &str,
465        value: &[u8],
466        ttl: Option<std::time::Duration>,
467    ) -> Result<bool> {
468        let expires_at = ttl.map(|d| {
469            chrono::Utc::now()
470                + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
471        });
472        // One statement: insert, or take over an EXPIRED row; a live row is left alone.
473        let result = sqlx::query(
474            r#"
475            INSERT INTO kv_store (key, value, expires_at)
476            VALUES ($1, $2, $3)
477            ON CONFLICT (key) DO UPDATE SET
478                value = EXCLUDED.value,
479                expires_at = EXCLUDED.expires_at
480            WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
481            "#,
482        )
483        .bind(key)
484        .bind(value)
485        .bind(expires_at)
486        .execute(&self.pool)
487        .await
488        .map_err(|e| {
489            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
490                "Failed to store kv if absent: {}",
491                e
492            )))
493        })?;
494        Ok(result.rows_affected() == 1)
495    }
496
497    async fn swap_kv(
498        &self,
499        remove: &[String],
500        key: &str,
501        value: &[u8],
502        ttl: Option<std::time::Duration>,
503    ) -> Result<bool> {
504        if remove.is_empty() {
505            return Ok(false);
506        }
507        let expires_at = ttl.map(|d| {
508            chrono::Utc::now()
509                + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
510        });
511        let fail = |what: &str, e: sqlx::Error| {
512            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
513                "Failed to swap kv ({what}): {e}"
514            )))
515        };
516        let mut tx = self.pool.begin().await.map_err(|e| fail("begin", e))?;
517
518        // Deleting takes the row locks: a competing swap of the same key waits here, then finds
519        // the row gone and loses.
520        let removed = sqlx::query(
521            r#"
522            DELETE FROM kv_store
523            WHERE key = ANY($1) AND (expires_at IS NULL OR expires_at > NOW())
524            "#,
525        )
526        .bind(remove)
527        .execute(&mut *tx)
528        .await
529        .map_err(|e| fail("remove", e))?
530        .rows_affected();
531        if removed == 0 {
532            tx.rollback().await.map_err(|e| fail("rollback", e))?;
533            return Ok(false);
534        }
535
536        // Insert, or take over an EXPIRED row; a live row refuses and rolls the delete back.
537        let inserted = sqlx::query(
538            r#"
539            INSERT INTO kv_store (key, value, expires_at)
540            VALUES ($1, $2, $3)
541            ON CONFLICT (key) DO UPDATE SET
542                value = EXCLUDED.value,
543                expires_at = EXCLUDED.expires_at
544            WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
545            "#,
546        )
547        .bind(key)
548        .bind(value)
549        .bind(expires_at)
550        .execute(&mut *tx)
551        .await
552        .map_err(|e| fail("store", e))?
553        .rows_affected();
554        if inserted != 1 {
555            tx.rollback().await.map_err(|e| fail("rollback", e))?;
556            return Ok(false);
557        }
558        tx.commit().await.map_err(|e| fail("commit", e))?;
559        Ok(true)
560    }
561
562    fn supports_atomic_swap(&self) -> bool {
563        true
564    }
565
566    async fn get_kv(&self, key: &str) -> Result<Option<Vec<u8>>> {
567        let row = sqlx::query(
568            r#"SELECT value FROM kv_store WHERE key = $1 AND (expires_at IS NULL OR expires_at > NOW())"#
569        )
570        .bind(key)
571        .fetch_optional(&self.pool)
572        .await
573        .map_err(|e| {
574            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
575                "Failed to fetch kv: {}", e
576            )))
577        })?;
578        if let Some(row) = row {
579            let value: Vec<u8> = row.try_get("value").map_err(|e| {
580                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
581                    "Failed to deserialize kv value: {}",
582                    e
583                )))
584            })?;
585            Ok(Some(value))
586        } else {
587            Ok(None)
588        }
589    }
590
591    async fn get_kv_ttl(&self, key: &str) -> Result<Option<std::time::Duration>> {
592        let row = sqlx::query(
593            r#"SELECT EXTRACT(EPOCH FROM (expires_at - NOW()))::float8 AS seconds
594               FROM kv_store
595               WHERE key = $1 AND expires_at IS NOT NULL AND expires_at > NOW()"#,
596        )
597        .bind(key)
598        .fetch_optional(&self.pool)
599        .await
600        .map_err(|e| {
601            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
602                "Failed to fetch kv ttl: {}",
603                e
604            )))
605        })?;
606        Ok(row
607            .and_then(|row| row.try_get::<f64, _>("seconds").ok())
608            .filter(|seconds| *seconds > 0.0)
609            .map(std::time::Duration::from_secs_f64))
610    }
611
612    fn tracks_kv_ttl(&self) -> bool {
613        true
614    }
615
616    async fn delete_kv(&self, key: &str) -> Result<()> {
617        sqlx::query(r#"DELETE FROM kv_store WHERE key = $1"#)
618            .bind(key)
619            .execute(&self.pool)
620            .await
621            .map_err(|e| {
622                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
623                    "Failed to delete kv: {}",
624                    e
625                )))
626            })?;
627        Ok(())
628    }
629
630    async fn list_kv_keys(&self, prefix: &str) -> Result<Vec<String>> {
631        let rows = sqlx::query(
632            r#"
633            SELECT key
634            FROM kv_store
635            WHERE key LIKE $1 AND (expires_at IS NULL OR expires_at > NOW())
636            ORDER BY key
637            "#,
638        )
639        .bind(format!("{prefix}%"))
640        .fetch_all(&self.pool)
641        .await
642        .map_err(|e| {
643            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
644                "Failed to list kv keys: {}",
645                e
646            )))
647        })?;
648
649        rows.into_iter()
650            .map(|row| {
651                row.try_get("key").map_err(|e| {
652                    AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
653                        "Failed to decode kv key: {}",
654                        e
655                    )))
656                })
657            })
658            .collect()
659    }
660
661    async fn cleanup_expired(&self) -> Result<()> {
662        // Remove expired tokens
663        sqlx::query("DELETE FROM auth_tokens WHERE expires_at < NOW()")
664            .execute(&self.pool)
665            .await
666            .map_err(|e| {
667                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
668                    "Failed to cleanup expired tokens: {}",
669                    e
670                )))
671            })?;
672
673        // Remove expired sessions
674        sqlx::query("DELETE FROM sessions WHERE expires_at IS NOT NULL AND expires_at < NOW()")
675            .execute(&self.pool)
676            .await
677            .map_err(|e| {
678                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
679                    "Failed to cleanup expired sessions: {}",
680                    e
681                )))
682            })?;
683
684        // Remove expired kv entries
685        sqlx::query("DELETE FROM kv_store WHERE expires_at IS NOT NULL AND expires_at < NOW()")
686            .execute(&self.pool)
687            .await
688            .map_err(|e| {
689                AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
690                    "Failed to cleanup expired kv: {}",
691                    e
692                )))
693            })?;
694
695        Ok(())
696    }
697
698    async fn list_user_sessions(&self, user_id: &str) -> Result<Vec<SessionData>> {
699        let rows = sqlx::query(
700            r#"
701            SELECT session_id, user_id, data, expires_at, created_at, last_activity, ip_address, user_agent
702            FROM sessions
703            WHERE user_id = $1 AND (expires_at IS NULL OR expires_at > NOW())
704            "#,
705        )
706        .bind(user_id)
707        .fetch_all(&self.pool)
708        .await
709        .map_err(|e| {
710            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
711                "Failed to list user sessions: {}",
712                e
713            )))
714        })?;
715
716        let sessions = rows
717            .into_iter()
718            .map(|row| SessionData {
719                session_id: row.try_get("session_id").unwrap_or_default(),
720                user_id: row.try_get("user_id").unwrap_or_default(),
721                created_at: row.try_get("created_at").unwrap_or_default(),
722                data: {
723                    let json_value: serde_json::Value = row.try_get("data").unwrap_or_default();
724                    if let serde_json::Value::Object(map) = json_value {
725                        map.into_iter().collect()
726                    } else {
727                        std::collections::HashMap::new()
728                    }
729                },
730                expires_at: row.try_get("expires_at").unwrap_or_default(),
731                last_activity: row.try_get("last_activity").unwrap_or_default(),
732                ip_address: row.try_get("ip_address").ok(),
733                user_agent: row.try_get("user_agent").ok(),
734            })
735            .collect();
736
737        Ok(sessions)
738    }
739
740    async fn count_active_sessions(&self) -> Result<u64> {
741        let row = sqlx::query(
742            "SELECT COUNT(*) as count FROM sessions WHERE expires_at IS NULL OR expires_at > NOW()",
743        )
744        .fetch_one(&self.pool)
745        .await
746        .map_err(|e| {
747            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
748                "Failed to count active sessions: {}",
749                e
750            )))
751        })?;
752
753        let count: i64 = row.try_get("count").map_err(|e| {
754            AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
755                "Failed to parse session count: {}",
756                e
757            )))
758        })?;
759
760        Ok(count as u64)
761    }
762}