1use argon2::{
2 Algorithm, Argon2, Params, Version,
3 password_hash::{PasswordHash, PasswordHasher, PasswordVerifier, SaltString},
4};
5use base64::Engine;
6use chrono::Utc;
7use sha2::{Digest, Sha256};
8use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
9use std::path::Path;
10use std::str::FromStr;
11use thiserror::Error;
12use uuid::Uuid;
13
14const ARGON2_MEMORY_KIB: u32 = 1 << 14; const ARGON2_ITERATIONS: u32 = 3;
16const ARGON2_PARALLELISM: u32 = 4;
17
18#[derive(Debug, Error)]
19pub enum DbError {
20 #[error("Database error: {0}")]
21 Database(#[from] sqlx::Error),
22 #[error("Not found: {0}")]
23 NotFound(String),
24 #[error("Conflict: {0}")]
25 Conflict(String),
26 #[error("Validation error: {0}")]
27 Validation(String),
28 #[error("Internal error: {0}")]
29 Internal(String),
30}
31
32pub type DbResult<T> = Result<T, DbError>;
33
34#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
35pub struct Snippet {
36 pub id: String,
37 pub description: String,
38 pub command: String,
39 pub tags: Vec<String>,
40 pub created_at: i64,
41 pub updated_at: i64,
42 pub device_id: String,
43 pub deleted: bool,
44 pub encrypted: bool,
45}
46
47#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
48pub struct Library {
49 pub id: String,
50 pub name: String,
51 pub created_at: i64,
52 pub snippet_count: i64,
53}
54
55#[derive(Clone)]
56pub struct Database {
57 pool: SqlitePool,
58}
59
60fn hash_api_key(api_key: &str) -> DbResult<String> {
61 let mut salt_bytes = [0u8; 16];
62 getrandom::fill(&mut salt_bytes)
63 .map_err(|e| DbError::Internal(format!("Failed to generate salt: {}", e)))?;
64 let salt_b64 = base64::engine::general_purpose::STANDARD_NO_PAD.encode(salt_bytes);
65 let salt = SaltString::from_b64(&salt_b64)
66 .map_err(|e| DbError::Internal(format!("Failed to create salt: {}", e)))?;
67 let params = Params::new(
68 ARGON2_MEMORY_KIB,
69 ARGON2_ITERATIONS,
70 ARGON2_PARALLELISM,
71 None,
72 )
73 .map_err(|e| DbError::Internal(format!("Invalid Argon2 params: {}", e)))?;
74 let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
75 let hash = argon2
76 .hash_password(api_key.as_bytes(), &salt)
77 .map_err(|e| DbError::Internal(format!("Failed to hash API key: {}", e)))?
78 .to_string();
79 Ok(hash)
80}
81
82fn verify_api_key(api_key: &str, hash: &str) -> bool {
83 let parsed = match PasswordHash::new(hash) {
84 Ok(p) => p,
85 Err(_) => return false,
86 };
87 let params = match Params::new(
88 ARGON2_MEMORY_KIB,
89 ARGON2_ITERATIONS,
90 ARGON2_PARALLELISM,
91 None,
92 ) {
93 Ok(p) => p,
94 Err(_) => return false,
95 };
96 let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
97 argon2.verify_password(api_key.as_bytes(), &parsed).is_ok()
98}
99
100fn compute_api_key_prefix(api_key: &str) -> String {
102 let hash = Sha256::digest(api_key.as_bytes());
103 let encoded = base64::engine::general_purpose::STANDARD_NO_PAD.encode(hash);
104 encoded.chars().take(8).collect()
105}
106
107fn derive_prefix_from_hash(stored_hash: &str) -> String {
113 let hash = Sha256::digest(stored_hash.as_bytes());
114 let encoded = base64::engine::general_purpose::STANDARD_NO_PAD.encode(hash);
115 encoded.chars().take(8).collect()
116}
117
118fn saturating_i32(v: i64) -> i32 {
119 v.min(i32::MAX as i64) as i32
120}
121
122type SnippetRow = (String, String, String, String, i64, i64, String, i32, i32);
123
124impl Database {
125 pub async fn connect(url: &str, max_connections: u32) -> DbResult<Self> {
126 let options = if url.starts_with("sqlite:") {
132 SqliteConnectOptions::from_str(url)?
133 } else {
134 SqliteConnectOptions::new().filename(url)
135 }
136 .create_if_missing(true);
137
138 let filename = options.get_filename();
139 if filename != Path::new(":memory:")
140 && let Some(parent) = filename.parent()
141 && !parent.as_os_str().is_empty()
142 {
143 std::fs::create_dir_all(parent).map_err(|e| {
144 DbError::Internal(format!(
145 "Failed to create database directory {}: {}",
146 parent.display(),
147 e
148 ))
149 })?;
150 }
151
152 let pool = SqlitePoolOptions::new()
153 .max_connections(max_connections)
154 .connect_with(options)
155 .await?;
156
157 sqlx::query("PRAGMA journal_mode=WAL")
158 .execute(&pool)
159 .await?;
160 sqlx::query("PRAGMA foreign_keys=ON").execute(&pool).await?;
161
162 sqlx::query(
163 "
164 CREATE TABLE IF NOT EXISTS users (
165 id TEXT PRIMARY KEY,
166 api_key TEXT UNIQUE NOT NULL,
167 api_key_prefix TEXT,
168 created_at INTEGER NOT NULL,
169 updated_at INTEGER NOT NULL
170 )
171 ",
172 )
173 .execute(&pool)
174 .await?;
175
176 sqlx::query("CREATE INDEX IF NOT EXISTS idx_users_api_key_prefix ON users(api_key_prefix)")
177 .execute(&pool)
178 .await?;
179
180 sqlx::query(
181 "
182 CREATE TABLE IF NOT EXISTS libraries (
183 id TEXT PRIMARY KEY,
184 user_id TEXT NOT NULL,
185 name TEXT NOT NULL,
186 created_at INTEGER NOT NULL,
187 deleted_at INTEGER,
188 FOREIGN KEY (user_id) REFERENCES users(id),
189 UNIQUE(user_id, name)
190 )
191 ",
192 )
193 .execute(&pool)
194 .await?;
195
196 sqlx::query(
197 "
198 CREATE TABLE IF NOT EXISTS snippets (
199 id TEXT PRIMARY KEY,
200 user_id TEXT NOT NULL,
201 library_id TEXT NOT NULL,
202 description TEXT NOT NULL,
203 command TEXT NOT NULL,
204 tags TEXT NOT NULL DEFAULT '[]',
205 created_at INTEGER NOT NULL,
206 updated_at INTEGER NOT NULL,
207 device_id TEXT NOT NULL,
208 deleted INTEGER NOT NULL DEFAULT 0,
209 encrypted INTEGER NOT NULL DEFAULT 0,
210 FOREIGN KEY (user_id) REFERENCES users(id),
211 FOREIGN KEY (library_id) REFERENCES libraries(id)
212 )
213 ",
214 )
215 .execute(&pool)
216 .await?;
217
218 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_user ON snippets(user_id)")
219 .execute(&pool)
220 .await?;
221 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_library ON snippets(library_id)")
222 .execute(&pool)
223 .await?;
224 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_updated ON snippets(updated_at)")
225 .execute(&pool)
226 .await?;
227 sqlx::query(
228 "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_updated \
229 ON snippets(user_id, library_id, updated_at, deleted)",
230 )
231 .execute(&pool)
232 .await?;
233 sqlx::query("CREATE INDEX IF NOT EXISTS idx_libraries_user ON libraries(user_id)")
234 .execute(&pool)
235 .await?;
236
237 Ok(Self { pool })
238 }
239
240 pub async fn ping(&self) -> DbResult<()> {
241 sqlx::query("SELECT 1").execute(&self.pool).await?;
242 Ok(())
243 }
244
245 pub fn pool(&self) -> &SqlitePool {
246 &self.pool
247 }
248
249 pub async fn create_user(&self, api_key: &str) -> DbResult<String> {
250 let user_id = Uuid::new_v4().to_string();
251 let now = Utc::now().timestamp();
252 let api_key_hash = hash_api_key(api_key)?;
253 let api_key_prefix = compute_api_key_prefix(api_key);
254
255 let mut tx = self.pool.begin().await?;
256
257 sqlx::query("INSERT INTO users (id, api_key, api_key_prefix, created_at, updated_at) VALUES (?, ?, ?, ?, ?)")
258 .bind(&user_id)
259 .bind(&api_key_hash)
260 .bind(&api_key_prefix)
261 .bind(now)
262 .bind(now)
263 .execute(&mut *tx)
264 .await?;
265
266 let default_lib_id = Uuid::new_v4().to_string();
267 sqlx::query("INSERT INTO libraries (id, user_id, name, created_at) VALUES (?, ?, ?, ?)")
268 .bind(&default_lib_id)
269 .bind(&user_id)
270 .bind("default")
271 .bind(now)
272 .execute(&mut *tx)
273 .await?;
274
275 tx.commit().await?;
276
277 tracing::info!(
278 "Created user {} with default library {}",
279 user_id,
280 default_lib_id
281 );
282
283 Ok(user_id)
284 }
285
286 pub async fn get_user_by_api_key(&self, api_key: &str) -> DbResult<Option<String>> {
287 let prefix = compute_api_key_prefix(api_key);
288
289 let rows: Vec<(String, String)> =
294 sqlx::query_as("SELECT id, api_key FROM users WHERE api_key_prefix = ?")
295 .bind(&prefix)
296 .fetch_all(&self.pool)
297 .await?;
298
299 for (user_id, stored_hash) in rows {
300 if verify_api_key(api_key, &stored_hash) {
301 return Ok(Some(user_id));
302 }
303 }
304
305 Ok(None)
306 }
307
308 pub async fn create_library(&self, user_id: &str, name: &str) -> DbResult<String> {
309 if !name
310 .chars()
311 .all(|c| c.is_alphanumeric() || c == '-' || c == '_')
312 {
313 return Err(DbError::Validation(
314 "Invalid library name. Use only letters, numbers, dash, and underscore."
315 .to_string(),
316 ));
317 }
318 if name.is_empty() || name.len() > 64 {
319 return Err(DbError::Validation(
320 "Library name must be 1-64 characters".to_string(),
321 ));
322 }
323
324 let lib_id = Uuid::new_v4().to_string();
325 let now = Utc::now().timestamp();
326
327 let result = sqlx::query(
328 "INSERT OR IGNORE INTO libraries (id, user_id, name, created_at) VALUES (?, ?, ?, ?)",
329 )
330 .bind(&lib_id)
331 .bind(user_id)
332 .bind(name)
333 .bind(now)
334 .execute(&self.pool)
335 .await?;
336
337 if result.rows_affected() == 0 {
338 return Err(DbError::Conflict(format!(
339 "Library '{}' already exists",
340 name
341 )));
342 }
343
344 Ok(lib_id)
345 }
346
347 pub async fn list_libraries(
348 &self,
349 user_id: &str,
350 limit: i32,
351 offset: i32,
352 ) -> DbResult<(Vec<Library>, i32)> {
353 let total: (i64,) = sqlx::query_as(
354 "SELECT COUNT(*) FROM libraries WHERE user_id = ? AND deleted_at IS NULL",
355 )
356 .bind(user_id)
357 .fetch_one(&self.pool)
358 .await?;
359
360 let rows: Vec<(String, String, i64, i64)> = sqlx::query_as(
361 "SELECT l.id, l.name, l.created_at, COUNT(s.id) as snippet_count \
362 FROM libraries l \
363 LEFT JOIN snippets s ON s.library_id = l.id AND s.deleted = 0 \
364 WHERE l.user_id = ? AND l.deleted_at IS NULL \
365 GROUP BY l.id \
366 ORDER BY l.name LIMIT ? OFFSET ?",
367 )
368 .bind(user_id)
369 .bind(limit)
370 .bind(offset)
371 .fetch_all(&self.pool)
372 .await?;
373
374 let libraries = rows
375 .into_iter()
376 .map(|(id, name, created_at, snippet_count)| Library {
377 id,
378 name,
379 created_at,
380 snippet_count,
381 })
382 .collect();
383
384 Ok((libraries, saturating_i32(total.0)))
385 }
386
387 pub async fn delete_library(&self, user_id: &str, library_id: &str) -> DbResult<()> {
388 let now = Utc::now().timestamp();
389 let mut tx = self.pool.begin().await?;
390
391 let result = sqlx::query(
392 "UPDATE libraries SET deleted_at = ? WHERE id = ? AND user_id = ? AND deleted_at IS NULL",
393 )
394 .bind(now)
395 .bind(library_id)
396 .bind(user_id)
397 .execute(&mut *tx)
398 .await?;
399
400 if result.rows_affected() == 0 {
401 tx.rollback().await?;
402 return Err(DbError::NotFound(
403 "Library not found or already deleted".to_string(),
404 ));
405 }
406
407 sqlx::query(
408 "UPDATE snippets
409 SET deleted = 1, updated_at = MAX(updated_at, ?)
410 WHERE library_id = ? AND user_id = ? AND deleted = 0",
411 )
412 .bind(now)
413 .bind(library_id)
414 .bind(user_id)
415 .execute(&mut *tx)
416 .await?;
417
418 tx.commit().await?;
419 Ok(())
420 }
421
422 pub async fn get_default_library(&self, user_id: &str) -> DbResult<String> {
423 let (lib_id,): (String,) = sqlx::query_as(
424 "SELECT id FROM libraries WHERE user_id = ? AND name = 'default' AND deleted_at IS NULL",
425 )
426 .bind(user_id)
427 .fetch_optional(&self.pool)
428 .await?
429 .ok_or_else(|| DbError::NotFound("Default library not found".to_string()))?;
430
431 Ok(lib_id)
432 }
433
434 pub async fn verify_library_ownership(
435 &self,
436 user_id: &str,
437 library_id: &str,
438 ) -> DbResult<bool> {
439 let (count,): (i64,) = sqlx::query_as(
440 "SELECT COUNT(*) FROM libraries WHERE id = ? AND user_id = ? AND deleted_at IS NULL",
441 )
442 .bind(library_id)
443 .bind(user_id)
444 .fetch_one(&self.pool)
445 .await?;
446
447 Ok(count > 0)
448 }
449
450 pub async fn get_snippets(
451 &self,
452 user_id: &str,
453 library_id: &str,
454 since: i64,
455 limit: i32,
456 offset: i32,
457 include_deleted: bool,
458 ) -> DbResult<(Vec<Snippet>, i32)> {
459 let total: (i64,) = sqlx::query_as(
460 "SELECT COUNT(*) FROM snippets \
461 WHERE user_id = ? \
462 AND library_id = ? \
463 AND updated_at > ? \
464 AND (? OR deleted = 0)",
465 )
466 .bind(user_id)
467 .bind(library_id)
468 .bind(since)
469 .bind(include_deleted)
470 .fetch_one(&self.pool)
471 .await?;
472
473 let rows: Vec<SnippetRow> = sqlx::query_as(
474 "SELECT id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted \
475 FROM snippets \
476 WHERE user_id = ? \
477 AND library_id = ? \
478 AND updated_at > ? \
479 AND (? OR deleted = 0) \
480 ORDER BY updated_at DESC, id DESC \
481 LIMIT ? OFFSET ?",
482 )
483 .bind(user_id)
484 .bind(library_id)
485 .bind(since)
486 .bind(include_deleted)
487 .bind(limit)
488 .bind(offset)
489 .fetch_all(&self.pool)
490 .await?;
491
492 let snippets = rows
493 .into_iter()
494 .map(
495 |(
496 id,
497 description,
498 command,
499 tags_str,
500 created_at,
501 updated_at,
502 device_id,
503 deleted,
504 encrypted,
505 )| {
506 let tags: Vec<String> = serde_json::from_str(&tags_str).inspect_err(|e| {
507 tracing::warn!(snippet_id = %id, error = %e, "Failed to parse tags JSON, using empty list");
508 }).unwrap_or_default();
509 Snippet {
510 id,
511 description,
512 command,
513 tags,
514 created_at,
515 updated_at,
516 device_id,
517 deleted: deleted != 0,
518 encrypted: encrypted != 0,
519 }
520 },
521 )
522 .collect();
523
524 Ok((snippets, saturating_i32(total.0)))
525 }
526
527 #[cfg(test)]
528 pub async fn upsert_snippet(
529 &self,
530 snippet: &Snippet,
531 user_id: &str,
532 library_id: &str,
533 ) -> DbResult<()> {
534 let tags_json = serde_json::to_string(&snippet.tags).unwrap_or_else(|_| "[]".to_string());
535 let deleted = snippet.deleted as i32;
536 let encrypted = snippet.encrypted as i32;
537
538 sqlx::query(
539 "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted)
540 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
541 ON CONFLICT(id) DO UPDATE SET
542 description = excluded.description,
543 command = excluded.command,
544 tags = excluded.tags,
545 updated_at = excluded.updated_at,
546 device_id = excluded.device_id,
547 deleted = excluded.deleted,
548 encrypted = excluded.encrypted
549 WHERE excluded.user_id = snippets.user_id AND excluded.library_id = snippets.library_id \
550 AND (excluded.updated_at > snippets.updated_at \
551 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id > snippets.device_id))",
552 )
553 .bind(&snippet.id)
554 .bind(user_id)
555 .bind(library_id)
556 .bind(&snippet.description)
557 .bind(&snippet.command)
558 .bind(&tags_json)
559 .bind(snippet.created_at)
560 .bind(snippet.updated_at)
561 .bind(&snippet.device_id)
562 .bind(deleted)
563 .bind(encrypted)
564 .execute(&self.pool)
565 .await?;
566
567 Ok(())
568 }
569
570 pub async fn upsert_snippet_in_tx(
571 &self,
572 tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
573 snippet: &Snippet,
574 user_id: &str,
575 library_id: &str,
576 ) -> DbResult<()> {
577 let tags_json = serde_json::to_string(&snippet.tags).unwrap_or_else(|_| "[]".to_string());
578 let deleted = snippet.deleted as i32;
579 let encrypted = snippet.encrypted as i32;
580
581 sqlx::query(
585 "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted)
586 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
587 ON CONFLICT(id) DO UPDATE SET
588 description = excluded.description,
589 command = excluded.command,
590 tags = excluded.tags,
591 updated_at = excluded.updated_at,
592 device_id = excluded.device_id,
593 deleted = excluded.deleted,
594 encrypted = excluded.encrypted
595 WHERE excluded.user_id = snippets.user_id AND excluded.library_id = snippets.library_id \
596 AND (excluded.updated_at > snippets.updated_at \
597 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id > snippets.device_id))",
598 )
599 .bind(&snippet.id)
600 .bind(user_id)
601 .bind(library_id)
602 .bind(&snippet.description)
603 .bind(&snippet.command)
604 .bind(&tags_json)
605 .bind(snippet.created_at)
606 .bind(snippet.updated_at)
607 .bind(&snippet.device_id)
608 .bind(deleted)
609 .bind(encrypted)
610 .execute(&mut **tx)
611 .await?;
612
613 Ok(())
614 }
615
616 pub async fn get_latest_timestamp(&self, user_id: &str, library_id: &str) -> DbResult<i64> {
617 let (timestamp,): (i64,) = sqlx::query_as(
618 "SELECT COALESCE(MAX(updated_at), 0) FROM snippets WHERE user_id = ? AND library_id = ?",
619 )
620 .bind(user_id)
621 .bind(library_id)
622 .fetch_one(&self.pool)
623 .await?;
624
625 Ok(timestamp)
626 }
627
628 pub async fn migrate_plaintext_api_keys(&self) -> DbResult<usize> {
629 let rows: Vec<(String, String, Option<String>)> =
630 sqlx::query_as("SELECT id, api_key, api_key_prefix FROM users")
631 .fetch_all(&self.pool)
632 .await?;
633
634 let mut migrated = 0;
635
636 for (user_id, stored, prefix) in rows {
637 let mut needs_update = false;
638 let mut new_hash = stored.clone();
639 let mut new_prefix = prefix.clone();
640
641 if !stored.starts_with("$argon2") {
643 new_hash = hash_api_key(&stored)?;
644 needs_update = true;
645 }
646
647 if prefix.is_none() {
649 if stored.starts_with("$argon2") {
650 new_prefix = Some(derive_prefix_from_hash(&stored));
655 } else {
656 new_prefix = Some(compute_api_key_prefix(&stored));
657 }
658 needs_update = true;
659 }
660
661 if needs_update {
662 sqlx::query("UPDATE users SET api_key = ?, api_key_prefix = ? WHERE id = ?")
663 .bind(&new_hash)
664 .bind(&new_prefix)
665 .bind(&user_id)
666 .execute(&self.pool)
667 .await?;
668 migrated += 1;
669 }
670 }
671
672 if migrated > 0 {
673 tracing::info!("Migrated {} API keys (hash or prefix update)", migrated);
674 }
675
676 Ok(migrated)
677 }
678}
679
680#[cfg(test)]
681mod tests {
682 use super::*;
683
684 async fn setup_db() -> Database {
685 Database::connect("sqlite::memory:", 5).await.unwrap()
686 }
687
688 #[tokio::test]
689 async fn test_file_database_is_created_from_path() {
690 let temp = tempfile::tempdir().unwrap();
691 let path = temp.path().join("nested").join("snippets.db");
692
693 let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
694
695 assert!(path.exists());
696 }
697
698 #[tokio::test]
699 async fn test_create_and_verify_user() {
700 let db = setup_db().await;
701 let api_key = "test-api-key-12345";
702
703 let user_id = db.create_user(api_key).await.unwrap();
704 assert!(!user_id.is_empty());
705
706 let found = db.get_user_by_api_key(api_key).await.unwrap();
708 assert_eq!(found, Some(user_id));
709
710 let not_found = db.get_user_by_api_key("wrong-key").await.unwrap();
712 assert_eq!(not_found, None);
713 }
714
715 #[tokio::test]
716 async fn test_api_key_is_hashed() {
717 let db = setup_db().await;
718 let api_key = "plaintext-key-should-be-hashed";
719
720 let _user_id = db.create_user(api_key).await.unwrap();
721
722 let rows: Vec<(String, String, Option<String>)> =
724 sqlx::query_as("SELECT id, api_key, api_key_prefix FROM users")
725 .fetch_all(&db.pool)
726 .await
727 .unwrap();
728
729 assert_eq!(rows.len(), 1);
730 assert!(
731 rows[0].1.starts_with("$argon2"),
732 "API key should be stored as Argon2 hash"
733 );
734 assert!(rows[0].2.is_some(), "API key prefix should be stored");
736 assert_eq!(rows[0].2.as_ref().unwrap().len(), 8);
737 }
738
739 #[tokio::test]
740 async fn test_create_default_library() {
741 let db = setup_db().await;
742 let user_id = db.create_user("key").await.unwrap();
743
744 let lib_id = db.get_default_library(&user_id).await.unwrap();
745 assert!(!lib_id.is_empty());
746
747 let (libraries, total) = db.list_libraries(&user_id, 10, 0).await.unwrap();
748 assert_eq!(total, 1);
749 assert_eq!(libraries[0].name, "default");
750 }
751
752 #[tokio::test]
753 async fn test_create_additional_library() {
754 let db = setup_db().await;
755 let user_id = db.create_user("key").await.unwrap();
756
757 let lib_id = db.create_library(&user_id, "work").await.unwrap();
758 assert!(!lib_id.is_empty());
759
760 let (_libraries, total) = db.list_libraries(&user_id, 10, 0).await.unwrap();
761 assert_eq!(total, 2);
762
763 let err = db.create_library(&user_id, "work").await.unwrap_err();
765 assert!(matches!(err, DbError::Conflict(_)));
766 }
767
768 #[tokio::test]
769 async fn test_invalid_library_names() {
770 let db = setup_db().await;
771 let user_id = db.create_user("key").await.unwrap();
772
773 assert!(db.create_library(&user_id, "").await.is_err());
775
776 assert!(db.create_library(&user_id, "my/lib").await.is_err());
778
779 assert!(db.create_library(&user_id, &"a".repeat(65)).await.is_err());
781
782 assert!(db.create_library(&user_id, "valid-name").await.is_ok());
784 assert!(db.create_library(&user_id, "valid_name").await.is_ok());
785 assert!(db.create_library(&user_id, "ValidName123").await.is_ok());
786 }
787
788 #[tokio::test]
789 async fn test_upsert_and_get_snippets() {
790 let db = setup_db().await;
791 let user_id = db.create_user("key").await.unwrap();
792 let lib_id = db.get_default_library(&user_id).await.unwrap();
793
794 let snippet = Snippet {
795 id: "snip-1".to_string(),
796 description: "Test snippet".to_string(),
797 command: "echo hello".to_string(),
798 tags: vec!["test".to_string()],
799 created_at: 100,
800 updated_at: 100,
801 device_id: "device-1".to_string(),
802 deleted: false,
803 encrypted: false,
804 };
805
806 db.upsert_snippet(&snippet, &user_id, &lib_id)
807 .await
808 .unwrap();
809
810 let (snippets, total) = db
811 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
812 .await
813 .unwrap();
814 assert_eq!(total, 1);
815 assert_eq!(snippets[0].description, "Test snippet");
816 assert_eq!(snippets[0].command, "echo hello");
817 assert_eq!(snippets[0].tags, vec!["test"]);
818 }
819
820 #[tokio::test]
821 async fn test_delete_library_preserves_future_tombstone_timestamp() {
822 let db = setup_db().await;
823 let user_id = db.create_user("key").await.unwrap();
824 let library_id = db.create_library(&user_id, "future-delete").await.unwrap();
825 let future_timestamp = Utc::now().timestamp() + 100;
826
827 let snippet = Snippet {
828 id: "future-snippet".to_string(),
829 description: "desc".to_string(),
830 command: "echo hi".to_string(),
831 tags: vec![],
832 created_at: 0,
833 updated_at: future_timestamp,
834 device_id: "device".to_string(),
835 deleted: false,
836 encrypted: false,
837 };
838 db.upsert_snippet(&snippet, &user_id, &library_id)
839 .await
840 .unwrap();
841
842 db.delete_library(&user_id, &library_id).await.unwrap();
843
844 let (snippets, total) = db
845 .get_snippets(&user_id, &library_id, future_timestamp - 1, 10, 0, true)
846 .await
847 .unwrap();
848 assert_eq!(total, 1);
849 assert!(snippets[0].deleted);
850 assert_eq!(snippets[0].updated_at, future_timestamp);
851 }
852
853 #[tokio::test]
854 async fn test_upsert_updates_existing() {
855 let db = setup_db().await;
856 let user_id = db.create_user("key").await.unwrap();
857 let lib_id = db.get_default_library(&user_id).await.unwrap();
858
859 let snippet = Snippet {
860 id: "snip-1".to_string(),
861 description: "Original".to_string(),
862 command: "echo old".to_string(),
863 tags: vec![],
864 created_at: 100,
865 updated_at: 100,
866 device_id: "device-1".to_string(),
867 deleted: false,
868 encrypted: false,
869 };
870 db.upsert_snippet(&snippet, &user_id, &lib_id)
871 .await
872 .unwrap();
873
874 let updated = Snippet {
876 id: "snip-1".to_string(),
877 description: "Updated".to_string(),
878 command: "echo new".to_string(),
879 tags: vec!["updated".to_string()],
880 created_at: 100,
881 updated_at: 200,
882 device_id: "device-1".to_string(),
883 deleted: false,
884 encrypted: false,
885 };
886 db.upsert_snippet(&updated, &user_id, &lib_id)
887 .await
888 .unwrap();
889
890 let (snippets, _) = db
891 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
892 .await
893 .unwrap();
894 assert_eq!(snippets.len(), 1);
895 assert_eq!(snippets[0].description, "Updated");
896 assert_eq!(snippets[0].command, "echo new");
897 }
898
899 #[tokio::test]
900 async fn test_upsert_rejects_older_timestamp() {
901 let db = setup_db().await;
902 let user_id = db.create_user("key").await.unwrap();
903 let lib_id = db.get_default_library(&user_id).await.unwrap();
904
905 let snippet = Snippet {
907 id: "snip-1".to_string(),
908 description: "Newer".to_string(),
909 command: "echo newer".to_string(),
910 tags: vec![],
911 created_at: 100,
912 updated_at: 200,
913 device_id: "d".to_string(),
914 deleted: false,
915 encrypted: false,
916 };
917 db.upsert_snippet(&snippet, &user_id, &lib_id)
918 .await
919 .unwrap();
920
921 let older = Snippet {
923 id: "snip-1".to_string(),
924 description: "Older".to_string(),
925 command: "echo older".to_string(),
926 tags: vec![],
927 created_at: 100,
928 updated_at: 100,
929 device_id: "d".to_string(),
930 deleted: false,
931 encrypted: false,
932 };
933 db.upsert_snippet(&older, &user_id, &lib_id).await.unwrap();
934
935 let (snippets, _) = db
937 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
938 .await
939 .unwrap();
940 assert_eq!(snippets.len(), 1);
941 assert_eq!(snippets[0].description, "Newer");
942 assert_eq!(snippets[0].command, "echo newer");
943 }
944
945 #[tokio::test]
946 async fn test_get_latest_timestamp() {
947 let db = setup_db().await;
948 let user_id = db.create_user("key").await.unwrap();
949 let lib_id = db.get_default_library(&user_id).await.unwrap();
950
951 let ts = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
953 assert_eq!(ts, 0);
954
955 let snippet = Snippet {
956 id: "snip-1".to_string(),
957 description: "Test".to_string(),
958 command: "echo".to_string(),
959 tags: vec![],
960 created_at: 100,
961 updated_at: 500,
962 device_id: "d".to_string(),
963 deleted: false,
964 encrypted: false,
965 };
966 db.upsert_snippet(&snippet, &user_id, &lib_id)
967 .await
968 .unwrap();
969
970 let ts = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
971 assert_eq!(ts, 500);
972 }
973
974 #[tokio::test]
975 async fn test_delete_library() {
976 let db = setup_db().await;
977 let user_id = db.create_user("key").await.unwrap();
978 let lib_id = db.create_library(&user_id, "temp").await.unwrap();
979
980 let snippet = Snippet {
982 id: "snip-1".to_string(),
983 description: "Test".to_string(),
984 command: "echo".to_string(),
985 tags: vec![],
986 created_at: 100,
987 updated_at: 100,
988 device_id: "d".to_string(),
989 deleted: false,
990 encrypted: false,
991 };
992 db.upsert_snippet(&snippet, &user_id, &lib_id)
993 .await
994 .unwrap();
995
996 db.delete_library(&user_id, &lib_id).await.unwrap();
998
999 assert!(db.delete_library(&user_id, &lib_id).await.is_err());
1001
1002 let (snippets, _) = db
1004 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1005 .await
1006 .unwrap();
1007 assert_eq!(snippets.len(), 0);
1008 }
1009
1010 #[tokio::test]
1011 async fn test_migrate_plaintext_api_keys() {
1012 let db = setup_db().await;
1013 let user_id = Uuid::new_v4().to_string();
1014 let now = Utc::now().timestamp();
1015
1016 sqlx::query("INSERT INTO users (id, api_key, created_at, updated_at) VALUES (?, ?, ?, ?)")
1018 .bind(&user_id)
1019 .bind("plaintext-api-key")
1020 .bind(now)
1021 .bind(now)
1022 .execute(&db.pool)
1023 .await
1024 .unwrap();
1025
1026 let migrated = db.migrate_plaintext_api_keys().await.unwrap();
1028 assert_eq!(migrated, 1);
1029
1030 let (stored_hash,): (String,) = sqlx::query_as("SELECT api_key FROM users WHERE id = ?")
1032 .bind(&user_id)
1033 .fetch_one(&db.pool)
1034 .await
1035 .unwrap();
1036
1037 assert!(stored_hash.starts_with("$argon2"));
1038
1039 let (prefix,): (Option<String>,) =
1041 sqlx::query_as("SELECT api_key_prefix FROM users WHERE id = ?")
1042 .bind(&user_id)
1043 .fetch_one(&db.pool)
1044 .await
1045 .unwrap();
1046 assert!(
1047 prefix.is_some(),
1048 "API key prefix should be backfilled by migration"
1049 );
1050
1051 let found = db.get_user_by_api_key("plaintext-api-key").await.unwrap();
1053 assert_eq!(found, Some(user_id));
1054
1055 let migrated_again = db.migrate_plaintext_api_keys().await.unwrap();
1057 assert_eq!(migrated_again, 0);
1058 }
1059
1060 #[tokio::test]
1061 async fn test_api_key_prefix_lookup() {
1062 let db = setup_db().await;
1063
1064 let user1 = db.create_user("key-one-alpha").await.unwrap();
1066 let user2 = db.create_user("key-two-beta").await.unwrap();
1067 let user3 = db.create_user("key-three-gamma").await.unwrap();
1068
1069 assert_eq!(
1071 db.get_user_by_api_key("key-one-alpha").await.unwrap(),
1072 Some(user1)
1073 );
1074 assert_eq!(
1075 db.get_user_by_api_key("key-two-beta").await.unwrap(),
1076 Some(user2)
1077 );
1078 assert_eq!(
1079 db.get_user_by_api_key("key-three-gamma").await.unwrap(),
1080 Some(user3)
1081 );
1082 assert_eq!(db.get_user_by_api_key("nonexistent").await.unwrap(), None);
1083
1084 let rows: Vec<(String,)> =
1086 sqlx::query_as("SELECT api_key_prefix FROM users WHERE api_key_prefix IS NOT NULL")
1087 .fetch_all(&db.pool)
1088 .await
1089 .unwrap();
1090 assert_eq!(rows.len(), 3);
1091 }
1092
1093 #[tokio::test]
1094 async fn test_library_ownership_verification() {
1095 let db = setup_db().await;
1096 let user1_id = db.create_user("user1-api-key").await.unwrap();
1097 let user2_id = db.create_user("user2-api-key").await.unwrap();
1098 let lib_id = db.create_library(&user1_id, "private").await.unwrap();
1099
1100 assert!(
1101 db.verify_library_ownership(&user1_id, &lib_id)
1102 .await
1103 .unwrap()
1104 );
1105 assert!(
1106 !db.verify_library_ownership(&user2_id, &lib_id)
1107 .await
1108 .unwrap()
1109 );
1110
1111 let nonexistent_lib = Uuid::new_v4().to_string();
1112 assert!(
1113 !db.verify_library_ownership(&user1_id, &nonexistent_lib)
1114 .await
1115 .unwrap()
1116 );
1117 }
1118
1119 #[tokio::test]
1120 async fn test_library_ownership_prevents_cross_user_access() {
1121 let db = setup_db().await;
1122 let user1_id = db.create_user("user1-key").await.unwrap();
1123 let user2_id = db.create_user("user2-key").await.unwrap();
1124 let user1_lib = db.create_library(&user1_id, "my-library").await.unwrap();
1125
1126 let snippet = Snippet {
1127 id: "snip-1".to_string(),
1128 description: "User 1 secret".to_string(),
1129 command: "secret command".to_string(),
1130 tags: vec![],
1131 created_at: 100,
1132 updated_at: 100,
1133 device_id: "device-1".to_string(),
1134 deleted: false,
1135 encrypted: false,
1136 };
1137
1138 db.upsert_snippet(&snippet, &user1_id, &user1_lib)
1139 .await
1140 .unwrap();
1141
1142 let (snippets, _) = db
1143 .get_snippets(&user1_id, &user1_lib, 0, 10, 0, false)
1144 .await
1145 .unwrap();
1146 assert_eq!(snippets.len(), 1);
1147
1148 let (snippets, _) = db
1149 .get_snippets(&user2_id, &user1_lib, 0, 10, 0, false)
1150 .await
1151 .unwrap();
1152 assert_eq!(snippets.len(), 0);
1153 }
1154
1155 #[tokio::test]
1156 async fn test_deleted_library_not_owned() {
1157 let db = setup_db().await;
1158 let user_id = db.create_user("test-key").await.unwrap();
1159 let lib_id = db.create_library(&user_id, "temp-lib").await.unwrap();
1160
1161 assert!(
1162 db.verify_library_ownership(&user_id, &lib_id)
1163 .await
1164 .unwrap()
1165 );
1166
1167 db.delete_library(&user_id, &lib_id).await.unwrap();
1168
1169 assert!(
1170 !db.verify_library_ownership(&user_id, &lib_id)
1171 .await
1172 .unwrap()
1173 );
1174 }
1175
1176 #[tokio::test]
1177 async fn test_get_snippets_include_deleted() {
1178 let db = setup_db().await;
1179 let user_id = db.create_user("key").await.unwrap();
1180 let lib_id = db.get_default_library(&user_id).await.unwrap();
1181
1182 let snippet = Snippet {
1183 id: "snip-1".to_string(),
1184 description: "Test".to_string(),
1185 command: "echo".to_string(),
1186 tags: vec![],
1187 created_at: 100,
1188 updated_at: 100,
1189 device_id: "d".to_string(),
1190 deleted: false,
1191 encrypted: false,
1192 };
1193 db.upsert_snippet(&snippet, &user_id, &lib_id)
1194 .await
1195 .unwrap();
1196
1197 let deleted = Snippet {
1199 id: "snip-1".to_string(),
1200 description: "Test".to_string(),
1201 command: "echo".to_string(),
1202 tags: vec![],
1203 created_at: 100,
1204 updated_at: 200,
1205 device_id: "d".to_string(),
1206 deleted: true,
1207 encrypted: false,
1208 };
1209 db.upsert_snippet(&deleted, &user_id, &lib_id)
1210 .await
1211 .unwrap();
1212
1213 let (snippets, _) = db
1215 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1216 .await
1217 .unwrap();
1218 assert_eq!(snippets.len(), 0);
1219
1220 let (snippets, _) = db
1222 .get_snippets(&user_id, &lib_id, 0, 10, 0, true)
1223 .await
1224 .unwrap();
1225 assert_eq!(snippets.len(), 1);
1226 assert!(snippets[0].deleted);
1227 assert_eq!(snippets[0].updated_at, 200);
1228 }
1229}