AISA v2.0 / Technical documentation / 008-immutability-and-payload-guards.sql
008-immutability-and-payload-guards.sql
SQL · 320 lines · 16,445 bytes · wiki path 10 Architecture/db/SrdSupport/008-immutability-and-payload-guards.sql · download the raw file · cited from Data Model — `SRD_SUPPORT`
Same folder: 001-work-and-planning.sql · 002-decisions-and-grants.sql · 003-evidence.sql · 004-ledger.sql · 005-registry.sql · 006-memory.sql · 007-policy-and-operations.sql · 009-seed-platform-policy.sql · 010-runtime-execution.sql · acceptance-install-clean.sql · acceptance-upgrade.sql · grant-runtime.sql · install-clean.sql · lint_ddl.py · upgrade.sql · verify-ledger.sql · verify-schema.sql
SET DEFINE OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE
-- 008 · IMMUTABILITY and PAYLOAD GUARDS — enforced in the database because the service is the thing being audited
-- (Architecture § 3.2; the SrdAi 002 pattern). Error codes -20010… are the platform's; -20001…-20009 are install errors.
--
-- -20010…-20029 append-only tables (UPDATE/DELETE refused)
-- -20030…-20039 the ledger chain
-- -20040…-20059 governance: published versions immutable; publish needs PASSED + approved; red eval set blocks
-- -20060…-20069 write shapes: approved template immutable; one extension of at most one day (D133)
-- -20070…-20079 data boundary
-- ------------------------------------------------------------------ append-only tables
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."AUDIT_LEDGER_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."AUDIT_LEDGER"
BEGIN
RAISE_APPLICATION_ERROR(-20010, 'AUDIT_LEDGER is append-only; correct with a CORRECTION record');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."COST_LEDGER_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."COST_LEDGER"
BEGIN
RAISE_APPLICATION_ERROR(-20011, 'COST_LEDGER is append-only');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."LEDGER_SEALS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."LEDGER_SEALS"
BEGIN
RAISE_APPLICATION_ERROR(-20012, 'LEDGER_SEALS are immutable');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."LEDGER_EXPORTS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."LEDGER_EXPORTS"
BEGIN
RAISE_APPLICATION_ERROR(-20013, 'LEDGER_EXPORTS is append-only; a failed export is retried as a new attempt row');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."GATE_DECISIONS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."GATE_DECISIONS"
BEGIN
RAISE_APPLICATION_ERROR(-20014, 'GATE_DECISIONS are immutable');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."WRITE_LOG_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."WRITE_LOG"
FOR EACH ROW
BEGIN
-- the only permitted transition: an intended write becomes executed/refused/failed/reverted, once
IF DELETING OR :OLD."OUTCOME" <> 'intended'
OR :NEW."STATEMENT_HASH" <> :OLD."STATEMENT_HASH" OR :NEW."TARGET" <> :OLD."TARGET"
OR :NEW."ENVIRONMENT" <> :OLD."ENVIRONMENT" OR :NEW."MODE" <> :OLD."MODE" THEN
RAISE_APPLICATION_ERROR(-20015, 'WRITE_LOG rows are append-only; an intended write may only receive its outcome');
END IF;
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PLSQL_SNAPSHOTS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."PLSQL_SNAPSHOTS"
BEGIN
RAISE_APPLICATION_ERROR(-20016, 'PLSQL_SNAPSHOTS are append-only');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PLAN_REVISIONS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."PLAN_REVISIONS"
BEGIN
RAISE_APPLICATION_ERROR(-20017, 'PLAN_REVISIONS are immutable; a change is a new revision');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."GOVERNANCE_EVENTS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."GOVERNANCE_EVENTS"
BEGIN
RAISE_APPLICATION_ERROR(-20018, 'GOVERNANCE_EVENTS are append-only');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."EVAL_RUNS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."EVAL_RUNS"
FOR EACH ROW
BEGIN
IF DELETING OR :OLD."ENDED_ON_UTC" IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20019, 'EVAL_RUNS are immutable once ended');
END IF;
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PAPERS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."PAPERS"
BEGIN
RAISE_APPLICATION_ERROR(-20020, 'PAPERS revision rows are immutable; a rewrite is a new REVISION_NO');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."MEMORY_USES_IMM_TRG"
BEFORE DELETE ON "SRD_SUPPORT"."MEMORY_USES"
BEGIN
RAISE_APPLICATION_ERROR(-20021, 'MEMORY_USES are never deleted; version pinning is the audit');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."RISK_ACCEPTANCES_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."RISK_ACCEPTANCES"
FOR EACH ROW
BEGIN
IF DELETING OR :OLD."WITHDRAWN_ON_UTC" IS NOT NULL
OR :NEW."CASE_TYPE" <> :OLD."CASE_TYPE" OR :NEW."GATE_KIND" <> :OLD."GATE_KIND"
OR :NEW."TARGET_CLASS" <> :OLD."TARGET_CLASS" OR :NEW."WRITE_CLASS" <> :OLD."WRITE_CLASS" THEN
RAISE_APPLICATION_ERROR(-20022, 'RISK_ACCEPTANCES may only be withdrawn, never edited or deleted (N-12)');
END IF;
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."ROLE_ASSIGNMENTS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."ROLE_ASSIGNMENTS"
FOR EACH ROW
BEGIN
IF DELETING OR :OLD."REVOKED_ON_UTC" IS NOT NULL OR :NEW."ROLE" <> :OLD."ROLE" OR :NEW."SUBJECT_ID" <> :OLD."SUBJECT_ID" THEN
RAISE_APPLICATION_ERROR(-20023, 'ROLE_ASSIGNMENTS may only be revoked, never edited or deleted');
END IF;
END;
/
-- ------------------------------------------------------------------ the ledger chain
-- PREV_HASH must be an existing RECORD_HASH — or the genesis constant for the first record only.
-- Together with UK_LEDGER_PREV_HASH this makes the chain strict: one successor per record, no orphans.
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."AUDIT_LEDGER_CHAIN_TRG"
BEFORE INSERT ON "SRD_SUPPORT"."AUDIT_LEDGER"
FOR EACH ROW
DECLARE
v_exists INTEGER;
v_genesis CONSTANT NVARCHAR2(64) := '0000000000000000000000000000000000000000000000000000000000000000';
BEGIN
IF :NEW."PREV_HASH" = v_genesis THEN
SELECT COUNT(*) INTO v_exists FROM "SRD_SUPPORT"."AUDIT_LEDGER";
IF v_exists > 0 THEN
RAISE_APPLICATION_ERROR(-20030, 'the genesis PREV_HASH may only appear on the first ledger record');
END IF;
ELSE
SELECT COUNT(*) INTO v_exists FROM "SRD_SUPPORT"."AUDIT_LEDGER" WHERE "RECORD_HASH" = :NEW."PREV_HASH";
IF v_exists = 0 THEN
RAISE_APPLICATION_ERROR(-20031, 'PREV_HASH does not name an existing RECORD_HASH — the chain would be broken');
END IF;
END IF;
IF :NEW."KIND" = 'CORRECTION' THEN
SELECT COUNT(*) INTO v_exists FROM "SRD_SUPPORT"."AUDIT_LEDGER" WHERE "RECORD_ID" = :NEW."SUPERSEDES_RECORD_ID";
IF v_exists = 0 THEN
RAISE_APPLICATION_ERROR(-20032, 'a CORRECTION must name an existing record it supersedes');
END IF;
END IF;
END;
/
-- ------------------------------------------------------------------ governance: published versions are immutable,
-- publishing needs a PASSED evaluation and an approval, and a red eval set blocks (Agent Framework § 6.1, § 6.5)
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PROMPTS_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."PROMPTS"
FOR EACH ROW
DECLARE
v_passed INTEGER; v_approved INTEGER;
BEGIN
IF :OLD."STATE" IN ('published','retired') AND (
DBMS_LOB.COMPARE(:NEW."BODY", :OLD."BODY") <> 0 OR :NEW."CONTENT_HASH" <> :OLD."CONTENT_HASH"
OR DBMS_LOB.COMPARE(:NEW."VARIABLES", :OLD."VARIABLES") <> 0 OR :NEW."PROMPT_KEY" <> :OLD."PROMPT_KEY" OR :NEW."VERSION" <> :OLD."VERSION") THEN
RAISE_APPLICATION_ERROR(-20040, 'a published prompt version is immutable; a change is a new version');
END IF;
IF :OLD."STATE" = 'retired' AND :NEW."STATE" <> 'retired' THEN
RAISE_APPLICATION_ERROR(-20041, 'a retired prompt version does not come back; rollback re-publishes a previous version');
END IF;
IF :NEW."STATE" = 'published' AND :OLD."STATE" <> 'published' THEN
SELECT COUNT(*) INTO v_passed FROM "SRD_SUPPORT"."GOVERNANCE_EVENTS"
WHERE "SUBJECT_TYPE" = 'prompt' AND "SUBJECT_KEY" = :NEW."PROMPT_KEY" AND "SUBJECT_VERSION" = :NEW."VERSION" AND "EVENT" = 'evaluated' AND "RESULT" = 'PASSED';
SELECT COUNT(*) INTO v_approved FROM "SRD_SUPPORT"."GOVERNANCE_EVENTS"
WHERE "SUBJECT_TYPE" = 'prompt' AND "SUBJECT_KEY" = :NEW."PROMPT_KEY" AND "SUBJECT_VERSION" = :NEW."VERSION" AND "EVENT" = 'approved';
IF v_passed = 0 OR v_approved = 0 THEN
RAISE_APPLICATION_ERROR(-20042, 'publishing a prompt needs a PASSED evaluation and an approval recorded in GOVERNANCE_EVENTS');
END IF;
:NEW."PUBLISHED_ON_UTC" := COALESCE(:NEW."PUBLISHED_ON_UTC", SYS_EXTRACT_UTC(SYSTIMESTAMP));
END IF;
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."SKILLS_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."SKILLS"
FOR EACH ROW
DECLARE
v_passed INTEGER; v_approved INTEGER;
BEGIN
IF :OLD."STATE" IN ('published','retired') AND (:NEW."MANIFEST_HASH" <> :OLD."MANIFEST_HASH" OR DBMS_LOB.COMPARE(:NEW."MANIFEST", :OLD."MANIFEST") <> 0) THEN
RAISE_APPLICATION_ERROR(-20043, 'a published skill version is immutable; a change is a new version');
END IF;
IF :NEW."STATE" = 'published' AND :OLD."STATE" <> 'published' THEN
SELECT COUNT(*) INTO v_passed FROM "SRD_SUPPORT"."GOVERNANCE_EVENTS"
WHERE "SUBJECT_TYPE" = 'skill' AND "SUBJECT_KEY" = :NEW."SKILL_KEY" AND "SUBJECT_VERSION" = :NEW."VERSION" AND "EVENT" = 'evaluated' AND "RESULT" = 'PASSED';
SELECT COUNT(*) INTO v_approved FROM "SRD_SUPPORT"."GOVERNANCE_EVENTS"
WHERE "SUBJECT_TYPE" = 'skill' AND "SUBJECT_KEY" = :NEW."SKILL_KEY" AND "SUBJECT_VERSION" = :NEW."VERSION" AND "EVENT" = 'approved';
IF v_passed = 0 OR v_approved = 0 THEN
RAISE_APPLICATION_ERROR(-20044, 'publishing a skill needs a PASSED evaluation and an approval recorded in GOVERNANCE_EVENTS');
END IF;
:NEW."PUBLISHED_ON_UTC" := COALESCE(:NEW."PUBLISHED_ON_UTC", SYS_EXTRACT_UTC(SYSTIMESTAMP));
END IF;
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PROFILES_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."PROFILES"
FOR EACH ROW
DECLARE
v_passed INTEGER; v_approved INTEGER; v_red INTEGER; v_zone NVARCHAR2(16); v_dpa NUMBER(1);
BEGIN
IF :OLD."STATE" IN ('published','retired') AND (:NEW."CONTENT_HASH" <> :OLD."CONTENT_HASH" OR :NEW."PROMPT_ID" <> :OLD."PROMPT_ID"
OR :NEW."MODEL_ROUTE_ID" <> :OLD."MODEL_ROUTE_ID" OR DBMS_LOB.COMPARE(:NEW."TOOLS", :OLD."TOOLS") <> 0
OR DBMS_LOB.COMPARE(:NEW."DENIED_CONTEXT", :OLD."DENIED_CONTEXT") <> 0 OR DBMS_LOB.COMPARE(:NEW."SKILLS", :OLD."SKILLS") <> 0
OR COALESCE(:NEW."MEMORY_WRITE_NODE", '-') <> COALESCE(:OLD."MEMORY_WRITE_NODE", '-')) THEN
RAISE_APPLICATION_ERROR(-20045, 'a published profile version is immutable; a change is a new version (Agents § 0.4)');
END IF;
IF :NEW."STATE" = 'published' AND :OLD."STATE" <> 'published' THEN
SELECT COUNT(*) INTO v_passed FROM "SRD_SUPPORT"."GOVERNANCE_EVENTS"
WHERE "SUBJECT_TYPE" = 'profile' AND "SUBJECT_KEY" = :NEW."PROFILE_KEY" AND "SUBJECT_VERSION" = :NEW."VERSION" AND "EVENT" = 'evaluated' AND "RESULT" = 'PASSED';
SELECT COUNT(*) INTO v_approved FROM "SRD_SUPPORT"."GOVERNANCE_EVENTS"
WHERE "SUBJECT_TYPE" = 'profile' AND "SUBJECT_KEY" = :NEW."PROFILE_KEY" AND "SUBJECT_VERSION" = :NEW."VERSION" AND "EVENT" = 'approved';
IF v_passed = 0 OR v_approved = 0 THEN
RAISE_APPLICATION_ERROR(-20046, 'publishing a profile needs a PASSED evaluation and an approval recorded in GOVERNANCE_EVENTS');
END IF;
SELECT COUNT(*) INTO v_red FROM "SRD_SUPPORT"."EVAL_SETS" WHERE "ID" = :NEW."EVAL_SET_ID" AND "LAST_RESULT" = 'red';
IF v_red > 0 THEN
RAISE_APPLICATION_ERROR(-20047, 'nothing publishes with a red eval set (Agent Framework § 6.5)');
END IF;
-- the data boundary is code at the route selector (Agents § 0.3): a Support profile binds only an EU, DPA-covered route
SELECT "DATA_ZONE", "DPA_COVERED" INTO v_zone, v_dpa FROM "SRD_SUPPORT"."MODEL_ROUTES" WHERE "ID" = :NEW."MODEL_ROUTE_ID";
IF :NEW."MODULE" = 'support' AND (v_zone <> 'EU' OR v_dpa <> 1) THEN
RAISE_APPLICATION_ERROR(-20048, 'a Support profile may only publish with an EU-resident, DPA-covered route (SG-9)');
END IF;
:NEW."PUBLISHED_ON_UTC" := COALESCE(:NEW."PUBLISHED_ON_UTC", SYS_EXTRACT_UTC(SYSTIMESTAMP));
END IF;
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."MODEL_ROUTES_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."MODEL_ROUTES"
FOR EACH ROW
BEGIN
IF :OLD."STATE" IN ('published','retired') AND (:NEW."PROVIDER_CODE" <> :OLD."PROVIDER_CODE" OR :NEW."DEPLOYMENT_NAME" <> :OLD."DEPLOYMENT_NAME"
OR :NEW."MODEL_NAME" <> :OLD."MODEL_NAME" OR :NEW."DATA_ZONE" <> :OLD."DATA_ZONE" OR :NEW."DPA_COVERED" <> :OLD."DPA_COVERED"
OR :NEW."ENDPOINT_REF" <> :OLD."ENDPOINT_REF") THEN
RAISE_APPLICATION_ERROR(-20049, 'a published model route is immutable; a route change is a governed decision per profile (Software Architecture § 7.1)');
END IF;
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."MEMORY_ARTICLES_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."MEMORY_ARTICLES"
FOR EACH ROW
BEGIN
IF :OLD."STATE" IN ('active','retired') AND (:NEW."GIT_SHA" <> :OLD."GIT_SHA" OR :NEW."CONTENT_HASH" <> :OLD."CONTENT_HASH") THEN
RAISE_APPLICATION_ERROR(-20050, 'an active article is immutable; a change is a new version (Agents Memory § 7.4b)');
END IF;
IF :OLD."STATE" = 'retired' AND :NEW."STATE" <> 'retired' THEN
RAISE_APPLICATION_ERROR(-20051, 'a retired article does not come back');
END IF;
END;
/
-- ------------------------------------------------------------------ write shapes (Gating § 4, § 7; D133)
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."WRITE_SHAPES_GOV_TRG"
BEFORE UPDATE ON "SRD_SUPPORT"."WRITE_SHAPES"
FOR EACH ROW
BEGIN
IF :OLD."STATE" IN ('approved','suspended','retired') AND (
:NEW."TEMPLATE_HASH" <> :OLD."TEMPLATE_HASH" OR DBMS_LOB.COMPARE(:NEW."TEMPLATE", :OLD."TEMPLATE") <> 0
OR DBMS_LOB.COMPARE(:NEW."TEARDOWN_TEMPLATE", :OLD."TEARDOWN_TEMPLATE") <> 0 OR DBMS_LOB.COMPARE(:NEW."ASSERTIONS", :OLD."ASSERTIONS") <> 0
OR DBMS_LOB.COMPARE(:NEW."CASE_TYPES", :OLD."CASE_TYPES") <> 0 OR :NEW."EXPECTED_COUNTS_EXPR" <> :OLD."EXPECTED_COUNTS_EXPR"
OR :NEW."TARGET_CLASS" <> :OLD."TARGET_CLASS" OR :NEW."EFFECT_CLASS" <> :OLD."EFFECT_CLASS" OR :NEW."WRITE_CLASS" <> :OLD."WRITE_CLASS"
OR :NEW."OPERATION_ID" <> :OLD."OPERATION_ID") THEN
RAISE_APPLICATION_ERROR(-20060, 'an approved write shape is immutable; a changed template is a new shape needing its own approval');
END IF;
IF :NEW."STATE" = 'approved' AND :OLD."STATE" = 'draft' THEN
IF :NEW."APPROVED_BY_DECISION_ID" IS NULL THEN
RAISE_APPLICATION_ERROR(-20061, 'a shape is approved only by an HW-approve GATE_DECISION');
END IF;
IF :NEW."CLEAN_INSTANCES" < 3 THEN
RAISE_APPLICATION_ERROR(-20062, 'a shape needs three consecutive clean, person-confirmed instances before approval (Gating § 9.3)');
END IF;
:NEW."APPROVED_ON_UTC" := COALESCE(:NEW."APPROVED_ON_UTC", SYS_EXTRACT_UTC(SYSTIMESTAMP));
END IF;
IF :OLD."STATE" = 'suspended' AND :NEW."STATE" = 'approved' AND :NEW."APPROVED_BY_DECISION_ID" = :OLD."APPROVED_BY_DECISION_ID" THEN
RAISE_APPLICATION_ERROR(-20063, 'a suspended shape returns only through a new HW-approve decision (Gating § 7)');
END IF;
-- the expiry default is a policy value (writes.shape_expiry_default); the approver may extend ONCE, by at most one day
IF :NEW."EXTENDED_UNTIL_UTC" IS NOT NULL AND :OLD."EXTENDED_UNTIL_UTC" IS NOT NULL AND :NEW."EXTENDED_UNTIL_UTC" <> :OLD."EXTENDED_UNTIL_UTC" THEN
RAISE_APPLICATION_ERROR(-20064, 'a write shape expiry may be extended once; a longer life is a re-approval (D133)');
END IF;
IF :NEW."EXTENDED_UNTIL_UTC" IS NOT NULL AND :OLD."EXTENDED_UNTIL_UTC" IS NULL AND (:NEW."EXTENDED_BY" IS NULL OR :NEW."EXTENDED_ON_UTC" IS NULL) THEN
RAISE_APPLICATION_ERROR(-20065, 'an extension names the approver and the time');
END IF;
END;
/
-- ------------------------------------------------------------------ data boundary
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."CASE_HANDLES_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."CASE_HANDLES"
BEGIN
RAISE_APPLICATION_ERROR(-20070, 'CASE_HANDLES are never rewritten; destruction is the key (CASE_KEYS.DESTROYED_ON_UTC), which shreds every value');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."CASE_KEYS_GOV_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."CASE_KEYS"
FOR EACH ROW
BEGIN
IF DELETING OR :OLD."DESTROYED_ON_UTC" IS NOT NULL OR :NEW."WRAPPED_DATA_KEY" <> :OLD."WRAPPED_DATA_KEY" THEN
RAISE_APPLICATION_ERROR(-20071, 'a case data key may only be destroyed, once');
END IF;
END;
/