RFC-005: Finance — Bank & Reconciliation Implementation

• Status: Draft
• Author(s): Aditya Lohumi
• Created: 2026-02-26
• Updated: 2026-02-26
• TSC Decision: Pending
• Supersedes: —
• Superseded by: —

Summary

This RFC covers the implementation of Phase 03 (Bank & Reconciliation) of the finance library. It specifies the business logic for BankAccount, BankStatement (with child BankStatementEntry), and BankReconciliation — including bank statement upload, automated entry matching, and reconciliation finalisation.

Motivation

Organisations of all types require rigorous bank reconciliation to ensure that the general ledger balance matches the bank statement. The reconciliation workflow must support importing bank statements, matching entries against GL postings, and producing a reconciliation statement as an audit artifact.

---

Detailed Design

1. BankStatement Upload & Submission Flow

Draft → [Upload CSV/entries] → Submitted → [Linked to BankReconciliation] → Reconciled

On submit of BankStatement:

1. Validate closing_balance == opening_balance + sum(credits) - sum(debits) across all BankStatementEntry rows.
2. Transition status to "Submitted".

class BankStatementController(Controller[BankStatement]):

    async def before_submit(self, doc: BankStatement) -> None:
        """Validate statement balance integrity."""
        computed_closing = doc.opening_balance
        for entry in doc.entries:
            computed_closing += entry.credit - entry.debit
        if computed_closing != doc.closing_balance:
            raise ValidationError(
                f"Closing balance mismatch. "
                f"Expected {computed_closing}, got {doc.closing_balance}."
            )

    async def after_submit(self, doc: BankStatement) -> None:
        doc.status = "Submitted"

2. Auto-Matching Logic

When a BankReconciliation document is created or refreshed, the system attempts to auto-match BankStatementEntry rows with GL entries:

Matching criteria (in priority order):

1. Exact reference match — BankStatementEntry.reference_number == GL entry’s reference_number
2. Amount + date match — Amount matches within tolerance (±0.01) and date within ±3 business days
3. Unmatched — Entries that cannot be auto-matched are flagged for manual review

class BankReconciliationController(Controller[BankReconciliation]):

    async def before_save(self, doc: BankReconciliation) -> None:
        """Recompute difference on every save."""
        doc.difference = doc.statement_balance - doc.gl_balance

    async def after_submit(self, doc: BankReconciliation) -> None:
        """Finalise reconciliation — mark statement as Reconciled."""
        if doc.difference != Decimal("0"):
            raise ValidationError(
                f"Cannot submit reconciliation with unreconciled difference of {doc.difference}."
            )
        await self.db.set_value(
            BankStatement, doc.bank_statement, "status", "Reconciled"
        )
        doc.status = "Reconciled"

3. BankStatementEntry.is_reconciled Flag

When a BankStatementEntry is matched (manually or automatically) with a GL entry:

• is_reconciled is set to True
• gl_entry is populated with the matched GL entry ID

Unmatched entries remain is_reconciled=False and appear in the outstanding items list on the reconciliation.

4. Reconciliation Difference Categories

The reconciliation view should categorise unreconciled items as:

Category	Description
Deposits in transit	In GL but not yet in bank statement
Outstanding cheques	In GL (payment) but not cleared by bank
Bank charges/errors	In bank statement but no matching GL entry
Timing differences	Date mismatch between GL and bank

5. API Endpoints

In addition to standard CRUD, the following actions are required:

Endpoint	Description
POST /api/resource/BankStatement/{id}/upload_entries	Bulk upload entries from a CSV file
POST /api/resource/BankReconciliation/{id}/auto_match	Trigger auto-matching algorithm
POST /api/resource/BankReconciliation/{id}/clear_matches	Reset all auto-matches
GET /api/resource/BankReconciliation/{id}/statement	Export reconciliation statement PDF

Example

# Create and submit a bank statement
stmt = await BankStatement.new({
    "statement_number": "SBI-2026-04-001",
    "bank_account": "SBI-CURRENT-001",
    "from_date": date(2026, 4, 1),
    "to_date": date(2026, 4, 30),
    "opening_balance": Decimal("100000.00"),
    "closing_balance": Decimal("85000.00"),
    "entries": [
        {"txn_date": date(2026, 4, 5), "description": "Supplier Payment", "debit": Decimal("15000.00"), "credit": Decimal("0"), "reference_number": "CHQ-001"},
    ],
})
await stmt.submit()

# Create reconciliation
rec = await BankReconciliation.new({
    "bank_account": "SBI-CURRENT-001",
    "reconciliation_date": date(2026, 4, 30),
    "bank_statement": stmt.name,
    "statement_balance": Decimal("85000.00"),
    "gl_balance": Decimal("85000.00"),
})
# before_save computes difference = 0
await rec.submit()
# after_submit sets BankStatement.status = "Reconciled"

---

Drawbacks

• Auto-matching by amount+date is heuristic and may produce false positives on bank statements with many similar transactions (e.g. multiple identical salary payments).
• Holding a full BankStatement with potentially thousands of BankStatementEntry rows in memory during validation may be slow for large statements. Pagination or streaming must be evaluated at implementation time.

Alternatives

Alternative	Pros	Cons	Why Not
Manual-only matching	Simple	Labour-intensive for 1000+ entries/month	Poor UX at enterprise scale
External reconciliation tool	Feature-rich	Vendor lock-in, data export overhead	Unnecessary complexity

Migration

No migration required — these are new doctypes with no existing data.

Unresolved Questions

• What CSV format will be accepted for bank statement uploads? (Column mapping, encoding, delimiter)
• Should auto-matching tolerance (±0.01 amount, ±3 days) be configurable in FinanceSettings?
• Is a partial reconciliation state needed (i.e. some entries matched, some outstanding, document not yet submitted)?

Implementation Plan

• Implement BankStatementController.before_submit — balance integrity check
• Implement BankStatementController.after_submit — status transition
• Implement BankReconciliationController.before_save — difference computation
• Implement BankReconciliationController.after_submit — finalisation + statement status update
• Implement auto-matching algorithm (reference match, then amount+date heuristic)
• Implement upload_entries action endpoint (CSV parsing)
• Implement auto_match and clear_matches action endpoints
• Implement statement PDF export endpoint
• Unit tests for all controller methods
• Integration tests for auto-match algorithm with sample bank data

Testing Strategy

Unit Tests

Test	Scenario
test_statement_balance_integrity_pass	Correct closing balance → submit succeeds
test_statement_balance_integrity_fail	Wrong closing balance → ValidationError
test_reconciliation_difference_computed	difference = statement_balance - gl_balance
test_reconciliation_submit_with_difference	Non-zero difference → ValidationError on submit
test_reconciliation_submit_zero_difference	Zero difference → statement marked Reconciled
test_auto_match_by_reference	Reference number match → is_reconciled=True
test_auto_match_by_amount_date	Amount+date match → is_reconciled=True
test_auto_match_no_match	No match → entry stays unreconciled

Integration Tests

• Upload a sample CSV; verify entries are created with correct amounts and dates
• Full reconciliation flow: statement → submit → reconciliation → auto-match → finalise

References

• RFC-003: Finance Library Architecture & Doctypes
• RFC-004: Foundation & F&A Masters