legacy_event_store_v1.sql (10712B)
1 CREATE TABLE IF NOT EXISTS event_envelopes ( 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 event_envelope_kind_created_idx ON event_envelopes(kind, created_at, event_id); 21 CREATE INDEX IF NOT EXISTS event_envelope_contract_idx ON event_envelopes(contract_id, seq); 22 CREATE INDEX IF NOT EXISTS event_envelope_projection_idx ON event_envelopes(projection_eligible, seq); 23 CREATE INDEX IF NOT EXISTS event_envelope_verification_contract_idx 24 ON event_envelopes(verification_status, contract_status, seq); 25 26 CREATE TABLE IF NOT EXISTS event_envelope_tags ( 27 event_id TEXT NOT NULL REFERENCES event_envelopes(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 event_envelope_tag_lookup_idx ON event_envelope_tags(tag_name, tag_value, event_id); 39 CREATE INDEX IF NOT EXISTS event_envelope_tag_relay_idx ON event_envelope_tags(relay_indexed, tag_name, tag_value, event_id); 40 41 CREATE TABLE IF NOT EXISTS event_transport_observation ( 42 event_id TEXT NOT NULL REFERENCES event_envelopes(event_id) ON DELETE CASCADE, 43 transport_kind TEXT NOT NULL, 44 endpoint_uri TEXT NOT NULL, 45 endpoint_fingerprint TEXT NOT NULL, 46 observation_type TEXT NOT NULL, 47 first_observed_at_ms INTEGER NOT NULL, 48 last_observed_at_ms INTEGER NOT NULL, 49 observation_count INTEGER NOT NULL, 50 redacted_message TEXT, 51 PRIMARY KEY (event_id, transport_kind, endpoint_fingerprint, observation_type) 52 ); 53 54 CREATE INDEX IF NOT EXISTS event_transport_observation_endpoint_idx 55 ON event_transport_observation(transport_kind, endpoint_fingerprint, last_observed_at_ms, event_id); 56 57 CREATE TABLE IF NOT EXISTS event_envelope_head ( 58 coordinate_type TEXT NOT NULL, 59 kind INTEGER NOT NULL, 60 pubkey TEXT NOT NULL, 61 d_tag TEXT, 62 event_id TEXT NOT NULL REFERENCES event_envelopes(event_id) ON DELETE CASCADE, 63 created_at INTEGER NOT NULL, 64 updated_at_ms INTEGER NOT NULL, 65 CHECK ( 66 (coordinate_type = 'replaceable' AND d_tag IS NULL) 67 OR (coordinate_type = 'addressable' AND d_tag IS NOT NULL) 68 ) 69 ); 70 71 CREATE UNIQUE INDEX IF NOT EXISTS event_envelope_head_replaceable_idx 72 ON event_envelope_head(kind, pubkey) 73 WHERE coordinate_type = 'replaceable'; 74 75 CREATE UNIQUE INDEX IF NOT EXISTS event_envelope_head_addressable_idx 76 ON event_envelope_head(kind, pubkey, d_tag) 77 WHERE coordinate_type = 'addressable'; 78 79 CREATE INDEX IF NOT EXISTS event_envelope_head_event_idx ON event_envelope_head(event_id); 80 81 CREATE TABLE IF NOT EXISTS projection_cursor ( 82 projection_id TEXT PRIMARY KEY NOT NULL, 83 projection_version INTEGER NOT NULL DEFAULT 1, 84 last_event_seq INTEGER NOT NULL DEFAULT 0, 85 updated_at_ms INTEGER NOT NULL 86 ); 87 88 CREATE TABLE IF NOT EXISTS listing_projection ( 89 listing_addr TEXT PRIMARY KEY NOT NULL, 90 listing_event_id TEXT NOT NULL REFERENCES event_envelopes(event_id) ON DELETE CASCADE, 91 seller_pubkey TEXT NOT NULL, 92 farm_pubkey TEXT NOT NULL, 93 farm_d_tag TEXT NOT NULL, 94 listing_d_tag TEXT NOT NULL, 95 title TEXT NOT NULL, 96 description TEXT NOT NULL, 97 product_type TEXT NOT NULL, 98 primary_bin_id TEXT NOT NULL, 99 quantity_amount TEXT NOT NULL, 100 quantity_unit TEXT NOT NULL, 101 price_amount TEXT NOT NULL, 102 price_currency TEXT NOT NULL, 103 inventory_available TEXT NOT NULL, 104 availability_status TEXT NOT NULL, 105 delivery_method TEXT NOT NULL, 106 locality_primary TEXT NOT NULL, 107 locality_city TEXT, 108 locality_region TEXT, 109 locality_country TEXT, 110 geohash5 TEXT NOT NULL, 111 listing_json TEXT NOT NULL, 112 source_event_seq INTEGER NOT NULL, 113 created_at INTEGER NOT NULL, 114 updated_at_ms INTEGER NOT NULL 115 ); 116 117 CREATE INDEX IF NOT EXISTS listing_projection_seller_idx 118 ON listing_projection(seller_pubkey, updated_at_ms, listing_addr); 119 120 CREATE INDEX IF NOT EXISTS listing_projection_geohash_idx 121 ON listing_projection(geohash5, updated_at_ms, listing_addr); 122 123 CREATE VIRTUAL TABLE IF NOT EXISTS listing_search_fts USING fts5( 124 listing_addr UNINDEXED, 125 title, 126 description, 127 product_type, 128 locality, 129 seller_pubkey UNINDEXED, 130 tokenize = 'unicode61' 131 ); 132 133 CREATE TABLE IF NOT EXISTS trade_mutation ( 134 mutation_id TEXT PRIMARY KEY NOT NULL, 135 trade_id TEXT NOT NULL, 136 root_mutation_id TEXT, 137 contract_id TEXT NOT NULL, 138 mutation_kind TEXT NOT NULL CHECK (mutation_kind IN ('proposal', 'decision', 'revision_proposal', 'revision_decision', 'cancellation')), 139 schema_version INTEGER NOT NULL, 140 candidate_id TEXT, 141 proposal_mutation_id TEXT, 142 target_claim_mutation_id TEXT, 143 author_pubkey TEXT NOT NULL, 144 counterparty_pubkey TEXT NOT NULL, 145 buyer_pubkey TEXT NOT NULL, 146 seller_pubkey TEXT NOT NULL, 147 farm_id TEXT NOT NULL, 148 authored_at_unix_s INTEGER NOT NULL, 149 canonical_payload_bytes BLOB NOT NULL, 150 payload_sha256 TEXT NOT NULL CHECK (length(payload_sha256) = 64), 151 first_event_seq INTEGER NOT NULL REFERENCES event_envelopes(seq) ON DELETE RESTRICT, 152 first_transport_event_id TEXT NOT NULL REFERENCES event_envelopes(event_id) ON DELETE RESTRICT, 153 inserted_at_ms INTEGER NOT NULL 154 ) STRICT; 155 156 CREATE INDEX IF NOT EXISTS trade_mutation_trade_idx 157 ON trade_mutation(trade_id, authored_at_unix_s, mutation_id); 158 159 CREATE INDEX IF NOT EXISTS trade_mutation_candidate_idx 160 ON trade_mutation(trade_id, candidate_id, mutation_id) 161 WHERE candidate_id IS NOT NULL; 162 163 CREATE INDEX IF NOT EXISTS trade_mutation_actor_idx 164 ON trade_mutation(buyer_pubkey, seller_pubkey, authored_at_unix_s, mutation_id); 165 166 CREATE TABLE IF NOT EXISTS trade_mutation_parent ( 167 mutation_id TEXT NOT NULL REFERENCES trade_mutation(mutation_id) ON DELETE CASCADE, 168 parent_mutation_id TEXT NOT NULL, 169 parent_index INTEGER NOT NULL, 170 PRIMARY KEY(mutation_id, parent_mutation_id) 171 ) STRICT; 172 173 CREATE INDEX IF NOT EXISTS trade_mutation_parent_lookup_idx 174 ON trade_mutation_parent(parent_mutation_id, mutation_id); 175 176 CREATE TABLE IF NOT EXISTS trade_missing_parent ( 177 trade_id TEXT NOT NULL, 178 mutation_id TEXT NOT NULL REFERENCES trade_mutation(mutation_id) ON DELETE CASCADE, 179 missing_parent_mutation_id TEXT NOT NULL, 180 first_transport_event_id TEXT NOT NULL REFERENCES event_envelopes(event_id) ON DELETE CASCADE, 181 first_seen_at_ms INTEGER NOT NULL, 182 PRIMARY KEY(trade_id, mutation_id, missing_parent_mutation_id) 183 ) STRICT; 184 185 CREATE INDEX IF NOT EXISTS trade_missing_parent_lookup_idx 186 ON trade_missing_parent(missing_parent_mutation_id, trade_id, mutation_id); 187 188 CREATE TABLE IF NOT EXISTS trade_transport_envelope ( 189 transport_event_id TEXT PRIMARY KEY NOT NULL REFERENCES event_envelopes(event_id) ON DELETE CASCADE, 190 mutation_id TEXT NOT NULL REFERENCES trade_mutation(mutation_id) ON DELETE CASCADE, 191 trade_id TEXT NOT NULL, 192 transport_kind TEXT NOT NULL, 193 pubkey TEXT NOT NULL, 194 created_at INTEGER NOT NULL, 195 event_seq INTEGER NOT NULL REFERENCES event_envelopes(seq) ON DELETE CASCADE, 196 payload_sha256 TEXT NOT NULL CHECK (length(payload_sha256) = 64), 197 observed_at_ms INTEGER NOT NULL 198 ) STRICT; 199 200 CREATE INDEX IF NOT EXISTS trade_transport_envelope_mutation_idx 201 ON trade_transport_envelope(mutation_id, observed_at_ms, transport_event_id); 202 203 CREATE INDEX IF NOT EXISTS trade_transport_envelope_trade_idx 204 ON trade_transport_envelope(trade_id, event_seq, transport_event_id); 205 206 CREATE TABLE IF NOT EXISTS seller_inventory_reservation ( 207 reservation_id TEXT PRIMARY KEY NOT NULL, 208 trade_id TEXT NOT NULL, 209 candidate_id TEXT NOT NULL, 210 claim_mutation_id TEXT NOT NULL REFERENCES trade_mutation(mutation_id) ON DELETE CASCADE, 211 inventory_authority_pubkey TEXT NOT NULL, 212 inventory_epoch INTEGER NOT NULL, 213 assertion_commitment TEXT NOT NULL CHECK (length(assertion_commitment) = 64), 214 reservation_expires_at_unix_s INTEGER NOT NULL, 215 reservation_json TEXT NOT NULL, 216 inserted_at_ms INTEGER NOT NULL, 217 UNIQUE(candidate_id, assertion_commitment) 218 ) STRICT; 219 220 CREATE INDEX IF NOT EXISTS seller_inventory_reservation_trade_idx 221 ON seller_inventory_reservation(trade_id, candidate_id, reservation_expires_at_unix_s); 222 223 CREATE INDEX IF NOT EXISTS seller_inventory_reservation_authority_idx 224 ON seller_inventory_reservation(inventory_authority_pubkey, inventory_epoch, reservation_id); 225 226 CREATE TABLE IF NOT EXISTS seller_inventory_reservation_line ( 227 reservation_id TEXT NOT NULL REFERENCES seller_inventory_reservation(reservation_id) ON DELETE CASCADE, 228 line_id TEXT NOT NULL, 229 bin_id TEXT NOT NULL, 230 quantity_mantissa TEXT NOT NULL, 231 quantity_scale INTEGER NOT NULL, 232 unit_code TEXT NOT NULL, 233 line_index INTEGER NOT NULL, 234 PRIMARY KEY(reservation_id, line_id) 235 ) STRICT; 236 237 CREATE INDEX IF NOT EXISTS seller_inventory_reservation_line_bin_idx 238 ON seller_inventory_reservation_line(bin_id, reservation_id, line_id); 239 240 CREATE TABLE IF NOT EXISTS trade_projection_checkpoint ( 241 trade_id TEXT PRIMARY KEY NOT NULL, 242 reducer_contract_id TEXT NOT NULL, 243 reducer_version INTEGER NOT NULL, 244 projection_digest TEXT NOT NULL CHECK (length(projection_digest) = 64), 245 root_mutation_id TEXT, 246 negotiation_state TEXT NOT NULL, 247 agreement_state TEXT NOT NULL, 248 evidence_state TEXT NOT NULL, 249 conflict_state TEXT NOT NULL, 250 private_terms_state TEXT NOT NULL, 251 attestation_state TEXT NOT NULL, 252 fulfillment_state TEXT NOT NULL, 253 payment_state TEXT NOT NULL, 254 projection_json TEXT NOT NULL, 255 last_mutation_id TEXT, 256 last_transport_event_seq INTEGER, 257 updated_at_ms INTEGER NOT NULL 258 ) STRICT; 259 260 CREATE INDEX IF NOT EXISTS trade_projection_checkpoint_agreement_idx 261 ON trade_projection_checkpoint(agreement_state, updated_at_ms, trade_id); 262 263 CREATE INDEX IF NOT EXISTS trade_projection_checkpoint_actor_idx 264 ON trade_projection_checkpoint(root_mutation_id, updated_at_ms, trade_id); 265 266 CREATE TABLE IF NOT EXISTS trade_projection_quarantine ( 267 quarantine_id INTEGER PRIMARY KEY AUTOINCREMENT, 268 trade_id TEXT, 269 mutation_id TEXT, 270 transport_event_id TEXT REFERENCES event_envelopes(event_id) ON DELETE CASCADE, 271 reason TEXT NOT NULL, 272 observed_at_ms INTEGER NOT NULL 273 ) STRICT; 274 275 CREATE INDEX IF NOT EXISTS trade_projection_quarantine_trade_idx 276 ON trade_projection_quarantine(trade_id, observed_at_ms, quarantine_id); 277 278 CREATE INDEX IF NOT EXISTS trade_projection_quarantine_mutation_idx 279 ON trade_projection_quarantine(mutation_id, observed_at_ms, quarantine_id);