commit 7c76e35a3c59a830107a4d00cdb714e895802870
parent b84b06aeeaa318c5ace692165f0ff4a1e0f3eade
Author: triesap <tyson@radroots.org>
Date: Mon, 3 Aug 2026 22:47:24 +0000
storage: adopt transactional normalized repositories
- move account profile selection and preference access to normalized tables
- commit identity and local binding writes in one SQLite transaction
- validate every required account binding and singleton row mutation
- preserve deterministic ordering cascades and V5-derived data behavior
Diffstat:
6 files changed, 128 insertions(+), 60 deletions(-)
diff --git a/crates/studio_ffi/src/commands.rs b/crates/studio_ffi/src/commands.rs
@@ -397,7 +397,7 @@ mod tests {
assert_eq!(property("baseline.id"), Some("studio-runtime-v5"));
assert_eq!(property("schema.version"), Some("5"));
- assert_eq!(CURRENT_SCHEMA_VERSION, 7);
+ assert_eq!(CURRENT_SCHEMA_VERSION, 8);
assert_eq!(property("ffi.contract"), Some("legacy-unversioned-v1"));
assert_eq!(property("ffi.snapshot.schema"), Some("1"));
assert_eq!(property("ffi.runtime.version"), Some("0.1.0-alpha"));
diff --git a/crates/studio_storage/migrations/V8__normalized_account_preferences.sql b/crates/studio_storage/migrations/V8__normalized_account_preferences.sql
@@ -0,0 +1,10 @@
+CREATE TABLE account_preferences (
+ owner_public_key TEXT NOT NULL REFERENCES account_identities(public_key) ON DELETE CASCADE,
+ preference_key TEXT NOT NULL CHECK (preference_key = 'namespace_probe'),
+ preference_value TEXT NOT NULL CHECK (length(preference_value) <= 4096),
+ PRIMARY KEY (owner_public_key, preference_key)
+) STRICT;
+
+INSERT INTO account_preferences (owner_public_key, preference_key, preference_value)
+SELECT owner_pubkey, preference_key, preference_value
+FROM account_namespace;
diff --git a/crates/studio_storage/src/account_namespace.rs b/crates/studio_storage/src/account_namespace.rs
@@ -14,8 +14,8 @@ impl AccountNamespaceRepository for Database {
) -> Result<Option<String>, SafeError> {
self.connection()
.query_row(
- "SELECT preference_value FROM account_namespace \
- WHERE owner_pubkey = ?1 AND preference_key = ?2",
+ "SELECT preference_value FROM account_preferences \
+ WHERE owner_public_key = ?1 AND preference_key = ?2",
params![owner.to_hex(), encode_key(key)],
|row| row.get(0),
)
@@ -34,8 +34,8 @@ impl AccountNamespaceRepository for Database {
}
self.connection()
.execute(
- "INSERT INTO account_namespace (owner_pubkey, preference_key, preference_value) \
- VALUES (?1, ?2, ?3) ON CONFLICT(owner_pubkey, preference_key) DO UPDATE SET \
+ "INSERT INTO account_preferences (owner_public_key, preference_key, preference_value) \
+ VALUES (?1, ?2, ?3) ON CONFLICT(owner_public_key, preference_key) DO UPDATE SET \
preference_value = excluded.preference_value",
params![owner.to_hex(), encode_key(key), value],
)
@@ -46,7 +46,7 @@ impl AccountNamespaceRepository for Database {
fn clear_owner(&self, owner: PublicKey) -> Result<(), SafeError> {
self.connection()
.execute(
- "DELETE FROM account_namespace WHERE owner_pubkey = ?1",
+ "DELETE FROM account_preferences WHERE owner_public_key = ?1",
[owner.to_hex()],
)
.map(|_| ())
diff --git a/crates/studio_storage/src/accounts.rs b/crates/studio_storage/src/accounts.rs
@@ -12,8 +12,12 @@ impl AccountRepository for Database {
let connection = self.connection();
let mut statement = connection
.prepare(
- "SELECT pubkey, npub, signer_kind, key_availability, label, created_at, \
- last_used_at FROM accounts ORDER BY created_at ASC, pubkey ASC",
+ "SELECT identity.public_key, identity.npub, binding.binding_kind, \
+ binding.availability, identity.label, identity.created_at, identity.last_used_at \
+ FROM account_identities AS identity \
+ JOIN local_signer_bindings AS binding \
+ ON binding.account_public_key = identity.public_key \
+ ORDER BY identity.created_at ASC, identity.public_key ASC",
)
.map_err(|_| storage_error())?;
let rows = statement
@@ -26,8 +30,12 @@ impl AccountRepository for Database {
fn find_account(&self, public_key: PublicKey) -> Result<Option<AccountSummary>, SafeError> {
self.connection()
.query_row(
- "SELECT pubkey, npub, signer_kind, key_availability, label, created_at, \
- last_used_at FROM accounts WHERE pubkey = ?1",
+ "SELECT identity.public_key, identity.npub, binding.binding_kind, \
+ binding.availability, identity.label, identity.created_at, identity.last_used_at \
+ FROM account_identities AS identity \
+ JOIN local_signer_bindings AS binding \
+ ON binding.account_public_key = identity.public_key \
+ WHERE identity.public_key = ?1",
[public_key.to_hex()],
decode_account,
)
@@ -37,56 +45,94 @@ impl AccountRepository for Database {
fn insert_account(&self, account: &AccountSummary) -> Result<(), SafeError> {
let encoded = EncodedAccount::from(account);
- let result = self.connection().execute(
- "INSERT INTO accounts (pubkey, npub, signer_kind, key_availability, label, \
- created_at, last_used_at) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
+ let mut connection = self.connection();
+ let transaction = connection.transaction().map_err(|_| storage_error())?;
+ let result = transaction.execute(
+ "INSERT INTO account_identities (public_key, npub, label, created_at, last_used_at) \
+ VALUES (?1, ?2, ?3, ?4, ?5)",
params![
encoded.public_key,
encoded.npub,
- encoded.signer_kind,
- encoded.key_availability,
encoded.label,
encoded.created_at,
- encoded.last_used_at,
+ encoded.last_used_at
],
);
match result {
- Ok(1) => Ok(()),
- Err(error) if is_constraint_violation(&error) => Err(account_exists()),
- Ok(_) | Err(_) => Err(storage_error()),
+ Ok(1) => {}
+ Err(error) if is_constraint_violation(&error) => return Err(account_exists()),
+ Ok(_) | Err(_) => return Err(storage_error()),
+ }
+ if transaction
+ .execute(
+ "INSERT INTO local_signer_bindings (account_public_key, binding_public_key, \
+ binding_kind, availability) VALUES (?1, ?1, ?2, ?3)",
+ params![
+ encoded.public_key,
+ encoded.signer_kind,
+ encoded.key_availability
+ ],
+ )
+ .map_err(|_| storage_error())?
+ != 1
+ {
+ return Err(storage_error());
}
+ transaction.commit().map_err(|_| storage_error())
}
fn update_account(&self, account: &AccountSummary) -> Result<(), SafeError> {
let encoded = EncodedAccount::from(account);
+ let mut connection = self.connection();
+ let transaction = connection.transaction().map_err(|_| storage_error())?;
+ let identity_rows = transaction
+ .execute(
+ "UPDATE account_identities SET npub = ?2, label = ?5, created_at = ?6, \
+ last_used_at = ?7 WHERE public_key = ?1",
+ params![
+ encoded.public_key,
+ encoded.npub,
+ encoded.signer_kind,
+ encoded.key_availability,
+ encoded.label,
+ encoded.created_at,
+ encoded.last_used_at,
+ ],
+ )
+ .map_err(|_| storage_error())?;
+ if identity_rows == 0 {
+ return Err(account_not_found());
+ }
+ if identity_rows != 1 {
+ return Err(storage_error());
+ }
+ let binding_rows = transaction
+ .execute(
+ "UPDATE local_signer_bindings SET binding_kind = ?2, availability = ?3 \
+ WHERE account_public_key = ?1 AND binding_public_key = ?1",
+ params![
+ encoded.public_key,
+ encoded.signer_kind,
+ encoded.key_availability
+ ],
+ )
+ .map_err(|_| storage_error())?;
+ if binding_rows != 1 {
+ return Err(corrupt_storage_error());
+ }
+ transaction.commit().map_err(|_| storage_error())
+ }
+
+ fn remove_account(&self, public_key: PublicKey) -> Result<(), SafeError> {
match self.connection().execute(
- "UPDATE accounts SET npub = ?2, signer_kind = ?3, key_availability = ?4, \
- label = ?5, created_at = ?6, last_used_at = ?7 WHERE pubkey = ?1",
- params![
- encoded.public_key,
- encoded.npub,
- encoded.signer_kind,
- encoded.key_availability,
- encoded.label,
- encoded.created_at,
- encoded.last_used_at,
- ],
+ "DELETE FROM account_identities WHERE public_key = ?1",
+ [public_key.to_hex()],
) {
Ok(1) => Ok(()),
Ok(0) => Err(account_not_found()),
Ok(_) | Err(_) => Err(storage_error()),
}
}
-
- fn remove_account(&self, public_key: PublicKey) -> Result<(), SafeError> {
- self.connection()
- .execute(
- "DELETE FROM accounts WHERE pubkey = ?1",
- [public_key.to_hex()],
- )
- .map(|_| ())
- .map_err(|_| storage_error())
- }
}
impl AppStateRepository for Database {
@@ -94,7 +140,7 @@ impl AppStateRepository for Database {
let value = self
.connection()
.query_row(
- "SELECT selected_pubkey FROM app_state WHERE singleton = 1",
+ "SELECT selected_public_key FROM runtime_state WHERE singleton = 1",
[],
|row| row.get::<_, Option<String>>(0),
)
@@ -105,18 +151,30 @@ impl AppStateRepository for Database {
}
fn save_selected_account(&self, public_key: Option<PublicKey>) -> Result<(), SafeError> {
- if let Some(public_key) = public_key
- && self.find_account(public_key)?.is_none()
- {
- return Err(account_not_found());
+ let mut connection = self.connection();
+ let transaction = connection.transaction().map_err(|_| storage_error())?;
+ if let Some(public_key) = public_key {
+ let exists = transaction
+ .query_row(
+ "SELECT EXISTS(SELECT 1 FROM account_identities WHERE public_key = ?1)",
+ [public_key.to_hex()],
+ |row| row.get::<_, bool>(0),
+ )
+ .map_err(|_| storage_error())?;
+ if !exists {
+ return Err(account_not_found());
+ }
}
- self.connection()
+ let rows = transaction
.execute(
- "UPDATE app_state SET selected_pubkey = ?1 WHERE singleton = 1",
+ "UPDATE runtime_state SET selected_public_key = ?1 WHERE singleton = 1",
[public_key.map(PublicKey::to_hex)],
)
- .map(|_| ())
- .map_err(|_| storage_error())
+ .map_err(|_| storage_error())?;
+ if rows != 1 {
+ return Err(corrupt_storage_error());
+ }
+ transaction.commit().map_err(|_| storage_error())
}
}
diff --git a/crates/studio_storage/src/db.rs b/crates/studio_storage/src/db.rs
@@ -9,7 +9,7 @@ use radroots_studio_domain::{AccountIdentity, PublicKey, SafeError, SafeErrorCod
use refinery::embed_migrations;
use rusqlite::{Connection, OpenFlags};
-pub const CURRENT_SCHEMA_VERSION: u32 = 7;
+pub const CURRENT_SCHEMA_VERSION: u32 = 8;
mod migrations {
use super::embed_migrations;
@@ -381,7 +381,7 @@ mod tests {
}
let database = Database::open(&path).expect("migrated database");
- assert_eq!(database.schema_version().expect("version"), 7);
+ assert_eq!(database.schema_version().expect("version"), 8);
assert_eq!(database.list_accounts().expect("accounts").len(), 1);
assert_eq!(
database.load_selected_account().expect("selection"),
diff --git a/crates/studio_storage/src/profiles.rs b/crates/studio_storage/src/profiles.rs
@@ -12,7 +12,7 @@ impl ProfileRepository for Database {
self.connection()
.query_row(
"SELECT event_id, event_created_at, name, display_name, nip05, about, picture, \
- refreshed_at, refresh_status FROM profile_cache WHERE subject_pubkey = ?1",
+ refreshed_at, refresh_status FROM profile_cache_v6 WHERE subject_public_key = ?1",
[public_key.to_hex()],
|row| decode_profile(row, public_key),
)
@@ -25,17 +25,17 @@ impl ProfileRepository for Database {
let metadata = candidate.metadata();
self.connection()
.execute(
- "INSERT INTO profile_cache (subject_pubkey, event_id, event_created_at, name, \
+ "INSERT INTO profile_cache_v6 (subject_public_key, event_id, event_created_at, name, \
display_name, nip05, about, picture, refreshed_at, refresh_status) \
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10) \
- ON CONFLICT(subject_pubkey) DO UPDATE SET \
+ ON CONFLICT(subject_public_key) DO UPDATE SET \
event_id = excluded.event_id, event_created_at = excluded.event_created_at, \
name = excluded.name, display_name = excluded.display_name, nip05 = excluded.nip05, \
about = excluded.about, picture = excluded.picture, \
refreshed_at = excluded.refreshed_at, refresh_status = excluded.refresh_status \
- WHERE excluded.event_created_at > profile_cache.event_created_at \
- OR (excluded.event_created_at = profile_cache.event_created_at \
- AND excluded.event_id < profile_cache.event_id)",
+ WHERE excluded.event_created_at > profile_cache_v6.event_created_at \
+ OR (excluded.event_created_at = profile_cache_v6.event_created_at \
+ AND excluded.event_id < profile_cache_v6.event_id)",
params![
candidate.author().to_hex(),
candidate.event_id().to_hex(),
@@ -61,8 +61,8 @@ impl ProfileRepository for Database {
) -> Result<(), SafeError> {
self.connection()
.execute(
- "UPDATE profile_cache SET refreshed_at = ?2, refresh_status = ?3 \
- WHERE subject_pubkey = ?1",
+ "UPDATE profile_cache_v6 SET refreshed_at = ?2, refresh_status = ?3 \
+ WHERE subject_public_key = ?1",
params![
public_key.to_hex(),
refreshed_at.as_seconds(),
@@ -76,7 +76,7 @@ impl ProfileRepository for Database {
fn remove_profile(&self, public_key: PublicKey) -> Result<(), SafeError> {
self.connection()
.execute(
- "DELETE FROM profile_cache WHERE subject_pubkey = ?1",
+ "DELETE FROM profile_cache_v6 WHERE subject_public_key = ?1",
[public_key.to_hex()],
)
.map(|_| ())
