commit 3e202261bd7ac8177ace82089b5d961dad7fd9d0
parent 4d37ba6ec129e04a69cfc8f612d84c61911817fe
Author: triesap <tyson@radroots.org>
Date: Mon, 15 Jun 2026 14:03:08 -0700
storage: allow sqlite store reopen
- make event-store and outbox schema setup idempotent for existing SQLite files
- add file reopen regression coverage for both stores
- validation: cargo test -p radroots_event_store; cargo test -p radroots_outbox
Diffstat:
7 files changed, 53 insertions(+), 23 deletions(-)
diff --git a/Cargo.lock b/Cargo.lock
@@ -3988,6 +3988,7 @@ dependencies = [
"serde",
"serde_json",
"sqlx",
+ "tempfile",
"thiserror 1.0.69",
"tokio",
]
@@ -4197,6 +4198,7 @@ dependencies = [
"serde_json",
"sha2",
"sqlx",
+ "tempfile",
"thiserror 1.0.69",
"tokio",
]
diff --git a/crates/event_store/Cargo.toml b/crates/event_store/Cargo.toml
@@ -32,4 +32,5 @@ sqlx = { workspace = true, optional = true, features = ["derive"] }
thiserror = { workspace = true }
[dev-dependencies]
+tempfile = { workspace = true }
tokio = { workspace = true, features = ["macros", "rt"] }
diff --git a/crates/event_store/migrations/0001_event_store.up.sql b/crates/event_store/migrations/0001_event_store.up.sql
@@ -1,4 +1,4 @@
-CREATE TABLE nostr_event (
+CREATE TABLE IF NOT EXISTS nostr_event (
seq INTEGER PRIMARY KEY AUTOINCREMENT,
event_id TEXT NOT NULL UNIQUE,
pubkey TEXT NOT NULL,
@@ -17,13 +17,13 @@ CREATE TABLE nostr_event (
updated_at_ms INTEGER NOT NULL
);
-CREATE INDEX nostr_event_kind_created_idx ON nostr_event(kind, created_at, event_id);
-CREATE INDEX nostr_event_contract_idx ON nostr_event(contract_id, seq);
-CREATE INDEX nostr_event_projection_idx ON nostr_event(projection_eligible, seq);
-CREATE INDEX nostr_event_verification_contract_idx
+CREATE INDEX IF NOT EXISTS nostr_event_kind_created_idx ON nostr_event(kind, created_at, event_id);
+CREATE INDEX IF NOT EXISTS nostr_event_contract_idx ON nostr_event(contract_id, seq);
+CREATE INDEX IF NOT EXISTS nostr_event_projection_idx ON nostr_event(projection_eligible, seq);
+CREATE INDEX IF NOT EXISTS nostr_event_verification_contract_idx
ON nostr_event(verification_status, contract_status, seq);
-CREATE TABLE nostr_event_tag (
+CREATE TABLE IF NOT EXISTS nostr_event_tag (
event_id TEXT NOT NULL REFERENCES nostr_event(event_id) ON DELETE CASCADE,
tag_index INTEGER NOT NULL,
tag_name TEXT NOT NULL,
@@ -35,10 +35,10 @@ CREATE TABLE nostr_event_tag (
PRIMARY KEY (event_id, tag_index)
);
-CREATE INDEX nostr_event_tag_lookup_idx ON nostr_event_tag(tag_name, tag_value, event_id);
-CREATE INDEX nostr_event_tag_relay_idx ON nostr_event_tag(relay_indexed, tag_name, tag_value, event_id);
+CREATE INDEX IF NOT EXISTS nostr_event_tag_lookup_idx ON nostr_event_tag(tag_name, tag_value, event_id);
+CREATE INDEX IF NOT EXISTS nostr_event_tag_relay_idx ON nostr_event_tag(relay_indexed, tag_name, tag_value, event_id);
-CREATE TABLE relay_event_seen (
+CREATE TABLE IF NOT EXISTS relay_event_seen (
event_id TEXT NOT NULL REFERENCES nostr_event(event_id) ON DELETE CASCADE,
relay_url TEXT NOT NULL,
observation_type TEXT NOT NULL,
@@ -49,9 +49,9 @@ CREATE TABLE relay_event_seen (
PRIMARY KEY (event_id, relay_url, observation_type)
);
-CREATE INDEX relay_event_seen_relay_idx ON relay_event_seen(relay_url, last_seen_at_ms, event_id);
+CREATE INDEX IF NOT EXISTS relay_event_seen_relay_idx ON relay_event_seen(relay_url, last_seen_at_ms, event_id);
-CREATE TABLE nostr_event_head (
+CREATE TABLE IF NOT EXISTS nostr_event_head (
coordinate_type TEXT NOT NULL,
kind INTEGER NOT NULL,
pubkey TEXT NOT NULL,
@@ -65,17 +65,17 @@ CREATE TABLE nostr_event_head (
)
);
-CREATE UNIQUE INDEX nostr_event_head_replaceable_idx
+CREATE UNIQUE INDEX IF NOT EXISTS nostr_event_head_replaceable_idx
ON nostr_event_head(kind, pubkey)
WHERE coordinate_type = 'replaceable';
-CREATE UNIQUE INDEX nostr_event_head_addressable_idx
+CREATE UNIQUE INDEX IF NOT EXISTS nostr_event_head_addressable_idx
ON nostr_event_head(kind, pubkey, d_tag)
WHERE coordinate_type = 'addressable';
-CREATE INDEX nostr_event_head_event_idx ON nostr_event_head(event_id);
+CREATE INDEX IF NOT EXISTS nostr_event_head_event_idx ON nostr_event_head(event_id);
-CREATE TABLE projection_cursor (
+CREATE TABLE IF NOT EXISTS projection_cursor (
projection_id TEXT PRIMARY KEY NOT NULL,
projection_version INTEGER NOT NULL DEFAULT 1,
last_event_seq INTEGER NOT NULL DEFAULT 0,
diff --git a/crates/event_store/src/store.rs b/crates/event_store/src/store.rs
@@ -888,6 +888,19 @@ mod tests {
}
#[tokio::test]
+ async fn file_store_reopens_existing_schema() {
+ let tempdir = tempfile::tempdir().expect("tempdir");
+ let path = tempdir.path().join("event_store.sqlite");
+
+ let first = RadrootsEventStore::open_file(&path).await.expect("first");
+ assert_eq!(first.pragma_foreign_keys().await.expect("foreign_keys"), 1);
+ drop(first);
+
+ let second = RadrootsEventStore::open_file(&path).await.expect("second");
+ assert_eq!(second.pragma_foreign_keys().await.expect("foreign_keys"), 1);
+ }
+
+ #[tokio::test]
async fn migration_can_run_down() {
let store = RadrootsEventStore::open_memory().await.expect("open");
store.migrate_down().await.expect("down");
diff --git a/crates/outbox/Cargo.toml b/crates/outbox/Cargo.toml
@@ -38,4 +38,5 @@ radroots_nostr = { workspace = true, default-features = false, features = [
"std",
"events",
] }
+tempfile = { workspace = true }
tokio = { workspace = true, features = ["macros", "rt"] }
diff --git a/crates/outbox/migrations/0001_outbox.up.sql b/crates/outbox/migrations/0001_outbox.up.sql
@@ -1,4 +1,4 @@
-CREATE TABLE outbox_operation (
+CREATE TABLE IF NOT EXISTS outbox_operation (
operation_id INTEGER PRIMARY KEY AUTOINCREMENT,
operation_kind TEXT NOT NULL,
expected_pubkey TEXT NOT NULL,
@@ -9,14 +9,14 @@ CREATE TABLE outbox_operation (
updated_at_ms INTEGER NOT NULL
);
-CREATE UNIQUE INDEX outbox_operation_idempotency_idx
+CREATE UNIQUE INDEX IF NOT EXISTS outbox_operation_idempotency_idx
ON outbox_operation(operation_kind, expected_pubkey, idempotency_key)
WHERE idempotency_key IS NOT NULL;
-CREATE INDEX outbox_operation_status_idx
+CREATE INDEX IF NOT EXISTS outbox_operation_status_idx
ON outbox_operation(status, created_at_ms, operation_id);
-CREATE TABLE outbox_event (
+CREATE TABLE IF NOT EXISTS outbox_event (
outbox_event_id INTEGER PRIMARY KEY AUTOINCREMENT,
operation_id INTEGER NOT NULL REFERENCES outbox_operation(operation_id) ON DELETE CASCADE,
event_id TEXT NOT NULL,
@@ -39,13 +39,13 @@ CREATE TABLE outbox_event (
updated_at_ms INTEGER NOT NULL
);
-CREATE INDEX outbox_event_ready_idx
+CREATE INDEX IF NOT EXISTS outbox_event_ready_idx
ON outbox_event(state, next_attempt_after_ms, claim_expires_at_ms, created_at_ms, outbox_event_id);
-CREATE INDEX outbox_event_event_id_idx
+CREATE INDEX IF NOT EXISTS outbox_event_event_id_idx
ON outbox_event(event_id);
-CREATE TABLE outbox_event_relay_status (
+CREATE TABLE IF NOT EXISTS outbox_event_relay_status (
outbox_event_id INTEGER NOT NULL REFERENCES outbox_event(outbox_event_id) ON DELETE CASCADE,
relay_url TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('pending', 'accepted', 'failed_retryable', 'failed_terminal')),
@@ -56,5 +56,5 @@ CREATE TABLE outbox_event_relay_status (
PRIMARY KEY (outbox_event_id, relay_url)
);
-CREATE INDEX outbox_event_relay_status_idx
+CREATE INDEX IF NOT EXISTS outbox_event_relay_status_idx
ON outbox_event_relay_status(status, relay_url, outbox_event_id);
diff --git a/crates/outbox/src/store.rs b/crates/outbox/src/store.rs
@@ -1218,6 +1218,19 @@ mod tests {
assert!(row.is_none());
}
+ #[tokio::test]
+ async fn file_outbox_reopens_existing_schema() {
+ let tempdir = tempfile::tempdir().expect("tempdir");
+ let path = tempdir.path().join("outbox.sqlite");
+
+ let first = RadrootsOutbox::open_file(&path).await.expect("first");
+ assert_eq!(first.pragma_foreign_keys().await.expect("foreign keys"), 1);
+ drop(first);
+
+ let second = RadrootsOutbox::open_file(&path).await.expect("second");
+ assert_eq!(second.pragma_foreign_keys().await.expect("foreign keys"), 1);
+ }
+
#[test]
fn terminal_and_cancelled_event_states_round_trip() {
assert_eq!(
