ledger-rs/doc/adr/0016-immutable-postings-index-tables.md

Immutable postings with active/reserved index tables

• Status: accepted
• Authors: Cesar Rodas
• Date: 2026-07-11
• Targeted modules: kuatia-types (Posting, PostingState, PostingFilter), kuatia-storage (PostingStore, InMemoryStore), kuatia-storage-sql (schema, migration 004), kuatia (ledger, saga)
• Associated tickets/PRs: N/A

Context and Problem Statement

ADR-0006 gave every posting a mutable status column (Active → PendingInactive → Inactive) plus a nullable reservation token, both flipped in place with UPDATE. Consumed postings stayed in the table as Inactive tombstones. That works, but it makes the postings table a mutate-in-place table: the commit path needs UPDATE rights on it, a historical posting's value or state can be rewritten by any code path (or any compromised credential) that can issue an UPDATE, and every "what can this account spend" read scans the full postings history filtered by a partial index on status, a set that only grows.

The reservation protocol of ADR-0006 does not actually require the state to live on the posting. It requires an atomic single-winner claim, durable recoverable ownership, and count-returning primitives (ADR-0003). Can we keep those guarantees while making a posting an append-only record that is written once and never changed?

Decision Drivers

• Append-only integrity. A posting is a fact about a committed transfer. Once written it should never change, matching the append-only stance of ADR-0001 (value as immutable signed postings) and ADR-0007 (undo by compensation, never mutation). The postings table becomes the immutable record; the transfer + event logs already record every create and consume.
• Least privilege / tamper surface. If postings are insert-only, the role that commits transfers needs only INSERT on postings, never UPDATE or DELETE. No path can rewrite a historical row. Lifecycle churn is confined to small index tables that hold ids, not monetary values.
• Read performance by segregation, not by partial index. "Spendable" and "live" are hot reads. Rather than scan a growing history and filter by status (a partial index over cold and hot rows mixed together), keep the working set in its own physically separate table. The index is the table.
• Preserve ADR-0006 semantics. Unconditional, lock-free double-spend safety; durable ownership that survives the multi-step saga and a crash; expressible as atomic, count-returning dumb-storage instructions (ADR-0003).

Considered Options

Option 1: Keep ADR-0006 (mutable status column + partial index)

Leave postings as a mutate-in-place table with status + reservation.

Pros:

• Good, because a posting's state is co-located with its data (one row, no join).
• Good, because it is already implemented and conformance-tested.

Cons:

• Bad, because the commit path requires UPDATE on the value-bearing table, so a bug or a compromised credential can rewrite historical postings.
• Bad, because spendable/live reads scan the full, ever-growing history filtered by status; the hot working set is never physically separated from cold tombstones.
• Bad, because the postings table is not append-only, at odds with ADR-0001/0007.

Option 2: Single active table, reservation only in the write-ahead record

Delete a posting id from a single active_postings table to reserve it; keep the reservation solely in the saga's write-ahead PendingSaga blob (ADR-0003).

Pros:

• Good, because the schema is minimal (one index table).

Cons:

• Bad, because reserved (in-flight) postings are no longer observable in storage, so balance (Active + Reserved) and close (blocks on live) change behavior, and recovery cannot read "reserved by rid" from a row.
• Bad, because it silently alters the observable semantics ADR-0006 fixed.

Option 3: Immutable postings + two id-only index tables

postings becomes insert-only and immutable: (transfer_id, idx, owner, subaccount, asset, value), no status, no reservation. Two index tables hold only ids: active_postings (membership = spendable) and reserved_postings ((id, reservation), membership = claimed by a saga). A posting's state is derived from membership: in active → Active, in reserved → Reserved(rid), in neither → Spent. Every transition is an insert/delete on an index table; the posting row never changes:

• Create (finalize): INSERT into postings, then INSERT id into active_postings.
• Reserve: DELETE id from active_postings; if it removed a row, INSERT id + rid into reserved_postings. The delete-returns-one is the atomic single-winner claim.
• Consume (finalize): DELETE id from reserved_postings where reservation = rid. The posting stays in postings forever, now in neither index = spent.
• Release (compensation): DELETE from reserved_postings, INSERT back into active_postings.

Pros:

• Good, because postings is append-only. The commit role needs only INSERT on it; no code path or credential can rewrite a historical posting. The immutable table is the audit trail, with history also reconstructable from the transfer + event logs.
• Good, because the active and reserved working sets are physically separate, small, and id-only. Spendable/live reads hit a dedicated table instead of scanning history behind a partial index.
• Good, because reservation is still a single atomic claim (the delete-CAS picks one winner; the loser sees zero rows), and ownership is still durable and observable: reserved_postings.reservation records who holds a posting, so recovery and finalize target only their own rows exactly as in ADR-0006.
• Good, because it stays within dumb storage: each primitive is one conditional insert/delete returning an affected-row count; the saga interprets it (ADR-0003).
• Good, because balance (Active ∪ Reserved) and close (blocks on any live) keep their exact prior semantics, now expressed as index membership instead of a status filter.

Cons:

• Bad, because a posting's state is no longer co-located with its data: reading state is a membership probe across two tables (get_posting_states), and three tables replace one.
• Bad, because consuming an already-spent posting no longer surfaces as a plan-time validation error; a spent posting is simply absent from the indexes, so the abort moves to the reserve claim / finalize guard (same safety, different surface).
• Bad, because the schema change is forward-only (migration 004 rebuilds postings without the status/reservation columns).

Decision Outcome

Chosen option: Option 3, immutable postings with active/reserved index tables, because it keeps every guarantee of ADR-0006 (lock-free double-spend safety, durable recoverable ownership, count-returning primitives) while making the value-bearing table append-only. That buys least-privilege security (the commit role needs no UPDATE on postings, so historical rows are tamper-evident by construction) and read performance by segregation (the hot active/reserved working set lives in its own id-only tables rather than behind a partial index over ever-growing history).

This supersedes the storage representation of ADR-0006. The reservation protocol of ADR-0006 stands; only its physical encoding changes, from an in-place status transition to index-table membership.

Positive Consequences

• postings is insert-only: grant INSERT, withhold UPDATE/DELETE.
• Reserve is DELETE FROM active_postings as the concurrency gate; the saga reads the affected-row count to know it won (ADR-0003).
• Recovery still distinguishes "reserved by this saga" (row present in reserved_postings with our rid) from "spent" (absent from both indexes) and "taken by another" (reserved by a different rid), which is what makes phase-tracked roll-forward safe.
• The immutable postings table and the append-only logs can be archived or pruned independently of the live working set, which partly addresses the deferred retention question (README "Recommended future ADRs").

Negative Consequences

• Reading a posting's state is a probe across active_postings / reserved_postings / postings rather than one column.
• Three tables instead of one; ports of the store must keep the reserve claim (delete-from-active + insert-into-reserved) atomic.
• No Inactive tombstone: a spent posting's state is "present in postings, absent from the indexes." Consumers that read per-posting state use the derived PostingState, not a stored column.

Links

• Supersedes the storage representation of ADR-0006 (the reservation protocol itself is unchanged).
• Builds on ADR-0001 (immutable signed postings) and the dumb-storage recovery of ADR-0003.
• Extended by the conformance suite of ADR-0008 (membership-based transition tests).
• Background: architecture.md ("Posting Lifecycle").