ledger-rs/doc/adr/0022-merged-live-postings-hot-index.md

Merge the active/reserved hot indexes into one live_postings table

• Status: accepted
• Authors: Cesar Rodas
• Date: 2026-08-01
• Targeted modules: kuatia-storage-sql (schema, migration 008, PostingStore)
• Associated tickets/PRs: N/A

Context and Problem Statement

ADR-0016/0017 split a posting's live state across two disposable hot tables: active_postings (membership = spendable) and reserved_postings (membership + reservation = claimed by a saga). A "live" read (Active ∪ Reserved), which compute_balance and has_live_postings need, therefore has no single table to hit: the SQL backend expresses it as a UNION ALL of the two tables. Reserve and release move a row between the two tables (DELETE from one, INSERT into the other), and get_posting_states probes three tables.

Only the SQL encoding forces the UNION ALL; the two-table split is not required by any correctness guarantee (ADR-0006's reservation protocol needs an atomic single-winner claim, durable observable ownership, and count-returning primitives, none of which depend on the physical table count). Can the live set live in one table so the UNION ALL disappears, without losing those guarantees?

Decision Drivers

• Query clarity and read performance. The live/spendable reads are hot (compute_balance runs per commit in the finalize validation loader). A single-table scan is simpler to reason about and to index than a UNION ALL.
• Preserve every ADR-0006/0016 guarantee. Unconditional lock-free double-spend safety; durable, observable, recoverable reservation ownership; atomic count-returning dumb-storage primitives (ADR-0003); balance as Active ∪ Reserved; append-only value table.
• Keep the source of truth append-only (ADR-0017). postings (the immutable record) must stay INSERT-only and rebuildable-from is the audit trail. Whatever changes may only touch a disposable hot table.

Considered Options

Option 1: Keep two hot tables (ADR-0016/0017)

active_postings + reserved_postings, live reads via UNION ALL, reserve as DELETE-from-active + INSERT-into-reserved.

Pros:

• Good, because the entire hot-table write path is INSERT + DELETE, so a grant can withhold UPDATE on them too.
• Good, because it is already implemented and conformance-tested.

Cons:

• Bad, because a live read has no single table and is a UNION ALL.
• Bad, because reserve/release are two-statement moves and get_posting_states probes three tables.

Option 2: One live_postings table with a nullable reservation

Merge the two hot tables into one: live_postings (transfer_id, idx, owner, subaccount, asset, value, reservation), reservation NULL = Active, set = Reserved by that id. State is still derived from membership + the column: present & NULL = Active; present & = rid = Reserved(rid); absent from live_postings but in postings = Spent; absent everywhere = Missing. The primitives become:

• Live read: SELECT ... FROM live_postings WHERE owner/subaccount/asset (no UNION). Active = ... AND reservation IS NULL, Reserved = ... AND reservation IS NOT NULL.
• Reserve: UPDATE live_postings SET reservation = $rid WHERE <ids> AND reservation IS NULL. The IS NULL guard is the atomic single-winner claim (concurrent reserves serialize on the row lock; the loser's predicate no longer matches → 0 rows). One statement.
• Release: UPDATE live_postings SET reservation = NULL WHERE <ids> AND reservation = $rid.
• Consume (finalize) / raw deactivate: DELETE FROM live_postings WHERE <ids> AND reservation = $rid (or IS NULL); the posting stays in postings = Spent.
• get_posting_states: two statements (live table + postings) instead of three.

Pros:

• Good, because the live set is one table: the UNION ALL is gone, reserve and release are single statements, and get_posting_states drops a statement.
• Good, because every ADR-0006 guarantee holds: the UPDATE ... WHERE reservation IS NULL gives the same single-winner claim as the DELETE-CAS; the reservation column is still the durable, observable ownership recovery reads; the primitives still return affected-row counts the saga interprets.
• Good, because the value table postings stays append-only INSERT-only, and live_postings is still fully rebuildable from postings plus the saga write-ahead records, so a corrupt hot table is drop-and-rebuild (ADR-0017's disposability principle is preserved).

Cons:

• Bad, because the hot table now takes an UPDATE (the reservation flip), so the "hot tables are INSERT/DELETE only, withhold UPDATE everywhere" grant story of ADR-0017 no longer holds for live_postings (it still holds for the value tables).
• Bad, because the schema change is forward-only (migration 008 merges the two tables and drops them).

Option 3: Keep the UNION ALL but hide it behind a view

A SQL VIEW live_postings AS active UNION ALL reserved.

Pros:

• Good, because callers read one name.

Cons:

• Bad, because it is the same UNION ALL at runtime; nothing is actually saved.

Decision Outcome

Chosen option: Option 2, one live_postings table with a nullable reservation. It removes the UNION ALL and collapses reserve/release to a single statement while preserving every correctness guarantee of ADR-0006/0016. The one concession is that the disposable hot table now takes an UPDATE (the reservation flip): this is deliberately confined to the rebuildable hot table and never touches the append-only postings value table, so ADR-0017's core driver (the source of truth cannot be corrupted or lost by any write path) is intact.

This supersedes the two-table hot-index encoding of ADR-0016 and ADR-0017. Their principle (append-only value tables plus disposable hot indexes) stands; only the number of hot tables and the reserve verb change.

Positive Consequences

• A live/spendable read is one index scan on idx_live_owner, no UNION ALL.
• Reserve and release are single UPDATE statements; get_posting_states is two queries instead of three.
• reservation IS NULL vs = rid still expresses Active vs Reserved, so state is still derived, and recovery still reads "reserved by this saga" from the column.

Negative Consequences

• The live_postings hot table takes an UPDATE; the grant that withholds UPDATE now applies to the value tables only, not the hot table.
• Forward-only migration (008) that drops active_postings / reserved_postings.

Links

• Supersedes the two-table hot-index encoding of ADR-0016 and ADR-0017; their append-only-value / disposable-hot-index principle is unchanged.
• Preserves the reservation protocol of ADR-0006 and the dumb-storage primitives of ADR-0003.
• Reflected in storage-schema.md.