AISA v2.0 / Technical documentation / 007-policy-and-operations.sql
007-policy-and-operations.sql
SQL · 156 lines · 11,977 bytes · wiki path 10 Architecture/db/SrdSupport/007-policy-and-operations.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 · 008-immutability-and-payload-guards.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
-- 007 · POLICY and OPERATIONS — Whitelabel Catalogue (every value with a default, as rows — CR-10, D51, D67);
-- Whitelabel Catalogue § 6 (case types); Trust and Data § 3 (roles held as rows — Software Architecture § 10.7 row 1);
-- Software Architecture § 4 (RabbitMQ.Outbox: "our SRD_SUPPORT supplies the rows"); Delivery § 4 (retention evaluator, D109).
DECLARE
PROCEDURE ddl(p_sql IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_sql;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE NOT IN (-955, -1408, -1430, -2260, -2261, -2264, -2275) THEN RAISE; END IF;
END;
BEGIN
-- ---------------------------------------------------------------- POLICY_VALUES (the Whitelabel Catalogue as rows)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."POLICY_VALUES" (
"ID" NVARCHAR2(64) NOT NULL,
"SCOPE" NVARCHAR2(16) NOT NULL,
"CUSTOMER_CODE" NVARCHAR2(32) DEFAULT '*' NOT NULL,
"MODULE" NVARCHAR2(16) DEFAULT '*' NOT NULL,
"CASE_TYPE" NVARCHAR2(64) DEFAULT '*' NOT NULL,
"PROFILE_KEY" NVARCHAR2(128) DEFAULT '*' NOT NULL,
"KEY" NVARCHAR2(128) NOT NULL,
"VERSION" NUMBER(10) NOT NULL,
"VALUE" CLOB NOT NULL,
"VALUE_TYPE" NVARCHAR2(16) NOT NULL,
"CATALOGUE_SECTION" NVARCHAR2(64),
"STATE" NVARCHAR2(16) NOT NULL,
"SET_BY" NVARCHAR2(128) NOT NULL,
"PUBLISHED_ON_UTC" TIMESTAMP(6),
"RETIRED_ON_UTC" TIMESTAMP(6),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
CONSTRAINT "PK_POLICY_VALUES" PRIMARY KEY ("ID"),
CONSTRAINT "UK_POLICY_VALUES" UNIQUE ("SCOPE", "CUSTOMER_CODE", "MODULE", "CASE_TYPE", "PROFILE_KEY", "KEY", "VERSION"),
CONSTRAINT "CK_POLICY_SCOPE" CHECK ("SCOPE" IN ('platform','customer','case_type','profile')),
CONSTRAINT "CK_POLICY_TYPE" CHECK ("VALUE_TYPE" IN ('number','string','boolean','duration','json')),
CONSTRAINT "CK_POLICY_STATE" CHECK ("STATE" IN ('draft','published','retired')),
CONSTRAINT "CK_POLICY_VALUE_JSON" CHECK ("VALUE" IS JSON),
CONSTRAINT "CK_POLICY_PLATFORM_STAR" CHECK ("SCOPE" <> 'platform' OR ("CUSTOMER_CODE" = '*' AND "MODULE" = '*' AND "CASE_TYPE" = '*' AND "PROFILE_KEY" = '*'))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_POLICY_KEY" ON "SRD_SUPPORT"."POLICY_VALUES" ("KEY", "STATE", "SCOPE")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."POLICY_VALUES" IS 'One row per Whitelabel Catalogue key, version and scope. Resolution order at case open: profile > case_type > customer > platform (the most specific published row wins; ''*'' = any). VALUE is always JSON (a scalar is a JSON scalar). A customer value is a governed change of the plug-in, not a code release (Catalogue § 7); the seed of the platform defaults is 009.'~');
-- ---------------------------------------------------------------- CASE_TYPES
ddl(q'~CREATE TABLE "SRD_SUPPORT"."CASE_TYPES" (
"ID" NVARCHAR2(64) NOT NULL,
"CUSTOMER_CODE" NVARCHAR2(32) NOT NULL,
"MODULE" NVARCHAR2(16) NOT NULL,
"CASE_TYPE" NVARCHAR2(64) NOT NULL,
"VERSION" NUMBER(10) NOT NULL,
"INITIAL_GRANTS" CLOB NOT NULL,
"BUDGET_MINUTES" NUMBER(10) NOT NULL,
"SLA_CLASS" NVARCHAR2(16),
"STAGE_SET" CLOB NOT NULL,
"GATE_PLAN" CLOB NOT NULL,
"DEFAULT_ENVIRONMENT" NVARCHAR2(32) NOT NULL,
"WORKING_ENVIRONMENT" NVARCHAR2(32),
"DEPLOYMENT_PRESET" NVARCHAR2(32),
"START_FLOW" NVARCHAR2(32),
"STATE" NVARCHAR2(16) NOT NULL,
"SET_BY" NVARCHAR2(128) NOT NULL,
"PUBLISHED_ON_UTC" TIMESTAMP(6),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
CONSTRAINT "PK_CASE_TYPES" PRIMARY KEY ("ID"),
CONSTRAINT "UK_CASE_TYPES" UNIQUE ("CUSTOMER_CODE", "MODULE", "CASE_TYPE", "VERSION"),
CONSTRAINT "CK_CTYPE_MODULE" CHECK ("MODULE" IN ('configuration','support','source')),
CONSTRAINT "CK_CTYPE_STATE" CHECK ("STATE" IN ('draft','published','retired')),
CONSTRAINT "CK_CTYPE_JSON" CHECK ("INITIAL_GRANTS" IS JSON AND "STAGE_SET" IS JSON AND "GATE_PLAN" IS JSON),
CONSTRAINT "CK_CTYPE_BUDGET" CHECK ("BUDGET_MINUTES" > 0)
)~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CASE_TYPES" IS 'Whitelabel Catalogue § 6: each type carries its initial grants, its predicted delivery time in minutes (the case budget — D135), SLA class, stage set and gate plan (graphs.stage_sets, D67), and per module the working environment / deployment preset / start flow. A case copies these at open; a later change never rewrites a running case.'~');
-- ---------------------------------------------------------------- ROLE_ASSIGNMENTS (roles held as rows)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."ROLE_ASSIGNMENTS" (
"ID" NVARCHAR2(64) NOT NULL,
"SUBJECT_ID" NVARCHAR2(256) NOT NULL,
"SUBJECT_KIND" NVARCHAR2(8) NOT NULL,
"CUSTOMER_CODE" NVARCHAR2(32) NOT NULL,
"ROLE" NVARCHAR2(32) NOT NULL,
"SCOPE_MODULE" NVARCHAR2(16) DEFAULT '*' NOT NULL,
"SCOPE_STAGE" NVARCHAR2(16) DEFAULT '*' NOT NULL,
"SCOPE_TARGET_CLASS" NVARCHAR2(24) DEFAULT '*' NOT NULL,
"SCOPE_SYSTEM" NVARCHAR2(32) DEFAULT '*' NOT NULL,
"SOURCE" NVARCHAR2(16) NOT NULL,
"SOURCE_REF" NVARCHAR2(256),
"GRANTED_BY" NVARCHAR2(128) NOT NULL,
"GRANTED_ON_UTC" TIMESTAMP(6) NOT NULL,
"REVOKED_ON_UTC" TIMESTAMP(6),
"REVOKED_BY" NVARCHAR2(128),
"LEDGER_RECORD_ID" NVARCHAR2(64) NOT NULL,
CONSTRAINT "PK_ROLE_ASSIGNMENTS" PRIMARY KEY ("ID"),
CONSTRAINT "CK_ROLE_SUBJECT" CHECK ("SUBJECT_KIND" IN ('user','group')),
CONSTRAINT "CK_ROLE_ROLE" CHECK ("ROLE" IN ('viewer','operator','approver','prompt_publisher','administrator','customer_representative')),
CONSTRAINT "CK_ROLE_SOURCE" CHECK ("SOURCE" IN ('token_claim','directory_group','platform_row'))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_ROLE_SUBJECT" ON "SRD_SUPPORT"."ROLE_ASSIGNMENTS" ("SUBJECT_ID", "REVOKED_ON_UTC")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_ROLE_ROLE" ON "SRD_SUPPORT"."ROLE_ASSIGNMENTS" ("CUSTOMER_CODE", "ROLE", "REVOKED_ON_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."ROLE_ASSIGNMENTS" IS 'Trust and Data § 3 (D134): the five platform roles + customer representative, held as rows. SOURCE says where a row came from — token_claim (a role claim in the Authority JWT), directory_group (roles.group_mapping resolved at login) or platform_row (granted by an Administrator here). Prompt publisher is scoped per module/stage, approver per target class/system. The gate check reads the union of live rows for the subject.'~');
-- ---------------------------------------------------------------- OUTBOX_MESSAGES (RabbitMQ.Outbox rows)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."OUTBOX_MESSAGES" (
"ID" NVARCHAR2(64) NOT NULL,
"EXCHANGE" NVARCHAR2(128) NOT NULL,
"ROUTING_KEY" NVARCHAR2(256) NOT NULL,
"MESSAGE_TYPE" NVARCHAR2(128) NOT NULL,
"MESSAGE_VERSION" NUMBER(5) NOT NULL,
"PAYLOAD" CLOB NOT NULL,
"HEADERS" CLOB,
"CORRELATION_ID" NVARCHAR2(64),
"CASE_ID" NVARCHAR2(64),
"STATE" NVARCHAR2(16) NOT NULL,
"LEASE_OWNER" NVARCHAR2(128),
"LEASE_UNTIL_UTC" TIMESTAMP(6),
"ATTEMPTS" NUMBER(5) DEFAULT 0 NOT NULL,
"LAST_ERROR" NVARCHAR2(2000),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"PUBLISHED_ON_UTC" TIMESTAMP(6),
CONSTRAINT "PK_OUTBOX_MESSAGES" PRIMARY KEY ("ID"),
CONSTRAINT "CK_OUTBOX_STATE" CHECK ("STATE" IN ('pending','leased','published','failed','parked')),
CONSTRAINT "CK_OUTBOX_JSON" CHECK ("PAYLOAD" IS JSON AND ("HEADERS" IS NULL OR "HEADERS" IS JSON))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_OUTBOX_PENDING" ON "SRD_SUPPORT"."OUTBOX_MESSAGES" ("STATE", "CREATED_ON_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."OUTBOX_MESSAGES" IS 'Transactional outbox for the Rabbit lanes and the SignalR fan-out (Software Architecture § 7 Realtime/Messaging): a row is written in the same transaction as the state change it announces; the copied RabbitMQ.Outbox library leases and publishes it.'~');
-- ---------------------------------------------------------------- RETENTION_ACTIONS (D109 — manual actions are gated writes)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."RETENTION_ACTIONS" (
"ID" NVARCHAR2(64) NOT NULL,
"ARTEFACT_CLASS" NVARCHAR2(32) NOT NULL,
"SUBJECT_TABLE" NVARCHAR2(64) NOT NULL,
"SUBJECT_ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64),
"ACTION" NVARCHAR2(16) NOT NULL,
"TRIGGER" NVARCHAR2(16) NOT NULL,
"POLICY_VALUE_ID" NVARCHAR2(64),
"GATE_ID" NVARCHAR2(64),
"STATE" NVARCHAR2(16) NOT NULL,
"REQUESTED_BY" NVARCHAR2(128) NOT NULL,
"REQUESTED_ON_UTC" TIMESTAMP(6) NOT NULL,
"EXECUTED_ON_UTC" TIMESTAMP(6),
"LEDGER_RECORD_ID" NVARCHAR2(64),
CONSTRAINT "PK_RETENTION_ACTIONS" PRIMARY KEY ("ID"),
CONSTRAINT "FK_RET_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "FK_RET_GATE" FOREIGN KEY ("GATE_ID") REFERENCES "SRD_SUPPORT"."GATES" ("ID"),
CONSTRAINT "FK_RET_POLICY" FOREIGN KEY ("POLICY_VALUE_ID") REFERENCES "SRD_SUPPORT"."POLICY_VALUES" ("ID"),
CONSTRAINT "CK_RET_CLASS" CHECK ("ARTEFACT_CLASS" IN ('case_papers','ledger','handle_map','special_category_evidence','memory_article','eval_corpus','tool_result')),
CONSTRAINT "CK_RET_ACTION" CHECK ("ACTION" IN ('erase','anonymise','extend','export','destroy_key')),
CONSTRAINT "CK_RET_TRIGGER" CHECK ("TRIGGER" IN ('policy_expiry','case_closure','manual','legal_request')),
CONSTRAINT "CK_RET_STATE" CHECK ("STATE" IN ('proposed','at_gate','approved','executed','refused')),
CONSTRAINT "CK_RET_MANUAL_GATED" CHECK ("TRIGGER" <> 'manual' OR "STATE" = 'proposed' OR "GATE_ID" IS NOT NULL)
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_RET_STATE" ON "SRD_SUPPORT"."RETENTION_ACTIONS" ("STATE", "REQUESTED_ON_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."RETENTION_ACTIONS" IS 'PG-13 / D109: retention per artefact class is configuration (retention.per_class); cases and the ledger never expire; handle maps die at case closure (destroy_key = crypto-shredding); manual erase/anonymise/extend/export are gated writes (CK_RET_MANUAL_GATED). The evaluator exists from day one and is idle until a class has an expiry.'~');
END;
/