core: implement on-chain to database state reconciliation job

[EmeditWeb]

Problem

The API writes loan and transaction state to Supabase optimistically and
submits to Soroban separately. Several paths make the two sources diverge
permanently: provisional loan IDs are persisted before the real on-chain ID
is known (loans.service.generateProvisionalLoanId), transaction records are
written fire-and-forget (transactions.service.persistTransactionRecord,
.catch() only logs), and indexed events can be missed. There is no process
that periodically compares indexed on-chain contract state against the
database and repairs drift, so the API can serve loan/balance data the chain
does not agree with.

Before Starting

Read ALL of these before writing any code:

• context/architecture-context.md
• context/code-standards.md
• context/progress-tracker.md

What To Build

A scheduled reconciliation job that treats the chain (via the event indexer)
as the source of truth:

1. Periodically read authoritative on-chain state for loans, liquidity
   positions, and reputation using the indexer / contract clients.
2. Diff against the corresponding Supabase rows; classify each mismatch
   (missing row, stale status, provisional ID never resolved, orphaned
   pending transaction).
3. Repair safely and idempotently: resolve provisional IDs, backfill missed
   events, mark stuck transactions, and emit a structured drift report.
4. Expose metrics (drift count by type) and never mutate on-chain state.

Files To Touch

• src/jobs/state-reconciliation/* (new module + processor)
• src/indexer/* (read authoritative state)
• src/modules/loans, src/modules/transactions repositories
• src/modules/metrics (drift gauges)

Acceptance Criteria

• Job runs on a schedule via BullMQ and is idempotent
• Provisional loan IDs reconciled to real on-chain IDs
• Orphaned/fire-and-forget transaction records detected and resolved
• Drift metrics exported and structured drift report logged
• No on-chain writes performed by the job
• Unit tests cover each drift class with mocked chain + DB

Mandatory Checks Before Opening PR

• All context/ files read and understood
• context/progress-tracker.md updated
• npm run build passes with zero TypeScript errors
• npm test passes — test count not decreased
• No new any types introduced
• Swagger decorators on any new endpoint
• Migration file created for any schema changes
• PR references this issue number

[Lanhubs]

I can take ownership of this issue. Developing robust, idempotent data reconciliation jobs that eliminate state drift between relational databases (Supabase) and authoritative on-chain layers (Soroban) is a perfect fit for my backend and ledger engineering workflow.

Proposed Implementation Strategy
BullMQ Scheduled Reconciliation Job: I will build a new background processor in src/jobs/state-reconciliation/ driven by BullMQ. The job will treat the Soroban blockchain (via the event indexer and contract clients) as the absolute source of truth. It will operate purely as a read-and-repair mechanism, ensuring it never attempts to execute mutating on-chain writes.

Diffing & Classification Engine: The processor will fetch authoritative states across loans, liquidity positions, and accounts, diffing them against your local Supabase database. It will categorize anomalies into precise drift classes:

Stale status structures or missed historical events.

Provisional IDs (generateProvisionalLoanId) that were never resolved to their real on-chain identifiers.

Orphaned or stuck fire-and-forget transaction records that failed silenty during the submission stage.

Idempotent Repair & Metrics: Detected drift will be corrected idempotently within Supabase—updating provisional fields, resolving stuck transactions, and backfilling missing entries. I will wire up telemetry metrics in src/modules/metrics to export drift gauges by type, alongside emitting structured, scannable drift reports in the logs.

Test Isolation: I will author mock-driven unit tests across every distinct drift class to verify that the diffing logic accurately highlights discrepancies and heals the data layer correctly without reducing your overall test count.