1 files changed, 150 insertions, 0 deletions

diff --git a/internal/storage/storage.go b/internal/storage/storage.go
new file mode 100644
index 0000000..64fc4c6
--- /dev/null
+++ b/internal/storage/storage.go
@@ -0,0 +1,150 @@
+package storage
+
+import (
+        "context"
+        "database/sql"
+        "fmt"
+
+        _ "modernc.org/sqlite" // Pure Go SQLite driver
+)
+
+// Storage provides event persistence using SQLite.
+// Consumers should define their own interface based on their needs.
+type Storage struct {
+        db *sql.DB
+}
+
+// New creates a new Storage instance and initializes the database schema.
+// The dbPath should be a file path or ":memory:" for in-memory database.
+func New(dbPath string) (*Storage, error) {
+        db, err := sql.Open("sqlite", dbPath)
+        if err != nil {
+                return nil, fmt.Errorf("failed to open database: %w", err)
+        }
+
+        // Configure SQLite for optimal performance
+        pragmas := []string{
+                "PRAGMA journal_mode=WAL",              // Write-Ahead Logging for concurrency
+                "PRAGMA synchronous=NORMAL",            // Balance safety and performance
+                "PRAGMA cache_size=10000",              // 10000 pages (~40MB cache)
+                "PRAGMA temp_store=MEMORY",             // Temp tables in memory
+                "PRAGMA mmap_size=268435456",           // 256MB memory-mapped I/O
+                "PRAGMA page_size=4096",                // Standard page size
+                "PRAGMA foreign_keys=ON",               // Enforce foreign key constraints
+                "PRAGMA busy_timeout=5000",             // Wait up to 5s for locks
+                "PRAGMA auto_vacuum=INCREMENTAL",       // Reclaim space incrementally
+        }
+
+        for _, pragma := range pragmas {
+                if _, err := db.Exec(pragma); err != nil {
+                        db.Close()
+                        return nil, fmt.Errorf("failed to set pragma %q: %w", pragma, err)
+                }
+        }
+
+        s := &Storage{db: db}
+
+        // Initialize schema
+        if err := s.initSchema(context.Background()); err != nil {
+                db.Close()
+                return nil, fmt.Errorf("failed to initialize schema: %w", err)
+        }
+
+        return s, nil
+}
+
+// Close closes the database connection.
+func (s *Storage) Close() error {
+        return s.db.Close()
+}
+
+// initSchema creates all necessary tables and indexes.
+func (s *Storage) initSchema(ctx context.Context) error {
+        schema := `
+        -- Main events table
+        CREATE TABLE IF NOT EXISTS events (
+                -- Primary event data
+                id TEXT PRIMARY KEY,
+                event_data BLOB NOT NULL,           -- Protobuf binary
+                canonical_json BLOB NOT NULL,       -- zstd compressed canonical JSON
+
+                -- Denormalized fields for efficient querying
+                pubkey TEXT NOT NULL,
+                kind INTEGER NOT NULL,
+                created_at INTEGER NOT NULL,        -- Unix timestamp
+                content TEXT,                       -- For full-text search (optional)
+                tags TEXT,                          -- JSON text for tag queries (use json_* functions)
+                sig TEXT NOT NULL,
+
+                -- Metadata
+                deleted INTEGER DEFAULT 0,          -- STRICT mode: use INTEGER for boolean
+                received_at INTEGER DEFAULT (unixepoch())
+        ) STRICT;
+
+        -- Critical indexes for Nostr query patterns
+        CREATE INDEX IF NOT EXISTS idx_pubkey_created
+                ON events(pubkey, created_at DESC)
+                WHERE deleted = 0;
+
+        CREATE INDEX IF NOT EXISTS idx_kind_created
+                ON events(kind, created_at DESC)
+                WHERE deleted = 0;
+
+        CREATE INDEX IF NOT EXISTS idx_created
+                ON events(created_at DESC)
+                WHERE deleted = 0;
+
+        -- For tag queries (#e, #p, etc)
+        CREATE INDEX IF NOT EXISTS idx_tags
+                ON events(tags)
+                WHERE deleted = 0;
+
+        -- Deletion events (NIP-09)
+        CREATE TABLE IF NOT EXISTS deletions (
+                event_id TEXT PRIMARY KEY,          -- ID of deletion event
+                deleted_event_id TEXT NOT NULL,     -- ID of event being deleted
+                pubkey TEXT NOT NULL,               -- Who requested deletion
+                created_at INTEGER NOT NULL,
+                FOREIGN KEY (deleted_event_id) REFERENCES events(id)
+        ) STRICT;
+
+        CREATE INDEX IF NOT EXISTS idx_deleted_event
+                ON deletions(deleted_event_id);
+
+        -- Replaceable events tracking (NIP-16, NIP-33)
+        CREATE TABLE IF NOT EXISTS replaceable_events (
+                kind INTEGER NOT NULL,
+                pubkey TEXT NOT NULL,
+                d_tag TEXT NOT NULL DEFAULT '',    -- For parameterized replaceable events (empty string for non-parameterized)
+                current_event_id TEXT NOT NULL,
+                created_at INTEGER NOT NULL,
+                PRIMARY KEY (kind, pubkey, d_tag),
+                FOREIGN KEY (current_event_id) REFERENCES events(id)
+        ) STRICT;
+
+        -- Auth challenges (NIP-42)
+        CREATE TABLE IF NOT EXISTS auth_challenges (
+                challenge TEXT PRIMARY KEY,
+                created_at INTEGER NOT NULL,
+                expires_at INTEGER NOT NULL,
+                used INTEGER DEFAULT 0              -- STRICT mode: use INTEGER for boolean
+        ) STRICT;
+
+        -- Rate limiting
+        CREATE TABLE IF NOT EXISTS rate_limits (
+                pubkey TEXT PRIMARY KEY,
+                event_count INTEGER DEFAULT 0,
+                window_start INTEGER NOT NULL,
+                last_reset INTEGER DEFAULT (unixepoch())
+        ) STRICT;
+        `
+
+        _, err := s.db.ExecContext(ctx, schema)
+        return err
+}
+
+// DB returns the underlying *sql.DB for advanced usage.
+// This allows consumers to execute custom queries if needed.
+func (s *Storage) DB() *sql.DB {
+        return s.db
+}