Payment Reconciliation & Unreconciliation Engine

A design reference for matching unallocated credits against outstanding invoices, running that match at batch scale, and safely undoing a wrong match

1. Requirements

1.1 Functional requirements

Given a party (customer or supplier) and one receivable/payable Ledger Account, fetch every candidate on both sides: outstanding invoices (including return/credit-debit notes) on one side, unallocated credits (unapplied advances, journal-entry credits) on the other.
Both lists must be filterable independently — posting-date range, min/max amount, a free-text name filter, cost center, and active accounting dimensions.
Auto-propose an allocation using a deterministic, oldest-first waterfall, splitting a payment across several invoices or an invoice across several payments, with no side ever over-allocated.
Let a user hand-edit a proposed allocation’s amount before committing, with the cross-currency difference amount recalculated live.
Handle multi-currency: when the party account’s currency differs from the legal entity’s base currency and the invoice’s and payment’s exchange rates disagree, post the difference through an auto-generated exchange gain/loss journal, on a configurable date.
Treat a return note as a distinct case: reconciling one against an invoice creates a dedicated credit/debit-note journal rather than a plain reference update.
Offer the identical fetch-allocate-commit sequence as an unattended background job scoped to the same (legal entity, party, account) filter, so a large backlog can be worked without a user at the interactive tool.
Guard against two processes — an interactive commit and a job, or two jobs — working the same party/account combination at once.
Provide a safe undo: given a settlement or journal voucher, list every invoice/order it currently pays, let the user pick which links to reverse, and undo exactly those without touching the rest or deleting history.

1.2 Non-functional requirements

Resumability: a background run must survive a pause, restart, or mid-run failure without redoing committed work.
Staleness safety: nothing may commit against a voucher whose amount changed since it was fetched — a concurrent edit must be rejected, not overwritten.
Auditability: both paths retain a stored error trail on failure without losing partial progress.
Non-destructive undo: reversing a match de-links rather than deletes — the original voucher and its history stay inspectable.
Bounded interactive load: the tool caps candidate rows per side so the screen stays usable on a long history.

1.3 Constraints

Reconciliation and un-reconciliation both operate within one legal entity, one receivable/payable Ledger Account, and one party at a time — no cross-party or cross-entity matching.
The safe-undo path only accepts a settlement or journal voucher as the “paying” side; an invoice-side posting is never itself the subject of an undo request.
The interactive tool holds no persistent state between sessions — a scratch workspace, not a saved record. Only the background job’s log persists progress.
Undo granularity is whole-link: an entire allocated amount between one voucher and one invoice is reversed as a unit; partial-amount rewind is not supported (see § 6).

2. High-Level Design

2.2 Two entry points converge on one core

The interactive workbench and the background job share the same fetch and allocation routines, differing only in row limits and in how the result gets committed:

Reconciliation Workbench — the interactive tool, never written to a table; exists only in memory for the session. Requires a legal entity, party type/party, and receivable/payable Ledger Account; optionally a default advance account (for advances booked to a dedicated account), cost center, accounting-dimension values, a bank/cash-account filter for journal candidates, and separate date/amount ranges and row-count caps per side (0 = unlimited).

Outstanding Invoice Row — one invoice candidate: type (sales, purchase, or journal voucher), number, date, original amount, currency, outstanding amount. Sourced from the Party Balance Entry table (the receivable/payable signed-amount table from the general-ledger design), filtered to nonzero-outstanding rows, sorted oldest first.

Unallocated Credit Row — one credit candidate: an unallocated settlement voucher (or its remainder), a journal credit/debit with no order reference, or a return note’s outstanding balance as a negative-amount row. Also oldest-first, same row cap.

Proposed Allocation — one match line, produced by the algorithm or hand-edited:

{ reference_type/name:     the paying voucher (settlement or journal)
  invoice_type/number:     the invoice or return note being paid
  allocated_amount:        how much of this voucher applies here
  unreconciled_amount:     the voucher's amount before this row
  difference_amount:       exchange-rate mismatch, if any
  difference_account:      where the mismatch posts (editable)
  exchange_rate:           invoice's own rate, resolved per invoice type
  gain_loss_posting_date:  which date the difference journal uses }

Reconciliation Job — submittable, queueable counterpart to the workbench, scoped to the same filters plus date/cost-center/bank-account filters only (no amount or name filters, since it sweeps rather than targets). Status: blank, Queued, Running, Paused, Completed, Partially Reconciled, Failed, Cancelled.

Reconciliation Job Log — the execution record for one Job: two flags (fetched-and-allocated? fully committed?), a committed/total counter, an error trail, and a child table of every proposed allocation row from one allocation pass, each tagged committed or not — the flag that makes the job resumable.

Unreconciliation Request — the undo document. Targets exactly one settlement or journal voucher (validated up front), with a child table of every link that voucher currently holds.

Undo Allocation Row — one link: account, party, the invoice/order it pays, the allocated amount, currency, and an “unlinked” flag set once that link is actually reversed.

3.2 Allocation algorithm

A single deterministic pass, not a search. Both lists arrive pre-sorted oldest-first; credits are the outer loop, invoices the inner loop, and the invoice list’s remaining amounts are shared state across the outer loop:

for each credit (oldest first):
    remaining = credit.amount
    for each invoice (oldest first, shared across credits):
        if remaining >= invoice.outstanding_amount:
            allocate invoice.outstanding_amount; remaining -= that; invoice.outstanding = 0
        else:
            allocate remaining; invoice.outstanding -= remaining; remaining = 0
        stop this credit if remaining == 0; else move to next invoice if this one is now 0

So one invoice can be paid by several credits, and one credit split across several invoices, both strictly oldest-first. Zero-amount rows are dropped.

The difference amount is computed only when the account currency differs from the legal entity’s base currency and the invoice’s exchange rate disagrees with the paying voucher’s; the sign flips for a payable account where the “invoice” side is itself another voucher (an adhoc payment offsetting a return). An advance always uses the payment’s posting date for the difference journal; everything else follows a configurable policy (invoice date, payment date, or run date).

Two independent over-allocation guards apply: on the workbench, a row cannot exceed the credit’s remaining amount or the invoice’s outstanding amount beyond a small rounding tolerance; inside the shared commit routine, a second check rejects a negative or over-limit allocated amount, so a job’s chunk gets the same protection a manual commit gets. If any row carries a nonzero difference, the interactive path prompts the user to confirm the difference account and date before committing.

3.3 Batch execution and resumability

A Reconciliation Job starts via an admin action (write permission plus a global auto-reconciliation switch) or a scheduled sweep that pulls queued jobs, de-duplicates by the exact (legal entity, party type, party, account, default advance account) tuple, and starts one job per unique combination up to a configurable queue size.

An orchestrator function re-invokes itself after each step:

Create the Job Log (Running), enqueue fetch-and-allocate.
Run the § 3.2 fetch and allocation once, row caps forced far larger than the interactive default; copy every row into the Log’s checklist as not-committed; record the total.
Find the next contiguous not-committed rows sharing one paying voucher and enqueue a commit for that group only — never the whole backlog at once. The task’s name embeds the group’s row range, so it can’t be double-enqueued while running.
Commit that voucher’s rows via the shared commit routine, flip its rows to committed, refresh the counter.
If committed == total, mark Log and Job Completed; otherwise, unless paused meanwhile, self-enqueue the next voucher’s chunk.

Per-voucher chunking bounds task size and keeps a voucher’s postings atomic. Resumability follows directly: “what’s left” is re-derived from not-committed rows each time, so a pause, crash, or restart loses at most the in-flight chunk. Two guards prevent contention: one blocks a second job or manual commit against the identical filter while one is running/paused; the other stops the same chunk being enqueued twice. A chunk’s exception rolls back only that chunk and records the traceback on both Log and Job, landing the Job Partially Reconciled if earlier chunks succeeded, or Failed if none had.

3.4 Un-reconciliation mechanics

An Unreconciliation Request targets one settlement or journal voucher and, on demand, pulls every link it holds from the Party Balance Entry table (plus, for advances in a dedicated account, the parallel Advance Balance Entry rows) — one row per distinct invoice/order it pays, with its amount. A bulk helper can create and submit one request per voucher from a multi-row selection, pre-filtered to the picked links.

On submission, for each selected link: the reference is removed from this voucher’s side only (a journal voucher’s reference fields are cleared on the matching row; a settlement voucher’s reference row is zeroed, its totals recomputed, per-reference postings reversed); affected Party Balance Entry rows have their against-voucher pointer flipped back to themselves (“no longer applied elsewhere”), and matching Advance Balance Entry rows are marked de-linked, never deleted; any exchange gain/loss journal for exactly this (invoice, voucher) pairing is cancelled, not deleted, leaving other pairings untouched; the invoice’s outstanding amount is recomputed from its remaining linked rows and persisted, refreshing linked overdue-notice records and status; and the Undo Allocation Row is flagged unlinked.

This rebuilds the paying voucher’s own Ledger Postings and Party Balance Entry rows from scratch — the same low-level mechanism the repost tooling described separately relies on, scoped here to one voucher. The voucher itself is never cancelled and remains reusable. Undo is whole-link only, not a partial-amount rewind — a gap the source’s own comments flag as future work.

3.5 Error handling and safety guards

Staleness check: before mutating, the commit routine re-reads the live voucher and re-verifies the amount it’s about to adjust still matches what was fetched, throwing rather than overwriting if it changed; a similar check exists for return-note commits.
Amount guards: a negative or over-limit allocated amount is rejected inside the shared commit routine, independent of the workbench’s own pre-commit check.
Concurrency guard: a running/paused job (or an in-progress manual commit) for one (legal entity, party type, party, account) combination blocks a second one on the same combination.
Batch failure isolation: a chunk’s exception rolls back only that chunk; a partially-failed job still reports prior committed rows.
Undo type guard: an Unreconciliation Request rejects any voucher type other than settlement/journal up front.

4. Scale and Reliability

Load shape: bursty per party around invoicing/collection cycles — a large backlog is one long-running job, not steady traffic.
Row limits as the lever: the interactive tool defaults to a modest cap per side (0 = unlimited); the job forces both caps far higher, since nothing renders to a screen.
Chunk size = one voucher: bounds the blast radius of failure and keeps a voucher’s postings atomic across tasks.
Counter-driven resumability: every checklist row is independently flagged, so “what’s left” is a live query, not a fragile position pointer.
Fan-out throttling: the scheduled sweep caps jobs started per run and de-duplicates by filter, so a burst of queued jobs doesn’t all launch at once, and two jobs on the identical party/account never run concurrently.
Scoped repost cost: a commit and an undo alike rebuild only the affected voucher’s postings, not a whole party’s ledger — the heavier repost mechanism (covered separately) is deliberately not invoked here.
Failure visibility: Job and Log both retain a full error trail, and status distinguishes total failure from partial.

5. Trade-off Analysis

Decision	Trade-off
Deterministic oldest-first waterfall, no configurable strategy	Cheap and predictable, editable before commit — but deliberately mismatched invoices/credits get cleared oldest-first, not necessarily as intended.
Whole-link undo, no partial-amount rewind	Simple, trivially preserves integrity — but a voucher misapplied for only part of its amount can’t be surgically corrected; the whole link must be redone.
Interactive workbench is a non-persisted scratch workspace	Zero setup, no stray records — but an in-progress session doesn’t survive a reload; only the job’s Log persists durably.
Batch job chunked per voucher, not per row or whole job	Bounds failure cost, keeps postings atomic — but many single-line vouchers pay a per-enqueue overhead each.
De-linking instead of deleting on undo	Full audit trail of what was matched and reversed — but balance tables grow indefinitely; nothing here purges de-linked history.
Concurrency guard scoped to (entity, party, account), not the invoice	Cheap, one query, prevents the realistic conflict — but coarser than needed: unrelated interactive work is blocked even while a job works different invoices for that same party/account.

6. What to Revisit as the System Grows

Partial-amount undo: only whole links reverse today; the source’s own commentary already flags more granular unreconciliation as unbuilt.
Configurable allocation ordering: the strict waterfall has no hook to pin specific pairs before the greedy pass runs, for parties with recurring out-of-order settlement patterns.
Chunk granularity at high volume: per-voucher chunking suits a typical backlog, but batching several small vouchers per task would help once per-enqueue overhead dominates.
Narrower concurrency scope: sharpening the guard from the whole (entity, party, account) tuple to the specific vouchers a chunk touches would unblock unrelated interactive work sooner.
Retention of de-linked history: the non-destructive undo design accumulates de-linked rows with no aging policy — worth revisiting once that history weighs on the outstanding-balance queries this engine depends on.