AISA v2.0 / Technical documentation / 010-runtime-execution.sql
010-runtime-execution.sql
SQL · 306 lines · 23,310 bytes · wiki path 10 Architecture/db/SrdSupport/010-runtime-execution.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 · 008-immutability-and-payload-guards.sql · 009-seed-platform-policy.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
-- 010: proposed runtime storage contract; never executed by the documentation build.
-- Agent Runtime sections 2, 4, 5 and 7; Model Execution; Modules.
-- Existing installations with hash-only checkpoints need an explicit state migration:
-- this upgrade refuses to manufacture missing checkpoint bytes.
DECLARE
PROCEDURE ddl(p_sql IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_sql;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE NOT IN (-955, -1408, -1430, -1442, -1451, -2260, -2261, -2264, -2275) THEN RAISE; END IF;
END;
PROCEDURE drop_constraint(p_table IN VARCHAR2, p_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE "SRD_SUPPORT"."' || p_table || '" DROP CONSTRAINT "' || p_name || '"';
EXCEPTION WHEN OTHERS THEN IF SQLCODE <> -2443 THEN RAISE; END IF;
END;
BEGIN
ddl('ALTER TABLE "SRD_SUPPORT"."TASKS" ADD ("LEASE_OWNER" NVARCHAR2(128), "LEASE_UNTIL_UTC" TIMESTAMP(6))');
ddl('ALTER TABLE "SRD_SUPPORT"."TURN_CHECKPOINTS" ADD ("STATE_PAPER_ID" NVARCHAR2(64))');
ddl('ALTER TABLE "SRD_SUPPORT"."TURN_CHECKPOINTS" MODIFY ("STATE_PAPER_ID" NOT NULL)');
ddl('ALTER TABLE "SRD_SUPPORT"."TURN_CHECKPOINTS" ADD CONSTRAINT "FK_TURN_STATE_PAPER" FOREIGN KEY ("STATE_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID")');
ddl(q'~CREATE TABLE "SRD_SUPPORT"."RUNTIME_CALLS" (
"ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"TASK_ID" NVARCHAR2(64) NOT NULL,
"TURN_NO" NUMBER(10) NOT NULL,
"CALL_ORDINAL" NUMBER(10) NOT NULL,
"KIND" NVARCHAR2(8) NOT NULL,
"OPERATION_KEY" NVARCHAR2(256) NOT NULL,
"OPERATION_VERSION" NVARCHAR2(128) NOT NULL,
"IDEMPOTENCY_KEY" NVARCHAR2(256) NOT NULL,
"REQUEST_HASH" NVARCHAR2(64) NOT NULL,
"REQUEST_PAPER_ID" NVARCHAR2(64) NOT NULL,
"RESULT_PAPER_ID" NVARCHAR2(64),
"EFFECT_CLASS" NVARCHAR2(16) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"LANDED_STATE" NVARCHAR2(24),
"GRANT_ID" NVARCHAR2(64),
"FENCE_EPOCH" NUMBER(19) NOT NULL,
"ATTEMPT_NO" NUMBER(10) DEFAULT 0 NOT NULL,
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"DISPATCHED_ON_UTC" TIMESTAMP(6),
"ENDED_ON_UTC" TIMESTAMP(6),
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_RUNTIME_CALLS" PRIMARY KEY ("ID"),
CONSTRAINT "UK_RUNTIME_CALL_SLOT" UNIQUE ("TASK_ID", "TURN_NO", "CALL_ORDINAL"),
CONSTRAINT "UK_RUNTIME_CALL_KEY" UNIQUE ("IDEMPOTENCY_KEY"),
CONSTRAINT "FK_RCALL_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "FK_RCALL_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
CONSTRAINT "FK_RCALL_REQUEST" FOREIGN KEY ("REQUEST_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID"),
CONSTRAINT "FK_RCALL_RESULT" FOREIGN KEY ("RESULT_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID"),
CONSTRAINT "FK_RCALL_GRANT" FOREIGN KEY ("GRANT_ID") REFERENCES "SRD_SUPPORT"."GRANTS" ("ID"),
CONSTRAINT "CK_RCALL_GRANTED" CHECK ("EFFECT_CLASS" IN ('none','read') OR "STATE" IN ('prepared','refused') OR "GRANT_ID" IS NOT NULL),
CONSTRAINT "CK_RCALL_KIND" CHECK ("KIND" IN ('model','tool')),
CONSTRAINT "CK_RCALL_EFFECT" CHECK ("EFFECT_CLASS" IN ('none','read','transactional','compensable','irreversible','ddl')),
CONSTRAINT "CK_RCALL_STATE" CHECK ("STATE" IN ('prepared','dispatched','succeeded','refused','failed','unknown')),
CONSTRAINT "CK_RCALL_LANDED" CHECK ("LANDED_STATE" IS NULL OR "LANDED_STATE" IN ('applied','not_applied','partly_applied','unknown')),
CONSTRAINT "CK_RCALL_SLOT" CHECK ("TURN_NO" > 0 AND "CALL_ORDINAL" >= 0 AND "ATTEMPT_NO" >= 0),
CONSTRAINT "CK_RCALL_SUCCESS" CHECK ("STATE" <> 'succeeded' OR ("RESULT_PAPER_ID" IS NOT NULL AND "ENDED_ON_UTC" IS NOT NULL)),
CONSTRAINT "CK_RCALL_DISPATCH" CHECK ("STATE" NOT IN ('dispatched','unknown','succeeded') OR "DISPATCHED_ON_UTC" IS NOT NULL)
)~');
ddl('CREATE INDEX "SRD_SUPPORT"."IX_RCALL_RECOVER" ON "SRD_SUPPORT"."RUNTIME_CALLS" ("TASK_ID", "STATE")');
ddl('ALTER TABLE "SRD_SUPPORT"."GRANTS" ADD ("CONSUMED_BY_CALL_ID" NVARCHAR2(64))');
ddl('ALTER TABLE "SRD_SUPPORT"."GRANTS" ADD CONSTRAINT "FK_GRANT_CONSUMED_CALL" FOREIGN KEY ("CONSUMED_BY_CALL_ID") REFERENCES "SRD_SUPPORT"."RUNTIME_CALLS" ("ID")');
ddl(q'~CREATE TABLE "SRD_SUPPORT"."INBOX_MESSAGES" (
"MESSAGE_ID" NVARCHAR2(64) NOT NULL,
"PAYLOAD_HASH" NVARCHAR2(64) NOT NULL,
"RECIPIENT_TASK_ID" NVARCHAR2(64),
"CONSUMER_KEY" NVARCHAR2(256) NOT NULL,
"RESPONSE_JSON" CLOB,
"DISPOSITION" NVARCHAR2(16) NOT NULL,
"RESULT_PAPER_ID" NVARCHAR2(64),
"APPLIED_ON_UTC" TIMESTAMP(6) NOT NULL,
CONSTRAINT "PK_INBOX_MESSAGES" PRIMARY KEY ("MESSAGE_ID"),
CONSTRAINT "FK_INBOX_TASK" FOREIGN KEY ("RECIPIENT_TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
CONSTRAINT "FK_INBOX_RESULT" FOREIGN KEY ("RESULT_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID"),
CONSTRAINT "CK_INBOX_RESPONSE_JSON" CHECK ("RESPONSE_JSON" IS NULL OR "RESPONSE_JSON" IS JSON),
CONSTRAINT "CK_INBOX_DISPOSITION" CHECK ("DISPOSITION" IN ('applied','refused'))
)~');
ddl(q'~CREATE TABLE "SRD_SUPPORT"."IMPACT_LOCKS" (
"IMPACT_KEY" NVARCHAR2(512) NOT NULL,
"CALL_ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"FENCE_EPOCH" NUMBER(19) NOT NULL,
"ACQUIRED_ON_UTC" TIMESTAMP(6) NOT NULL,
CONSTRAINT "PK_IMPACT_LOCKS" PRIMARY KEY ("IMPACT_KEY"),
CONSTRAINT "FK_ILOCK_CALL" FOREIGN KEY ("CALL_ID") REFERENCES "SRD_SUPPORT"."RUNTIME_CALLS" ("ID"),
CONSTRAINT "FK_ILOCK_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID")
)~');
drop_constraint('CASES', 'CK_CASES_PARK');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."CASES" ADD CONSTRAINT "CK_CASES_PARK_V2" CHECK ("PARK_REASON" IS NULL OR "PARK_REASON" IN ('question','gate','sub_case','connector_down','provider_down','budget','capability_gap','external_job','effect_unknown'))~');
drop_constraint('TASKS', 'CK_TASKS_PARK');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."TASKS" ADD CONSTRAINT "CK_TASKS_PARK_V2" CHECK ("PARK_REASON" IS NULL OR "PARK_REASON" IN ('question','gate','sub_case','connector_down','provider_down','budget','capability_gap','external_job','effect_unknown'))~');
-- A mechanical profile has a registered code handler, not a fictitious LLM route.
ddl(q'~ALTER TABLE "SRD_SUPPORT"."PROFILES" ADD ("EXECUTION_KIND" NVARCHAR2(16) DEFAULT 'llm' NOT NULL, "HANDLER_KEY" NVARCHAR2(128))~');
ddl('ALTER TABLE "SRD_SUPPORT"."PROFILES" MODIFY ("PROMPT_ID" NULL, "MODEL_ROUTE_ID" NULL)');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."PROFILES" ADD CONSTRAINT "CK_PROFILE_EXEC_KIND" CHECK (
("EXECUTION_KIND" = 'llm' AND "PROMPT_ID" IS NOT NULL AND "MODEL_ROUTE_ID" IS NOT NULL AND "HANDLER_KEY" IS NULL)
OR ("EXECUTION_KIND" = 'deterministic' AND "PROMPT_ID" IS NULL AND "MODEL_ROUTE_ID" IS NULL AND "HANDLER_KEY" IS NOT NULL))~');
ddl('ALTER TABLE "SRD_SUPPORT"."CONNECTOR_SCOPES" ADD ("EXECUTION_FORM" NVARCHAR2(1))');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."CONNECTOR_SCOPES" ADD CONSTRAINT "CK_SCOPE_EXEC_FORM" CHECK ("EXECUTION_FORM" IS NULL OR "EXECUTION_FORM" IN ('A','B','E'))~');
drop_constraint('CONNECTOR_SCOPES', 'CK_SCOPES_CONNECTOR');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."CONNECTOR_SCOPES" ADD CONSTRAINT "CK_SCOPE_CONNECTOR_KEY" CHECK (TRIM("CONNECTOR") IS NOT NULL)~');
-- Eligibility is representable for every class; no risk acceptance is seeded.
drop_constraint('RISK_ACCEPTANCES', 'CK_RISK_WCLASS');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."RISK_ACCEPTANCES" ADD CONSTRAINT "CK_RISK_WCLASS_V2" CHECK ("WRITE_CLASS" IN ('W1','W2','W3','W4','W5','W6','W7'))~');
ddl('ALTER TABLE "SRD_SUPPORT"."GATE_DECISIONS" ADD ("DECISION_POLICY_REF" NVARCHAR2(256))');
drop_constraint('GATE_DECISIONS', 'CK_GDEC_POLICY_SHAPE');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."GATE_DECISIONS" ADD CONSTRAINT "CK_GDEC_POLICY_REF_V2" CHECK ("ACTOR_KIND" <> 'policy' OR "DECISION_POLICY_REF" IS NOT NULL)~');
drop_constraint('AUDIT_LEDGER', 'CK_LEDGER_WRITE_COUNT');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."AUDIT_LEDGER" ADD CONSTRAINT "CK_LEDGER_WRITE_COUNT_V2" CHECK ("KIND" <> 'WRITE_EXECUTED' OR "ROW_COUNT" IS NOT NULL OR "MODE" IN ('send','ddl'))~');
ddl('CREATE UNIQUE INDEX "SRD_SUPPORT"."UX_COST_PROVIDER_RECORD" ON "SRD_SUPPORT"."COST_LEDGER" ("LEDGER_RECORD_ID")');
-- Missing provider measurements remain unknown, never fabricated as zero.
ddl('ALTER TABLE "SRD_SUPPORT"."COST_LEDGER" MODIFY ("INPUT_TOKENS" NULL, "OUTPUT_TOKENS" NULL, "CACHED_TOKENS" NULL)');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."COST_LEDGER" ADD ("USAGE_STATUS" NVARCHAR2(16) DEFAULT 'measured' NOT NULL)~');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."COST_LEDGER" ADD CONSTRAINT "CK_COST_USAGE_STATUS" CHECK ("USAGE_STATUS" IN ('measured','partial','unavailable'))~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."RUNTIME_CALLS" IS 'Durable model/tool intent and outcome. Request identity is immutable; an uncertain effect is reconciled before retry. Result bytes are immutable PAPERS revisions. Attempt evidence is appended to AUDIT_LEDGER.'~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."INBOX_MESSAGES" IS 'Deduplication for messages and HTTP commands, scoped by consumer. Commit key/hash, response reference and state transition together; unequal replay bytes are refused.'~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."IMPACT_LOCKS" IS 'Current platform execution claims, acquired in sorted order. Not released merely because a worker lease expires; unknown target outcomes remain held. External authors are outside this lock.'~');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."RUNTIME_CALLS_GOV_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."RUNTIME_CALLS"
FOR EACH ROW
BEGIN
IF DELETING OR :NEW."ID" <> :OLD."ID" OR :NEW."TASK_ID" <> :OLD."TASK_ID"
OR :NEW."CASE_ID" <> :OLD."CASE_ID" OR :NEW."TURN_NO" <> :OLD."TURN_NO"
OR :NEW."CALL_ORDINAL" <> :OLD."CALL_ORDINAL" OR :NEW."KIND" <> :OLD."KIND"
OR :NEW."REQUEST_HASH" <> :OLD."REQUEST_HASH" OR :NEW."REQUEST_PAPER_ID" <> :OLD."REQUEST_PAPER_ID"
OR :NEW."IDEMPOTENCY_KEY" <> :OLD."IDEMPOTENCY_KEY" OR :NEW."OPERATION_KEY" <> :OLD."OPERATION_KEY"
OR :NEW."OPERATION_VERSION" <> :OLD."OPERATION_VERSION" OR :NEW."EFFECT_CLASS" <> :OLD."EFFECT_CLASS" THEN
RAISE_APPLICATION_ERROR(-20080, 'runtime call request identity is immutable');
END IF;
IF :OLD."STATE" IN ('succeeded','refused') THEN
RAISE_APPLICATION_ERROR(-20081, 'terminal runtime call evidence is immutable; record a new corrective action');
END IF;
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."INBOX_MESSAGES_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."INBOX_MESSAGES"
BEGIN
RAISE_APPLICATION_ERROR(-20082, 'inbox message dispositions are immutable');
END;
/
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."TURN_CHECKPOINTS_IMM_TRG"
BEFORE UPDATE OR DELETE ON "SRD_SUPPORT"."TURN_CHECKPOINTS"
BEGIN
RAISE_APPLICATION_ERROR(-20083, 'checkpoint revisions are immutable');
END;
/
-- Replace the 008 profile guard to support deterministic profiles without model rows.
CREATE OR REPLACE TRIGGER "SRD_SUPPORT"."PROFILES_GOV_TRG"
BEFORE INSERT OR UPDATE OR DELETE ON "SRD_SUPPORT"."PROFILES"
FOR EACH ROW
DECLARE
v_passed INTEGER; v_approved INTEGER; v_green INTEGER;
v_zone NVARCHAR2(16); v_dpa NUMBER(1);
BEGIN
IF DELETING THEN
RAISE_APPLICATION_ERROR(-20084, 'retire a profile version; do not delete it');
END IF;
IF INSERTING AND :NEW."STATE" <> 'draft' THEN
RAISE_APPLICATION_ERROR(-20085, 'a profile begins as draft and publishes through governance');
END IF;
IF UPDATING AND :OLD."STATE" IN ('published','retired') AND (
:NEW."CONTENT_HASH" <> :OLD."CONTENT_HASH"
OR COALESCE(:NEW."PROMPT_ID", '-') <> COALESCE(:OLD."PROMPT_ID", '-')
OR COALESCE(:NEW."MODEL_ROUTE_ID", '-') <> COALESCE(:OLD."MODEL_ROUTE_ID", '-')
OR :NEW."EXECUTION_KIND" <> :OLD."EXECUTION_KIND"
OR COALESCE(:NEW."HANDLER_KEY", '-') <> COALESCE(:OLD."HANDLER_KEY", '-')
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 definition is immutable');
END IF;
IF UPDATING AND :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';
SELECT COUNT(*) INTO v_green FROM "SRD_SUPPORT"."EVAL_SETS"
WHERE "ID" = :NEW."EVAL_SET_ID" AND "LAST_RESULT" = 'green';
IF v_passed = 0 OR v_approved = 0 OR v_green = 0 THEN
RAISE_APPLICATION_ERROR(-20046, 'publication requires passed evaluation, approval and a green eval set');
END IF;
IF :NEW."EXECUTION_KIND" = 'llm' THEN
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 model profile requires an EU DPA-covered route');
END IF;
END IF;
:NEW."PUBLISHED_ON_UTC" := COALESCE(:NEW."PUBLISHED_ON_UTC", SYS_EXTRACT_UTC(SYSTIMESTAMP));
END IF;
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."WITHDRAWN_ON_UTC" IS NULL OR :NEW."WITHDRAWN_BY" IS NULL
OR (:NEW."ID" <> :OLD."ID" OR (:NEW."ID" IS NULL AND :OLD."ID" IS NOT NULL) OR (:NEW."ID" IS NOT NULL AND :OLD."ID" IS NULL))
OR (:NEW."CUSTOMER_CODE" <> :OLD."CUSTOMER_CODE" OR (:NEW."CUSTOMER_CODE" IS NULL AND :OLD."CUSTOMER_CODE" IS NOT NULL) OR (:NEW."CUSTOMER_CODE" IS NOT NULL AND :OLD."CUSTOMER_CODE" IS NULL))
OR (:NEW."MODULE" <> :OLD."MODULE" OR (:NEW."MODULE" IS NULL AND :OLD."MODULE" IS NOT NULL) OR (:NEW."MODULE" IS NOT NULL AND :OLD."MODULE" IS NULL))
OR (:NEW."CASE_TYPE" <> :OLD."CASE_TYPE" OR (:NEW."CASE_TYPE" IS NULL AND :OLD."CASE_TYPE" IS NOT NULL) OR (:NEW."CASE_TYPE" IS NOT NULL AND :OLD."CASE_TYPE" IS NULL))
OR (:NEW."GATE_KIND" <> :OLD."GATE_KIND" OR (:NEW."GATE_KIND" IS NULL AND :OLD."GATE_KIND" IS NOT NULL) OR (:NEW."GATE_KIND" IS NOT NULL AND :OLD."GATE_KIND" IS NULL))
OR (:NEW."TARGET_CLASS" <> :OLD."TARGET_CLASS" OR (:NEW."TARGET_CLASS" IS NULL AND :OLD."TARGET_CLASS" IS NOT NULL) OR (:NEW."TARGET_CLASS" IS NOT NULL AND :OLD."TARGET_CLASS" IS NULL))
OR (:NEW."WRITE_CLASS" <> :OLD."WRITE_CLASS" OR (:NEW."WRITE_CLASS" IS NULL AND :OLD."WRITE_CLASS" IS NOT NULL) OR (:NEW."WRITE_CLASS" IS NOT NULL AND :OLD."WRITE_CLASS" IS NULL))
OR (:NEW."ACCEPTED_BY" <> :OLD."ACCEPTED_BY" OR (:NEW."ACCEPTED_BY" IS NULL AND :OLD."ACCEPTED_BY" IS NOT NULL) OR (:NEW."ACCEPTED_BY" IS NOT NULL AND :OLD."ACCEPTED_BY" IS NULL))
OR (:NEW."ACCEPTED_ROLE" <> :OLD."ACCEPTED_ROLE" OR (:NEW."ACCEPTED_ROLE" IS NULL AND :OLD."ACCEPTED_ROLE" IS NOT NULL) OR (:NEW."ACCEPTED_ROLE" IS NOT NULL AND :OLD."ACCEPTED_ROLE" IS NULL))
OR (:NEW."DECISION_REF" <> :OLD."DECISION_REF" OR (:NEW."DECISION_REF" IS NULL AND :OLD."DECISION_REF" IS NOT NULL) OR (:NEW."DECISION_REF" IS NOT NULL AND :OLD."DECISION_REF" IS NULL))
OR (:NEW."ACCEPTED_ON_UTC" <> :OLD."ACCEPTED_ON_UTC" OR (:NEW."ACCEPTED_ON_UTC" IS NULL AND :OLD."ACCEPTED_ON_UTC" IS NOT NULL) OR (:NEW."ACCEPTED_ON_UTC" IS NOT NULL AND :OLD."ACCEPTED_ON_UTC" IS NULL))
OR (:NEW."LEDGER_RECORD_ID" <> :OLD."LEDGER_RECORD_ID" OR (:NEW."LEDGER_RECORD_ID" IS NULL AND :OLD."LEDGER_RECORD_ID" IS NOT NULL) OR (:NEW."LEDGER_RECORD_ID" IS NOT NULL AND :OLD."LEDGER_RECORD_ID" IS NULL)) THEN
RAISE_APPLICATION_ERROR(-20022, 'RISK_ACCEPTANCES may only be withdrawn/revoked once; its authority scope is immutable');
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."REVOKED_ON_UTC" IS NULL OR :NEW."REVOKED_BY" IS NULL
OR (:NEW."ID" <> :OLD."ID" OR (:NEW."ID" IS NULL AND :OLD."ID" IS NOT NULL) OR (:NEW."ID" IS NOT NULL AND :OLD."ID" IS NULL))
OR (:NEW."SUBJECT_ID" <> :OLD."SUBJECT_ID" OR (:NEW."SUBJECT_ID" IS NULL AND :OLD."SUBJECT_ID" IS NOT NULL) OR (:NEW."SUBJECT_ID" IS NOT NULL AND :OLD."SUBJECT_ID" IS NULL))
OR (:NEW."SUBJECT_KIND" <> :OLD."SUBJECT_KIND" OR (:NEW."SUBJECT_KIND" IS NULL AND :OLD."SUBJECT_KIND" IS NOT NULL) OR (:NEW."SUBJECT_KIND" IS NOT NULL AND :OLD."SUBJECT_KIND" IS NULL))
OR (:NEW."CUSTOMER_CODE" <> :OLD."CUSTOMER_CODE" OR (:NEW."CUSTOMER_CODE" IS NULL AND :OLD."CUSTOMER_CODE" IS NOT NULL) OR (:NEW."CUSTOMER_CODE" IS NOT NULL AND :OLD."CUSTOMER_CODE" IS NULL))
OR (:NEW."ROLE" <> :OLD."ROLE" OR (:NEW."ROLE" IS NULL AND :OLD."ROLE" IS NOT NULL) OR (:NEW."ROLE" IS NOT NULL AND :OLD."ROLE" IS NULL))
OR (:NEW."SCOPE_MODULE" <> :OLD."SCOPE_MODULE" OR (:NEW."SCOPE_MODULE" IS NULL AND :OLD."SCOPE_MODULE" IS NOT NULL) OR (:NEW."SCOPE_MODULE" IS NOT NULL AND :OLD."SCOPE_MODULE" IS NULL))
OR (:NEW."SCOPE_STAGE" <> :OLD."SCOPE_STAGE" OR (:NEW."SCOPE_STAGE" IS NULL AND :OLD."SCOPE_STAGE" IS NOT NULL) OR (:NEW."SCOPE_STAGE" IS NOT NULL AND :OLD."SCOPE_STAGE" IS NULL))
OR (:NEW."SCOPE_TARGET_CLASS" <> :OLD."SCOPE_TARGET_CLASS" OR (:NEW."SCOPE_TARGET_CLASS" IS NULL AND :OLD."SCOPE_TARGET_CLASS" IS NOT NULL) OR (:NEW."SCOPE_TARGET_CLASS" IS NOT NULL AND :OLD."SCOPE_TARGET_CLASS" IS NULL))
OR (:NEW."SCOPE_SYSTEM" <> :OLD."SCOPE_SYSTEM" OR (:NEW."SCOPE_SYSTEM" IS NULL AND :OLD."SCOPE_SYSTEM" IS NOT NULL) OR (:NEW."SCOPE_SYSTEM" IS NOT NULL AND :OLD."SCOPE_SYSTEM" IS NULL))
OR (:NEW."SOURCE" <> :OLD."SOURCE" OR (:NEW."SOURCE" IS NULL AND :OLD."SOURCE" IS NOT NULL) OR (:NEW."SOURCE" IS NOT NULL AND :OLD."SOURCE" IS NULL))
OR (:NEW."SOURCE_REF" <> :OLD."SOURCE_REF" OR (:NEW."SOURCE_REF" IS NULL AND :OLD."SOURCE_REF" IS NOT NULL) OR (:NEW."SOURCE_REF" IS NOT NULL AND :OLD."SOURCE_REF" IS NULL))
OR (:NEW."GRANTED_BY" <> :OLD."GRANTED_BY" OR (:NEW."GRANTED_BY" IS NULL AND :OLD."GRANTED_BY" IS NOT NULL) OR (:NEW."GRANTED_BY" IS NOT NULL AND :OLD."GRANTED_BY" IS NULL))
OR (:NEW."GRANTED_ON_UTC" <> :OLD."GRANTED_ON_UTC" OR (:NEW."GRANTED_ON_UTC" IS NULL AND :OLD."GRANTED_ON_UTC" IS NOT NULL) OR (:NEW."GRANTED_ON_UTC" IS NOT NULL AND :OLD."GRANTED_ON_UTC" IS NULL))
OR (:NEW."LEDGER_RECORD_ID" <> :OLD."LEDGER_RECORD_ID" OR (:NEW."LEDGER_RECORD_ID" IS NULL AND :OLD."LEDGER_RECORD_ID" IS NOT NULL) OR (:NEW."LEDGER_RECORD_ID" IS NOT NULL AND :OLD."LEDGER_RECORD_ID" IS NULL)) THEN
RAISE_APPLICATION_ERROR(-20023, 'ROLE_ASSIGNMENTS may only be withdrawn/revoked once; its authority scope is immutable');
END IF;
END;
/
-- Preserve scope, parameter and expiry immutability on re-approval as well.
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"
OR DBMS_LOB.COMPARE(:NEW."PARAMETERS", :OLD."PARAMETERS") <> 0
OR :NEW."SHAPE_KEY" <> :OLD."SHAPE_KEY" OR :NEW."VERSION" <> :OLD."VERSION"
OR :NEW."CUSTOMER_CODE" <> :OLD."CUSTOMER_CODE" OR :NEW."MODULE" <> :OLD."MODULE"
OR :NEW."CONNECTOR" <> :OLD."CONNECTOR"
OR (:NEW."EXPIRES_ON_UTC" <> :OLD."EXPIRES_ON_UTC"
OR (:NEW."EXPIRES_ON_UTC" IS NULL AND :OLD."EXPIRES_ON_UTC" IS NOT NULL)
OR (:NEW."EXPIRES_ON_UTC" IS NOT NULL AND :OLD."EXPIRES_ON_UTC" IS NULL))) 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" <> 'approved' 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;
/