AISA v2.0 / Technical documentation / 004-ledger.sql
004-ledger.sql
SQL · 164 lines · 12,054 bytes · wiki path 10 Architecture/db/SrdSupport/004-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 · 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
-- 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;
/