Bank Ingestion Data Model

1. Executive Summary

Purpose

The bank ingestion domain captures, normalizes, deduplicates, and stores bank statement data flowing into the Client Processing system. Bank files arrive as ISO 20022 CAMT.053 (end-of-day statements) and CAMT.052 (intraday reports) from multiple banking partners. The ingestion pipeline parses these files, applies gatekeeper filtering rules, and persists the results into a set of tables that form a complete audit trail from raw file through individual transactions. The centerpiece is the bank_transaction table — the golden record — where each row represents a single, deduplicated, normalized bank transaction that downstream processes (cash receipt creation, reconciliation) query against.

Scope

Covered:

bank_file — Tracks each uploaded bank statement file
bank_file_account — Junction table linking files to the bank accounts they contain
bank_file_header — Preserves raw ISO 20022 group header and statement header fields for audit
bank_file_balance — Captures balance snapshots (opening, closing, interim) from each file
bank_file_summary — Aggregated credit/debit entry totals from the file's transaction summary block
bank_file_group — Links intraday (CAMT.052) files to end-of-day (CAMT.053) files by account and date
bank_transaction — The golden record: deduplicated, normalized individual bank transactions
bank_transaction_history — Append-only audit trail of every state change to a transaction
bank_code_mapping — Bank-specific proprietary code mappings (e.g., BAI codes) linked to a bank and optionally mapped to ISO 20022 universal codes

Not covered (documented separately):

Cash receipts, splits, worksheets, and applications — see Cash Receipts Data Model and Worksheets Data Model
bank and bank_account reference tables — documented inline below as FK targets; they are shared reference entities without their own foundation document

2. Data Model

2.1 Entity-Relationship Diagram

mermaid

erDiagram
    bank ||--o{ bank_file : "source_bank_id"
    bank ||--o{ bank_account : "bank_id"
    bank_file ||--o{ bank_file_account : "bank_file_id"
    bank_file ||--o{ bank_file_header : "bank_file_id"
    bank_file ||--o{ bank_file_balance : "bank_file_id"
    bank_file ||--o{ bank_file_summary : "bank_file_id"
    bank_file ||--o{ bank_transaction : "bank_file_id (latest source)"
    bank_file ||--o{ bank_transaction_history : "bank_file_id"
    bank_file ||--o{ bank_file_group : "parent_file_id (CAMT.053)"
    bank_file ||--o{ bank_file_group : "child_file_id (CAMT.052)"
    bank_account ||--o{ bank_file_account : "bank_account_id"
    bank_account ||--o{ bank_file_balance : "bank_account_id"
    bank_account ||--o{ bank_file_group : "bank_account_id"
    bank_account ||--o{ bank_transaction : "bank_account_id"
    bank_transaction ||--o{ bank_transaction_history : "bank_transaction_id"
    bank_transaction ||--o| bank_transaction : "reversal_of_transaction_id"
    bank_file ||--o| bank_file : "duplicate_of_file_id"
    bank_code_mapping }o--|| bank : "bank_id"

2.2 `bank_file`

Tracks each uploaded bank statement file; one row per file upload regardless of how many accounts or transactions the file contains.

Field	Type	Required	Default	Description
`bank_file_id`	`serial`	Yes	Auto	Primary key. Referenced by all child file tables and `bank_transaction`.
`source_bank_id`	`varchar(20)`	No	—	FK to `bank.bank_id`. Identifies which bank produced the file (e.g., `BOFA`, `JPM`, `CNB`).
`file_name`	`text`	Yes	—	Original file name as uploaded.
`file_type`	`varchar(20)`	Yes	—	`CAMT053` (end-of-day) or `CAMT052` (intraday). See Section 5.
`xsd_name`	`varchar(50)`	No	—	ISO 20022 schema version identifier (e.g., `camt.052.001.02`).
`message_id`	`varchar(100)`	No	—	Extracted from `<MsgId>` in the XML group header. Used in file-level deduplication.
`content_hash`	`varchar(64)`	No	—	SHA-256 hash of raw file content. Secondary deduplication mechanism.
`electronic_seq_nb`	`integer`	No	—	Extracted from `<ElctrncSeqNb>`. Sequential file number assigned by the bank.
`is_duplicate`	`boolean`	Yes	`false`	Set to `true` when the file is detected as a duplicate of a previously uploaded file.
`duplicate_of_file_id`	`integer`	No	—	Self-referencing FK to `bank_file.bank_file_id`. Points to the original file when `is_duplicate = true`.
`statement_currency`	`varchar(10)`	No	—	Currency code extracted from the statement-level `<Ccy>` attribute.
`upload_date`	`timestamp`	Yes	`now()`	When the file was uploaded to the system.
`status`	`varchar(50)`	Yes	—	Processing status. See Section 3: Status Lifecycle.
`raw_content`	`text`	No	—	Original XML content preserved for debugging and reprocessing. All entries including filtered ones are stored here.
`transaction_count`	`integer`	No	—	Number of transactions parsed from the file after gatekeeper filtering.

IMPORTANT

A single bank file can contain data for multiple bank accounts. The bank_file_account junction table captures this many-to-many relationship. The source_bank_id on bank_file identifies the issuing bank, not any specific account.

FK reference — bank: The bank table is a first-class reference entity with fields bank_id (PK, e.g., BOFA), bank_name, bank_full_name, file_format (ISO20022_XML or CNB_JSON), adapter_class, bic, country_code, and active_ind.

2.3 `bank_file_account`

Junction table linking a bank file to the bank accounts it contains. A single CAMT file can include multiple <Rpt> or <Stmt> blocks for different accounts.

Field	Type	Required	Default	Description
`bank_file_account_id`	`serial`	Yes	Auto	Primary key.
`bank_file_id`	`integer`	Yes	—	FK to `bank_file.bank_file_id`.
`bank_account_id`	`integer`	Yes	—	FK to `bank_account.bank_account_id`.
`statement_id`	`varchar(100)`	No	—	Statement identifier extracted from `<StmtId>`.
`statement_from_date`	`timestamp`	No	—	Start of the statement period for this account within the file.
`statement_to_date`	`timestamp`	No	—	End of the statement period for this account within the file.
`account_currency`	`varchar(10)`	No	—	Currency of this specific account within the file.
`transaction_count`	`integer`	No	—	Number of transactions for this account within the file.

FK reference — bank_account: The bank_account table stores account details including bank_account_name, bank_account_no, bank_account_routing_no, currency_cd, country_cd, is_uta_account, uta_account_id, and a FK to bank.bank_id.

NOTE

Pure audit columns (created_by, created_dt, updated_by, updated_dt) and auto-generated primary keys are omitted unless they carry business meaning.

2.4 `bank_file_header`

Preserves all group header (<GrpHdr>) and statement/report header fields from the ISO 20022 XML. One row per file, capturing raw metadata for audit and traceability.

Field	Type	Required	Default	Description
`bank_file_header_id`	`serial`	Yes	Auto	Primary key.
`bank_file_id`	`integer`	Yes	—	FK to `bank_file.bank_file_id`.
`message_id`	`varchar(100)`	No	—	From `<GrpHdr><MsgId>`. Same value as `bank_file.message_id`.
`creation_date_time`	`timestamp`	No	—	From `<GrpHdr><CreDtTm>`. When the bank generated the file. Used as fallback booking date for transactions.
`statement_id`	`varchar(100)`	No	—	From `<Stmt><StmtId>` or `<Rpt><RptId>`.
`electronic_sequence_number`	`integer`	No	—	From `<ElctrncSeqNb>`.
`legal_sequence_number`	`integer`	No	—	From `<LglSeqNb>`.
`statement_from_date`	`timestamp`	No	—	Start of the statement/report period.
`statement_to_date`	`timestamp`	No	—	End of the statement/report period.
`account_iban`	`varchar(34)`	No	—	From `<Acct><Id><IBAN>`.
`account_other_id`	`varchar(100)`	No	—	From `<Acct><Id><Othr><Id>`. Used when IBAN is not present.
`account_currency`	`varchar(10)`	No	—	From `<Acct><Ccy>`.
`account_owner_name`	`varchar(255)`	No	—	From `<Acct><Ownr><Nm>`.
`servicer_bic`	`varchar(11)`	No	—	From `<Svr><FinInstnId><BIC>`. The bank's SWIFT/BIC code.
`servicer_name`	`varchar(255)`	No	—	From `<Svr><FinInstnId><Nm>`. The bank's name as stated in the file.
`additional_info`	`text`	No	—	From `<AddtlInf>`. Freeform text from the bank.

2.5 `bank_file_balance`

Captures all <Bal> elements from the file. A single file typically contains multiple balance types (opening booked, closing booked, interim, etc.), and multi-account files produce balances per account.

Field	Type	Required	Default	Description
`bank_file_balance_id`	`serial`	Yes	Auto	Primary key.
`bank_file_id`	`integer`	Yes	—	FK to `bank_file.bank_file_id`.
`bank_account_id`	`integer`	No	—	FK to `bank_account.bank_account_id`. Identifies which account this balance belongs to in multi-account files.
`balance_type`	`varchar(10)`	Yes	—	ISO 20022 balance type code (`OPBD`, `CLBD`, `ITBD`, etc.). See Section 5.
`amount`	`decimal(15,2)`	Yes	—	Balance amount.
`currency`	`varchar(10)`	Yes	—	Currency code of the balance.
`credit_debit_indicator`	`varchar(10)`	No	—	`CRDT` or `DBIT`. Indicates whether the balance represents a credit or debit position.
`balance_date`	`timestamp`	No	—	Date of the balance (date only).
`balance_date_time`	`varchar(50)`	No	—	Full ISO datetime string when finer granularity is available.
`account_iban`	`varchar(34)`	No	—	IBAN from the file, used for account matching during parsing.
`account_other_id`	`varchar(100)`	No	—	Non-IBAN account identifier from the file.
`availability_days`	`integer`	No	—	From `<Avlbty><Dt><NbOfDays>`. Number of days until funds become available.

NOTE

Balance records serve two purposes: daily cash position reporting for treasury, and reconciliation anchoring. The closing booked balance (CLBD) of one statement should match the opening booked balance (OPBD) of the next.

2.6 `bank_file_summary`

Captures the <TxsSummry> element from the file, providing aggregated credit and debit totals without requiring summation over individual transactions.

Field	Type	Required	Default	Description
`bank_file_summary_id`	`serial`	Yes	Auto	Primary key.
`bank_file_id`	`integer`	Yes	—	FK to `bank_file.bank_file_id`.
`total_credit_entries`	`integer`	No	—	Count of credit entries reported in the file summary block.
`total_credit_amount`	`decimal(15,2)`	No	—	Sum of all credit entry amounts as reported by the bank.
`total_debit_entries`	`integer`	No	—	Count of debit entries reported in the file summary block.
`total_debit_amount`	`decimal(15,2)`	No	—	Sum of all debit entry amounts as reported by the bank.
`net_entry_amount`	`decimal(15,2)`	No	—	Net of credits minus debits as reported in the file.

2.7 `bank_file_group`

Links CAMT.052 (intraday) files to CAMT.053 (end-of-day) files at the account level. Grouping may be performed automatically by the system or manually by a user.

Field	Type	Required	Default	Description
`bank_file_group_id`	`serial`	Yes	Auto	Primary key.
`parent_file_id`	`integer`	Yes	—	FK to `bank_file.bank_file_id`. The CAMT.053 end-of-day file (parent).
`child_file_id`	`integer`	Yes	—	FK to `bank_file.bank_file_id`. The CAMT.052 intraday file (child).
`bank_account_id`	`integer`	No	—	FK to `bank_account.bank_account_id`. Scopes the grouping to a specific account.
`statement_date`	`timestamp`	No	—	The statement date used for matching, derived from file content.
`matching_criteria`	`varchar(50)`	No	—	How the group was formed: `AUTO` or `MANUAL`. See Section 5.

NOTE

Auto-grouping is triggered when a CAMT.053 file is uploaded. The system searches for existing CAMT.052 files with the same source_bank_id, bank_account_id, and statement_date, then creates group records automatically. Manual grouping allows users to associate files that auto-grouping could not match.

2.8 `bank_transaction`

The golden record — the deduplicated, normalized representation of individual bank transactions. This is the primary table that downstream processes (cash receipt creation, reconciliation) query against.

IMPORTANT

The golden record uses (bank_account_id, bank_reference_id) as a composite unique key. When an intraday file creates a PDNG record and the end-of-day file arrives with the same transaction, the existing record is updated in place: status is promoted to BOOK and bank_file_id is updated to point to the latest source file. This upsert pattern is fundamental to preventing duplicate transactions.

Core Fields

Field	Type	Required	Default	Description
`bank_transaction_id`	`serial`	Yes	Auto	Primary key.
`bank_file_id`	`integer`	Yes	—	FK to `bank_file.bank_file_id`. Points to the latest source file that created or last updated this record. Updated on upsert.
`bank_account_id`	`integer`	No	—	FK to `bank_account.bank_account_id`. The UTA bank account this transaction belongs to. Part of the golden-record unique key.
`bank_reference_id`	`varchar(100)`	Yes	—	From `<AcctSvcrRef>`. The bank's unique reference for this transaction. Part of the golden-record unique key.
`booking_date`	`timestamp`	No	—	From `<Ntry><BookgDt><Dt>`, with fallback to `<GrpHdr><CreDtTm>`. The date the bank booked the transaction.
`value_date`	`timestamp`	No	—	Settlement/value date if different from booking date.
`amount`	`decimal(15,2)`	Yes	—	Transaction amount from the parent `<Ntry><Amt>`. Always taken from the entry level, not from individual `<TxDtls>`.
`currency`	`varchar(10)`	Yes	—	Currency code of the transaction amount.
`status`	`varchar(20)`	Yes	—	ISO 20022 entry status. See Section 3: Status Lifecycle.
`cash_receipt_status`	`varchar(20)`	Yes	`'UNMATCHED'`	Reconciliation status tracking whether a cash receipt has been created from this transaction. See Section 3.
`direction`	`varchar(10)`	No	—	`CREDIT` or `DEBIT`. Derived from `<CdtDbtInd>`.

Reversal Fields

Field	Type	Required	Default	Description
`is_reversal`	`integer`	Yes	`0`	`1` if this transaction is a reversal of a prior transaction; `0` otherwise.
`reversal_of_transaction_id`	`integer`	No	—	Self-referencing FK to `bank_transaction.bank_transaction_id`. Links to the original transaction being reversed.
`reversal_status`	`varchar(20)`	No	—	`PENDING`, `ACCEPTED`, or `REJECTED`. `null` for non-reversal transactions. See Section 3.
`reversal_accepted_at`	`timestamp`	No	—	When the reversal was manually accepted by a user.
`reversal_accepted_by`	`varchar(100)`	No	—	Identifier of the user who accepted the reversal.

WARNING

Reversal transactions (is_reversal = 1) always start with reversal_status = 'PENDING' and require manual human review before acceptance. Accepting a reversal triggers downstream effects including unapplication from receivables. This is not automated because reversals represent real financial risk (ACH bounces, payment clawbacks).

Classification Fields

Field	Type	Required	Default	Description
`transaction_type`	`varchar(50)`	No	—	Human-readable classification: `ACH`, `WIRE`, `FX`, etc.
`bank_transaction_code`	`varchar(50)`	No	—	From `<BkTxCd><Prtry><Cd>`. Bank-specific proprietary code (e.g., BAI codes `165`, `195`, `208`).
`source_bank`	`varchar(20)`	No	—	Detected bank identifier: `BOFA`, `JPM`, `CNB`.
`domain_code`	`varchar(10)`	No	—	From `<BkTxCd><Domn><Cd>`. ISO 20022 domain (e.g., `PMNT` for Payments).
`family_code`	`varchar(10)`	No	—	From `<BkTxCd><Domn><Fmly><Cd>`. Transaction family (e.g., `RCDT` for Received Credit Transfer).
`sub_family_code`	`varchar(10)`	No	—	From `<BkTxCd><Domn><Fmly><SubFmlyCd>`. Sub-family detail (e.g., `ACDT` for ACH Credit).
`transaction_code_issuer`	`varchar(20)`	No	—	From `<BkTxCd><Prtry><Issr>`. Code system issuer (e.g., `BAI`).

Remittance and Counterparty Fields

Field	Type	Required	Default	Description
`payer_name`	`varchar(255)`	No	—	Extracted from `<Ustrd>` remittance text. The name of the entity that sent the payment.
`payer_id`	`varchar(100)`	No	—	Extracted vendor/payer ID (e.g., from `ID:00046064` patterns in remittance text).
`invoice_number`	`varchar(100)`	No	—	Extracted invoice reference (e.g., `INV 2024-001` patterns in remittance text).
`end_to_end_id`	`varchar(100)`	No	—	From `<EndToEndId>`. Only stored if non-placeholder; values like `0000000000` are ignored.
`remittance_info`	`text`	No	—	Raw `<Ustrd>` text preserved as fallback when structured extraction does not yield results.
`debtor_name`	`varchar(255)`	No	—	From `<RltdPties><Dbtr><Nm>`. The payer's formal name from structured party data.
`debtor_account`	`varchar(100)`	No	—	From `<RltdPties><Dbtr><Acct>`. The payer's account number.
`debtor_org_id`	`varchar(100)`	No	—	From `<Dbtr><Id><OrgId><Othr><Id>`. Organization identifier for the payer.
`creditor_name`	`varchar(255)`	No	—	From `<RltdPties><Cdtr><Nm>`. The payee's name (typically UTA).
`creditor_account`	`varchar(100)`	No	—	From `<RltdPties><Cdtr><Acct>`. The payee's account number.
`debtor_agent_name`	`varchar(255)`	No	—	From `<RltdAgts><DbtrAgt><FinInstnId><Nm>`. The payer's bank name.
`creditor_agent_name`	`varchar(255)`	No	—	From `<RltdAgts><CdtrAgt><FinInstnId><Nm>`. UTA's bank name.

Entry Reference and FX Fields

Field	Type	Required	Default	Description
`tx_id`	`varchar(100)`	No	—	From `<TxId>`. The bank's internal transaction identifier.
`entry_class_type`	`varchar(100)`	No	—	From `<Prtry><Tp>` (e.g., `ENTRY CLASS`).
`entry_class_ref`	`varchar(50)`	No	—	From `<Prtry><Ref>` (e.g., `CCD`, `CTX`). Indicates ACH format type.
`purpose`	`varchar(100)`	No	—	From `<Purp><Prtry>` (e.g., `PAYMENTS`).
`original_amount`	`decimal(15,2)`	No	—	For FX transactions: amount in the original currency before conversion.
`original_currency`	`varchar(10)`	No	—	Original currency code (e.g., `GBP`) before conversion to account currency.
`exchange_rate`	`decimal(15,6)`	No	—	FX rate applied by the bank. Precision of 6 decimal places for rate accuracy.
`availability_days`	`integer`	No	—	From `<Avlbty><Dt><NbOfDays>`. `0` = same-day availability.
`booking_date_time`	`varchar(50)`	No	—	Full ISO datetime with time component (e.g., `2026-01-23T19:00:24.0Z`).
`raw_entry_ref`	`varchar(100)`	No	—	From `<NtryRef>`. Less reliable than `<AcctSvcrRef>` but preserved for reference.

2.9 `bank_transaction_history`

Append-only audit trail that records every state change to a bank_transaction record. Every file upload that creates or updates a transaction produces one history record.

Field	Type	Required	Default	Description
`bank_transaction_history_id`	`serial`	Yes	Auto	Primary key.
`bank_transaction_id`	`integer`	Yes	—	FK to `bank_transaction.bank_transaction_id`.
`bank_file_id`	`integer`	Yes	—	FK to `bank_file.bank_file_id`. The source file that caused this state change.
`from_status`	`varchar(20)`	No	—	Previous status. `null` for `CREATE` actions (new transaction).
`to_status`	`varchar(20)`	Yes	—	New status after the change.
`booking_date`	`timestamp`	No	—	Booking date as recorded at the time of this state change.
`action_type`	`varchar(20)`	Yes	—	`CREATE` (new record) or `UPDATE` (existing record modified).

NOTE

Records in this table are never updated or deleted. Combined with the bank_file_id FK, it provides a complete provenance chain: for any transaction, you can trace every file that touched it and exactly how its status changed over time.

2.10 `bank_code_mapping`

Stores bank-specific proprietary codes that appear in <Prtry> elements of ISO 20022 XML, such as BAI codes. Each record links a bank-specific code to an optional universal ISO 20022 code and carries extended attributes for treasury and cash application guidance.

Field	Type	Required	Default	Description
`bank_code_mapping_id`	`serial`	Yes	Auto	Primary key.
`bank_id`	`varchar(20)`	Yes	—	FK to `bank.bank_id`. The bank that issues this proprietary code.
`bank_code_type`	`varchar(50)`	Yes	—	Category of the code, e.g., `BAI_CODE` or `PROPRIETARY_STATUS`.
`bank_code`	`varchar(50)`	Yes	—	The bank's proprietary code value (e.g., `165`, `ZBAL`).
`bank_code_issuer`	`varchar(50)`	No	—	The code system issuer (e.g., `BAI`, `BOFA`, `JPM`).
`bank_code_name`	`varchar(255)`	No	—	Short name for this code (e.g., `Preauthorized ACH Credit`).
`bank_code_description`	`text`	No	—	Full description of the code's meaning and usage.
`universal_code_type`	`varchar(50)`	No	—	ISO 20022 code type this maps to (e.g., `ISO20022_SUBFAMILY`).
`universal_code`	`varchar(50)`	No	—	The corresponding universal ISO 20022 code (e.g., `APAC`, `DMCT`).
`treasury_impact`	`text`	No	—	Free-text guidance on cash position and treasury implications.
`cash_application_impact`	`text`	No	—	Free-text guidance on how this code affects cash application decisions.
`cash_application_notes`	`text`	No	—	Additional notes for cash processors reviewing transactions with this code.
`direction_indicator`	`varchar(10)`	No	—	`CREDIT`, `DEBIT`, or `BOTH`. Indicates the typical cash flow direction for this code.
`active_ind`	`boolean`	Yes	`true`	Whether this mapping is active. Inactive mappings are retained for historical reference.

NOTE

Pure audit columns (created_by, created_dt, updated_by, updated_dt) and auto-generated primary keys are omitted unless they carry business meaning.

3. Status Lifecycle

3.1 Bank File Processing Status (`bank_file.status`)

The bank_file.status field tracks the processing state of each uploaded file.

Status	Code	Description	Allowed Transitions
Pending	`PENDING`	File has been received but parsing has not yet started.	`PROCESSED`, `ERROR`, `DUPLICATE`
Processed	`PROCESSED`	File parsed successfully. Transactions extracted and upserted.	—
Error	`ERROR`	Parsing failed. Raw content preserved for debugging.	—
Duplicate	`DUPLICATE`	File detected as a duplicate of a previously uploaded file. `is_duplicate` set to `true`.	—

mermaid

stateDiagram-v2
    [*] --> PENDING : File uploaded
    PENDING --> PROCESSED : Parse succeeds
    PENDING --> ERROR : Parse fails
    PENDING --> DUPLICATE : Duplicate detected

Transition: PENDING → PROCESSED

Trigger: Parser successfully extracts and upserts all transactions from the file.
Preconditions: File content is valid ISO 20022 XML (CAMT.052 or CAMT.053) or CNB JSON. Gatekeeper filter runs during parse.
Side-effects: bank_transaction records created or updated; bank_transaction_history records created; bank_file.transaction_count set to the count of accepted transactions.

Transition: PENDING → ERROR

Trigger: Parser encounters an unrecoverable error (malformed XML, schema violation, unexpected format).
Preconditions: None.
Side-effects: No transactions are upserted. bank_file.raw_content preserved for debugging and reprocessing.

Transition: PENDING → DUPLICATE

Trigger: Incoming file's (source_bank_id, message_id) matches an existing processed file, or content_hash matches.
Preconditions: An existing bank_file record with the same source_bank_id and message_id already exists.
Side-effects: bank_file.is_duplicate set to true; bank_file.duplicate_of_file_id set to the bank_file_id of the original. No transactions are parsed or upserted.

IMPORTANT

Duplicate detection happens during processing. A file with status = 'DUPLICATE' is preserved in the database with its raw_content intact but contributes no transactions to the golden record.

3.2 Bank Transaction Entry Status (`bank_transaction.status`)

The bank_transaction.status field uses ISO 20022 entry status codes. These determine whether a transaction is safe for downstream processing.

Status	Code	Description	Allowed Transitions
Booked	`BOOK`	Funds are settled. Safe to apply to receivables.	—
Pending	`PDNG`	Transaction may still reverse. Apply with caution.	`BOOK`
Information	`INFO`	No cash movement. Informational entry only.	—
Future	`FUTR`	Future-dated transaction. Queue for future application.	`BOOK`

mermaid

stateDiagram-v2
    [*] --> PDNG : CAMT.052 intraday file creates transaction
    [*] --> BOOK : CAMT.053 end-of-day file creates transaction (no prior intraday)
    PDNG --> BOOK : CAMT.053 end-of-day file upserts same bank_reference_id
    FUTR --> BOOK : End-of-day file confirms settlement

Transition: (new) → PDNG

Trigger: A CAMT.052 intraday file is processed and contains a transaction with a bank_reference_id not yet present in bank_transaction.
Preconditions: No existing bank_transaction record for (bank_account_id, bank_reference_id).
Side-effects: New bank_transaction row inserted; bank_transaction_history record created with action_type = 'CREATE', from_status = null, to_status = 'PDNG'.

Transition: (new) → BOOK

Trigger: A CAMT.053 end-of-day file is processed and contains a transaction with no prior intraday record.
Preconditions: No existing bank_transaction record for (bank_account_id, bank_reference_id).
Side-effects: New bank_transaction row inserted with status = 'BOOK'; bank_transaction_history record created with action_type = 'CREATE'.

Transition: PDNG → BOOK

Trigger: A CAMT.053 end-of-day file is processed and contains the same bank_reference_id as an existing PDNG transaction.
Preconditions: Existing bank_transaction row with matching (bank_account_id, bank_reference_id) and status = 'PDNG'.
Side-effects: Existing row updated in place: status set to BOOK, bank_file_id updated to the latest source file, booking_date refreshed. bank_transaction_history record created with action_type = 'UPDATE', from_status = 'PDNG', to_status = 'BOOK'.

3.3 Cash Receipt Reconciliation Status (`bank_transaction.cash_receipt_status`)

The bank_transaction.cash_receipt_status field tracks whether a cash receipt has been created from this transaction.

Status	Code	Description	Allowed Transitions
Unmatched	`UNMATCHED`	No cash receipt has been created from this transaction. Default state.	`MATCHED`, `PARTIAL`
Matched	`MATCHED`	A cash receipt has been created and fully linked to this transaction.	—
Partial	`PARTIAL`	A cash receipt exists but only partially covers this transaction amount.	`MATCHED`

Default on creation: UNMATCHED

Transition: UNMATCHED → MATCHED

Trigger: User creates a cash receipt from this bank transaction and the receipt amount equals the transaction amount.
Preconditions: bank_transaction.status = 'BOOK'; no existing linked cash receipt.
Side-effects: cash_receipt row created; cash_receipt.bank_transaction_id set to this transaction's bank_transaction_id.

Transition: UNMATCHED → PARTIAL

Trigger: User creates a cash receipt from this bank transaction with an amount less than the transaction amount.
Preconditions: bank_transaction.status = 'BOOK'.
Side-effects: cash_receipt row created with partial amount.

Transition: PARTIAL → MATCHED

Trigger: Additional cash receipt(s) created to cover the remaining balance on the transaction.
Preconditions: Sum of linked cash receipts now equals the transaction amount.
Side-effects: Status promoted to MATCHED.

3.4 Reversal Status (`bank_transaction.reversal_status`)

The bank_transaction.reversal_status field applies only to reversal transactions (is_reversal = 1). Non-reversal transactions have reversal_status = null.

Status	Code	Description	Allowed Transitions
Pending	`PENDING`	Reversal detected but not yet reviewed by a user. Initial state for all reversals.	`ACCEPTED`, `REJECTED`
Accepted	`ACCEPTED`	User has reviewed and accepted the reversal. Downstream reversal actions proceed.	—
Rejected	`REJECTED`	User has reviewed and rejected the reversal (e.g., false positive, disputed).	—

mermaid

stateDiagram-v2
    [*] --> PENDING : Reversal transaction detected during parse
    PENDING --> ACCEPTED : User accepts reversal
    PENDING --> REJECTED : User rejects reversal

Transition: (new) → PENDING

Trigger: Parser detects a reversal entry (<RvslInd> = true or RVSL/ARET sub-family code) during file processing.
Preconditions: None.
Side-effects: bank_transaction inserted with is_reversal = 1, reversal_status = 'PENDING', reversal_of_transaction_id set to the original transaction if matched.

Transition: PENDING → ACCEPTED

Trigger: User reviews the reversal and accepts it.
Preconditions: reversal_status = 'PENDING'.
Side-effects: reversal_accepted_at and reversal_accepted_by set. Downstream: any cash receipt created from the original transaction must be reviewed and potentially unapplied.

Transition: PENDING → REJECTED

Trigger: User reviews the reversal and rejects it (e.g., determines it is a false positive or disputes the reversal).
Preconditions: reversal_status = 'PENDING'.
Side-effects: reversal_status set to REJECTED. No downstream action taken on linked cash receipts.

4. Validation & Database Constraints

Unique Constraints

Table	Constraint	Columns	Business Rule
`bank_file`	`uq_bank_file_dedup`	`(source_bank_id, message_id)`	Prevents duplicate file uploads from the same bank. A given `<MsgId>` is unique per bank.
`bank_file_account`	`uq_bank_file_account`	`(bank_file_id, bank_account_id)`	A file can reference a given account at most once.
`bank_file_group`	`uq_bank_file_group`	`(parent_file_id, child_file_id, bank_account_id)`	Prevents duplicate grouping of the same intraday file under the same end-of-day file for the same account.
`bank_transaction`	`uq_bank_transaction_golden_record`	`(bank_account_id, bank_reference_id)`	Enforces the golden-record pattern. A bank's transaction reference is unique within an account. Enables safe upserts.
`bank_code_mapping`	`uq_bank_code_mapping_code`	`(bank_id, bank_code_type, bank_code)`	Prevents duplicate code definitions for the same bank and code type.

Business Validation

File-level deduplication:

When a file's (source_bank_id, message_id) matches an existing file, the incoming file is stored with status = 'DUPLICATE', is_duplicate = true, and duplicate_of_file_id pointing to the original. No transactions are parsed or upserted.
The content_hash (SHA-256) provides a secondary safety net. Even if the message_id differs (e.g., bank reissues a corrected file with a new message ID but identical content), the content hash can flag the overlap for manual review.

Gatekeeper filtering rules:

Before transactions are persisted, a gatekeeper filter is applied during parsing. The filter determines which entries from the raw bank file are accepted for processing.

Rule	Condition	Result
Direction filter	`<CdtDbtInd>` = `DBIT`	Rejected. Only credits (incoming payments) are processed.
Reversal filter	`<RvslInd>` = `true`	Rejected at parse time; handled separately as reversal transactions.
Code whitelist	`<BkTxCd><Prtry><Cd>` not in accepted BAI codes (`165`, `195`, `208`)	Rejected. Only whitelisted BAI codes are accepted.

Transactions that pass all rules are accepted for upsert into bank_transaction. Filtered entries are not stored in bank_transaction; the raw file in bank_file.raw_content preserves all entries for audit purposes.

Transaction-level upsert behavior (golden record):

Scenario	Action
No existing record for `(bank_account_id, bank_reference_id)`	`INSERT` new row. History record created with `action_type = 'CREATE'`, `from_status = null`.
Existing record found	`UPDATE` in place: promote `status`, update `bank_file_id` to latest source, refresh `booking_date`. History record created with `action_type = 'UPDATE'`.

IMPORTANT

The bank_file_id on bank_transaction always points to the latest file that touched the record. To see the full file history for a transaction, query bank_transaction_history, which preserves every bank_file_id that created or updated the transaction.

Precision rules:

Field Category	Precision	Scale
Monetary amounts	15	2
Exchange rates	15	6

5. Code Master Values

5.1 `FILE_TYPE` (bank_file.file_type)

Code	Description	Behavior / When Used
`CAMT052`	Intraday bank statement (ISO 20022 `BkToCstmrAcctRpt`)	Transactions arrive with `PDNG` status. Created when intraday data is available before end-of-day settlement.
`CAMT053`	End-of-day bank statement (ISO 20022 `BkToCstmrStmt`)	Transactions arrive with `BOOK` status. The authoritative statement for the day.

Default on creation: Derived from file content. Not defaulted.

5.2 `ISO20022_ENTRY_STATUS` (bank_transaction.status)

Code	Description	Behavior / When Used
`BOOK`	Booked	Funds are settled and cleared. Safe to apply to receivables. Downstream cash receipt creation is permitted.
`PDNG`	Pending	Transaction is pending settlement. Apply with caution — may reverse. Cash receipts can be created but should be flagged as tentative.
`INFO`	Information	No cash movement. Informational entry only. Do not create cash receipts.
`FUTR`	Future	Future-dated transaction. Queue for application when the settlement date arrives.

Default on creation: Set by the parser from the <Sts> element of the bank file entry. PDNG for CAMT.052 files; BOOK for CAMT.053 files.

5.3 `CASH_RECEIPT_STATUS` (bank_transaction.cash_receipt_status)

Code	Description	Behavior / When Used
`UNMATCHED`	No cash receipt created from this transaction	Default state on transaction creation. Transaction is available for cash receipt creation.
`MATCHED`	Cash receipt created and fully linked	Receipt amount equals transaction amount. Transaction is fully reconciled.
`PARTIAL`	Cash receipt created but partially covers transaction	Receipt amount is less than transaction amount. Additional receipts may be created to cover the remainder.

Default on creation: UNMATCHED

5.4 `REVERSAL_STATUS` (bank_transaction.reversal_status)

Code	Description	Behavior / When Used
`PENDING`	Reversal detected, awaiting review	Set on all reversal transactions at parse time. Requires human review before downstream effects are applied.
`ACCEPTED`	Reversal accepted by a user	Downstream cash application review proceeds. `reversal_accepted_at` and `reversal_accepted_by` are set.
`REJECTED`	Reversal rejected by a user	No downstream action. Transaction treated as non-actionable.

Default on creation: PENDING (applies only to transactions where is_reversal = 1).

5.5 `ISO20022_BALANCE_TYPE` (bank_file_balance.balance_type)

Code	Description	Behavior / When Used
`OPBD`	Opening Booked Balance	Reconciliation anchor — must match the prior statement's `CLBD`. Found as the first balance in CAMT.053 files.
`CLBD`	Closing Booked Balance	End-of-day settled cash position for reporting. Found as the final balance in CAMT.053 files.
`OPAV`	Opening Available Balance	Liquidity available at day start.
`CLAV`	Closing Available Balance	End-of-day available liquidity.
`ITBD`	Interim Booked Balance	Real-time settled position during the day. Found in CAMT.052 intraday files.
`ITAV`	Interim Available Balance	Real-time spendable cash during the day. Found in CAMT.052 intraday files.
`FWAV`	Forward Available Balance	Future projected availability for forecasting.
`PRCD`	Previously Closed Balance	Prior period closing balance for audit reference.

Default on creation: Not defaulted; derived from <Bal><Tp><CdOrPrtry><Cd> in file content.

5.6 `ISO20022_FAMILY` (bank_transaction.family_code)

Code	Description	Behavior / When Used
`RCDT`	Received Credit Transfer	CREDIT direction. Primary family for incoming deposits — match to open invoices.
`ICDT`	Issued Credit Transfer	DEBIT direction. Outgoing payments — not used for receivables matching.
`RDDT`	Received Direct Debit	DEBIT direction. Someone pulled from UTA's account.
`IDDT`	Issued Direct Debit	CREDIT direction. UTA pulled from a customer's account as a collection method.

Default on creation: Not defaulted; derived from <BkTxCd><Domn><Fmly><Cd> in file content.

5.7 `ISO20022_SUBFAMILY` (bank_transaction.sub_family_code)

Code	Description	Behavior / When Used
`DMCT`	Domestic Credit Transfer	High-value, same-day funds. Match by reference.
`XBCT`	Cross-Border Credit Transfer	International wire. Watch for FX conversion.
`APAC`	ACH Pre-Authorized Credit	Predictable timing. Good candidate for auto-matching.
`ACDT`	ACH Credit	1–2 day settlement. Match by ACH trace number.
`ATXN`	ACH Transaction	Check direction indicator to determine nature.
`BOOK`	Book Transfer	May be an internal transfer rather than a customer payment.
`STDO`	Standing Order	Scheduled disbursement.
`RVSL`	Reversal	Critical — represents a reversal of a prior payment. Requires unapplication from receivables.
`ARET`	ACH Return	Critical — ACH payment bounced. Requires reversal of cash application and reopening of the invoice.
`NTAV`	Not Available	Manual review required.
`CCHQ`	Certified Cheque	May have a hold period before funds are available.
`PRCT`	Priority Credit Transfer	Same-day wire. High value.
`SALA`	Salary Payment	Usually not a customer payment.
`ESCT`	Express Credit Transfer	Expedited transfer with fast settlement.

Default on creation: Not defaulted; derived from <BkTxCd><Domn><Fmly><SubFmlyCd> in file content.

WARNING

RVSL (Reversal) and ARET (ACH Return) sub-family codes represent critical financial events. Both require immediate investigation: any cash receipt created from the original transaction must be reviewed, and any cash application must be considered for reversal.

5.8 BAI Codes (`bank_code_mapping` where `bank_code_type = 'BAI_CODE'`)

BAI codes are bank-specific transaction type codes stored in <BkTxCd><Prtry><Cd> with issuer BAI. They are stored in bank_code_mapping with a bank_id and optionally mapped to a universal ISO20022_SUBFAMILY code. The gatekeeper filter accepts only BAI codes 165, 195, and 208.

Bank of America (BOFA):

Code	Name	Direction	Universal Code
`165`	Preauthorized ACH Credit	CREDIT	`APAC`
`169`	Miscellaneous ACH Credit	CREDIT	`ACDT`
`195`	Incoming Wire Transfer	CREDIT	`DMCT`
`208`	Incoming International Wire	CREDIT	`XBCT`
`255`	Check Deposit	CREDIT	`CCHQ`
`399`	Miscellaneous Credit	CREDIT	—
`451`	ACH Debit Received	DEBIT	`ATXN`
`469`	Miscellaneous ACH Debit	DEBIT	`ATXN`
`495`	Outgoing Wire Transfer	DEBIT	`DMCT`
`508`	Outgoing International Wire	DEBIT	`XBCT`
`527`	Standing Order Debit	DEBIT	`STDO`
`699`	Miscellaneous Debit	DEBIT	—

JPMorgan Chase (JPM):

Code	Name	Direction	Universal Code
`165`	Preauthorized ACH Credit	CREDIT	`APAC`
`169`	Miscellaneous ACH Credit	CREDIT	`ACDT`
`195`	Incoming Wire Transfer	CREDIT	`DMCT`
`208`	Incoming International Wire	CREDIT	`XBCT`
`255`	Check Deposit	CREDIT	`CCHQ`
`475`	ACH Debit	DEBIT	`ATXN`
`495`	Outgoing Wire Transfer	DEBIT	`DMCT`
`508`	Outgoing International Wire	DEBIT	`XBCT`

Default on creation: Not applicable. BAI codes are seeded reference data managed in bank_code_mapping, not set at transaction parse time.

5.9 File Grouping Matching Criteria (`bank_file_group.matching_criteria`)

Code	Description	Behavior / When Used
`AUTO`	Automatic match	System matched CAMT.052 to CAMT.053 using `source_bank_id` + `bank_account_id` + `statement_date`. Applied during CAMT.053 upload.
`MANUAL`	Manual association	User explicitly associated a CAMT.052 file with a CAMT.053 file. Used when auto-grouping fails to find a match.

Default on creation: AUTO when created by the system during file processing; MANUAL when created by a user action.

6. Cross-References

Document	Relationship
Cash Receipts Data Model	`bank_transaction.bank_transaction_id` is optionally stored on `cash_receipt.bank_transaction_id` as an audit trail back to the originating bank data. `bank_transaction.cash_receipt_status` tracks whether a receipt has been created.
Worksheets Data Model	Worksheets are created downstream of cash receipts, which originate from bank transactions. No direct FK from worksheets to bank ingestion tables.
Accounting Data Model	Bank balance records in `bank_file_balance` are used for daily cash position reporting and GL reconciliation anchoring.
Parties Data Model	`bank_account` (FK target throughout this domain) is a shared reference table; `party_bank_account` links parties to their bank accounts for outbound payments.

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Bank Ingestion Data Model ​

1. Executive Summary ​

Purpose ​

Scope ​

2. Data Model ​

2.1 Entity-Relationship Diagram ​

2.2 bank_file ​

2.3 bank_file_account ​

2.4 bank_file_header ​

2.5 bank_file_balance ​

2.6 bank_file_summary ​

2.7 bank_file_group ​

2.8 bank_transaction ​

Core Fields ​

Reversal Fields ​

Classification Fields ​

Remittance and Counterparty Fields ​

Entry Reference and FX Fields ​

2.9 bank_transaction_history ​

2.10 bank_code_mapping ​

3. Status Lifecycle ​

3.1 Bank File Processing Status (bank_file.status) ​

Transition: PENDING → PROCESSED ​

Transition: PENDING → ERROR ​

Transition: PENDING → DUPLICATE ​

3.2 Bank Transaction Entry Status (bank_transaction.status) ​

Transition: (new) → PDNG ​

Transition: (new) → BOOK ​

Transition: PDNG → BOOK ​

3.3 Cash Receipt Reconciliation Status (bank_transaction.cash_receipt_status) ​

Transition: UNMATCHED → MATCHED ​

Transition: UNMATCHED → PARTIAL ​

Transition: PARTIAL → MATCHED ​

3.4 Reversal Status (bank_transaction.reversal_status) ​

Transition: (new) → PENDING ​

Transition: PENDING → ACCEPTED ​

Transition: PENDING → REJECTED ​

4. Validation & Database Constraints ​

Unique Constraints ​

Business Validation ​

5. Code Master Values ​

5.1 FILE_TYPE (bank_file.file_type) ​

5.2 ISO20022_ENTRY_STATUS (bank_transaction.status) ​

5.3 CASH_RECEIPT_STATUS (bank_transaction.cash_receipt_status) ​

5.4 REVERSAL_STATUS (bank_transaction.reversal_status) ​

5.5 ISO20022_BALANCE_TYPE (bank_file_balance.balance_type) ​

5.6 ISO20022_FAMILY (bank_transaction.family_code) ​

5.7 ISO20022_SUBFAMILY (bank_transaction.sub_family_code) ​

5.8 BAI Codes (bank_code_mapping where bank_code_type = 'BAI_CODE') ​

5.9 File Grouping Matching Criteria (bank_file_group.matching_criteria) ​

6. Cross-References ​

Bank Ingestion Data Model

1. Executive Summary

Purpose

Scope

2. Data Model

2.1 Entity-Relationship Diagram

2.2 `bank_file`

2.3 `bank_file_account`

2.4 `bank_file_header`

2.5 `bank_file_balance`

2.6 `bank_file_summary`

2.7 `bank_file_group`

2.8 `bank_transaction`

Core Fields

Reversal Fields

Classification Fields

Remittance and Counterparty Fields

Entry Reference and FX Fields

2.9 `bank_transaction_history`

2.10 `bank_code_mapping`

3. Status Lifecycle

3.1 Bank File Processing Status (`bank_file.status`)

Transition: PENDING → PROCESSED

Transition: PENDING → ERROR

Transition: PENDING → DUPLICATE

3.2 Bank Transaction Entry Status (`bank_transaction.status`)

Transition: (new) → PDNG

Transition: (new) → BOOK

Transition: PDNG → BOOK

3.3 Cash Receipt Reconciliation Status (`bank_transaction.cash_receipt_status`)

Transition: UNMATCHED → MATCHED

Transition: UNMATCHED → PARTIAL

Transition: PARTIAL → MATCHED

3.4 Reversal Status (`bank_transaction.reversal_status`)

Transition: (new) → PENDING

Transition: PENDING → ACCEPTED

Transition: PENDING → REJECTED

4. Validation & Database Constraints

Unique Constraints

Business Validation

5. Code Master Values

5.1 `FILE_TYPE` (bank_file.file_type)

5.2 `ISO20022_ENTRY_STATUS` (bank_transaction.status)

5.3 `CASH_RECEIPT_STATUS` (bank_transaction.cash_receipt_status)

5.4 `REVERSAL_STATUS` (bank_transaction.reversal_status)

5.5 `ISO20022_BALANCE_TYPE` (bank_file_balance.balance_type)

5.6 `ISO20022_FAMILY` (bank_transaction.family_code)

5.7 `ISO20022_SUBFAMILY` (bank_transaction.sub_family_code)

5.8 BAI Codes (`bank_code_mapping` where `bank_code_type = 'BAI_CODE'`)

5.9 File Grouping Matching Criteria (`bank_file_group.matching_criteria`)

6. Cross-References