Add ledger-pinned reconciliation, drift forensics, and zero-downtime projection repair #8

Problem

The event indexer is a derived-state pipeline, not a source of truth. Even if ingestion is resumable and idempotent, missed ledger ranges, decoder regressions, contract upgrades, RPC inconsistencies, partial migrations, or manual database changes can silently corrupt PostgreSQL projections.

OrbitPay needs an independent control plane that can prove what was checked, explain every discrepancy, and repair or rebuild projections without mutating chain state or interrupting reads.

Complexity

Complex — this work crosses Soroban historical-state access, event replay, exact financial arithmetic, database consistency, distributed locking, authorization, observability, and zero-downtime data migration. It should be delivered as a design document plus several reviewable implementation PRs rather than one monolithic change.

Goals

1. Reconcile every indexed protocol domain against authoritative chain evidence at a reproducible ledger boundary.
2. Preserve enough forensic evidence to distinguish ingestion, decoding, schema, RPC, and operator failures.
3. Provide safe, resumable repair and shadow-rebuild workflows.
4. Prove that reader traffic never observes partially repaired or mixed-version projections.

Architecture requirements

1. Reconciliation run model

• Persist a run record containing network passphrase, RPC endpoint identity, contract registry version, contract IDs/WASM hashes, pinned ledger, decoder version, database schema version, start/end time, and terminal status.
• Use a distributed lease so only one conflicting run or cutover can operate on a projection.
• Support cancellation, timeout, crash recovery, and retry without losing completed comparison work.
• Partition work into bounded, resumable shards with explicit high-water marks.

2. Canonical comparison

• Define canonical snapshots for treasury configuration and withdrawals, payroll streams and claims, vesting schedules and claims, and governance proposals, membership, weights, and votes.
• Compare exact base-unit integers; never use JavaScript or SQL floating point.
• Normalize ordering and optional/default fields before hashing.
• Store deterministic fingerprints for chain state and projected state.
• Classify discrepancies as missing, unexpected, stale, value mismatch, invalid transition, aggregate mismatch, decode failure, unavailable historical state, or unsupported contract version.

3. Evidence and forensics

• Persist the source ledger, transaction/event identifiers, raw XDR reference or checksum, decoded payload, projection row/version, expected value, observed value, and decoder provenance.
• Make discrepancy records immutable; resolution creates a linked append-only action record.
• Redact secrets and sensitive authentication material from all logs and evidence.
• Define evidence retention and export suitable for incident review.

4. Repair safety

• Default to report-only mode.
• Require an authorized operator, explicit network and contract scope, ledger range, discrepancy set, idempotency key, reason, and dry-run approval before repair.
• Generate a deterministic repair plan whose checksum is approved before execution.
• Use transactional compare-and-swap or equivalent guards so rows changed after planning are not overwritten.
• Never submit Soroban transactions or otherwise mutate chain state.
• Support pause, resume, rollback, and safe repetition of every repair batch.

5. Shadow rebuild and cutover

• Rebuild into versioned shadow tables or an isolated schema while the active projection continues serving traffic.
• Record replay checkpoints and validate row counts, domain aggregates, fingerprints, and sampled records before cutover.
• Cut over atomically through a versioned indirection mechanism.
• Keep the previous projection available for a defined rollback window.
• Reject cutover if the indexer has not caught the shadow projection up to the approved ledger boundary.

6. API, CLI, and observability

• Provide an operator CLI or protected administrative API for planning, starting, inspecting, cancelling, approving, and resuming runs.
• Expose progress by domain/shard without unbounded cardinality.
• Publish metrics for last successful run, pinned ledger, checked records, discrepancies by class, lag, duration, retries, repair results, lease conflicts, and cutover/rollback outcomes.
• Add structured audit logs with correlation IDs across planning, approval, execution, and verification.
• Define alerts for stalled runs, unexplained drift, repeated decoder failures, failed repairs, and overdue reconciliation.

Required deliverables

• Architecture decision record covering consistency model, historical-state limitations, locking, evidence retention, repair authorization, and cutover strategy.
• Database migrations for run, shard, discrepancy, evidence, repair-plan, approval, and audit records.
• Reconciliation engine with domain adapters and a versioned canonicalization contract.
• Report-only operator workflow.
• Guarded targeted-repair workflow.
• Shadow rebuild, verification, atomic cutover, and rollback workflow.
• Dashboards, alerts, operational runbook, and incident evidence export.
• Capacity model for expected record counts, RPC usage, runtime, storage, and rate limits.

Acceptance criteria

• The same inputs produce identical canonical fingerprints and repair-plan checksums.
• A run is reproducible from its stored ledger, contract registry, schema, and decoder metadata.
• A forced crash at every shard and repair boundary resumes without duplicate actions or lost evidence.
• Concurrent conflicting runs, repairs, and cutovers are rejected or safely serialized.
• Rows changed after plan generation fail compare-and-swap and require replanning.
• Reader requests never observe a mixture of old and new projection versions during cutover.
• Rollback restores the previous projection without replaying from genesis.
• Reconciliation detects injected missed, duplicated, reordered, malformed, and incorrectly decoded events.
• Tests distinguish an unavailable historical ledger from an actual state mismatch.
• Large datasets are processed with bounded memory and configurable RPC/database concurrency.
• Repair authorization and audit tests fail closed across organizations and roles.
• Metrics and alerts are validated in an automated failure drill.
• A staging exercise demonstrates report, targeted repair, full rebuild, cutover, rollback, and post-run verification.

Test matrix

At minimum, automate:

• deterministic fixture and property tests for canonicalization;
• PostgreSQL integration tests for isolation, leases, checkpoints, compare-and-swap, and atomic cutover;
• mocked RPC tests for pagination, rate limiting, timeouts, inconsistent responses, and unavailable historical state;
• decoder compatibility tests across supported contract versions;
• chaos tests for process termination and dependency outages;
• scale tests at the documented production volume;
• authorization and audit-integrity tests for every mutating operator action.

Dependencies

• Resumable Soroban event indexer.
• Exact on-chain amount representation.
• API authentication and organization authorization.
• Versioned protocol/event specification and contract registry.

Work may begin with the ADR and report-only data model while those dependencies are in progress. Repair and cutover cannot ship until their relevant dependencies are complete.

Non-goals

• Mutating or correcting on-chain state.
• Replacing indexer idempotency and checkpoint guarantees.
• Treating the Horizon API or current PostgreSQL projection as authoritative.
• Automatically repairing unexplained discrepancies.
• Supporting contract versions outside the published compatibility matrix.