Bank Import Pipeline Review

Plane task: MHPAY-1

This document records the current state of the bank statement import pipeline in MyHoreca Pay. It is an audit/review document, not a new implementation.

Purpose

The bank import pipeline is responsible for converting uploaded Indonesian bank statement files into normalized bank transactions that can later be matched to counterparties, allocated to Syrve/iiko documents, reviewed by operators, and used as payment evidence.

The expected business flow is:

Upload bank statement
  -> detect bank/format
  -> choose adapter
  -> parse file
  -> normalize transactions
  -> persist import + transactions + quality data
  -> expose result to operator UI
  -> continue to counterparty matching and allocation

Current high-level status

The repository contains a production-oriented MVP pipeline with multiple sanitized-tested adapters and extension points for additional Indonesian bank formats.

Current state:

• IBankAdapter contract exists.
• Format detection abstraction exists.
• Parser abstraction exists.
• Import service abstraction exists.
• Supported banks / descriptors abstraction exists.
• Quality report and duplicate candidate abstractions exist.
• _TemplateBankAdapter.example.cs is the starting point for future adapters.
• Permata CSV/TXT/MT940-like, CIMB corporate explicit debit/credit CSV, BCA multi-account CR/DB, and Mandiri adapters exist.
• Additional real bank adapters beyond current coverage remain the main Phase 1 follow-up.

Main backend areas

Area	Location	Role
Domain contracts	backend/src/MyHoreca.Pay.Domain/BankStatements/	Adapter/parser/import abstractions and normalized models
Bank parsing implementation	backend/src/MyHoreca.Pay.BankStatements/	Adapter implementations, detector, parser/import services
API layer	backend/src/MyHoreca.Pay.Api/Controllers/	Upload/import endpoints and operator-facing API
Persistence	backend/src/MyHoreca.Pay.Persistence/	EF Core persistence for imports, transactions, quality data
Tests	backend/tests/MyHoreca.Pay.UnitTests/	Adapter, detector, and pipeline unit tests
Docs	docs/docs/architecture/, docs/docs/backend/	Architecture and API documentation

Important domain contracts

The central contract is IBankAdapter.

An adapter is responsible for:

• describing supported bank and file format through BankAdapterDescriptor;
• scoring whether a file belongs to this adapter using ScoreDetection;
• confirming parse capability through CanParse;
• parsing the file into a BankStatementAdapterParseResult;
• returning normalized transactions, warnings, errors, and parse confidence.

Related contracts:

• IBankStatementFormatDetector — detects the best adapter for an uploaded file.
• IBankStatementParser — executes parsing with a selected adapter.
• IBankStatementSupportedBanksService — returns supported banks/formats for API/UI.
• IBankStatementQualityService — provides quality reports and duplicate candidate workflow.
• IBankStatementImportService — orchestrates the import operation.

Adapter reference template

Use _TemplateBankAdapter.example.cs when adding a new bank format. Production adapters must handle quoted commas, separators, localized dates/amounts, and bank-specific headers — see the development checklist.

Current import pipeline

The intended pipeline is:

1. API receives uploaded file with file name, content type, stream, user ID, organization ID, and location ID.
2. Import service creates a bank statement import record.
3. Format detector reads file preview/header bytes and evaluates registered adapters.
4. The best adapter is selected using detection confidence.
5. Parser invokes the selected adapter.
6. Adapter returns normalized transactions and quality information.
7. Import service persists import metadata, transactions, adapter detection, and quality counters.
8. API/UI expose import result to the operator.
9. Downstream modules use transactions for counterparty matching and payment allocation.

Normalized transaction expectations

Every real bank adapter should map source-specific fields into a shared transaction model.

Minimum expected data:

• source bank code;
• source format code;
• transaction date;
• value date when available;
• direction: debit or credit;
• amount;
• currency;
• raw description;
• counterparty raw name;
• bank reference when available;
• payment purpose when available;
• raw source fields for traceability;
• import ID;
• stable internal ID generated by backend UUID v7 generator.

Adapters should not generate persistent IDs in frontend code. Backend persistent IDs must follow the repository UUID v7 rule.

Quality report expectations

The current domain contract already supports quality reporting.

Quality report should help the operator answer:

• Was the file format confidently detected?
• How many rows were read?
• How many transactions were parsed?
• How many rows were skipped?
• Are there warnings or errors?
• Was the counterparty extracted?
• Are there duplicate candidates?
• Do balances match when opening/closing balances are available?

For real bank adapters, warnings and errors must be explainable in operator-friendly language.

Duplicate detection expectations

Two layers apply:

1. File-level duplicate upload (MHPAY-62) — before import creation, SHA256 of the original file bytes is compared with OrganizationId. A match returns the existing import and skips parser/transaction creation. LocationId is not part of this MVP key.
2. Transaction duplicate candidates — after parsing, similar transactions across imports may be flagged for operator confirm/dismiss.

Duplicate detection is important because bank files may be uploaded repeatedly or overlap by date/account.

Potential duplicate matching signals:

• bank reference;
• transaction date;
• amount;
• direction;
• currency;
• account number if available;
• normalized counterparty name;
• normalized description/payment purpose.

The current contracts support duplicate candidates and manual confirm/dismiss workflow.

Current gaps

Real Indonesian bank adapters

The main Phase 1 gap is support for real bank formats.

Required adapters should be implemented incrementally from real samples:

• Permata-style exports if available;
• corporate debit/credit CSV;
• multi-account credit/debit CSV;
• MT940-like text formats;
• other Indonesian bank statement layouts from pilot customers.

Robust CSV parsing

Simple comma splitting is not enough for production bank CSV files, which may contain:

• quoted commas;
• semicolon separators;
• tab separators;
• localized date formats;
• localized amount formats;
• headers in Indonesian/English;
• multi-line descriptions;
• different encodings.

Real adapters should use a robust parsing approach per format.

Encoding and locale handling

Real bank files may use different encodings and localized number/date formats. Each adapter should explicitly define:

• expected encoding or detection strategy;
• date formats;
• decimal separator;
• thousands separator;
• currency handling;
• timezone assumptions when timestamps exist.

Parser confidence rules

Detection confidence and parse confidence should be consistent across adapters.

For each adapter, define:

• high-confidence detection signals;
• weak fallback signals;
• mandatory columns;
• optional columns;
• row-level warning rules;
• import-level failure rules.

Operator documentation

Operators need clear explanations for failed or partial imports.

Missing or incomplete documentation should be addressed in bank-statement-parsing.md.

Suggested next tasks

This review directly supports the first Phase 1 tasks:

• MHPAY-2 — document the normalized bank transaction model.
• MHPAY-3 — create bank-statement-parsing.md.
• MHPAY-4 — implement the first real Indonesian bank CSV adapter.
• MHPAY-5 — expose supported banks and formats to API/UI.

Recommended implementation order

1. Document normalized transaction model.
2. Document adapter rules and parsing pipeline.
3. Choose the first real bank sample.
4. Add sanitized fixture for this sample.
5. Implement adapter detection.
6. Implement parser.
7. Add success and failure unit tests.
8. Add quality warnings/errors.
9. Register adapter in DI.
10. Update supported banks documentation/UI.

Development rules to keep

When continuing this area, keep repository rules:

• add new bank formats through new adapters;
• do not break the shared pipeline;
• keep parsing errors explainable to operators;
• keep documentation up to date;
• use UUID v7 for new persistent entity IDs;
• do not generate backend persistent IDs in React;
• do not hardcode secrets;
• keep API changes documented.