1use argon2::{
2 Algorithm, Argon2, Params, Version,
3 password_hash::{PasswordHasher, PasswordVerifier, phc::PasswordHash},
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 params = Params::new(
66 ARGON2_MEMORY_KIB,
67 ARGON2_ITERATIONS,
68 ARGON2_PARALLELISM,
69 None,
70 )
71 .map_err(|e| DbError::Internal(format!("Invalid Argon2 params: {}", e)))?;
72 let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
73 let hash = argon2
74 .hash_password_with_salt(api_key.as_bytes(), &salt_bytes)
75 .map_err(|e| DbError::Internal(format!("Failed to hash API key: {}", e)))?
76 .to_string();
77 Ok(hash)
78}
79
80fn verify_api_key(api_key: &str, hash: &str) -> bool {
81 let parsed = match PasswordHash::new(hash) {
82 Ok(p) => p,
83 Err(_) => return false,
84 };
85 let params = match Params::new(
86 ARGON2_MEMORY_KIB,
87 ARGON2_ITERATIONS,
88 ARGON2_PARALLELISM,
89 None,
90 ) {
91 Ok(p) => p,
92 Err(_) => return false,
93 };
94 let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
95 argon2.verify_password(api_key.as_bytes(), &parsed).is_ok()
96}
97
98fn compute_api_key_prefix(api_key: &str) -> String {
100 let hash = Sha256::digest(api_key.as_bytes());
101 let encoded = base64::engine::general_purpose::STANDARD_NO_PAD.encode(hash);
102 encoded.chars().take(8).collect()
103}
104
105fn derive_prefix_from_hash(stored_hash: &str) -> String {
111 let hash = Sha256::digest(stored_hash.as_bytes());
112 let encoded = base64::engine::general_purpose::STANDARD_NO_PAD.encode(hash);
113 encoded.chars().take(8).collect()
114}
115
116fn saturating_i32(v: i64) -> i32 {
117 v.min(i32::MAX as i64) as i32
118}
119
120type SnippetRow = (String, String, String, String, i64, i64, String, i32, i32);
121
122const MIGRATION_BATCH_SIZE: i64 = 100;
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 .busy_timeout(Duration::from_secs(5));
138
139 let filename = options.get_filename();
140 if filename != Path::new(":memory:")
141 && let Some(parent) = filename.parent()
142 && !parent.as_os_str().is_empty()
143 {
144 std::fs::create_dir_all(parent).map_err(|e| {
145 DbError::Internal(format!(
146 "Failed to create database directory {}: {}",
147 parent.display(),
148 e
149 ))
150 })?;
151 }
152
153 let pool = SqlitePoolOptions::new()
154 .max_connections(max_connections)
155 .connect_with(options)
156 .await?;
157
158 sqlx::query("PRAGMA journal_mode=WAL")
159 .execute(&pool)
160 .await?;
161 sqlx::query("PRAGMA foreign_keys=ON").execute(&pool).await?;
162
163 sqlx::query(
164 "
165 CREATE TABLE IF NOT EXISTS users (
166 id TEXT PRIMARY KEY,
167 api_key TEXT UNIQUE NOT NULL,
168 api_key_prefix TEXT,
169 created_at INTEGER NOT NULL,
170 updated_at INTEGER NOT NULL
171 )
172 ",
173 )
174 .execute(&pool)
175 .await?;
176
177 sqlx::query("CREATE INDEX IF NOT EXISTS idx_users_api_key_prefix ON users(api_key_prefix)")
178 .execute(&pool)
179 .await?;
180
181 sqlx::query(
182 "
183 CREATE TABLE IF NOT EXISTS libraries (
184 id TEXT PRIMARY KEY,
185 user_id TEXT NOT NULL,
186 name TEXT NOT NULL,
187 created_at INTEGER NOT NULL,
188 deleted_at INTEGER,
189 FOREIGN KEY (user_id) REFERENCES users(id),
190 UNIQUE(user_id, name)
191 )
192 ",
193 )
194 .execute(&pool)
195 .await?;
196
197 sqlx::query(
198 "
199 CREATE TABLE IF NOT EXISTS snippets (
200 id TEXT PRIMARY KEY,
201 user_id TEXT NOT NULL,
202 library_id TEXT NOT NULL,
203 description TEXT NOT NULL,
204 command TEXT NOT NULL,
205 tags TEXT NOT NULL DEFAULT '[]',
206 created_at INTEGER NOT NULL,
207 updated_at INTEGER NOT NULL,
208 device_id TEXT NOT NULL,
209 deleted INTEGER NOT NULL DEFAULT 0,
210 encrypted INTEGER NOT NULL DEFAULT 0,
211 FOREIGN KEY (user_id) REFERENCES users(id),
212 FOREIGN KEY (library_id) REFERENCES libraries(id)
213 )
214 ",
215 )
216 .execute(&pool)
217 .await?;
218
219 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_user ON snippets(user_id)")
220 .execute(&pool)
221 .await?;
222 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_library ON snippets(library_id)")
223 .execute(&pool)
224 .await?;
225 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_updated ON snippets(updated_at)")
226 .execute(&pool)
227 .await?;
228 sqlx::query(
229 "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_updated \
230 ON snippets(user_id, library_id, updated_at, deleted)",
231 )
232 .execute(&pool)
233 .await?;
234 sqlx::query("CREATE INDEX IF NOT EXISTS idx_libraries_user ON libraries(user_id)")
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)> =
295 sqlx::query_as("SELECT id, api_key FROM users WHERE api_key_prefix = ?")
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 mut tx = self.pool.begin().await?;
461
462 let total: (i64,) = sqlx::query_as(
469 "SELECT COUNT(*) FROM snippets \
470 WHERE user_id = ? \
471 AND library_id = ? \
472 AND updated_at >= ? \
473 AND (? OR deleted = 0)",
474 )
475 .bind(user_id)
476 .bind(library_id)
477 .bind(since)
478 .bind(include_deleted)
479 .fetch_one(&mut *tx)
480 .await?;
481
482 let rows: Vec<SnippetRow> = sqlx::query_as(
483 "SELECT id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted \
484 FROM snippets \
485 WHERE user_id = ? \
486 AND library_id = ? \
487 AND updated_at >= ? \
488 AND (? OR deleted = 0) \
489 ORDER BY updated_at DESC, id DESC \
490 LIMIT ? OFFSET ?",
491 )
492 .bind(user_id)
493 .bind(library_id)
494 .bind(since)
495 .bind(include_deleted)
496 .bind(limit)
497 .bind(offset)
498 .fetch_all(&mut *tx)
499 .await?;
500
501 tx.commit().await?;
502
503 let snippets = rows
504 .into_iter()
505 .map(
506 |(
507 id,
508 description,
509 command,
510 tags_str,
511 created_at,
512 updated_at,
513 device_id,
514 deleted,
515 encrypted,
516 )| {
517 let tags: Vec<String> = serde_json::from_str(&tags_str).map_err(|e| {
520 DbError::Internal(format!(
521 "corrupt tags JSON for snippet {id} (row must be repaired): {e}"
522 ))
523 })?;
524 Ok(Snippet {
525 id,
526 description,
527 command,
528 tags,
529 created_at,
530 updated_at,
531 device_id,
532 deleted: deleted != 0,
533 encrypted: encrypted != 0,
534 })
535 },
536 )
537 .collect::<DbResult<Vec<Snippet>>>()?;
538
539 Ok((snippets, saturating_i32(total.0)))
540 }
541
542 #[cfg(test)]
543 pub async fn upsert_snippet(
544 &self,
545 snippet: &Snippet,
546 user_id: &str,
547 library_id: &str,
548 ) -> DbResult<()> {
549 let tags_json = serde_json::to_string(&snippet.tags).unwrap_or_else(|_| "[]".to_string());
550 let deleted = snippet.deleted as i32;
551 let encrypted = snippet.encrypted as i32;
552
553 sqlx::query(
554 "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted)
555 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
556 ON CONFLICT(id) DO UPDATE SET
557 description = excluded.description,
558 command = excluded.command,
559 tags = excluded.tags,
560 updated_at = excluded.updated_at,
561 device_id = excluded.device_id,
562 deleted = excluded.deleted,
563 encrypted = excluded.encrypted
564 WHERE excluded.user_id = snippets.user_id AND excluded.library_id = snippets.library_id \
565 AND (excluded.updated_at > snippets.updated_at \
566 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id > snippets.device_id) \
567 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id = snippets.device_id AND excluded.command > snippets.command) \
568 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id = snippets.device_id AND excluded.command = snippets.command AND excluded.description > snippets.description))",
569 )
570 .bind(&snippet.id)
571 .bind(user_id)
572 .bind(library_id)
573 .bind(&snippet.description)
574 .bind(&snippet.command)
575 .bind(&tags_json)
576 .bind(snippet.created_at)
577 .bind(snippet.updated_at)
578 .bind(&snippet.device_id)
579 .bind(deleted)
580 .bind(encrypted)
581 .execute(&self.pool)
582 .await?;
583
584 Ok(())
585 }
586
587 pub async fn upsert_snippet_in_tx(
588 &self,
589 tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
590 snippet: &Snippet,
591 user_id: &str,
592 library_id: &str,
593 ) -> DbResult<()> {
594 let tags_json = serde_json::to_string(&snippet.tags).unwrap_or_else(|_| "[]".to_string());
595 let deleted = snippet.deleted as i32;
596 let encrypted = snippet.encrypted as i32;
597
598 sqlx::query(
605 "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted)
606 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
607 ON CONFLICT(id) DO UPDATE SET
608 description = excluded.description,
609 command = excluded.command,
610 tags = excluded.tags,
611 updated_at = excluded.updated_at,
612 device_id = excluded.device_id,
613 deleted = excluded.deleted,
614 encrypted = excluded.encrypted
615 WHERE excluded.user_id = snippets.user_id AND excluded.library_id = snippets.library_id \
616 AND (excluded.updated_at > snippets.updated_at \
617 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id > snippets.device_id) \
618 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id = snippets.device_id AND excluded.command > snippets.command) \
619 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id = snippets.device_id AND excluded.command = snippets.command AND excluded.description > snippets.description))",
620 )
621 .bind(&snippet.id)
622 .bind(user_id)
623 .bind(library_id)
624 .bind(&snippet.description)
625 .bind(&snippet.command)
626 .bind(&tags_json)
627 .bind(snippet.created_at)
628 .bind(snippet.updated_at)
629 .bind(&snippet.device_id)
630 .bind(deleted)
631 .bind(encrypted)
632 .execute(&mut **tx)
633 .await?;
634
635 Ok(())
636 }
637
638 pub async fn get_latest_timestamp(&self, user_id: &str, library_id: &str) -> DbResult<i64> {
639 let (timestamp,): (i64,) = sqlx::query_as(
640 "SELECT COALESCE(MAX(updated_at), 0) FROM snippets WHERE user_id = ? AND library_id = ?",
641 )
642 .bind(user_id)
643 .bind(library_id)
644 .fetch_one(&self.pool)
645 .await?;
646
647 Ok(timestamp)
648 }
649
650 pub async fn migrate_plaintext_api_keys(&self) -> DbResult<usize> {
651 let mut migrated = 0;
652 let mut last_id = String::new();
653
654 loop {
655 let rows: Vec<(String, String, Option<String>)> = sqlx::query_as(
656 "SELECT id, api_key, api_key_prefix FROM users \
657 WHERE id > ? ORDER BY id LIMIT ?",
658 )
659 .bind(&last_id)
660 .bind(MIGRATION_BATCH_SIZE)
661 .fetch_all(&self.pool)
662 .await?;
663
664 if rows.is_empty() {
665 break;
666 }
667
668 let mut tx = self.pool.begin().await?;
672 for (user_id, stored, prefix) in rows {
673 last_id = user_id.clone();
674 let mut needs_update = false;
675 let mut new_hash = stored.clone();
676 let mut new_prefix = prefix.clone();
677
678 if !stored.starts_with("$argon2") {
680 new_hash = hash_api_key(&stored)?;
681 needs_update = true;
682 }
683
684 if prefix.is_none() {
686 if stored.starts_with("$argon2") {
687 new_prefix = Some(derive_prefix_from_hash(&stored));
692 } else {
693 new_prefix = Some(compute_api_key_prefix(&stored));
694 }
695 needs_update = true;
696 }
697
698 if needs_update {
699 sqlx::query("UPDATE users SET api_key = ?, api_key_prefix = ? WHERE id = ?")
700 .bind(&new_hash)
701 .bind(&new_prefix)
702 .bind(&user_id)
703 .execute(&mut *tx)
704 .await?;
705 migrated += 1;
706 }
707 }
708 tx.commit().await?;
709 }
710
711 if migrated > 0 {
712 tracing::info!("Migrated {} API keys (hash or prefix update)", migrated);
713 }
714
715 Ok(migrated)
716 }
717}
718
719#[cfg(test)]
720mod tests {
721 use super::*;
722
723 async fn setup_db() -> Database {
724 Database::connect("sqlite::memory:", 5).await.unwrap()
725 }
726
727 #[tokio::test]
728 async fn test_file_database_is_created_from_path() {
729 let temp = tempfile::tempdir().unwrap();
730 let path = temp.path().join("nested").join("snippets.db");
731
732 let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
733
734 assert!(path.exists());
735 }
736
737 #[tokio::test]
738 async fn test_create_and_verify_user() {
739 let db = setup_db().await;
740 let api_key = "test-api-key-12345";
741
742 let user_id = db.create_user(api_key).await.unwrap();
743 assert!(!user_id.is_empty());
744
745 let found = db.get_user_by_api_key(api_key).await.unwrap();
747 assert_eq!(found, Some(user_id));
748
749 let not_found = db.get_user_by_api_key("wrong-key").await.unwrap();
751 assert_eq!(not_found, None);
752 }
753
754 #[tokio::test]
755 async fn test_api_key_is_hashed() {
756 let db = setup_db().await;
757 let api_key = "plaintext-key-should-be-hashed";
758
759 let _user_id = db.create_user(api_key).await.unwrap();
760
761 let rows: Vec<(String, String, Option<String>)> =
763 sqlx::query_as("SELECT id, api_key, api_key_prefix FROM users")
764 .fetch_all(&db.pool)
765 .await
766 .unwrap();
767
768 assert_eq!(rows.len(), 1);
769 assert!(
770 rows[0].1.starts_with("$argon2"),
771 "API key should be stored as Argon2 hash"
772 );
773 assert!(rows[0].2.is_some(), "API key prefix should be stored");
775 assert_eq!(rows[0].2.as_ref().unwrap().len(), 8);
776 }
777
778 #[tokio::test]
779 async fn test_create_default_library() {
780 let db = setup_db().await;
781 let user_id = db.create_user("key").await.unwrap();
782
783 let lib_id = db.get_default_library(&user_id).await.unwrap();
784 assert!(!lib_id.is_empty());
785
786 let (libraries, total) = db.list_libraries(&user_id, 10, 0).await.unwrap();
787 assert_eq!(total, 1);
788 assert_eq!(libraries[0].name, "default");
789 }
790
791 #[tokio::test]
792 async fn test_create_additional_library() {
793 let db = setup_db().await;
794 let user_id = db.create_user("key").await.unwrap();
795
796 let lib_id = db.create_library(&user_id, "work").await.unwrap();
797 assert!(!lib_id.is_empty());
798
799 let (_libraries, total) = db.list_libraries(&user_id, 10, 0).await.unwrap();
800 assert_eq!(total, 2);
801
802 let err = db.create_library(&user_id, "work").await.unwrap_err();
804 assert!(matches!(err, DbError::Conflict(_)));
805 }
806
807 #[tokio::test]
808 async fn test_invalid_library_names() {
809 let db = setup_db().await;
810 let user_id = db.create_user("key").await.unwrap();
811
812 assert!(db.create_library(&user_id, "").await.is_err());
814
815 assert!(db.create_library(&user_id, "my/lib").await.is_err());
817
818 assert!(db.create_library(&user_id, &"a".repeat(65)).await.is_err());
820
821 assert!(db.create_library(&user_id, "valid-name").await.is_ok());
823 assert!(db.create_library(&user_id, "valid_name").await.is_ok());
824 assert!(db.create_library(&user_id, "ValidName123").await.is_ok());
825 }
826
827 #[tokio::test]
828 async fn test_upsert_and_get_snippets() {
829 let db = setup_db().await;
830 let user_id = db.create_user("key").await.unwrap();
831 let lib_id = db.get_default_library(&user_id).await.unwrap();
832
833 let snippet = Snippet {
834 id: "snip-1".to_string(),
835 description: "Test snippet".to_string(),
836 command: "echo hello".to_string(),
837 tags: vec!["test".to_string()],
838 created_at: 100,
839 updated_at: 100,
840 device_id: "device-1".to_string(),
841 deleted: false,
842 encrypted: false,
843 };
844
845 db.upsert_snippet(&snippet, &user_id, &lib_id)
846 .await
847 .unwrap();
848
849 let (snippets, total) = db
850 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
851 .await
852 .unwrap();
853 assert_eq!(total, 1);
854 assert_eq!(snippets[0].description, "Test snippet");
855 assert_eq!(snippets[0].command, "echo hello");
856 assert_eq!(snippets[0].tags, vec!["test"]);
857 }
858
859 #[tokio::test]
864 async fn test_delta_query_includes_watermark_boundary_row() {
865 let db = setup_db().await;
866 let user_id = db.create_user("key").await.unwrap();
867 let lib_id = db.get_default_library(&user_id).await.unwrap();
868
869 let snippet = Snippet {
870 id: "boundary-snip".to_string(),
871 description: "Boundary".to_string(),
872 command: "echo boundary".to_string(),
873 tags: vec![],
874 created_at: 500,
875 updated_at: 500,
876 device_id: "device-1".to_string(),
877 deleted: false,
878 encrypted: false,
879 };
880 db.upsert_snippet(&snippet, &user_id, &lib_id)
881 .await
882 .unwrap();
883
884 let latest = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
885 assert_eq!(latest, 500);
886
887 let (snippets, total) = db
889 .get_snippets(&user_id, &lib_id, latest, 10, 0, false)
890 .await
891 .unwrap();
892 assert_eq!(total, 1, "row at exactly `since` must be returned");
893 assert_eq!(snippets[0].id, "boundary-snip");
894 }
895
896 #[tokio::test]
897 async fn test_delete_library_preserves_future_tombstone_timestamp() {
898 let db = setup_db().await;
899 let user_id = db.create_user("key").await.unwrap();
900 let library_id = db.create_library(&user_id, "future-delete").await.unwrap();
901 let future_timestamp = Utc::now().timestamp() + 100;
902
903 let snippet = Snippet {
904 id: "future-snippet".to_string(),
905 description: "desc".to_string(),
906 command: "echo hi".to_string(),
907 tags: vec![],
908 created_at: 0,
909 updated_at: future_timestamp,
910 device_id: "device".to_string(),
911 deleted: false,
912 encrypted: false,
913 };
914 db.upsert_snippet(&snippet, &user_id, &library_id)
915 .await
916 .unwrap();
917
918 db.delete_library(&user_id, &library_id).await.unwrap();
919
920 let (snippets, total) = db
921 .get_snippets(&user_id, &library_id, future_timestamp - 1, 10, 0, true)
922 .await
923 .unwrap();
924 assert_eq!(total, 1);
925 assert!(snippets[0].deleted);
926 assert_eq!(snippets[0].updated_at, future_timestamp);
927 }
928
929 #[tokio::test]
930 async fn test_upsert_updates_existing() {
931 let db = setup_db().await;
932 let user_id = db.create_user("key").await.unwrap();
933 let lib_id = db.get_default_library(&user_id).await.unwrap();
934
935 let snippet = Snippet {
936 id: "snip-1".to_string(),
937 description: "Original".to_string(),
938 command: "echo old".to_string(),
939 tags: vec![],
940 created_at: 100,
941 updated_at: 100,
942 device_id: "device-1".to_string(),
943 deleted: false,
944 encrypted: false,
945 };
946 db.upsert_snippet(&snippet, &user_id, &lib_id)
947 .await
948 .unwrap();
949
950 let updated = Snippet {
952 id: "snip-1".to_string(),
953 description: "Updated".to_string(),
954 command: "echo new".to_string(),
955 tags: vec!["updated".to_string()],
956 created_at: 100,
957 updated_at: 200,
958 device_id: "device-1".to_string(),
959 deleted: false,
960 encrypted: false,
961 };
962 db.upsert_snippet(&updated, &user_id, &lib_id)
963 .await
964 .unwrap();
965
966 let (snippets, _) = db
967 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
968 .await
969 .unwrap();
970 assert_eq!(snippets.len(), 1);
971 assert_eq!(snippets[0].description, "Updated");
972 assert_eq!(snippets[0].command, "echo new");
973 }
974
975 #[tokio::test]
976 async fn test_upsert_rejects_older_timestamp() {
977 let db = setup_db().await;
978 let user_id = db.create_user("key").await.unwrap();
979 let lib_id = db.get_default_library(&user_id).await.unwrap();
980
981 let snippet = Snippet {
983 id: "snip-1".to_string(),
984 description: "Newer".to_string(),
985 command: "echo newer".to_string(),
986 tags: vec![],
987 created_at: 100,
988 updated_at: 200,
989 device_id: "d".to_string(),
990 deleted: false,
991 encrypted: false,
992 };
993 db.upsert_snippet(&snippet, &user_id, &lib_id)
994 .await
995 .unwrap();
996
997 let older = Snippet {
999 id: "snip-1".to_string(),
1000 description: "Older".to_string(),
1001 command: "echo older".to_string(),
1002 tags: vec![],
1003 created_at: 100,
1004 updated_at: 100,
1005 device_id: "d".to_string(),
1006 deleted: false,
1007 encrypted: false,
1008 };
1009 db.upsert_snippet(&older, &user_id, &lib_id).await.unwrap();
1010
1011 let (snippets, _) = db
1013 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1014 .await
1015 .unwrap();
1016 assert_eq!(snippets.len(), 1);
1017 assert_eq!(snippets[0].description, "Newer");
1018 assert_eq!(snippets[0].command, "echo newer");
1019 }
1020
1021 #[tokio::test]
1022 async fn test_get_latest_timestamp() {
1023 let db = setup_db().await;
1024 let user_id = db.create_user("key").await.unwrap();
1025 let lib_id = db.get_default_library(&user_id).await.unwrap();
1026
1027 let ts = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
1029 assert_eq!(ts, 0);
1030
1031 let snippet = Snippet {
1032 id: "snip-1".to_string(),
1033 description: "Test".to_string(),
1034 command: "echo".to_string(),
1035 tags: vec![],
1036 created_at: 100,
1037 updated_at: 500,
1038 device_id: "d".to_string(),
1039 deleted: false,
1040 encrypted: false,
1041 };
1042 db.upsert_snippet(&snippet, &user_id, &lib_id)
1043 .await
1044 .unwrap();
1045
1046 let ts = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
1047 assert_eq!(ts, 500);
1048 }
1049
1050 #[tokio::test]
1051 async fn test_delete_library() {
1052 let db = setup_db().await;
1053 let user_id = db.create_user("key").await.unwrap();
1054 let lib_id = db.create_library(&user_id, "temp").await.unwrap();
1055
1056 let snippet = Snippet {
1058 id: "snip-1".to_string(),
1059 description: "Test".to_string(),
1060 command: "echo".to_string(),
1061 tags: vec![],
1062 created_at: 100,
1063 updated_at: 100,
1064 device_id: "d".to_string(),
1065 deleted: false,
1066 encrypted: false,
1067 };
1068 db.upsert_snippet(&snippet, &user_id, &lib_id)
1069 .await
1070 .unwrap();
1071
1072 db.delete_library(&user_id, &lib_id).await.unwrap();
1074
1075 assert!(db.delete_library(&user_id, &lib_id).await.is_err());
1077
1078 let (snippets, _) = db
1080 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1081 .await
1082 .unwrap();
1083 assert_eq!(snippets.len(), 0);
1084 }
1085
1086 #[tokio::test]
1087 async fn test_migrate_plaintext_api_keys() {
1088 let db = setup_db().await;
1089 let user_id = Uuid::new_v4().to_string();
1090 let now = Utc::now().timestamp();
1091
1092 sqlx::query("INSERT INTO users (id, api_key, created_at, updated_at) VALUES (?, ?, ?, ?)")
1094 .bind(&user_id)
1095 .bind("plaintext-api-key")
1096 .bind(now)
1097 .bind(now)
1098 .execute(&db.pool)
1099 .await
1100 .unwrap();
1101
1102 let migrated = db.migrate_plaintext_api_keys().await.unwrap();
1104 assert_eq!(migrated, 1);
1105
1106 let (stored_hash,): (String,) = sqlx::query_as("SELECT api_key FROM users WHERE id = ?")
1108 .bind(&user_id)
1109 .fetch_one(&db.pool)
1110 .await
1111 .unwrap();
1112
1113 assert!(stored_hash.starts_with("$argon2"));
1114
1115 let (prefix,): (Option<String>,) =
1117 sqlx::query_as("SELECT api_key_prefix FROM users WHERE id = ?")
1118 .bind(&user_id)
1119 .fetch_one(&db.pool)
1120 .await
1121 .unwrap();
1122 assert!(
1123 prefix.is_some(),
1124 "API key prefix should be backfilled by migration"
1125 );
1126
1127 let found = db.get_user_by_api_key("plaintext-api-key").await.unwrap();
1129 assert_eq!(found, Some(user_id));
1130
1131 let migrated_again = db.migrate_plaintext_api_keys().await.unwrap();
1133 assert_eq!(migrated_again, 0);
1134 }
1135
1136 #[tokio::test]
1137 async fn test_api_key_prefix_lookup() {
1138 let db = setup_db().await;
1139
1140 let user1 = db.create_user("key-one-alpha").await.unwrap();
1142 let user2 = db.create_user("key-two-beta").await.unwrap();
1143 let user3 = db.create_user("key-three-gamma").await.unwrap();
1144
1145 assert_eq!(
1147 db.get_user_by_api_key("key-one-alpha").await.unwrap(),
1148 Some(user1)
1149 );
1150 assert_eq!(
1151 db.get_user_by_api_key("key-two-beta").await.unwrap(),
1152 Some(user2)
1153 );
1154 assert_eq!(
1155 db.get_user_by_api_key("key-three-gamma").await.unwrap(),
1156 Some(user3)
1157 );
1158 assert_eq!(db.get_user_by_api_key("nonexistent").await.unwrap(), None);
1159
1160 let rows: Vec<(String,)> =
1162 sqlx::query_as("SELECT api_key_prefix FROM users WHERE api_key_prefix IS NOT NULL")
1163 .fetch_all(&db.pool)
1164 .await
1165 .unwrap();
1166 assert_eq!(rows.len(), 3);
1167 }
1168
1169 #[tokio::test]
1170 async fn test_library_ownership_verification() {
1171 let db = setup_db().await;
1172 let user1_id = db.create_user("user1-api-key").await.unwrap();
1173 let user2_id = db.create_user("user2-api-key").await.unwrap();
1174 let lib_id = db.create_library(&user1_id, "private").await.unwrap();
1175
1176 assert!(
1177 db.verify_library_ownership(&user1_id, &lib_id)
1178 .await
1179 .unwrap()
1180 );
1181 assert!(
1182 !db.verify_library_ownership(&user2_id, &lib_id)
1183 .await
1184 .unwrap()
1185 );
1186
1187 let nonexistent_lib = Uuid::new_v4().to_string();
1188 assert!(
1189 !db.verify_library_ownership(&user1_id, &nonexistent_lib)
1190 .await
1191 .unwrap()
1192 );
1193 }
1194
1195 #[tokio::test]
1196 async fn test_library_ownership_prevents_cross_user_access() {
1197 let db = setup_db().await;
1198 let user1_id = db.create_user("user1-key").await.unwrap();
1199 let user2_id = db.create_user("user2-key").await.unwrap();
1200 let user1_lib = db.create_library(&user1_id, "my-library").await.unwrap();
1201
1202 let snippet = Snippet {
1203 id: "snip-1".to_string(),
1204 description: "User 1 secret".to_string(),
1205 command: "secret command".to_string(),
1206 tags: vec![],
1207 created_at: 100,
1208 updated_at: 100,
1209 device_id: "device-1".to_string(),
1210 deleted: false,
1211 encrypted: false,
1212 };
1213
1214 db.upsert_snippet(&snippet, &user1_id, &user1_lib)
1215 .await
1216 .unwrap();
1217
1218 let (snippets, _) = db
1219 .get_snippets(&user1_id, &user1_lib, 0, 10, 0, false)
1220 .await
1221 .unwrap();
1222 assert_eq!(snippets.len(), 1);
1223
1224 let (snippets, _) = db
1225 .get_snippets(&user2_id, &user1_lib, 0, 10, 0, false)
1226 .await
1227 .unwrap();
1228 assert_eq!(snippets.len(), 0);
1229 }
1230
1231 #[tokio::test]
1232 async fn test_deleted_library_not_owned() {
1233 let db = setup_db().await;
1234 let user_id = db.create_user("test-key").await.unwrap();
1235 let lib_id = db.create_library(&user_id, "temp-lib").await.unwrap();
1236
1237 assert!(
1238 db.verify_library_ownership(&user_id, &lib_id)
1239 .await
1240 .unwrap()
1241 );
1242
1243 db.delete_library(&user_id, &lib_id).await.unwrap();
1244
1245 assert!(
1246 !db.verify_library_ownership(&user_id, &lib_id)
1247 .await
1248 .unwrap()
1249 );
1250 }
1251
1252 #[tokio::test]
1253 async fn test_get_snippets_include_deleted() {
1254 let db = setup_db().await;
1255 let user_id = db.create_user("key").await.unwrap();
1256 let lib_id = db.get_default_library(&user_id).await.unwrap();
1257
1258 let snippet = Snippet {
1259 id: "snip-1".to_string(),
1260 description: "Test".to_string(),
1261 command: "echo".to_string(),
1262 tags: vec![],
1263 created_at: 100,
1264 updated_at: 100,
1265 device_id: "d".to_string(),
1266 deleted: false,
1267 encrypted: false,
1268 };
1269 db.upsert_snippet(&snippet, &user_id, &lib_id)
1270 .await
1271 .unwrap();
1272
1273 let deleted = Snippet {
1275 id: "snip-1".to_string(),
1276 description: "Test".to_string(),
1277 command: "echo".to_string(),
1278 tags: vec![],
1279 created_at: 100,
1280 updated_at: 200,
1281 device_id: "d".to_string(),
1282 deleted: true,
1283 encrypted: false,
1284 };
1285 db.upsert_snippet(&deleted, &user_id, &lib_id)
1286 .await
1287 .unwrap();
1288
1289 let (snippets, _) = db
1291 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1292 .await
1293 .unwrap();
1294 assert_eq!(snippets.len(), 0);
1295
1296 let (snippets, _) = db
1298 .get_snippets(&user_id, &lib_id, 0, 10, 0, true)
1299 .await
1300 .unwrap();
1301 assert_eq!(snippets.len(), 1);
1302 assert!(snippets[0].deleted);
1303 assert_eq!(snippets[0].updated_at, 200);
1304 }
1305}