Hashes on your disk

Local ledger

A shape you build, not a file we hand you. It is a SQLite table your own process appends to, one row per event, hashes not payloads. It keeps working when the network does not, and it reconciles against the hosted record when the network returns.

What it does

Two verifiers, two questions

The signature verifier asks whether the bytes of an event changed after signing. The chain verifier asks whether the event still sits where it was written. A reordered but unedited ledger passes the first and fails the second. Run both.

Reconcile against the hosted record

Reconcile compares the local head with the last hosted settlement block for the same machine and writes an anchor row when they agree. A disagreement is reported with the first differing sequence, not repaired silently.

Check: append a row locally with the network off, reconnect, reconcile, and the anchor names the hosted block that now covers it.

Schema

The whole schema is below. There is no file to fetch: this page is the delivery, so read it, copy it, and run it against a SQLite file you own. Nothing here calls us.

-- AFA Protocol: reference local ledger (SQLite).
--
-- This is the store shape we recommend for the ledger you keep on your own
-- machine. It is the same shape our own local ledger converged on after its
-- chain-break count was measured at 7.98% and driven to zero. Every rule
-- below exists because its absence produced a defect we recorded.
--
-- Rules, in plain language:
--
--   1. WAL mode. Readers never block the single writer and a crash mid-write
--      leaves the previous state intact.
--   2. Every append runs under BEGIN IMMEDIATE. The appender takes the write
--      lock BEFORE it reads the chain tip, so two concurrent appenders cannot
--      both read the same tip and both link to it. A bare read-then-write of
--      the tip forks the chain under concurrency; the lock is what made our
--      own fork count zero.
--   3. `seq` is insertion order and belongs to the store. It is separate from
--      any sequence number a client puts on its own events (`client_sequence`).
--      Two writers can each believe they hold "sequence 7"; only one of them
--      is row 7.
--   4. The payload is NOT stored. `payload_hash` is a SHA-256 over the
--      canonical JSON of the payload. Anyone who can read this file learns
--      that something happened, when, and by whom, not what was said.
--   5. `prev_hash` is the chain hash of the previous row in `seq` order, and
--      `chain_hash` is a SHA-256 over the canonical JSON of this row's chained
--      fields INCLUDING `prev_hash`. Altering an old row changes its chain
--      hash, and the next row's `prev_hash` no longer matches it.
--   6. `chain_state` is a single row holding the tip. It is written in the
--      SAME transaction as the insert, so the tip can never point at a row
--      that does not exist and a row can never exist that the tip missed.
--   7. `malformed_lines` is quarantine. A reader that swallows a parse error
--      and moves on is more broken than the data it skipped. Anything that
--      cannot be parsed is stored here with the reason, never discarded.
--   8. `anchors` holds hosted checkpoint receipts: the hosted record's root
--      and event id at the moment you anchored, so a later reconcile can say
--      which side moved.
--
-- Two verifiers read this file, and both are required:
--   verify_signatures: were the bytes of a row altered after it was signed?
--   verify_chain:      is every row still in its original position?
-- A green signature check never means an intact chain. Reordered rows carry
-- valid signatures. Run both.

PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS store_meta (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS events (
    seq                INTEGER PRIMARY KEY AUTOINCREMENT,
    event_id           TEXT    NOT NULL UNIQUE,
    event_type         TEXT    NOT NULL,
    tool               TEXT,
    decision           TEXT,
    parent_id          TEXT,
    client_sequence    INTEGER,
    prev_hash          TEXT    NOT NULL,
    payload_hash       TEXT    NOT NULL,
    chain_hash         TEXT    NOT NULL UNIQUE,
    signature          TEXT,
    signer_fingerprint TEXT,
    surface            TEXT    NOT NULL
        CHECK (surface IN ('rest', 'mcp', 'mcp_stdio', 'sdk', 'cli', 'hook', 'dashboard')),
    created_at         TEXT    NOT NULL
);

CREATE INDEX IF NOT EXISTS events_by_type    ON events (event_type);
CREATE INDEX IF NOT EXISTS events_by_created ON events (created_at);

CREATE TABLE IF NOT EXISTS chain_state (
    id         INTEGER PRIMARY KEY CHECK (id = 1),
    tip_hash   TEXT    NOT NULL,
    max_seq    INTEGER NOT NULL,
    updated_at TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS malformed_lines (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    raw_line       TEXT NOT NULL,
    reason         TEXT NOT NULL,
    quarantined_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS anchors (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    checkpoint_id   TEXT NOT NULL UNIQUE,
    root            TEXT NOT NULL,
    hosted_event_id TEXT,
    received_at     TEXT NOT NULL
);