commit ae79e76e77b6204f5e6f44d395b226a683e0ef0d
parent d76101fbad2701c7e6ff65c68dcc25f04f543011
Author: triesap <tyson@radroots.org>
Date: Thu, 26 Mar 2026 15:11:34 +0000
nostr-signer: add sqlite schema migrations
Diffstat:
8 files changed, 374 insertions(+), 0 deletions(-)
diff --git a/Cargo.lock b/Cargo.lock
@@ -2313,6 +2313,7 @@ dependencies = [
"radroots-identity",
"radroots-nostr-connect",
"radroots-runtime",
+ "radroots-sql-core",
"radroots-test-fixtures",
"serde",
"serde_json",
diff --git a/crates/nostr-signer/Cargo.toml b/crates/nostr-signer/Cargo.toml
@@ -13,6 +13,10 @@ homepage.workspace = true
documentation = "https://docs.rs/radroots-nostr-signer"
readme.workspace = true
+[features]
+default = []
+native = ["dep:radroots-sql-core", "radroots-sql-core/native"]
+
[dependencies]
hex = { workspace = true }
nostr = { workspace = true }
@@ -22,6 +26,7 @@ radroots-identity = { workspace = true, default-features = false, features = [
] }
radroots-nostr-connect = { workspace = true }
radroots-runtime = { workspace = true }
+radroots-sql-core = { workspace = true, optional = true }
serde = { workspace = true, features = ["derive"] }
sha2 = { workspace = true }
thiserror = { workspace = true }
@@ -29,6 +34,7 @@ url = { workspace = true }
uuid = { workspace = true }
[dev-dependencies]
+radroots-sql-core = { workspace = true, features = ["native"] }
radroots-test-fixtures = { workspace = true }
serde_json = { workspace = true }
tempfile = { workspace = true }
diff --git a/crates/nostr-signer/migrations/0000_init.down.sql b/crates/nostr-signer/migrations/0000_init.down.sql
@@ -0,0 +1,8 @@
+DROP TABLE IF EXISTS signer_request_audit;
+DROP TABLE IF EXISTS signer_connection_pending_request;
+DROP TABLE IF EXISTS signer_connection_auth_challenge;
+DROP TABLE IF EXISTS signer_connection_relay;
+DROP TABLE IF EXISTS signer_connection_permission_grant;
+DROP TABLE IF EXISTS signer_connection;
+DELETE FROM signer_store_metadata WHERE singleton_id = 1;
+DROP TABLE IF EXISTS signer_store_metadata;
diff --git a/crates/nostr-signer/migrations/0000_init.up.sql b/crates/nostr-signer/migrations/0000_init.up.sql
@@ -0,0 +1,97 @@
+CREATE TABLE IF NOT EXISTS signer_store_metadata (
+ singleton_id INTEGER PRIMARY KEY CHECK (singleton_id = 1),
+ store_version INTEGER NOT NULL,
+ signer_identity_id TEXT,
+ signer_identity_public_key_hex TEXT,
+ signer_identity_json TEXT,
+ updated_at TEXT NOT NULL DEFAULT (datetime('now'))
+);
+
+INSERT OR IGNORE INTO signer_store_metadata (singleton_id, store_version)
+VALUES (1, 1);
+
+CREATE TABLE IF NOT EXISTS signer_connection (
+ connection_id TEXT PRIMARY KEY,
+ client_public_key_hex TEXT NOT NULL,
+ signer_identity_id TEXT NOT NULL,
+ signer_identity_public_key_hex TEXT NOT NULL,
+ signer_identity_json TEXT NOT NULL,
+ user_identity_id TEXT NOT NULL,
+ user_identity_public_key_hex TEXT NOT NULL,
+ user_identity_json TEXT NOT NULL,
+ connect_secret_hash_algorithm TEXT,
+ connect_secret_hash_digest_hex TEXT,
+ connect_secret_consumed_at_unix INTEGER,
+ requested_permissions_json TEXT NOT NULL,
+ approval_requirement TEXT NOT NULL,
+ approval_state TEXT NOT NULL,
+ auth_state TEXT NOT NULL,
+ status TEXT NOT NULL,
+ status_reason TEXT,
+ created_at_unix INTEGER NOT NULL,
+ updated_at_unix INTEGER NOT NULL,
+ last_authenticated_at_unix INTEGER,
+ last_request_at_unix INTEGER
+);
+
+CREATE INDEX IF NOT EXISTS signer_connection_client_public_key_idx
+ON signer_connection (client_public_key_hex);
+
+CREATE INDEX IF NOT EXISTS signer_connection_user_identity_idx
+ON signer_connection (user_identity_id);
+
+CREATE INDEX IF NOT EXISTS signer_connection_connect_secret_digest_idx
+ON signer_connection (connect_secret_hash_digest_hex)
+WHERE connect_secret_hash_digest_hex IS NOT NULL;
+
+CREATE INDEX IF NOT EXISTS signer_connection_status_idx
+ON signer_connection (status);
+
+CREATE TABLE IF NOT EXISTS signer_connection_permission_grant (
+ connection_id TEXT NOT NULL REFERENCES signer_connection (connection_id) ON DELETE CASCADE,
+ permission TEXT NOT NULL,
+ granted_at_unix INTEGER NOT NULL,
+ PRIMARY KEY (connection_id, permission)
+);
+
+CREATE INDEX IF NOT EXISTS signer_connection_permission_grant_permission_idx
+ON signer_connection_permission_grant (permission);
+
+CREATE TABLE IF NOT EXISTS signer_connection_relay (
+ connection_id TEXT NOT NULL REFERENCES signer_connection (connection_id) ON DELETE CASCADE,
+ ordinal INTEGER NOT NULL,
+ relay_url TEXT NOT NULL,
+ PRIMARY KEY (connection_id, ordinal),
+ UNIQUE (connection_id, relay_url)
+);
+
+CREATE INDEX IF NOT EXISTS signer_connection_relay_url_idx
+ON signer_connection_relay (relay_url);
+
+CREATE TABLE IF NOT EXISTS signer_connection_auth_challenge (
+ connection_id TEXT PRIMARY KEY REFERENCES signer_connection (connection_id) ON DELETE CASCADE,
+ auth_url TEXT NOT NULL,
+ required_at_unix INTEGER NOT NULL,
+ authorized_at_unix INTEGER
+);
+
+CREATE TABLE IF NOT EXISTS signer_connection_pending_request (
+ connection_id TEXT PRIMARY KEY REFERENCES signer_connection (connection_id) ON DELETE CASCADE,
+ request_message_json TEXT NOT NULL,
+ created_at_unix INTEGER NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS signer_request_audit (
+ request_id TEXT PRIMARY KEY,
+ connection_id TEXT NOT NULL REFERENCES signer_connection (connection_id) ON DELETE CASCADE,
+ method TEXT NOT NULL,
+ decision TEXT NOT NULL,
+ message TEXT,
+ created_at_unix INTEGER NOT NULL
+);
+
+CREATE INDEX IF NOT EXISTS signer_request_audit_connection_id_idx
+ON signer_request_audit (connection_id);
+
+CREATE INDEX IF NOT EXISTS signer_request_audit_created_at_idx
+ON signer_request_audit (created_at_unix);
diff --git a/crates/nostr-signer/src/error.rs b/crates/nostr-signer/src/error.rs
@@ -44,6 +44,13 @@ impl From<radroots_runtime::RuntimeJsonError> for RadrootsNostrSignerError {
}
}
+#[cfg(feature = "native")]
+impl From<radroots_sql_core::SqlError> for RadrootsNostrSignerError {
+ fn from(value: radroots_sql_core::SqlError) -> Self {
+ Self::Store(value.to_string())
+ }
+}
+
#[cfg(test)]
mod tests {
use super::*;
diff --git a/crates/nostr-signer/src/lib.rs b/crates/nostr-signer/src/lib.rs
@@ -5,7 +5,11 @@ pub mod capability;
pub mod error;
pub mod evaluation;
pub mod manager;
+#[cfg(feature = "native")]
+pub mod migrations;
pub mod model;
+#[cfg(feature = "native")]
+pub mod sqlite;
pub mod store;
#[cfg(test)]
@@ -34,6 +38,8 @@ pub mod prelude {
RadrootsNostrSignerRequestDecision, RadrootsNostrSignerRequestId,
RadrootsNostrSignerSecretDigestAlgorithm, RadrootsNostrSignerStoreState,
};
+ #[cfg(feature = "native")]
+ pub use crate::sqlite::RadrootsNostrSignerSqliteDb;
pub use crate::store::{
RadrootsNostrFileSignerStore, RadrootsNostrMemorySignerStore, RadrootsNostrSignerStore,
};
diff --git a/crates/nostr-signer/src/migrations.rs b/crates/nostr-signer/src/migrations.rs
@@ -0,0 +1,29 @@
+#[cfg(feature = "native")]
+use radroots_sql_core::SqlExecutor;
+#[cfg(feature = "native")]
+use radroots_sql_core::error::SqlError;
+#[cfg(feature = "native")]
+use radroots_sql_core::migrations::{Migration, migrations_run_all_down, migrations_run_all_up};
+
+#[cfg(feature = "native")]
+pub static MIGRATIONS: &[Migration] = &[Migration {
+ name: "0000_init",
+ up_sql: include_str!("../migrations/0000_init.up.sql"),
+ down_sql: include_str!("../migrations/0000_init.down.sql"),
+}];
+
+#[cfg(feature = "native")]
+pub fn run_all_up<E>(executor: &E) -> Result<(), SqlError>
+where
+ E: SqlExecutor,
+{
+ migrations_run_all_up(executor, MIGRATIONS)
+}
+
+#[cfg(feature = "native")]
+pub fn run_all_down<E>(executor: &E) -> Result<(), SqlError>
+where
+ E: SqlExecutor,
+{
+ migrations_run_all_down(executor, MIGRATIONS)
+}
diff --git a/crates/nostr-signer/src/sqlite.rs b/crates/nostr-signer/src/sqlite.rs
@@ -0,0 +1,220 @@
+use crate::error::RadrootsNostrSignerError;
+use crate::migrations;
+use radroots_sql_core::{SqlExecutor, SqliteExecutor};
+use std::path::Path;
+
+pub struct RadrootsNostrSignerSqliteDb {
+ executor: SqliteExecutor,
+ file_backed: bool,
+}
+
+impl RadrootsNostrSignerSqliteDb {
+ pub fn open(path: impl AsRef<Path>) -> Result<Self, RadrootsNostrSignerError> {
+ let path = path.as_ref();
+ if let Some(parent) = path.parent()
+ && !parent.as_os_str().is_empty()
+ {
+ std::fs::create_dir_all(parent)
+ .map_err(|error| RadrootsNostrSignerError::Store(error.to_string()))?;
+ }
+ let executor = SqliteExecutor::open(path)?;
+ let db = Self {
+ executor,
+ file_backed: true,
+ };
+ db.configure()?;
+ db.migrate_up()?;
+ Ok(db)
+ }
+
+ pub fn open_memory() -> Result<Self, RadrootsNostrSignerError> {
+ let executor = SqliteExecutor::open_memory()?;
+ let db = Self {
+ executor,
+ file_backed: false,
+ };
+ db.configure()?;
+ db.migrate_up()?;
+ Ok(db)
+ }
+
+ pub fn executor(&self) -> &SqliteExecutor {
+ &self.executor
+ }
+
+ pub fn migrate_up(&self) -> Result<(), RadrootsNostrSignerError> {
+ migrations::run_all_up(&self.executor)?;
+ Ok(())
+ }
+
+ pub fn migrate_down(&self) -> Result<(), RadrootsNostrSignerError> {
+ migrations::run_all_down(&self.executor)?;
+ Ok(())
+ }
+
+ fn configure(&self) -> Result<(), RadrootsNostrSignerError> {
+ let pragma_batch = if self.file_backed {
+ "PRAGMA foreign_keys = ON;
+ PRAGMA synchronous = FULL;
+ PRAGMA wal_autocheckpoint = 1000;
+ PRAGMA busy_timeout = 5000;
+ PRAGMA temp_store = MEMORY;"
+ } else {
+ "PRAGMA foreign_keys = ON;
+ PRAGMA synchronous = NORMAL;
+ PRAGMA busy_timeout = 5000;
+ PRAGMA temp_store = MEMORY;"
+ };
+ let _ = self.executor.exec(pragma_batch, "[]")?;
+ if self.file_backed {
+ let _ = self.executor.query_raw("PRAGMA journal_mode = WAL", "[]")?;
+ } else {
+ let _ = self
+ .executor
+ .query_raw("PRAGMA journal_mode = MEMORY", "[]")?;
+ }
+ Ok(())
+ }
+}
+
+#[cfg(test)]
+mod tests {
+ use super::RadrootsNostrSignerSqliteDb;
+ use radroots_sql_core::SqlExecutor;
+ use serde_json::Value;
+
+ fn query_values(
+ db: &RadrootsNostrSignerSqliteDb,
+ sql: &str,
+ ) -> Vec<serde_json::Map<String, Value>> {
+ let raw = db.executor().query_raw(sql, "[]").expect("query");
+ serde_json::from_str::<Vec<serde_json::Map<String, Value>>>(&raw).expect("rows")
+ }
+
+ fn query_single_text(db: &RadrootsNostrSignerSqliteDb, sql: &str, field: &str) -> String {
+ query_values(db, sql)
+ .into_iter()
+ .next()
+ .and_then(|row| row.get(field).cloned())
+ .and_then(|value| value.as_str().map(ToOwned::to_owned))
+ .expect("single text row")
+ }
+
+ fn query_single_i64(db: &RadrootsNostrSignerSqliteDb, sql: &str, field: &str) -> i64 {
+ query_values(db, sql)
+ .into_iter()
+ .next()
+ .and_then(|row| row.get(field).cloned())
+ .and_then(|value| value.as_i64())
+ .expect("single integer row")
+ }
+
+ #[test]
+ fn open_memory_bootstraps_schema_and_migrations_idempotently() {
+ let db = RadrootsNostrSignerSqliteDb::open_memory().expect("open memory db");
+ db.migrate_up().expect("rerun migrations");
+
+ let tables = query_values(
+ &db,
+ "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name",
+ );
+ let table_names = tables
+ .into_iter()
+ .filter_map(|row| {
+ row.get("name")
+ .and_then(Value::as_str)
+ .map(ToOwned::to_owned)
+ })
+ .collect::<Vec<_>>();
+ assert!(table_names.iter().any(|name| name == "__migrations"));
+ assert!(
+ table_names
+ .iter()
+ .any(|name| name == "signer_store_metadata")
+ );
+ assert!(table_names.iter().any(|name| name == "signer_connection"));
+ assert!(
+ table_names
+ .iter()
+ .any(|name| name == "signer_connection_permission_grant")
+ );
+ assert!(
+ table_names
+ .iter()
+ .any(|name| name == "signer_connection_relay")
+ );
+ assert!(
+ table_names
+ .iter()
+ .any(|name| name == "signer_connection_auth_challenge")
+ );
+ assert!(
+ table_names
+ .iter()
+ .any(|name| name == "signer_connection_pending_request")
+ );
+ assert!(
+ table_names
+ .iter()
+ .any(|name| name == "signer_request_audit")
+ );
+
+ let migration_count = query_single_i64(
+ &db,
+ "SELECT COUNT(*) AS applied_count FROM __migrations",
+ "applied_count",
+ );
+ assert_eq!(migration_count, 1);
+
+ let store_version = query_single_i64(
+ &db,
+ "SELECT store_version FROM signer_store_metadata WHERE singleton_id = 1",
+ "store_version",
+ );
+ assert_eq!(store_version, 1);
+ }
+
+ #[test]
+ fn file_database_uses_wal_and_foreign_keys() {
+ let temp = tempfile::tempdir().expect("tempdir");
+ let db = RadrootsNostrSignerSqliteDb::open(temp.path().join("signer.sqlite"))
+ .expect("open sqlite file db");
+
+ assert_eq!(
+ query_single_text(&db, "PRAGMA journal_mode", "journal_mode"),
+ "wal"
+ );
+ assert_eq!(
+ query_single_i64(&db, "PRAGMA foreign_keys", "foreign_keys"),
+ 1
+ );
+ }
+
+ #[test]
+ fn migrate_down_and_up_roundtrip_restores_schema() {
+ let db = RadrootsNostrSignerSqliteDb::open_memory().expect("open memory db");
+ db.migrate_down().expect("migrate down");
+
+ let tables = query_values(
+ &db,
+ "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name",
+ );
+ let table_names = tables
+ .into_iter()
+ .filter_map(|row| {
+ row.get("name")
+ .and_then(Value::as_str)
+ .map(ToOwned::to_owned)
+ })
+ .collect::<Vec<_>>();
+ assert_eq!(table_names, vec!["__migrations".to_owned()]);
+
+ db.migrate_up().expect("migrate up again");
+ let migration_count = query_single_i64(
+ &db,
+ "SELECT COUNT(*) AS applied_count FROM __migrations",
+ "applied_count",
+ );
+ assert_eq!(migration_count, 1);
+ }
+}
