0001_private.up.sql (3412B)
1 CREATE TABLE radroots_private_artifacts ( 2 artifact_id BLOB PRIMARY KEY NOT NULL CHECK (length(artifact_id) = 16), 3 artifact_kind TEXT NOT NULL CHECK (length(artifact_kind) BETWEEN 1 AND 128), 4 schema_id TEXT NOT NULL CHECK (length(schema_id) BETWEEN 1 AND 128), 5 commitment BLOB NOT NULL CHECK (length(commitment) = 32), 6 protected_size_bytes INTEGER NOT NULL CHECK (protected_size_bytes > 0), 7 secret_provider TEXT NOT NULL CHECK (length(secret_provider) BETWEEN 1 AND 64), 8 secret_reference TEXT NOT NULL CHECK (length(secret_reference) BETWEEN 1 AND 512), 9 key_version INTEGER NOT NULL CHECK (key_version BETWEEN 1 AND 4294967295), 10 envelope_version INTEGER CHECK (envelope_version IS NULL OR envelope_version > 0), 11 encrypted_envelope BLOB, 12 delete_not_before_unix_ms INTEGER CHECK (delete_not_before_unix_ms > 0), 13 expires_at_unix_ms INTEGER CHECK (expires_at_unix_ms > 0), 14 revision INTEGER NOT NULL CHECK (revision > 0), 15 stage TEXT NOT NULL CHECK (stage IN ('active', 'expired', 'tombstoned')), 16 created_at_unix_ms INTEGER NOT NULL CHECK (created_at_unix_ms > 0), 17 updated_at_unix_ms INTEGER NOT NULL CHECK (updated_at_unix_ms >= created_at_unix_ms), 18 deleted_at_unix_ms INTEGER, 19 deletion_reason TEXT CHECK ( 20 deletion_reason IS NULL OR deletion_reason IN ( 21 'user_requested', 22 'retention_expired', 23 'key_revoked', 24 'integrity_failure', 25 'operator_requested' 26 ) 27 ), 28 tombstone_commitment BLOB CHECK ( 29 tombstone_commitment IS NULL OR length(tombstone_commitment) = 32 30 ), 31 CHECK ( 32 (encrypted_envelope IS NULL AND envelope_version IS NULL) 33 OR ( 34 encrypted_envelope IS NOT NULL 35 AND envelope_version IS NOT NULL 36 AND length(encrypted_envelope) = protected_size_bytes 37 ) 38 ), 39 CHECK ( 40 (stage <> 'tombstoned' AND deleted_at_unix_ms IS NULL 41 AND deletion_reason IS NULL AND tombstone_commitment IS NULL) 42 OR (stage = 'tombstoned' AND deleted_at_unix_ms = updated_at_unix_ms 43 AND deletion_reason IS NOT NULL AND tombstone_commitment = commitment 44 AND encrypted_envelope IS NULL AND envelope_version IS NULL) 45 ) 46 ) STRICT, WITHOUT ROWID; 47 48 CREATE INDEX radroots_private_artifacts_expiry_idx 49 ON radroots_private_artifacts(stage, expires_at_unix_ms, artifact_id) 50 WHERE stage = 'active' AND expires_at_unix_ms IS NOT NULL; 51 52 CREATE INDEX radroots_private_artifacts_kind_idx 53 ON radroots_private_artifacts(artifact_kind, schema_id, stage, artifact_id); 54 55 CREATE INDEX radroots_private_artifacts_key_version_idx 56 ON radroots_private_artifacts(secret_provider, key_version, stage, artifact_id); 57 58 CREATE TRIGGER radroots_private_artifacts_delete_guard 59 BEFORE DELETE ON radroots_private_artifacts 60 BEGIN 61 SELECT RAISE(ABORT, 'private artifacts require durable tombstones'); 62 END; 63 64 CREATE TRIGGER radroots_private_artifacts_identity_guard 65 BEFORE UPDATE OF 66 artifact_id, 67 artifact_kind, 68 schema_id, 69 commitment, 70 protected_size_bytes, 71 secret_provider, 72 secret_reference, 73 key_version, 74 created_at_unix_ms 75 ON radroots_private_artifacts 76 BEGIN 77 SELECT RAISE(ABORT, 'private artifact identity is immutable'); 78 END; 79 80 CREATE TRIGGER radroots_private_artifacts_envelope_guard 81 BEFORE UPDATE OF encrypted_envelope, envelope_version ON radroots_private_artifacts 82 WHEN OLD.encrypted_envelope IS NOT NULL AND NEW.encrypted_envelope IS NOT NULL 83 BEGIN 84 SELECT RAISE(ABORT, 'private artifact envelopes are immutable'); 85 END;