-- The ledger (spec §9.1, §9.5): every event, in order, chained by hash.

-- Columns are the frozen envelope; `pruned` marks a stub whose payload and

-- proof were removed by `spectra admin prune` (its hash and prev stay).

CREATE TABLE events (

    seq             INTEGER PRIMARY KEY,

    id              TEXT NOT NULL UNIQUE,

    at_ms           INTEGER NOT NULL,

    hlc_wall        INTEGER NOT NULL,

    hlc_logical     INTEGER NOT NULL,

    prev            BLOB NOT NULL,

    hash            BLOB NOT NULL,

    auth_key        BLOB,

    auth_method     INTEGER NOT NULL,

    auth_assurance  INTEGER NOT NULL,

    auth_proof      BLOB,

    kind            TEXT NOT NULL,

    actor           TEXT NOT NULL,

    session         TEXT,

    project         TEXT,

    subject         TEXT,

    payload         BLOB,

    trace           TEXT,

    pruned          INTEGER NOT NULL DEFAULT 0

);

CREATE INDEX events_kind ON events(kind);

CREATE INDEX events_subject ON events(subject);

-- Facts about this database: the ledger format it was written with.

CREATE TABLE meta (

    key   TEXT PRIMARY KEY,

    value TEXT NOT NULL

);

-- Projection: how many events of each kind (Radar's Log header, `admin check`).

CREATE TABLE event_counts (

    kind  TEXT PRIMARY KEY,

    n     INTEGER NOT NULL

);