From a6502c0888613bd0377a25e43de8ae306c4de4d7 Mon Sep 17 00:00:00 2001 From: bndw Date: Fri, 13 Feb 2026 17:41:13 -0800 Subject: feat: add SQLite storage layer with binary-first event persistence Storage implementation: - Concrete type with constructor (consumer-side interfaces) - Event storage: protobuf + zstd-compressed canonical JSON - Schema: events, deletions, replaceable_events, auth_challenges, rate_limits - WAL mode, STRICT typing, optimized indexes - Methods: StoreEvent, GetEvent, GetEventWithCanonical, DeleteEvent Dependencies: - modernc.org/sqlite v1.45.0 (pure Go SQLite driver) - github.com/klauspost/compress v1.18.4 (zstd compression) 366 lines, 10 tests passing --- internal/storage/storage.go | 150 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 150 insertions(+) create mode 100644 internal/storage/storage.go (limited to 'internal/storage/storage.go') 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 +} -- cgit v1.2.3