1use argon2::{
2 Algorithm, Argon2, Params, Version,
3 password_hash::{PasswordHasher, PasswordVerifier, phc::PasswordHash},
4};
5use base64::Engine;
6use sha2::{Digest, Sha256};
7use sqlx::AssertSqlSafe;
8use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
9use std::path::{Path, PathBuf};
10use std::str::FromStr;
11use std::time::Duration;
12use thiserror::Error;
13use time::OffsetDateTime;
14use uuid::Uuid;
15
16const ARGON2_MEMORY_KIB: u32 = 1 << 14; const ARGON2_ITERATIONS: u32 = 3;
18const ARGON2_PARALLELISM: u32 = 4;
19
20#[derive(Debug, Error)]
21pub enum DbError {
22 #[error("Database error: {0}")]
23 Database(#[from] sqlx::Error),
24 #[error("Not found: {0}")]
25 NotFound(String),
26 #[error("Conflict: {0}")]
27 Conflict(String),
28 #[error("Validation error: {0}")]
29 Validation(String),
30 #[error("Internal error: {0}")]
31 Internal(String),
32}
33
34pub type DbResult<T> = Result<T, DbError>;
35
36#[derive(Debug, Clone, Copy, PartialEq, Eq)]
44pub enum UpsertOutcome {
45 Written,
47 Stale,
50 ForeignScope,
54}
55
56#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
57pub struct Snippet {
58 pub id: String,
59 pub description: String,
60 pub command: String,
61 pub tags: Vec<String>,
62 pub created_at: i64,
63 pub updated_at: i64,
64 pub device_id: String,
65 pub deleted: bool,
66 pub encrypted: bool,
67}
68
69#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
70pub struct Library {
71 pub id: String,
72 pub name: String,
73 pub created_at: i64,
74 pub snippet_count: i64,
75}
76
77#[derive(Debug, Clone)]
79pub struct SnippetPage {
80 pub snippets: Vec<Snippet>,
82 pub has_more: bool,
84 pub total: i32,
86}
87
88#[derive(Debug, Clone, Copy, PartialEq, Eq)]
97pub enum DeltaCursor {
98 Timestamp(i64),
100 Sequence(i64),
102}
103
104impl DeltaCursor {
105 fn predicate(self) -> (&'static str, i64) {
110 match self {
111 DeltaCursor::Timestamp(value) => ("AND visible_at >= ?", value),
112 DeltaCursor::Sequence(value) => ("AND sequence >= ?", value),
113 }
114 }
115}
116
117#[derive(Clone)]
118pub struct Database {
119 pool: SqlitePool,
120}
121
122fn hash_api_key(api_key: &str) -> DbResult<String> {
123 let mut salt_bytes = [0u8; 16];
124 getrandom::fill(&mut salt_bytes)
125 .map_err(|e| DbError::Internal(format!("Failed to generate salt: {}", e)))?;
126 let params = Params::new(
127 ARGON2_MEMORY_KIB,
128 ARGON2_ITERATIONS,
129 ARGON2_PARALLELISM,
130 None,
131 )
132 .map_err(|e| DbError::Internal(format!("Invalid Argon2 params: {}", e)))?;
133 let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
134 let hash = argon2
135 .hash_password_with_salt(api_key.as_bytes(), &salt_bytes)
136 .map_err(|e| DbError::Internal(format!("Failed to hash API key: {}", e)))?
137 .to_string();
138 Ok(hash)
139}
140
141fn verify_api_key(api_key: &str, hash: &str) -> bool {
142 let parsed = match PasswordHash::new(hash) {
143 Ok(p) => p,
144 Err(_) => return false,
145 };
146 let params = match Params::new(
147 ARGON2_MEMORY_KIB,
148 ARGON2_ITERATIONS,
149 ARGON2_PARALLELISM,
150 None,
151 ) {
152 Ok(p) => p,
153 Err(_) => return false,
154 };
155 let argon2 = Argon2::new(Algorithm::Argon2id, Version::V0x13, params);
156 argon2.verify_password(api_key.as_bytes(), &parsed).is_ok()
157}
158
159fn compute_api_key_prefix(api_key: &str) -> String {
161 let hash = Sha256::digest(api_key.as_bytes());
162 let encoded = base64::engine::general_purpose::STANDARD_NO_PAD.encode(hash);
163 encoded.chars().take(8).collect()
164}
165
166fn derive_prefix_from_hash(stored_hash: &str) -> String {
172 let hash = Sha256::digest(stored_hash.as_bytes());
173 let encoded = base64::engine::general_purpose::STANDARD_NO_PAD.encode(hash);
174 encoded.chars().take(8).collect()
175}
176
177fn saturating_i32(v: i64) -> i32 {
178 v.min(i32::MAX as i64) as i32
179}
180
181type SnippetRow = (String, String, String, String, i64, i64, String, i32, i32);
182
183async fn count_snippets(
190 conn: &mut sqlx::SqliteConnection,
191 user_id: &str,
192 library_id: &str,
193 cursor: DeltaCursor,
194 include_deleted: bool,
195) -> DbResult<i32> {
196 let (predicate, cursor_value) = cursor.predicate();
197 let sql = AssertSqlSafe(format!(
201 "SELECT COUNT(*) FROM snippets \
202 WHERE user_id = ? \
203 AND library_id = ? \
204 {predicate} \
205 AND (? OR deleted = 0)"
206 ));
207 let total: (i64,) = sqlx::query_as(sql)
208 .bind(user_id)
209 .bind(library_id)
210 .bind(cursor_value)
211 .bind(include_deleted)
212 .fetch_one(&mut *conn)
213 .await?;
214
215 Ok(saturating_i32(total.0))
216}
217
218async fn fetch_snippets(
220 conn: &mut sqlx::SqliteConnection,
221 user_id: &str,
222 library_id: &str,
223 cursor: DeltaCursor,
224 limit: i32,
225 offset: i32,
226 include_deleted: bool,
227) -> DbResult<Vec<Snippet>> {
228 let (predicate, cursor_value) = cursor.predicate();
229 let sql = AssertSqlSafe(format!(
231 "SELECT id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted \
232 FROM snippets \
233 WHERE user_id = ? \
234 AND library_id = ? \
235 {predicate} \
236 AND (? OR deleted = 0) \
237 ORDER BY updated_at DESC, id DESC \
238 LIMIT ? OFFSET ?"
239 ));
240 let rows: Vec<SnippetRow> = sqlx::query_as(sql)
241 .bind(user_id)
242 .bind(library_id)
243 .bind(cursor_value)
244 .bind(include_deleted)
245 .bind(limit)
246 .bind(offset)
247 .fetch_all(&mut *conn)
248 .await?;
249
250 rows.into_iter()
251 .map(
252 |(
253 id,
254 description,
255 command,
256 tags_str,
257 created_at,
258 updated_at,
259 device_id,
260 deleted,
261 encrypted,
262 )| {
263 let tags: Vec<String> = serde_json::from_str(&tags_str).map_err(|e| {
266 DbError::Internal(format!(
267 "corrupt tags JSON for snippet {id} (row must be repaired): {e}"
268 ))
269 })?;
270 Ok(Snippet {
271 id,
272 description,
273 command,
274 tags,
275 created_at,
276 updated_at,
277 device_id,
278 deleted: deleted != 0,
279 encrypted: encrypted != 0,
280 })
281 },
282 )
283 .collect::<DbResult<Vec<Snippet>>>()
284}
285
286const MIGRATION_BATCH_SIZE: i64 = 100;
287
288async fn bump_library_sequence(
295 tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
296 library_id: &str,
297) -> DbResult<i64> {
298 sqlx::query(
299 "INSERT INTO library_sequences (library_id, sequence) VALUES (?, 0) \
300 ON CONFLICT(library_id) DO NOTHING",
301 )
302 .bind(library_id)
303 .execute(&mut **tx)
304 .await?;
305
306 let (sequence,): (i64,) = sqlx::query_as(
307 "UPDATE library_sequences SET sequence = sequence + 1 WHERE library_id = ? RETURNING sequence",
308 )
309 .bind(library_id)
310 .fetch_one(&mut **tx)
311 .await?;
312
313 Ok(sequence)
314}
315
316#[cfg(unix)]
326fn prepare_data_directory(path: &Path) -> std::io::Result<()> {
327 use std::os::unix::fs::{DirBuilderExt, PermissionsExt};
328
329 let existed = path.exists();
330 std::fs::DirBuilder::new()
331 .recursive(true)
332 .mode(0o700)
333 .create(path)?;
334
335 if existed {
336 let mode = std::fs::metadata(path)?.permissions().mode() & 0o777;
337 if mode & 0o077 != 0 {
338 match std::fs::set_permissions(path, std::fs::Permissions::from_mode(0o700)) {
339 Ok(()) => tracing::info!(
340 "Tightened server data directory {} from mode {:#05o} to 0o700",
341 path.display(),
342 mode
343 ),
344 Err(error) => tracing::warn!(
345 "Server data directory {} is accessible to other local users \
346 (mode {:#05o}) and could not be tightened to 0o700: {}",
347 path.display(),
348 mode,
349 error
350 ),
351 }
352 }
353 }
354 Ok(())
355}
356
357#[cfg(not(unix))]
358fn prepare_data_directory(path: &Path) -> std::io::Result<()> {
359 std::fs::create_dir_all(path)
361}
362
363#[cfg(unix)]
369fn restrict_database_files(filename: &Path) {
370 use std::os::unix::fs::PermissionsExt;
371
372 let mut targets = vec![filename.to_path_buf()];
373 for suffix in ["-wal", "-shm"] {
374 let mut sibling = filename.as_os_str().to_owned();
375 sibling.push(suffix);
376 targets.push(PathBuf::from(sibling));
377 }
378
379 for target in targets {
380 let mode = match std::fs::metadata(&target) {
381 Ok(metadata) => metadata.permissions().mode() & 0o777,
382 Err(error) if error.kind() == std::io::ErrorKind::NotFound => continue,
385 Err(error) => {
386 tracing::debug!(
387 "Unable to inspect database file {}: {}",
388 target.display(),
389 error
390 );
391 continue;
392 }
393 };
394 if mode & 0o077 == 0 {
395 continue;
396 }
397 if let Err(error) =
398 std::fs::set_permissions(&target, std::fs::Permissions::from_mode(0o600))
399 {
400 tracing::warn!(
401 "Database file {} is accessible to other local users (mode {:#05o}) \
402 and could not be tightened to 0o600: {}",
403 target.display(),
404 mode,
405 error
406 );
407 }
408 }
409}
410
411#[cfg(not(unix))]
412fn restrict_database_files(_filename: &Path) {}
413
414impl Database {
415 pub async fn connect(url: &str, max_connections: u32) -> DbResult<Self> {
416 let options = if url.starts_with("sqlite:") {
422 SqliteConnectOptions::from_str(url)?
423 } else {
424 SqliteConnectOptions::new().filename(url)
425 }
426 .create_if_missing(true)
427 .busy_timeout(Duration::from_secs(5));
428
429 let filename = options.get_filename().to_path_buf();
430 if filename != Path::new(":memory:")
431 && let Some(parent) = filename.parent()
432 && !parent.as_os_str().is_empty()
433 {
434 prepare_data_directory(parent).map_err(|e| {
435 DbError::Internal(format!(
436 "Failed to create database directory {}: {}",
437 parent.display(),
438 e
439 ))
440 })?;
441 }
442
443 let pool = SqlitePoolOptions::new()
450 .max_connections(max_connections)
451 .after_connect(|conn, _meta| {
452 Box::pin(async move {
453 sqlx::query("PRAGMA foreign_keys=ON").execute(conn).await?;
454 Ok(())
455 })
456 })
457 .connect_with(options)
458 .await?;
459
460 sqlx::query("PRAGMA journal_mode=WAL")
461 .execute(&pool)
462 .await?;
463
464 sqlx::query(
465 "
466 CREATE TABLE IF NOT EXISTS users (
467 id TEXT PRIMARY KEY,
468 api_key TEXT UNIQUE NOT NULL,
469 api_key_prefix TEXT,
470 created_at INTEGER NOT NULL,
471 updated_at INTEGER NOT NULL
472 )
473 ",
474 )
475 .execute(&pool)
476 .await?;
477
478 sqlx::query("CREATE INDEX IF NOT EXISTS idx_users_api_key_prefix ON users(api_key_prefix)")
479 .execute(&pool)
480 .await?;
481
482 sqlx::query(
483 "
484 CREATE TABLE IF NOT EXISTS libraries (
485 id TEXT PRIMARY KEY,
486 user_id TEXT NOT NULL,
487 name TEXT NOT NULL,
488 created_at INTEGER NOT NULL,
489 deleted_at INTEGER,
490 FOREIGN KEY (user_id) REFERENCES users(id),
491 UNIQUE(user_id, name)
492 )
493 ",
494 )
495 .execute(&pool)
496 .await?;
497
498 sqlx::query(
499 "
500 CREATE TABLE IF NOT EXISTS snippets (
501 id TEXT PRIMARY KEY,
502 user_id TEXT NOT NULL,
503 library_id TEXT NOT NULL,
504 description TEXT NOT NULL,
505 command TEXT NOT NULL,
506 tags TEXT NOT NULL DEFAULT '[]',
507 created_at INTEGER NOT NULL,
508 updated_at INTEGER NOT NULL,
509 device_id TEXT NOT NULL,
510 deleted INTEGER NOT NULL DEFAULT 0,
511 encrypted INTEGER NOT NULL DEFAULT 0,
512 visible_at INTEGER NOT NULL DEFAULT 0,
513 sequence INTEGER NOT NULL DEFAULT 0,
514 FOREIGN KEY (user_id) REFERENCES users(id),
515 FOREIGN KEY (library_id) REFERENCES libraries(id)
516 )
517 ",
518 )
519 .execute(&pool)
520 .await?;
521
522 sqlx::query(
527 "
528 CREATE TABLE IF NOT EXISTS library_sequences (
529 library_id TEXT PRIMARY KEY,
530 sequence INTEGER NOT NULL DEFAULT 0
531 )
532 ",
533 )
534 .execute(&pool)
535 .await?;
536
537 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_user ON snippets(user_id)")
538 .execute(&pool)
539 .await?;
540 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_library ON snippets(library_id)")
541 .execute(&pool)
542 .await?;
543 sqlx::query("CREATE INDEX IF NOT EXISTS idx_snippets_updated ON snippets(updated_at)")
544 .execute(&pool)
545 .await?;
546 sqlx::query(
547 "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_updated \
548 ON snippets(user_id, library_id, updated_at, deleted)",
549 )
550 .execute(&pool)
551 .await?;
552 sqlx::query(
555 "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_visible \
556 ON snippets(user_id, library_id, visible_at, deleted)",
557 )
558 .execute(&pool)
559 .await?;
560 sqlx::query("CREATE INDEX IF NOT EXISTS idx_libraries_user ON libraries(user_id)")
561 .execute(&pool)
562 .await?;
563
564 let db = Self { pool };
571 db.migrate_snippet_sequence().await?;
572
573 restrict_database_files(&filename);
575
576 Ok(db)
577 }
578
579 pub async fn ping(&self) -> DbResult<()> {
580 sqlx::query("SELECT 1").execute(&self.pool).await?;
581 Ok(())
582 }
583
584 pub fn pool(&self) -> &SqlitePool {
585 &self.pool
586 }
587
588 pub async fn create_user(&self, api_key: &str) -> DbResult<String> {
589 let user_id = Uuid::new_v4().to_string();
590 let now = OffsetDateTime::now_utc().unix_timestamp();
591 let api_key_hash = hash_api_key(api_key)?;
592 let api_key_prefix = compute_api_key_prefix(api_key);
593
594 let mut tx = self.pool.begin().await?;
595
596 sqlx::query("INSERT INTO users (id, api_key, api_key_prefix, created_at, updated_at) VALUES (?, ?, ?, ?, ?)")
597 .bind(&user_id)
598 .bind(&api_key_hash)
599 .bind(&api_key_prefix)
600 .bind(now)
601 .bind(now)
602 .execute(&mut *tx)
603 .await?;
604
605 let default_lib_id = Uuid::new_v4().to_string();
606 sqlx::query("INSERT INTO libraries (id, user_id, name, created_at) VALUES (?, ?, ?, ?)")
607 .bind(&default_lib_id)
608 .bind(&user_id)
609 .bind("default")
610 .bind(now)
611 .execute(&mut *tx)
612 .await?;
613
614 tx.commit().await?;
615
616 tracing::info!(
617 "Created user {} with default library {}",
618 user_id,
619 default_lib_id
620 );
621
622 Ok(user_id)
623 }
624
625 pub async fn get_user_by_api_key(&self, api_key: &str) -> DbResult<Option<String>> {
626 let prefix = compute_api_key_prefix(api_key);
627
628 let rows: Vec<(String, String)> =
633 sqlx::query_as("SELECT id, api_key FROM users WHERE api_key_prefix = ?")
634 .bind(&prefix)
635 .fetch_all(&self.pool)
636 .await?;
637
638 for (user_id, stored_hash) in rows {
639 if verify_api_key(api_key, &stored_hash) {
640 return Ok(Some(user_id));
641 }
642 }
643
644 Ok(None)
645 }
646
647 pub async fn create_library(&self, user_id: &str, name: &str) -> DbResult<String> {
648 if !name
649 .chars()
650 .all(|c| c.is_alphanumeric() || c == '-' || c == '_')
651 {
652 return Err(DbError::Validation(
653 "Invalid library name. Use only letters, numbers, dash, and underscore."
654 .to_string(),
655 ));
656 }
657 if name.is_empty() || name.len() > 64 {
658 return Err(DbError::Validation(
659 "Library name must be 1-64 characters".to_string(),
660 ));
661 }
662
663 let lib_id = Uuid::new_v4().to_string();
664 let now = OffsetDateTime::now_utc().unix_timestamp();
665
666 let result = sqlx::query(
667 "INSERT OR IGNORE INTO libraries (id, user_id, name, created_at) VALUES (?, ?, ?, ?)",
668 )
669 .bind(&lib_id)
670 .bind(user_id)
671 .bind(name)
672 .bind(now)
673 .execute(&self.pool)
674 .await?;
675
676 if result.rows_affected() == 0 {
677 return Err(DbError::Conflict(format!(
678 "Library '{}' already exists",
679 name
680 )));
681 }
682
683 Ok(lib_id)
684 }
685
686 pub async fn list_libraries(
687 &self,
688 user_id: &str,
689 limit: i32,
690 offset: i32,
691 ) -> DbResult<(Vec<Library>, i32)> {
692 let total: (i64,) = sqlx::query_as(
693 "SELECT COUNT(*) FROM libraries WHERE user_id = ? AND deleted_at IS NULL",
694 )
695 .bind(user_id)
696 .fetch_one(&self.pool)
697 .await?;
698
699 let rows: Vec<(String, String, i64, i64)> = sqlx::query_as(
700 "SELECT l.id, l.name, l.created_at, COUNT(s.id) as snippet_count \
701 FROM libraries l \
702 LEFT JOIN snippets s ON s.library_id = l.id AND s.deleted = 0 \
703 WHERE l.user_id = ? AND l.deleted_at IS NULL \
704 GROUP BY l.id \
705 ORDER BY l.name LIMIT ? OFFSET ?",
706 )
707 .bind(user_id)
708 .bind(limit)
709 .bind(offset)
710 .fetch_all(&self.pool)
711 .await?;
712
713 let libraries = rows
714 .into_iter()
715 .map(|(id, name, created_at, snippet_count)| Library {
716 id,
717 name,
718 created_at,
719 snippet_count,
720 })
721 .collect();
722
723 Ok((libraries, saturating_i32(total.0)))
724 }
725
726 pub async fn delete_library(&self, user_id: &str, library_id: &str) -> DbResult<()> {
727 let now = OffsetDateTime::now_utc().unix_timestamp();
728 let mut tx = self.pool.begin().await?;
729
730 let result = sqlx::query(
731 "UPDATE libraries SET deleted_at = ? WHERE id = ? AND user_id = ? AND deleted_at IS NULL",
732 )
733 .bind(now)
734 .bind(library_id)
735 .bind(user_id)
736 .execute(&mut *tx)
737 .await?;
738
739 if result.rows_affected() == 0 {
740 tx.rollback().await?;
741 return Err(DbError::NotFound(
742 "Library not found or already deleted".to_string(),
743 ));
744 }
745
746 let rows: Vec<(String,)> = sqlx::query_as(
750 "SELECT id FROM snippets WHERE library_id = ? AND user_id = ? AND deleted = 0",
751 )
752 .bind(library_id)
753 .bind(user_id)
754 .fetch_all(&mut *tx)
755 .await?;
756
757 for (id,) in rows {
758 let sequence = bump_library_sequence(&mut tx, library_id).await?;
759 sqlx::query(
760 "UPDATE snippets
761 SET deleted = 1, updated_at = MAX(updated_at, ?), visible_at = MAX(visible_at, ?), sequence = ?
762 WHERE id = ?",
763 )
764 .bind(now)
765 .bind(now)
766 .bind(sequence)
767 .bind(&id)
768 .execute(&mut *tx)
769 .await?;
770 }
771
772 tx.commit().await?;
773 Ok(())
774 }
775
776 pub async fn get_default_library(&self, user_id: &str) -> DbResult<String> {
777 let (lib_id,): (String,) = sqlx::query_as(
778 "SELECT id FROM libraries WHERE user_id = ? AND name = 'default' AND deleted_at IS NULL",
779 )
780 .bind(user_id)
781 .fetch_optional(&self.pool)
782 .await?
783 .ok_or_else(|| DbError::NotFound("Default library not found".to_string()))?;
784
785 Ok(lib_id)
786 }
787
788 pub async fn verify_library_ownership(
789 &self,
790 user_id: &str,
791 library_id: &str,
792 ) -> DbResult<bool> {
793 let (count,): (i64,) = sqlx::query_as(
794 "SELECT COUNT(*) FROM libraries WHERE id = ? AND user_id = ? AND deleted_at IS NULL",
795 )
796 .bind(library_id)
797 .bind(user_id)
798 .fetch_one(&self.pool)
799 .await?;
800
801 Ok(count > 0)
802 }
803
804 pub async fn get_snippets(
805 &self,
806 user_id: &str,
807 library_id: &str,
808 since: i64,
809 limit: i32,
810 offset: i32,
811 include_deleted: bool,
812 ) -> DbResult<(Vec<Snippet>, i32)> {
813 let page = self
816 .get_snippets_page(
817 user_id,
818 library_id,
819 DeltaCursor::Timestamp(since),
820 limit,
821 offset,
822 include_deleted,
823 )
824 .await?;
825 Ok((page.snippets, page.total))
826 }
827
828 pub async fn get_snippets_page(
839 &self,
840 user_id: &str,
841 library_id: &str,
842 cursor: DeltaCursor,
843 limit: i32,
844 offset: i32,
845 include_deleted: bool,
846 ) -> DbResult<SnippetPage> {
847 let mut tx = self.pool.begin().await?;
848
849 let page_limit = limit.max(0);
850 let mut snippets = fetch_snippets(
851 &mut tx,
852 user_id,
853 library_id,
854 cursor,
855 page_limit.saturating_add(1),
856 offset,
857 include_deleted,
858 )
859 .await?;
860
861 let has_more = snippets.len() > page_limit as usize;
862 snippets.truncate(page_limit as usize);
863
864 let total = if !has_more && (!snippets.is_empty() || offset == 0) {
865 offset.saturating_add(snippets.len() as i32)
866 } else {
867 count_snippets(&mut tx, user_id, library_id, cursor, include_deleted).await?
868 };
869
870 tx.commit().await?;
871
872 Ok(SnippetPage {
873 snippets,
874 has_more,
875 total,
876 })
877 }
878
879 #[cfg(test)]
880 pub async fn upsert_snippet(
881 &self,
882 snippet: &Snippet,
883 user_id: &str,
884 library_id: &str,
885 ) -> DbResult<()> {
886 let mut tx = self.pool.begin().await?;
889 self.upsert_snippet_in_tx(&mut tx, snippet, user_id, library_id)
890 .await?;
891 tx.commit().await?;
892 Ok(())
893 }
894
895 pub async fn upsert_snippet_in_tx(
896 &self,
897 tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
898 snippet: &Snippet,
899 user_id: &str,
900 library_id: &str,
901 ) -> DbResult<UpsertOutcome> {
902 let tags_json = serde_json::to_string(&snippet.tags).unwrap_or_else(|_| "[]".to_string());
903 let deleted = snippet.deleted as i32;
904 let encrypted = snippet.encrypted as i32;
905
906 let sequence = bump_library_sequence(tx, library_id).await?;
913
914 let (watermark,): (i64,) = sqlx::query_as(
923 "SELECT COALESCE(MAX(visible_at), 0) FROM snippets WHERE user_id = ? AND library_id = ?",
924 )
925 .bind(user_id)
926 .bind(library_id)
927 .fetch_one(&mut **tx)
928 .await?;
929 let visible_at = snippet.updated_at.max(watermark);
930
931 let result = sqlx::query(
942 "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted, visible_at, sequence)
943 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
944 ON CONFLICT(id) DO UPDATE SET
945 description = excluded.description,
946 command = excluded.command,
947 tags = excluded.tags,
948 updated_at = excluded.updated_at,
949 device_id = excluded.device_id,
950 deleted = excluded.deleted,
951 encrypted = excluded.encrypted,
952 visible_at = MAX(snippets.visible_at, excluded.visible_at),
953 sequence = excluded.sequence
954 WHERE excluded.user_id = snippets.user_id AND excluded.library_id = snippets.library_id \
955 AND (excluded.updated_at > snippets.updated_at \
956 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id > snippets.device_id) \
957 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id = snippets.device_id AND excluded.command > snippets.command) \
958 OR (excluded.updated_at = snippets.updated_at AND excluded.device_id = snippets.device_id AND excluded.command = snippets.command AND excluded.description > snippets.description))",
959 )
960 .bind(&snippet.id)
961 .bind(user_id)
962 .bind(library_id)
963 .bind(&snippet.description)
964 .bind(&snippet.command)
965 .bind(&tags_json)
966 .bind(snippet.created_at)
967 .bind(snippet.updated_at)
968 .bind(&snippet.device_id)
969 .bind(deleted)
970 .bind(encrypted)
971 .bind(visible_at)
972 .bind(sequence)
973 .execute(&mut **tx)
974 .await?;
975
976 if result.rows_affected() > 0 {
977 return Ok(UpsertOutcome::Written);
978 }
979
980 let foreign: Option<(String, String)> = sqlx::query_as(
985 "SELECT user_id, library_id FROM snippets \
986 WHERE id = ? AND (user_id != ? OR library_id != ?) LIMIT 1",
987 )
988 .bind(&snippet.id)
989 .bind(user_id)
990 .bind(library_id)
991 .fetch_optional(&mut **tx)
992 .await?;
993
994 Ok(match foreign {
995 Some(_) => UpsertOutcome::ForeignScope,
996 None => UpsertOutcome::Stale,
997 })
998 }
999
1000 pub async fn get_latest_timestamp(&self, user_id: &str, library_id: &str) -> DbResult<i64> {
1001 let (timestamp,): (i64,) = sqlx::query_as(
1005 "SELECT COALESCE(MAX(visible_at), 0) FROM snippets WHERE user_id = ? AND library_id = ?",
1006 )
1007 .bind(user_id)
1008 .bind(library_id)
1009 .fetch_one(&self.pool)
1010 .await?;
1011
1012 Ok(timestamp)
1013 }
1014
1015 pub async fn get_latest_sequence(&self, user_id: &str, library_id: &str) -> DbResult<i64> {
1021 let (sequence,): (i64,) = sqlx::query_as(
1022 "SELECT COALESCE(MAX(sequence), 0) FROM snippets WHERE user_id = ? AND library_id = ?",
1023 )
1024 .bind(user_id)
1025 .bind(library_id)
1026 .fetch_one(&self.pool)
1027 .await?;
1028
1029 Ok(sequence)
1030 }
1031
1032 pub async fn migrate_snippet_sequence(&self) -> DbResult<bool> {
1045 let columns: Vec<String> =
1046 sqlx::query_scalar("SELECT name FROM pragma_table_info('snippets')")
1047 .fetch_all(&self.pool)
1048 .await?;
1049
1050 let migrated = if columns.iter().any(|name| name == "sequence") {
1051 false
1052 } else {
1053 sqlx::query("ALTER TABLE snippets ADD COLUMN sequence INTEGER NOT NULL DEFAULT 0")
1054 .execute(&self.pool)
1055 .await?;
1056 sqlx::query(
1061 "WITH ranked AS (
1062 SELECT id,
1063 ROW_NUMBER() OVER (
1064 PARTITION BY user_id, library_id
1065 ORDER BY updated_at ASC, id ASC
1066 ) AS rn
1067 FROM snippets
1068 )
1069 UPDATE snippets
1070 SET sequence = (SELECT rn FROM ranked WHERE ranked.id = snippets.id)",
1071 )
1072 .execute(&self.pool)
1073 .await?;
1074 true
1075 };
1076
1077 sqlx::query(
1082 "INSERT INTO library_sequences (library_id, sequence)
1083 SELECT library_id, highest
1084 FROM (
1085 SELECT library_id, MAX(sequence) AS highest
1086 FROM snippets
1087 GROUP BY library_id
1088 ) WHERE true
1089 ON CONFLICT(library_id) DO UPDATE
1090 SET sequence = MAX(library_sequences.sequence, excluded.sequence)",
1091 )
1092 .execute(&self.pool)
1093 .await?;
1094
1095 sqlx::query(
1099 "CREATE UNIQUE INDEX IF NOT EXISTS idx_snippets_library_sequence \
1100 ON snippets(library_id, sequence)",
1101 )
1102 .execute(&self.pool)
1103 .await?;
1104
1105 sqlx::query(
1107 "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_sequence \
1108 ON snippets(user_id, library_id, sequence, deleted)",
1109 )
1110 .execute(&self.pool)
1111 .await?;
1112
1113 if migrated {
1114 tracing::info!("Migrated snippets table: added the sequence write-cursor column");
1115 }
1116
1117 Ok(migrated)
1118 }
1119
1120 pub async fn migrate_snippet_visible_at(&self) -> DbResult<bool> {
1128 let columns: Vec<String> =
1129 sqlx::query_scalar("SELECT name FROM pragma_table_info('snippets')")
1130 .fetch_all(&self.pool)
1131 .await?;
1132 if columns.iter().any(|name| name == "visible_at") {
1133 return Ok(false);
1134 }
1135
1136 sqlx::query("ALTER TABLE snippets ADD COLUMN visible_at INTEGER NOT NULL DEFAULT 0")
1137 .execute(&self.pool)
1138 .await?;
1139 sqlx::query("UPDATE snippets SET visible_at = updated_at")
1140 .execute(&self.pool)
1141 .await?;
1142 sqlx::query(
1143 "CREATE INDEX IF NOT EXISTS idx_snippets_user_library_visible \
1144 ON snippets(user_id, library_id, visible_at, deleted)",
1145 )
1146 .execute(&self.pool)
1147 .await?;
1148
1149 tracing::info!("Migrated snippets table: added the visible_at delta-cursor column");
1150 Ok(true)
1151 }
1152
1153 pub async fn migrate_plaintext_api_keys(&self) -> DbResult<usize> {
1154 let mut migrated = 0;
1155 let mut last_id = String::new();
1156
1157 loop {
1158 let rows: Vec<(String, String, Option<String>)> = sqlx::query_as(
1159 "SELECT id, api_key, api_key_prefix FROM users \
1160 WHERE id > ? ORDER BY id LIMIT ?",
1161 )
1162 .bind(&last_id)
1163 .bind(MIGRATION_BATCH_SIZE)
1164 .fetch_all(&self.pool)
1165 .await?;
1166
1167 if rows.is_empty() {
1168 break;
1169 }
1170
1171 let mut tx = self.pool.begin().await?;
1175 for (user_id, stored, prefix) in rows {
1176 last_id = user_id.clone();
1177 let mut needs_update = false;
1178 let mut new_hash = stored.clone();
1179 let mut new_prefix = prefix.clone();
1180
1181 if !stored.starts_with("$argon2") {
1183 new_hash = hash_api_key(&stored)?;
1184 needs_update = true;
1185 }
1186
1187 if prefix.is_none() {
1189 if stored.starts_with("$argon2") {
1190 new_prefix = Some(derive_prefix_from_hash(&stored));
1195 } else {
1196 new_prefix = Some(compute_api_key_prefix(&stored));
1197 }
1198 needs_update = true;
1199 }
1200
1201 if needs_update {
1202 sqlx::query("UPDATE users SET api_key = ?, api_key_prefix = ? WHERE id = ?")
1203 .bind(&new_hash)
1204 .bind(&new_prefix)
1205 .bind(&user_id)
1206 .execute(&mut *tx)
1207 .await?;
1208 migrated += 1;
1209 }
1210 }
1211 tx.commit().await?;
1212 }
1213
1214 if migrated > 0 {
1215 tracing::info!("Migrated {} API keys (hash or prefix update)", migrated);
1216 }
1217
1218 Ok(migrated)
1219 }
1220}
1221
1222#[cfg(test)]
1223mod tests {
1224 use super::*;
1225
1226 async fn setup_db() -> Database {
1227 Database::connect("sqlite::memory:", 5).await.unwrap()
1228 }
1229
1230 #[tokio::test]
1231 async fn test_file_database_is_created_from_path() {
1232 let temp = tempfile::tempdir().unwrap();
1233 let path = temp.path().join("nested").join("snippets.db");
1234
1235 let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
1236
1237 assert!(path.exists());
1238 }
1239
1240 #[cfg(unix)]
1243 #[tokio::test]
1244 async fn data_directory_is_created_owner_only() {
1245 use std::os::unix::fs::PermissionsExt;
1246
1247 let temp = tempfile::tempdir().unwrap();
1248 let dir = temp.path().join("server-data");
1249 let path = dir.join("snippets.db");
1250
1251 let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
1252
1253 let mode = std::fs::metadata(&dir).unwrap().permissions().mode() & 0o777;
1254 assert_eq!(
1255 mode, 0o700,
1256 "the data directory must be created owner-only, got {mode:#05o}"
1257 );
1258 }
1259
1260 #[cfg(unix)]
1263 #[tokio::test]
1264 async fn database_file_is_restricted_to_owner_only() {
1265 use std::os::unix::fs::PermissionsExt;
1266
1267 let temp = tempfile::tempdir().unwrap();
1268 let dir = temp.path().join("server-data");
1269 std::fs::create_dir_all(&dir).unwrap();
1270 let path = dir.join("snippets.db");
1271 std::fs::write(&path, b"").unwrap();
1273 std::fs::set_permissions(&path, std::fs::Permissions::from_mode(0o644)).unwrap();
1274
1275 let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
1276
1277 let mode = std::fs::metadata(&path).unwrap().permissions().mode() & 0o777;
1278 assert_eq!(
1279 mode & 0o077,
1280 0,
1281 "the database file must not be group/world accessible, got {mode:#05o}"
1282 );
1283 }
1284
1285 #[cfg(unix)]
1287 #[tokio::test]
1288 async fn preexisting_broad_directory_is_tightened() {
1289 use std::os::unix::fs::PermissionsExt;
1290
1291 let temp = tempfile::tempdir().unwrap();
1292 let dir = temp.path().join("server-data");
1293 std::fs::create_dir_all(&dir).unwrap();
1294 std::fs::set_permissions(&dir, std::fs::Permissions::from_mode(0o755)).unwrap();
1295 let path = dir.join("snippets.db");
1296
1297 let _db = Database::connect(path.to_str().unwrap(), 1).await.unwrap();
1298
1299 let mode = std::fs::metadata(&dir).unwrap().permissions().mode() & 0o777;
1300 assert_eq!(mode, 0o700, "an existing broad mode must be tightened");
1301 }
1302
1303 #[tokio::test]
1306 async fn memory_database_skips_file_permission_handling() {
1307 let _db = setup_db().await;
1308 }
1309
1310 #[tokio::test]
1311 async fn test_create_and_verify_user() {
1312 let db = setup_db().await;
1313 let api_key = "test-api-key-12345";
1314
1315 let user_id = db.create_user(api_key).await.unwrap();
1316 assert!(!user_id.is_empty());
1317
1318 let found = db.get_user_by_api_key(api_key).await.unwrap();
1320 assert_eq!(found, Some(user_id));
1321
1322 let not_found = db.get_user_by_api_key("wrong-key").await.unwrap();
1324 assert_eq!(not_found, None);
1325 }
1326
1327 #[tokio::test]
1328 async fn test_api_key_is_hashed() {
1329 let db = setup_db().await;
1330 let api_key = "plaintext-key-should-be-hashed";
1331
1332 let _user_id = db.create_user(api_key).await.unwrap();
1333
1334 let rows: Vec<(String, String, Option<String>)> =
1336 sqlx::query_as("SELECT id, api_key, api_key_prefix FROM users")
1337 .fetch_all(&db.pool)
1338 .await
1339 .unwrap();
1340
1341 assert_eq!(rows.len(), 1);
1342 assert!(
1343 rows[0].1.starts_with("$argon2"),
1344 "API key should be stored as Argon2 hash"
1345 );
1346 assert!(rows[0].2.is_some(), "API key prefix should be stored");
1348 assert_eq!(rows[0].2.as_ref().unwrap().len(), 8);
1349 }
1350
1351 #[tokio::test]
1352 async fn test_create_default_library() {
1353 let db = setup_db().await;
1354 let user_id = db.create_user("key").await.unwrap();
1355
1356 let lib_id = db.get_default_library(&user_id).await.unwrap();
1357 assert!(!lib_id.is_empty());
1358
1359 let (libraries, total) = db.list_libraries(&user_id, 10, 0).await.unwrap();
1360 assert_eq!(total, 1);
1361 assert_eq!(libraries[0].name, "default");
1362 }
1363
1364 #[tokio::test]
1365 async fn test_create_additional_library() {
1366 let db = setup_db().await;
1367 let user_id = db.create_user("key").await.unwrap();
1368
1369 let lib_id = db.create_library(&user_id, "work").await.unwrap();
1370 assert!(!lib_id.is_empty());
1371
1372 let (_libraries, total) = db.list_libraries(&user_id, 10, 0).await.unwrap();
1373 assert_eq!(total, 2);
1374
1375 let err = db.create_library(&user_id, "work").await.unwrap_err();
1377 assert!(matches!(err, DbError::Conflict(_)));
1378 }
1379
1380 #[tokio::test]
1381 async fn test_invalid_library_names() {
1382 let db = setup_db().await;
1383 let user_id = db.create_user("key").await.unwrap();
1384
1385 assert!(db.create_library(&user_id, "").await.is_err());
1387
1388 assert!(db.create_library(&user_id, "my/lib").await.is_err());
1390
1391 assert!(db.create_library(&user_id, &"a".repeat(65)).await.is_err());
1393
1394 assert!(db.create_library(&user_id, "valid-name").await.is_ok());
1396 assert!(db.create_library(&user_id, "valid_name").await.is_ok());
1397 assert!(db.create_library(&user_id, "ValidName123").await.is_ok());
1398 }
1399
1400 #[tokio::test]
1401 async fn test_upsert_and_get_snippets() {
1402 let db = setup_db().await;
1403 let user_id = db.create_user("key").await.unwrap();
1404 let lib_id = db.get_default_library(&user_id).await.unwrap();
1405
1406 let snippet = Snippet {
1407 id: "snip-1".to_string(),
1408 description: "Test snippet".to_string(),
1409 command: "echo hello".to_string(),
1410 tags: vec!["test".to_string()],
1411 created_at: 100,
1412 updated_at: 100,
1413 device_id: "device-1".to_string(),
1414 deleted: false,
1415 encrypted: false,
1416 };
1417
1418 db.upsert_snippet(&snippet, &user_id, &lib_id)
1419 .await
1420 .unwrap();
1421
1422 let (snippets, total) = db
1423 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1424 .await
1425 .unwrap();
1426 assert_eq!(total, 1);
1427 assert_eq!(snippets[0].description, "Test snippet");
1428 assert_eq!(snippets[0].command, "echo hello");
1429 assert_eq!(snippets[0].tags, vec!["test"]);
1430 }
1431
1432 #[tokio::test]
1437 async fn test_delta_query_includes_watermark_boundary_row() {
1438 let db = setup_db().await;
1439 let user_id = db.create_user("key").await.unwrap();
1440 let lib_id = db.get_default_library(&user_id).await.unwrap();
1441
1442 let snippet = Snippet {
1443 id: "boundary-snip".to_string(),
1444 description: "Boundary".to_string(),
1445 command: "echo boundary".to_string(),
1446 tags: vec![],
1447 created_at: 500,
1448 updated_at: 500,
1449 device_id: "device-1".to_string(),
1450 deleted: false,
1451 encrypted: false,
1452 };
1453 db.upsert_snippet(&snippet, &user_id, &lib_id)
1454 .await
1455 .unwrap();
1456
1457 let latest = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
1458 assert_eq!(latest, 500);
1459
1460 let (snippets, total) = db
1462 .get_snippets(&user_id, &lib_id, latest, 10, 0, false)
1463 .await
1464 .unwrap();
1465 assert_eq!(total, 1, "row at exactly `since` must be returned");
1466 assert_eq!(snippets[0].id, "boundary-snip");
1467 }
1468
1469 #[tokio::test]
1474 async fn test_insert_below_watermark_is_still_delivered() {
1475 let db = setup_db().await;
1476 let user_id = db.create_user("key").await.unwrap();
1477 let lib_id = db.get_default_library(&user_id).await.unwrap();
1478
1479 let make = |id: &str, updated_at: i64| Snippet {
1480 id: id.to_string(),
1481 description: format!("desc-{id}"),
1482 command: format!("echo {id}"),
1483 tags: vec![],
1484 created_at: updated_at,
1485 updated_at,
1486 device_id: "device-1".to_string(),
1487 deleted: false,
1488 encrypted: false,
1489 };
1490
1491 let mut tx = db.pool.begin().await.unwrap();
1492 assert_eq!(
1493 db.upsert_snippet_in_tx(&mut tx, &make("newer", 1000), &user_id, &lib_id)
1494 .await
1495 .unwrap(),
1496 UpsertOutcome::Written
1497 );
1498 assert_eq!(
1499 db.upsert_snippet_in_tx(&mut tx, &make("older", 500), &user_id, &lib_id)
1500 .await
1501 .unwrap(),
1502 UpsertOutcome::Written
1503 );
1504 tx.commit().await.unwrap();
1505
1506 let watermark = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
1507 assert_eq!(watermark, 1000);
1508
1509 let (snippets, total) = db
1510 .get_snippets(&user_id, &lib_id, watermark, 10, 0, false)
1511 .await
1512 .unwrap();
1513 assert_eq!(
1514 total, 2,
1515 "the row pushed below the watermark must still be delivered by a delta pull"
1516 );
1517
1518 let older = snippets
1520 .iter()
1521 .find(|s| s.id == "older")
1522 .expect("the older row must be present");
1523 assert_eq!(older.updated_at, 500);
1524 }
1525
1526 #[tokio::test]
1530 async fn test_update_below_the_watermark_still_uses_the_raw_timestamp() {
1531 let db = setup_db().await;
1532 let user_id = db.create_user("key").await.unwrap();
1533 let lib_id = db.get_default_library(&user_id).await.unwrap();
1534
1535 let make = |id: &str, command: &str, updated_at: i64| Snippet {
1536 id: id.to_string(),
1537 description: "desc".to_string(),
1538 command: command.to_string(),
1539 tags: vec![],
1540 created_at: 0,
1541 updated_at,
1542 device_id: "device-1".to_string(),
1543 deleted: false,
1544 encrypted: false,
1545 };
1546
1547 let mut tx = db.pool.begin().await.unwrap();
1549 db.upsert_snippet_in_tx(
1550 &mut tx,
1551 &make("other", "echo other", 1000),
1552 &user_id,
1553 &lib_id,
1554 )
1555 .await
1556 .unwrap();
1557 tx.commit().await.unwrap();
1558
1559 let mut tx = db.pool.begin().await.unwrap();
1561 assert_eq!(
1562 db.upsert_snippet_in_tx(&mut tx, &make("row", "echo one", 500), &user_id, &lib_id)
1563 .await
1564 .unwrap(),
1565 UpsertOutcome::Written
1566 );
1567 tx.commit().await.unwrap();
1568
1569 let mut tx = db.pool.begin().await.unwrap();
1572 assert_eq!(
1573 db.upsert_snippet_in_tx(&mut tx, &make("row", "echo two", 700), &user_id, &lib_id)
1574 .await
1575 .unwrap(),
1576 UpsertOutcome::Written
1577 );
1578 tx.commit().await.unwrap();
1579
1580 let mut tx = db.pool.begin().await.unwrap();
1582 assert_eq!(
1583 db.upsert_snippet_in_tx(&mut tx, &make("row", "echo older", 600), &user_id, &lib_id)
1584 .await
1585 .unwrap(),
1586 UpsertOutcome::Stale
1587 );
1588 tx.commit().await.unwrap();
1589
1590 let (snippets, _) = db
1591 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1592 .await
1593 .unwrap();
1594 let row = snippets
1595 .iter()
1596 .find(|s| s.id == "row")
1597 .expect("the updated snippet must be present");
1598 assert_eq!(row.command, "echo two");
1599 assert_eq!(row.updated_at, 700);
1600 }
1601
1602 #[tokio::test]
1606 async fn test_migrate_snippet_visible_at_backfills_the_cursor() {
1607 let temp = tempfile::tempdir().unwrap();
1608 let db_path = temp.path().join("legacy.sqlite");
1609 let db = Database::connect(db_path.to_str().unwrap(), 1)
1610 .await
1611 .unwrap();
1612 let user_id = db.create_user("key").await.unwrap();
1613 let lib_id = db.get_default_library(&user_id).await.unwrap();
1614
1615 sqlx::query("DROP INDEX IF EXISTS idx_snippets_user_library_visible")
1617 .execute(db.pool())
1618 .await
1619 .unwrap();
1620 sqlx::query("ALTER TABLE snippets DROP COLUMN visible_at")
1621 .execute(db.pool())
1622 .await
1623 .unwrap();
1624 let snippet = Snippet {
1625 id: "legacy-1".to_string(),
1626 description: "legacy".to_string(),
1627 command: "echo legacy".to_string(),
1628 tags: vec![],
1629 created_at: 100,
1630 updated_at: 100,
1631 device_id: "device-1".to_string(),
1632 deleted: false,
1633 encrypted: false,
1634 };
1635 sqlx::query(
1636 "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted)
1637 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
1638 )
1639 .bind(&snippet.id)
1640 .bind(&user_id)
1641 .bind(&lib_id)
1642 .bind(&snippet.description)
1643 .bind(&snippet.command)
1644 .bind("[]")
1645 .bind(snippet.created_at)
1646 .bind(snippet.updated_at)
1647 .bind(&snippet.device_id)
1648 .bind(0)
1649 .bind(0)
1650 .execute(db.pool())
1651 .await
1652 .unwrap();
1653
1654 assert!(db.migrate_snippet_visible_at().await.unwrap());
1655 assert!(!db.migrate_snippet_visible_at().await.unwrap());
1657
1658 assert_eq!(
1659 db.get_latest_timestamp(&user_id, &lib_id).await.unwrap(),
1660 100
1661 );
1662 let (snippets, total) = db
1663 .get_snippets(&user_id, &lib_id, 100, 10, 0, false)
1664 .await
1665 .unwrap();
1666 assert_eq!(total, 1);
1667 assert_eq!(snippets[0].id, "legacy-1");
1668 assert_eq!(snippets[0].updated_at, 100);
1669 }
1670
1671 #[tokio::test]
1672 async fn test_delete_library_preserves_future_tombstone_timestamp() {
1673 let db = setup_db().await;
1674 let user_id = db.create_user("key").await.unwrap();
1675 let library_id = db.create_library(&user_id, "future-delete").await.unwrap();
1676 let future_timestamp = OffsetDateTime::now_utc().unix_timestamp() + 100;
1677
1678 let snippet = Snippet {
1679 id: "future-snippet".to_string(),
1680 description: "desc".to_string(),
1681 command: "echo hi".to_string(),
1682 tags: vec![],
1683 created_at: 0,
1684 updated_at: future_timestamp,
1685 device_id: "device".to_string(),
1686 deleted: false,
1687 encrypted: false,
1688 };
1689 db.upsert_snippet(&snippet, &user_id, &library_id)
1690 .await
1691 .unwrap();
1692
1693 db.delete_library(&user_id, &library_id).await.unwrap();
1694
1695 let (snippets, total) = db
1696 .get_snippets(&user_id, &library_id, future_timestamp - 1, 10, 0, true)
1697 .await
1698 .unwrap();
1699 assert_eq!(total, 1);
1700 assert!(snippets[0].deleted);
1701 assert_eq!(snippets[0].updated_at, future_timestamp);
1702 }
1703
1704 #[tokio::test]
1705 async fn test_upsert_updates_existing() {
1706 let db = setup_db().await;
1707 let user_id = db.create_user("key").await.unwrap();
1708 let lib_id = db.get_default_library(&user_id).await.unwrap();
1709
1710 let snippet = Snippet {
1711 id: "snip-1".to_string(),
1712 description: "Original".to_string(),
1713 command: "echo old".to_string(),
1714 tags: vec![],
1715 created_at: 100,
1716 updated_at: 100,
1717 device_id: "device-1".to_string(),
1718 deleted: false,
1719 encrypted: false,
1720 };
1721 db.upsert_snippet(&snippet, &user_id, &lib_id)
1722 .await
1723 .unwrap();
1724
1725 let updated = Snippet {
1727 id: "snip-1".to_string(),
1728 description: "Updated".to_string(),
1729 command: "echo new".to_string(),
1730 tags: vec!["updated".to_string()],
1731 created_at: 100,
1732 updated_at: 200,
1733 device_id: "device-1".to_string(),
1734 deleted: false,
1735 encrypted: false,
1736 };
1737 db.upsert_snippet(&updated, &user_id, &lib_id)
1738 .await
1739 .unwrap();
1740
1741 let (snippets, _) = db
1742 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1743 .await
1744 .unwrap();
1745 assert_eq!(snippets.len(), 1);
1746 assert_eq!(snippets[0].description, "Updated");
1747 assert_eq!(snippets[0].command, "echo new");
1748 }
1749
1750 #[tokio::test]
1751 async fn test_upsert_rejects_older_timestamp() {
1752 let db = setup_db().await;
1753 let user_id = db.create_user("key").await.unwrap();
1754 let lib_id = db.get_default_library(&user_id).await.unwrap();
1755
1756 let snippet = Snippet {
1758 id: "snip-1".to_string(),
1759 description: "Newer".to_string(),
1760 command: "echo newer".to_string(),
1761 tags: vec![],
1762 created_at: 100,
1763 updated_at: 200,
1764 device_id: "d".to_string(),
1765 deleted: false,
1766 encrypted: false,
1767 };
1768 db.upsert_snippet(&snippet, &user_id, &lib_id)
1769 .await
1770 .unwrap();
1771
1772 let older = Snippet {
1774 id: "snip-1".to_string(),
1775 description: "Older".to_string(),
1776 command: "echo older".to_string(),
1777 tags: vec![],
1778 created_at: 100,
1779 updated_at: 100,
1780 device_id: "d".to_string(),
1781 deleted: false,
1782 encrypted: false,
1783 };
1784 db.upsert_snippet(&older, &user_id, &lib_id).await.unwrap();
1785
1786 let (snippets, _) = db
1788 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1789 .await
1790 .unwrap();
1791 assert_eq!(snippets.len(), 1);
1792 assert_eq!(snippets[0].description, "Newer");
1793 assert_eq!(snippets[0].command, "echo newer");
1794 }
1795
1796 #[tokio::test]
1797 async fn test_get_latest_timestamp() {
1798 let db = setup_db().await;
1799 let user_id = db.create_user("key").await.unwrap();
1800 let lib_id = db.get_default_library(&user_id).await.unwrap();
1801
1802 let ts = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
1804 assert_eq!(ts, 0);
1805
1806 let snippet = Snippet {
1807 id: "snip-1".to_string(),
1808 description: "Test".to_string(),
1809 command: "echo".to_string(),
1810 tags: vec![],
1811 created_at: 100,
1812 updated_at: 500,
1813 device_id: "d".to_string(),
1814 deleted: false,
1815 encrypted: false,
1816 };
1817 db.upsert_snippet(&snippet, &user_id, &lib_id)
1818 .await
1819 .unwrap();
1820
1821 let ts = db.get_latest_timestamp(&user_id, &lib_id).await.unwrap();
1822 assert_eq!(ts, 500);
1823 }
1824
1825 #[tokio::test]
1826 async fn test_delete_library() {
1827 let db = setup_db().await;
1828 let user_id = db.create_user("key").await.unwrap();
1829 let lib_id = db.create_library(&user_id, "temp").await.unwrap();
1830
1831 let snippet = Snippet {
1833 id: "snip-1".to_string(),
1834 description: "Test".to_string(),
1835 command: "echo".to_string(),
1836 tags: vec![],
1837 created_at: 100,
1838 updated_at: 100,
1839 device_id: "d".to_string(),
1840 deleted: false,
1841 encrypted: false,
1842 };
1843 db.upsert_snippet(&snippet, &user_id, &lib_id)
1844 .await
1845 .unwrap();
1846
1847 db.delete_library(&user_id, &lib_id).await.unwrap();
1849
1850 assert!(db.delete_library(&user_id, &lib_id).await.is_err());
1852
1853 let (snippets, _) = db
1855 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
1856 .await
1857 .unwrap();
1858 assert_eq!(snippets.len(), 0);
1859 }
1860
1861 #[tokio::test]
1862 async fn test_migrate_plaintext_api_keys() {
1863 let db = setup_db().await;
1864 let user_id = Uuid::new_v4().to_string();
1865 let now = OffsetDateTime::now_utc().unix_timestamp();
1866
1867 sqlx::query("INSERT INTO users (id, api_key, created_at, updated_at) VALUES (?, ?, ?, ?)")
1869 .bind(&user_id)
1870 .bind("plaintext-api-key")
1871 .bind(now)
1872 .bind(now)
1873 .execute(&db.pool)
1874 .await
1875 .unwrap();
1876
1877 let migrated = db.migrate_plaintext_api_keys().await.unwrap();
1879 assert_eq!(migrated, 1);
1880
1881 let (stored_hash,): (String,) = sqlx::query_as("SELECT api_key FROM users WHERE id = ?")
1883 .bind(&user_id)
1884 .fetch_one(&db.pool)
1885 .await
1886 .unwrap();
1887
1888 assert!(stored_hash.starts_with("$argon2"));
1889
1890 let (prefix,): (Option<String>,) =
1892 sqlx::query_as("SELECT api_key_prefix FROM users WHERE id = ?")
1893 .bind(&user_id)
1894 .fetch_one(&db.pool)
1895 .await
1896 .unwrap();
1897 assert!(
1898 prefix.is_some(),
1899 "API key prefix should be backfilled by migration"
1900 );
1901
1902 let found = db.get_user_by_api_key("plaintext-api-key").await.unwrap();
1904 assert_eq!(found, Some(user_id));
1905
1906 let migrated_again = db.migrate_plaintext_api_keys().await.unwrap();
1908 assert_eq!(migrated_again, 0);
1909 }
1910
1911 #[tokio::test]
1912 async fn test_api_key_prefix_lookup() {
1913 let db = setup_db().await;
1914
1915 let user1 = db.create_user("key-one-alpha").await.unwrap();
1917 let user2 = db.create_user("key-two-beta").await.unwrap();
1918 let user3 = db.create_user("key-three-gamma").await.unwrap();
1919
1920 assert_eq!(
1922 db.get_user_by_api_key("key-one-alpha").await.unwrap(),
1923 Some(user1)
1924 );
1925 assert_eq!(
1926 db.get_user_by_api_key("key-two-beta").await.unwrap(),
1927 Some(user2)
1928 );
1929 assert_eq!(
1930 db.get_user_by_api_key("key-three-gamma").await.unwrap(),
1931 Some(user3)
1932 );
1933 assert_eq!(db.get_user_by_api_key("nonexistent").await.unwrap(), None);
1934
1935 let rows: Vec<(String,)> =
1937 sqlx::query_as("SELECT api_key_prefix FROM users WHERE api_key_prefix IS NOT NULL")
1938 .fetch_all(&db.pool)
1939 .await
1940 .unwrap();
1941 assert_eq!(rows.len(), 3);
1942 }
1943
1944 #[tokio::test]
1945 async fn test_library_ownership_verification() {
1946 let db = setup_db().await;
1947 let user1_id = db.create_user("user1-api-key").await.unwrap();
1948 let user2_id = db.create_user("user2-api-key").await.unwrap();
1949 let lib_id = db.create_library(&user1_id, "private").await.unwrap();
1950
1951 assert!(
1952 db.verify_library_ownership(&user1_id, &lib_id)
1953 .await
1954 .unwrap()
1955 );
1956 assert!(
1957 !db.verify_library_ownership(&user2_id, &lib_id)
1958 .await
1959 .unwrap()
1960 );
1961
1962 let nonexistent_lib = Uuid::new_v4().to_string();
1963 assert!(
1964 !db.verify_library_ownership(&user1_id, &nonexistent_lib)
1965 .await
1966 .unwrap()
1967 );
1968 }
1969
1970 #[tokio::test]
1971 async fn test_library_ownership_prevents_cross_user_access() {
1972 let db = setup_db().await;
1973 let user1_id = db.create_user("user1-key").await.unwrap();
1974 let user2_id = db.create_user("user2-key").await.unwrap();
1975 let user1_lib = db.create_library(&user1_id, "my-library").await.unwrap();
1976
1977 let snippet = Snippet {
1978 id: "snip-1".to_string(),
1979 description: "User 1 secret".to_string(),
1980 command: "secret command".to_string(),
1981 tags: vec![],
1982 created_at: 100,
1983 updated_at: 100,
1984 device_id: "device-1".to_string(),
1985 deleted: false,
1986 encrypted: false,
1987 };
1988
1989 db.upsert_snippet(&snippet, &user1_id, &user1_lib)
1990 .await
1991 .unwrap();
1992
1993 let (snippets, _) = db
1994 .get_snippets(&user1_id, &user1_lib, 0, 10, 0, false)
1995 .await
1996 .unwrap();
1997 assert_eq!(snippets.len(), 1);
1998
1999 let (snippets, _) = db
2000 .get_snippets(&user2_id, &user1_lib, 0, 10, 0, false)
2001 .await
2002 .unwrap();
2003 assert_eq!(snippets.len(), 0);
2004 }
2005
2006 #[tokio::test]
2007 async fn test_deleted_library_not_owned() {
2008 let db = setup_db().await;
2009 let user_id = db.create_user("test-key").await.unwrap();
2010 let lib_id = db.create_library(&user_id, "temp-lib").await.unwrap();
2011
2012 assert!(
2013 db.verify_library_ownership(&user_id, &lib_id)
2014 .await
2015 .unwrap()
2016 );
2017
2018 db.delete_library(&user_id, &lib_id).await.unwrap();
2019
2020 assert!(
2021 !db.verify_library_ownership(&user_id, &lib_id)
2022 .await
2023 .unwrap()
2024 );
2025 }
2026
2027 #[tokio::test]
2028 async fn test_get_snippets_include_deleted() {
2029 let db = setup_db().await;
2030 let user_id = db.create_user("key").await.unwrap();
2031 let lib_id = db.get_default_library(&user_id).await.unwrap();
2032
2033 let snippet = Snippet {
2034 id: "snip-1".to_string(),
2035 description: "Test".to_string(),
2036 command: "echo".to_string(),
2037 tags: vec![],
2038 created_at: 100,
2039 updated_at: 100,
2040 device_id: "d".to_string(),
2041 deleted: false,
2042 encrypted: false,
2043 };
2044 db.upsert_snippet(&snippet, &user_id, &lib_id)
2045 .await
2046 .unwrap();
2047
2048 let deleted = Snippet {
2050 id: "snip-1".to_string(),
2051 description: "Test".to_string(),
2052 command: "echo".to_string(),
2053 tags: vec![],
2054 created_at: 100,
2055 updated_at: 200,
2056 device_id: "d".to_string(),
2057 deleted: true,
2058 encrypted: false,
2059 };
2060 db.upsert_snippet(&deleted, &user_id, &lib_id)
2061 .await
2062 .unwrap();
2063
2064 let (snippets, _) = db
2066 .get_snippets(&user_id, &lib_id, 0, 10, 0, false)
2067 .await
2068 .unwrap();
2069 assert_eq!(snippets.len(), 0);
2070
2071 let (snippets, _) = db
2073 .get_snippets(&user_id, &lib_id, 0, 10, 0, true)
2074 .await
2075 .unwrap();
2076 assert_eq!(snippets.len(), 1);
2077 assert!(snippets[0].deleted);
2078 assert_eq!(snippets[0].updated_at, 200);
2079 }
2080
2081 fn sequence_snippet(id: &str, updated_at: i64) -> Snippet {
2086 Snippet {
2087 id: id.to_string(),
2088 description: "Test".to_string(),
2089 command: "echo".to_string(),
2090 tags: vec![],
2091 created_at: updated_at,
2092 updated_at,
2093 device_id: "d".to_string(),
2094 deleted: false,
2095 encrypted: false,
2096 }
2097 }
2098
2099 async fn snippet_sequences(db: &Database, library_id: &str) -> Vec<(String, i64)> {
2100 sqlx::query_as("SELECT id, sequence FROM snippets WHERE library_id = ? ORDER BY sequence")
2101 .bind(library_id)
2102 .fetch_all(db.pool())
2103 .await
2104 .unwrap()
2105 }
2106
2107 #[tokio::test]
2109 async fn test_sequence_watermark_is_the_library_maximum() {
2110 let db = setup_db().await;
2111 let user_id = db.create_user("key").await.unwrap();
2112 let lib_id = db.get_default_library(&user_id).await.unwrap();
2113
2114 assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 0);
2115
2116 db.upsert_snippet(&sequence_snippet("a", 100), &user_id, &lib_id)
2117 .await
2118 .unwrap();
2119 db.upsert_snippet(&sequence_snippet("b", 200), &user_id, &lib_id)
2120 .await
2121 .unwrap();
2122
2123 assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 2);
2124 assert_eq!(snippet_sequences(&db, &lib_id).await.len(), 2);
2125 }
2126
2127 #[tokio::test]
2131 async fn test_write_assigns_a_sequence_above_the_library_maximum() {
2132 let db = setup_db().await;
2133 let user_id = db.create_user("key").await.unwrap();
2134 let lib_id = db.get_default_library(&user_id).await.unwrap();
2135
2136 db.upsert_snippet(&sequence_snippet("a", 300), &user_id, &lib_id)
2137 .await
2138 .unwrap();
2139 db.upsert_snippet(&sequence_snippet("b", 100), &user_id, &lib_id)
2140 .await
2141 .unwrap();
2142 db.upsert_snippet(&sequence_snippet("c", 200), &user_id, &lib_id)
2143 .await
2144 .unwrap();
2145
2146 let rows = snippet_sequences(&db, &lib_id).await;
2147 let sequences: Vec<i64> = rows.iter().map(|(_, sequence)| *sequence).collect();
2148 assert_eq!(sequences, vec![1, 2, 3]);
2149 assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 3);
2150 }
2151
2152 #[tokio::test]
2159 async fn test_concurrent_pushes_to_one_library_get_distinct_sequences() {
2160 let temp = tempfile::tempdir().unwrap();
2164 let db_path = temp.path().join("concurrent.sqlite");
2165 let db = Database::connect(db_path.to_str().unwrap(), 5)
2166 .await
2167 .unwrap();
2168 let user_id = db.create_user("key").await.unwrap();
2169 let lib_id = db.get_default_library(&user_id).await.unwrap();
2170
2171 let first_snippet = sequence_snippet("a", 100);
2172 let second_snippet = sequence_snippet("b", 200);
2173 let first = db.upsert_snippet(&first_snippet, &user_id, &lib_id);
2174 let second = db.upsert_snippet(&second_snippet, &user_id, &lib_id);
2175 let (first, second) = tokio::join!(first, second);
2176 first.unwrap();
2177 second.unwrap();
2178
2179 let rows = snippet_sequences(&db, &lib_id).await;
2180 assert_eq!(rows.len(), 2);
2181 assert_ne!(
2182 rows[0].1, rows[1].1,
2183 "concurrent writes to one library must not share a sequence value"
2184 );
2185 }
2186
2187 #[tokio::test]
2190 async fn test_rolled_back_write_does_not_consume_a_sequence_value() {
2191 let db = setup_db().await;
2192 let user_id = db.create_user("key").await.unwrap();
2193 let lib_id = db.get_default_library(&user_id).await.unwrap();
2194
2195 let mut tx = db.pool().begin().await.unwrap();
2196 db.upsert_snippet_in_tx(&mut tx, &sequence_snippet("a", 100), &user_id, &lib_id)
2197 .await
2198 .unwrap();
2199 tx.rollback().await.unwrap();
2200
2201 assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 0);
2202 assert!(snippet_sequences(&db, &lib_id).await.is_empty());
2203
2204 db.upsert_snippet(&sequence_snippet("b", 100), &user_id, &lib_id)
2206 .await
2207 .unwrap();
2208 assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 1);
2209 }
2210
2211 #[tokio::test]
2214 async fn test_unique_index_rejects_a_shared_sequence_value() {
2215 let db = setup_db().await;
2216 let user_id = db.create_user("key").await.unwrap();
2217 let lib_id = db.get_default_library(&user_id).await.unwrap();
2218
2219 let insert = |id: &str, sequence: i64| {
2220 sqlx::query(
2221 "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted, visible_at, sequence)
2222 VALUES (?, ?, ?, 'd', 'echo', '[]', 1, 1, 'dev', 0, 0, 1, ?)",
2223 )
2224 .bind(id)
2225 .bind(&user_id)
2226 .bind(&lib_id)
2227 .bind(sequence)
2228 };
2229
2230 insert("a", 1).execute(db.pool()).await.unwrap();
2231 let error = insert("b", 1).execute(db.pool()).await.unwrap_err();
2232 assert!(
2233 matches!(error, sqlx::Error::Database(_)),
2234 "a shared sequence value must be rejected by the unique index, got {error:?}"
2235 );
2236 }
2237
2238 #[tokio::test]
2242 async fn test_migrate_snippet_sequence_backfills_the_updated_at_ordering() {
2243 let temp = tempfile::tempdir().unwrap();
2244 let db_path = temp.path().join("legacy.sqlite");
2245 let db = Database::connect(db_path.to_str().unwrap(), 1)
2246 .await
2247 .unwrap();
2248 let user_id = db.create_user("key").await.unwrap();
2249 let lib_id = db.get_default_library(&user_id).await.unwrap();
2250
2251 sqlx::query("DROP INDEX IF EXISTS idx_snippets_library_sequence")
2253 .execute(db.pool())
2254 .await
2255 .unwrap();
2256 sqlx::query("DROP INDEX IF EXISTS idx_snippets_user_library_sequence")
2257 .execute(db.pool())
2258 .await
2259 .unwrap();
2260 sqlx::query("ALTER TABLE snippets DROP COLUMN sequence")
2261 .execute(db.pool())
2262 .await
2263 .unwrap();
2264
2265 for (id, updated_at) in [("c", 300_i64), ("a", 100), ("b", 200)] {
2267 sqlx::query(
2268 "INSERT INTO snippets (id, user_id, library_id, description, command, tags, created_at, updated_at, device_id, deleted, encrypted)
2269 VALUES (?, ?, ?, 'd', 'echo', '[]', ?, ?, 'dev', 0, 0)",
2270 )
2271 .bind(id)
2272 .bind(&user_id)
2273 .bind(&lib_id)
2274 .bind(updated_at)
2275 .bind(updated_at)
2276 .execute(db.pool())
2277 .await
2278 .unwrap();
2279 }
2280
2281 assert!(db.migrate_snippet_sequence().await.unwrap());
2282 assert!(!db.migrate_snippet_sequence().await.unwrap());
2284
2285 let rows: Vec<(String, i64, i64)> =
2286 sqlx::query_as("SELECT id, updated_at, sequence FROM snippets ORDER BY sequence")
2287 .fetch_all(db.pool())
2288 .await
2289 .unwrap();
2290 assert_eq!(rows.len(), 3);
2291 assert_eq!(rows[0].0, "a");
2294 assert_eq!(rows[1].0, "b");
2295 assert_eq!(rows[2].0, "c");
2296 assert!(rows[0].2 < rows[1].2 && rows[1].2 < rows[2].2);
2297
2298 assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 3);
2301 db.upsert_snippet(&sequence_snippet("d", 400), &user_id, &lib_id)
2302 .await
2303 .unwrap();
2304 assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 4);
2305 }
2306
2307 #[tokio::test]
2310 async fn test_sequence_cursor_selects_the_rows_written_after_it() {
2311 let db = setup_db().await;
2312 let user_id = db.create_user("key").await.unwrap();
2313 let lib_id = db.get_default_library(&user_id).await.unwrap();
2314
2315 db.upsert_snippet(&sequence_snippet("a", 100), &user_id, &lib_id)
2316 .await
2317 .unwrap();
2318 db.upsert_snippet(&sequence_snippet("b", 200), &user_id, &lib_id)
2319 .await
2320 .unwrap();
2321 db.upsert_snippet(&sequence_snippet("c", 300), &user_id, &lib_id)
2322 .await
2323 .unwrap();
2324
2325 let page = db
2326 .get_snippets_page(&user_id, &lib_id, DeltaCursor::Sequence(2), 100, 0, true)
2327 .await
2328 .unwrap();
2329 assert_eq!(page.total, 2);
2330 let mut ids: Vec<&str> = page
2331 .snippets
2332 .iter()
2333 .map(|snippet| snippet.id.as_str())
2334 .collect();
2335 ids.sort_unstable();
2336 assert_eq!(ids, vec!["b", "c"]);
2337
2338 let page = db
2341 .get_snippets_page(&user_id, &lib_id, DeltaCursor::Sequence(3), 100, 0, true)
2342 .await
2343 .unwrap();
2344 assert_eq!(page.total, 1);
2345 assert_eq!(page.snippets[0].id, "c");
2346 }
2347
2348 #[tokio::test]
2351 async fn test_timestamp_cursor_still_selects_the_timestamp_rows() {
2352 let db = setup_db().await;
2353 let user_id = db.create_user("key").await.unwrap();
2354 let lib_id = db.get_default_library(&user_id).await.unwrap();
2355
2356 db.upsert_snippet(&sequence_snippet("a", 100), &user_id, &lib_id)
2357 .await
2358 .unwrap();
2359 db.upsert_snippet(&sequence_snippet("b", 200), &user_id, &lib_id)
2360 .await
2361 .unwrap();
2362
2363 let timestamp_page = db
2364 .get_snippets_page(&user_id, &lib_id, DeltaCursor::Timestamp(0), 100, 0, true)
2365 .await
2366 .unwrap();
2367 let sequence_page = db
2368 .get_snippets_page(&user_id, &lib_id, DeltaCursor::Sequence(0), 100, 0, true)
2369 .await
2370 .unwrap();
2371
2372 assert_eq!(timestamp_page.total, 2);
2373 assert_eq!(timestamp_page.total, sequence_page.total);
2374 let mut timestamp_ids: Vec<&str> = timestamp_page
2375 .snippets
2376 .iter()
2377 .map(|snippet| snippet.id.as_str())
2378 .collect();
2379 let mut sequence_ids: Vec<&str> = sequence_page
2380 .snippets
2381 .iter()
2382 .map(|snippet| snippet.id.as_str())
2383 .collect();
2384 timestamp_ids.sort_unstable();
2385 sequence_ids.sort_unstable();
2386 assert_eq!(timestamp_ids, sequence_ids);
2387 }
2388
2389 #[tokio::test]
2393 async fn test_sequence_cursor_is_scoped_to_its_library_and_user() {
2394 let db = setup_db().await;
2395 let user_id = db.create_user("key").await.unwrap();
2396 let lib_a = db.get_default_library(&user_id).await.unwrap();
2397 let lib_b = db.create_library(&user_id, "other").await.unwrap();
2398
2399 let other_user = db.create_user("other-key").await.unwrap();
2400 let other_lib = db.get_default_library(&other_user).await.unwrap();
2401
2402 db.upsert_snippet(&sequence_snippet("a", 100), &user_id, &lib_a)
2403 .await
2404 .unwrap();
2405 db.upsert_snippet(&sequence_snippet("b", 100), &user_id, &lib_b)
2408 .await
2409 .unwrap();
2410 db.upsert_snippet(&sequence_snippet("c", 100), &other_user, &other_lib)
2411 .await
2412 .unwrap();
2413
2414 let page = db
2415 .get_snippets_page(&user_id, &lib_a, DeltaCursor::Sequence(1), 100, 0, true)
2416 .await
2417 .unwrap();
2418 assert_eq!(page.total, 1);
2419 assert_eq!(page.snippets[0].id, "a");
2420
2421 let page = db
2423 .get_snippets_page(
2424 &other_user,
2425 &other_lib,
2426 DeltaCursor::Sequence(1),
2427 100,
2428 0,
2429 true,
2430 )
2431 .await
2432 .unwrap();
2433 assert_eq!(page.total, 1);
2434 assert_eq!(page.snippets[0].id, "c");
2435 }
2436
2437 #[tokio::test]
2440 async fn test_delete_library_gives_the_tombstone_a_fresh_sequence() {
2441 let db = setup_db().await;
2442 let user_id = db.create_user("key").await.unwrap();
2443 let lib_id = db.get_default_library(&user_id).await.unwrap();
2444
2445 db.upsert_snippet(&sequence_snippet("a", 100), &user_id, &lib_id)
2446 .await
2447 .unwrap();
2448 assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 1);
2449
2450 db.delete_library(&user_id, &lib_id).await.unwrap();
2451
2452 assert_eq!(db.get_latest_sequence(&user_id, &lib_id).await.unwrap(), 2);
2453 let page = db
2454 .get_snippets_page(&user_id, &lib_id, DeltaCursor::Sequence(2), 100, 0, true)
2455 .await
2456 .unwrap();
2457 assert_eq!(page.total, 1);
2458 assert!(page.snippets[0].deleted);
2459 }
2460}