0001_runtime.up.sql (12150B)
1 CREATE TABLE radroots_runtime_source_generations ( 2 generation BLOB PRIMARY KEY NOT NULL CHECK (length(generation) = 32), 3 sequence_head INTEGER NOT NULL DEFAULT 0 CHECK (sequence_head >= 0), 4 state TEXT NOT NULL CHECK (state IN ('active', 'retired')), 5 created_at_unix_ms INTEGER NOT NULL CHECK (created_at_unix_ms > 0), 6 retired_at_unix_ms INTEGER, 7 CHECK ( 8 (state = 'active' AND retired_at_unix_ms IS NULL) 9 OR (state = 'retired' AND retired_at_unix_ms >= created_at_unix_ms) 10 ) 11 ) STRICT, WITHOUT ROWID; 12 13 CREATE TRIGGER radroots_runtime_source_generations_delete_guard 14 BEFORE DELETE ON radroots_runtime_source_generations 15 BEGIN 16 SELECT RAISE(ABORT, 'runtime source generations are append-only'); 17 END; 18 19 CREATE TRIGGER radroots_runtime_source_generations_identity_guard 20 BEFORE UPDATE OF generation, created_at_unix_ms ON radroots_runtime_source_generations 21 BEGIN 22 SELECT RAISE(ABORT, 'runtime source generation identity is immutable'); 23 END; 24 25 CREATE TABLE radroots_runtime_events ( 26 source_generation BLOB NOT NULL 27 REFERENCES radroots_runtime_source_generations(generation), 28 source_sequence INTEGER NOT NULL CHECK (source_sequence > 0), 29 event_id BLOB NOT NULL CHECK (length(event_id) = 32), 30 admission_stage TEXT NOT NULL CHECK (admission_stage IN ('raw', 'verified', 'visible')), 31 signed_event BLOB NOT NULL CHECK (length(signed_event) > 0), 32 admitted_at_unix_ms INTEGER NOT NULL CHECK (admitted_at_unix_ms > 0), 33 updated_at_unix_ms INTEGER NOT NULL CHECK (updated_at_unix_ms >= admitted_at_unix_ms), 34 PRIMARY KEY (source_generation, source_sequence), 35 UNIQUE (event_id) 36 ) STRICT, WITHOUT ROWID; 37 38 CREATE UNIQUE INDEX radroots_runtime_events_event_id_idx 39 ON radroots_runtime_events(event_id); 40 41 CREATE INDEX radroots_runtime_events_admission_idx 42 ON radroots_runtime_events(admission_stage, source_generation, source_sequence); 43 44 CREATE TRIGGER radroots_runtime_events_delete_guard 45 BEFORE DELETE ON radroots_runtime_events 46 BEGIN 47 SELECT RAISE(ABORT, 'canonical runtime events are append-only'); 48 END; 49 50 CREATE TRIGGER radroots_runtime_events_raw_update_guard 51 BEFORE UPDATE OF source_generation, source_sequence, event_id, signed_event, admitted_at_unix_ms 52 ON radroots_runtime_events 53 BEGIN 54 SELECT RAISE(ABORT, 'canonical runtime event authority is immutable'); 55 END; 56 57 CREATE TABLE radroots_runtime_event_provenance ( 58 event_id BLOB NOT NULL REFERENCES radroots_runtime_events(event_id), 59 transport_kind TEXT NOT NULL CHECK (length(transport_kind) > 0), 60 endpoint_fingerprint BLOB NOT NULL CHECK (length(endpoint_fingerprint) > 0), 61 observation_kind TEXT NOT NULL CHECK (length(observation_kind) > 0), 62 first_observed_at_unix_ms INTEGER NOT NULL CHECK (first_observed_at_unix_ms > 0), 63 last_observed_at_unix_ms INTEGER NOT NULL 64 CHECK (last_observed_at_unix_ms >= first_observed_at_unix_ms), 65 observation_count INTEGER NOT NULL CHECK (observation_count > 0), 66 PRIMARY KEY (event_id, transport_kind, endpoint_fingerprint, observation_kind) 67 ) STRICT, WITHOUT ROWID; 68 69 CREATE INDEX radroots_runtime_event_provenance_observed_idx 70 ON radroots_runtime_event_provenance(last_observed_at_unix_ms, event_id); 71 72 CREATE TABLE radroots_runtime_journal_operations ( 73 instance_id BLOB PRIMARY KEY NOT NULL CHECK (length(instance_id) = 16), 74 operation_id BLOB NOT NULL CHECK (length(operation_id) > 0), 75 idempotency_key TEXT NOT NULL CHECK (length(idempotency_key) BETWEEN 1 AND 256), 76 input_digest BLOB NOT NULL CHECK (length(input_digest) = 32), 77 prepared_at_unix_ms INTEGER NOT NULL CHECK (prepared_at_unix_ms > 0), 78 revision INTEGER NOT NULL CHECK (revision > 0), 79 stage TEXT NOT NULL CHECK (stage IN ('prepared', 'signed', 'recoverable', 'committed')), 80 event_id BLOB CHECK (event_id IS NULL OR length(event_id) = 32), 81 recovery_record BLOB, 82 cancellation_state TEXT NOT NULL 83 CHECK (cancellation_state IN ('not_requested', 'cancelled_before_commit', 'observed_after_commit')), 84 committed_at_unix_ms INTEGER, 85 updated_at_unix_ms INTEGER NOT NULL CHECK (updated_at_unix_ms >= prepared_at_unix_ms), 86 CHECK ((stage IN ('signed', 'committed') AND event_id IS NOT NULL) OR stage IN ('prepared', 'recoverable')), 87 CHECK ((stage = 'recoverable' AND recovery_record IS NOT NULL) OR (stage <> 'recoverable' AND recovery_record IS NULL)), 88 CHECK ((stage = 'committed' AND committed_at_unix_ms >= prepared_at_unix_ms) OR (stage <> 'committed' AND committed_at_unix_ms IS NULL)) 89 ) STRICT, WITHOUT ROWID; 90 91 CREATE UNIQUE INDEX radroots_runtime_journal_idempotency_idx 92 ON radroots_runtime_journal_operations(operation_id, idempotency_key); 93 94 CREATE INDEX radroots_runtime_journal_recovery_idx 95 ON radroots_runtime_journal_operations(stage, updated_at_unix_ms, instance_id) 96 WHERE stage = 'recoverable'; 97 98 CREATE TABLE radroots_runtime_outbox_items ( 99 item_id BLOB PRIMARY KEY NOT NULL CHECK (length(item_id) = 16), 100 operation_instance_id BLOB NOT NULL 101 REFERENCES radroots_runtime_journal_operations(instance_id), 102 plan_digest BLOB NOT NULL CHECK (length(plan_digest) = 32), 103 delivery_request BLOB NOT NULL CHECK (length(delivery_request) > 0), 104 revision INTEGER NOT NULL CHECK (revision > 0), 105 stage TEXT NOT NULL CHECK (stage IN ('pending', 'leased', 'retryable', 'satisfied', 'exhausted')), 106 lease_id BLOB CHECK (lease_id IS NULL OR length(lease_id) = 16), 107 lease_owner TEXT CHECK (lease_owner IS NULL OR length(lease_owner) BETWEEN 1 AND 128), 108 lease_acquired_at_unix_ms INTEGER, 109 lease_expires_at_unix_ms INTEGER, 110 last_attempt INTEGER CHECK (last_attempt IS NULL OR last_attempt > 0), 111 satisfaction TEXT NOT NULL CHECK (satisfaction IN ('pending', 'satisfied', 'exhausted')), 112 retry_not_before_unix_ms INTEGER, 113 created_at_unix_ms INTEGER NOT NULL CHECK (created_at_unix_ms > 0), 114 updated_at_unix_ms INTEGER NOT NULL CHECK (updated_at_unix_ms >= created_at_unix_ms), 115 UNIQUE (operation_instance_id, plan_digest), 116 CHECK ( 117 (stage = 'leased' AND lease_id IS NOT NULL AND lease_owner IS NOT NULL 118 AND lease_acquired_at_unix_ms > 0 AND lease_expires_at_unix_ms > lease_acquired_at_unix_ms) 119 OR (stage <> 'leased' AND lease_id IS NULL AND lease_owner IS NULL 120 AND lease_acquired_at_unix_ms IS NULL AND lease_expires_at_unix_ms IS NULL) 121 ) 122 ) STRICT, WITHOUT ROWID; 123 124 CREATE INDEX radroots_runtime_outbox_ready_idx 125 ON radroots_runtime_outbox_items(stage, retry_not_before_unix_ms, created_at_unix_ms, item_id); 126 127 CREATE TABLE radroots_runtime_outbox_targets ( 128 item_id BLOB NOT NULL REFERENCES radroots_runtime_outbox_items(item_id) ON DELETE CASCADE, 129 target_fingerprint BLOB NOT NULL CHECK (length(target_fingerprint) > 0), 130 target_request BLOB NOT NULL CHECK (length(target_request) > 0), 131 ordinal INTEGER NOT NULL CHECK (ordinal >= 0), 132 PRIMARY KEY (item_id, target_fingerprint), 133 UNIQUE (item_id, ordinal) 134 ) STRICT, WITHOUT ROWID; 135 136 CREATE TABLE radroots_runtime_delivery_evidence ( 137 item_id BLOB NOT NULL, 138 target_fingerprint BLOB NOT NULL, 139 attempt INTEGER NOT NULL CHECK (attempt > 0), 140 attempted INTEGER NOT NULL CHECK (attempted IN (0, 1)), 141 outcome BLOB NOT NULL CHECK (length(outcome) > 0), 142 retryability TEXT NOT NULL CHECK (retryability IN ('retryable', 'terminal', 'not_applicable')), 143 recorded_at_unix_ms INTEGER NOT NULL CHECK (recorded_at_unix_ms > 0), 144 PRIMARY KEY (item_id, target_fingerprint, attempt), 145 FOREIGN KEY (item_id, target_fingerprint) 146 REFERENCES radroots_runtime_outbox_targets(item_id, target_fingerprint) ON DELETE CASCADE 147 ) STRICT, WITHOUT ROWID; 148 149 CREATE INDEX radroots_runtime_delivery_evidence_item_idx 150 ON radroots_runtime_delivery_evidence(item_id, attempt, target_fingerprint); 151 152 CREATE TABLE radroots_runtime_projection_checkpoints ( 153 projection_id TEXT NOT NULL CHECK (length(projection_id) BETWEEN 1 AND 128), 154 projection_generation BLOB NOT NULL CHECK (length(projection_generation) = 32), 155 source_generation BLOB, 156 source_sequence INTEGER, 157 projected_rows INTEGER NOT NULL CHECK (projected_rows >= 0), 158 updated_at_unix_ms INTEGER NOT NULL CHECK (updated_at_unix_ms > 0), 159 PRIMARY KEY (projection_id, projection_generation), 160 FOREIGN KEY (source_generation) REFERENCES radroots_runtime_source_generations(generation), 161 CHECK ((source_generation IS NULL AND source_sequence IS NULL) OR (length(source_generation) = 32 AND source_sequence > 0)) 162 ) STRICT, WITHOUT ROWID; 163 164 CREATE TABLE radroots_runtime_projection_invalidations ( 165 projection_id TEXT NOT NULL CHECK (length(projection_id) BETWEEN 1 AND 128), 166 invalid_generation BLOB NOT NULL CHECK (length(invalid_generation) = 32), 167 replacement_generation BLOB NOT NULL CHECK (length(replacement_generation) = 32), 168 reason TEXT NOT NULL CHECK (reason IN ('source_generation_changed', 'projection_generation_changed', 'event_index_manifest_changed', 'integrity_failure', 'operator_requested')), 169 invalidated_at_unix_ms INTEGER NOT NULL CHECK (invalidated_at_unix_ms > 0), 170 PRIMARY KEY (projection_id, invalid_generation), 171 CHECK (invalid_generation <> replacement_generation) 172 ) STRICT, WITHOUT ROWID; 173 174 CREATE TABLE radroots_runtime_projection_rebuilds ( 175 ticket_id BLOB PRIMARY KEY NOT NULL CHECK (length(ticket_id) = 16), 176 projection_id TEXT NOT NULL, 177 invalid_generation BLOB NOT NULL, 178 replacement_generation BLOB NOT NULL, 179 revision INTEGER NOT NULL CHECK (revision > 0), 180 stage TEXT NOT NULL CHECK (stage IN ('requested', 'running', 'completed', 'failed')), 181 requested_at_unix_ms INTEGER NOT NULL CHECK (requested_at_unix_ms > 0), 182 updated_at_unix_ms INTEGER NOT NULL CHECK (updated_at_unix_ms >= requested_at_unix_ms), 183 FOREIGN KEY (projection_id, invalid_generation) 184 REFERENCES radroots_runtime_projection_invalidations(projection_id, invalid_generation) 185 ) STRICT, WITHOUT ROWID; 186 187 CREATE INDEX radroots_runtime_projection_rebuilds_stage_idx 188 ON radroots_runtime_projection_rebuilds(stage, updated_at_unix_ms, ticket_id); 189 190 CREATE TABLE radroots_runtime_event_index_manifests ( 191 projection_id TEXT NOT NULL CHECK (length(projection_id) BETWEEN 1 AND 128), 192 projection_generation BLOB NOT NULL CHECK (length(projection_generation) = 32), 193 manifest_digest BLOB NOT NULL CHECK (length(manifest_digest) = 32), 194 source_generation BLOB NOT NULL 195 REFERENCES radroots_runtime_source_generations(generation), 196 created_at_unix_ms INTEGER NOT NULL CHECK (created_at_unix_ms > 0), 197 PRIMARY KEY (projection_id, projection_generation), 198 UNIQUE (manifest_digest) 199 ) STRICT, WITHOUT ROWID; 200 201 CREATE TABLE radroots_runtime_event_index_shards ( 202 manifest_digest BLOB NOT NULL 203 REFERENCES radroots_runtime_event_index_manifests(manifest_digest) ON DELETE CASCADE, 204 shard_id TEXT NOT NULL CHECK (length(shard_id) BETWEEN 1 AND 128), 205 ordinal INTEGER NOT NULL CHECK (ordinal >= 0), 206 artifact_path TEXT NOT NULL CHECK (length(artifact_path) BETWEEN 1 AND 512), 207 artifact_digest BLOB NOT NULL CHECK (length(artifact_digest) = 32), 208 cursor BLOB NOT NULL CHECK (length(cursor) BETWEEN 1 AND 2048), 209 PRIMARY KEY (manifest_digest, shard_id), 210 UNIQUE (manifest_digest, ordinal), 211 UNIQUE (manifest_digest, artifact_path) 212 ) STRICT, WITHOUT ROWID; 213 214 CREATE TABLE radroots_runtime_event_index_checkpoints ( 215 manifest_digest BLOB NOT NULL, 216 shard_id TEXT NOT NULL, 217 indexed_through_event_id BLOB CHECK (indexed_through_event_id IS NULL OR length(indexed_through_event_id) = 32), 218 indexed_events INTEGER NOT NULL CHECK (indexed_events >= 0), 219 updated_at_unix_ms INTEGER NOT NULL CHECK (updated_at_unix_ms > 0), 220 PRIMARY KEY (manifest_digest, shard_id), 221 FOREIGN KEY (manifest_digest, shard_id) 222 REFERENCES radroots_runtime_event_index_shards(manifest_digest, shard_id) ON DELETE CASCADE 223 ) STRICT, WITHOUT ROWID; 224 225 CREATE TABLE radroots_runtime_atomic_commits ( 226 commit_id BLOB PRIMARY KEY NOT NULL CHECK (length(commit_id) = 16), 227 commit_digest BLOB NOT NULL CHECK (length(commit_digest) = 32), 228 workflow_kind TEXT NOT NULL CHECK (workflow_kind IN ('prepared', 'signed', 'enqueued', 'delivered', 'ingested')), 229 requested_at_unix_ms INTEGER NOT NULL CHECK (requested_at_unix_ms > 0), 230 committed_at_unix_ms INTEGER NOT NULL CHECK (committed_at_unix_ms >= requested_at_unix_ms), 231 receipt BLOB NOT NULL CHECK (length(receipt) > 0) 232 ) STRICT, WITHOUT ROWID;