Bank Statement Import and Matching

CommunityPay imports bank statements in three formats, automatically matches transactions against ledger records using an 8-strategy priority engine, and connects the results to the GL integrity scan system. This page describes the full pipeline from file upload through reconciliation.

Supported Formats

The system accepts three bank statement formats. Format is auto-detected from file content — the user does not need to specify which format they are uploading.

BAI2 and OFX files are fully structured — they skip column mapping and parse directly into transactions. CSV files require a column mapping step because column layouts vary by bank.

Import Pipeline

The import follows a 3-step wizard. BAI2 and OFX skip Step 2 (column mapping) because their structure is self-describing.

Step 1: Upload

Navigation: Reconciliation tab > Statement Imports > Import Statement

Page title: Import Bank Statement — Step 1 of 3: Select bank account and upload statement file.

The user selects a bank account from a dropdown and uploads the statement file. Accepted extensions: .csv, .txt, .bai2, .bai, .ofx, .qfx. The help text reads: "CSV, BAI2, OFX/QFX — format is auto-detected."

Button: Upload and Continue

The system detects the file format from content signatures. If BAI2 or OFX is detected, the file parses immediately and advances to Step 3 (Match Review). If CSV is detected, the user proceeds to Step 2.

Step 2: Column Mapping (CSV only)

Page title: Map Columns — Step 2 of 3: Map CSV columns to transaction fields.

The page displays a file preview table showing detected headers and sample data rows. Below the preview, the user maps columns using dropdown selectors:

Either Amount or Debit/Credit columns are required — not both.

Additional options: - Reverse sign convention checkbox: For banks that use positive for withdrawals and negative for deposits. - Date Format Override: Text input for explicit date format (e.g., %m/%d/%Y). Leave blank for auto-detection.

A sidebar collects optional Statement Balances (begin date, end date, beginning balance, ending balance) which enable integrity verification if provided.

The system auto-suggests column mappings based on 70+ recognized header patterns (e.g., "Transaction Date", "Tran Date", "Date Posted" all map to the date field).

Button: Parse and Continue

Step 3: Match Review

Page title: Review Matches — Step 3 of 3: Accept or reject proposed matches, then apply to reconciliation.

This is the primary review interface. It has four main zones:

Integrity Verification (top): Three status boxes showing pass/fail/not-available for: - Transaction count verification (parsed count matches control total) - Balance verification (computed vs. stated ending balance) - Hash verification (data integrity check)

Each box shows green checkmark text for pass, red X text for fail, or grey dash for not available.

Summary Statistics: Five stat cards showing Total Lines, Matched (green), Unmatched (red), Deposits total, and Withdrawals total.

Proposed Matches section: A stacked card list (not tabs) of matched transactions. Each match shows the bank line, the matched ledger record, the matching strategy that found it, and a confidence badge (Exact, High, Medium, or Low). Each match has Accept and Reject buttons that fire AJAX requests and reload the page.

Above the matches: an Accept All Exact button that bulk-accepts all exact-confidence matches in one action.

Unmatched Lines section: Bank lines with no ledger match. Each unmatched line shows three inline action buttons: - Bank Fee — creates a journal entry recording a bank fee (DR: Bank Fee Expense, CR: Cash) - Interest — creates a journal entry recording bank interest (DR: Cash, CR: Interest Income) - Exclude — marks the line as excluded from matching (e.g., duplicate or irrelevant)

These are inline buttons, not a dropdown menu.

Excluded Lines section: Lines that have been manually excluded.

For large imports (100+ lines), matching runs asynchronously via Celery. The page shows a blue "Matching in Progress" banner with automatic 5-second refresh until matching completes.

Final action: Apply Accepted Matches — converts all accepted matches into BankReconciliationItem records via the ReconciliationBridge service and creates journal entries for any bank fees or interest recorded during the review.

Matching Engine

The TransactionMatchingEngine evaluates each bank statement line against 8 strategies in priority order. The first strategy to produce a match wins. Strategies are direction-aware — deposit-only strategies skip withdrawal lines and vice versa.

Strategy Priority Order

Confidence Levels

Each match receives a confidence score that determines its badge:

The engine performs duplicate detection — if a ledger record has already been matched to a different bank line (in the current import or a previous one), it is not matched again.

Reconciliation Bridge

When the user clicks Apply Accepted Matches, the ReconciliationBridge service converts the accepted matches into formal reconciliation records:

1. Each accepted match creates a BankReconciliationItem linking the bank statement line to the matched ledger record
2. Bank fee matches auto-create balanced journal entries via JournalEngine (DR: Bank Fee Expense, CR: Cash)
3. Interest matches auto-create balanced journal entries via JournalEngine (DR: Cash, CR: Interest Income)
4. All auto-created journal entries flow through the enforcement dispatcher — guard chain evaluation, immutable decision logging, the full enforcement path

The bridge does not bypass any accounting controls. A bank fee journal entry receives the same enforcement decision as a manual journal entry.

BAI2 Parser

The BAI2 parser handles the commercial banking standard with production-grade features:

• Integrity verification: 6 checks — control totals, balance integrity, transaction count, group/file consistency
• 100+ transaction type codes: Full BAI2 type code registry with human-readable descriptions
• Classification: Transactions classified as loan (6xx codes), fee, wire, ACH, or check based on type code
• Value-date extraction: Funds availability type V for value-dated transactions
• Multi-group/multi-account: Handles files with multiple bank groups and accounts
• Bank-specific quirk handling: Accommodates known formatting variations from Wells Fargo, JPMorgan, and Bank of America

OFX/QFX Parser

The OFX parser handles both versions of the Open Financial Exchange standard:

• OFX 1.x (SGML): Automatically closes unclosed tags per the SGML spec
• OFX 2.x (XML): Standard XML parsing
• Credit card support: Handles CCSTMTRS (credit card statement) response elements
• FITID matching: Uses the Financial Institution Transaction ID as the primary deduplication reference
• CURDEF extraction: Reads currency designation from the statement header
• Date range: Extracts DTSTART/DTEND for statement period bounds

Connection to Integrity Scanning

Bank statement import feeds directly into the ledger integrity scan system. The scan runs 6 automated checks on the general ledger:

Viewing Scan Results

Navigation: Insights tab > Audit > Scans

The scan history page shows: - KPI cards: Latest status, total scans, last scan date, checks passed count, findings (critical/warning/info) - Run Scan button: Triggers an on-demand scan - Scan history table: Date, type, status, checks, findings by severity, duration, content hash

Status display: - GREEN: Plain text "Passed" (no badge) - YELLOW: Badge "Warnings" - RED: Badge "Failed"

Each scan creates an immutable IntegritySnapshot with a SHA-256 content hash. Clicking a scan row shows the detail page with findings grouped by severity (CRITICAL > HIGH > MEDIUM > LOW > INFO) and delta comparison against the previous scan.

Bank fee and interest journal entries created during statement import are included in the next integrity scan — they are verified like any other journal entry because they were posted through JournalEngine with full enforcement.