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