SET DEFINE OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- 004 · LEDGER — Architecture § 3.2; Trust and Data § 5; Delivery § 4 (RPO 15 min).
-- Append-only and tamper-evident IN THE DATABASE, because the service is the thing being audited:
--   * record_hash = SHA-256( prev_hash || canonical(record) ), canonical form SERDICA-JCS-1 from AI.Contracts
--   * the chain is a constraint, not a convention: PREV_HASH is UNIQUE and must equal an existing RECORD_HASH
--     (or the genesis constant), so no two records can claim the same predecessor and no record can be
--     inserted "beside" the chain. A racing insert fails with ORA-00001 and the sealer retries — no lock needed.
--   * every 15 minutes the head is sealed (LEDGER_SEALS) and exported off-database (LEDGER_EXPORTS);
--     verify-ledger.sql compares the chain head against the last exported seal.
-- Immutability triggers are in 008.

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
    -- ---------------------------------------------------------------- AUDIT_LEDGER
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."AUDIT_LEDGER" (
        "SEQ_NO"                    NUMBER(19)      GENERATED ALWAYS AS IDENTITY NOT NULL,
        "RECORD_ID"                 NVARCHAR2(64)   NOT NULL,
        "KIND"                      NVARCHAR2(32)   NOT NULL,
        "OCCURRED_ON_UTC"           TIMESTAMP(6)    NOT NULL,
        "RECORDED_ON_UTC"           TIMESTAMP(6)    DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL,
        "CASE_ID"                   NVARCHAR2(64),
        "TASK_ID"                   NVARCHAR2(64),
        "TASK_PATH"                 NVARCHAR2(1000),
        "ACTOR_KIND"                NVARCHAR2(8)    NOT NULL,
        "ACTOR_ID"                  NVARCHAR2(128)  NOT NULL,
        "PROFILE_KEY"               NVARCHAR2(128),
        "PROFILE_VERSION"           NUMBER(10),
        "PROMPT_VERSION"            NUMBER(10),
        "ROUTE_VERSION"             NUMBER(10),
        "FRAMEWORK_VERSION"         NVARCHAR2(128)  NOT NULL,
        "PLAN_REVISION_ID"          NVARCHAR2(64),
        "PARENT_DECISION_ID"        NVARCHAR2(64),
        "POLICY_REF"                NVARCHAR2(256),
        "GRANT_ID"                  NVARCHAR2(64),
        "ENVIRONMENT"               NVARCHAR2(32),
        "SYSTEM"                    NVARCHAR2(32),
        "TARGET"                    NVARCHAR2(512),
        "MODE"                      NVARCHAR2(8),
        "REQUESTED_ACTION"          NVARCHAR2(2000),
        "ACTUAL_ACTION"             NVARCHAR2(2000),
        "ROW_COUNT"                 NUMBER(10),
        "VERIFICATION_RESULT"       NVARCHAR2(16),
        "TRUST_CLASS"               NVARCHAR2(16),
        "PAYLOAD_HASH"              NVARCHAR2(64),
        "ARTEFACT_HASHES"           CLOB,
        "SUPERSEDES_RECORD_ID"      NVARCHAR2(64),
        "CANONICAL_FORM"            NVARCHAR2(32)   DEFAULT 'SERDICA-JCS-1' NOT NULL,
        "PREV_HASH"                 NVARCHAR2(64)   NOT NULL,
        "RECORD_HASH"               NVARCHAR2(64)   NOT NULL,
        CONSTRAINT "PK_AUDIT_LEDGER" PRIMARY KEY ("SEQ_NO"),
        CONSTRAINT "UK_LEDGER_RECORD_ID" UNIQUE ("RECORD_ID"),
        CONSTRAINT "UK_LEDGER_RECORD_HASH" UNIQUE ("RECORD_HASH"),
        CONSTRAINT "UK_LEDGER_PREV_HASH" UNIQUE ("PREV_HASH"),
        CONSTRAINT "CK_LEDGER_ACTOR" CHECK ("ACTOR_KIND" IN ('agent','person','policy','system')),
        CONSTRAINT "CK_LEDGER_KIND" CHECK ("KIND" IN (
            'CASE_OPENED','CASE_STATE','CASE_ROUTED','CASE_MERGED','CASE_CLOSED',
            'TASK_SPAWNED','TASK_STATE','TURN','MESSAGE','TOOL_CALL','TOOL_RESULT','TOOL_REFUSED',
            'GATE_OPENED','GATE_DECIDED','GATE_ESCALATED','GRANT_ISSUED','GRANT_EXPIRED','GRANT_REVOKED',
            'WRITE_INTENDED','WRITE_EXECUTED','WRITE_REFUSED','WRITE_FAILED','VERIFY','REVERT','PREFLIGHT',
            'SHAPE_STATE','RISK_ACCEPTED','RISK_WITHDRAWN',
            'PAPER_ACCESS','MEMORY_READ','MEMORY_WRITE','MEMORY_STATE','REIDENTIFY','HANDLE_DESTROYED',
            'CONSULT','HANDOVER','EXIT_TO_ESTATE',
            'GOVERNANCE','EVAL_RUN','ROUTE_CHANGED','FRAMEWORK_CHANGED',
            'CONNECTOR_HEALTH','BUDGET_WARNING','BUDGET_STOP','ALERT','KILL_SWITCH',
            'SEAL','EXPORT','RESTORE','RETENTION','CORRECTION')),
        CONSTRAINT "CK_LEDGER_MODE" CHECK ("MODE" IS NULL OR "MODE" IN ('read','dry_run','write','send','ddl')),
        CONSTRAINT "CK_LEDGER_TRUST" CHECK ("TRUST_CLASS" IS NULL OR "TRUST_CLASS" IN ('trusted','derived','untrusted','absent')),
        CONSTRAINT "CK_LEDGER_VERIF" CHECK ("VERIFICATION_RESULT" IS NULL OR "VERIFICATION_RESULT" IN ('pass','fail','unverifiable','skipped')),
        CONSTRAINT "CK_LEDGER_HASHES" CHECK (LENGTH("PREV_HASH") = 64 AND LENGTH("RECORD_HASH") = 64 AND "PREV_HASH" <> "RECORD_HASH"),
        CONSTRAINT "CK_LEDGER_ART_JSON" CHECK ("ARTEFACT_HASHES" IS NULL OR "ARTEFACT_HASHES" IS JSON),
        CONSTRAINT "CK_LEDGER_CORRECTION" CHECK ("KIND" <> 'CORRECTION' OR "SUPERSEDES_RECORD_ID" IS NOT NULL),
        CONSTRAINT "CK_LEDGER_WRITE_COUNT" CHECK ("KIND" <> 'WRITE_EXECUTED' OR "ROW_COUNT" IS NOT NULL)
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_LEDGER_CASE" ON "SRD_SUPPORT"."AUDIT_LEDGER" ("CASE_ID", "SEQ_NO")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_LEDGER_TASK" ON "SRD_SUPPORT"."AUDIT_LEDGER" ("TASK_ID", "SEQ_NO")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_LEDGER_KIND_TIME" ON "SRD_SUPPORT"."AUDIT_LEDGER" ("KIND", "OCCURRED_ON_UTC")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_LEDGER_SUPERSEDES" ON "SRD_SUPPORT"."AUDIT_LEDGER" ("SUPERSEDES_RECORD_ID")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."AUDIT_LEDGER" IS 'Architecture § 3.2. One row per action (contract: contracts/ledger-record.schema.json). Genesis PREV_HASH = 64 zeros. UK_LEDGER_PREV_HASH + FK_LEDGER_CHAIN (008) make the chain a constraint. Payload hashes only — never payloads, never result rows. Metrics are parsed from here by tools, never counted by agents (CR-1).'~');

    -- ---------------------------------------------------------------- LEDGER_SEALS (immutable)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."LEDGER_SEALS" (
        "SEAL_NO"                   NUMBER(19)      GENERATED ALWAYS AS IDENTITY NOT NULL,
        "HEAD_SEQ_NO"               NUMBER(19)      NOT NULL,
        "HEAD_RECORD_HASH"          NVARCHAR2(64)   NOT NULL,
        "RECORD_COUNT"              NUMBER(19)      NOT NULL,
        "PREV_SEAL_HASH"            NVARCHAR2(64)   NOT NULL,
        "SEAL_HASH"                 NVARCHAR2(64)   NOT NULL,
        "SIGNATURE_ALG"             NVARCHAR2(16),
        "SIGNATURE"                 NVARCHAR2(1024),
        "SEALED_BY"                 NVARCHAR2(128)  NOT NULL,
        "SEALED_ON_UTC"             TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_LEDGER_SEALS" PRIMARY KEY ("SEAL_NO"),
        CONSTRAINT "UK_LEDGER_SEALS_HEAD" UNIQUE ("HEAD_SEQ_NO"),
        CONSTRAINT "UK_LEDGER_SEALS_HASH" UNIQUE ("SEAL_HASH"),
        CONSTRAINT "FK_SEALS_HEAD" FOREIGN KEY ("HEAD_SEQ_NO") REFERENCES "SRD_SUPPORT"."AUDIT_LEDGER" ("SEQ_NO"),
        CONSTRAINT "CK_SEALS_COUNT" CHECK ("RECORD_COUNT" > 0),
        CONSTRAINT "CK_SEALS_SIG" CHECK ("SIGNATURE_ALG" IS NULL OR "SIGNATURE_ALG" IN ('ES256'))
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."LEDGER_SEALS" IS 'The chain head sealed on ledger.seal_interval (15 min, D45/D66): head hash, count, timestamp; seals are themselves chained (PREV_SEAL_HASH). Signed with ES256 when Es256PayloadSealSigner is wired (Software Architecture § 9.1). Owned by the ISealer hosted job, not ambient.'~');

    -- ---------------------------------------------------------------- LEDGER_EXPORTS (append-only attempts)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."LEDGER_EXPORTS" (
        "ID"                        NVARCHAR2(64)   NOT NULL,
        "SEAL_NO"                   NUMBER(19)      NOT NULL,
        "ATTEMPT_NO"                NUMBER(5)       NOT NULL,
        "FROM_SEQ_NO"               NUMBER(19)      NOT NULL,
        "TO_SEQ_NO"                 NUMBER(19)      NOT NULL,
        "STATE"                     NVARCHAR2(16)   NOT NULL,
        "TARGET_KIND"               NVARCHAR2(16)   NOT NULL,
        "TARGET_REF"                NVARCHAR2(512),
        "BYTES"                     NUMBER(19),
        "ERROR"                     NVARCHAR2(2000),
        "ATTEMPTED_ON_UTC"          TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_LEDGER_EXPORTS" PRIMARY KEY ("ID"),
        CONSTRAINT "UK_LEDGER_EXPORTS" UNIQUE ("SEAL_NO", "ATTEMPT_NO"),
        CONSTRAINT "FK_EXPORTS_SEAL" FOREIGN KEY ("SEAL_NO") REFERENCES "SRD_SUPPORT"."LEDGER_SEALS" ("SEAL_NO"),
        CONSTRAINT "CK_EXPORTS_STATE" CHECK ("STATE" IN ('exported','failed')),
        CONSTRAINT "CK_EXPORTS_TARGET" CHECK ("TARGET_KIND" IN ('gitlab','object_store','file')),
        CONSTRAINT "CK_EXPORTS_RANGE" CHECK ("TO_SEQ_NO" >= "FROM_SEQ_NO")
    )~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."LEDGER_EXPORTS" IS 'Each export attempt of a seal plus the delta since the previous seal, as canonical JSONL, to a store the database account cannot reach (the agent-owned GitLab repository, D45). A seal without an exported row older than one interval is the ledger-lag alert (D66).'~');

    -- ---------------------------------------------------------------- COST_LEDGER (append-only metering; a control, not a metric — D117)
    ddl(q'~CREATE TABLE "SRD_SUPPORT"."COST_LEDGER" (
        "SEQ_NO"                    NUMBER(19)      GENERATED ALWAYS AS IDENTITY NOT NULL,
        "LEDGER_RECORD_ID"          NVARCHAR2(64)   NOT NULL,
        "CASE_ID"                   NVARCHAR2(64)   NOT NULL,
        "TASK_ID"                   NVARCHAR2(64)   NOT NULL,
        "TURN_NO"                   NUMBER(10),
        "PROFILE_KEY"               NVARCHAR2(128)  NOT NULL,
        "ROUTE_KEY"                 NVARCHAR2(128)  NOT NULL,
        "ROUTE_VERSION"             NUMBER(10)      NOT NULL,
        "PROVIDER_CODE"             NVARCHAR2(32)   NOT NULL,
        "DEPLOYMENT_NAME"           NVARCHAR2(128),
        "RESOLVED_MODEL"            NVARCHAR2(128),
        "DATA_ZONE"                 NVARCHAR2(16)   NOT NULL,
        "INPUT_TOKENS"              NUMBER(19)      DEFAULT 0 NOT NULL,
        "OUTPUT_TOKENS"             NUMBER(19)      DEFAULT 0 NOT NULL,
        "CACHED_TOKENS"             NUMBER(19)      DEFAULT 0 NOT NULL,
        "DURATION_MS"               NUMBER(19)      NOT NULL,
        "MINUTES_CHARGED"           NUMBER(10,2)    NOT NULL,
        "PRICE_PIN_REF"             NVARCHAR2(128),
        "KNOWN_COST_AMOUNT"         DECIMAL(20,8),
        "CURRENCY_CODE"             NVARCHAR2(3),
        "RECORDED_ON_UTC"           TIMESTAMP(6)    NOT NULL,
        CONSTRAINT "PK_COST_LEDGER" PRIMARY KEY ("SEQ_NO"),
        CONSTRAINT "FK_COST_LEDGER_REC" FOREIGN KEY ("LEDGER_RECORD_ID") REFERENCES "SRD_SUPPORT"."AUDIT_LEDGER" ("RECORD_ID"),
        CONSTRAINT "CK_COST_ZONE" CHECK ("DATA_ZONE" IN ('EU','GLOBAL','LOCAL')),
        CONSTRAINT "CK_COST_TOKENS" CHECK ("INPUT_TOKENS" >= 0 AND "OUTPUT_TOKENS" >= 0 AND "CACHED_TOKENS" >= 0 AND "DURATION_MS" >= 0)
    )~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_COST_CASE" ON "SRD_SUPPORT"."COST_LEDGER" ("CASE_ID", "RECORDED_ON_UTC")~');
    ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_COST_ROUTE" ON "SRD_SUPPORT"."COST_LEDGER" ("ROUTE_KEY", "ROUTE_VERSION", "RECORDED_ON_UTC")~');
    ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."COST_LEDGER" IS 'Provider usage per turn, extending the AiCostLedgerStore/AiProviderAttemptStore pattern (Architecture § 3.1). MINUTES_CHARGED is what the budget consumes (D135: budget is time); tokens and cost are a control and an attribution (which route produced which case — Software Architecture § 7.1), never a metric shown to people (D117). DATA_ZONE records residency per call (SG-9).'~');
END;
/
