0001_event_store.up.sql (3002B)
1 CREATE TABLE IF NOT EXISTS nostr_event ( 2 seq INTEGER PRIMARY KEY AUTOINCREMENT, 3 event_id TEXT NOT NULL UNIQUE, 4 pubkey TEXT NOT NULL, 5 created_at INTEGER NOT NULL, 6 kind INTEGER NOT NULL, 7 tags_json TEXT NOT NULL, 8 content TEXT NOT NULL, 9 sig TEXT NOT NULL, 10 raw_json TEXT NOT NULL, 11 verification_status TEXT NOT NULL, 12 contract_status TEXT NOT NULL, 13 contract_id TEXT, 14 event_class TEXT, 15 projection_eligible INTEGER NOT NULL, 16 inserted_at_ms INTEGER NOT NULL, 17 updated_at_ms INTEGER NOT NULL 18 ); 19 20 CREATE INDEX IF NOT EXISTS nostr_event_kind_created_idx ON nostr_event(kind, created_at, event_id); 21 CREATE INDEX IF NOT EXISTS nostr_event_contract_idx ON nostr_event(contract_id, seq); 22 CREATE INDEX IF NOT EXISTS nostr_event_projection_idx ON nostr_event(projection_eligible, seq); 23 CREATE INDEX IF NOT EXISTS nostr_event_verification_contract_idx 24 ON nostr_event(verification_status, contract_status, seq); 25 26 CREATE TABLE IF NOT EXISTS nostr_event_tag ( 27 event_id TEXT NOT NULL REFERENCES nostr_event(event_id) ON DELETE CASCADE, 28 tag_index INTEGER NOT NULL, 29 tag_name TEXT NOT NULL, 30 tag_value TEXT, 31 tag_json TEXT NOT NULL, 32 contract_semantic TEXT, 33 contract_value_type TEXT, 34 relay_indexed INTEGER NOT NULL, 35 PRIMARY KEY (event_id, tag_index) 36 ); 37 38 CREATE INDEX IF NOT EXISTS nostr_event_tag_lookup_idx ON nostr_event_tag(tag_name, tag_value, event_id); 39 CREATE INDEX IF NOT EXISTS nostr_event_tag_relay_idx ON nostr_event_tag(relay_indexed, tag_name, tag_value, event_id); 40 41 CREATE TABLE IF NOT EXISTS relay_event_seen ( 42 event_id TEXT NOT NULL REFERENCES nostr_event(event_id) ON DELETE CASCADE, 43 relay_url TEXT NOT NULL, 44 observation_type TEXT NOT NULL, 45 first_seen_at_ms INTEGER NOT NULL, 46 last_seen_at_ms INTEGER NOT NULL, 47 observation_count INTEGER NOT NULL, 48 last_message TEXT, 49 PRIMARY KEY (event_id, relay_url, observation_type) 50 ); 51 52 CREATE INDEX IF NOT EXISTS relay_event_seen_relay_idx ON relay_event_seen(relay_url, last_seen_at_ms, event_id); 53 54 CREATE TABLE IF NOT EXISTS nostr_event_head ( 55 coordinate_type TEXT NOT NULL, 56 kind INTEGER NOT NULL, 57 pubkey TEXT NOT NULL, 58 d_tag TEXT, 59 event_id TEXT NOT NULL REFERENCES nostr_event(event_id) ON DELETE CASCADE, 60 created_at INTEGER NOT NULL, 61 updated_at_ms INTEGER NOT NULL, 62 CHECK ( 63 (coordinate_type = 'replaceable' AND d_tag IS NULL) 64 OR (coordinate_type = 'addressable' AND d_tag IS NOT NULL) 65 ) 66 ); 67 68 CREATE UNIQUE INDEX IF NOT EXISTS nostr_event_head_replaceable_idx 69 ON nostr_event_head(kind, pubkey) 70 WHERE coordinate_type = 'replaceable'; 71 72 CREATE UNIQUE INDEX IF NOT EXISTS nostr_event_head_addressable_idx 73 ON nostr_event_head(kind, pubkey, d_tag) 74 WHERE coordinate_type = 'addressable'; 75 76 CREATE INDEX IF NOT EXISTS nostr_event_head_event_idx ON nostr_event_head(event_id); 77 78 CREATE TABLE IF NOT EXISTS projection_cursor ( 79 projection_id TEXT PRIMARY KEY NOT NULL, 80 projection_version INTEGER NOT NULL DEFAULT 1, 81 last_event_seq INTEGER NOT NULL DEFAULT 0, 82 updated_at_ms INTEGER NOT NULL 83 );