0005_projection_metadata.up.sql (5294B)
1 DROP TABLE radroots_runtime_event_index_checkpoints; 2 DROP TABLE radroots_runtime_event_index_shards; 3 DROP TABLE radroots_runtime_event_index_manifests; 4 DROP INDEX radroots_runtime_projection_rebuilds_stage_idx; 5 DROP TABLE radroots_runtime_projection_rebuilds; 6 DROP TABLE radroots_runtime_projection_invalidations; 7 DROP TABLE radroots_runtime_projection_checkpoints; 8 9 CREATE TABLE radroots_runtime_projection_checkpoints ( 10 projection_id TEXT PRIMARY KEY NOT NULL CHECK (length(projection_id) BETWEEN 1 AND 128), 11 projection_generation BLOB NOT NULL CHECK (length(projection_generation) = 32), 12 health TEXT NOT NULL CHECK (health IN ('ready', 'invalidated', 'rebuilding', 'failed')), 13 source_generation BLOB, 14 source_sequence INTEGER, 15 projected_rows INTEGER, 16 checkpoint_updated_at_unix_ms INTEGER, 17 active_rebuild BLOB CHECK (active_rebuild IS NULL OR length(active_rebuild) = 16), 18 CHECK ( 19 (projected_rows IS NULL AND checkpoint_updated_at_unix_ms IS NULL 20 AND source_generation IS NULL AND source_sequence IS NULL) 21 OR (projected_rows >= 0 AND checkpoint_updated_at_unix_ms > 0 22 AND ((source_generation IS NULL AND source_sequence IS NULL) 23 OR (length(source_generation) = 32 AND source_sequence > 0))) 24 ), 25 CHECK ((health = 'rebuilding') = (active_rebuild IS NOT NULL)) 26 ) STRICT, WITHOUT ROWID; 27 28 CREATE TABLE radroots_runtime_projection_invalidations ( 29 projection_id TEXT NOT NULL CHECK (length(projection_id) BETWEEN 1 AND 128), 30 invalid_generation BLOB NOT NULL CHECK (length(invalid_generation) = 32), 31 replacement_generation BLOB NOT NULL CHECK (length(replacement_generation) = 32), 32 reason TEXT NOT NULL CHECK (reason IN ('source_generation_changed', 'projection_generation_changed', 'event_index_manifest_changed', 'integrity_failure', 'operator_requested')), 33 invalidated_at_unix_ms INTEGER NOT NULL CHECK (invalidated_at_unix_ms > 0), 34 PRIMARY KEY (projection_id, invalid_generation), 35 CHECK (invalid_generation <> replacement_generation) 36 ) STRICT, WITHOUT ROWID; 37 38 CREATE TABLE radroots_runtime_projection_rebuilds ( 39 ticket_id BLOB PRIMARY KEY NOT NULL CHECK (length(ticket_id) = 16), 40 projection_id TEXT NOT NULL, 41 invalid_generation BLOB NOT NULL, 42 replacement_generation BLOB NOT NULL CHECK (length(replacement_generation) = 32), 43 revision INTEGER NOT NULL CHECK (revision > 0), 44 stage TEXT NOT NULL CHECK (stage IN ('requested', 'running', 'completed', 'failed')), 45 checkpoint_source_generation BLOB, 46 checkpoint_source_sequence INTEGER, 47 checkpoint_projected_rows INTEGER, 48 checkpoint_updated_at_unix_ms INTEGER, 49 requested_at_unix_ms INTEGER NOT NULL CHECK (requested_at_unix_ms > 0), 50 updated_at_unix_ms INTEGER NOT NULL CHECK (updated_at_unix_ms >= requested_at_unix_ms), 51 FOREIGN KEY (projection_id, invalid_generation) 52 REFERENCES radroots_runtime_projection_invalidations(projection_id, invalid_generation), 53 CHECK ( 54 (checkpoint_projected_rows IS NULL AND checkpoint_updated_at_unix_ms IS NULL 55 AND checkpoint_source_generation IS NULL AND checkpoint_source_sequence IS NULL) 56 OR (checkpoint_projected_rows >= 0 AND checkpoint_updated_at_unix_ms > 0 57 AND ((checkpoint_source_generation IS NULL AND checkpoint_source_sequence IS NULL) 58 OR (length(checkpoint_source_generation) = 32 AND checkpoint_source_sequence > 0))) 59 ) 60 ) STRICT, WITHOUT ROWID; 61 62 CREATE INDEX radroots_runtime_projection_rebuilds_stage_idx 63 ON radroots_runtime_projection_rebuilds(stage, updated_at_unix_ms, ticket_id); 64 65 CREATE TABLE radroots_runtime_event_index_manifests ( 66 projection_generation BLOB PRIMARY KEY NOT NULL CHECK (length(projection_generation) = 32), 67 total_events INTEGER NOT NULL CHECK (total_events > 0), 68 target_shard_size INTEGER NOT NULL CHECK (target_shard_size > 0), 69 first_published_at_unix_s INTEGER NOT NULL CHECK (first_published_at_unix_s > 0), 70 last_published_at_unix_s INTEGER NOT NULL CHECK (last_published_at_unix_s >= first_published_at_unix_s) 71 ) STRICT, WITHOUT ROWID; 72 73 CREATE TABLE radroots_runtime_event_index_shards ( 74 projection_generation BLOB NOT NULL 75 REFERENCES radroots_runtime_event_index_manifests(projection_generation) ON DELETE CASCADE, 76 shard_id TEXT NOT NULL CHECK (length(shard_id) BETWEEN 1 AND 128), 77 ordinal INTEGER NOT NULL CHECK (ordinal >= 0), 78 artifact_path TEXT NOT NULL CHECK (length(artifact_path) BETWEEN 1 AND 512), 79 event_count INTEGER NOT NULL CHECK (event_count > 0), 80 first_event_id BLOB NOT NULL CHECK (length(first_event_id) = 32), 81 last_event_id BLOB NOT NULL CHECK (length(last_event_id) = 32), 82 first_published_at_unix_s INTEGER NOT NULL CHECK (first_published_at_unix_s > 0), 83 last_published_at_unix_s INTEGER NOT NULL CHECK (last_published_at_unix_s >= first_published_at_unix_s), 84 artifact_digest BLOB NOT NULL CHECK (length(artifact_digest) = 32), 85 PRIMARY KEY (projection_generation, shard_id), 86 UNIQUE (projection_generation, ordinal), 87 UNIQUE (projection_generation, artifact_path) 88 ) STRICT, WITHOUT ROWID; 89 90 CREATE TABLE radroots_runtime_event_index_checkpoints ( 91 projection_generation BLOB PRIMARY KEY NOT NULL CHECK (length(projection_generation) = 32), 92 generated_at_unix_ms INTEGER NOT NULL CHECK (generated_at_unix_ms > 0), 93 checkpoint BLOB NOT NULL CHECK (length(checkpoint) > 0) 94 ) STRICT, WITHOUT ROWID;