Event-sourced accounting layer: Postgres projections with an on-chain reconciliation proof

[grantfox-oss]

Difficulty: Expert. Scope: multi-week epic. Backend service.

Problem

The current indexer (indexer/index.mjs) is a prototype: it appends decoded events to a flat events.ndjson with a single cursor and no query layer beyond a CSV dump (export-csv.mjs). There is no database, no derived state, no API, and critically no way to verify that what it recorded matches the chain. For a payments protocol, the off-chain view of "who was paid what, and what is currently escrowed" needs to be queryable, correct, and provably consistent with contract storage.

Proposal

A production event-sourced accounting service:

1. Ingest contract events into Postgres as an append-only event log (the source of truth for the projection).
2. Build projections from the log: per-split payout history, per-recipient earnings, current escrow balances per split and token, creator and (once available) recipient indexes.
3. Expose a query API over the projections.
4. Add a reconciliation job that reads the same values from the contract (balance, get_split, split_count) and asserts the projection matches on-chain state, alerting on drift.

Why this is hard

• Correct projections depend on unambiguous events. This must be built on top of, and will expose the need for, the event-model fixes: Emit per-recipient payout legs so payout history can be reconstructed #258 (per-recipient legs) and Deposited event conflates real deposits with internal nested-split routing #267 (deposit vs routing). Deriving per-recipient earnings is impossible today because those events do not carry the breakdown. Sequencing this epic with those is part of the work.
• Reconciliation is the hard, valuable core: proving a derived balance equals the on-chain balance(id, token) across all splits and tokens, continuously, and pinpointing the exact event where drift began when they disagree.
• Reorg safety and exactly-once ingestion under an at-least-once event source (builds on Handle RPC reorgs and make re-scans idempotent #80).
• Idempotent, replayable projections: dropping the projection tables and rebuilding from the log must reproduce identical state.
• Schema and migrations for the log and projections; backfill of historical events.

Deliverables

• Design doc: event schema, projection tables, reconciliation strategy, reorg handling.
• Postgres schema + migrations; ingestion worker; projection builders; reconciliation job with alerting.
• Query API (REST at minimum) for history, earnings, and balances.
• Tests: replay determinism, reconciliation catches an injected drift, reorg handling.

Acceptance criteria

1. Maintainer-approved design before implementation, sequenced against Emit per-recipient payout legs so payout history can be reconstructed #258 and Deposited event conflates real deposits with internal nested-split routing #267.
2. Projections rebuild deterministically from the event log (drop-and-replay yields identical state).
3. Reconciliation continuously proves projected balances equal on-chain balance for every split and token, and reports the first divergent event on mismatch.
4. Ingestion is exactly-once and reorg-safe.
5. The query API serves payout history, per-recipient earnings, and live escrow balances.

Related

Supersedes the ndjson prototype; absorbs or coordinates with #80 (reorgs), #81 (restart durability), #82 (query API), #84 (logging/metrics). Depends on #258 and #267 for correct per-recipient and deposit/routing data.

[BigJohn-dev]

Allow me fix this quickly

[grantfox-oss]

🦊 GrantFox — @BigJohn-dev has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #350)
2. Your PR will be reviewed by the tributary-protocol maintainers

Good luck! Track your progress on GrantFox.