# Bank / Credit Card Statement Import **Date:** 2026-06-01 **Status:** Draft **Spec:** 167 **Tier:** All tiers **Depends on:** `expenses-module`, `invoices-core`, `foundation-auth-rbac`, `settings-module` **Referenced by:** `expenses-module`, `financial-statements` --- ## Overview Israeli freelancers and businesses reconcile their finances primarily through bank and credit card statements (CSV or OFX exports). This spec defines the import flow: upload a bank/CC statement, auto-match transactions to existing invoices and expenses, flag unmatched transactions for manual review, and create new expense records from unmatched debits. Supported Israeli banks and credit cards: Bank Hapoalim (הפועלים), Bank Leumi (לאומי), Mizrahi Tefahot (מזרחי), Discount (דיסקונט), Bank of Israel / Postal Bank, Cal / Max / Isracard / Visa CAL (credit cards). Import format: CSV (bank-specific column layouts) and OFX/QFX (standard). --- ## Data Model ```sql CREATE TABLE bank_imports ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, imported_by UUID NOT NULL REFERENCES users(id) ON DELETE SET NULL, file_name TEXT NOT NULL, bank_name TEXT NOT NULL, -- 'hapoalim' | 'leumi' | 'mizrahi' | 'discount' | 'cal' | 'max' | 'isracard' | 'generic_csv' | 'ofx' account_number TEXT, -- masked: last 4 digits period_start DATE, period_end DATE, row_count INTEGER NOT NULL DEFAULT 0, matched_count INTEGER NOT NULL DEFAULT 0, unmatched_count INTEGER NOT NULL DEFAULT 0, status TEXT NOT NULL DEFAULT 'pending', -- 'pending' | 'processing' | 'review' | 'complete' r2_key TEXT NOT NULL, -- uploaded file stored in R2 created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE TABLE bank_transactions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), import_id UUID NOT NULL REFERENCES bank_imports(id) ON DELETE CASCADE, tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, txn_date DATE NOT NULL, description TEXT NOT NULL, -- raw bank description amount NUMERIC(12,2) NOT NULL, -- positive = credit (income); negative = debit (expense) currency TEXT NOT NULL DEFAULT 'ILS', balance_after NUMERIC(12,2), -- account balance after transaction (if provided) reference TEXT, -- bank reference number match_status TEXT NOT NULL DEFAULT 'unmatched', -- 'unmatched' | 'matched_invoice' | 'matched_expense' | 'ignored' | 'expense_created' | 'sent_to_reconcile' matched_invoice_id UUID REFERENCES invoices(id) ON DELETE SET NULL, matched_expense_id UUID REFERENCES expenses(id) ON DELETE SET NULL, match_confidence NUMERIC(3,2), -- 0.0–1.0 confidence of auto-match reviewed_by UUID REFERENCES users(id), reviewed_at TIMESTAMPTZ, created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_bank_txns_import ON bank_transactions(import_id); CREATE INDEX idx_bank_txns_tenant ON bank_transactions(tenant_id, txn_date DESC); ``` --- ## Import Flow ### Step 1: Upload `/expenses/bank-import` (or linked from `/expenses` header: "Import statement"): ``` ┌──────────────────────────────────────────────────────────────┐ │ Import bank / credit card statement │ │ │ │ Bank / card: [Bank Hapoalim ▾] │ │ Bank Hapoalim · Bank Leumi · Mizrahi │ │ Discount · Bank of Israel │ │ Cal · Max · Isracard · OFX (any bank) │ │ Generic CSV │ │ │ │ Account #: [____] (optional, for display only) │ │ │ │ File: [Choose file] or drag & drop │ │ Accepted: .csv, .ofx, .qfx │ │ Max size: 5 MB │ │ │ │ [Cancel] [Upload & process] │ └──────────────────────────────────────────────────────────────┘ ``` File uploaded to R2 (`tenants/{tenantId}/bank-imports/{importId}.csv`), job enqueued. ### Step 2: Parse + Auto-match (Queue consumer) ```ts // packages/bank-import/src/process.ts async function processBankImport(importId: string, env: Env) { const imp = await getImport(importId) const file = await env.R2.get(imp.r2_key) const rows = parseBankFile(file, imp.bank_name) // bank-specific parser (see Parsers section) for (const row of rows) { const txn = await insertTransaction(importId, row) // Auto-match credits (income) to invoices if (row.amount > 0) { const match = await matchInvoice(txn, imp.tenant_id) if (match.confidence >= 0.85) { await markMatched(txn.id, 'matched_invoice', match.invoice_id, match.confidence) } } // Auto-match debits (expenses) to existing expenses if (row.amount < 0) { const match = await matchExpense(txn, imp.tenant_id) if (match.confidence >= 0.80) { await markMatched(txn.id, 'matched_expense', null, match.confidence) } } } await updateImportStatus(importId, 'review') } ``` **Invoice matching logic:** 1. Look for invoices with `amount_paid ≈ |txn.amount|` (within ₪1 tolerance for rounding) 2. AND `paid_at` within ±5 days of `txn_date` 3. AND `status IN ('TAX_ISSUED', 'PARTIALLY_PAID')` 4. Confidence boosted if customer name appears in `txn.description` **Expense matching logic:** 1. Look for existing expenses with `amount ≈ |txn.amount|` (within ₪1) 2. AND `expense_date` within ±3 days of `txn_date` 3. AND `status IN ('PENDING', 'COMPLETED')` ### Step 3: Review UI `/expenses/bank-import/:importId/review`: ``` ┌──────────────────────────────────────────────────────────────────────┐ │ Review import — Hapoalim — Jun 2026 [Complete] │ │ │ │ 38 transactions · 22 matched · 16 to review │ │ │ │ Filter: [All ▾] [Unmatched ▾] │ │ │ │ Date Description Amount Status │ │ ───────────────────────────────────────────────────────────────── │ │ Jun 01 ACME CORP LTD +₪12,700 ✓ Matched INV-0042 │ │ Jun 01 BEZEQ INTERNATIONAL -₪450 ✓ Matched (Expense) │ │ May 30 OFFICE DEPOT TLV -₪890 ⚠ Unmatched │ │ [Create expense] [Match to existing] [Ignore] │ │ May 29 UNKNOWN PAYER +₪3,400 ⚠ Unmatched (credit)│ │ [Send to reconcile] [Match to invoice] [Ignore] │ │ │ │ ───────────────────────────────────────────────────────────────── │ │ Progress: 22 / 38 resolved │ └──────────────────────────────────────────────────────────────────────┘ ``` **Actions on unmatched transactions:** - **Create expense** (debits only): opens pre-populated expense modal; creates expense with amount, date, description from transaction; links `expense.bank_transaction_id = txn.id` - **Match to existing**: search/select existing invoice or expense to link - **Send to reconcile** (credits only): an unmatched **incoming credit** (customer paid, but the payer/reference doesn't resolve to an invoice) is pushed into the reconciliation queue — it inserts an `unmatched_payments` row (spec 157) from the transaction (`amount`, `paid_at = txn_date`, `payer_name`, `reference`, `payment_method='bank_transfer'`) and sets `bank_transactions.match_status = 'sent_to_reconcile'` with `unmatched_payment_id` FK. This is what makes a bank-imported unmatched credit appear in `/invoices/reconcile`; without it, incoming credits would dead-end in the import screen. - **Ignore**: mark as `ignored` (personal transaction, owner salary, inter-account transfer, etc.) — removes from review queue ```sql -- extend match_status enum value set + link to the reconcile row -- match_status now includes 'sent_to_reconcile' ALTER TABLE bank_transactions ADD COLUMN unmatched_payment_id UUID REFERENCES unmatched_payments(id) ON DELETE SET NULL; ``` ```sql ALTER TABLE expenses ADD COLUMN bank_transaction_id UUID REFERENCES bank_transactions(id) ON DELETE SET NULL; ``` ### Step 4: Complete When all transactions are resolved (matched, expense_created, or ignored): - `bank_imports.status = 'complete'` - Import appears in history; can be downloaded again ### Ownership: invoice payment writes When a bank transaction is matched to an invoice, **bank-statement-import owns the write**: - bank-statement-import writes the `invoice_payments` row (`source = 'bank_import'`) and updates `invoices.status`. - The `payment-reconciliation` spec resolves `unmatched_payments` rows (manual bank receipts **and** credits pushed from bank import via "Send to reconcile"). - **No double-write:** when matching a bank txn directly to an invoice, bank import sets `bank_transactions.match_status = 'matched_invoice'` first, inside a DB transaction with a row lock, and writes the single `invoice_payments` row. It never also creates an `unmatched_payments` row for the same txn — a txn is *either* matched here *or* sent to reconcile, never both (`match_status` is the discriminator). Reconciliation only ever writes `invoice_payments` when a human matches an `unmatched_payments` row to an invoice; it does not touch `bank_transactions`. --- ## Bank-Specific CSV Parsers ```ts // packages/bank-import/src/parsers/index.ts const PARSERS: Record = { hapoalim: parseHapoalim, // columns: date, description, debit, credit, balance leumi: parseLeumi, mizrahi: parseMizrahi, discount: parseDiscount, cal: parseCal, max: parseMax, isracard: parseIsracard, generic_csv: parseGenericCsv, // auto-detect columns by header ofx: parseOFX, // standard OFX/QFX XML format } interface BankRow { date: Date description: string amount: number // positive = credit, negative = debit balance?: number reference?: string currency: string } ``` **Hebrew column handling:** Israeli bank CSV exports use Hebrew column headers and `DD/MM/YYYY` date format. Parsers handle Hebrew text natively (no transliteration). **Encoding:** Bank CSVs are commonly exported in CP1255 (Windows Hebrew) or UTF-8 with BOM. Parsers auto-detect encoding. --- ## API ``` GET /api/bank-imports → list import history (paginated) POST /api/bank-imports → upload + create import job GET /api/bank-imports/:id → import status + stats GET /api/bank-imports/:id/transactions → list transactions (filterable: match_status) PATCH /api/bank-imports/:id/transactions/:tid → update match status (manual match/ignore/expense_created) POST /api/bank-imports/:id/transactions/:tid/create-expense → create expense from debit transaction POST /api/bank-imports/:id/complete → mark import complete (OWNER/ADMIN) ``` All routes require `expenses:write` (upload/review) or `expenses:read` (list/view). --- ## Architecture Decisions | Decision | Choice | Reason | |----------|--------|--------| | File stored in R2, parsed async | Not in-request parsing | Bank CSVs can be large (12 months = 300+ rows); Workers CPU limits make sync parsing risky; queue consumer has no time limit | | Confidence threshold for auto-match | 0.85 invoice / 0.80 expense | High confidence required to auto-accept; false positives are worse than manual review; user confirms edge cases | | OFX support | Not Excel/XLSX | OFX is standard machine-readable format; Israeli bank websites export OFX natively; Excel would require XLSX parsing library | | Hebrew CSV handling | Native in parsers | IL banks export Hebrew headers; abstracted in each bank parser to produce a normalized `BankRow`; core logic stays encoding-agnostic | | Per-bank parsers | Not universal CSV | Bank column layouts vary significantly (different date formats, debit/credit column positions); per-bank parsers are more reliable than auto-detection | | Bank import owns the direct `invoice_payments` write | Not payment-reconciliation | Avoids dual ownership; import flow has the full transaction context for txns it matches itself | | Unmatched **credits** flow into reconcile via `unmatched_payments` | Not dead-ended in the import screen | A bank-imported credit with no resolvable invoice must reach `/invoices/reconcile`; "Send to reconcile" inserts an `unmatched_payments` row (status `sent_to_reconcile`), so the two specs share one staging table instead of two disconnected ones |