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