0004_context_bound_envelopes.up.sql (6811B)
1 CREATE TABLE radroots_private_envelope_v2_preflight ( 2 unsupported_v2_rows INTEGER NOT NULL CHECK(unsupported_v2_rows = 0) 3 ) STRICT; 4 5 INSERT INTO radroots_private_envelope_v2_preflight(unsupported_v2_rows) 6 SELECT COUNT(*) FROM radroots_private_artifacts WHERE envelope_version = 2; 7 8 DROP TABLE radroots_private_envelope_v2_preflight; 9 10 ALTER TABLE radroots_private_artifacts 11 ADD COLUMN context_fingerprint BLOB 12 CHECK(context_fingerprint IS NULL OR length(context_fingerprint) = 32); 13 14 ALTER TABLE radroots_private_artifacts 15 ADD COLUMN last_reseal_id BLOB 16 CHECK(last_reseal_id IS NULL OR length(last_reseal_id) = 16); 17 18 ALTER TABLE radroots_private_artifacts 19 ADD COLUMN last_reseal_fingerprint BLOB 20 CHECK(last_reseal_fingerprint IS NULL OR length(last_reseal_fingerprint) = 32); 21 22 CREATE TABLE radroots_private_envelope_reseals ( 23 reseal_id BLOB PRIMARY KEY NOT NULL CHECK(length(reseal_id) = 16), 24 artifact_id BLOB NOT NULL CHECK(length(artifact_id) = 16), 25 request_fingerprint BLOB NOT NULL CHECK(length(request_fingerprint) = 32), 26 expected_revision INTEGER NOT NULL CHECK(expected_revision > 0), 27 expected_commitment BLOB NOT NULL CHECK(length(expected_commitment) = 32), 28 committed_revision INTEGER NOT NULL CHECK(committed_revision = expected_revision + 1), 29 next_commitment BLOB NOT NULL CHECK(length(next_commitment) = 32), 30 committed_at_unix_ms INTEGER NOT NULL CHECK(committed_at_unix_ms > 0), 31 FOREIGN KEY(artifact_id) REFERENCES radroots_private_artifacts(artifact_id), 32 UNIQUE(artifact_id, committed_revision), 33 UNIQUE(artifact_id, request_fingerprint) 34 ) STRICT, WITHOUT ROWID; 35 36 CREATE INDEX radroots_private_envelope_reseals_artifact_idx 37 ON radroots_private_envelope_reseals(artifact_id, committed_revision); 38 39 CREATE TRIGGER radroots_private_envelope_reseals_update_guard 40 BEFORE UPDATE ON radroots_private_envelope_reseals 41 BEGIN 42 SELECT RAISE(ABORT, 'private envelope reseal evidence is immutable'); 43 END; 44 45 CREATE TRIGGER radroots_private_envelope_reseals_delete_guard 46 BEFORE DELETE ON radroots_private_envelope_reseals 47 BEGIN 48 SELECT RAISE(ABORT, 'private envelope reseal evidence is retained'); 49 END; 50 51 DROP TRIGGER radroots_private_artifacts_identity_guard; 52 DROP TRIGGER radroots_private_artifacts_envelope_guard; 53 54 CREATE TRIGGER radroots_private_artifacts_identity_guard 55 BEFORE UPDATE OF artifact_id, artifact_kind, schema_id, created_at_unix_ms 56 ON radroots_private_artifacts 57 BEGIN 58 SELECT RAISE(ABORT, 'private artifact identity is immutable'); 59 END; 60 61 CREATE TRIGGER radroots_private_artifacts_insert_envelope_guard 62 BEFORE INSERT ON radroots_private_artifacts 63 WHEN NOT ( 64 (NEW.encrypted_envelope IS NULL 65 AND NEW.envelope_version IS NULL 66 AND NEW.context_fingerprint IS NULL 67 AND NEW.last_reseal_id IS NULL 68 AND NEW.last_reseal_fingerprint IS NULL) 69 OR 70 (NEW.encrypted_envelope IS NOT NULL 71 AND NEW.envelope_version = 2 72 AND NEW.context_fingerprint IS NOT NULL 73 AND NEW.last_reseal_id IS NULL 74 AND NEW.last_reseal_fingerprint IS NULL) 75 ) 76 BEGIN 77 SELECT RAISE(ABORT, 'new private envelopes must be context-bound v2'); 78 END; 79 80 CREATE TRIGGER radroots_private_artifacts_envelope_guard 81 BEFORE UPDATE OF 82 commitment, 83 protected_size_bytes, 84 secret_provider, 85 secret_reference, 86 key_version, 87 envelope_version, 88 encrypted_envelope, 89 context_fingerprint, 90 last_reseal_id, 91 last_reseal_fingerprint 92 ON radroots_private_artifacts 93 WHEN NOT ( 94 ( 95 OLD.encrypted_envelope IS NULL 96 AND OLD.envelope_version IS NULL 97 AND OLD.context_fingerprint IS NULL 98 AND NEW.encrypted_envelope IS NOT NULL 99 AND NEW.envelope_version = 2 100 AND NEW.context_fingerprint IS NOT NULL 101 AND NEW.last_reseal_id IS NULL 102 AND NEW.last_reseal_fingerprint IS NULL 103 AND NEW.commitment = OLD.commitment 104 AND NEW.protected_size_bytes = OLD.protected_size_bytes 105 AND NEW.secret_provider = OLD.secret_provider 106 AND NEW.secret_reference = OLD.secret_reference 107 AND NEW.key_version = OLD.key_version 108 AND NEW.revision = OLD.revision 109 AND NEW.stage = OLD.stage 110 AND NEW.updated_at_unix_ms = OLD.updated_at_unix_ms 111 ) 112 OR 113 ( 114 OLD.encrypted_envelope IS NULL 115 AND OLD.envelope_version IS NULL 116 AND OLD.context_fingerprint IS NULL 117 AND NEW.encrypted_envelope IS NULL 118 AND NEW.envelope_version IS NULL 119 AND NEW.context_fingerprint IS NULL 120 AND NEW.last_reseal_id IS NOT NULL 121 AND NEW.last_reseal_id IS NOT OLD.last_reseal_id 122 AND NEW.last_reseal_fingerprint IS NOT NULL 123 AND NEW.revision = OLD.revision + 1 124 AND NEW.stage = 'active' 125 AND NEW.updated_at_unix_ms > OLD.updated_at_unix_ms 126 AND NEW.deleted_at_unix_ms IS NULL 127 AND NEW.deletion_reason IS NULL 128 AND NEW.tombstone_commitment IS NULL 129 ) 130 OR 131 ( 132 OLD.encrypted_envelope IS NOT NULL 133 AND OLD.envelope_version = 1 134 AND OLD.context_fingerprint IS NULL 135 AND OLD.last_reseal_id IS NULL 136 AND OLD.last_reseal_fingerprint IS NULL 137 AND OLD.stage = 'active' 138 AND NEW.encrypted_envelope IS NOT NULL 139 AND NEW.envelope_version = 2 140 AND NEW.context_fingerprint IS NOT NULL 141 AND NEW.last_reseal_id IS NOT NULL 142 AND NEW.last_reseal_fingerprint IS NOT NULL 143 AND NEW.revision = OLD.revision + 1 144 AND NEW.stage = 'active' 145 AND NEW.updated_at_unix_ms > OLD.updated_at_unix_ms 146 AND NEW.deleted_at_unix_ms IS NULL 147 AND NEW.deletion_reason IS NULL 148 AND NEW.tombstone_commitment IS NULL 149 ) 150 OR 151 ( 152 OLD.encrypted_envelope IS NOT NULL 153 AND NEW.encrypted_envelope IS NULL 154 AND NEW.envelope_version IS NULL 155 AND NEW.context_fingerprint = OLD.context_fingerprint 156 AND NEW.last_reseal_id IS OLD.last_reseal_id 157 AND NEW.last_reseal_fingerprint IS OLD.last_reseal_fingerprint 158 AND NEW.commitment = OLD.commitment 159 AND NEW.protected_size_bytes = OLD.protected_size_bytes 160 AND NEW.secret_provider = OLD.secret_provider 161 AND NEW.secret_reference = OLD.secret_reference 162 AND NEW.key_version = OLD.key_version 163 AND NEW.revision = OLD.revision + 1 164 AND NEW.stage = 'tombstoned' 165 AND NEW.updated_at_unix_ms > OLD.updated_at_unix_ms 166 ) 167 ) 168 BEGIN 169 SELECT RAISE(ABORT, 'private envelope mutation is not an authorized transition'); 170 END; 171 172 CREATE TRIGGER radroots_private_artifacts_reseal_audit 173 AFTER UPDATE OF last_reseal_id, last_reseal_fingerprint ON radroots_private_artifacts 174 WHEN NEW.last_reseal_id IS NOT NULL AND NEW.last_reseal_id IS NOT OLD.last_reseal_id 175 BEGIN 176 INSERT INTO radroots_private_envelope_reseals ( 177 reseal_id, 178 artifact_id, 179 request_fingerprint, 180 expected_revision, 181 expected_commitment, 182 committed_revision, 183 next_commitment, 184 committed_at_unix_ms 185 ) VALUES ( 186 NEW.last_reseal_id, 187 NEW.artifact_id, 188 NEW.last_reseal_fingerprint, 189 OLD.revision, 190 OLD.commitment, 191 NEW.revision, 192 NEW.commitment, 193 NEW.updated_at_unix_ms 194 ); 195 END;