Skip to content

Bank reconciliation

Upload a bank statement CSV, auto-match lines against journal entries / payments / ledger entries on the same financial account, and clean up the mismatches by hand.

1. Overview

BankStatementImport is one uploaded CSV. BankStatementLine is one row of that statement. Every line starts status = 'unmatched' and can end up in one of three states:

stateDiagram-v2
    [*] --> unmatched
    unmatched --> matched: auto-match OR manual match
    unmatched --> ignored: user dismisses (e.g. bank charges)
    matched --> unmatched: unmatch (rare)

A line's matchedEntryType is one of:

  • journal_entry — matched to a JournalEntry.id.
  • payment — matched to a Payment.id.
  • ledger_entry — matched to a LedgerEntry.id.

See the schema at supabase/migrations/20260418150000_bank_reconciliation.sql.

2. CSV formats

The parser targets the "Indian retail banking" CSV shape. One parser (parseSimpleCsv) handles all major Indian retail banks by sniffing header aliases. Known-working: HDFC, ICICI, SBI, plus Axis, Kotak, Yes Bank, JK Bank in practice (header aliases are broad enough).

Reference: src/lib/bankStatementParser.ts:1-13.

Header aliases

src/lib/bankStatementParser.ts:109-122 — the matcher is case-insensitive and trims quotes.

Logical column Matching headers
Date Txn Date, Transaction Date, Value Date, Date, Posting Date
Description Description, Narration, Particulars, Remarks, Details, Transaction Remarks
Debit Debit, Debit Amount, Withdrawal, Withdrawal Amt., Dr, Dr Amount
Credit Credit, Credit Amount, Deposit, Deposit Amt., Cr, Cr Amount
Balance (optional) Balance, Closing Balance, Running Balance, Balance Amount, Balance (INR)

Dates

Default dd/MM/yyyy; also accepts yyyy-MM-dd and dd-MM-yyyy. The parser tolerates separator drift (HDFC uses /, SBI uses -) by trying both.

Amounts

parseAmount (bankStatementParser.ts:153) strips ₹ / Rs / INR prefixes and thousands separators. Empty / - / . → 0.

Row handling

  • Blank rows between real data → silently skipped.
  • Bank-inserted "opening balance" rows (both debit and credit zero) → silently skipped.
  • Rows where debit AND credit are both non-zero → accepted, debit wins (HDFC double-populates occasionally).
  • Errors are collected in parsed.errors[] (per-row) — the import still succeeds with the good rows.

MT940

parseMt940() is a stub that throws "MT940 import is not implemented yet" (bankStatementParser.ts:322-332). Deferred until a customer requires it.

3. Auto-match heuristic

suggestMatches (bankStatementParser.ts:370-408) is a pure function, exercised by the unit tests in bankStatementParser.test.ts.

Parameters per line:

  • amountToleranceInr (default ₹1) — absolute difference on unsigned amounts.
  • dateToleranceDays (default 3) — both sides.
  • Direction check — a credit on the bank (money in) must match a positive candidate; a debit (money out) must match a negative one.

Outcome per line:

Candidates within tolerance Status
Exactly 1 matched → linked automatically
> 1 multiple → flagged for manual choice
0 none → flagged unmatched

The endpoint

POST /finance/bank-imports/:id/auto-match runs the heuristic against every unmatched line on the import. Single-candidate lines are promoted to matched and linked in-place.

4. UI

Finance page → Reconcile tab (Finance.tsx:9988) renders BankReconciliationPanel.tsx (src/components/finance/BankReconciliationPanel.tsx).

Flow:

  1. Upload — pick a financial account (bank/cash leaf ledger), drop the CSV. The UI parses client-side, shows a preview, and on confirm POST /finance/bank-imports with the parsed lines.
  2. Auto-match — button fires POST /finance/bank-imports/:id/auto-match. UI shows matched / multiple / unmatched counts.
  3. Manual match — for multiple and none, the user picks a candidate; POST /finance/bank-imports/:id/lines/:lineId/match links it.
  4. Ignore — dismiss a line you won't reconcile (e.g. bank charges posted by the bank itself). Status flips to ignored.

The banner copy (BankReconciliationPanel.tsx:300) documents the matcher tolerance inline for operators:

The system parses the rows, then the auto-match step looks for journal entries and payments on the same bank account within ±₹1 and ±3 days. Anything with exactly one candidate is linked automatically; multi-candidate and unmatched rows need a manual decision.

5. Endpoints

All handled by handleBankImports (src/lib/api.ts:27148).

Method Path Permission Purpose
GET /finance/bank-imports finance.view List imports (newest first).
POST /finance/bank-imports finance.edit Create an import from parsed lines.
GET /finance/bank-imports/:id finance.view Import header.
GET /finance/bank-imports/:id/lines finance.view All lines of an import.
POST /finance/bank-imports/:id/auto-match finance.edit Run the auto-match pass.
POST /finance/bank-imports/:id/lines/:lineId/match finance.edit Manually match one line to a candidate.

6. Permissions

Per PERMISSIONS.md §4.1, reconciliation is a finance.edit action. There is no dedicated finance.bank.reconcile permission. RLS policies on BankStatementImport + BankStatementLine use auth_user_has_permission('finance.edit') for writes and finance.view for reads (supabase/migrations/20260418150000_bank_reconciliation.sql:85-117).

7. Tests

src/lib/bankStatementParser.test.ts — parser + auto-match unit tests. Fixtures cover:

  • HDFC / ICICI / SBI header variants.
  • Currency symbol stripping and thousands separators.
  • Blank-row padding and "both debit + credit" rows.
  • suggestMatches direction / tolerance cases.

Run: npx vitest run src/lib/bankStatementParser.test.ts.