Bank Reconciliation & Statement Import Pipeline

A design reference for turning an imported bank statement into matched, cleared accounting entries

1. Requirements

1.1 Functional requirements

• Ingest a bank statement file in CSV, XLSX/XLS, or the MT940 structured interchange format, or pull rows from a publicly accessible linked spreadsheet, for a specific bank account belonging to a specific legal entity.
• Let each banking institution keep a reusable column-to-field mapping, so repeat imports of the same bank’s export layout don’t require re-mapping columns every time.
• Represent every imported row as its own line-item record — deposit or withdrawal amount, currency, free-text description, the counter-party details as printed on the statement, a bank-supplied transaction id/type, and any statement-listed fee — that can be tracked independently from “just arrived” through to “fully accounted for.”
• Propose which existing accounting document (or documents) a statement line most likely corresponds to as a ranked list rather than a single guess, so a person can accept the top suggestion or pick a lower-ranked one.
• Support partial correspondence in both directions: one statement line covering only part of a voucher’s amount, or one statement line being claimed piecemeal across several vouchers, and the reverse — several statement lines slowly paying off one large voucher.
• Support a rule-based classifier, configured once per description text / amount range / direction, so recurring low-ambiguity lines (bank fees, standing transfers) don’t need manual attention on every import.
• Detect which internal party (customer, supplier, or employee) a statement line’s counter-party most likely refers to, without being asked — using an exact account-number/IBAN identifier first, and a fuzzy name comparison only as a fallback.
• Let a person work from either direction: start from an imported statement line and find its voucher, or start from a ledger account and a date range and directly set or clear which vouchers count as “cleared by the bank,” independent of whether any statement was ever imported.
• Recognize same-institution internal transfers — a withdrawal on one account paired with a matching deposit on another — and let a person collapse both sides into a single transfer posting in one action.

1.2 Non-functional requirements

• Idempotent classification: re-running the rule engine, or re-importing a file, must not double-classify or double-post; a line that has already been evaluated is skipped unless a person explicitly forces re-evaluation.
• Resilience under partial failure: a large import runs as a background job, tracks success and failure per row, and lets a person re-export and retry only the rows that failed rather than the whole file.
• No silent over-allocation: the settlement math refuses to let more be claimed against a voucher’s actual posted amount than that amount supports, summed across every statement line that references it, and raises an error rather than guessing.
• Auditability: every automatic classification and every settlement link is traceable — a line records which rule fired against it, and each link records whether it came from a match against an existing voucher or from a voucher the tool created on the spot.
• Currency integrity: a statement line’s currency must agree with its bank account’s own ledger currency; cross-currency conversion is only invoked when a new posting is created from scratch, never when matching against one that already exists.

1.3 Constraints

• Single deployment, same database as the rest of the books — reconciliation queries the general ledger directly (to make exact multi-leg clearance decisions on journal-style postings) rather than through an abstraction layer.
• The ranked-candidate query set is fixed to a small number of document types per direction — deposits only search receivable-side documents, withdrawals only payable-side ones — it is not a generic full-table search.
• The automatic bulk reconciler is deliberately more conservative than the interactive workbench: it only ever acts on an exact reference-number match, never on amount or party similarity alone.
• Party auto-detection is opt-in per legal entity and depends on a second, separate opt-in (fuzzy matching); without both settings enabled, only exact account/IBAN identification runs.

2. High-Level Design

2.1 Component diagram

2.2 Data flow — statement crossing into the system

The statement file (or spreadsheet) is the one place this module talks to something outside its own database, so it is worth drawing as a sequence rather than a procedure list:

Two source-specific details worth calling out under this flow:

• MT940 preprocessing. Before an MT940 file reaches the parser, a text-level fix-up truncates any statement-number field (:28C:) longer than five digits — some banks emit longer numbers than the standard allows, which would otherwise abort parsing. The corrected content is parsed into transactions and rewritten as an ordinary CSV (date, deposit, withdrawal, description, reference number, bank account, currency), then re-enters the same import path as a native CSV file — MT940 is a pre-conversion step, not a separate ingestion path.
• Column mapping is shared, not per-job. The mapping lives once per banking institution and is re-synchronized from whatever a person confirms during any one import’s preview, so mapping a bank’s export layout once benefits every future statement from that institution.

3. Deep Dive

3.1 Data model

Legal Entity and Ledger Account are as defined in the general-ledger design: every bank account belongs to one legal entity’s books, and its ledger side is a single leaf account with a fixed currency.

Banking Institution The master record for the bank itself (as opposed to a specific account at that bank). Owns the reusable Column Mapping Table — a small list of {bank statement column name, target field on Bank Feed Line} pairs — shared by every account held at that institution and by every future import from it.

Bank Account Links a physical account number/IBAN, its currency, and its owning legal entity to a specific ledger account. Two flags on it change downstream behavior: whether it counts as a “company” account (only these are offered in the reconciliation workbench), and whether it is a credit-card account (which changes which kind of correcting journal posting a bulk “create and reconcile” action produces).

Statement Import Job The record that drives one import run: the target legal entity and bank account, the attached file or spreadsheet URL, an MT940-format flag, optional custom delimiter characters, and a status of Pending / Success / Partial Success / Error. It caches the resolved column mapping as JSON at creation time and hands the actual row-by-row insert work to a background job so a large file does not block the request that started it.

Bank Feed Line One row per imported statement entry. Carries the date, deposit and withdrawal amounts (only one is ever non-zero), currency, free-text description, reference number, a bank-supplied transaction id/type, the counter-party’s name/account-number/IBAN exactly as printed on the statement (bank_party_name, bank_party_account_number, bank_party_iban — distinct from the internally-resolved party/party_type), and two fee fields (included_fee, an amount already netted out of the withdrawal by the bank; excluded_fee, a fee quoted separately that this module folds into the withdrawal and re-labels as included on save, so all downstream math only ever has to handle one fee shape). It also carries allocated_amount and unallocated_amount — the latter recomputed on every save as abs(withdrawal - deposit) - allocated_amount — and a list of Settlement Links.

Its status lifecycle:

Settlement Link A row on a Bank Feed Line joining it to one existing voucher (or to another Bank Feed Line, for the refund/reversal case): payment_document + payment_entry identify the target, allocated_amount is how much of this line has been claimed against it, clearance_date mirrors the date this module wrote back onto the target voucher, and reconciliation_type is either Matched (linked to something that already existed) or Voucher Created (the workbench created the voucher on the spot). The same (payment_document, payment_entry) pair cannot appear twice on one line.

Auto-Match Rule A per-legal-entity classification rule with an auto-assigned priority (new rules go to the back of the queue unless a priority is given explicitly), an optional transaction-type filter (Any / Withdrawal / Deposit), an optional min/max amount band, and a list of Description Conditions. Its classify_as outcome is Bank Entry (a single ledger account, or a multi-account split whose per-row debit/credit can be a small arithmetic formula over transaction_amount), Payment Entry (a party + party account), or Transfer. Rules are evaluated in priority order and the classifier stops at the first match, stamping matched_transaction_rule and is_rule_evaluated on the line — it does not itself post anything; it only tags the line for whatever downstream action a person or the front end takes next.

Description Condition One row of a rule: a check (Contains, Starts With, Ends With, Regex) and a value, compared case-insensitively against the line’s description. Conditions on a rule are evaluated with OR semantics — the rule fires as soon as any one condition matches, not only when all of them do.

Reconciliation Workbench The interactive matching surface: scoped to one legal entity, one bank account, and a date window (posting-date or reference/cheque-date based). It surfaces ranked candidates for a line, lets a person accept a match, bulk-auto-reconcile many lines at once, or create a brand-new voucher (a manual journal posting, a payment voucher, an internal transfer, or a bulk payment voucher against one party) and reconcile it in the same action. It also renders opening/closing balance figures by combining the ledger’s running balance with entries the report layer flags as not yet reflected in the system.

Clearance Worksheet A separate, simpler tool scoped only to one ledger account and a date range — it does not go through Bank Feed Lines at all. It pulls every payment voucher, journal posting, direct-paid purchase invoice, or point-of-sale collection row touching that account in the window (optionally including ones already cleared) and lets a person batch-set or batch-clear the clearance date directly — the fallback path for clearing vouchers against a paper statement.

3.2 The candidate-ranking algorithm

Given a Bank Feed Line, the workbench asks for matching candidates across whichever document types the caller specifies, and gets back a single list sorted by a computed integer rank, highest first. Each document type has its own query, but they share the same shape:

Hard filters (must pass to appear at all):

• Direction: withdrawals only look at pay-type vouchers and directly-paid purchase invoices; deposits only look at receive-type vouchers, a sales invoice’s own embedded collection row, and (for internal-transfer detection elsewhere) the opposite account.
• Same ledger account: the candidate must actually touch the bank’s own ledger account, not just belong to the same company.
• Not already cleared: a candidate with a clearance date already set is excluded, except for the Bank Feed Line self-match query, which instead excludes lines already Reconciled and never matches a line against itself.
• Inside the requested window: either the posting-date range, or — if reference-date filtering is toggled — the voucher’s own reference/cheque-date range.
• Non-zero amount; and if the caller asked for exact-match mode, the amount must equal the line’s unallocated amount precisely rather than merely be non-zero.

Ranking (added on top of the hard filters, highest score wins):

• +1 baseline for appearing at all.
• +1 if the candidate’s reference number equals the line’s reference number exactly.
• +1 if the candidate’s amount equals the line’s unallocated amount exactly.
• +1 if the candidate’s party type and party both equal the line’s already-known (or already-resolved) party.
• The Bank Feed Line self-match query adds one further point if the two lines’ unallocated amounts match exactly, on top of the same reference/party/amount bonuses.

Before the ranked list is handed back, each candidate’s displayed amount is reduced by whatever has already been claimed against that same ledger account by other Bank Feed Lines referencing it — computed by summing existing settlement links per account/voucher and taking the most recent claiming date — so a voucher that is already half-claimed by a previous partial match is never suggested as if it were still whole.

The automatic bulk reconciler is a stricter subset of this same query set, not a separate algorithm: when invoked from the bulk auto-reconcile action, every one of these queries adds a hard filter that the candidate’s reference number must equal the line’s reference number exactly. Ranking still runs on the results, but because every survivor already has a matching reference number, the amount and party bonuses only ever act as tie-breakers among reference-confirmed candidates — the automatic path never accepts a match on amount or party similarity alone.

Internal transfers are matched by a separate, narrower search, not the ranked-candidate list above: given one line, it looks for exactly one other Bank Feed Line on a different bank account, within a configurable number of days (three, by default) of the same date, with exactly opposite deposit/withdrawal amounts and a status of Unreconciled. If precisely one such mirror line exists, creating the transfer reconciles both sides at once with a single new transfer-type payment voucher; otherwise the person matches manually.

3.3 The settlement allocation algorithm

Once one or more candidates are accepted (or a new voucher is created), the workbench appends zero-allocation settlement links and lets the save cycle compute the real numbers. For each pending link, in turn, against the line’s remaining unallocated balance:

1. If the target is another Bank Feed Line (a refund/reversal booked back into the same account): the entire remaining balance of that other line is claimable, the two lines are linked in both directions, and the link always resolves immediately — no clearance date bookkeeping is needed because both sides are Bank Feed Lines, not vouchers.
2. Otherwise, the claimable amount is (the voucher's own posted amount on this ledger account) − (amount already claimed against it by other Bank Feed Lines on this account). For a manual journal posting with more than one leg touching the bank account, the posted amount is the sum of every such leg’s actual ledger posting (the general-ledger design’s persisted debit/credit row), not the voucher’s face total — and the voucher is only considered fully cleared once every bank-touching leg, on every account it touches, is fully claimed, not just the leg this particular line is settling.

From there, four outcomes are possible per link:

• Zero — nothing left to claim (some other line already covers it); if the voucher-wide clearance condition above is now satisfied, its clearance date is still (re)set, and the link is dropped either way.
• Positive, within the line’s remaining balance — the full claimable amount is allocated, the clearance date is set (to the later of this line’s date and any date already recorded by an earlier partial claim, so it never moves backwards), and the remaining balance shrinks by that amount.
• Positive, but exceeds the line’s remaining balance — only what’s left is allocated; the clearance date is deliberately not set, since the voucher still isn’t fully claimed. This is the multi-match case: one voucher filled gradually by several Bank Feed Lines. Once remaining balance hits zero, further pending links on the same save are simply dropped, and reconciling an already-fully-allocated line is rejected outright.
• Negative — prior links have already claimed more against the voucher than its posted amount supports; a hard error, not a silent clamp.

3.4 Party auto-detection

On submission — only if the legal entity has this behavior enabled, and only if a party is not already set — the line’s counter-party is resolved in two ordered passes, stopping at the first hit:

1. Exact identifier match. The statement-supplied account number or IBAN is looked up first against any bank account in the system that already has a party attached, then, if nothing matches, against employee bank-detail fields.
2. Fuzzy name/description match (a second, independent opt-in). The statement’s counter-party name and free-text description are each compared, using a token-set fuzzy-matching score, against customer, supplier, and employee display names — tried in the order most likely to pay the business first for a deposit (customer, then supplier, then employee) or most likely to be paid by the business first for a withdrawal (supplier, then employee, then customer). A match is only accepted above a fixed score cutoff, and — deliberately — a result that is ambiguous (two candidates tied at or above the cutoff) is discarded rather than guessed at, even though a match search did technically run.

3.5 Illustrative interaction contract

POST /bank-statement-imports
  { company, bank_account, bank, import_file | google_sheets_url }
  → resolves/caches the banking institution's column mapping
  → returns { status: "Pending" }

POST /bank-statement-imports/{id}/start
  → validates a Bank Account column is present in the parsed file
  → enqueues background import
  → returns a background job id

GET /bank-statement-imports/{id}/status
  → returns { status, success_count, failed_count, total_records }

POST /bank-transactions/{id}/linked-payments
  { document_types: [...], from_date, to_date, exact_match? }
  → returns ranked candidates, amounts net of already-claimed allocations

POST /bank-transactions/{id}/reconcile
  { vouchers: [{ payment_document, payment_name, amount }] }
  → appends settlement links, runs the allocation algorithm, updates status

POST /bank-transactions/auto-reconcile
  { bank_account, from_date, to_date }
  → runs the same candidate search with reference-number hard-filtering
  → reconciles or partially reconciles in bulk, reports counts back

3.6 Error handling and edge cases

• Currency mismatch: a line’s currency must equal its bank account’s ledger currency at validation time — this is checked before any matching logic runs, so a mismatched line simply cannot be saved rather than silently matching against the wrong-currency vouchers.
• Duplicate settlement links: the same voucher cannot be linked twice on one line; attempting it raises an error rather than double-counting.
• Over-allocation: as above, a negative claimable amount is a hard stop, not a clamp — this protects against two people (or a rule action and a manual match) both claiming the same voucher at once.
• Fee directionality: a statement-quoted fee larger than the deposit it is meant to be netted from, or a fee applied when both deposit and withdrawal are non-zero on the same line, is rejected outright rather than producing a negative or ambiguous amount.
• Party-matching failures are non-fatal: if the matching pass itself throws (a lookup error, a malformed configuration), the line still submits with its party fields left unset, so a matching-layer bug never blocks the transaction from being recorded.
• Import row failures are isolated: a background import records success/failure per row rather than aborting the whole file; the failed subset is re-exportable so a person can fix and retry only what actually failed.
• Unreconciling is explicit and reversible: a link whose reconciliation_type was Voucher Created cancels that voucher outright on unlink (since the reconciliation module produced it), while a link that was Matched against something pre-existing is only unlinked, leaving the original voucher untouched.
• Scope boundary: this module changes only clearance dates and settlement links; it never recomputes an invoice’s own outstanding receivable/payable figure (the general-ledger design’s Party Balance Entry), which stays owned by whatever voucher logic created or amended the invoice.

4. Scale and Reliability

• Load pattern: bursty and periodic — a full statement import happens once per bank account per reconciliation cycle (often monthly), while candidate-ranking queries are read-heavy and triggered by interactive workbench use. Neither is a sustained high-throughput path.
• Background execution for imports: row-by-row insertion runs as an enqueued background job rather than inline with the request, so a large file doesn’t tie up or time out a web request; progress is reported back incrementally.
• Batched auto-reconciliation: bulk auto-reconcile splits large candidate sets into fixed-size batches and enqueues each batch separately once the transaction count crosses a threshold, rather than processing an unbounded list synchronously.
• Query shape as the main scaling lever: ranking queries are hard-filtered first (ledger account, direction, date window, clearance state) before any ranking arithmetic runs, so ranking itself only ever operates over an already-small candidate set — the expensive part is the underlying indexes, not the ranking logic.
• Idempotent rule evaluation: the rule sweep only considers lines that are Unreconciled and not yet evaluated, unless a person forces a full re-evaluation, so a scheduled run and a manual re-run can both fire without reprocessing the same line twice.
• Failure isolation: import failures are tracked per row and settlement failures (over-allocation, duplicate links) raise rather than silently degrade, so a single bad row or match attempt cannot corrupt the rest of a batch.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• Confidence scoring beyond small integer ranks: as more signals accumulate (historical match patterns per party, learned per-bank quirks), a genuine weighted-confidence model would distinguish “obviously the same voucher” from “technically top-ranked but still a coin flip” better than a tie-prone additive score can.
• Loosening the automatic reconciler’s reference-number requirement: for banks whose exports rarely populate a clean reference number, a safer middle ground — exact amount and exact party and a tight date window, without requiring the reference field — would recover automation that today falls back to manual review over one missing field.
• Closing the loop from rule match to posting: a matched Auto-Match Rule only tags a line today; wiring a configurable follow-on action directly into the rule (create this kind of voucher, auto-accept this classification) would remove a manual step for lines the engine already identifies correctly.
• Unifying the two clearance-writing surfaces: as usage of the ledger-driven Clearance Worksheet and the statement-driven Reconciliation Workbench both grow, converging them onto one reconciliation history — rather than two tools that happen to write the same field — would keep the audit trail legible at scale.
• Party auto-detection coverage: seeding more parties with exact bank identifiers, instead of leaning on the fuzzy fallback, would move volume out of the lower-confidence path and reduce the ambiguous, deliberately-discarded results a person has to resolve by hand.