AISA v2.0 / Technical documentation / 002-decisions-and-grants.sql
002-decisions-and-grants.sql
SQL · 258 lines · 19,455 bytes · wiki path 10 Architecture/db/SrdSupport/002-decisions-and-grants.sql · download the raw file · cited from Data Model — `SRD_SUPPORT`
Same folder: 001-work-and-planning.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 · 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
-- 002 · DECISIONS and GRANTS — Architecture § 3.1 group "Decisions" + "Queues"; Gating § 4, § 9.2; Trust and Data § 2.
-- A gate is a row, not a call (Gating § 9.2). A grant is bound to the artefact hash it was issued for and is
-- checked by the connector before contact. A write shape expires on a default schedule and an approver may
-- extend it by at most one day per extension (A-9 → D133).
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
-- ---------------------------------------------------------------- WRITE_SHAPES (before GATES: GATES/PLAN_STEPS reference it)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."WRITE_SHAPES" (
"ID" NVARCHAR2(64) NOT NULL,
"SHAPE_KEY" NVARCHAR2(128) NOT NULL,
"VERSION" NUMBER(10) NOT NULL,
"MODULE" NVARCHAR2(16) NOT NULL,
"CUSTOMER_CODE" NVARCHAR2(32) NOT NULL,
"OPERATION_ID" NVARCHAR2(128) NOT NULL,
"CONNECTOR" NVARCHAR2(32) NOT NULL,
"TEMPLATE" CLOB NOT NULL,
"TEMPLATE_HASH" NVARCHAR2(64) NOT NULL,
"PARAMETERS" CLOB NOT NULL,
"TARGET_CLASS" NVARCHAR2(24) NOT NULL,
"EFFECT_CLASS" NVARCHAR2(16) NOT NULL,
"WRITE_CLASS" NVARCHAR2(2) NOT NULL,
"EXPECTED_COUNTS_EXPR" NVARCHAR2(1000) NOT NULL,
"ASSERTIONS" CLOB NOT NULL,
"TEARDOWN_TEMPLATE" CLOB NOT NULL,
"CASE_TYPES" CLOB NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"CLEAN_INSTANCES" NUMBER(5) DEFAULT 0 NOT NULL,
"APPROVED_BY_DECISION_ID" NVARCHAR2(64),
"APPROVED_ON_UTC" TIMESTAMP(6),
"EXPIRES_ON_UTC" TIMESTAMP(6),
"EXTENDED_UNTIL_UTC" TIMESTAMP(6),
"EXTENDED_BY" NVARCHAR2(128),
"EXTENDED_ON_UTC" TIMESTAMP(6),
"SUSPENDED_ON_UTC" TIMESTAMP(6),
"SUSPENSION_REASON" NVARCHAR2(1000),
"CREATED_BY" NVARCHAR2(128) NOT NULL,
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_WRITE_SHAPES" PRIMARY KEY ("ID"),
CONSTRAINT "UK_WRITE_SHAPES" UNIQUE ("CUSTOMER_CODE", "SHAPE_KEY", "VERSION"),
CONSTRAINT "CK_SHAPES_MODULE" CHECK ("MODULE" IN ('configuration','support','source')),
CONSTRAINT "CK_SHAPES_STATE" CHECK ("STATE" IN ('draft','approved','suspended','retired')),
CONSTRAINT "CK_SHAPES_TCLASS" CHECK ("TARGET_CLASS" IN ('working','shared','customer_facing','external','customer_visible')),
CONSTRAINT "CK_SHAPES_EFFECT" CHECK ("EFFECT_CLASS" IN ('transactional','compensable','irreversible','ddl')),
CONSTRAINT "CK_SHAPES_WCLASS" CHECK ("WRITE_CLASS" IN ('W1','W2','W3','W4','W5','W6','W7')),
CONSTRAINT "CK_SHAPES_PARAMS_JSON" CHECK ("PARAMETERS" IS JSON),
CONSTRAINT "CK_SHAPES_ASSERT_JSON" CHECK ("ASSERTIONS" IS JSON),
CONSTRAINT "CK_SHAPES_TYPES_JSON" CHECK ("CASE_TYPES" IS JSON),
CONSTRAINT "CK_SHAPES_CLEAN" CHECK ("CLEAN_INSTANCES" >= 0),
CONSTRAINT "CK_SHAPES_APPROVED" CHECK ("STATE" <> 'approved' OR ("APPROVED_ON_UTC" IS NOT NULL AND "EXPIRES_ON_UTC" IS NOT NULL)),
CONSTRAINT "CK_SHAPES_EXTENSION" CHECK ("EXTENDED_UNTIL_UTC" IS NULL OR ("EXPIRES_ON_UTC" IS NOT NULL AND "EXTENDED_UNTIL_UTC" > "EXPIRES_ON_UTC" AND "EXTENDED_UNTIL_UTC" <= "EXPIRES_ON_UTC" + INTERVAL '1' DAY))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_SHAPES_STATE" ON "SRD_SUPPORT"."WRITE_SHAPES" ("STATE", "MODULE", "CUSTOMER_CODE")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."WRITE_SHAPES" IS 'Gating § 4. The approved template a policy may later instantiate (contract: contracts/write-shape.schema.json). Immutable once approved except STATE, the suspension columns and one extension (CK_SHAPES_EXTENSION: at most EXPIRES_ON_UTC + 1 day — D133). EXPIRES_ON_UTC defaults from policy writes.shape_expiry_default at approval.'~');
-- ---------------------------------------------------------------- GATES
ddl(q'~CREATE TABLE "SRD_SUPPORT"."GATES" (
"ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"TASK_ID" NVARCHAR2(64),
"KIND" NVARCHAR2(16) NOT NULL,
"TARGET_CLASS" NVARCHAR2(24),
"ENVIRONMENT" NVARCHAR2(32),
"SYSTEM" NVARCHAR2(32),
"ARTEFACT_HASH" NVARCHAR2(64) NOT NULL,
"PACKET_PAPER_ID" NVARCHAR2(64),
"REQUIRED_ROLE" NVARCHAR2(32) NOT NULL,
"SLA_CLASS" NVARCHAR2(16),
"STATE" NVARCHAR2(16) NOT NULL,
"SHAPE_ID" NVARCHAR2(64),
"AUTO_CONFIRM_FAILED_CHECK" NUMBER(2),
"AUDITOR_VERDICT" NVARCHAR2(8),
"AUDITOR_VERDICT_PAPER_ID" NVARCHAR2(64),
"OPENED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ESCALATION_DUE_UTC" TIMESTAMP(6),
"ESCALATION_LEVEL" NUMBER(1) DEFAULT 0 NOT NULL,
"DECIDED_ON_UTC" TIMESTAMP(6),
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_GATES" PRIMARY KEY ("ID"),
CONSTRAINT "FK_GATES_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "FK_GATES_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
CONSTRAINT "FK_GATES_SHAPE" FOREIGN KEY ("SHAPE_ID") REFERENCES "SRD_SUPPORT"."WRITE_SHAPES" ("ID"),
CONSTRAINT "CK_GATES_KIND" CHECK ("KIND" IN ('H1','H2','H3','H4','H5','H6','H7','Hd','HW-approve','HW-instance','HW-shared','HW-irreversible','HW-ddl','H5-SIM','H5-send','CG-11','publish')),
CONSTRAINT "CK_GATES_STATE" CHECK ("STATE" IN ('open','decided','expired','withdrawn')),
CONSTRAINT "CK_GATES_ROLE" CHECK ("REQUIRED_ROLE" IN ('operator','approver','prompt_publisher','administrator','customer_representative','approver_pair')),
CONSTRAINT "CK_GATES_TCLASS" CHECK ("TARGET_CLASS" IS NULL OR "TARGET_CLASS" IN ('working','shared','customer_facing','external','customer_visible')),
CONSTRAINT "CK_GATES_CHECK" CHECK ("AUTO_CONFIRM_FAILED_CHECK" IS NULL OR "AUTO_CONFIRM_FAILED_CHECK" BETWEEN 1 AND 9),
CONSTRAINT "CK_GATES_AUDITOR" CHECK ("AUDITOR_VERDICT" IS NULL OR "AUDITOR_VERDICT" IN ('pass','refuse')),
CONSTRAINT "CK_GATES_ESC" CHECK ("ESCALATION_LEVEL" BETWEEN 0 AND 2)
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GATES_OPEN" ON "SRD_SUPPORT"."GATES" ("STATE", "REQUIRED_ROLE", "OPENED_ON_UTC")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GATES_CASE" ON "SRD_SUPPORT"."GATES" ("CASE_ID", "STATE")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GATES_ESCALATION" ON "SRD_SUPPORT"."GATES" ("STATE", "ESCALATION_DUE_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."GATES" IS 'Gating § 1 and § 9.2. The Decisions workspace is a view over STATE = open. AUTO_CONFIRM_FAILED_CHECK names the first failing condition of Gating § 9.4 so the operator sees why they are asked. Escalation is a timer on the row (PG-12): level 0 holders, 1 all holders, 2 administrator. REQUIRED_ROLE approver_pair = H5-SIM (two roles, D107).'~');
-- ---------------------------------------------------------------- GATE_DECISIONS (immutable)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."GATE_DECISIONS" (
"ID" NVARCHAR2(64) NOT NULL,
"GATE_ID" NVARCHAR2(64) NOT NULL,
"ACTOR_KIND" NVARCHAR2(8) NOT NULL,
"ACTOR_ID" NVARCHAR2(128) NOT NULL,
"ACTOR_ROLE" NVARCHAR2(32) NOT NULL,
"DECISION" NVARCHAR2(16) NOT NULL,
"SCOPE_APPROVED" CLOB,
"PLAN_REVISION_ID" NVARCHAR2(64),
"ARTEFACT_HASH" NVARCHAR2(64) NOT NULL,
"SHAPE_ID" NVARCHAR2(64),
"SHAPE_VERSION" NUMBER(10),
"REASON" NVARCHAR2(2000),
"DECIDED_ON_UTC" TIMESTAMP(6) NOT NULL,
"LEDGER_RECORD_ID" NVARCHAR2(64) NOT NULL,
CONSTRAINT "PK_GATE_DECISIONS" PRIMARY KEY ("ID"),
CONSTRAINT "FK_GDEC_GATE" FOREIGN KEY ("GATE_ID") REFERENCES "SRD_SUPPORT"."GATES" ("ID"),
CONSTRAINT "FK_GDEC_PLANREV" FOREIGN KEY ("PLAN_REVISION_ID") REFERENCES "SRD_SUPPORT"."PLAN_REVISIONS" ("ID"),
CONSTRAINT "FK_GDEC_SHAPE" FOREIGN KEY ("SHAPE_ID") REFERENCES "SRD_SUPPORT"."WRITE_SHAPES" ("ID"),
CONSTRAINT "CK_GDEC_ACTOR" CHECK ("ACTOR_KIND" IN ('person','policy')),
CONSTRAINT "CK_GDEC_DECISION" CHECK ("DECISION" IN ('approve','reject','amend','defer','accept','withdraw')),
CONSTRAINT "CK_GDEC_SCOPE_JSON" CHECK ("SCOPE_APPROVED" IS NULL OR "SCOPE_APPROVED" IS JSON),
CONSTRAINT "CK_GDEC_POLICY_SHAPE" CHECK ("ACTOR_KIND" <> 'policy' OR ("SHAPE_ID" IS NOT NULL AND "SHAPE_VERSION" IS NOT NULL))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GDEC_GATE" ON "SRD_SUPPORT"."GATE_DECISIONS" ("GATE_ID")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GDEC_SHAPE" ON "SRD_SUPPORT"."GATE_DECISIONS" ("SHAPE_ID", "SHAPE_VERSION", "DECIDED_ON_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."GATE_DECISIONS" IS 'Append-only. ACTOR_KIND policy = an auto-confirmed instance (Gating § 5, D70) — the same row, the policy id where a name would be; CK_GDEC_POLICY_SHAPE makes the shape mandatory for it. Every row is chained into AUDIT_LEDGER via LEDGER_RECORD_ID.'~');
-- ---------------------------------------------------------------- GRANTS
ddl(q'~CREATE TABLE "SRD_SUPPORT"."GRANTS" (
"ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"ENVIRONMENT" NVARCHAR2(32) NOT NULL,
"SYSTEM" NVARCHAR2(32) NOT NULL,
"TARGET" NVARCHAR2(512) NOT NULL,
"MODE" NVARCHAR2(8) NOT NULL,
"BOUND_ARTEFACT_HASH" NVARCHAR2(64),
"EXPECTED_ROWS" NUMBER(10),
"PRECONDITIONS_HASH" NVARCHAR2(64),
"ISSUED_BY_KIND" NVARCHAR2(16) NOT NULL,
"ISSUED_BY_REF" NVARCHAR2(64) NOT NULL,
"ISSUED_ON_UTC" TIMESTAMP(6) NOT NULL,
"EXPIRES_ON_UTC" TIMESTAMP(6) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"REVOKED_ON_UTC" TIMESTAMP(6),
"REVOKED_REASON" NVARCHAR2(1000),
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_GRANTS" PRIMARY KEY ("ID"),
CONSTRAINT "FK_GRANTS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "CK_GRANTS_MODE" CHECK ("MODE" IN ('read','dry_run','write','send','ddl')),
CONSTRAINT "CK_GRANTS_STATE" CHECK ("STATE" IN ('active','consumed','expired','revoked')),
CONSTRAINT "CK_GRANTS_ISSUER" CHECK ("ISSUED_BY_KIND" IN ('case_type','gate_decision','administrator')),
CONSTRAINT "CK_GRANTS_BOUND" CHECK ("MODE" IN ('read','dry_run') OR "BOUND_ARTEFACT_HASH" IS NOT NULL),
CONSTRAINT "CK_GRANTS_WINDOW" CHECK ("EXPIRES_ON_UTC" > "ISSUED_ON_UTC")
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GRANTS_CASE" ON "SRD_SUPPORT"."GRANTS" ("CASE_ID", "STATE")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GRANTS_SCOPE" ON "SRD_SUPPORT"."GRANTS" ("ENVIRONMENT", "SYSTEM", "MODE", "STATE")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."GRANTS" IS 'Trust and Data § 2. Scope = environment × system × target × mode; a write grant is bound to the artefact it was approved for (CK_GRANTS_BOUND). Checked by the connector before contact. Expires at case close or EXPIRES_ON_UTC, whichever first (grants.expiry).'~');
-- ---------------------------------------------------------------- RISK_ACCEPTANCES (N-12 register)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."RISK_ACCEPTANCES" (
"ID" NVARCHAR2(64) NOT NULL,
"CUSTOMER_CODE" NVARCHAR2(32) NOT NULL,
"MODULE" NVARCHAR2(16) NOT NULL,
"CASE_TYPE" NVARCHAR2(64) NOT NULL,
"GATE_KIND" NVARCHAR2(16) NOT NULL,
"TARGET_CLASS" NVARCHAR2(24) NOT NULL,
"WRITE_CLASS" NVARCHAR2(2) NOT NULL,
"ACCEPTED_BY" NVARCHAR2(128) NOT NULL,
"ACCEPTED_ROLE" NVARCHAR2(64) NOT NULL,
"DECISION_REF" NVARCHAR2(256) NOT NULL,
"ACCEPTED_ON_UTC" TIMESTAMP(6) NOT NULL,
"WITHDRAWN_ON_UTC" TIMESTAMP(6),
"WITHDRAWN_BY" NVARCHAR2(128),
"LEDGER_RECORD_ID" NVARCHAR2(64) NOT NULL,
CONSTRAINT "PK_RISK_ACCEPTANCES" PRIMARY KEY ("ID"),
CONSTRAINT "CK_RISK_MODULE" CHECK ("MODULE" IN ('configuration','support','source')),
CONSTRAINT "CK_RISK_WCLASS" CHECK ("WRITE_CLASS" IN ('W1','W2'))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_RISK_LOOKUP" ON "SRD_SUPPORT"."RISK_ACCEPTANCES" ("CUSTOMER_CODE", "CASE_TYPE", "GATE_KIND", "TARGET_CLASS", "WITHDRAWN_ON_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."RISK_ACCEPTANCES" IS 'The register N-12 names: a write class leaves the always-human set only by a row here, written by a person with DECISION_REF to the recorded organisational decision. Gating § 9.4 check 1 reads it. CK_RISK_WCLASS: only W1/W2 can ever be accepted (Gating § 3); a plug-in may narrow, never widen.'~');
-- ---------------------------------------------------------------- HELD_BATCHES · HELD_BATCH_ITEMS
ddl(q'~CREATE TABLE "SRD_SUPPORT"."HELD_BATCHES" (
"ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"GATE_DECISION_ID" NVARCHAR2(64) NOT NULL,
"GRANT_ID" NVARCHAR2(64) NOT NULL,
"ITEMS_TOTAL" NUMBER(10) NOT NULL,
"ITEMS_APPLIED" NUMBER(10) DEFAULT 0 NOT NULL,
"PREFLIGHT_MODE" NVARCHAR2(16) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"HELD_SINCE_UTC" TIMESTAMP(6) NOT NULL,
"LAST_PREFLIGHT_UTC" TIMESTAMP(6),
"COMPLETED_ON_UTC" TIMESTAMP(6),
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_HELD_BATCHES" PRIMARY KEY ("ID"),
CONSTRAINT "FK_HELD_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "FK_HELD_DECISION" FOREIGN KEY ("GATE_DECISION_ID") REFERENCES "SRD_SUPPORT"."GATE_DECISIONS" ("ID"),
CONSTRAINT "FK_HELD_GRANT" FOREIGN KEY ("GRANT_ID") REFERENCES "SRD_SUPPORT"."GRANTS" ("ID"),
CONSTRAINT "CK_HELD_MODE" CHECK ("PREFLIGHT_MODE" IN ('ordinal','per_item')),
CONSTRAINT "CK_HELD_STATE" CHECK ("STATE" IN ('held','applying','applied','abandoned')),
CONSTRAINT "CK_HELD_COUNTS" CHECK ("ITEMS_TOTAL" > 0 AND "ITEMS_APPLIED" BETWEEN 0 AND "ITEMS_TOTAL")
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_HELD_STATE" ON "SRD_SUPPORT"."HELD_BATCHES" ("STATE", "HELD_SINCE_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."HELD_BATCHES" IS 'Approved-but-unapplied work with its age (Gating § 9.2) — the fix for the 12-week HDesk plateau. A batch older than held_batches.repreflight_after is re-preflighted from the first unapplied item (D66).'~');
ddl(q'~CREATE TABLE "SRD_SUPPORT"."HELD_BATCH_ITEMS" (
"ID" NVARCHAR2(64) NOT NULL,
"BATCH_ID" NVARCHAR2(64) NOT NULL,
"SEQ_NO" NUMBER(10) NOT NULL,
"PLAN_STEP_ID" NVARCHAR2(64) NOT NULL,
"STATEMENT_HASH" NVARCHAR2(64) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"PREFLIGHTED_ON_UTC" TIMESTAMP(6),
"APPLIED_ON_UTC" TIMESTAMP(6),
"WRITE_LOG_ID" NVARCHAR2(64),
CONSTRAINT "PK_HELD_BATCH_ITEMS" PRIMARY KEY ("ID"),
CONSTRAINT "UK_HELD_BATCH_ITEMS" UNIQUE ("BATCH_ID", "SEQ_NO"),
CONSTRAINT "FK_HITEM_BATCH" FOREIGN KEY ("BATCH_ID") REFERENCES "SRD_SUPPORT"."HELD_BATCHES" ("ID"),
CONSTRAINT "FK_HITEM_STEP" FOREIGN KEY ("PLAN_STEP_ID") REFERENCES "SRD_SUPPORT"."PLAN_STEPS" ("ID"),
CONSTRAINT "CK_HITEM_STATE" CHECK ("STATE" IN ('pending','preflighted','applied','failed','skipped'))
)~');
-- ---------------------------------------------------------------- WRITE_LEASES (shape B)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."WRITE_LEASES" (
"ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"GRANT_ID" NVARCHAR2(64) NOT NULL,
"CONNECTOR" NVARCHAR2(32) NOT NULL,
"ENVIRONMENT" NVARCHAR2(32) NOT NULL,
"SESSION_REF" NVARCHAR2(256) NOT NULL,
"EPOCH" NUMBER(19) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"LEASED_ON_UTC" TIMESTAMP(6) NOT NULL,
"LEASED_UNTIL_UTC" TIMESTAMP(6) NOT NULL,
"ENDED_ON_UTC" TIMESTAMP(6),
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_WRITE_LEASES" PRIMARY KEY ("ID"),
CONSTRAINT "FK_LEASE_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "FK_LEASE_GRANT" FOREIGN KEY ("GRANT_ID") REFERENCES "SRD_SUPPORT"."GRANTS" ("ID"),
CONSTRAINT "CK_LEASE_STATE" CHECK ("STATE" IN ('open','committed','rolled_back','expired')),
CONSTRAINT "CK_LEASE_WINDOW" CHECK ("LEASED_UNTIL_UTC" > "LEASED_ON_UTC")
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_LEASES_OPEN" ON "SRD_SUPPORT"."WRITE_LEASES" ("STATE", "LEASED_UNTIL_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."WRITE_LEASES" IS 'Failure and Recovery § 1 write shape B: a connector-owned transaction under a lease that rolls back on expiry. No agent turn spans an uncommitted write (CR-9); the lease is what makes the rule checkable.'~');
-- deferred FK: PLAN_STEPS.SHAPE_ID → WRITE_SHAPES (WRITE_SHAPES is created in this script)
ddl(q'~ALTER TABLE "SRD_SUPPORT"."PLAN_STEPS" ADD CONSTRAINT "FK_STEPS_SHAPE" FOREIGN KEY ("SHAPE_ID") REFERENCES "SRD_SUPPORT"."WRITE_SHAPES" ("ID")~');
END;
/