Add On-Chain Portfolio Reconciliation and Indexer Repair Jobs

[grantfox-oss]

Package: packages/backend

Complexity: High

Problem

BACKit uses PostgreSQL as the frontend read model and Soroban events as the source of on-chain activity. RPC interruptions, parsing failures, reprocessing, or a deployment configuration mistake can leave calls, stakes, resolutions, and payout claims missing or duplicated in PostgreSQL. There is currently no operator workflow that quantifies drift and repairs affected records safely.

Goal

Build an idempotent reconciliation process that compares indexed records with authoritative Soroban events/state and repairs recoverable drift without modifying on-chain state.

Acceptance criteria

• Introduce a reconciliation service and BullMQ job that can scan a configurable ledger range and selected contract IDs.
• Compare at least call creation, stake, resolution, and payout claim records using stable event identity (contractId, ledger, transaction hash, and event index or equivalent).
• Classify discrepancies as MISSING_OFFCHAIN, DUPLICATE_OFFCHAIN, VALUE_MISMATCH, UNKNOWN_CONTRACT, or UNRECOVERABLE.
• Dry-run mode produces a structured report without database writes.
• Repair mode upserts missing records transactionally and quarantines unsafe mismatches for admin review; it never submits a Soroban transaction.
• Re-running the same ledger range creates no duplicate records and no duplicate notifications.
• Store reconciliation run metadata, range, contract/network, counts, status, duration, and failure reason.
• Add protected admin endpoints to start a run and inspect runs/discrepancies with pagination and filters.
• Prevent overlapping runs for the same network and contract set using a distributed lock.
• Emit metrics/logs for indexer lag, discrepancy count by type, repaired records, and failed repairs.
• Tests cover an RPC page boundary, retry after interruption, duplicate events, missing records, value mismatch, and idempotent reruns.
• Add an operator runbook describing dry-run review, repair execution, and rollback/incident steps.

Technical pointers

• packages/backend/src/indexer
• packages/backend/src/event-store
• packages/backend/src/payouts
• packages/backend/src/common/queues
• packages/backend/src/admin

Out of scope

• Replacing the existing indexer
• Altering contract state
• Supporting contracts outside the configured BACKit deployment set

---

[aweledavid3]

Hi Maintainer,

I would like to contribute to this project.

[PeterOche]

Repository context: BACKit currently persists indexed Soroban activity in PostgreSQL through packages/backend/src/indexer, but there is no operator-safe way to prove that the read model still matches chain events after an RPC interruption or parser failure. This issue is intentionally backend-only: it must reuse the existing indexer, event-store, admin, and queue patterns and must never submit or alter on-chain state.

[khaadish]

I’d like to take this on because reconciliation is critical for keeping the off-chain read model trustworthy as the platform scales.

I’ll build the reconciliation service and BullMQ workflow with stable event identity, dry-run and transactional repair modes, discrepancy classification, distributed locking, and idempotent reruns. I’ll also add protected admin tooling, observability, and an operator runbook so reconciliation is safe to operate in production.

The test coverage will focus on the failure cases that matter most: RPC interruptions and page boundaries, duplicate/missing events, value mismatches, retries, and repeated reconciliation runs.

[Ibinola]

I’d like to work on this issue. I have a strong interest in backend systems, data consistency, and building reliable, idempotent processes. I’m comfortable working with queues, databases, APIs, and testing edge cases, and I’d approach the reconciliation service carefully to ensure repairs are safe and repeatable.

I’d be happy to take ownership of the implementation, tests, admin workflow, and documentation described in the issue.

[grantfox-oss]

🦊 GrantFox — @Ibinola has been assigned to this issue as part of the Third Campaign campaign!

Next steps:

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

Good luck! Track your progress on GrantFox.

[grantfox-oss]

🎉 This issue has been marked as completed on GrantFox as part of the Third Campaign campaign!

@Ibinola's PR #567 was approved and merged by @PeterOche.

🏆 @Ibinola: You earned 40 FoxPoints for this contribution! Your current tier: Builder (1,505 total points). Track your full progress on GrantFox.

👏 Great work, @Ibinola! Keep contributing to degenspot.