1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
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 connection pool for SQLite
// SQLite works best with a single connection due to single-writer lock
db.SetMaxOpenConns(1) // Single connection (SQLite is single-writer)
db.SetMaxIdleConns(1) // Keep connection alive
db.SetConnMaxLifetime(0) // Never close the connection
// 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;
-- 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
}
|
