RFC: Single Append-Only Audit Log

[crodas]

Summary

This RFC proposes adding one append-only event_log table while keeping the existing tables as the source of current state.

The existing tables continue to answer: “What is true now?”

The append-only log answers: “How did the system get here?”

This does not replace existing tables, does not change the normal read path, and does not require every domain table to become append-only. It only adds a shared audit trail for meaningful state changes.

Proposal

Add a single table that records changes across all tracked entities.

CREATE TABLE event_log (
    change_id   INTEGER PRIMARY KEY,
    table_name  TEXT NOT NULL,
    entity_id   TEXT NOT NULL,
    change_type TEXT NOT NULL,
    data        TEXT NOT NULL
);

CREATE INDEX idx_event_log_entity
    ON event_log(table_name, entity_id, change_id);

Conceptually, each row is:

struct EventLogRow {
    change_id: i64,
    table_name: String,
    entity_id: String,
    change_type: ChangeType,
    data: JsonSnapshot,
}

enum ChangeType {
    InitialMigration,
    Created,
    Updated,
    Deleted,
}

The data field stores a serialized snapshot of the entity at the time of the event. A Created event stores the inserted state. An Updated event stores the resulting state after the update. A Deleted event stores the final known state before deletion. An InitialMigration event stores the baseline state found during migration.

Why Append-Only?

Mutable tables are efficient for current state, but they do not explain history. After an update, the previous value may be gone. After a delete, the row may disappear entirely.

The append-only log preserves every meaningful transition. It makes the database easier to audit, debug, replay, and reconcile. It allows the system to inspect the lifecycle of an entity without changing the existing operational model.

The current tables remain optimized for normal work. The event log is optimized for historical explanation.

Why a Single Log Table?

A single shared event_log is simpler than creating one history table per entity type.

Instead of maintaining many separate history tables, all events use the same structure: change_id, table_name, entity_id, change_type, and data.

This gives the system one uniform audit model. The same query can retrieve the history of any tracked entity:

SELECT *
FROM event_log
WHERE table_name = :table_name
  AND entity_id = :entity_id
ORDER BY change_id;

change_id

The change_id is an application-generated i64. It should not use database autoincrement.

The proposed layout is:

1 bit:  always 0
41 bits: timestamp in milliseconds since custom epoch
22 bits: hash(changeset)

Conceptually:

change_id =
    0 | timestamp_ms_since_custom_epoch | hash(changeset)

The highest bit is always 0 so the value remains a positive signed i64.

The timestamp is stored in the high-order bits, so IDs remain naturally sortable by time. Because event time is embedded directly in change_id, a separate changed_at column is unnecessary.

The final 22 bits come from hash(changeset). The changeset is the stable event content used to describe what changed.

Conceptually, the changeset includes:

struct ChangeSet {
    table_name: String,
    entity_id: String,
    change_type: ChangeType,
    data: JsonSnapshot,
}

The hash should be derived from the canonical serialized form of the changeset. The change_id itself should not be included in the hash input.

This gives the ID two useful properties:

First, it is time-sortable because the timestamp is in the high-order bits.

Second, it is content-aware because the lower bits are derived from the event content.

Other systems often use a Snowflake-style layout with worker ID and increment/sequence bits. That approach is useful when many IDs may be generated in the same millisecond or when there are multiple writers.

This RFC intentionally uses the lower bits for hash(changeset) instead. The goal is to make change_id not only time-sortable, but also connected to the event payload.

Notes on Collisions

The 22-bit hash is a lightweight content-derived component. It helps detect obviously mismatched IDs and changesets, but it should not be treated as a full cryptographic proof.

Collisions are possible because 22 bits is a small hash space. The database should still enforce uniqueness through the primary key on change_id.

If stronger guarantees are needed later, the same general model can be extended with a separate full payload_hash, a keyed MAC, a hash chain, or a Snowflake-style worker/sequence layout.

Invariants

The existing tables remain the current-state tables.

The event log is append-only.

Every tracked create, update, or delete should append one event in the same transaction as the state change.

Events are ordered by change_id, which primarily orders by the embedded millisecond timestamp.

There is no separate changed_at; event time is derived from change_id.

The database should enforce uniqueness with the primary key on change_id.

Conclusion

This proposal adds auditability without replacing the existing storage model.

The system keeps fast current-state tables and gains a durable append-only history of state changes.

The central idea is simple: keep the existing tables, append every meaningful change to one shared log, and identify each event with an application-generated i64 change_id.

The proposed change_id uses one fixed zero bit, 41 bits of millisecond timestamp, and 22 bits derived from hash(changeset). This keeps the ID positive, sortable by time, and lightly tied to the event content.

[thesimplekid]

I think to make a more informed decision here, we should first be more specific about what we actually want or need to be append-only, and how that state is currently represented in the mint database.

For example, mint_quote already has a more domain-specific append-only path. We have mint_quote_payments and mint_quote_issued, which record individual payment and issuance events. From those tables, we should be able to rebuild or verify the current amount_paid and amount_issued fields on mint_quote.

That seems like a clearer audit model than storing generic JSON snapshots in a shared event_log row saying that mint_quote was updated.

More generally, I think it would be worth breaking down the mint by event type / state transition and deciding which ones actually need append-only history. I do not think the mint has that many categories: quote payments, quote issuances, proof state transitions, blind signature creation, saga/operation lifecycle transitions, completed operation summaries, and fee/issued/redeemed accounting entries.

Some of these may already have domain-specific tables. For the others, purpose-specific append-only tables may give a clearer picture than one generic JSON event log.

So I would suggest the RFC first inventory the mint-side state changes and classify them as:

1. already append-only or derivable from existing append-only tables;
2. current-state only but worth adding domain-specific append-only history for;
3. not worth tracking append-only.

After that, we can decide whether a generic event_log is still needed.

I also think its worth noting what is the standard we need to meet here as some of the motivation here was compliance inside a TEE. Is an audit trail that can rebuild the mint state as outlined in your RFC and the direction of my suggestion, good enough or does the mint db truly need to be append only. The later would be much tougher, technically the worse solution and likely have performance implications.

[crodas]

After digesting your feedback @thesimplekid here is an updated action plan, much simpler and I think useful.

RFC: Append-Only Change Log for the Mint Database

Summary

Add per-table, append-only log tables that record every state change (updates or deletions) to auditable mint entities. The existing tables remain the source of the current state. The log tables answer: "How did the system get here?"

Only state changes are logged. Insert-only entities (blind signatures, completed operations) are not duplicated because their source tables already serve as the complete, immutable record.

Target Entities and Their Changesets

1. mint_quote already append-only, no log table needed

update_mint_quote already records changes to two append-only detail tables:

• mint_quote_payments: one row per incoming payment (quote_id, payment_id, amount, timestamp)
• mint_quote_issued: one row per issuance (quote_id, amount, timestamp)

The amount_paid and amount_issued columns on mint_quote are materialized sums of these detail tables. The full payment and issuance history is already preserved. No additional log table is needed.

See crates/cdk-sql-common/src/mint/quotes.rs:843-915.

2. melt_quote_log

Source table: melt_quote
Triggered by: update_melt_quote_state, update_melt_quote_request_lookup_id
What changes: state, request_lookup_id, payment_proof, paid_time

CREATE TABLE melt_quote_log (
    change_id           BIGINT PRIMARY KEY,
    quote_id            TEXT NOT NULL,
    state               TEXT,
    request_lookup_id   TEXT,
    payment_proof       TEXT,
    paid_time           BIGINT
);
CREATE INDEX idx_melt_quote_log_quote ON melt_quote_log(quote_id, change_id);

Lifecycle: Quote created (Unpaid) -> Pending (inputs locked, payment initiated) -> Paid (payment confirmed) or Failed (payment failed, can retry). Each state transition appends one row. The request_lookup_id update (when the LN backend assigns a payment identifier) also appends a row.

3. proof_log

Source table: proof
Triggered by: update_proofs_state, remove_proofs
What changes: state (Unspent -> Pending -> Spent), or deletion

CREATE TABLE proof_log (
    change_id       BIGINT PRIMARY KEY,
    change_type     SMALLINT NOT NULL, -- 0 = Updated, 1 = Deleted
    ys              TEXT NOT NULL,     -- JSON array of Y hex values
    state           TEXT,              -- new state (Updated) or final state (Deleted)
    count           INTEGER NOT NULL
);
CREATE INDEX idx_proof_log_change ON proof_log(change_id);

Lifecycle: Proofs are created (Unspent) -> reserved (Pending) -> consumed (Spent). On compensation (failed operation), pending proofs may be deleted via remove_proofs. Each batch state transition or deletion appends one row with all affected Y values.

---

4. keyset_log

Source table: keyset
Triggered by: set_active_keyset
What changes: active flag

CREATE TABLE keyset_log (
    change_id       BIGINT PRIMARY KEY,
    keyset_id       TEXT NOT NULL,
    active          BOOLEAN NOT NULL
);
CREATE INDEX idx_keyset_log_keyset ON keyset_log(keyset_id, change_id);

Lifecycle: Keyset is created (active=true) -> rotated (active=false, new keyset becomes active). Each set_active_keyset call deactivates the old keyset and activates the new one, producing two log entries.

---

Ignored Tables

Entity	Why no log table
blind_signature	Insert-only, never updated or deleted. Source table is the complete record.
completed_operation	Insert-only, never updated or deleted. Source table is the complete record.
melt_request	Ephemeral staging data, always deleted when the operation completes. The melt quote state transition already captures the meaningful event.
blinded_message	Pending-signature placeholders (rows with c IS NULL). Cleaned up after signing. The blind signature insert captures the real event.
saga_state	Process-private crash recovery state. Always deleted after operation completion. Carries no audit value.
kv_store	Generic key-value store for configuration and plugin data. Too heterogeneous for a typed log. Per-namespace logging can be added later if needed.
protected_endpoint (auth)	Configuration data, not a financial event.
auth_keyset / auth_proof / auth_blind_signature	Same patterns as their non-auth counterparts. Deferred to a follow-up.

change_id Design

Application-generated i64. Layout:

Bit 63:      always 0 (positive signed i64)
Bits 62..22: 41 bits of millisecond timestamp since custom epoch
Bits 21..0:  22 bits of hash(changeset)

The changeset hash is derived from (table_name, entity_id, change_type). This makes change_id time-sortable (high bits) and content-aware (low bits). No separate changed_at column is needed, event time is derived from change_id.

Implementation Approach

Each mutation method in the SQL implementation (crates/cdk-sql-common/src/mint/*.rs) appends an INSERT INTO <entity>_log after the primary mutation, on the same database connection/transaction. If the transaction rolls back, the log entry rolls back too.

This is the same pattern already used for keyset_amounts updates piggy-backed onto proof/signature mutations.

Affected Methods (6 total)

File	Method	Log Table
quotes.rs	update_melt_quote_state	melt_quote_log
quotes.rs	update_melt_quote_request_lookup_id	melt_quote_log
proofs.rs	update_proofs_state	proof_log
proofs.rs	remove_proofs	proof_log
keys.rs	set_active_keyset	keyset_log

New Files

File	Purpose
crates/cdk-common/src/database/event_log.rs	ChangeType enum, generate_change_id()
crates/cdk-sql-common/src/mint/event_log.rs	Per-table append_*_log() SQL helpers
migrations/sqlite/20260626000000_create_log_tables.sql	3 log tables (SQLite)
migrations/postgres/20260626000000_create_log_tables.sql	3 log tables (PostgreSQL)

Invariants

1. The existing tables remain the source of the current state.
2. Log tables are append-only. No updates, no deletes.
3. Every logged state change is appended in the same transaction as the mutation.
4. Events are ordered by change_id (primarily by embedded timestamp).
5. The database enforces uniqueness via change_id primary key.

Action Plan

1. Core types + migration Define ChangeType enum and generate_change_id(). Create the 3 log tables in both SQLite and PostgreSQL migrations.
2. SQL helpers: Implement append_melt_quote_log(), append_proof_log(), append_keyset_log().
3. Instrument mutations: Add log INSERT calls to the 5 mutation methods listed above.
4. Read API : Query helpers for retrieving log entries by entity ID or time range.
5. Tests: Verify log entries are created on state changes, rolled back on transaction rollback, and queryable by entity.

[crodas]

Closed in favor of #2173