0011_authored_operations.up.sql (10367B)
1 ALTER TABLE radroots_runtime_events 2 ADD COLUMN admitted_contract_id TEXT 3 CHECK(admitted_contract_id IS NULL OR ( 4 length(admitted_contract_id) BETWEEN 1 AND 192 5 AND admitted_contract_id = lower(admitted_contract_id) 6 AND admitted_contract_id LIKE 'radroots.%' 7 )); 8 9 ALTER TABLE radroots_runtime_events 10 ADD COLUMN admitted_registry_version INTEGER 11 CHECK(admitted_registry_version IS NULL OR admitted_registry_version > 0); 12 13 CREATE TRIGGER radroots_runtime_events_contract_metadata_guard 14 BEFORE UPDATE OF admitted_contract_id, admitted_registry_version 15 ON radroots_runtime_events 16 WHEN 17 (OLD.admitted_contract_id IS NOT NULL AND ( 18 NEW.admitted_contract_id IS NULL OR NEW.admitted_contract_id != OLD.admitted_contract_id 19 )) 20 OR (OLD.admitted_registry_version IS NOT NULL AND ( 21 NEW.admitted_registry_version IS NULL 22 OR NEW.admitted_registry_version != OLD.admitted_registry_version 23 )) 24 OR ((NEW.admitted_contract_id IS NULL) != (NEW.admitted_registry_version IS NULL)) 25 BEGIN 26 SELECT RAISE(ABORT, 'event contract metadata must be paired and immutable'); 27 END; 28 29 CREATE TRIGGER radroots_runtime_events_contract_metadata_insert_guard 30 BEFORE INSERT ON radroots_runtime_events 31 WHEN ((NEW.admitted_contract_id IS NULL) != (NEW.admitted_registry_version IS NULL)) 32 BEGIN 33 SELECT RAISE(ABORT, 'event contract metadata must be paired'); 34 END; 35 36 CREATE TABLE radroots_runtime_authored_operations ( 37 operation_id BLOB PRIMARY KEY CHECK(length(operation_id) = 16), 38 artifact_count INTEGER NOT NULL CHECK(artifact_count BETWEEN 1 AND 1024), 39 created_at_unix_ms INTEGER NOT NULL CHECK(created_at_unix_ms > 0), 40 updated_at_unix_ms INTEGER NOT NULL CHECK(updated_at_unix_ms >= created_at_unix_ms), 41 revision INTEGER NOT NULL CHECK(revision > 0), 42 snapshot BLOB NOT NULL CHECK(length(snapshot) BETWEEN 2 AND 4194304) 43 ) STRICT; 44 45 CREATE TABLE radroots_runtime_authored_artifacts ( 46 artifact_id BLOB PRIMARY KEY CHECK(length(artifact_id) = 16), 47 operation_id BLOB NOT NULL REFERENCES radroots_runtime_authored_operations(operation_id) ON DELETE CASCADE, 48 ordinal INTEGER NOT NULL CHECK(ordinal BETWEEN 0 AND 65535), 49 origin TEXT NOT NULL CHECK(origin IN ('planned', 'imported_signed')), 50 signing_state TEXT NOT NULL CHECK(signing_state IN ( 51 'planned', 'signed', 'retryable', 'indeterminate', 'failed_terminal', 'cancelled' 52 )), 53 admission_state TEXT NOT NULL CHECK(admission_state IN ( 54 'pending', 'inserted', 'duplicate', 'retryable', 'rejected', 'cancelled' 55 )), 56 plan_wire BLOB CHECK(plan_wire IS NULL OR length(plan_wire) BETWEEN 2 AND 1048576), 57 signed_raw_json BLOB CHECK(signed_raw_json IS NULL OR length(signed_raw_json) BETWEEN 2 AND 1048576), 58 signed_raw_sha256 BLOB CHECK(signed_raw_sha256 IS NULL OR length(signed_raw_sha256) = 32), 59 signing_claim_token BLOB CHECK(signing_claim_token IS NULL OR length(signing_claim_token) = 16), 60 signing_claim_generation INTEGER CHECK(signing_claim_generation IS NULL OR signing_claim_generation > 0), 61 signing_claim_revision INTEGER CHECK(signing_claim_revision IS NULL OR signing_claim_revision > 0), 62 signing_claim_expires_at_unix_ms INTEGER CHECK(signing_claim_expires_at_unix_ms IS NULL OR signing_claim_expires_at_unix_ms > 0), 63 admission_claim_token BLOB CHECK(admission_claim_token IS NULL OR length(admission_claim_token) = 16), 64 admission_claim_generation INTEGER CHECK(admission_claim_generation IS NULL OR admission_claim_generation > 0), 65 admission_claim_revision INTEGER CHECK(admission_claim_revision IS NULL OR admission_claim_revision > 0), 66 admission_claim_expires_at_unix_ms INTEGER CHECK(admission_claim_expires_at_unix_ms IS NULL OR admission_claim_expires_at_unix_ms > 0), 67 retry_not_before_unix_ms INTEGER CHECK(retry_not_before_unix_ms IS NULL OR retry_not_before_unix_ms > 0), 68 last_failure_code TEXT CHECK(last_failure_code IS NULL OR length(last_failure_code) BETWEEN 1 AND 96), 69 created_at_unix_ms INTEGER NOT NULL CHECK(created_at_unix_ms > 0), 70 updated_at_unix_ms INTEGER NOT NULL CHECK(updated_at_unix_ms >= created_at_unix_ms), 71 revision INTEGER NOT NULL CHECK(revision > 0), 72 snapshot BLOB NOT NULL CHECK(length(snapshot) BETWEEN 2 AND 4194304), 73 UNIQUE(operation_id, ordinal), 74 CHECK((signing_state = 'signed') = (signed_raw_json IS NOT NULL)), 75 CHECK((signed_raw_json IS NULL) = (signed_raw_sha256 IS NULL)), 76 CHECK((plan_wire IS NULL) = (origin = 'imported_signed')), 77 CHECK((signing_claim_token IS NULL) = (signing_claim_generation IS NULL)), 78 CHECK((signing_claim_token IS NULL) = (signing_claim_revision IS NULL)), 79 CHECK((signing_claim_token IS NULL) = (signing_claim_expires_at_unix_ms IS NULL)), 80 CHECK((admission_claim_token IS NULL) = (admission_claim_generation IS NULL)), 81 CHECK((admission_claim_token IS NULL) = (admission_claim_revision IS NULL)), 82 CHECK((admission_claim_token IS NULL) = (admission_claim_expires_at_unix_ms IS NULL)) 83 ) STRICT; 84 85 CREATE INDEX radroots_runtime_authored_artifacts_signing_ready_idx 86 ON radroots_runtime_authored_artifacts( 87 signing_state, retry_not_before_unix_ms, signing_claim_expires_at_unix_ms, 88 updated_at_unix_ms, artifact_id 89 ); 90 91 CREATE INDEX radroots_runtime_authored_artifacts_admission_ready_idx 92 ON radroots_runtime_authored_artifacts( 93 admission_state, retry_not_before_unix_ms, admission_claim_expires_at_unix_ms, 94 updated_at_unix_ms, artifact_id 95 ); 96 97 CREATE TABLE radroots_runtime_authored_delivery_plans ( 98 plan_id BLOB PRIMARY KEY CHECK(length(plan_id) = 16), 99 artifact_id BLOB NOT NULL REFERENCES radroots_runtime_authored_artifacts(artifact_id) ON DELETE CASCADE, 100 request_digest BLOB NOT NULL CHECK(length(request_digest) = 32), 101 state TEXT NOT NULL CHECK(state IN ( 102 'pending', 'retryable', 'satisfied', 'exhausted', 'failed_terminal', 'cancelled' 103 )), 104 attempt_count INTEGER NOT NULL CHECK(attempt_count BETWEEN 0 AND 1024), 105 claim_token BLOB CHECK(claim_token IS NULL OR length(claim_token) = 16), 106 claim_generation INTEGER CHECK(claim_generation IS NULL OR claim_generation > 0), 107 claim_revision INTEGER CHECK(claim_revision IS NULL OR claim_revision > 0), 108 claim_expires_at_unix_ms INTEGER CHECK(claim_expires_at_unix_ms IS NULL OR claim_expires_at_unix_ms > 0), 109 retry_not_before_unix_ms INTEGER CHECK(retry_not_before_unix_ms IS NULL OR retry_not_before_unix_ms > 0), 110 last_failure_code TEXT CHECK(last_failure_code IS NULL OR length(last_failure_code) BETWEEN 1 AND 96), 111 created_at_unix_ms INTEGER NOT NULL CHECK(created_at_unix_ms > 0), 112 updated_at_unix_ms INTEGER NOT NULL CHECK(updated_at_unix_ms >= created_at_unix_ms), 113 revision INTEGER NOT NULL CHECK(revision > 0), 114 snapshot BLOB NOT NULL CHECK(length(snapshot) BETWEEN 2 AND 4194304), 115 CHECK((claim_token IS NULL) = (claim_generation IS NULL)), 116 CHECK((claim_token IS NULL) = (claim_revision IS NULL)), 117 CHECK((claim_token IS NULL) = (claim_expires_at_unix_ms IS NULL)), 118 CHECK((state = 'retryable') = (retry_not_before_unix_ms IS NOT NULL)) 119 ) STRICT; 120 121 CREATE INDEX radroots_runtime_authored_delivery_ready_idx 122 ON radroots_runtime_authored_delivery_plans( 123 state, retry_not_before_unix_ms, claim_expires_at_unix_ms, 124 updated_at_unix_ms, plan_id 125 ); 126 127 CREATE TABLE radroots_runtime_authored_delivery_targets ( 128 plan_id BLOB NOT NULL REFERENCES radroots_runtime_authored_delivery_plans(plan_id) ON DELETE CASCADE, 129 ordinal INTEGER NOT NULL CHECK(ordinal BETWEEN 0 AND 65535), 130 target_fingerprint TEXT NOT NULL CHECK(length(target_fingerprint) BETWEEN 1 AND 512), 131 target_snapshot BLOB NOT NULL CHECK(length(target_snapshot) BETWEEN 2 AND 65536), 132 PRIMARY KEY(plan_id, ordinal), 133 UNIQUE(plan_id, target_fingerprint) 134 ) STRICT, WITHOUT ROWID; 135 136 CREATE TABLE radroots_runtime_authored_delivery_attempts ( 137 plan_id BLOB NOT NULL REFERENCES radroots_runtime_authored_delivery_plans(plan_id) ON DELETE CASCADE, 138 attempt INTEGER NOT NULL CHECK(attempt BETWEEN 1 AND 1024), 139 satisfaction TEXT NOT NULL CHECK(satisfaction IN ('satisfied', 'pending', 'exhausted')), 140 recorded_at_unix_ms INTEGER NOT NULL CHECK(recorded_at_unix_ms > 0), 141 outcome_snapshot BLOB NOT NULL CHECK(length(outcome_snapshot) BETWEEN 2 AND 4194304), 142 PRIMARY KEY(plan_id, attempt) 143 ) STRICT, WITHOUT ROWID; 144 145 CREATE TABLE radroots_runtime_authored_atomic_commits ( 146 commit_id BLOB PRIMARY KEY CHECK(length(commit_id) = 16), 147 commit_digest BLOB NOT NULL CHECK(length(commit_digest) = 32), 148 phase TEXT NOT NULL CHECK(phase IN ( 149 'prepare', 'claim', 'signing', 'admission', 'delivery', 150 'signing_failure', 'admission_failure', 'delivery_failure', 'cancel' 151 )), 152 target_id BLOB NOT NULL CHECK(length(target_id) = 16), 153 requested_at_unix_ms INTEGER NOT NULL CHECK(requested_at_unix_ms > 0), 154 committed_at_unix_ms INTEGER NOT NULL CHECK(committed_at_unix_ms >= requested_at_unix_ms), 155 receipt BLOB NOT NULL CHECK(length(receipt) BETWEEN 2 AND 4194304) 156 ) STRICT; 157 158 CREATE TRIGGER radroots_runtime_authored_atomic_commits_update_guard 159 BEFORE UPDATE ON radroots_runtime_authored_atomic_commits 160 BEGIN 161 SELECT RAISE(ABORT, 'authored atomic receipts are immutable'); 162 END; 163 164 CREATE TABLE radroots_runtime_authored_migration_evidence ( 165 source_version INTEGER PRIMARY KEY CHECK(source_version = 10), 166 operation_count INTEGER NOT NULL CHECK(operation_count >= 0), 167 event_count INTEGER NOT NULL CHECK(event_count >= 0), 168 outbox_count INTEGER NOT NULL CHECK(outbox_count >= 0), 169 target_count INTEGER NOT NULL CHECK(target_count >= 0), 170 attempt_count INTEGER NOT NULL CHECK(attempt_count >= 0), 171 imported_count INTEGER NOT NULL CHECK(imported_count >= 0), 172 source_digest BLOB NOT NULL CHECK(length(source_digest) = 32), 173 completed_at_unix_ms INTEGER NOT NULL CHECK(completed_at_unix_ms > 0), 174 CHECK(imported_count <= operation_count), 175 CHECK(imported_count <= outbox_count) 176 ) STRICT; 177 178 CREATE TRIGGER radroots_runtime_authored_migration_evidence_update_guard 179 BEFORE UPDATE ON radroots_runtime_authored_migration_evidence 180 BEGIN 181 SELECT RAISE(ABORT, 'authored migration evidence is immutable'); 182 END; 183 184 CREATE TRIGGER radroots_runtime_authored_migration_evidence_delete_guard 185 BEFORE DELETE ON radroots_runtime_authored_migration_evidence 186 BEGIN 187 SELECT RAISE(ABORT, 'authored migration evidence is retained'); 188 END; 189 190 CREATE TRIGGER radroots_runtime_authored_atomic_commits_delete_guard 191 BEFORE DELETE ON radroots_runtime_authored_atomic_commits 192 BEGIN 193 SELECT RAISE(ABORT, 'authored atomic receipts are retained'); 194 END;