commit 38e2fe77da3dbb0eb29d9a7dfda338149c48105f
parent b18932a171b0fc75f429c6204272ce521a03f52d
Author: triesap <tyson@radroots.org>
Date: Fri, 17 Apr 2026 19:11:33 +0000
sqlite: add the radroots_app sqlite substrate
Diffstat:
7 files changed, 509 insertions(+), 0 deletions(-)
diff --git a/Cargo.lock b/Cargo.lock
@@ -1685,6 +1685,18 @@ dependencies = [
]
[[package]]
+name = "fallible-iterator"
+version = "0.3.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "2acce4a10f12dc2fb14a218589d4f1f62ef011b2d0cc4b3cb1bba8e94da14649"
+
+[[package]]
+name = "fallible-streaming-iterator"
+version = "0.1.9"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "7360491ce676a36bf9bb3c56c1aa791658183a54d2744120f27285738d90465a"
+
+[[package]]
name = "fastrand"
version = "1.9.0"
source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -2568,6 +2580,9 @@ name = "hashbrown"
version = "0.14.5"
source = "registry+https://github.com/rust-lang/crates.io-index"
checksum = "e5274423e17b7c9fc20b6e7e208532f9b19825d82dfd615708b70edd83df41f1"
+dependencies = [
+ "ahash",
+]
[[package]]
name = "hashbrown"
@@ -2585,6 +2600,15 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
checksum = "4f467dd6dccf739c208452f8014c75c18bb8301b050ad1cfb27153803edb0f51"
[[package]]
+name = "hashlink"
+version = "0.9.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "6ba4ff7128dee98c7dc9794b6a411377e1404dba1c97deb8d1a55297bd25d8af"
+dependencies = [
+ "hashbrown 0.14.5",
+]
+
+[[package]]
name = "heck"
version = "0.4.1"
source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -3260,6 +3284,17 @@ dependencies = [
]
[[package]]
+name = "libsqlite3-sys"
+version = "0.30.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "2e99fb7a497b1e3339bc746195567ed8d3e24945ecd636e3619d20b9de9e9149"
+dependencies = [
+ "cc",
+ "pkg-config",
+ "vcpkg",
+]
+
+[[package]]
name = "linux-raw-sys"
version = "0.4.15"
source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -4601,6 +4636,14 @@ dependencies = [
]
[[package]]
+name = "radroots_app_sqlite"
+version = "0.1.0"
+dependencies = [
+ "rusqlite",
+ "thiserror 2.0.18",
+]
+
+[[package]]
name = "radroots_app_ui"
version = "0.1.0"
dependencies = [
@@ -4913,6 +4956,20 @@ source = "registry+https://github.com/rust-lang/crates.io-index"
checksum = "6c20b6793b5c2fa6553b250154b78d6d0db37e72700ae35fad9387a46f487c97"
[[package]]
+name = "rusqlite"
+version = "0.32.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "7753b721174eb8ff87a9a0e799e2d7bc3749323e773db92e0984debb00019d6e"
+dependencies = [
+ "bitflags 2.11.1",
+ "fallible-iterator",
+ "fallible-streaming-iterator",
+ "hashlink",
+ "libsqlite3-sys",
+ "smallvec",
+]
+
+[[package]]
name = "rust-embed"
version = "8.11.0"
source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -6618,6 +6675,12 @@ dependencies = [
]
[[package]]
+name = "vcpkg"
+version = "0.2.15"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "accd4ea62f7bb7a82fe23066fb0957d48ef677f6eeb8215f372f52e48bb32426"
+
+[[package]]
name = "version_check"
version = "0.9.5"
source = "registry+https://github.com/rust-lang/crates.io-index"
diff --git a/Cargo.toml b/Cargo.toml
@@ -3,6 +3,7 @@ members = [
"crates/shared/core",
"crates/shared/i18n",
"crates/shared/models",
+ "crates/shared/sqlite",
"crates/shared/ui",
"crates/launchers/desktop",
]
@@ -29,9 +30,12 @@ mf2-i18n-native = { path = "../../../../vendor/triesap/mf2-i18n/crates/mf2-i18n-
radroots_app_core = { path = "crates/shared/core", version = "0.1.0" }
radroots_app_i18n = { path = "crates/shared/i18n", version = "0.1.0" }
radroots_app_models = { path = "crates/shared/models", version = "0.1.0" }
+radroots_app_sqlite = { path = "crates/shared/sqlite", version = "0.1.0" }
radroots_app_ui = { path = "crates/shared/ui", version = "0.1.0" }
+rusqlite = { version = "0.32", features = ["bundled"] }
serde = { version = "1.0", features = ["derive"] }
serde_json = "1.0"
+thiserror = "2"
toml = "0.8"
uuid = { version = "1", features = ["serde", "v7"] }
diff --git a/crates/shared/sqlite/Cargo.toml b/crates/shared/sqlite/Cargo.toml
@@ -0,0 +1,15 @@
+[package]
+name = "radroots_app_sqlite"
+version.workspace = true
+edition.workspace = true
+authors.workspace = true
+rust-version.workspace = true
+license.workspace = true
+publish = false
+
+[dependencies]
+rusqlite.workspace = true
+thiserror.workspace = true
+
+[lints]
+workspace = true
diff --git a/crates/shared/sqlite/migrations/0001_init.sql b/crates/shared/sqlite/migrations/0001_init.sql
@@ -0,0 +1,77 @@
+CREATE TABLE farms (
+ id TEXT PRIMARY KEY NOT NULL,
+ display_name TEXT NOT NULL,
+ readiness TEXT NOT NULL CHECK (readiness IN ('incomplete', 'ready')),
+ created_at TEXT NOT NULL,
+ updated_at TEXT NOT NULL
+);
+
+CREATE TABLE fulfillment_windows (
+ id TEXT PRIMARY KEY NOT NULL,
+ farm_id TEXT NOT NULL REFERENCES farms(id) ON DELETE CASCADE,
+ starts_at TEXT NOT NULL,
+ ends_at TEXT NOT NULL,
+ capacity_limit INTEGER,
+ created_at TEXT NOT NULL,
+ updated_at TEXT NOT NULL
+);
+
+CREATE TABLE products (
+ id TEXT PRIMARY KEY NOT NULL,
+ farm_id TEXT NOT NULL REFERENCES farms(id) ON DELETE CASCADE,
+ title TEXT NOT NULL,
+ status TEXT NOT NULL CHECK (status IN ('draft', 'published', 'paused')),
+ stock_count INTEGER NOT NULL DEFAULT 0,
+ updated_at TEXT NOT NULL
+);
+
+CREATE TABLE orders (
+ id TEXT PRIMARY KEY NOT NULL,
+ farm_id TEXT NOT NULL REFERENCES farms(id) ON DELETE CASCADE,
+ fulfillment_window_id TEXT REFERENCES fulfillment_windows(id) ON DELETE SET NULL,
+ order_number TEXT NOT NULL,
+ customer_display_name TEXT NOT NULL,
+ status TEXT NOT NULL CHECK (
+ status IN ('needs_action', 'scheduled', 'packed', 'completed', 'refunded')
+ ),
+ updated_at TEXT NOT NULL
+);
+
+CREATE TABLE local_outbox (
+ id TEXT PRIMARY KEY NOT NULL,
+ aggregate_kind TEXT NOT NULL,
+ aggregate_id TEXT NOT NULL,
+ operation_kind TEXT NOT NULL,
+ payload_json TEXT NOT NULL,
+ created_at TEXT NOT NULL,
+ available_at TEXT NOT NULL,
+ attempt_count INTEGER NOT NULL DEFAULT 0
+);
+
+CREATE TABLE local_conflicts (
+ id TEXT PRIMARY KEY NOT NULL,
+ aggregate_kind TEXT NOT NULL,
+ aggregate_id TEXT NOT NULL,
+ conflict_kind TEXT NOT NULL,
+ local_payload_json TEXT NOT NULL,
+ remote_payload_json TEXT,
+ detected_at TEXT NOT NULL,
+ resolved_at TEXT
+);
+
+CREATE TABLE sync_checkpoints (
+ id INTEGER PRIMARY KEY CHECK (id = 1),
+ last_sync_started_at TEXT,
+ last_sync_completed_at TEXT,
+ last_remote_cursor TEXT,
+ last_error_message TEXT
+);
+
+CREATE INDEX idx_products_farm_status ON products(farm_id, status);
+CREATE INDEX idx_orders_farm_status ON orders(farm_id, status);
+CREATE INDEX idx_fulfillment_windows_farm_starts_at ON fulfillment_windows(farm_id, starts_at);
+CREATE INDEX idx_local_outbox_available_at ON local_outbox(available_at);
+CREATE INDEX idx_local_outbox_aggregate ON local_outbox(aggregate_kind, aggregate_id);
+CREATE INDEX idx_local_conflicts_aggregate ON local_conflicts(aggregate_kind, aggregate_id);
+
+INSERT INTO sync_checkpoints (id) VALUES (1);
diff --git a/crates/shared/sqlite/src/error.rs b/crates/shared/sqlite/src/error.rs
@@ -0,0 +1,68 @@
+use std::{io, path::PathBuf};
+
+use thiserror::Error;
+
+#[derive(Debug, Error)]
+pub enum AppSqliteError {
+ #[error("failed to create sqlite parent directory `{path}`")]
+ CreateParentDirectory {
+ path: PathBuf,
+ #[source]
+ source: io::Error,
+ },
+ #[error("failed to open sqlite database at `{path}`")]
+ OpenPath {
+ path: PathBuf,
+ #[source]
+ source: rusqlite::Error,
+ },
+ #[error("failed to open in-memory sqlite database")]
+ OpenInMemory {
+ #[source]
+ source: rusqlite::Error,
+ },
+ #[error("failed to configure sqlite busy timeout")]
+ ConfigureBusyTimeout {
+ #[source]
+ source: rusqlite::Error,
+ },
+ #[error("failed to apply sqlite pragma `{pragma}`")]
+ ApplyPragma {
+ pragma: &'static str,
+ #[source]
+ source: rusqlite::Error,
+ },
+ #[error("failed to read sqlite schema version")]
+ ReadSchemaVersion {
+ #[source]
+ source: rusqlite::Error,
+ },
+ #[error(
+ "sqlite schema version {current} is newer than supported version {latest}; manual migration is required"
+ )]
+ UnsupportedSchemaVersion { current: u32, latest: u32 },
+ #[error("failed to begin sqlite migration transaction for version {version}")]
+ BeginMigration {
+ version: u32,
+ #[source]
+ source: rusqlite::Error,
+ },
+ #[error("failed to execute sqlite migration {version}")]
+ ExecuteMigration {
+ version: u32,
+ #[source]
+ source: rusqlite::Error,
+ },
+ #[error("failed to record sqlite schema version {version}")]
+ RecordSchemaVersion {
+ version: u32,
+ #[source]
+ source: rusqlite::Error,
+ },
+ #[error("failed to commit sqlite migration {version}")]
+ CommitMigration {
+ version: u32,
+ #[source]
+ source: rusqlite::Error,
+ },
+}
diff --git a/crates/shared/sqlite/src/lib.rs b/crates/shared/sqlite/src/lib.rs
@@ -0,0 +1,262 @@
+#![forbid(unsafe_code)]
+
+mod error;
+mod migrations;
+
+use std::{fs, path::PathBuf, time::Duration};
+
+use rusqlite::Connection;
+
+pub use error::AppSqliteError;
+pub use migrations::latest_schema_version;
+
+const SQLITE_BUSY_TIMEOUT_MS: u64 = 5_000;
+
+#[derive(Clone, Debug, Eq, PartialEq)]
+pub enum DatabaseTarget {
+ InMemory,
+ Path(PathBuf),
+}
+
+pub struct AppSqliteStore {
+ connection: Connection,
+}
+
+impl AppSqliteStore {
+ pub fn open(target: DatabaseTarget) -> Result<Self, AppSqliteError> {
+ let mut connection = open_connection(&target)?;
+ bootstrap_connection(&mut connection, &target)?;
+
+ Ok(Self { connection })
+ }
+
+ pub fn connection(&self) -> &Connection {
+ &self.connection
+ }
+
+ pub fn into_connection(self) -> Connection {
+ self.connection
+ }
+
+ pub fn schema_version(&self) -> Result<u32, AppSqliteError> {
+ schema_version(&self.connection)
+ }
+}
+
+fn open_connection(target: &DatabaseTarget) -> Result<Connection, AppSqliteError> {
+ match target {
+ DatabaseTarget::InMemory => {
+ Connection::open_in_memory().map_err(|source| AppSqliteError::OpenInMemory { source })
+ }
+ DatabaseTarget::Path(path) => {
+ if let Some(parent) = path.parent() {
+ if !parent.as_os_str().is_empty() {
+ fs::create_dir_all(parent).map_err(|source| {
+ AppSqliteError::CreateParentDirectory {
+ path: parent.to_path_buf(),
+ source,
+ }
+ })?;
+ }
+ }
+
+ Connection::open(path).map_err(|source| AppSqliteError::OpenPath {
+ path: path.clone(),
+ source,
+ })
+ }
+ }
+}
+
+fn bootstrap_connection(
+ connection: &mut Connection,
+ target: &DatabaseTarget,
+) -> Result<(), AppSqliteError> {
+ connection
+ .busy_timeout(Duration::from_millis(SQLITE_BUSY_TIMEOUT_MS))
+ .map_err(|source| AppSqliteError::ConfigureBusyTimeout { source })?;
+
+ apply_pragma(connection, "foreign_keys", "ON")?;
+ apply_pragma(connection, "synchronous", "NORMAL")?;
+
+ if matches!(target, DatabaseTarget::Path(_)) {
+ connection
+ .query_row("PRAGMA journal_mode = WAL", [], |row| {
+ row.get::<_, String>(0)
+ })
+ .map_err(|source| AppSqliteError::ApplyPragma {
+ pragma: "journal_mode",
+ source,
+ })?;
+ }
+
+ apply_migrations(connection)
+}
+
+fn apply_pragma(
+ connection: &Connection,
+ pragma: &'static str,
+ value: &str,
+) -> Result<(), AppSqliteError> {
+ let sql = format!("PRAGMA {pragma} = {value}");
+ connection
+ .execute_batch(&sql)
+ .map_err(|source| AppSqliteError::ApplyPragma { pragma, source })
+}
+
+fn schema_version(connection: &Connection) -> Result<u32, AppSqliteError> {
+ connection
+ .query_row("PRAGMA user_version", [], |row| row.get(0))
+ .map_err(|source| AppSqliteError::ReadSchemaVersion { source })
+}
+
+fn apply_migrations(connection: &mut Connection) -> Result<(), AppSqliteError> {
+ let current_version = schema_version(connection)?;
+ let latest_version = migrations::latest_schema_version();
+
+ if current_version > latest_version {
+ return Err(AppSqliteError::UnsupportedSchemaVersion {
+ current: current_version,
+ latest: latest_version,
+ });
+ }
+
+ for (version, sql) in migrations::pending_migrations(current_version) {
+ let transaction = connection
+ .transaction()
+ .map_err(|source| AppSqliteError::BeginMigration { version, source })?;
+
+ transaction
+ .execute_batch(sql)
+ .map_err(|source| AppSqliteError::ExecuteMigration { version, source })?;
+ transaction
+ .pragma_update(None, "user_version", version)
+ .map_err(|source| AppSqliteError::RecordSchemaVersion { version, source })?;
+ transaction
+ .commit()
+ .map_err(|source| AppSqliteError::CommitMigration { version, source })?;
+ }
+
+ Ok(())
+}
+
+#[cfg(test)]
+mod tests {
+ use super::{AppSqliteStore, DatabaseTarget, latest_schema_version};
+ use rusqlite::Connection;
+ use std::{
+ env, fs,
+ path::PathBuf,
+ time::{SystemTime, UNIX_EPOCH},
+ };
+
+ #[test]
+ fn file_store_bootstrap_applies_pragmas_and_migrations() {
+ let path = temp_database_path("bootstrap");
+ let store =
+ AppSqliteStore::open(DatabaseTarget::Path(path.clone())).expect("store should open");
+ let connection = store.connection();
+
+ assert_eq!(
+ store.schema_version().expect("schema version"),
+ latest_schema_version()
+ );
+ assert_eq!(pragma_i64(connection, "foreign_keys"), 1);
+ assert_eq!(pragma_text(connection, "journal_mode"), "wal");
+ assert!(table_exists(connection, "farms"));
+ assert!(table_exists(connection, "products"));
+ assert!(table_exists(connection, "orders"));
+ assert!(table_exists(connection, "local_outbox"));
+ assert!(table_exists(connection, "local_conflicts"));
+ assert!(table_exists(connection, "sync_checkpoints"));
+ assert_eq!(row_count(connection, "sync_checkpoints"), 1);
+
+ drop(store);
+ remove_database_artifacts(&path);
+ }
+
+ #[test]
+ fn reopening_existing_store_is_idempotent() {
+ let path = temp_database_path("reopen");
+ AppSqliteStore::open(DatabaseTarget::Path(path.clone())).expect("first open should work");
+ let reopened = AppSqliteStore::open(DatabaseTarget::Path(path.clone()))
+ .expect("second open should work");
+
+ assert_eq!(
+ reopened.schema_version().expect("schema version"),
+ latest_schema_version()
+ );
+ assert_eq!(row_count(reopened.connection(), "sync_checkpoints"), 1);
+
+ drop(reopened);
+ remove_database_artifacts(&path);
+ }
+
+ #[test]
+ fn in_memory_store_bootstraps_without_file_only_pragmas() {
+ let store = AppSqliteStore::open(DatabaseTarget::InMemory).expect("store should open");
+
+ assert_eq!(
+ store.schema_version().expect("schema version"),
+ latest_schema_version()
+ );
+ assert_eq!(pragma_i64(store.connection(), "foreign_keys"), 1);
+ assert!(table_exists(store.connection(), "farms"));
+ }
+
+ fn table_exists(connection: &Connection, table_name: &str) -> bool {
+ connection
+ .query_row(
+ "SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?1)",
+ [table_name],
+ |row| row.get::<_, i64>(0),
+ )
+ .expect("table existence query should succeed")
+ == 1
+ }
+
+ fn row_count(connection: &Connection, table_name: &str) -> i64 {
+ let sql = format!("SELECT COUNT(*) FROM {table_name}");
+ connection
+ .query_row(&sql, [], |row| row.get(0))
+ .expect("row count query should succeed")
+ }
+
+ fn pragma_i64(connection: &Connection, pragma_name: &str) -> i64 {
+ let sql = format!("PRAGMA {pragma_name}");
+ connection
+ .query_row(&sql, [], |row| row.get(0))
+ .expect("pragma query should succeed")
+ }
+
+ fn pragma_text(connection: &Connection, pragma_name: &str) -> String {
+ let sql = format!("PRAGMA {pragma_name}");
+ connection
+ .query_row(&sql, [], |row| row.get(0))
+ .expect("pragma query should succeed")
+ }
+
+ fn temp_database_path(test_name: &str) -> PathBuf {
+ let nonce = SystemTime::now()
+ .duration_since(UNIX_EPOCH)
+ .expect("time should move forward")
+ .as_nanos();
+
+ env::temp_dir()
+ .join("radroots_app_sqlite_tests")
+ .join(format!("{test_name}-{nonce}"))
+ .join("app.sqlite3")
+ }
+
+ fn remove_database_artifacts(database_path: &std::path::Path) {
+ if let Some(parent) = database_path.parent() {
+ let wal_path = database_path.with_extension("sqlite3-wal");
+ let shm_path = database_path.with_extension("sqlite3-shm");
+
+ let _ = fs::remove_file(&wal_path);
+ let _ = fs::remove_file(&shm_path);
+ let _ = fs::remove_file(database_path);
+ let _ = fs::remove_dir_all(parent);
+ }
+ }
+}
diff --git a/crates/shared/sqlite/src/migrations.rs b/crates/shared/sqlite/src/migrations.rs
@@ -0,0 +1,20 @@
+struct Migration {
+ version: u32,
+ sql: &'static str,
+}
+
+const MIGRATIONS: &[Migration] = &[Migration {
+ version: 1,
+ sql: include_str!("../migrations/0001_init.sql"),
+}];
+
+pub fn latest_schema_version() -> u32 {
+ MIGRATIONS.last().map_or(0, |migration| migration.version)
+}
+
+pub fn pending_migrations(current_version: u32) -> impl Iterator<Item = (u32, &'static str)> {
+ MIGRATIONS
+ .iter()
+ .filter(move |migration| migration.version > current_version)
+ .map(|migration| (migration.version, migration.sql))
+}
