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