1 files changed, 68 insertions, 0 deletions

diff --git a/src/db.rs b/src/db.rs
index bb1bf31..777d19f 100644
--- a/src/db.rs
+++ b/src/db.rs
@@ -84,6 +84,24 @@ impl MirrorDb {
             CREATE INDEX IF NOT EXISTS idx_repos_pubkey ON repos(pubkey);
             CREATE INDEX IF NOT EXISTS idx_server_syncs_repo ON server_syncs(repo_id);
             CREATE INDEX IF NOT EXISTS idx_seen_events_id ON seen_events(event_id);
+
+            CREATE TABLE IF NOT EXISTS nip46_sessions (
+                npub TEXT PRIMARY KEY,
+                client_secret TEXT NOT NULL,
+                signer_pubkey TEXT,
+                connected INTEGER NOT NULL DEFAULT 0
+            );
+
+            CREATE TABLE IF NOT EXISTS signing_queue (
+                id INTEGER PRIMARY KEY AUTOINCREMENT,
+                npub TEXT NOT NULL,
+                repo_identifier TEXT NOT NULL,
+                state_event_json TEXT NOT NULL,
+                status TEXT NOT NULL DEFAULT 'pending',
+                created_at INTEGER NOT NULL,
+                signed_at INTEGER,
+                error TEXT
+            );
             "#,
         )
         .execute(&self.pool)
@@ -258,6 +276,56 @@ impl MirrorDb {
         .await?;
         Ok(records)
     }
+
+    pub async fn get_nip46_session(&self, npub: &str) -> Result<Option<Nip46SessionRecord>> {
+        let result = sqlx::query_as::<_, Nip46SessionRecord>(
+            "SELECT * FROM nip46_sessions WHERE npub = ?",
+        )
+        .bind(npub)
+        .fetch_optional(&self.pool)
+        .await?;
+        Ok(result)
+    }
+
+    pub async fn upsert_nip46_session(
+        &self,
+        npub: &str,
+        client_secret: &str,
+        signer_pubkey: Option<&str>,
+        connected: bool,
+    ) -> Result<()> {
+        sqlx::query(
+            r#"INSERT INTO nip46_sessions (npub, client_secret, signer_pubkey, connected)
+               VALUES (?, ?, ?, ?)
+               ON CONFLICT(npub) DO UPDATE SET client_secret = ?, signer_pubkey = ?, connected = ?"#,
+        )
+        .bind(npub)
+        .bind(client_secret)
+        .bind(signer_pubkey)
+        .bind(connected as i32)
+        .bind(client_secret)
+        .bind(signer_pubkey)
+        .bind(connected as i32)
+        .execute(&self.pool)
+        .await?;
+        Ok(())
+    }
+
+    pub async fn get_all_nip46_sessions(&self) -> Result<Vec<Nip46SessionRecord>> {
+        let records =
+            sqlx::query_as::<_, Nip46SessionRecord>("SELECT * FROM nip46_sessions")
+                .fetch_all(&self.pool)
+                .await?;
+        Ok(records)
+    }
+}
+
+#[derive(Debug, sqlx::FromRow)]
+pub struct Nip46SessionRecord {
+    pub npub: String,
+    pub client_secret: String,
+    pub signer_pubkey: Option<String>,
+    pub connected: bool,
 }
 
 fn chrono_now_secs() -> i64 {