zopp_store_sqlite/
lib.rs

1use chrono::{DateTime, Utc};
2use sqlx::{sqlite::SqlitePoolOptions, SqlitePool};
3use uuid::Uuid;
4use zopp_storage::{
5    AddWorkspacePrincipalParams, CreateEnvParams, CreateInviteParams, CreatePrincipalParams,
6    CreateProjectParams, CreateUserParams, CreateWorkspaceParams, EnvName, Environment,
7    EnvironmentId, Invite, InviteId, Principal, PrincipalId, ProjectName, SecretRow, Store,
8    StoreError, User, UserId, Workspace, WorkspaceId, WorkspacePrincipal,
9};
10
11static MIGRATOR: sqlx::migrate::Migrator = sqlx::migrate!("./migrations");
12
13pub struct SqliteStore {
14    pool: SqlitePool,
15}
16
17impl SqliteStore {
18    /// `~/.zopp/store.db` (creates dir with 0700 perms on unix)
19    pub async fn open_default() -> Result<Self, StoreError> {
20        let dir = dirs::home_dir()
21            .ok_or_else(|| StoreError::Backend("no home dir".into()))?
22            .join(".zopp");
23        std::fs::create_dir_all(&dir).map_err(|e| StoreError::Backend(e.to_string()))?;
24        #[cfg(unix)]
25        {
26            use std::os::unix::fs::PermissionsExt;
27            std::fs::set_permissions(&dir, std::fs::Permissions::from_mode(0o700))
28                .map_err(|e| StoreError::Backend(e.to_string()))?;
29        }
30        let path = dir.join("store.db");
31        let url = format!("sqlite://{}", path.to_string_lossy());
32        Self::open(&url).await
33    }
34
35    pub async fn open_in_memory() -> Result<Self, StoreError> {
36        Self::open("sqlite::memory:").await
37    }
38
39    pub async fn open(url: &str) -> Result<Self, StoreError> {
40        let pool = SqlitePoolOptions::new()
41            .max_connections(1)
42            .connect(url)
43            .await
44            .map_err(|e| StoreError::Backend(e.to_string()))?;
45
46        MIGRATOR
47            .run(&pool)
48            .await
49            .map_err(|e| StoreError::Backend(e.to_string()))?;
50
51        Ok(Self { pool })
52    }
53}
54
55#[async_trait::async_trait]
56impl Store for SqliteStore {
57    // ───────────────────────────── Users ─────────────────────────────
58
59    async fn create_user(
60        &self,
61        params: &CreateUserParams,
62    ) -> Result<(UserId, Option<PrincipalId>), StoreError> {
63        // Use transaction if we need to create principal or add to workspaces
64        let needs_tx = params.principal.is_some() || !params.workspace_ids.is_empty();
65
66        if needs_tx {
67            let mut tx = self
68                .pool
69                .begin()
70                .await
71                .map_err(|e| StoreError::Backend(e.to_string()))?;
72
73            // First try to get existing user
74            let existing_user = sqlx::query!("SELECT id FROM users WHERE email = ?", params.email)
75                .fetch_optional(&mut *tx)
76                .await
77                .map_err(|e| StoreError::Backend(e.to_string()))?;
78
79            let actual_user_id_str = if let Some(existing) = existing_user {
80                // User already exists, use their ID
81                existing.id
82            } else {
83                // Create new user
84                let user_id = Uuid::now_v7();
85                let user_id_str = user_id.to_string();
86                sqlx::query!(
87                    "INSERT INTO users(id, email) VALUES(?, ?)",
88                    user_id_str,
89                    params.email
90                )
91                .execute(&mut *tx)
92                .await
93                .map_err(|e| StoreError::Backend(e.to_string()))?;
94                user_id_str
95            };
96
97            // Create principal if provided
98            let principal_id = if let Some(principal_data) = &params.principal {
99                let principal_id = Uuid::now_v7();
100                let principal_id_str = principal_id.to_string();
101
102                // Service principals have NULL user_id
103                let user_id_for_principal = if principal_data.is_service {
104                    None
105                } else {
106                    Some(actual_user_id_str.clone())
107                };
108
109                sqlx::query!(
110                    "INSERT INTO principals(id, user_id, name, public_key, x25519_public_key) VALUES(?, ?, ?, ?, ?)",
111                    principal_id_str,
112                    user_id_for_principal,
113                    principal_data.name,
114                    principal_data.public_key,
115                    principal_data.x25519_public_key
116                )
117                .execute(&mut *tx)
118                .await
119                .map_err(|e| StoreError::Backend(e.to_string()))?;
120
121                Some(PrincipalId(principal_id))
122            } else {
123                None
124            };
125
126            // Add user to workspaces (user-level membership)
127            for workspace_id in &params.workspace_ids {
128                let ws_id = workspace_id.0.to_string();
129                sqlx::query!(
130                    "INSERT INTO workspace_members(workspace_id, user_id) VALUES(?, ?)",
131                    ws_id,
132                    actual_user_id_str
133                )
134                .execute(&mut *tx)
135                .await
136                .map_err(|e| StoreError::Backend(e.to_string()))?;
137            }
138
139            tx.commit()
140                .await
141                .map_err(|e| StoreError::Backend(e.to_string()))?;
142
143            // Parse the actual user_id
144            let actual_user_id = Uuid::parse_str(&actual_user_id_str)
145                .map_err(|e| StoreError::Backend(e.to_string()))?;
146
147            Ok((UserId(actual_user_id), principal_id))
148        } else {
149            // Simple case: just create user
150            let user_id = Uuid::now_v7();
151            let user_id_str = user_id.to_string();
152
153            sqlx::query!(
154                "INSERT INTO users(id, email) VALUES(?, ?)",
155                user_id_str,
156                params.email
157            )
158            .execute(&self.pool)
159            .await
160            .map_err(|e| {
161                let s = e.to_string();
162                if s.contains("UNIQUE") {
163                    StoreError::AlreadyExists
164                } else {
165                    StoreError::Backend(s)
166                }
167            })?;
168
169            Ok((UserId(user_id), None))
170        }
171    }
172
173    async fn get_user_by_email(&self, email: &str) -> Result<User, StoreError> {
174        let row = sqlx::query!(
175            r#"SELECT id, email,
176               created_at as "created_at: DateTime<Utc>",
177               updated_at as "updated_at: DateTime<Utc>"
178               FROM users WHERE email = ?"#,
179            email
180        )
181        .fetch_optional(&self.pool)
182        .await
183        .map_err(|e| StoreError::Backend(e.to_string()))?;
184
185        match row {
186            None => Err(StoreError::NotFound),
187            Some(row) => {
188                let id =
189                    Uuid::try_parse(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?;
190                Ok(User {
191                    id: UserId(id),
192                    email: row.email,
193                    created_at: row.created_at,
194                    updated_at: row.updated_at,
195                })
196            }
197        }
198    }
199
200    async fn get_user_by_id(&self, user_id: &UserId) -> Result<User, StoreError> {
201        let user_id_str = user_id.0.to_string();
202        let row = sqlx::query!(
203            r#"SELECT id, email,
204               created_at as "created_at: DateTime<Utc>",
205               updated_at as "updated_at: DateTime<Utc>"
206               FROM users WHERE id = ?"#,
207            user_id_str
208        )
209        .fetch_optional(&self.pool)
210        .await
211        .map_err(|e| StoreError::Backend(e.to_string()))?;
212
213        match row {
214            None => Err(StoreError::NotFound),
215            Some(row) => {
216                let id =
217                    Uuid::try_parse(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?;
218                Ok(User {
219                    id: UserId(id),
220                    email: row.email,
221                    created_at: row.created_at,
222                    updated_at: row.updated_at,
223                })
224            }
225        }
226    }
227
228    // ───────────────────────────── Principals ─────────────────────────────
229
230    async fn create_principal(
231        &self,
232        params: &CreatePrincipalParams,
233    ) -> Result<PrincipalId, StoreError> {
234        let principal_id = Uuid::now_v7();
235        let principal_id_str = principal_id.to_string();
236        let user_id_str = params.user_id.as_ref().map(|id| id.0.to_string());
237
238        sqlx::query!(
239            "INSERT INTO principals(id, user_id, name, public_key, x25519_public_key) VALUES(?, ?, ?, ?, ?)",
240            principal_id_str,
241            user_id_str,
242            params.name,
243            params.public_key,
244            params.x25519_public_key
245        )
246        .execute(&self.pool)
247        .await
248        .map_err(|e| {
249            let s = e.to_string();
250            if s.contains("UNIQUE") {
251                StoreError::AlreadyExists
252            } else {
253                StoreError::Backend(s)
254            }
255        })?;
256        Ok(PrincipalId(principal_id))
257    }
258
259    async fn get_principal(&self, principal_id: &PrincipalId) -> Result<Principal, StoreError> {
260        let principal_id_str = principal_id.0.to_string();
261        let row = sqlx::query!(
262            r#"SELECT id, user_id, name, public_key, x25519_public_key,
263               created_at as "created_at: DateTime<Utc>",
264               updated_at as "updated_at: DateTime<Utc>"
265               FROM principals WHERE id = ?"#,
266            principal_id_str
267        )
268        .fetch_optional(&self.pool)
269        .await
270        .map_err(|e| StoreError::Backend(e.to_string()))?;
271
272        match row {
273            None => Err(StoreError::NotFound),
274            Some(row) => {
275                let id =
276                    Uuid::try_parse(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?;
277                let user_id = row
278                    .user_id
279                    .as_ref()
280                    .map(|id| Uuid::try_parse(id).map(UserId))
281                    .transpose()
282                    .map_err(|e| StoreError::Backend(e.to_string()))?;
283                Ok(Principal {
284                    id: PrincipalId(id),
285                    user_id,
286                    name: row.name,
287                    public_key: row.public_key,
288                    x25519_public_key: row.x25519_public_key,
289                    created_at: row.created_at,
290                    updated_at: row.updated_at,
291                })
292            }
293        }
294    }
295
296    async fn rename_principal(
297        &self,
298        principal_id: &PrincipalId,
299        new_name: &str,
300    ) -> Result<(), StoreError> {
301        let principal_id_str = principal_id.0.to_string();
302        let result = sqlx::query!(
303            "UPDATE principals SET name = ? WHERE id = ?",
304            new_name,
305            principal_id_str
306        )
307        .execute(&self.pool)
308        .await
309        .map_err(|e| StoreError::Backend(e.to_string()))?;
310
311        if result.rows_affected() == 0 {
312            Err(StoreError::NotFound)
313        } else {
314            Ok(())
315        }
316    }
317
318    async fn list_principals(&self, user_id: &UserId) -> Result<Vec<Principal>, StoreError> {
319        let user_id_str = user_id.0.to_string();
320        let rows = sqlx::query!(
321            r#"SELECT id, user_id, name, public_key, x25519_public_key,
322               created_at as "created_at: DateTime<Utc>",
323               updated_at as "updated_at: DateTime<Utc>"
324               FROM principals WHERE user_id = ?"#,
325            user_id_str
326        )
327        .fetch_all(&self.pool)
328        .await
329        .map_err(|e| StoreError::Backend(e.to_string()))?;
330
331        let mut principals = Vec::with_capacity(rows.len());
332        for row in rows {
333            let id = Uuid::try_parse(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?;
334            let user_id = row
335                .user_id
336                .as_ref()
337                .map(|id| Uuid::try_parse(id).map(UserId))
338                .transpose()
339                .map_err(|e| StoreError::Backend(e.to_string()))?;
340            principals.push(Principal {
341                id: PrincipalId(id),
342                user_id,
343                name: row.name,
344                public_key: row.public_key,
345                x25519_public_key: row.x25519_public_key,
346                created_at: row.created_at,
347                updated_at: row.updated_at,
348            });
349        }
350        Ok(principals)
351    }
352
353    // ───────────────────────────── Invites ─────────────────────────────
354
355    async fn create_invite(&self, params: &CreateInviteParams) -> Result<Invite, StoreError> {
356        let invite_id = Uuid::now_v7();
357        let invite_id_str = invite_id.to_string();
358
359        let created_by_user_id_str = params
360            .created_by_user_id
361            .as_ref()
362            .map(|id| id.0.to_string());
363
364        sqlx::query!(
365            "INSERT INTO invites(id, token, expires_at, created_by_user_id, kek_encrypted, kek_nonce) VALUES(?, ?, ?, ?, ?, ?)",
366            invite_id_str,
367            params.token,
368            params.expires_at,
369            created_by_user_id_str,
370            params.kek_encrypted,
371            params.kek_nonce
372        )
373        .execute(&self.pool)
374        .await
375        .map_err(|e| StoreError::Backend(e.to_string()))?;
376
377        // Insert workspace associations
378        for ws_id in &params.workspace_ids {
379            let ws_id_str = ws_id.0.to_string();
380            sqlx::query!(
381                "INSERT INTO invite_workspaces(invite_id, workspace_id) VALUES(?, ?)",
382                invite_id_str,
383                ws_id_str
384            )
385            .execute(&self.pool)
386            .await
387            .map_err(|e| StoreError::Backend(e.to_string()))?;
388        }
389
390        // Fetch the created invite to get the database-set timestamps
391        let row = sqlx::query!(
392            r#"SELECT created_at as "created_at: DateTime<Utc>",
393               updated_at as "updated_at: DateTime<Utc>"
394               FROM invites WHERE id = ?"#,
395            invite_id_str
396        )
397        .fetch_one(&self.pool)
398        .await
399        .map_err(|e| StoreError::Backend(e.to_string()))?;
400
401        Ok(Invite {
402            id: InviteId(invite_id),
403            token: params.token.clone(),
404            workspace_ids: params.workspace_ids.clone(),
405            kek_encrypted: params.kek_encrypted.clone(),
406            kek_nonce: params.kek_nonce.clone(),
407            created_at: row.created_at,
408            updated_at: row.updated_at,
409            expires_at: params.expires_at,
410            created_by_user_id: params.created_by_user_id.clone(),
411        })
412    }
413
414    async fn get_invite_by_token(&self, token: &str) -> Result<Invite, StoreError> {
415        let row = sqlx::query!(
416            r#"SELECT id, token,
417               created_at as "created_at: DateTime<Utc>",
418               updated_at as "updated_at: DateTime<Utc>",
419               expires_at as "expires_at: DateTime<Utc>",
420               created_by_user_id, revoked, kek_encrypted, kek_nonce
421               FROM invites WHERE token = ?"#,
422            token
423        )
424        .fetch_optional(&self.pool)
425        .await
426        .map_err(|e| StoreError::Backend(e.to_string()))?;
427
428        match row {
429            None => Err(StoreError::NotFound),
430            Some(row) => {
431                if row.revoked != 0 {
432                    return Err(StoreError::NotFound); // Treat revoked as not found
433                }
434
435                let id =
436                    Uuid::try_parse(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?;
437                let created_by_user_id = row
438                    .created_by_user_id
439                    .as_ref()
440                    .map(|id| Uuid::try_parse(id).map(UserId))
441                    .transpose()
442                    .map_err(|e| StoreError::Backend(e.to_string()))?;
443
444                // Get workspace IDs
445                let invite_id_str = row.id;
446                let ws_rows = sqlx::query!(
447                    "SELECT workspace_id FROM invite_workspaces WHERE invite_id = ?",
448                    invite_id_str
449                )
450                .fetch_all(&self.pool)
451                .await
452                .map_err(|e| StoreError::Backend(e.to_string()))?;
453
454                let mut workspace_ids = Vec::new();
455                for ws_row in ws_rows {
456                    let ws_id = Uuid::try_parse(&ws_row.workspace_id)
457                        .map_err(|e| StoreError::Backend(e.to_string()))?;
458                    workspace_ids.push(WorkspaceId(ws_id));
459                }
460
461                Ok(Invite {
462                    id: InviteId(id),
463                    token: row.token,
464                    workspace_ids,
465                    kek_encrypted: row.kek_encrypted,
466                    kek_nonce: row.kek_nonce,
467                    created_at: row.created_at,
468                    updated_at: row.updated_at,
469                    expires_at: row.expires_at,
470                    created_by_user_id,
471                })
472            }
473        }
474    }
475
476    async fn list_invites(&self, user_id: Option<&UserId>) -> Result<Vec<Invite>, StoreError> {
477        let user_id_str = user_id.map(|id| id.0.to_string());
478
479        // Query for either user-created invites or server invites (where created_by_user_id IS NULL)
480        let rows = sqlx::query!(
481            r#"SELECT id, token,
482               created_at as "created_at: DateTime<Utc>",
483               updated_at as "updated_at: DateTime<Utc>",
484               expires_at as "expires_at: DateTime<Utc>",
485               created_by_user_id, revoked, kek_encrypted, kek_nonce
486               FROM invites
487               WHERE revoked = 0 AND (
488                   (? IS NOT NULL AND created_by_user_id = ?) OR
489                   (? IS NULL AND created_by_user_id IS NULL)
490               )"#,
491            user_id_str,
492            user_id_str,
493            user_id_str
494        )
495        .fetch_all(&self.pool)
496        .await
497        .map_err(|e| StoreError::Backend(e.to_string()))?;
498
499        let mut invites = Vec::new();
500        for row in rows {
501            let id = Uuid::try_parse(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?;
502            let created_by_user_id = row
503                .created_by_user_id
504                .as_ref()
505                .map(|id| Uuid::try_parse(id).map(UserId))
506                .transpose()
507                .map_err(|e| StoreError::Backend(e.to_string()))?;
508
509            // Get workspace IDs
510            let ws_rows = sqlx::query!(
511                "SELECT workspace_id FROM invite_workspaces WHERE invite_id = ?",
512                row.id
513            )
514            .fetch_all(&self.pool)
515            .await
516            .map_err(|e| StoreError::Backend(e.to_string()))?;
517
518            let mut workspace_ids = Vec::new();
519            for ws_row in ws_rows {
520                let ws_id = Uuid::try_parse(&ws_row.workspace_id)
521                    .map_err(|e| StoreError::Backend(e.to_string()))?;
522                workspace_ids.push(WorkspaceId(ws_id));
523            }
524
525            invites.push(Invite {
526                id: InviteId(id),
527                token: row.token,
528                workspace_ids,
529                kek_encrypted: row.kek_encrypted,
530                kek_nonce: row.kek_nonce,
531                created_at: row.created_at,
532                updated_at: row.updated_at,
533                expires_at: row.expires_at,
534                created_by_user_id,
535            });
536        }
537        Ok(invites)
538    }
539
540    async fn revoke_invite(&self, invite_id: &InviteId) -> Result<(), StoreError> {
541        let invite_id_str = invite_id.0.to_string();
542        let result = sqlx::query!("UPDATE invites SET revoked = 1 WHERE id = ?", invite_id_str)
543            .execute(&self.pool)
544            .await
545            .map_err(|e| StoreError::Backend(e.to_string()))?;
546
547        if result.rows_affected() == 0 {
548            Err(StoreError::NotFound)
549        } else {
550            Ok(())
551        }
552    }
553
554    // ───────────────────────────── Workspaces ─────────────────────────────
555
556    async fn create_workspace(
557        &self,
558        params: &CreateWorkspaceParams,
559    ) -> Result<WorkspaceId, StoreError> {
560        let ws_id_str = params.id.0.to_string();
561        let owner_user_id_str = params.owner_user_id.0.to_string();
562        let m_cost = params.m_cost_kib as i64;
563        let t_cost = params.t_cost as i64;
564        let p_cost = params.p_cost as i64;
565
566        sqlx::query!(
567            "INSERT INTO workspaces(id, name, owner_user_id, kdf_salt, kdf_m_cost_kib, kdf_t_cost, kdf_p_cost)
568             VALUES(?, ?, ?, ?, ?, ?, ?)",
569            ws_id_str,
570            params.name,
571            owner_user_id_str,
572            params.kdf_salt,
573            m_cost,
574            t_cost,
575            p_cost
576        )
577        .execute(&self.pool)
578        .await
579        .map_err(|e| StoreError::Backend(e.to_string()))?;
580        Ok(params.id.clone())
581    }
582
583    async fn list_workspaces(&self, user_id: &UserId) -> Result<Vec<Workspace>, StoreError> {
584        let user_id_str = user_id.0.to_string();
585
586        // Get all workspaces where user is a member
587        let rows = sqlx::query!(
588            r#"SELECT w.id, w.name, w.owner_user_id, w.kdf_salt, w.kdf_m_cost_kib, w.kdf_t_cost, w.kdf_p_cost,
589               w.created_at as "created_at: DateTime<Utc>",
590               w.updated_at as "updated_at: DateTime<Utc>"
591               FROM workspaces w
592               JOIN workspace_members wm ON w.id = wm.workspace_id
593               WHERE wm.user_id = ?"#,
594            user_id_str
595        )
596        .fetch_all(&self.pool)
597        .await
598        .map_err(|e| StoreError::Backend(e.to_string()))?;
599
600        let mut workspaces = Vec::new();
601        for row in rows {
602            let id = Uuid::try_parse(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?;
603            let owner_user_id = Uuid::try_parse(&row.owner_user_id)
604                .map_err(|e| StoreError::Backend(e.to_string()))?;
605            workspaces.push(Workspace {
606                id: WorkspaceId(id),
607                name: row.name,
608                owner_user_id: UserId(owner_user_id),
609                kdf_salt: row.kdf_salt,
610                m_cost_kib: row.kdf_m_cost_kib as u32,
611                t_cost: row.kdf_t_cost as u32,
612                p_cost: row.kdf_p_cost as u32,
613                created_at: row.created_at,
614                updated_at: row.updated_at,
615            });
616        }
617        Ok(workspaces)
618    }
619
620    async fn get_workspace(&self, ws: &WorkspaceId) -> Result<Workspace, StoreError> {
621        let ws_id = ws.0.to_string();
622        let row = sqlx::query!(
623            r#"SELECT id, name, owner_user_id, kdf_salt, kdf_m_cost_kib, kdf_t_cost, kdf_p_cost,
624               created_at as "created_at: DateTime<Utc>",
625               updated_at as "updated_at: DateTime<Utc>"
626               FROM workspaces WHERE id = ?"#,
627            ws_id
628        )
629        .fetch_optional(&self.pool)
630        .await
631        .map_err(|e| StoreError::Backend(e.to_string()))?;
632
633        match row {
634            None => Err(StoreError::NotFound),
635            Some(row) => {
636                let id =
637                    Uuid::try_parse(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?;
638                let owner_user_id = Uuid::try_parse(&row.owner_user_id)
639                    .map_err(|e| StoreError::Backend(e.to_string()))?;
640                Ok(Workspace {
641                    id: WorkspaceId(id),
642                    name: row.name,
643                    owner_user_id: UserId(owner_user_id),
644                    kdf_salt: row.kdf_salt,
645                    m_cost_kib: row.kdf_m_cost_kib as u32,
646                    t_cost: row.kdf_t_cost as u32,
647                    p_cost: row.kdf_p_cost as u32,
648                    created_at: row.created_at,
649                    updated_at: row.updated_at,
650                })
651            }
652        }
653    }
654
655    async fn get_workspace_by_name(
656        &self,
657        user_id: &UserId,
658        name: &str,
659    ) -> Result<Workspace, StoreError> {
660        let user_id_str = user_id.0.to_string();
661        let row = sqlx::query!(
662            r#"SELECT w.id, w.name, w.owner_user_id, w.kdf_salt, w.kdf_m_cost_kib, w.kdf_t_cost, w.kdf_p_cost,
663               w.created_at as "created_at: DateTime<Utc>",
664               w.updated_at as "updated_at: DateTime<Utc>"
665               FROM workspaces w
666               LEFT JOIN workspace_members wm ON w.id = wm.workspace_id
667               WHERE w.name = ? AND (w.owner_user_id = ? OR wm.user_id = ?)
668               LIMIT 1"#,
669            name,
670            user_id_str,
671            user_id_str
672        )
673        .fetch_optional(&self.pool)
674        .await
675        .map_err(|e| StoreError::Backend(e.to_string()))?;
676
677        match row {
678            None => Err(StoreError::NotFound),
679            Some(row) => {
680                let id =
681                    Uuid::try_parse(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?;
682                let owner_user_id = Uuid::try_parse(&row.owner_user_id)
683                    .map_err(|e| StoreError::Backend(e.to_string()))?;
684                Ok(Workspace {
685                    id: WorkspaceId(id),
686                    name: row.name,
687                    owner_user_id: UserId(owner_user_id),
688                    kdf_salt: row.kdf_salt,
689                    m_cost_kib: row.kdf_m_cost_kib as u32,
690                    t_cost: row.kdf_t_cost as u32,
691                    p_cost: row.kdf_p_cost as u32,
692                    created_at: row.created_at,
693                    updated_at: row.updated_at,
694                })
695            }
696        }
697    }
698
699    async fn get_workspace_by_name_for_principal(
700        &self,
701        principal_id: &PrincipalId,
702        name: &str,
703    ) -> Result<Workspace, StoreError> {
704        let principal_id_str = principal_id.0.to_string();
705        let row = sqlx::query!(
706            r#"SELECT w.id, w.name, w.owner_user_id, w.kdf_salt, w.kdf_m_cost_kib, w.kdf_t_cost, w.kdf_p_cost,
707               w.created_at as "created_at: DateTime<Utc>",
708               w.updated_at as "updated_at: DateTime<Utc>"
709               FROM workspaces w
710               INNER JOIN workspace_principals wp ON w.id = wp.workspace_id
711               WHERE w.name = ? AND wp.principal_id = ?
712               LIMIT 1"#,
713            name,
714            principal_id_str
715        )
716        .fetch_optional(&self.pool)
717        .await
718        .map_err(|e| StoreError::Backend(e.to_string()))?;
719
720        match row {
721            None => Err(StoreError::NotFound),
722            Some(row) => {
723                let id =
724                    Uuid::try_parse(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?;
725                let owner_user_id = Uuid::try_parse(&row.owner_user_id)
726                    .map_err(|e| StoreError::Backend(e.to_string()))?;
727                Ok(Workspace {
728                    id: WorkspaceId(id),
729                    name: row.name,
730                    owner_user_id: UserId(owner_user_id),
731                    kdf_salt: row.kdf_salt,
732                    m_cost_kib: row.kdf_m_cost_kib as u32,
733                    t_cost: row.kdf_t_cost as u32,
734                    p_cost: row.kdf_p_cost as u32,
735                    created_at: row.created_at,
736                    updated_at: row.updated_at,
737                })
738            }
739        }
740    }
741
742    async fn add_workspace_principal(
743        &self,
744        params: &AddWorkspacePrincipalParams,
745    ) -> Result<(), StoreError> {
746        let ws_id = params.workspace_id.0.to_string();
747        let p_id = params.principal_id.0.to_string();
748
749        sqlx::query!(
750            "INSERT INTO workspace_principals(workspace_id, principal_id, ephemeral_pub, kek_wrapped, kek_nonce) VALUES(?, ?, ?, ?, ?)",
751            ws_id,
752            p_id,
753            params.ephemeral_pub,
754            params.kek_wrapped,
755            params.kek_nonce
756        )
757        .execute(&self.pool)
758        .await
759        .map_err(|e| match e {
760            sqlx::Error::Database(ref db_err) if db_err.is_unique_violation() => {
761                StoreError::AlreadyExists
762            }
763            _ => StoreError::Backend(e.to_string()),
764        })?;
765        Ok(())
766    }
767
768    async fn get_workspace_principal(
769        &self,
770        workspace_id: &WorkspaceId,
771        principal_id: &PrincipalId,
772    ) -> Result<WorkspacePrincipal, StoreError> {
773        let ws_id = workspace_id.0.to_string();
774        let p_id = principal_id.0.to_string();
775
776        let row = sqlx::query!(
777            r#"SELECT workspace_id, principal_id, ephemeral_pub, kek_wrapped, kek_nonce,
778               created_at as "created_at: DateTime<Utc>"
779               FROM workspace_principals WHERE workspace_id = ? AND principal_id = ?"#,
780            ws_id,
781            p_id
782        )
783        .fetch_optional(&self.pool)
784        .await
785        .map_err(|e| StoreError::Backend(e.to_string()))?;
786
787        match row {
788            None => Err(StoreError::NotFound),
789            Some(row) => {
790                let workspace_id = Uuid::try_parse(&row.workspace_id)
791                    .map_err(|e| StoreError::Backend(e.to_string()))?;
792                let principal_id = Uuid::try_parse(&row.principal_id)
793                    .map_err(|e| StoreError::Backend(e.to_string()))?;
794                Ok(WorkspacePrincipal {
795                    workspace_id: WorkspaceId(workspace_id),
796                    principal_id: PrincipalId(principal_id),
797                    ephemeral_pub: row.ephemeral_pub,
798                    kek_wrapped: row.kek_wrapped,
799                    kek_nonce: row.kek_nonce,
800                    created_at: row.created_at,
801                })
802            }
803        }
804    }
805
806    async fn list_workspace_principals(
807        &self,
808        workspace_id: &WorkspaceId,
809    ) -> Result<Vec<WorkspacePrincipal>, StoreError> {
810        let ws_id = workspace_id.0.to_string();
811
812        let rows = sqlx::query!(
813            r#"SELECT workspace_id, principal_id, ephemeral_pub, kek_wrapped, kek_nonce,
814               created_at as "created_at: DateTime<Utc>"
815               FROM workspace_principals WHERE workspace_id = ?"#,
816            ws_id
817        )
818        .fetch_all(&self.pool)
819        .await
820        .map_err(|e| StoreError::Backend(e.to_string()))?;
821
822        let mut principals = Vec::with_capacity(rows.len());
823        for row in rows {
824            let workspace_id = Uuid::try_parse(&row.workspace_id)
825                .map_err(|e| StoreError::Backend(e.to_string()))?;
826            let principal_id = Uuid::try_parse(&row.principal_id)
827                .map_err(|e| StoreError::Backend(e.to_string()))?;
828            principals.push(WorkspacePrincipal {
829                workspace_id: WorkspaceId(workspace_id),
830                principal_id: PrincipalId(principal_id),
831                ephemeral_pub: row.ephemeral_pub,
832                kek_wrapped: row.kek_wrapped,
833                kek_nonce: row.kek_nonce,
834                created_at: row.created_at,
835            });
836        }
837        Ok(principals)
838    }
839
840    async fn add_user_to_workspace(
841        &self,
842        workspace_id: &WorkspaceId,
843        user_id: &UserId,
844    ) -> Result<(), StoreError> {
845        let ws_id = workspace_id.0.to_string();
846        let u_id = user_id.0.to_string();
847
848        sqlx::query!(
849            "INSERT INTO workspace_members(workspace_id, user_id) VALUES(?, ?)",
850            ws_id,
851            u_id
852        )
853        .execute(&self.pool)
854        .await
855        .map_err(|e| match e {
856            sqlx::Error::Database(ref db_err) if db_err.is_unique_violation() => {
857                StoreError::AlreadyExists
858            }
859            _ => StoreError::Backend(e.to_string()),
860        })?;
861        Ok(())
862    }
863
864    // ───────────────────────────── Projects ───────────────────────────────
865
866    async fn create_project(
867        &self,
868        params: &CreateProjectParams,
869    ) -> Result<zopp_storage::ProjectId, StoreError> {
870        let id = Uuid::now_v7();
871        let id_str = id.to_string();
872        let ws_id = params.workspace_id.0.to_string();
873
874        sqlx::query!(
875            "INSERT INTO projects(id, workspace_id, name) VALUES(?, ?, ?)",
876            id_str,
877            ws_id,
878            params.name
879        )
880        .execute(&self.pool)
881        .await
882        .map_err(|e| {
883            let s = e.to_string();
884            if s.contains("UNIQUE") {
885                StoreError::AlreadyExists
886            } else {
887                StoreError::Backend(s)
888            }
889        })?;
890        Ok(zopp_storage::ProjectId(id))
891    }
892
893    async fn list_projects(
894        &self,
895        workspace_id: &WorkspaceId,
896    ) -> Result<Vec<zopp_storage::Project>, StoreError> {
897        let ws_id = workspace_id.0.to_string();
898
899        let rows = sqlx::query!(
900            r#"SELECT id, workspace_id, name,
901               created_at as "created_at: DateTime<Utc>",
902               updated_at as "updated_at: DateTime<Utc>"
903               FROM projects WHERE workspace_id = ? ORDER BY created_at DESC"#,
904            ws_id
905        )
906        .fetch_all(&self.pool)
907        .await
908        .map_err(|e| StoreError::Backend(e.to_string()))?;
909
910        let projects = rows
911            .into_iter()
912            .map(|row| {
913                Ok(zopp_storage::Project {
914                    id: zopp_storage::ProjectId(
915                        Uuid::parse_str(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?,
916                    ),
917                    workspace_id: zopp_storage::WorkspaceId(
918                        Uuid::parse_str(&row.workspace_id)
919                            .map_err(|e| StoreError::Backend(e.to_string()))?,
920                    ),
921                    name: row.name,
922                    created_at: row.created_at,
923                    updated_at: row.updated_at,
924                })
925            })
926            .collect::<Result<Vec<_>, StoreError>>()?;
927
928        Ok(projects)
929    }
930
931    async fn get_project(
932        &self,
933        project_id: &zopp_storage::ProjectId,
934    ) -> Result<zopp_storage::Project, StoreError> {
935        let id_str = project_id.0.to_string();
936
937        let row = sqlx::query!(
938            r#"SELECT id, workspace_id, name,
939               created_at as "created_at: DateTime<Utc>",
940               updated_at as "updated_at: DateTime<Utc>"
941               FROM projects WHERE id = ?"#,
942            id_str
943        )
944        .fetch_optional(&self.pool)
945        .await
946        .map_err(|e| StoreError::Backend(e.to_string()))?
947        .ok_or(StoreError::NotFound)?;
948
949        Ok(zopp_storage::Project {
950            id: zopp_storage::ProjectId(
951                Uuid::parse_str(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?,
952            ),
953            workspace_id: zopp_storage::WorkspaceId(
954                Uuid::parse_str(&row.workspace_id)
955                    .map_err(|e| StoreError::Backend(e.to_string()))?,
956            ),
957            name: row.name,
958            created_at: row.created_at,
959            updated_at: row.updated_at,
960        })
961    }
962
963    async fn get_project_by_name(
964        &self,
965        workspace_id: &WorkspaceId,
966        name: &str,
967    ) -> Result<zopp_storage::Project, StoreError> {
968        let ws_id_str = workspace_id.0.to_string();
969
970        let row = sqlx::query!(
971            r#"SELECT id, workspace_id, name,
972               created_at as "created_at: DateTime<Utc>",
973               updated_at as "updated_at: DateTime<Utc>"
974               FROM projects WHERE workspace_id = ? AND name = ?"#,
975            ws_id_str,
976            name
977        )
978        .fetch_optional(&self.pool)
979        .await
980        .map_err(|e| StoreError::Backend(e.to_string()))?
981        .ok_or(StoreError::NotFound)?;
982
983        Ok(zopp_storage::Project {
984            id: zopp_storage::ProjectId(
985                Uuid::parse_str(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?,
986            ),
987            workspace_id: zopp_storage::WorkspaceId(
988                Uuid::parse_str(&row.workspace_id)
989                    .map_err(|e| StoreError::Backend(e.to_string()))?,
990            ),
991            name: row.name,
992            created_at: row.created_at,
993            updated_at: row.updated_at,
994        })
995    }
996
997    async fn delete_project(&self, project_id: &zopp_storage::ProjectId) -> Result<(), StoreError> {
998        let id_str = project_id.0.to_string();
999
1000        let result = sqlx::query!("DELETE FROM projects WHERE id = ?", id_str)
1001            .execute(&self.pool)
1002            .await
1003            .map_err(|e| StoreError::Backend(e.to_string()))?;
1004
1005        if result.rows_affected() == 0 {
1006            return Err(StoreError::NotFound);
1007        }
1008
1009        Ok(())
1010    }
1011
1012    // ──────────────────────────── Environments ────────────────────────────
1013
1014    async fn create_env(&self, params: &CreateEnvParams) -> Result<EnvironmentId, StoreError> {
1015        let env_id = Uuid::now_v7();
1016        let env_id_str = env_id.to_string();
1017        let proj_id_str = params.project_id.0.to_string();
1018
1019        // Get project to determine workspace_id
1020        let proj_row = sqlx::query!(
1021            "SELECT workspace_id FROM projects WHERE id = ?",
1022            proj_id_str
1023        )
1024        .fetch_optional(&self.pool)
1025        .await
1026        .map_err(|e| StoreError::Backend(e.to_string()))?
1027        .ok_or(StoreError::NotFound)?;
1028
1029        let ws_id = proj_row.workspace_id;
1030
1031        sqlx::query!(
1032            "INSERT INTO environments(id, workspace_id, project_id, name, dek_wrapped, dek_nonce)
1033             VALUES(?, ?, ?, ?, ?, ?)",
1034            env_id_str,
1035            ws_id,
1036            proj_id_str,
1037            params.name,
1038            params.dek_wrapped,
1039            params.dek_nonce
1040        )
1041        .execute(&self.pool)
1042        .await
1043        .map_err(|e| {
1044            let s = e.to_string();
1045            if s.contains("UNIQUE") {
1046                StoreError::AlreadyExists
1047            } else {
1048                StoreError::Backend(s)
1049            }
1050        })?;
1051
1052        Ok(EnvironmentId(env_id))
1053    }
1054
1055    async fn list_environments(
1056        &self,
1057        project_id: &zopp_storage::ProjectId,
1058    ) -> Result<Vec<Environment>, StoreError> {
1059        let proj_id_str = project_id.0.to_string();
1060
1061        let rows = sqlx::query!(
1062            r#"SELECT id, project_id, name, dek_wrapped, dek_nonce, version,
1063               created_at as "created_at: DateTime<Utc>",
1064               updated_at as "updated_at: DateTime<Utc>"
1065               FROM environments WHERE project_id = ? ORDER BY created_at DESC"#,
1066            proj_id_str
1067        )
1068        .fetch_all(&self.pool)
1069        .await
1070        .map_err(|e| StoreError::Backend(e.to_string()))?;
1071
1072        let environments = rows
1073            .into_iter()
1074            .map(|row| {
1075                Ok(Environment {
1076                    id: EnvironmentId(
1077                        Uuid::parse_str(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?,
1078                    ),
1079                    project_id: zopp_storage::ProjectId(
1080                        Uuid::parse_str(&row.project_id)
1081                            .map_err(|e| StoreError::Backend(e.to_string()))?,
1082                    ),
1083                    name: row.name,
1084                    dek_wrapped: row.dek_wrapped,
1085                    dek_nonce: row.dek_nonce,
1086                    version: row.version,
1087                    created_at: row.created_at,
1088                    updated_at: row.updated_at,
1089                })
1090            })
1091            .collect::<Result<Vec<_>, StoreError>>()?;
1092
1093        Ok(environments)
1094    }
1095
1096    async fn get_environment(&self, env_id: &EnvironmentId) -> Result<Environment, StoreError> {
1097        let env_id_str = env_id.0.to_string();
1098
1099        let row = sqlx::query!(
1100            r#"SELECT id, project_id, name, dek_wrapped, dek_nonce, version,
1101               created_at as "created_at: DateTime<Utc>",
1102               updated_at as "updated_at: DateTime<Utc>"
1103               FROM environments WHERE id = ?"#,
1104            env_id_str
1105        )
1106        .fetch_optional(&self.pool)
1107        .await
1108        .map_err(|e| StoreError::Backend(e.to_string()))?
1109        .ok_or(StoreError::NotFound)?;
1110
1111        Ok(Environment {
1112            id: EnvironmentId(
1113                Uuid::parse_str(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?,
1114            ),
1115            project_id: zopp_storage::ProjectId(
1116                Uuid::parse_str(&row.project_id).map_err(|e| StoreError::Backend(e.to_string()))?,
1117            ),
1118            name: row.name,
1119            dek_wrapped: row.dek_wrapped,
1120            dek_nonce: row.dek_nonce,
1121            version: row.version,
1122            created_at: row.created_at,
1123            updated_at: row.updated_at,
1124        })
1125    }
1126
1127    async fn get_environment_by_name(
1128        &self,
1129        project_id: &zopp_storage::ProjectId,
1130        name: &str,
1131    ) -> Result<Environment, StoreError> {
1132        let proj_id_str = project_id.0.to_string();
1133
1134        let row = sqlx::query!(
1135            r#"SELECT id, project_id, name, dek_wrapped, dek_nonce, version,
1136               created_at as "created_at: DateTime<Utc>",
1137               updated_at as "updated_at: DateTime<Utc>"
1138               FROM environments WHERE project_id = ? AND name = ?"#,
1139            proj_id_str,
1140            name
1141        )
1142        .fetch_optional(&self.pool)
1143        .await
1144        .map_err(|e| StoreError::Backend(e.to_string()))?
1145        .ok_or(StoreError::NotFound)?;
1146
1147        Ok(Environment {
1148            id: EnvironmentId(
1149                Uuid::parse_str(&row.id).map_err(|e| StoreError::Backend(e.to_string()))?,
1150            ),
1151            project_id: zopp_storage::ProjectId(
1152                Uuid::parse_str(&row.project_id).map_err(|e| StoreError::Backend(e.to_string()))?,
1153            ),
1154            name: row.name,
1155            dek_wrapped: row.dek_wrapped,
1156            dek_nonce: row.dek_nonce,
1157            version: row.version,
1158            created_at: row.created_at,
1159            updated_at: row.updated_at,
1160        })
1161    }
1162
1163    async fn delete_environment(&self, env_id: &EnvironmentId) -> Result<(), StoreError> {
1164        let env_id_str = env_id.0.to_string();
1165
1166        let result = sqlx::query!("DELETE FROM environments WHERE id = ?", env_id_str)
1167            .execute(&self.pool)
1168            .await
1169            .map_err(|e| StoreError::Backend(e.to_string()))?;
1170
1171        if result.rows_affected() == 0 {
1172            return Err(StoreError::NotFound);
1173        }
1174
1175        Ok(())
1176    }
1177
1178    async fn get_env_wrap(
1179        &self,
1180        ws: &WorkspaceId,
1181        project: &ProjectName,
1182        env: &EnvName,
1183    ) -> Result<(Vec<u8>, Vec<u8>), StoreError> {
1184        let ws_id = ws.0.to_string();
1185        let proj_name = &project.0;
1186        let env_name = &env.0;
1187
1188        let row = sqlx::query!(
1189            "SELECT e.dek_wrapped, e.dek_nonce
1190             FROM environments e
1191             JOIN projects p ON p.id = e.project_id
1192             WHERE e.workspace_id = ? AND p.workspace_id = ? AND p.name = ? AND e.name = ?",
1193            ws_id,
1194            ws_id,
1195            proj_name,
1196            env_name
1197        )
1198        .fetch_optional(&self.pool)
1199        .await
1200        .map_err(|e| StoreError::Backend(e.to_string()))?;
1201
1202        match row {
1203            Some(row) => Ok((row.dek_wrapped, row.dek_nonce)),
1204            None => Err(StoreError::NotFound),
1205        }
1206    }
1207
1208    // ────────────────────────────── Secrets ───────────────────────────────
1209
1210    async fn upsert_secret(
1211        &self,
1212        env_id: &EnvironmentId,
1213        key: &str,
1214        nonce: &[u8],
1215        ciphertext: &[u8],
1216    ) -> Result<i64, StoreError> {
1217        let env_id_str = env_id.0.to_string();
1218
1219        let mut tx = self
1220            .pool
1221            .begin()
1222            .await
1223            .map_err(|e| StoreError::Backend(e.to_string()))?;
1224
1225        // Get environment to determine workspace_id
1226        let env_row = sqlx::query!(
1227            "SELECT workspace_id FROM environments WHERE id = ?",
1228            env_id_str
1229        )
1230        .fetch_optional(&mut *tx)
1231        .await
1232        .map_err(|e| StoreError::Backend(e.to_string()))?
1233        .ok_or(StoreError::NotFound)?;
1234
1235        let ws_id = env_row.workspace_id;
1236        let secret_id = Uuid::now_v7().to_string();
1237
1238        // Insert/update the secret
1239        sqlx::query!(
1240            "INSERT INTO secrets(id, workspace_id, env_id, key_name, nonce, ciphertext)
1241             VALUES(?, ?, ?, ?, ?, ?)
1242             ON CONFLICT(workspace_id, env_id, key_name)
1243             DO UPDATE SET nonce = excluded.nonce, ciphertext = excluded.ciphertext",
1244            secret_id,
1245            ws_id,
1246            env_id_str,
1247            key,
1248            nonce,
1249            ciphertext
1250        )
1251        .execute(&mut *tx)
1252        .await
1253        .map_err(|e| StoreError::Backend(e.to_string()))?;
1254
1255        // Increment environment version
1256        let result = sqlx::query!(
1257            "UPDATE environments SET version = version + 1 WHERE id = ? RETURNING version",
1258            env_id_str
1259        )
1260        .fetch_one(&mut *tx)
1261        .await
1262        .map_err(|e| StoreError::Backend(e.to_string()))?;
1263
1264        tx.commit()
1265            .await
1266            .map_err(|e| StoreError::Backend(e.to_string()))?;
1267
1268        Ok(result.version)
1269    }
1270
1271    async fn get_secret(&self, env_id: &EnvironmentId, key: &str) -> Result<SecretRow, StoreError> {
1272        let env_id_str = env_id.0.to_string();
1273
1274        let row = sqlx::query!(
1275            "SELECT nonce, ciphertext FROM secrets WHERE env_id = ? AND key_name = ?",
1276            env_id_str,
1277            key
1278        )
1279        .fetch_optional(&self.pool)
1280        .await
1281        .map_err(|e| StoreError::Backend(e.to_string()))?;
1282
1283        match row {
1284            None => Err(StoreError::NotFound),
1285            Some(row) => Ok(SecretRow {
1286                nonce: row.nonce,
1287                ciphertext: row.ciphertext,
1288            }),
1289        }
1290    }
1291
1292    async fn list_secret_keys(&self, env_id: &EnvironmentId) -> Result<Vec<String>, StoreError> {
1293        let env_id_str = env_id.0.to_string();
1294
1295        let rows = sqlx::query!(
1296            "SELECT key_name FROM secrets WHERE env_id = ? ORDER BY key_name",
1297            env_id_str
1298        )
1299        .fetch_all(&self.pool)
1300        .await
1301        .map_err(|e| StoreError::Backend(e.to_string()))?;
1302
1303        Ok(rows.into_iter().map(|row| row.key_name).collect())
1304    }
1305
1306    async fn delete_secret(&self, env_id: &EnvironmentId, key: &str) -> Result<i64, StoreError> {
1307        let env_id_str = env_id.0.to_string();
1308
1309        let mut tx = self
1310            .pool
1311            .begin()
1312            .await
1313            .map_err(|e| StoreError::Backend(e.to_string()))?;
1314
1315        let result = sqlx::query!(
1316            "DELETE FROM secrets WHERE env_id = ? AND key_name = ?",
1317            env_id_str,
1318            key
1319        )
1320        .execute(&mut *tx)
1321        .await
1322        .map_err(|e| StoreError::Backend(e.to_string()))?;
1323
1324        if result.rows_affected() == 0 {
1325            return Err(StoreError::NotFound);
1326        }
1327
1328        // Increment environment version
1329        let version_result = sqlx::query!(
1330            "UPDATE environments SET version = version + 1 WHERE id = ? RETURNING version",
1331            env_id_str
1332        )
1333        .fetch_one(&mut *tx)
1334        .await
1335        .map_err(|e| StoreError::Backend(e.to_string()))?;
1336
1337        tx.commit()
1338            .await
1339            .map_err(|e| StoreError::Backend(e.to_string()))?;
1340
1341        Ok(version_result.version)
1342    }
1343}
1344
1345#[cfg(test)]
1346mod tests {
1347    use super::*;
1348    use zopp_storage::{
1349        CreateWorkspaceParams, EnvName, ProjectName, StoreError, UserId, WorkspaceId,
1350    };
1351
1352    fn workspace_params(owner_user_id: UserId, name: &str) -> CreateWorkspaceParams {
1353        CreateWorkspaceParams {
1354            id: WorkspaceId(uuid::Uuid::now_v7()),
1355            name: name.to_string(),
1356            owner_user_id,
1357            kdf_salt: b"abcdef0123456789".to_vec(),
1358            m_cost_kib: 1024,
1359            t_cost: 2,
1360            p_cost: 1,
1361        }
1362    }
1363
1364    #[tokio::test]
1365    async fn workspace_params_roundtrip() {
1366        let s = SqliteStore::open_in_memory().await.unwrap();
1367        let (user_id, _) = s
1368            .create_user(&CreateUserParams {
1369                email: "test@example.com".to_string(),
1370                principal: None,
1371                workspace_ids: vec![],
1372            })
1373            .await
1374            .unwrap();
1375        let ws = s
1376            .create_workspace(&workspace_params(user_id, "test-workspace"))
1377            .await
1378            .unwrap();
1379        let got = s.get_workspace(&ws).await.unwrap();
1380        assert_eq!(
1381            got.kdf_salt,
1382            workspace_params(got.owner_user_id.clone(), "test-workspace").kdf_salt
1383        );
1384        assert_eq!(got.m_cost_kib, 1024);
1385        assert_eq!(got.t_cost, 2);
1386        assert_eq!(got.p_cost, 1);
1387    }
1388
1389    #[tokio::test]
1390    async fn duplicate_project_maps_to_alreadyexists() {
1391        let s = SqliteStore::open_in_memory().await.unwrap();
1392        let (user_id, _) = s
1393            .create_user(&CreateUserParams {
1394                email: "test@example.com".to_string(),
1395                principal: None,
1396                workspace_ids: vec![],
1397            })
1398            .await
1399            .unwrap();
1400        let ws = s
1401            .create_workspace(&workspace_params(user_id, "test-workspace"))
1402            .await
1403            .unwrap();
1404        let name = ProjectName("app".into());
1405
1406        s.create_project(&CreateProjectParams {
1407            workspace_id: ws.clone(),
1408            name: name.0.clone(),
1409        })
1410        .await
1411        .unwrap();
1412        let err = s
1413            .create_project(&CreateProjectParams {
1414                workspace_id: ws,
1415                name: name.0,
1416            })
1417            .await
1418            .unwrap_err();
1419
1420        matches!(err, StoreError::AlreadyExists);
1421    }
1422
1423    #[tokio::test]
1424    async fn workspace_scoping_isolation() {
1425        let s = SqliteStore::open_in_memory().await.unwrap();
1426        let (user_id, _) = s
1427            .create_user(&CreateUserParams {
1428                email: "test@example.com".to_string(),
1429                principal: None,
1430                workspace_ids: vec![],
1431            })
1432            .await
1433            .unwrap();
1434        let ws1 = s
1435            .create_workspace(&workspace_params(user_id.clone(), "test-workspace-1"))
1436            .await
1437            .unwrap();
1438        let ws2 = s
1439            .create_workspace(&workspace_params(user_id, "test-workspace-2"))
1440            .await
1441            .unwrap();
1442
1443        let p = ProjectName("app".into());
1444        let e = EnvName("prod".into());
1445
1446        let project_id1 = s
1447            .create_project(&CreateProjectParams {
1448                workspace_id: ws1.clone(),
1449                name: p.0.clone(),
1450            })
1451            .await
1452            .unwrap();
1453        let env_id1 = s
1454            .create_env(&CreateEnvParams {
1455                project_id: project_id1,
1456                name: e.0.clone(),
1457                dek_wrapped: vec![1],
1458                dek_nonce: vec![9; 24],
1459            })
1460            .await
1461            .unwrap();
1462
1463        let project_id2 = s
1464            .create_project(&CreateProjectParams {
1465                workspace_id: ws2.clone(),
1466                name: p.0.clone(),
1467            })
1468            .await
1469            .unwrap();
1470        let env_id2 = s
1471            .create_env(&CreateEnvParams {
1472                project_id: project_id2,
1473                name: e.0.clone(),
1474                dek_wrapped: vec![2],
1475                dek_nonce: vec![9; 24],
1476            })
1477            .await
1478            .unwrap();
1479
1480        // only insert secret into env1
1481        s.upsert_secret(&env_id1, "TOKEN", &[7; 24], &[1; 8])
1482            .await
1483            .unwrap();
1484
1485        // env2 must NOT be able to see env1's secret
1486        let err = s.get_secret(&env_id2, "TOKEN").await.unwrap_err();
1487        matches!(err, StoreError::NotFound);
1488    }
1489
1490    #[tokio::test]
1491    async fn list_secret_keys_returns_sorted() {
1492        let s = SqliteStore::open_in_memory().await.unwrap();
1493        let (user_id, _) = s
1494            .create_user(&CreateUserParams {
1495                email: "test@example.com".to_string(),
1496                principal: None,
1497                workspace_ids: vec![],
1498            })
1499            .await
1500            .unwrap();
1501        let ws = s
1502            .create_workspace(&workspace_params(user_id, "test-workspace"))
1503            .await
1504            .unwrap();
1505
1506        let p = ProjectName("app".into());
1507        let e = EnvName("prod".into());
1508        let project_id = s
1509            .create_project(&CreateProjectParams {
1510                workspace_id: ws.clone(),
1511                name: p.0.clone(),
1512            })
1513            .await
1514            .unwrap();
1515        let env_id = s
1516            .create_env(&CreateEnvParams {
1517                project_id,
1518                name: e.0.clone(),
1519                dek_wrapped: vec![1],
1520                dek_nonce: vec![9; 24],
1521            })
1522            .await
1523            .unwrap();
1524
1525        s.upsert_secret(&env_id, "z_last", &[7; 24], &[1])
1526            .await
1527            .unwrap();
1528        s.upsert_secret(&env_id, "a_first", &[7; 24], &[1])
1529            .await
1530            .unwrap();
1531        s.upsert_secret(&env_id, "m_middle", &[7; 24], &[1])
1532            .await
1533            .unwrap();
1534
1535        let keys = s.list_secret_keys(&env_id).await.unwrap();
1536        assert_eq!(keys, vec!["a_first", "m_middle", "z_last"]);
1537    }
1538
1539    #[tokio::test]
1540    async fn create_env_requires_existing_project() {
1541        let s = SqliteStore::open_in_memory().await.unwrap();
1542        let (user_id, _) = s
1543            .create_user(&CreateUserParams {
1544                email: "test@example.com".to_string(),
1545                principal: None,
1546                workspace_ids: vec![],
1547            })
1548            .await
1549            .unwrap();
1550        let _ws = s
1551            .create_workspace(&workspace_params(user_id, "test-workspace"))
1552            .await
1553            .unwrap();
1554
1555        // Non-existent project ID → creating env should fail with NotFound
1556        let fake_project_id = zopp_storage::ProjectId(uuid::Uuid::new_v4());
1557        let err = s
1558            .create_env(&CreateEnvParams {
1559                project_id: fake_project_id,
1560                name: "prod".to_string(),
1561                dek_wrapped: vec![1],
1562                dek_nonce: vec![9; 24],
1563            })
1564            .await
1565            .unwrap_err();
1566        matches!(err, StoreError::NotFound);
1567    }
1568
1569    #[tokio::test]
1570    async fn duplicate_env_maps_to_alreadyexists() {
1571        let s = SqliteStore::open_in_memory().await.unwrap();
1572        let (user_id, _) = s
1573            .create_user(&CreateUserParams {
1574                email: "test@example.com".to_string(),
1575                principal: None,
1576                workspace_ids: vec![],
1577            })
1578            .await
1579            .unwrap();
1580        let ws = s
1581            .create_workspace(&workspace_params(user_id, "test-workspace"))
1582            .await
1583            .unwrap();
1584        let p = ProjectName("app".into());
1585        let e = EnvName("prod".into());
1586
1587        let project_id = s
1588            .create_project(&CreateProjectParams {
1589                workspace_id: ws.clone(),
1590                name: p.0.clone(),
1591            })
1592            .await
1593            .unwrap();
1594        s.create_env(&CreateEnvParams {
1595            project_id: project_id.clone(),
1596            name: e.0.clone(),
1597            dek_wrapped: vec![1],
1598            dek_nonce: vec![9; 24],
1599        })
1600        .await
1601        .unwrap();
1602        let err = s
1603            .create_env(&CreateEnvParams {
1604                project_id,
1605                name: e.0,
1606                dek_wrapped: vec![1],
1607                dek_nonce: vec![9; 24],
1608            })
1609            .await
1610            .unwrap_err();
1611        matches!(err, StoreError::AlreadyExists);
1612    }
1613
1614    #[tokio::test]
1615    async fn upsert_secret_overwrites_value_and_nonce() {
1616        let s = SqliteStore::open_in_memory().await.unwrap();
1617        let (user_id, _) = s
1618            .create_user(&CreateUserParams {
1619                email: "test@example.com".to_string(),
1620                principal: None,
1621                workspace_ids: vec![],
1622            })
1623            .await
1624            .unwrap();
1625        let ws = s
1626            .create_workspace(&workspace_params(user_id, "test-workspace"))
1627            .await
1628            .unwrap();
1629
1630        let p = ProjectName("app".into());
1631        let e = EnvName("prod".into());
1632        let project_id = s
1633            .create_project(&CreateProjectParams {
1634                workspace_id: ws.clone(),
1635                name: p.0.clone(),
1636            })
1637            .await
1638            .unwrap();
1639        let env_id = s
1640            .create_env(&CreateEnvParams {
1641                project_id,
1642                name: e.0.clone(),
1643                dek_wrapped: vec![1],
1644                dek_nonce: vec![9; 24],
1645            })
1646            .await
1647            .unwrap();
1648
1649        s.upsert_secret(&env_id, "API", &[1; 24], &[10; 4])
1650            .await
1651            .unwrap();
1652        let a = s.get_secret(&env_id, "API").await.unwrap();
1653
1654        s.upsert_secret(&env_id, "API", &[2; 24], &[20; 6])
1655            .await
1656            .unwrap();
1657        let b = s.get_secret(&env_id, "API").await.unwrap();
1658
1659        assert_ne!(a.nonce, b.nonce);
1660        assert_ne!(a.ciphertext, b.ciphertext);
1661        assert_eq!(b.nonce, vec![2; 24]);
1662        assert_eq!(b.ciphertext, vec![20; 6]);
1663    }
1664
1665    #[tokio::test]
1666    async fn get_env_wrap_scoped_by_workspace() {
1667        let s = SqliteStore::open_in_memory().await.unwrap();
1668        let (user_id, _) = s
1669            .create_user(&CreateUserParams {
1670                email: "test@example.com".to_string(),
1671                principal: None,
1672                workspace_ids: vec![],
1673            })
1674            .await
1675            .unwrap();
1676        let ws1 = s
1677            .create_workspace(&workspace_params(user_id.clone(), "test-workspace-1"))
1678            .await
1679            .unwrap();
1680        let ws2 = s
1681            .create_workspace(&workspace_params(user_id, "test-workspace-2"))
1682            .await
1683            .unwrap();
1684
1685        let p = ProjectName("app".into());
1686        let e = EnvName("prod".into());
1687
1688        let project_id1 = s
1689            .create_project(&CreateProjectParams {
1690                workspace_id: ws1.clone(),
1691                name: p.0.clone(),
1692            })
1693            .await
1694            .unwrap();
1695        s.create_env(&CreateEnvParams {
1696            project_id: project_id1,
1697            name: e.0.clone(),
1698            dek_wrapped: b"wrap1".to_vec(),
1699            dek_nonce: vec![9; 24],
1700        })
1701        .await
1702        .unwrap();
1703
1704        let project_id2 = s
1705            .create_project(&CreateProjectParams {
1706                workspace_id: ws2.clone(),
1707                name: p.0.clone(),
1708            })
1709            .await
1710            .unwrap();
1711        s.create_env(&CreateEnvParams {
1712            project_id: project_id2,
1713            name: e.0.clone(),
1714            dek_wrapped: b"wrap2".to_vec(),
1715            dek_nonce: vec![8; 24],
1716        })
1717        .await
1718        .unwrap();
1719
1720        let (w1, n1) = s.get_env_wrap(&ws1, &p, &e).await.unwrap();
1721        let (w2, n2) = s.get_env_wrap(&ws2, &p, &e).await.unwrap();
1722
1723        assert_eq!(w1, b"wrap1");
1724        assert_eq!(n1, vec![9; 24]);
1725        assert_eq!(w2, b"wrap2");
1726        assert_eq!(n2, vec![8; 24]);
1727    }
1728
1729    #[tokio::test]
1730    async fn update_changes_updated_at_but_not_created_at() {
1731        let s = SqliteStore::open_in_memory().await.unwrap();
1732        let (user_id, _) = s
1733            .create_user(&CreateUserParams {
1734                email: "test@example.com".to_string(),
1735                principal: None,
1736                workspace_ids: vec![],
1737            })
1738            .await
1739            .unwrap();
1740
1741        // Create a principal
1742        let principal_id = s
1743            .create_principal(&CreatePrincipalParams {
1744                user_id: Some(user_id.clone()),
1745                name: "old-name".to_string(),
1746                public_key: vec![1, 2, 3],
1747                x25519_public_key: None,
1748            })
1749            .await
1750            .unwrap();
1751
1752        let initial = s.get_principal(&principal_id).await.unwrap();
1753
1754        // Sleep to ensure time passes (SQLite timestamps have millisecond precision)
1755        tokio::time::sleep(std::time::Duration::from_millis(10)).await;
1756
1757        // Update the principal
1758        s.rename_principal(&principal_id, "new-name").await.unwrap();
1759
1760        let after_update = s.get_principal(&principal_id).await.unwrap();
1761
1762        // created_at should never change
1763        assert_eq!(initial.created_at, after_update.created_at);
1764        // updated_at should be newer after an update
1765        assert!(after_update.updated_at > initial.updated_at);
1766        // The actual update should have worked
1767        assert_eq!(after_update.name, "new-name");
1768    }
1769
1770    #[tokio::test]
1771    async fn unicode_keys_and_names_roundtrip() {
1772        let s = SqliteStore::open_in_memory().await.unwrap();
1773        let (user_id, _) = s
1774            .create_user(&CreateUserParams {
1775                email: "test@example.com".to_string(),
1776                principal: None,
1777                workspace_ids: vec![],
1778            })
1779            .await
1780            .unwrap();
1781        let ws = s
1782            .create_workspace(&workspace_params(user_id, "test-workspace"))
1783            .await
1784            .unwrap();
1785
1786        let p = ProjectName("🚀-プロジェクト".into());
1787        let e = EnvName("生产".into());
1788        let k = "🔑-секрет";
1789
1790        let project_id = s
1791            .create_project(&CreateProjectParams {
1792                workspace_id: ws.clone(),
1793                name: p.0.clone(),
1794            })
1795            .await
1796            .unwrap();
1797        let env_id = s
1798            .create_env(&CreateEnvParams {
1799                project_id,
1800                name: e.0.clone(),
1801                dek_wrapped: vec![1, 2, 3],
1802                dek_nonce: vec![9; 24],
1803            })
1804            .await
1805            .unwrap();
1806        s.upsert_secret(&env_id, k, &[7; 24], &[1, 2, 3, 4])
1807            .await
1808            .unwrap();
1809
1810        let row = s.get_secret(&env_id, k).await.unwrap();
1811        assert_eq!(row.ciphertext, vec![1, 2, 3, 4]);
1812
1813        let keys = s.list_secret_keys(&env_id).await.unwrap();
1814        assert_eq!(keys, vec![k]);
1815    }
1816}
zopp_store_sqlite/lib.rs

zopp_store_sqlite/
lib.rs
