koprogo_api/infrastructure/database/repositories/
two_factor_repository_impl.rs

1use crate::application::ports::TwoFactorRepository;
2use crate::domain::entities::TwoFactorSecret;
3use async_trait::async_trait;
4use chrono::Utc;
5use sqlx::PgPool;
6use uuid::Uuid;
7
8/// PostgreSQL implementation of TwoFactorRepository
9pub struct PostgresTwoFactorRepository {
10    pool: PgPool,
11}
12
13impl PostgresTwoFactorRepository {
14    pub fn new(pool: PgPool) -> Self {
15        Self { pool }
16    }
17}
18
19#[async_trait]
20impl TwoFactorRepository for PostgresTwoFactorRepository {
21    async fn create(&self, secret: &TwoFactorSecret) -> Result<TwoFactorSecret, String> {
22        sqlx::query_as!(
23            TwoFactorSecretRow,
24            r#"
25            INSERT INTO two_factor_secrets (
26                id, user_id, secret_encrypted, backup_codes_encrypted,
27                is_enabled, verified_at, last_used_at, created_at, updated_at
28            )
29            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
30            RETURNING
31                id, user_id, secret_encrypted, backup_codes_encrypted,
32                is_enabled, verified_at, last_used_at, created_at, updated_at
33            "#,
34            secret.id,
35            secret.user_id,
36            secret.secret_encrypted,
37            &secret.backup_codes_encrypted,
38            secret.is_enabled,
39            secret.verified_at,
40            secret.last_used_at,
41            secret.created_at,
42            secret.updated_at
43        )
44        .fetch_one(&self.pool)
45        .await
46        .map_err(|e| format!("Failed to create two factor secret: {}", e))?
47        .try_into()
48    }
49
50    async fn find_by_user_id(&self, user_id: Uuid) -> Result<Option<TwoFactorSecret>, String> {
51        let result = sqlx::query_as!(
52            TwoFactorSecretRow,
53            r#"
54            SELECT
55                id, user_id, secret_encrypted, backup_codes_encrypted,
56                is_enabled, verified_at, last_used_at, created_at, updated_at
57            FROM two_factor_secrets
58            WHERE user_id = $1
59            "#,
60            user_id
61        )
62        .fetch_optional(&self.pool)
63        .await
64        .map_err(|e| format!("Failed to find two factor secret by user_id: {}", e))?;
65
66        match result {
67            Some(row) => Ok(Some(row.try_into()?)),
68            None => Ok(None),
69        }
70    }
71
72    async fn update(&self, secret: &TwoFactorSecret) -> Result<TwoFactorSecret, String> {
73        let updated_at = Utc::now();
74
75        sqlx::query_as!(
76            TwoFactorSecretRow,
77            r#"
78            UPDATE two_factor_secrets
79            SET
80                secret_encrypted = $2,
81                backup_codes_encrypted = $3,
82                is_enabled = $4,
83                verified_at = $5,
84                last_used_at = $6,
85                updated_at = $7
86            WHERE id = $1
87            RETURNING
88                id, user_id, secret_encrypted, backup_codes_encrypted,
89                is_enabled, verified_at, last_used_at, created_at, updated_at
90            "#,
91            secret.id,
92            secret.secret_encrypted,
93            &secret.backup_codes_encrypted,
94            secret.is_enabled,
95            secret.verified_at,
96            secret.last_used_at,
97            updated_at
98        )
99        .fetch_one(&self.pool)
100        .await
101        .map_err(|e| format!("Failed to update two factor secret: {}", e))?
102        .try_into()
103    }
104
105    async fn delete(&self, user_id: Uuid) -> Result<(), String> {
106        let result = sqlx::query!(
107            r#"
108            DELETE FROM two_factor_secrets
109            WHERE user_id = $1
110            "#,
111            user_id
112        )
113        .execute(&self.pool)
114        .await
115        .map_err(|e| format!("Failed to delete two factor secret: {}", e))?;
116
117        if result.rows_affected() == 0 {
118            return Err(format!("Two factor secret not found for user {}", user_id));
119        }
120
121        Ok(())
122    }
123
124    async fn find_needing_reverification(&self) -> Result<Vec<TwoFactorSecret>, String> {
125        sqlx::query_as!(
126            TwoFactorSecretRow,
127            r#"
128            SELECT
129                id, user_id, secret_encrypted, backup_codes_encrypted,
130                is_enabled, verified_at, last_used_at, created_at, updated_at
131            FROM two_factor_secrets
132            WHERE is_enabled = true
133              AND last_used_at < NOW() - INTERVAL '90 days'
134            ORDER BY last_used_at ASC
135            "#
136        )
137        .fetch_all(&self.pool)
138        .await
139        .map_err(|e| format!("Failed to find secrets needing reverification: {}", e))?
140        .into_iter()
141        .map(|row| row.try_into())
142        .collect()
143    }
144
145    async fn find_with_low_backup_codes(&self) -> Result<Vec<TwoFactorSecret>, String> {
146        sqlx::query_as!(
147            TwoFactorSecretRow,
148            r#"
149            SELECT
150                id, user_id, secret_encrypted, backup_codes_encrypted,
151                is_enabled, verified_at, last_used_at, created_at, updated_at
152            FROM two_factor_secrets
153            WHERE is_enabled = true
154              AND array_length(backup_codes_encrypted, 1) < 3
155            ORDER BY array_length(backup_codes_encrypted, 1) ASC
156            "#
157        )
158        .fetch_all(&self.pool)
159        .await
160        .map_err(|e| format!("Failed to find secrets with low backup codes: {}", e))?
161        .into_iter()
162        .map(|row| row.try_into())
163        .collect()
164    }
165}
166
167// ========================================
168// Database row mapping
169// ========================================
170
171/// SQLx row struct for two_factor_secrets table
172#[derive(Debug)]
173struct TwoFactorSecretRow {
174    id: Uuid,
175    user_id: Uuid,
176    secret_encrypted: String,
177    backup_codes_encrypted: Vec<String>,
178    is_enabled: bool,
179    verified_at: Option<chrono::DateTime<Utc>>,
180    last_used_at: Option<chrono::DateTime<Utc>>,
181    created_at: chrono::DateTime<Utc>,
182    updated_at: chrono::DateTime<Utc>,
183}
184
185impl TryFrom<TwoFactorSecretRow> for TwoFactorSecret {
186    type Error = String;
187
188    fn try_from(row: TwoFactorSecretRow) -> Result<Self, Self::Error> {
189        Ok(Self {
190            id: row.id,
191            user_id: row.user_id,
192            secret_encrypted: row.secret_encrypted,
193            backup_codes_encrypted: row.backup_codes_encrypted,
194            is_enabled: row.is_enabled,
195            verified_at: row.verified_at,
196            last_used_at: row.last_used_at,
197            created_at: row.created_at,
198            updated_at: row.updated_at,
199        })
200    }
201}
202
203#[cfg(test)]
204mod tests {
205    use super::*;
206    use crate::domain::entities::TwoFactorSecret;
207    use testcontainers_modules::{postgres::Postgres, testcontainers::runners::AsyncRunner};
208
209    async fn setup_test_db() -> PgPool {
210        let container = Postgres::default().start().await.unwrap();
211        let host_port = container.get_host_port_ipv4(5432).await.unwrap();
212        let connection_string = format!(
213            "postgres://postgres:postgres@127.0.0.1:{}/postgres",
214            host_port
215        );
216
217        let pool = PgPool::connect(&connection_string).await.unwrap();
218
219        // Run migrations
220        sqlx::migrate!("./migrations").run(&pool).await.unwrap();
221
222        pool
223    }
224
225    #[tokio::test]
226    #[ignore] // Integration test - requires database
227    async fn test_create_two_factor_secret() {
228        let pool = setup_test_db().await;
229        let repo = PostgresTwoFactorRepository::new(pool.clone());
230
231        // Create test user first
232        let user_id = Uuid::new_v4();
233        sqlx::query(
234            r#"
235            INSERT INTO users (id, email, first_name, last_name, password_hash, is_active, created_at, updated_at)
236            VALUES ($1, $2, 'Test', 'User', 'hash', true, NOW(), NOW())
237            "#
238        )
239        .bind(user_id)
240        .bind(format!("test-{}@example.com", user_id))
241        .execute(&pool)
242        .await
243        .unwrap();
244
245        let secret = TwoFactorSecret::new(user_id, "encrypted_secret".to_string())
246            .unwrap()
247            .with_backup_codes(vec![
248                "code1".to_string(),
249                "code2".to_string(),
250                "code3".to_string(),
251            ])
252            .unwrap();
253
254        let created = repo.create(&secret).await.unwrap();
255
256        assert_eq!(created.user_id, user_id);
257        assert_eq!(created.secret_encrypted, "encrypted_secret");
258        assert_eq!(created.backup_codes_encrypted.len(), 3);
259        assert!(!created.is_enabled);
260    }
261
262    #[tokio::test]
263    #[ignore] // Integration test - requires database
264    async fn test_find_by_user_id() {
265        let pool = setup_test_db().await;
266        let repo = PostgresTwoFactorRepository::new(pool.clone());
267
268        // Create test user
269        let user_id = Uuid::new_v4();
270        sqlx::query(
271            r#"
272            INSERT INTO users (id, email, first_name, last_name, password_hash, is_active, created_at, updated_at)
273            VALUES ($1, $2, 'Test', 'User', 'hash', true, NOW(), NOW())
274            "#
275        )
276        .bind(user_id)
277        .bind(format!("test-{}@example.com", user_id))
278        .execute(&pool)
279        .await
280        .unwrap();
281
282        let secret = TwoFactorSecret::new(user_id, "encrypted_secret".to_string()).unwrap();
283
284        repo.create(&secret).await.unwrap();
285
286        let found = repo.find_by_user_id(user_id).await.unwrap();
287        assert!(found.is_some());
288        assert_eq!(found.unwrap().user_id, user_id);
289    }
290
291    #[tokio::test]
292    #[ignore] // Integration test - requires database
293    async fn test_update_two_factor_secret() {
294        let pool = setup_test_db().await;
295        let repo = PostgresTwoFactorRepository::new(pool.clone());
296
297        // Create test user
298        let user_id = Uuid::new_v4();
299        sqlx::query(
300            r#"
301            INSERT INTO users (id, email, first_name, last_name, password_hash, is_active, created_at, updated_at)
302            VALUES ($1, $2, 'Test', 'User', 'hash', true, NOW(), NOW())
303            "#
304        )
305        .bind(user_id)
306        .bind(format!("test-{}@example.com", user_id))
307        .execute(&pool)
308        .await
309        .unwrap();
310
311        let mut secret = TwoFactorSecret::new(user_id, "encrypted_secret".to_string()).unwrap();
312
313        let created = repo.create(&secret).await.unwrap();
314
315        secret = created;
316        secret.enable().unwrap();
317
318        let updated = repo.update(&secret).await.unwrap();
319        assert!(updated.is_enabled);
320        assert!(updated.verified_at.is_some());
321    }
322
323    #[tokio::test]
324    #[ignore] // Integration test - requires database
325    async fn test_delete_two_factor_secret() {
326        let pool = setup_test_db().await;
327        let repo = PostgresTwoFactorRepository::new(pool.clone());
328
329        // Create test user
330        let user_id = Uuid::new_v4();
331        sqlx::query(
332            r#"
333            INSERT INTO users (id, email, first_name, last_name, password_hash, is_active, created_at, updated_at)
334            VALUES ($1, $2, 'Test', 'User', 'hash', true, NOW(), NOW())
335            "#
336        )
337        .bind(user_id)
338        .bind(format!("test-{}@example.com", user_id))
339        .execute(&pool)
340        .await
341        .unwrap();
342
343        let secret = TwoFactorSecret::new(user_id, "encrypted_secret".to_string()).unwrap();
344
345        repo.create(&secret).await.unwrap();
346        repo.delete(user_id).await.unwrap();
347
348        let found = repo.find_by_user_id(user_id).await.unwrap();
349        assert!(found.is_none());
350    }
351
352    #[tokio::test]
353    #[ignore] // Integration test - requires database
354    async fn test_find_needing_reverification() {
355        let pool = setup_test_db().await;
356        let repo = PostgresTwoFactorRepository::new(pool.clone());
357
358        // Create test user
359        let user_id = Uuid::new_v4();
360        sqlx::query(
361            r#"
362            INSERT INTO users (id, email, first_name, last_name, password_hash, is_active, created_at, updated_at)
363            VALUES ($1, $2, 'Test', 'User', 'hash', true, NOW(), NOW())
364            "#
365        )
366        .bind(user_id)
367        .bind(format!("test-{}@example.com", user_id))
368        .execute(&pool)
369        .await
370        .unwrap();
371
372        let mut secret = TwoFactorSecret::new(user_id, "encrypted_secret".to_string()).unwrap();
373
374        secret.enable().unwrap();
375        let created = repo.create(&secret).await.unwrap();
376
377        // Manually set last_used_at to 91 days ago
378        sqlx::query(
379            "UPDATE two_factor_secrets SET last_used_at = NOW() - INTERVAL '91 days' WHERE id = $1",
380        )
381        .bind(created.id)
382        .execute(&pool)
383        .await
384        .unwrap();
385
386        let needing_reverification = repo.find_needing_reverification().await.unwrap();
387        assert_eq!(needing_reverification.len(), 1);
388        assert_eq!(needing_reverification[0].user_id, user_id);
389    }
390
391    #[tokio::test]
392    #[ignore] // Integration test - requires database
393    async fn test_find_with_low_backup_codes() {
394        let pool = setup_test_db().await;
395        let repo = PostgresTwoFactorRepository::new(pool.clone());
396
397        // Create test user
398        let user_id = Uuid::new_v4();
399        sqlx::query(
400            r#"
401            INSERT INTO users (id, email, first_name, last_name, password_hash, is_active, created_at, updated_at)
402            VALUES ($1, $2, 'Test', 'User', 'hash', true, NOW(), NOW())
403            "#
404        )
405        .bind(user_id)
406        .bind(format!("test-{}@example.com", user_id))
407        .execute(&pool)
408        .await
409        .unwrap();
410
411        let mut secret = TwoFactorSecret::new(user_id, "encrypted_secret".to_string())
412            .unwrap()
413            .with_backup_codes(vec!["code1".to_string(), "code2".to_string()])
414            .unwrap();
415
416        secret.enable().unwrap();
417        repo.create(&secret).await.unwrap();
418
419        let low_codes = repo.find_with_low_backup_codes().await.unwrap();
420        assert_eq!(low_codes.len(), 1);
421        assert_eq!(low_codes[0].user_id, user_id);
422    }
423}
koprogo_api/infrastructure/database/repositories/two_factor_repository_impl.rs

koprogo_api/infrastructure/database/repositories/
two_factor_repository_impl.rs
