verify-ledger.sql

SQL · 72 lines · 3,774 bytes · wiki path 10 Architecture/db/SrdSupport/verify-ledger.sql · download the raw file · cited from Data Model — `SRD_SUPPORT`

Same folder: 001-work-and-planning.sql · 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-schema.sql

SET DEFINE OFF
SET SERVEROUTPUT ON
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- verify-ledger.sql — Architecture § 3.2: "verify-ledger.sql compares the chain head against the last exported seal".
-- Three checks, each a hard failure:
--   1. link continuity — every record's PREV_HASH is the RECORD_HASH of the record before it (by SEQ_NO);
--   2. seal integrity — every seal's HEAD_RECORD_HASH is the RECORD_HASH at its HEAD_SEQ_NO and its RECORD_COUNT is that SEQ_NO;
--   3. export currency — the newest exported seal is not older than two seal intervals (one interval late = the ledger-lag
--      alert, D66; two = this script fails, because the RPO is the audit gap — Delivery § 4).
-- Recomputing RECORD_HASH itself needs the SERDICA-JCS-1 canonicaliser (AI.Contracts) and is the exporter's job; this script
-- checks the chain's SHAPE from inside the database, which is what a DBA-side rewrite would have to fake.

DECLARE
    v_breaks      INTEGER;
    v_bad_seals   INTEGER;
    v_head_seq    NUMBER(19);
    v_head_hash   NVARCHAR2(64);
    v_last_seal   NUMBER(19);
    v_last_export TIMESTAMP(6);
    v_interval    INTERVAL DAY TO SECOND := INTERVAL '15' MINUTE;
    v_interval_s  VARCHAR2(64);
BEGIN
    -- 1. link continuity
    SELECT COUNT(*) INTO v_breaks
      FROM (SELECT "PREV_HASH", LAG("RECORD_HASH") OVER (ORDER BY "SEQ_NO") AS expected_prev, "SEQ_NO"
              FROM "SRD_SUPPORT"."AUDIT_LEDGER")
     WHERE expected_prev IS NOT NULL AND "PREV_HASH" <> expected_prev;
    IF v_breaks > 0 THEN
        RAISE_APPLICATION_ERROR(-20080, 'ledger chain broken: ' || v_breaks || ' record(s) whose PREV_HASH is not the previous RECORD_HASH');
    END IF;

    -- 2. seal integrity
    SELECT COUNT(*) INTO v_bad_seals
      FROM "SRD_SUPPORT"."LEDGER_SEALS" s
      JOIN "SRD_SUPPORT"."AUDIT_LEDGER" l ON l."SEQ_NO" = s."HEAD_SEQ_NO"
     WHERE s."HEAD_RECORD_HASH" <> l."RECORD_HASH" OR s."RECORD_COUNT" <> s."HEAD_SEQ_NO";
    IF v_bad_seals > 0 THEN
        RAISE_APPLICATION_ERROR(-20081, 'ledger seals disagree with the chain: ' || v_bad_seals || ' seal(s)');
    END IF;

    -- 3. export currency
    SELECT MAX("SEQ_NO") INTO v_head_seq FROM "SRD_SUPPORT"."AUDIT_LEDGER";
    IF v_head_seq IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('verify-ledger: empty ledger — nothing to verify');
        RETURN;
    END IF;
    SELECT "RECORD_HASH" INTO v_head_hash FROM "SRD_SUPPORT"."AUDIT_LEDGER" WHERE "SEQ_NO" = v_head_seq;

    BEGIN
        SELECT JSON_VALUE("VALUE", '$') INTO v_interval_s
          FROM "SRD_SUPPORT"."POLICY_VALUES"
         WHERE "SCOPE" = 'platform' AND "KEY" = 'ledger.export_interval' AND "STATE" = 'published'
         ORDER BY "VERSION" DESC FETCH FIRST 1 ROW ONLY;
        v_interval := TO_DSINTERVAL(v_interval_s);
    EXCEPTION WHEN OTHERS THEN NULL; -- keep the 15-minute default
    END;

    SELECT MAX(s."SEAL_NO"), MAX(e."ATTEMPTED_ON_UTC") INTO v_last_seal, v_last_export
      FROM "SRD_SUPPORT"."LEDGER_SEALS" s
      JOIN "SRD_SUPPORT"."LEDGER_EXPORTS" e ON e."SEAL_NO" = s."SEAL_NO" AND e."STATE" = 'exported';

    IF v_last_seal IS NULL THEN
        RAISE_APPLICATION_ERROR(-20082, 'ledger has ' || v_head_seq || ' records and no exported seal — the off-database copy does not exist');
    END IF;
    IF v_last_export < SYS_EXTRACT_UTC(SYSTIMESTAMP) - (v_interval * 2) THEN
        RAISE_APPLICATION_ERROR(-20083, 'last exported seal ' || v_last_seal || ' at ' || TO_CHAR(v_last_export, 'YYYY-MM-DD HH24:MI:SS') || ' UTC is older than two export intervals');
    END IF;

    DBMS_OUTPUT.PUT_LINE('verify-ledger: OK — head SEQ_NO ' || v_head_seq || ' hash ' || v_head_hash || '; last exported seal ' || v_last_seal || ' at ' || TO_CHAR(v_last_export, 'YYYY-MM-DD HH24:MI:SS') || ' UTC');
END;
/