AISA v2.0 / Technical documentation / 001-work-and-planning.sql
001-work-and-planning.sql
SQL · 284 lines · 20,095 bytes · wiki path 10 Architecture/db/SrdSupport/001-work-and-planning.sql · download the raw file · cited from Data Model — `SRD_SUPPORT`
Same folder: 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 · 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
-- 001 · WORK and PLANNING — Architecture § 3.1 groups "Work" and "Planning".
-- Every statement is twice-runnable: ORA-00955 (name in use) and the constraint/index
-- "already exists" codes are swallowed, anything else stops the script (SrdAi pattern).
--
-- Conventions of the whole SrdSupport script set
-- ids NVARCHAR2(64) opaque strings (ULID/GUID text) issued by the service — never sequences,
-- so a case id is the same in the ledger, the SignalR ticket and the papers volume
-- time TIMESTAMP(6) UTC, column suffix _UTC
-- hashes NVARCHAR2(64) lower-case hex SHA-256 over the SERDICA-JCS-1 canonical form (AI.Contracts)
-- json CLOB with CHECK (… IS JSON); the schema of each payload is a contract under 10 Architecture/contracts
-- budget minutes of predicted delivery time — the only budget dimension (D135)
-- states closed CHECK lists; a module extends a vocabulary by adding a row to POLICY_VALUES, not a value here
DECLARE
v_count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count FROM ALL_USERS WHERE USERNAME = 'SRD_SUPPORT';
IF v_count = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'SRD_SUPPORT user does not exist — created once by the DBA (D47) before any script runs');
END IF;
END;
/
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
-- ---------------------------------------------------------------- CASES
ddl(q'~CREATE TABLE "SRD_SUPPORT"."CASES" (
"ID" NVARCHAR2(64) NOT NULL,
"CASE_NO" NVARCHAR2(32) NOT NULL,
"MODULE" NVARCHAR2(16) NOT NULL,
"CASE_TYPE" NVARCHAR2(64) NOT NULL,
"CUSTOMER_CODE" NVARCHAR2(32) NOT NULL,
"OBJECTIVE" NVARCHAR2(2000) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"PARK_REASON" NVARCHAR2(32),
"PARENT_CASE_ID" NVARCHAR2(64),
"ORIGIN_CHANNEL" NVARCHAR2(32) NOT NULL,
"ORIGIN_KEY" NVARCHAR2(256) NOT NULL,
"AUTHORITATIVE_CHANNEL" NVARCHAR2(32) NOT NULL,
"DEFAULT_ENVIRONMENT" NVARCHAR2(32) NOT NULL,
"WORKING_ENVIRONMENT" NVARCHAR2(32),
"SLA_CLASS" NVARCHAR2(16),
"CONTRACT_SECTION" NVARCHAR2(16),
"SEVERITY" NVARCHAR2(16),
"RESPONSE_DUE_UTC" TIMESTAMP(6),
"RESOLUTION_DUE_UTC" TIMESTAMP(6),
"CLOCK_PAUSED_FROM_UTC" TIMESTAMP(6),
"BUDGET_MINUTES" NUMBER(10) NOT NULL,
"BUDGET_USED_MINUTES" NUMBER(10,2) DEFAULT 0 NOT NULL,
"CONTROLLER_USER" NVARCHAR2(128),
"ROUTE_CONFIDENCE" NVARCHAR2(8) DEFAULT 'high' NOT NULL,
"OPENED_BY" NVARCHAR2(128) NOT NULL,
"OPENED_ON_UTC" TIMESTAMP(6) NOT NULL,
"RESOLVED_ON_UTC" TIMESTAMP(6),
"CLOSED_ON_UTC" TIMESTAMP(6),
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_CASES" PRIMARY KEY ("ID"),
CONSTRAINT "UK_CASES_NO" UNIQUE ("CASE_NO"),
CONSTRAINT "UK_CASES_ORIGIN" UNIQUE ("ORIGIN_CHANNEL", "ORIGIN_KEY"),
CONSTRAINT "FK_CASES_PARENT" FOREIGN KEY ("PARENT_CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "CK_CASES_MODULE" CHECK ("MODULE" IN ('configuration','support','source')),
CONSTRAINT "CK_CASES_STATE" CHECK ("STATE" IN ('opened','running','parked','at_gate','held','resolved','closed','cancelled')),
CONSTRAINT "CK_CASES_PARK" CHECK ("PARK_REASON" IS NULL OR "PARK_REASON" IN ('question','gate','sub_case','connector_down','provider_down','budget','capability_gap')),
CONSTRAINT "CK_CASES_ROUTE" CHECK ("ROUTE_CONFIDENCE" IN ('high','low')),
CONSTRAINT "CK_CASES_BUDGET" CHECK ("BUDGET_MINUTES" > 0 AND "BUDGET_USED_MINUTES" >= 0)
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_CASES_STATE" ON "SRD_SUPPORT"."CASES" ("STATE", "MODULE")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_CASES_PARENT" ON "SRD_SUPPORT"."CASES" ("PARENT_CASE_ID")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_CASES_CLOCKS" ON "SRD_SUPPORT"."CASES" ("RESPONSE_DUE_UTC", "RESOLUTION_DUE_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CASES" IS 'The durable work object (Agent Runtime § 11.1). PARENT_CASE_ID = an Hd sub-case inside the same case (Agents § 5). Clocks: two per case (Stage Classification § 3), paused while CLOCK_PAUSED_FROM_UTC is set. BUDGET_MINUTES = predicted delivery time (D135).'~');
-- ---------------------------------------------------------------- SESSIONS
ddl(q'~CREATE TABLE "SRD_SUPPORT"."SESSIONS" (
"ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"CONTROLLER_USER" NVARCHAR2(128),
"WORKER_ID" NVARCHAR2(128),
"RUNTIME_VERSION" NVARCHAR2(64) NOT NULL,
"FRAMEWORK_VERSION" NVARCHAR2(128) NOT NULL,
"STARTED_ON_UTC" TIMESTAMP(6) NOT NULL,
"STOPPED_ON_UTC" TIMESTAMP(6),
"LAST_CHECKPOINT_UTC" TIMESTAMP(6),
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_SESSIONS" PRIMARY KEY ("ID"),
CONSTRAINT "FK_SESSIONS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "CK_SESSIONS_STATE" CHECK ("STATE" IN ('live','stopped','resumed','deleted'))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_SESSIONS_CASE" ON "SRD_SUPPORT"."SESSIONS" ("CASE_ID", "STATE")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."SESSIONS" IS 'The live execution of a case: stoppable, resumable, deletable; many viewers, one CONTROLLER_USER (CR-5). A worker is only a cache of the papers; the session row says which worker holds it.'~');
-- ---------------------------------------------------------------- TASKS (the graph is data)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."TASKS" (
"ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"SESSION_ID" NVARCHAR2(64),
"PARENT_TASK_ID" NVARCHAR2(64),
"TASK_PATH" NVARCHAR2(1000) NOT NULL,
"KIND" NVARCHAR2(16) NOT NULL,
"PROFILE_KEY" NVARCHAR2(128) NOT NULL,
"PROFILE_VERSION" NUMBER(10) NOT NULL,
"STAGE_ID" NVARCHAR2(16),
"STATE" NVARCHAR2(16) NOT NULL,
"PARK_REASON" NVARCHAR2(32),
"TASK_TEXT" CLOB NOT NULL,
"DENIED_CONTEXT" CLOB,
"GRANT_SUBSET" CLOB,
"MEMORY_NODE" NVARCHAR2(256) NOT NULL,
"BUDGET_MINUTES" NUMBER(10) NOT NULL,
"BUDGET_USED_MINUTES" NUMBER(10,2) DEFAULT 0 NOT NULL,
"DEADLINE_UTC" TIMESTAMP(6),
"PRIORITY" NUMBER(3),
"RUNTIME_VERSION" NVARCHAR2(64) NOT NULL,
"FENCE_EPOCH" NUMBER(19) DEFAULT 0 NOT NULL,
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ENDED_ON_UTC" TIMESTAMP(6),
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_TASKS" PRIMARY KEY ("ID"),
CONSTRAINT "FK_TASKS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "FK_TASKS_SESSION" FOREIGN KEY ("SESSION_ID") REFERENCES "SRD_SUPPORT"."SESSIONS" ("ID"),
CONSTRAINT "FK_TASKS_PARENT" FOREIGN KEY ("PARENT_TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
CONSTRAINT "CK_TASKS_KIND" CHECK ("KIND" IN ('root','stage','sub_agent','consult','write_executor','connector','handover')),
CONSTRAINT "CK_TASKS_STATE" CHECK ("STATE" IN ('open','running','parked','done','failed','cancelled')),
CONSTRAINT "CK_TASKS_PARK" CHECK ("PARK_REASON" IS NULL OR "PARK_REASON" IN ('question','gate','sub_case','connector_down','provider_down','budget','capability_gap')),
CONSTRAINT "CK_TASKS_DENIED_JSON" CHECK ("DENIED_CONTEXT" IS NULL OR "DENIED_CONTEXT" IS JSON),
CONSTRAINT "CK_TASKS_GRANTS_JSON" CHECK ("GRANT_SUBSET" IS NULL OR "GRANT_SUBSET" IS JSON),
CONSTRAINT "CK_TASKS_BUDGET" CHECK ("BUDGET_MINUTES" >= 0 AND "BUDGET_USED_MINUTES" >= 0)
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_TASKS_CASE" ON "SRD_SUPPORT"."TASKS" ("CASE_ID", "STATE")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_TASKS_PARENT" ON "SRD_SUPPORT"."TASKS" ("PARENT_TASK_ID")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_TASKS_CONSULT_QUEUE" ON "SRD_SUPPORT"."TASKS" ("KIND", "STATE", "MEMORY_NODE", "PRIORITY", "CREATED_ON_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."TASKS" IS 'One agent instance in one case (Agent Runtime § 1). The tree of PARENT_TASK_ID rows IS the module graph. KIND consult = a read-only domain consult (Agents § 5, D92); KIND handover = the Hd request that opened a sub-case. TASK_PATH = materialised ancestry for ledger correlation.'~');
-- ---------------------------------------------------------------- TURN_CHECKPOINTS
ddl(q'~CREATE TABLE "SRD_SUPPORT"."TURN_CHECKPOINTS" (
"ID" NVARCHAR2(64) NOT NULL,
"TASK_ID" NVARCHAR2(64) NOT NULL,
"TURN_NO" NUMBER(10) NOT NULL,
"CONTEXT_HASH" NVARCHAR2(64) NOT NULL,
"STATE_HASH" NVARCHAR2(64) NOT NULL,
"COMPACTION_ARTEFACT_HASH" NVARCHAR2(64),
"PROFILE_VERSION" NUMBER(10) NOT NULL,
"PROMPT_VERSION" NUMBER(10) NOT NULL,
"ROUTE_VERSION" NUMBER(10) NOT NULL,
"FRAMEWORK_VERSION" NVARCHAR2(128) NOT NULL,
"DURATION_MS" NUMBER(19) NOT NULL,
"MINUTES_CHARGED" NUMBER(10,2) NOT NULL,
"LEDGER_FIRST_SEQ" NUMBER(19),
"LEDGER_LAST_SEQ" NUMBER(19),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
CONSTRAINT "PK_TURN_CHECKPOINTS" PRIMARY KEY ("ID"),
CONSTRAINT "UK_TURN_CHECKPOINTS" UNIQUE ("TASK_ID", "TURN_NO"),
CONSTRAINT "FK_TURN_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
CONSTRAINT "CK_TURN_NO" CHECK ("TURN_NO" > 0)
)~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."TURN_CHECKPOINTS" IS 'One row per completed turn (Agent Runtime § 2 rule 4): the checkpoint a resumed worker continues from; the events of the turn are the ledger rows LEDGER_FIRST_SEQ..LEDGER_LAST_SEQ. FRAMEWORK_VERSION stamped per Agent Framework § Challenges.'~');
-- ---------------------------------------------------------------- CASE_IMPACT_KEYS (PG-20)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."CASE_IMPACT_KEYS" (
"CASE_ID" NVARCHAR2(64) NOT NULL,
"IMPACT_KEY" NVARCHAR2(512) NOT NULL,
"MODE" NVARCHAR2(8) NOT NULL,
"DECLARED_ON_UTC" TIMESTAMP(6) NOT NULL,
CONSTRAINT "PK_CASE_IMPACT_KEYS" PRIMARY KEY ("CASE_ID", "IMPACT_KEY", "MODE"),
CONSTRAINT "FK_IMPACT_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "CK_IMPACT_MODE" CHECK ("MODE" IN ('read','write'))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_IMPACT_KEY" ON "SRD_SUPPORT"."CASE_IMPACT_KEYS" ("IMPACT_KEY", "MODE")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CASE_IMPACT_KEYS" IS 'environment|system|product-or-version|account|route keys a case intends to write (Agent Runtime § 11.5). Two open cases sharing a write key raise a coordination decision; reads never block.'~');
-- ---------------------------------------------------------------- CASE_LINKS
ddl(q'~CREATE TABLE "SRD_SUPPORT"."CASE_LINKS" (
"ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"LINK_KIND" NVARCHAR2(16) NOT NULL,
"EXTERNAL_SYSTEM" NVARCHAR2(32) NOT NULL,
"EXTERNAL_REF" NVARCHAR2(512) NOT NULL,
"IS_AUTHORITATIVE" NUMBER(1) DEFAULT 0 NOT NULL,
"LINKED_ON_UTC" TIMESTAMP(6) NOT NULL,
CONSTRAINT "PK_CASE_LINKS" PRIMARY KEY ("ID"),
CONSTRAINT "UK_CASE_LINKS" UNIQUE ("CASE_ID", "EXTERNAL_SYSTEM", "EXTERNAL_REF"),
CONSTRAINT "FK_LINKS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "CK_LINKS_KIND" CHECK ("LINK_KIND" IN ('ticket','mail_thread','hdesk_ticket','merge_request','issue','pipeline','duplicate_of','mirror'))
)~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CASE_LINKS" IS 'The ticket system stays the system of record (N-11); the case links to it. Exactly one link per case carries IS_AUTHORITATIVE = 1 — the channel the customer speaks on; mirrors are projections (Stage Classification § 4).'~');
-- ---------------------------------------------------------------- ARRIVALS (the Inbox, before a case exists)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."ARRIVALS" (
"ID" NVARCHAR2(64) NOT NULL,
"CHANNEL" NVARCHAR2(32) NOT NULL,
"ORIGIN_KEY" NVARCHAR2(256) NOT NULL,
"CUSTOMER_CODE" NVARCHAR2(32),
"REPORTER_HANDLE" NVARCHAR2(64),
"SUBJECT" NVARCHAR2(1000),
"BODY_HASH" NVARCHAR2(64) NOT NULL,
"ARTEFACT_REF" NVARCHAR2(512) NOT NULL,
"PROPOSED_MODULE" NVARCHAR2(16),
"PROPOSED_CASE_TYPE" NVARCHAR2(64),
"ROUTE_CONFIDENCE" NVARCHAR2(8),
"DUPLICATE_OF_CASE_ID" NVARCHAR2(64),
"INITIATION_OUTCOME" NVARCHAR2(16),
"STATE" NVARCHAR2(24) NOT NULL,
"CASE_ID" NVARCHAR2(64),
"RECEIVED_ON_UTC" TIMESTAMP(6) NOT NULL,
"DECIDED_ON_UTC" TIMESTAMP(6),
"DECIDED_BY" NVARCHAR2(128),
CONSTRAINT "PK_ARRIVALS" PRIMARY KEY ("ID"),
CONSTRAINT "UK_ARRIVALS_ORIGIN" UNIQUE ("CHANNEL", "ORIGIN_KEY"),
CONSTRAINT "FK_ARRIVALS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "FK_ARRIVALS_DUP" FOREIGN KEY ("DUPLICATE_OF_CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "CK_ARRIVALS_STATE" CHECK ("STATE" IN ('new','opened','merged','dismissed','awaiting_authorisation','refused')),
CONSTRAINT "CK_ARRIVALS_INIT" CHECK ("INITIATION_OUTCOME" IS NULL OR "INITIATION_OUTCOME" IN ('granted','may_request','not_granted')),
CONSTRAINT "CK_ARRIVALS_MODULE" CHECK ("PROPOSED_MODULE" IS NULL OR "PROPOSED_MODULE" IN ('configuration','support','source'))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_ARRIVALS_STATE" ON "SRD_SUPPORT"."ARRIVALS" ("STATE", "RECEIVED_ON_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."ARRIVALS" IS 'A normalised arrival (contract: drafts/normalised-arrival) as platform.intake re-briefs it (Agents § 5c). Dedup checks (CHANNEL, ORIGIN_KEY) first. The body itself is a paper; only its hash is here. REPORTER_HANDLE is a handle, never the identifier (Trust and Data § 4).'~');
-- ---------------------------------------------------------------- PLANS · PLAN_REVISIONS · PLAN_STEPS
ddl(q'~CREATE TABLE "SRD_SUPPORT"."PLANS" (
"ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"TASK_ID" NVARCHAR2(64) NOT NULL,
"CURRENT_REVISION_NO" NUMBER(10) DEFAULT 0 NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_PLANS" PRIMARY KEY ("ID"),
CONSTRAINT "FK_PLANS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "FK_PLANS_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
CONSTRAINT "CK_PLANS_STATE" CHECK ("STATE" IN ('draft','confirmed','superseded','reconciled'))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_PLANS_CASE" ON "SRD_SUPPORT"."PLANS" ("CASE_ID")~');
ddl(q'~CREATE TABLE "SRD_SUPPORT"."PLAN_REVISIONS" (
"ID" NVARCHAR2(64) NOT NULL,
"PLAN_ID" NVARCHAR2(64) NOT NULL,
"REVISION_NO" NUMBER(10) NOT NULL,
"ARTEFACT_HASH" NVARCHAR2(64) NOT NULL,
"CONFIRMED_BY_KIND" NVARCHAR2(16),
"CONFIRMED_BY_REF" NVARCHAR2(64),
"REASON" NVARCHAR2(1000),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
CONSTRAINT "PK_PLAN_REVISIONS" PRIMARY KEY ("ID"),
CONSTRAINT "UK_PLAN_REVISIONS" UNIQUE ("PLAN_ID", "REVISION_NO"),
CONSTRAINT "FK_PLANREV_PLAN" FOREIGN KEY ("PLAN_ID") REFERENCES "SRD_SUPPORT"."PLANS" ("ID"),
CONSTRAINT "CK_PLANREV_NO" CHECK ("REVISION_NO" > 0),
CONSTRAINT "CK_PLANREV_BY" CHECK ("CONFIRMED_BY_KIND" IS NULL OR "CONFIRMED_BY_KIND" IN ('parent_task','gate_decision'))
)~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."PLAN_REVISIONS" IS 'Immutable. A child plan is confirmed by its immediate parent; only the root asks the human (Agent Runtime § 11.3). Parent confirmation never substitutes for a gate (CR-2).'~');
ddl(q'~CREATE TABLE "SRD_SUPPORT"."PLAN_STEPS" (
"ID" NVARCHAR2(64) NOT NULL,
"PLAN_REVISION_ID" NVARCHAR2(64) NOT NULL,
"STEP_ID" NVARCHAR2(64) NOT NULL,
"SEQ_NO" NUMBER(10) NOT NULL,
"DESCRIPTION" NVARCHAR2(2000) NOT NULL,
"ASSERTION" NVARCHAR2(1000) NOT NULL,
"EFFECT_CLASS" NVARCHAR2(16) NOT NULL,
"WRITE_CLASS" NVARCHAR2(2),
"TARGET_ENVIRONMENT" NVARCHAR2(32),
"TARGET_SYSTEM" NVARCHAR2(32),
"TARGET_OBJECT" NVARCHAR2(256),
"SHAPE_ID" NVARCHAR2(64),
"STATUS" NVARCHAR2(16) DEFAULT 'planned' NOT NULL,
CONSTRAINT "PK_PLAN_STEPS" PRIMARY KEY ("ID"),
CONSTRAINT "UK_PLAN_STEPS" UNIQUE ("PLAN_REVISION_ID", "STEP_ID"),
CONSTRAINT "FK_STEPS_REV" FOREIGN KEY ("PLAN_REVISION_ID") REFERENCES "SRD_SUPPORT"."PLAN_REVISIONS" ("ID"),
CONSTRAINT "CK_STEPS_EFFECT" CHECK ("EFFECT_CLASS" IN ('none','transactional','compensable','irreversible','ddl')),
CONSTRAINT "CK_STEPS_WCLASS" CHECK ("WRITE_CLASS" IS NULL OR "WRITE_CLASS" IN ('W1','W2','W3','W4','W5','W6','W7')),
CONSTRAINT "CK_STEPS_STATUS" CHECK ("STATUS" IN ('planned','done','skipped','failed','reverted'))
)~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."PLAN_STEPS" IS 'STEP_ID is stable across revisions so BUILD_STATE and WRITE_LOG can be reconciled against the plan at close (Agent Runtime § 11.3 rule 3). Every step declares its effect class (CR-9) and, for a write, its class W1–W7 (Gating § 3).'~');
END;
/