ReconResolve

Catch every payment discrepancy before it costs you.

Comment

Inspiration

Modern digital businesses and e-commerce platforms process thousands of transactions every day across third-party gateways (like PayPal) and internal Order Management Systems (OMS / ERP). In theory, the gateway settlement and the internal ledger should always match. In reality, they almost never do.

Between delayed or dropped webhooks, silent refunds, partial captures, currency exchange fluctuations, processing fee deductions, and distributed race conditions, financial discrepancies—known in the industry as "breaks"—inevitably occur. When they do, finance and operations teams are left drowning in brittle, manual CSV spreadsheet lookups at month-end. Worse, recent attempts to automate this with "black-box" AI models fall flat because financial compliance officers and auditors cannot verify or trust probabilistic guesses when real money is on the line.

My friend and I built ReconResolve to fix this once and for all. We wanted to engineer a dual-entry reconciliation system that balances absolute mathematical determinism (so audit teams can sleep at night) with grounded, verifiable AI intelligence (so finance teams can resolve breaks in seconds instead of hours).

What it does

ReconResolve is an enterprise-grade multi-tenant financial reconciliation platform that continuously synchronizes, matches, investigates, and resolves discrepancies between external payment provider records and internal merchant order ledgers.

Here is what it delivers under the hood:

  • Exact Minor-Unit Integer Arithmetic: Banning IEEE-754 floating-point math entirely, ReconResolve processes and stores all monetary values as BigInt minor units (e.g., cents, pence) to guarantee zero micro-cent leakage and eliminate rounding drift.
  • Deterministic, Zero-Fuzzy Matching Engine: Matches transactions through an explainable, pure mathematical rule cascade:
    1. Rule 1: Exact Provider Order Reference match.
    2. Rule 2: Exact Transaction Metadata Reference match.
    3. Rule 3: Exact Amount, Currency & Temporal Window (24h) match.
  • Deterministic Break Detection & Taxonomy: Automatically flags and isolates breaks into precise financial categories: AMOUNT_MISMATCH, STATUS_DISCREPANCY, AMBIGUOUS_MATCH, MISSING_MERCHANT_COUNTERPART, and MISSING_PROVIDER_COUNTERPART.
  • Transactional Outbox Daemon (FOR UPDATE SKIP LOCKED): Eliminates distributed 2-phase commit (2PC) anti-patterns by pairing real-time PayPal webhook ingress with an asynchronous background worker using PostgreSQL row-level claim leases, exponential backoff with full jitter, and error sanitization.
  • Grounded AI Root-Cause Investigator: Powered by Google Gemini 2.5 Flash, the AI analyzes un-reconciled breaks and provides clear diagnostic hypotheses. Crucially, the AI is constrained by an inviolable read-only advisory invariant—every fact, hypothesis, and recommendation must cite pre-verified database evidence references, and it cannot mutate ledgers or initiate money movement.
  • High-Velocity Operator Console: A responsive, 60fps workbench built with React 19, Tailwind CSS v4, and AG Grid Community, complete with full keyboard hotkeys (j/k navigation, r to resolve, o to reopen, d to dismiss, i to investigate), split-pane evidence diffs, Optimistic Concurrency Control (OCC), and immutable, append-only audit logs.

How we built it

We built ReconResolve as a modern, decoupled full-stack TypeScript platform:

  • Backend & Ledger Engine: Built with Node.js, Express, Prisma ORM, and PostgreSQL 16. We used PostgreSQL to enforce relational integrity, multi-tenant scoping, and transactional consistency across 16 core entities.
  • Payment Ingestion Pipeline:
    • Real-Time Ingress: Implemented an Express webhook endpoint capturing raw cryptographic bytes to verify PayPal's asymmetric RSA-SHA256 signatures, dual-writing events to a webhook store and the outbox in an atomic $transaction.
    • Batch Reporting Sync: Built an import coordinator querying PayPal’s /v1/reporting/transactions REST API with OAuth token deduplication, 31-day bounded sliding windows, and deterministic SHA-256 record key generation.
  • Transactional Outbox Worker: Designed a background polling daemon utilizing PostgreSQL SELECT ... FOR UPDATE SKIP LOCKED with 30-second UUID claim leases, concurrency pools, and Dead-Letter Queue (DLQ) replay capabilities.
  • AI Intelligence & Guardrails: Integrated Google Gemini 2.5 Flash using the @google/genai SDK in Structured JSON Mode. We isolated untrusted user content using XML fencing (<untrusted_external_content>) to thwart prompt injection, and built a custom post-generation citation validation layer that verifies every LLM citation against real database records.
  • Frontend Operator Workbench: Developed with React 19, Tailwind CSS v4, and AG Grid Community 36. We designed a split-pane evidence comparison viewer, interactive AI investigation drawer, natural language Copilot with money-movement guardrail interceptors, and a 6-stage visual pipeline timeline.

Challenges we ran into

  1. Eradicating Floating-Point Arithmetic Across the Entire Stack: JavaScript's native Number type is infamous for precision issues (e.g., 0.1 + 0.2 = 0.30000000000000004). In financial reconciliation, this is fatal. We had to enforce BigInt minor units across database schemas, Prisma models, serialization boundaries (JSON does not natively serialize BigInt), REST APIs, and UI presentation components.
  2. Preventing Race Conditions & Webhook Duplication: Webhooks arrive out-of-order, in duplicate, or during service restarts. Implementing the Transactional Outbox pattern with PostgreSQL row-level locks (FOR UPDATE SKIP LOCKED) and claim tokens while ensuring graceful shutdown draining without event loss took careful concurrency tuning.
  3. Grounded AI with Zero Hallucination: LLMs can easily fabricate plausible-sounding transaction IDs or fake financial reasoning. We had to build a strict Evidence Assembler and an Anti-Hallucination Validator. If Gemini generated a diagnosis referencing an ID not present in the verified evidence bundle, the citation is stripped or rejected immediately.
  4. PayPal API Boundaries & Rate Limits: PayPal limits transaction search windows to 31 consecutive days and enforces strict query constraints. We had to implement chunked window pagination, UTC boundary math, and a thread-safe token manager with single-flight promise memoization to avoid redundant OAuth calls.
  5. Multi-Operator Concurrency: When two finance operators review the same exception simultaneously, there is a risk of conflicting overwrites. We engineered Optimistic Concurrency Control (OCC) using integer version tokens, returning HTTP 409 CONFLICT when stale updates are attempted, backed by an append-only audit trail.

Accomplishments that we're proud of

  • Financial-Grade Integrity: We didn't cut corners. We built a system that complies with real-world accounting standards: strict minor units, deterministic rule evaluation, complete tenant isolation, and non-repudiable audit logs.
  • Safe, Grounded AI That Finance Teams Can Trust: Instead of treating AI as an autonomous black-box with direct database write access, we successfully constrained Gemini 2.5 Flash to a verified, citation-backed advisory co-pilot that explains complex breaks in seconds without any risk of unauthorized ledger changes.
  • Super-Fast Keyboard-First Operator UX: We brought "Vim-style" speed to financial reconciliation. Operators can navigate through hundreds of breaks at 60fps using j and k keys, trigger side-by-side diffs, inspect grounded AI root causes, and submit auditable resolution notes without ever lifting their hands from the keyboard.
  • Zero Webhook Loss Architecture: The combination of dual-write ingestion, transactional outbox queuing, and DLQ replay ensures that not a single payment event gets lost, even during system crashes or external API outages.

What we learned

  • The Reality of Distributed Payment States: We learned the intricate lifecycle differences between payment authorizations, delayed captures, partial refunds, reversals, dispute holds, and processing fees across gateways and merchant systems.
  • Asynchronous Resiliency Patterns: Designing the Transactional Outbox pattern gave us deep practical experience with PostgreSQL locking primitives, distributed claim leases, and jittered exponential backoff.
  • Grounded AI Architecture: We learned that making LLMs useful in high-stakes enterprise applications isn't about giving them free rein; it's about sandwiching them between deterministic boundaries—strict input isolation, structured schema constraints, and programmatic citation verification.

What's next for ReconResolve

  • Multi-Gateway Expansion: Adding native adapters for Stripe, Adyen, Braintree, Shopify Payments, and direct bank clearing feeds (ACH, SEPA, FedNow).
  • Automated Resolution Rules: Introducing configurable, operator-defined auto-resolution policies for deterministic, low-risk breaks (such as automated write-offs for known minor fee discrepancies below a strict threshold).
  • Bi-Directional ERP Integrations: Building native connectors for NetSuite, SAP, and QuickBooks to automatically sync resolved breaks and generate journal entries back into general ledgers.
  • Proactive Anomaly Streaming: Leveraging streaming analytics to flag sudden spikes in settlement delays, gateway failures, or dispute rates before they impact month-end financial closing.

Built With

Share this project:

Updates

Submission history