AISA v2.0 / Technical documentation / 003-evidence.sql
003-evidence.sql
SQL · 191 lines · 14,704 bytes · wiki path 10 Architecture/db/SrdSupport/003-evidence.sql · download the raw file · cited from Data Model — `SRD_SUPPORT`
Same folder: 001-work-and-planning.sql · 002-decisions-and-grants.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
-- 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;
/