0020_indexed_visibility_reads.up.sql (3675B)
1 ALTER TABLE radroots_runtime_visibility_progress 2 ADD COLUMN raw_events INTEGER NOT NULL DEFAULT 0 CHECK (raw_events >= 0); 3 ALTER TABLE radroots_runtime_visibility_progress 4 ADD COLUMN verified_events INTEGER NOT NULL DEFAULT 0 CHECK (verified_events >= 0); 5 ALTER TABLE radroots_runtime_visibility_progress 6 ADD COLUMN visible_events INTEGER NOT NULL DEFAULT 0 CHECK (visible_events >= 0); 7 8 -- A one-time forward upgrade rebuilds membership through bounded source work. 9 -- No event payload is decoded or updated by migration. 10 UPDATE radroots_runtime_visibility_progress 11 SET backfill_target = (SELECT sequence_head FROM radroots_runtime_source_generations 12 WHERE generation = source_generation), 13 backfill_sequence = 0, replay_epoch = 0, 14 replay_sequence = 9223372036854775807, ready = 0; 15 16 CREATE TABLE radroots_runtime_visibility_members ( 17 source_generation BLOB NOT NULL REFERENCES radroots_runtime_visibility_progress(source_generation), 18 source_sequence INTEGER NOT NULL CHECK (source_sequence > 0), 19 event_id BLOB NOT NULL UNIQUE REFERENCES radroots_runtime_visibility_events(event_id), 20 admission_stage TEXT NOT NULL CHECK (admission_stage IN ('raw', 'verified', 'visible')), 21 visible INTEGER NOT NULL CHECK (visible IN (0, 1)), 22 PRIMARY KEY (source_generation, source_sequence) 23 ) STRICT, WITHOUT ROWID; 24 25 CREATE INDEX radroots_runtime_visibility_members_page_idx 26 ON radroots_runtime_visibility_members(source_generation, visible, source_sequence); 27 28 CREATE UNIQUE INDEX radroots_runtime_visibility_members_event_idx 29 ON radroots_runtime_visibility_members(source_generation, event_id); 30 31 CREATE TABLE radroots_runtime_visibility_heads ( 32 source_generation BLOB NOT NULL REFERENCES radroots_runtime_visibility_progress(source_generation), 33 head_coordinate BLOB NOT NULL, 34 source_sequence INTEGER NOT NULL CHECK (source_sequence > 0), 35 event_id BLOB NOT NULL REFERENCES radroots_runtime_visibility_events(event_id), 36 PRIMARY KEY (source_generation, head_coordinate) 37 ) STRICT, WITHOUT ROWID; 38 39 CREATE TABLE radroots_runtime_visibility_pending ( 40 source_generation BLOB NOT NULL REFERENCES radroots_runtime_visibility_progress(source_generation), 41 target_type INTEGER NOT NULL CHECK (target_type IN (0, 1)), 42 target_key BLOB NOT NULL CHECK ( 43 (target_type = 0 AND length(target_key) = 32) OR 44 (target_type = 1 AND length(target_key) BETWEEN 1 AND 4096)), 45 PRIMARY KEY (source_generation, target_type, target_key) 46 ) STRICT, WITHOUT ROWID; 47 48 CREATE TRIGGER radroots_runtime_visibility_members_count_insert 49 AFTER INSERT ON radroots_runtime_visibility_members 50 BEGIN 51 UPDATE radroots_runtime_visibility_progress 52 SET raw_events = raw_events + 1, 53 verified_events = verified_events + (NEW.admission_stage != 'raw'), 54 visible_events = visible_events + NEW.visible 55 WHERE source_generation = NEW.source_generation; 56 END; 57 58 CREATE TRIGGER radroots_runtime_visibility_members_count_update 59 AFTER UPDATE OF admission_stage, visible ON radroots_runtime_visibility_members 60 BEGIN 61 UPDATE radroots_runtime_visibility_progress 62 SET verified_events = verified_events + (NEW.admission_stage != 'raw') - (OLD.admission_stage != 'raw'), 63 visible_events = visible_events + NEW.visible - OLD.visible 64 WHERE source_generation = NEW.source_generation; 65 END; 66 67 CREATE TRIGGER radroots_runtime_visibility_empty_source 68 AFTER INSERT ON radroots_runtime_source_generations 69 WHEN NEW.sequence_head = 0 AND NEW.mutation_epoch = 0 70 BEGIN 71 INSERT INTO radroots_runtime_visibility_progress 72 (source_generation, backfill_target, backfill_sequence, replay_epoch, replay_sequence, ready) 73 VALUES (NEW.generation, 0, 0, 0, 9223372036854775807, 1); 74 END;