SET DEFINE OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- 003 · EVIDENCE — Architecture § 3.1 group "Evidence"; Agent Runtime § 11.2 (the working papers);
-- Trust and Data § 4 (handles); Stage Planning § 3b (PL/SQL snapshots).
-- Papers are files on the papers volume behind the token-gated file service; PAPERS is their revision index.
-- WRITE_LOG holds statements, targets, modes and counts — never result rows.

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
    -- ---------------------------------------------------------------- PAPERS (revision index; rows immutable)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."PAPERS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "KIND"                      NVARCHAR2(24)   NOT NULL,
        "NAME"                      NVARCHAR2(256)  NOT NULL,
        "REVISION_NO"               NUMBER(10)      NOT NULL,
        "CONTENT_HASH"              NVARCHAR2(64)   NOT NULL,
        "MEDIA_TYPE"                NVARCHAR2(128)  NOT NULL,
        "BYTE_LENGTH"               NUMBER(19)      NOT NULL,
        "STORAGE_REF"               NVARCHAR2(512)  NOT NULL,
        "SCHEMA_ID"                 NVARCHAR2(128),
        "SCHEMA_VERSION"            NVARCHAR2(32),
        "TRUST_CLASS"               NVARCHAR2(16)   NOT NULL,
        "WRITTEN_BY_TASK_ID"        NVARCHAR2(64),
        "WRITTEN_BY_USER"           NVARCHAR2(128),
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_PAPERS" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_PAPERS_REVISION" UNIQUE ("CASE_ID", "KIND", "NAME", "REVISION_NO"),
        CONSTRAINT "FK_PAPERS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_PAPERS_TASK" FOREIGN KEY ("WRITTEN_BY_TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "CK_PAPERS_KIND" CHECK ("KIND" IN ('plan','contract','write_log','build_state','missing','report','artefact','compaction','tool_result','consult','packet','brief','branch_set','sign_out','summary','arrival','verdict','handover')),
        CONSTRAINT "CK_PAPERS_TRUST" CHECK ("TRUST_CLASS" IN ('trusted','derived','untrusted','absent')),
        CONSTRAINT "CK_PAPERS_LEN" CHECK ("BYTE_LENGTH" >= 0 AND "REVISION_NO" > 0)
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_PAPERS_CASE" ON "SRD_SUPPORT"."PAPERS" ("CASE_ID", "KIND", "NAME")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_PAPERS_HASH" ON "SRD_SUPPORT"."PAPERS" ("CONTENT_HASH")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."PAPERS" IS 'Agent Runtime § 11.2. A paper is rewritten many times per case: each rewrite is a new immutable row (REVISION_NO). KIND names the fixed paper set plus the module contracts (contract = the semantic contract: whitelabel spec / fix packet / change packet). Every read and write is a PAPER_ACCESS ledger event.'~');

    -- ---------------------------------------------------------------- WRITE_LOG (append-only)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."WRITE_LOG" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "TASK_ID"                   NVARCHAR2(64)   NOT NULL,
        "PLAN_STEP_ID"              NVARCHAR2(64),
        "GRANT_ID"                  NVARCHAR2(64),
        "SHAPE_ID"                  NVARCHAR2(64),
        "SHAPE_VERSION"             NUMBER(10),
        "ENVIRONMENT"               NVARCHAR2(32)   NOT NULL,
        "SYSTEM"                    NVARCHAR2(32)   NOT NULL,
        "TARGET"                    NVARCHAR2(512)  NOT NULL,
        "MODE"                      NVARCHAR2(8)    NOT NULL,
        "WRITE_PATH"                NVARCHAR2(16)   NOT NULL,
        "STATEMENT"                 CLOB            NOT NULL,
        "STATEMENT_HASH"            NVARCHAR2(64)   NOT NULL,
        "EXPECTED_ROWS"             NUMBER(10),
        "ACTUAL_ROWS"               NUMBER(10),
        "OUTCOME"                   NVARCHAR2(16)   NOT NULL,
        "REFUSAL_REASON"            NVARCHAR2(1000),
        "WRITE_SHAPE_KIND"          NVARCHAR2(1),
        "LEASE_ID"                  NVARCHAR2(64),
        "VERIFICATION"              CLOB,
        "TEARDOWN_OF_ID"            NVARCHAR2(64),
        "INTENDED_ON_UTC"           TIMESTAMP(6)    NOT NULL,
        "EXECUTED_ON_UTC"           TIMESTAMP(6),
        "LEDGER_RECORD_ID"          NVARCHAR2(64)   NOT NULL,
        CONSTRAINT "PK_WRITE_LOG" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_WLOG_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_WLOG_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "FK_WLOG_STEP" FOREIGN KEY ("PLAN_STEP_ID") REFERENCES "SRD_SUPPORT"."PLAN_STEPS" ("ID"),
        CONSTRAINT "FK_WLOG_GRANT" FOREIGN KEY ("GRANT_ID") REFERENCES "SRD_SUPPORT"."GRANTS" ("ID"),
        CONSTRAINT "FK_WLOG_SHAPE" FOREIGN KEY ("SHAPE_ID") REFERENCES "SRD_SUPPORT"."WRITE_SHAPES" ("ID"),
        CONSTRAINT "FK_WLOG_LEASE" FOREIGN KEY ("LEASE_ID") REFERENCES "SRD_SUPPORT"."WRITE_LEASES" ("ID"),
        CONSTRAINT "FK_WLOG_TEARDOWN" FOREIGN KEY ("TEARDOWN_OF_ID") REFERENCES "SRD_SUPPORT"."WRITE_LOG" ("ID"),
        CONSTRAINT "CK_WLOG_MODE" CHECK ("MODE" IN ('dry_run','write','send','ddl')),
        CONSTRAINT "CK_WLOG_PATH" CHECK ("WRITE_PATH" IN ('endpoint','statement','gateway','ui_action','send')),
        CONSTRAINT "CK_WLOG_OUTCOME" CHECK ("OUTCOME" IN ('intended','executed','refused','failed','reverted')),
        CONSTRAINT "CK_WLOG_SHAPE_KIND" CHECK ("WRITE_SHAPE_KIND" IS NULL OR "WRITE_SHAPE_KIND" IN ('A','B')),
        CONSTRAINT "CK_WLOG_VERIF_JSON" CHECK ("VERIFICATION" IS NULL OR "VERIFICATION" IS JSON),
        CONSTRAINT "CK_WLOG_EXECUTED" CHECK ("OUTCOME" <> 'executed' OR ("EXECUTED_ON_UTC" IS NOT NULL AND "ACTUAL_ROWS" IS NOT NULL)),
        CONSTRAINT "CK_WLOG_GRANTED" CHECK ("MODE" = 'dry_run' OR "OUTCOME" IN ('intended','refused') OR "GRANT_ID" IS NOT NULL)
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_WLOG_CASE" ON "SRD_SUPPORT"."WRITE_LOG" ("CASE_ID", "INTENDED_ON_UTC")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_WLOG_TARGET" ON "SRD_SUPPORT"."WRITE_LOG" ("ENVIRONMENT", "SYSTEM", "TARGET")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."WRITE_LOG" IS 'Trust and Data § 5: statement, target, environment, mode, row count — never result rows. WRITE_PATH records which path the write took (D136: endpoint first where the system has one, guarded statement otherwise, gateway for rate publication, ui_action for surfaces without a write API). CK_WLOG_GRANTED: nothing executes without a grant.'~');

    -- ---------------------------------------------------------------- BUILD_STATE
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."BUILD_STATE" (
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "STEP_ID"                   NVARCHAR2(64)   NOT NULL,
        "ATTEMPT_NO"                NUMBER(5)       DEFAULT 1 NOT NULL,
        "RETURNED_IDS"              CLOB,
        "COMPLETED_ON_UTC"          TIMESTAMP(6)    NOT NULL,
        "COMPLETED_BY_TASK_ID"      NVARCHAR2(64)   NOT NULL,
        CONSTRAINT "PK_BUILD_STATE" PRIMARY KEY ("CASE_ID", "STEP_ID"),
        CONSTRAINT "FK_BUILD_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_BUILD_TASK" FOREIGN KEY ("COMPLETED_BY_TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "CK_BUILD_IDS_JSON" CHECK ("RETURNED_IDS" IS NULL OR "RETURNED_IDS" IS JSON)
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."BUILD_STATE" IS 'Completed step ids + platform-returned ids (source → target map). Resume skips what is here; children re-parent onto RETURNED_IDS, never predicted ones (Agent Runtime § 11.2, the 9951 step-41 resume).'~');

    -- ---------------------------------------------------------------- MISSING
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."MISSING" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "WHAT"                      NVARCHAR2(1000) NOT NULL,
        "WHY_NEEDED"                NVARCHAR2(2000) NOT NULL,
        "EXPECTED_SOURCE"           NVARCHAR2(512),
        "BLOCKING"                  NUMBER(1)       NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "RAISED_BY_TASK_ID"         NVARCHAR2(64)   NOT NULL,
        "RAISED_ON_UTC"             TIMESTAMP(6)    NOT NULL,
        "RESOLVED_ON_UTC"           TIMESTAMP(6),
        "ANSWER_PAPER_ID"           NVARCHAR2(64),
        CONSTRAINT "PK_MISSING" PRIMARY KEY ("ID"),
        CONSTRAINT "FK_MISSING_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_MISSING_TASK" FOREIGN KEY ("RAISED_BY_TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
        CONSTRAINT "FK_MISSING_ANSWER" FOREIGN KEY ("ANSWER_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID"),
        CONSTRAINT "CK_MISSING_BLOCKING" CHECK ("BLOCKING" IN (0,1)),
        CONSTRAINT "CK_MISSING_STATE" CHECK ("STATE" IN ('open','answered','accepted_gap','withdrawn'))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_MISSING_CASE" ON "SRD_SUPPORT"."MISSING" ("CASE_ID", "STATE")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."MISSING" IS 'Deliberate omissions and unresolved questions: missing information is a deliverable, not a failure (Agent Runtime § 11.2). H1 refuses while a BLOCKING = 1 row is open (validate_product.py check_h1 is the Configuration instance of the same rule).'~');

    -- ---------------------------------------------------------------- PLSQL_SNAPSHOTS (append-only)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."PLSQL_SNAPSHOTS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "ENVIRONMENT"               NVARCHAR2(32)   NOT NULL,
        "SCHEMA_NAME"               NVARCHAR2(128)  NOT NULL,
        "OBJECT_NAME"               NVARCHAR2(128)  NOT NULL,
        "OBJECT_TYPE"               NVARCHAR2(32)   NOT NULL,
        "SIGNATURE"                 NVARCHAR2(64)   NOT NULL,
        "SIGNATURE_ALGORITHM"       NVARCHAR2(32)   NOT NULL,
        "SOURCE_HASH"               NVARCHAR2(64)   NOT NULL,
        "SOURCE_LINES"              NUMBER(10)      NOT NULL,
        "MIRROR_COMMIT_SHA"         NVARCHAR2(64),
        "OBSERVED_LAST_DDL_UTC"     TIMESTAMP(6),
        "OBSERVED_STATUS"           NVARCHAR2(16),
        "CAPTURED_BY_CASE_ID"       NVARCHAR2(64),
        "CAPTURED_ON_UTC"           TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_PLSQL_SNAPSHOTS" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_PLSQL_SNAPSHOTS" UNIQUE ("ENVIRONMENT", "SCHEMA_NAME", "OBJECT_NAME", "OBJECT_TYPE", "SIGNATURE"),
        CONSTRAINT "FK_PLSQL_CASE" FOREIGN KEY ("CAPTURED_BY_CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "CK_PLSQL_TYPE" CHECK ("OBJECT_TYPE" IN ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TRIGGER','TYPE','TYPE BODY','VIEW')),
        CONSTRAINT "CK_PLSQL_STATUS" CHECK ("OBSERVED_STATUS" IS NULL OR "OBSERVED_STATUS" IN ('VALID','INVALID'))
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_PLSQL_OBJECT" ON "SRD_SUPPORT"."PLSQL_SNAPSHOTS" ("ENVIRONMENT", "SCHEMA_NAME", "OBJECT_NAME", "CAPTURED_ON_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."PLSQL_SNAPSHOTS" IS 'Stage Planning § 3b: the 95 packages not in version control. SIGNATURE = SHA-256 over the canonical source (algorithm named in SIGNATURE_ALGORITHM, defined in Stage Planning § 3c) and is the precondition of a PL/SQL write grant; a stale extract is refused before a session opens. MIRROR_COMMIT_SHA = the commit in the agent-owned git mirror.'~');

    -- ---------------------------------------------------------------- CASE_KEYS · CASE_HANDLES (the cryptographic data boundary)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."CASE_KEYS" (
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "KEY_VERSION"               NUMBER(5)       NOT NULL,
        "WRAPPED_DATA_KEY"          RAW(512)        NOT NULL,
        "TRANSIT_KEY_REF"           NVARCHAR2(256)  NOT NULL,
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        "DESTROYED_ON_UTC"          TIMESTAMP(6),
        "DESTROYED_BY"              NVARCHAR2(128),
        CONSTRAINT "PK_CASE_KEYS" PRIMARY KEY ("CASE_ID", "KEY_VERSION"),
        CONSTRAINT "FK_CASEKEYS_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID")
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CASE_KEYS" IS 'The per-case data key, wrapped by a Vault transit key (Software Architecture § Challenges, handles table). Destroying the key at customer closure crypto-shreds every CASE_HANDLES value, backups included.'~');

    ddl(q'~CREATE TABLE "SRD_SUPPORT"."CASE_HANDLES" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "HANDLE"                    NVARCHAR2(64)   NOT NULL,
        "KIND"                      NVARCHAR2(16)   NOT NULL,
        "VALUE_ENC"                 BLOB            NOT NULL,
        "NONCE"                     RAW(16)         NOT NULL,
        "KEY_VERSION"               NUMBER(5)       NOT NULL,
        "STRUCTURAL_FACETS"         CLOB,
        "FIRST_SEEN_CHANNEL"        NVARCHAR2(32),
        "CREATED_ON_UTC"            TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_CASE_HANDLES" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_CASE_HANDLES" UNIQUE ("CASE_ID", "HANDLE"),
        CONSTRAINT "FK_HANDLES_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
        CONSTRAINT "FK_HANDLES_KEY" FOREIGN KEY ("CASE_ID", "KEY_VERSION") REFERENCES "SRD_SUPPORT"."CASE_KEYS" ("CASE_ID", "KEY_VERSION"),
        CONSTRAINT "CK_HANDLES_KIND" CHECK ("KIND" IN ('EGN','EIK','VIN','POLICY_NO','CLAIM_NO','IBAN','NAME','EMAIL','PHONE','ADDRESS','OTHER')),
        CONSTRAINT "CK_HANDLES_FACETS_JSON" CHECK ("STRUCTURAL_FACETS" IS NULL OR "STRUCTURAL_FACETS" IS JSON)
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CASE_HANDLES" IS 'Trust and Data § 4: identifiers are replaced by handles at the connector; VALUE_ENC is AES-256-GCM ciphertext under the case data key, so Oracle, its backups and a DBA SELECT hold ciphertext. STRUCTURAL_FACETS (length, checksum_ok, prefix) let a malformed identifier be found without seeing it. Re-identification is a REIDENTIFY ledger event inside the connector process.'~');
END;
/
