Client Framework

Appearance

Bank Ingestion Procedures ​

1. Executive Summary ​

This document specifies every data mutation procedure in the Bank Ingestion domain — the step-by-step operations that create, update, and link records across the bank ingestion tables when bank statement files are uploaded, parsed, deduplicated, and reconciled.

Bank ingestion is the entry point for all cash into the Client Payments system. Every dollar that flows through worksheets, settlements, and payments originates as a bank_transaction record created by these procedures. The procedures fall into five functional groups:

  1. File Upload & Ingestion — Validate, parse, deduplicate, and persist a bank statement file along with its header, balances, summary, and file-to-account linkages. This is the primary orchestration procedure, coordinating writes across six tables in a single operation.

  2. Transaction Upsert (Golden Record) — The core insert-or-update cycle that maintains the bank_transaction golden record. Intraday files create PDNG records; end-of-day files promote existing records to BOOK. Every mutation produces a bank_transaction_history audit record.

  3. File Grouping — Automatic and manual association of CAMT.052 (intraday) files under their corresponding CAMT.053 (end-of-day) files, stored in bank_file_group.

  4. Reversal Processing — Accept or reject reversal transactions that arrive with PENDING status. Reversals are linked to their original transactions and require explicit human action before downstream processing.

  5. Cash Receipt Generation — Create a cash_receipt record from a bank transaction, linking the two domains through the (bank_account_id, bank_reference_id) composite key.

Relationship to Other Foundation Documents ​

2. Key Procedures ​

2.1 Upload and Process a Bank File ​

Operation: uploadBankFile

Trigger: A user selects a bank and uploads an ISO 20022 XML file (CAMT.052 or CAMT.053) through the bank files management interface.

Input Parameters:

  • file: File — the XML file to upload (must have .xml extension)
  • sourceBankId: String — FK to bank.bank_id (e.g., BOFA, JPM, CNB); selected by the user before upload

Orchestrates all writes needed to persist a bank statement file: deduplication check, file record creation, header/balance/summary extraction, account matching, transaction upsert, status finalization, and (for CAMT.053 files) auto-grouping of matching intraday files.

Step 1. Validate File Format and Bank Identity ​

  • Source: User-submitted file and sourceBankId.
  • Action: No database writes. Validation only.
  • Logic:
    • Reject if file is absent or file.name does not end in .xml.
    • Reject if sourceBankId is absent.
    • Reject if the file does not parse as a valid ISO 20022 document (must contain BkToCstmrStmt for CAMT.053 or BkToCstmrAcctRpt for CAMT.052).
    • Derive bank_file.file_type: CAMT053 if BkToCstmrStmt root element present; CAMT052 if BkToCstmrAcctRpt present.
    • Extract bank_file.message_id from <GrpHdr><MsgId>.
    • Extract bank_file.xsd_name from the xmlns namespace attribute (e.g., camt.052.001.02).
    • Detect bank identity from file content (BIC code, servicer name, routing patterns). If detected bank identity does not match sourceBankId with HIGH confidence, reject with a bank mismatch error.

Step 2. Deduplicate Against Existing Files ​

  • Source: Computed SHA-256 hash of raw file content (content_hash) and extracted message_id + sourceBankId.
  • Action: SELECT from bank_file. No writes.
  • Logic:
    • Compute content_hash = SHA-256 of the raw XML string.
    • Query bank_file WHERE content_hash = computed hash. If a match exists, reject — this is an exact content duplicate. Return the original bank_file_id in the error message.
    • If no content hash match, query bank_file WHERE source_bank_id = sourceBankId AND message_id = extracted message_id. If a match exists, reject — this is a semantic duplicate (same message from same bank). Return the original bank_file_id in the error message.
    • If neither check fires, proceed.

Step 3. Apply Gatekeeper Filter and Extract Transactions ​

  • Source: Raw XML content parsed in memory.
  • Action: No database writes. Produces an in-memory list of accepted transactions.
  • Logic:
    • Parse all <Ntry> (Entry) elements from each <Stmt> or <Rpt> block.
    • For each entry, apply the gatekeeper filter in order:
      1. Reject if <CdtDbtInd> = DBIT (only credit transactions are processed).
      2. Reject if <RvslInd> = true (reversals are handled separately as reversal transactions).
      3. Reject if <BkTxCd><Prtry><Cd> is present but NOT in the whitelist (165, 195, 208). Entries with no proprietary code are accepted.
    • For each accepted entry, extract one transaction record using the lump-sum rule: use <Ntry><Amt> for the amount — never sum child <TxDtls> amounts.
    • Use <AcctSvcrRef> as bank_reference_id. Skip entries that have no <AcctSvcrRef>.
    • Set status = PDNG for CAMT.052 files; BOOK for CAMT.053 files.
    • Date fallback: use <Ntry><BookgDt><Dt> if present; otherwise use <GrpHdr><CreDtTm> date portion.
    • Aggregate remittance info by iterating all <NtryDtls><TxDtls> children. Extract payer_name, payer_id, invoice_number, end_to_end_id from <RmtInf><Ustrd> using pattern matching.
    • Reject if no transactions pass the gatekeeper filter (nothing to persist).

Step 4. Create the bank_file Record ​

  • Source: Parsed file metadata from Step 1 and Step 3.
  • Action: INSERT one row into bank_file.
  • Logic:
    • Set bank_file.source_bank_id = sourceBankId.
    • Set bank_file.file_name = original file name.
    • Set bank_file.file_type = derived file type (CAMT052 or CAMT053).
    • Set bank_file.xsd_name = extracted from namespace attribute.
    • Set bank_file.message_id = extracted from <GrpHdr><MsgId>.
    • Set bank_file.content_hash = SHA-256 of raw XML content.
    • Set bank_file.statement_currency = currency from first account or first balance entry.
    • Set bank_file.raw_content = full XML string (preserved for audit and reprocessing).
    • Set bank_file.is_duplicate = false.
    • Set bank_file.status = PENDING (initial state before processing completes).

Step 5. Extract and Persist File Header ​

  • Source: <GrpHdr> and <Stmt> or <Rpt> header elements from the XML.
  • Action: INSERT one row into bank_file_header.
  • Logic:
    • Set bank_file_header.bank_file_id = new bank_file.bank_file_id from Step 4.
    • Set message_id, creation_date_time, statement_id, electronic_sequence_number, legal_sequence_number, statement_from_date, statement_to_date, account_iban, account_other_id, account_currency, account_owner_name, servicer_bic, servicer_name, additional_info from corresponding ISO 20022 XML elements.
    • Skip this step if no header elements are present in the file.

Step 6. Extract and Persist Balances ​

  • Source: All <Bal> elements from the XML.
  • Action: INSERT one row into bank_file_balance per balance element found.
  • Logic:
    • Set bank_file_balance.bank_file_id = bank_file.bank_file_id from Step 4.
    • Set balance_type from <Bal><Tp><CdOrPrtry><Cd> (e.g., OPBD, CLBD, ITBD).
    • Set amount, currency, credit_debit_indicator, balance_date, balance_date_time, availability_days, account_iban, account_other_id from corresponding XML elements.
    • Skip this step if no balance elements are present.

Step 7. Extract and Persist Transaction Summary ​

  • Source: <TxsSummry> element from the XML.
  • Action: INSERT one row into bank_file_summary.
  • Logic:
    • Set bank_file_summary.bank_file_id = bank_file.bank_file_id from Step 4.
    • Set total_credit_entries, total_credit_amount, total_debit_entries, total_debit_amount, net_entry_amount from the summary block.
    • Skip this step if no summary element is present.

Step 8. Match File Accounts to Bank Account Records ​

  • Source: Account identifiers (IBAN or other account ID) extracted from each <Stmt> or <Rpt> block in the XML.
  • Action: SELECT from bank_account for each account found in the file. No writes at this step.
  • Logic:
    • For each unique account in the file, query bank_account WHERE bank_id = sourceBankId AND bank_account_no = account identifier from file.
    • Build a map of account identifier → bank_account_id.
    • Log a warning for any account identifiers in the file that have no matching bank_account record; those transactions will be skipped.
    • If no accounts match at all, reject the upload with an error listing the unmatched account identifiers.

Step 9. Create bank_file_account Records and Upsert Transactions Per Account ​

  • Source: Account-to-transaction groupings built in Step 8; transactions extracted in Step 3.
  • Action: For each matched account: INSERT one row into bank_file_account; then call the Transaction Upsert procedure (2.2) for that account's transactions.
  • Logic:
    • For each bank_account_id that matched:
      • INSERT bank_file_account row: set bank_file_id, bank_account_id, account_currency = bank_file.statement_currency, transaction_count = count of transactions for this account.
      • Execute procedure 2.2 (Transaction Upsert) for each transaction belonging to this account.
    • Skip transactions whose account identifier had no match in Step 8.
    • Track cumulative new_count and updated_count across all accounts.

Step 10. Finalize File Status ​

  • Source: bank_file record from Step 4; total transaction count from Step 9.
  • Action: UPDATE bank_file.
  • Logic:
    • Set bank_file.status = PROCESSED.
    • Set bank_file.transaction_count = total number of transactions accepted (after gatekeeper filtering, before account skipping).

Step 11. Auto-Group Intraday Files (CAMT.053 Only) ​

  • Source: Newly processed bank_file record; source_bank_id; matched account IDs.
  • Action: Triggers procedure 2.3 (Auto-Group Intraday Files) for each matched account. No direct writes in this step.
  • Logic:
    • Only execute if bank_file.file_type = CAMT053.
    • Extract statement_date from file header (statementFromDate or creationDateTime, normalized to start of day).
    • For each bank_account_id that had matched transactions, call procedure 2.3 with eod_file_id = new bank_file_id, source_bank_id, bank_account_id, and statement_date.

Side-effects:

  • One bank_file row is created (status PENDING → PROCESSED).
  • One bank_file_header row is created.
  • Zero or more bank_file_balance rows are created.
  • Zero or one bank_file_summary row is created.
  • One bank_file_account row is created per matched bank account in the file.
  • Zero or more bank_transaction rows are created (INSERT) or updated (UPDATE via golden record upsert).
  • One bank_transaction_history row is created per transaction that was created or updated.
  • Zero or more bank_file_group rows may be created (if CAMT.053 and matching CAMT.052 files exist).

Postconditions:

  • bank_file.status = PROCESSED.
  • All accepted transactions are present in bank_transaction with status = PDNG (CAMT.052) or BOOK (CAMT.053).
  • bank_transaction.cash_receipt_status = UNMATCHED for all newly inserted transactions.
  • Every transaction mutation has a corresponding bank_transaction_history record.
  • Any matching intraday files are linked under this file via bank_file_group.

2.2 Upsert Bank Transaction (Golden Record) ​

Operation: upsertBankTransaction

Trigger: Called internally during procedure 2.1 (Upload and Process a Bank File) for each transaction that passes the gatekeeper filter and belongs to a matched bank account.

Input Parameters:

  • bankFileId: Integer — bank_file.bank_file_id of the source file
  • bankAccountId: Integer — bank_account.bank_account_id of the UTA account
  • transaction: NormalizedTransaction — extracted transaction data from the parsed file
  • actorId: String — identifier of the user who initiated the upload

Maintains the golden record in bank_transaction using (bank_account_id, bank_reference_id) as the composite unique key. Intraday arrivals create new PDNG records; end-of-day arrivals either create BOOK records or promote existing PDNG records to BOOK.

Step 1. Look Up Existing Record ​

  • Source: bank_transaction table.
  • Action: SELECT from bank_transaction WHERE bank_account_id = bankAccountId AND bank_reference_id = transaction.transactionId. No writes.
  • Logic:
    • Map transaction.status from adapter notation to database status: PROVISIONAL → PDNG; all others → BOOK.
    • If a row is found, proceed to Step 2 (UPDATE path).
    • If no row is found, proceed to Step 3 (INSERT path).

Step 2. Update Existing Transaction (Status Promotion) ​

  • Source: Existing bank_transaction row found in Step 1.
  • Action: UPDATE bank_transaction; INSERT into bank_transaction_history.
  • Logic:
    • Set bank_transaction.status = mapped status from Step 1 (typically promoting PDNG → BOOK).
    • Set bank_transaction.bank_file_id = bankFileId (updated to point to the latest source file).
    • Set bank_transaction.booking_date = transaction.bookingDate (refreshed from the later file).
    • Set bank_transaction.value_date = transaction.valueDate if present.
    • Set bank_transaction.debtor_name = transaction.debtorName if present in this file (end-of-day files often have richer party data).
    • Set bank_transaction.end_to_end_id = transaction.remittance.endToEndId if present.
    • INSERT bank_transaction_history: set bank_transaction_id, bank_file_id = bankFileId, from_status = previous status, to_status = new status, booking_date = transaction.bookingDate, action_type = UPDATE.

Step 3. Insert New Transaction Record ​

  • Source: transaction data from the parser; reversal linking logic if transaction.isReversal = true.
  • Action: INSERT into bank_transaction; INSERT into bank_transaction_history.
  • Logic:
    • If transaction.isReversal = true, attempt to link to the original transaction:
      • Strategy 1: Query bank_transaction WHERE bank_account_id = bankAccountId AND end_to_end_id = transaction.remittance.endToEndId AND direction = opposite direction AND is_reversal = 0. Use the most recently created match. Set reversal_of_transaction_id to its bank_transaction_id.
      • Strategy 2 (fallback): Query bank_transaction WHERE bank_account_id = bankAccountId AND amount = transaction.amount AND currency = transaction.currency AND direction = opposite direction AND is_reversal = 0. Use the most recently created match.
      • If no match found, set reversal_of_transaction_id = null and log a warning.
    • INSERT bank_transaction with all extracted fields:
      • bank_file_id = bankFileId, bank_account_id = bankAccountId
      • bank_reference_id = transaction.transactionId
      • status = mapped status from Step 1
      • booking_date, value_date, amount, currency, direction
      • is_reversal = 1 if reversal, else 0
      • reversal_of_transaction_id = linked original (or null)
      • reversal_status = PENDING if is_reversal = 1, else null
      • cash_receipt_status = UNMATCHED (default)
      • All remittance fields: payer_name, payer_id, invoice_number, end_to_end_id, remittance_info
      • All counterparty fields: debtor_name, debtor_account, debtor_org_id, creditor_name, creditor_account
      • All agent fields: debtor_agent_name, creditor_agent_name
      • All classification fields: transaction_type, bank_transaction_code, source_bank, domain_code, family_code, sub_family_code, transaction_code_issuer
      • All FX fields: original_amount, original_currency, exchange_rate
      • Reference fields: tx_id, entry_class_type, entry_class_ref, purpose, raw_entry_ref, booking_date_time
      • availability_days
    • INSERT bank_transaction_history: set bank_transaction_id = new ID, bank_file_id = bankFileId, from_status = null, to_status = mapped status, booking_date = transaction.bookingDate, action_type = CREATE.

Side-effects:

  • bank_transaction_history receives one row for every INSERT or UPDATE to bank_transaction.

Postconditions:

  • A bank_transaction row exists for (bankAccountId, transaction.transactionId) with the most current status and file reference.
  • bank_transaction_history contains an unbroken audit trail of every file that touched this transaction.
  • Reversal transactions have reversal_status = PENDING and (when linkable) reversal_of_transaction_id populated.

2.3 Auto-Group Intraday Files Under an End-of-Day File ​

Operation: autoGroupIntradayFiles

Trigger: Called automatically at the end of procedure 2.1 when a CAMT.053 (end-of-day) file is successfully processed.

Input Parameters:

  • eodFileId: Integer — bank_file.bank_file_id of the CAMT.053 file
  • sourceBankId: String — FK to bank.bank_id
  • bankAccountId: Integer — bank_account.bank_account_id to scope the grouping
  • statementDate: Date — normalized to start of day; derived from file header statementFromDate or creationDateTime
  • actorId: String — identifier of the user who initiated the upload

Associates existing CAMT.052 (intraday) files with the newly uploaded CAMT.053 (end-of-day) file for the same bank account and statement date. Produces bank_file_group records with matching_criteria = AUTO.

Step 1. Find Matching Intraday Files ​

  • Source: bank_file and bank_file_account tables.
  • Action: SELECT from bank_file INNER JOIN bank_file_account on bank_file_id. No writes.
  • Logic:
    • Filter: bank_file.source_bank_id = sourceBankId, bank_file.file_type = CAMT052, bank_file_account.bank_account_id = bankAccountId, bank_file.is_duplicate = false.
    • Return the list of matching CAMT.052 bank_file_id values.

Step 2. Exclude Already-Grouped Files ​

  • Source: bank_file_group table.
  • Action: SELECT from bank_file_group WHERE parent_file_id = eodFileId. No writes.
  • Logic:
    • Build a set of child_file_id values already grouped under this parent.
    • Filter the list from Step 1 to exclude any file IDs already in that set.
    • If no files remain after filtering, return with zero grouped files (success, no new groups needed).

Step 3. Create Group Records ​

  • Source: Filtered intraday file list from Step 2.
  • Action: INSERT one row into bank_file_group per intraday file to group.
  • Logic:
    • For each intraday bank_file_id not yet grouped:
      • INSERT bank_file_group: set parent_file_id = eodFileId, child_file_id = intraday file ID, bank_account_id = bankAccountId, statement_date = statementDate, matching_criteria = AUTO.

Side-effects:

  • One bank_file_group row is created per newly associated intraday file.

Postconditions:

  • All CAMT.052 files from the same bank, account, and statement date that are not duplicates are linked to this CAMT.053 file via bank_file_group with matching_criteria = AUTO.

2.4 Manually Group an Intraday File Under an End-of-Day File ​

Operation: manualGroupFile

Trigger: A user explicitly associates a specific CAMT.052 file with a specific CAMT.053 file through the bank files management interface, typically when auto-grouping failed to find a match.

Input Parameters:

  • eodFileId: Integer — bank_file.bank_file_id of the CAMT.053 parent file
  • intradayFileId: Integer — bank_file.bank_file_id of the CAMT.052 child file
  • bankAccountId: Integer (optional) — bank_account.bank_account_id to scope the grouping
  • actorId: String — identifier of the user performing the association

Creates a single bank_file_group record with matching_criteria = MANUAL. No matching algorithm is applied; the user explicitly controls the association.

Step 1. Create Group Record ​

  • Source: User-supplied eodFileId, intradayFileId, bankAccountId.
  • Action: INSERT one row into bank_file_group.
  • Logic:
    • Set bank_file_group.parent_file_id = eodFileId.
    • Set bank_file_group.child_file_id = intradayFileId.
    • Set bank_file_group.bank_account_id = bankAccountId (may be null if not provided).
    • Set bank_file_group.statement_date = null (no date matching is applied for manual grouping).
    • Set bank_file_group.matching_criteria = MANUAL.

NOTE

The unique constraint 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. If this constraint is violated, the operation fails.

Side-effects:

  • One bank_file_group row is created.

Postconditions:

  • The intraday file is linked to the end-of-day file via bank_file_group with matching_criteria = MANUAL.

2.5 Accept a Reversal Transaction ​

Operation: acceptReversal

Trigger: A user reviews a reversal transaction (one where bank_transaction.is_reversal = 1 and reversal_status = PENDING) and confirms it should be processed.

Input Parameters:

  • bankTransactionId: Integer — bank_transaction.bank_transaction_id of the reversal to accept
  • actorId: String — identifier of the user accepting the reversal

Moves a reversal transaction from PENDING to ACCEPTED status, enabling downstream cash application review. Does not automatically unapply any cash receipts — that remains a manual workflow responsibility.

Step 1. Validate Reversal Preconditions ​

  • Source: bank_transaction row matching bankTransactionId.
  • Action: SELECT from bank_transaction. No writes.
  • Logic:
    • If no row found: reject with "Transaction not found".
    • If bank_transaction.is_reversal ≠ 1: reject with "This is not a reversal transaction".
    • If bank_transaction.reversal_status = ACCEPTED: reject with "Reversal already accepted".

Step 2. Update Reversal Status ​

  • Source: bank_transaction row validated in Step 1.
  • Action: UPDATE bank_transaction.
  • Logic:
    • Set bank_transaction.reversal_status = ACCEPTED.
    • Set bank_transaction.reversal_accepted_at = current timestamp.
    • Set bank_transaction.reversal_accepted_by = actorId.

WARNING

Accepting a reversal does not automatically reverse any cash applications. After acceptance, the cash processor must manually review any cash_receipt records linked to the original transaction (via cash_receipt.bank_transaction_id → bank_transaction.reversal_of_transaction_id) and initiate the appropriate worksheet return or receipt void workflow if the payment has bounced.

Side-effects:

  • No additional records are created or modified beyond the bank_transaction update.

Postconditions:

  • bank_transaction.reversal_status = ACCEPTED.
  • bank_transaction.reversal_accepted_at and bank_transaction.reversal_accepted_by are set.
  • The reversal transaction is now visible to downstream cash review processes.

2.6 Reject a Reversal Transaction ​

Operation: rejectReversal

Trigger: A user reviews a reversal transaction (one where bank_transaction.is_reversal = 1 and reversal_status = PENDING) and determines it should not be processed (e.g., the reversal is a false positive or is disputed).

Input Parameters:

  • bankTransactionId: Integer — bank_transaction.bank_transaction_id of the reversal to reject
  • actorId: String — identifier of the user rejecting the reversal

Moves a reversal transaction from PENDING to REJECTED status. No downstream actions are taken on linked cash receipts or applications.

Step 1. Validate Reversal Preconditions ​

  • Source: bank_transaction row matching bankTransactionId.
  • Action: SELECT from bank_transaction. No writes.
  • Logic:
    • If no row found: reject with "Transaction not found".
    • If bank_transaction.is_reversal ≠ 1: reject with "This is not a reversal transaction".
    • If bank_transaction.reversal_status ≠ PENDING: reject with "Reversal is not pending" (cannot reject an already-accepted or already-rejected reversal).

Step 2. Update Reversal Status ​

  • Source: bank_transaction row validated in Step 1.
  • Action: UPDATE bank_transaction.
  • Logic:
    • Set bank_transaction.reversal_status = REJECTED.

Side-effects:

  • No additional records are created or modified.

Postconditions:

  • bank_transaction.reversal_status = REJECTED.
  • The reversal transaction is treated as non-actionable; no downstream cash receipt or application changes occur.

2.7 Generate Cash Receipt from Bank Transaction ​

Operation: generateCashReceipt

Trigger: A user selects a BOOK-status bank transaction and clicks the "Generate Cash Receipt" action in the bank files interface.

Input Parameters:

  • bankTransactionId: Integer — bank_transaction.bank_transaction_id to create a receipt from
  • actorId: String — identifier of the user initiating the action

Creates a new cash_receipt from a bank transaction and auto-creates the default 1:1 cash_receipt_split. If a receipt already exists for the same (bank_account_id, bank_reference_id) composite key, the existing receipt is updated to reflect any status change (e.g., PDNG → BOOK) rather than creating a duplicate.

Step 1. Validate Transaction Eligibility ​

  • Source: bank_transaction row matching bankTransactionId.
  • Action: SELECT from bank_transaction (via joined view with file and account info). No writes.
  • Logic:
    • If no row found: reject.
    • If bank_transaction.direction ≠ CREDIT: reject — only credit transactions can generate cash receipts.
    • If bank_transaction.is_reversal = 1: reject — reversal transactions cannot generate receipts; they must be handled by reviewing the original transaction's cash application.
    • If a cash_receipt already linked to this transaction exists: reject with the existing cash_receipt_id in the error.

Step 2. Check for Existing Receipt by Composite Key ​

  • Source: cash_receipt table.
  • Action: SELECT from cash_receipt WHERE bank_account_id = bank_transaction.bank_account_id AND bank_ref_id = bank_transaction.bank_reference_id.
  • Logic:
    • If a matching cash_receipt exists and cash_receipt.entry_status ≠ bank_transaction.status:
      • UPDATE cash_receipt: set entry_status = bank_transaction.status, booking_date = bank_transaction.booking_date, bank_transaction_id = bank_transaction.bank_transaction_id.
      • Return the updated receipt (do not create a new one).
    • If a matching cash_receipt exists with the same status: return the existing receipt unchanged.
    • If no matching cash_receipt exists: proceed to Step 3.

Step 3. Create the Cash Receipt ​

  • Source: bank_transaction data.
  • Action: INSERT one row into cash_receipt.
  • Logic:
    • Set cash_receipt.entry_status = bank_transaction.status.
    • Set cash_receipt.bank_ref_id = bank_transaction.bank_reference_id.
    • Set cash_receipt.bank_transaction_id = bank_transaction.bank_transaction_id.
    • Set cash_receipt.booking_date = bank_transaction.booking_date.
    • Set cash_receipt.remittance_info = bank_transaction.remittance_info.
    • Set cash_receipt.bank_account_id = bank_transaction.bank_account_id.
    • Set cash_receipt.deposit_date = bank_transaction.booking_date.
    • Set cash_receipt.cash_receipt_ref = bank_transaction.bank_reference_id.
    • Set cash_receipt.filename = name of the bank file that contained this transaction.
    • Set cash_receipt.original_receipt_amt = bank_transaction.amount.
    • Set cash_receipt.receipt_amt = bank_transaction.amount.
    • Set cash_receipt.net_receipt_amt = bank_transaction.amount.
    • Set cash_receipt.original_currency_cd = bank_transaction.currency.
    • Set cash_receipt.currency_cd = bank_transaction.currency.
    • Set cash_receipt.posting_status_cd = U (Unposted, ready for GL).

Step 4. Create Default Split ​

  • Source: Newly created cash_receipt from Step 3.
  • Action: INSERT one row into cash_receipt_split.
  • Logic:
    • Create a default 1:1 split covering the full net_receipt_amt.
    • This default split is the starting point for worksheet creation.
    • See Cash Receipts Procedures for full split creation logic.

Side-effects:

  • One cash_receipt row is created.
  • One cash_receipt_split row (default split) is created.
  • bank_transaction.cash_receipt_status is NOT updated by this procedure in the PoC. Cash receipt reconciliation status tracking (UNMATCHED → MATCHED) is a downstream concern.

NOTE

PoC Artifact: The bank_transaction.cash_receipt_status field (UNMATCHED, MATCHED, PARTIAL) is defined in the schema and its lifecycle is documented in the data model, but the PoC does not automatically update it when a cash receipt is created. Production should update cash_receipt_status to MATCHED (or PARTIAL) when a receipt is linked.

Postconditions:

  • A cash_receipt row exists linked to the bank_transaction via cash_receipt.bank_transaction_id and the composite (bank_account_id, bank_ref_id) key.
  • A default cash_receipt_split exists for the full receipt amount.
  • The cash receipt is ready for split approval and worksheet creation.

3. Business Rules & Logic ​

3.1 Golden Record Upsert via Composite Unique Key ​

Business rule: A bank transaction is uniquely identified by the combination of the bank account it arrived in and the bank's own reference for that transaction. This composite key must never produce duplicates regardless of how many files contain the same transaction.

Data-level enforcement:

  • Read: bank_transaction WHERE bank_account_id = :bankAccountId AND bank_reference_id = :bankReferenceId.
  • Guard: If a row is found, UPDATE (do not INSERT another row). The unique constraint uq_bank_transaction_golden_record (bank_account_id, bank_reference_id) would reject a duplicate INSERT at the database level as a secondary safety net.
  • Write: On INSERT, populate all extracted fields. On UPDATE, set status, bank_file_id, booking_date, and any richer fields available from the end-of-day file.

3.2 Gatekeeper Filter: Credit-Only, No Reversals, Code Whitelist ​

Business rule: The ingestion pipeline only processes incoming credit transactions matching approved payment types. Debits, reversal entries, and unrecognized transaction codes are filtered out at parse time to prevent unintended data from entering the golden record.

Data-level enforcement:

  • Read: <CdtDbtInd>, <RvslInd>, and <BkTxCd><Prtry><Cd> from each XML entry.
  • Guard: Reject entry if CdtDbtInd = DBIT. Reject if RvslInd = true. Reject if a proprietary code is present and not in the whitelist (165, 195, 208). Accept if no proprietary code is present.
  • Write: Accepted entries are persisted. Filtered entries are not stored in bank_transaction; the full XML is preserved in bank_file.raw_content for audit.

3.3 File-Level Deduplication ​

Business rule: A bank file that has already been successfully processed must not create duplicate records. Deduplication operates on two signals: exact content (SHA-256 hash) and semantic identity (same bank + same message ID).

Data-level enforcement:

  • Read: bank_file WHERE content_hash = :hash. Also bank_file WHERE source_bank_id = :bank AND message_id = :msgId.
  • Guard: If either check finds an existing record, reject the upload. Do not create a new bank_file record. Return the original bank_file_id in the error response.
  • Write: On pass, set bank_file.is_duplicate = false. (A bank_file record is still created even for duplicates in some flows, but in the primary upload path a duplicate is rejected before any writes occur.)

3.4 Reversal Transactions Require Manual Review ​

Business rule: Reversal entries detected in bank files (those with <RvslInd> = true, or sub-family code RVSL or ARET) represent events where money that was received is being clawed back. Because reversals carry direct financial risk, they must not be processed automatically — a cash processor must explicitly accept or reject each reversal before any downstream action is taken.

Data-level enforcement:

  • Read: bank_transaction.is_reversal, bank_transaction.reversal_status.
  • Guard: generateCashReceipt (procedure 2.7) rejects any transaction where is_reversal = 1. The acceptReversal operation (procedure 2.5) requires reversal_status = PENDING.
  • Write: On reversal INSERT, set bank_transaction.reversal_status = PENDING. On accept, set reversal_status = ACCEPTED, reversal_accepted_at = now, reversal_accepted_by = actorId. On reject, set reversal_status = REJECTED.

3.5 Lump-Sum Amount Rule ​

Business rule: A single bank entry (<Ntry>) can contain multiple sub-transactions (<TxDtls>) representing a batch payment. The system always records the full entry amount as a single bank_transaction row — never summing the sub-transaction amounts. This prevents accounting discrepancies when sub-transaction amounts are in different currencies or partially enriched.

Data-level enforcement:

  • Read: <Ntry><Amt> (parent entry amount only).
  • Guard: Sub-transaction <TxDtls><Amt> values are parsed for remittance display only and are never used to set bank_transaction.amount.
  • Write: Set bank_transaction.amount = <Ntry><Amt> value. Sub-transaction amounts may appear in the aggregated bank_transaction.remittance_info string for reference.

3.6 Bank Identity Validation at Upload ​

Business rule: The bank selected by the user at upload must match the bank that actually produced the file. Mismatching the bank would corrupt the source_bank_id attribution used for deduplication, grouping, and downstream cash matching.

Data-level enforcement:

  • Read: <BIC>, <Svr><FinInstnId><Nm>, and routing number patterns extracted from file content.
  • Guard: If detected bank identity does not match sourceBankId with HIGH confidence (reliable indicator signals), reject the upload with a bank mismatch error before any writes occur.
  • Write: On pass, set bank_file.source_bank_id = user-selected sourceBankId.

3.7 Auto-Grouping Triggered Only by CAMT.053 ​

Business rule: Grouping CAMT.052 (intraday) files under CAMT.053 (end-of-day) files is always initiated by the arrival of the end-of-day file. Uploading an intraday file alone never triggers grouping — the intraday file waits to be claimed by its corresponding end-of-day file.

Data-level enforcement:

  • Read: bank_file.file_type of the newly uploaded file.
  • Guard: Auto-grouping logic only executes if file_type = CAMT053.
  • Write: bank_file_group rows created only during CAMT.053 processing.

4. Field Mapping & Transformation ​

4.1 ISO 20022 XML → bank_transaction ​

The table below shows how ISO 20022 XML elements map to bank_transaction fields. All source paths are relative to the <Ntry> (Entry) element unless otherwise noted.

Source Table.FieldTarget Table.FieldTransform
<AcctSvcrRef>bank_transaction.bank_reference_idCopied as-is. Primary deduplication key.
<Ntry><Amt> (text value)bank_transaction.amountCopied as-is (lump-sum rule — never use <TxDtls><Amt>).
<Ntry><Amt @Ccy>bank_transaction.currencyCopied as-is.
<Ntry><BookgDt><Dt>bank_transaction.booking_dateParsed as date. Falls back to <GrpHdr><CreDtTm> date portion if absent.
<Ntry><ValDt><Dt>bank_transaction.value_dateParsed as date. Optional.
<Ntry><CdtDbtInd>bank_transaction.directionCRDT → CREDIT; DBIT → DEBIT.
<BkToCstmrStmt> root elementbank_transaction.statusBOOK.
<BkToCstmrAcctRpt> root elementbank_transaction.statusPDNG.
<BkTxCd><Prtry><Cd>bank_transaction.bank_transaction_codePreferred. Falls back to <BkTxCd><Domn><Fmly><Cd> if absent.
<BkTxCd><Prtry><Issr>bank_transaction.transaction_code_issuerCopied as-is.
<BkTxCd><Domn><Cd>bank_transaction.domain_codeCopied as-is.
<BkTxCd><Domn><Fmly><Cd>bank_transaction.family_codeCopied as-is.
<BkTxCd><Domn><Fmly><SubFmlyCd>bank_transaction.sub_family_codeCopied as-is.
<RvslInd> = truebank_transaction.is_reversalSet to 1. Gatekeeper filters these entries; only entries passing the filter and flagged by sub-family code RVSL/ARET reach the golden record as reversals.
—bank_transaction.reversal_statusPENDING when is_reversal = 1; null otherwise.
<NtryDtls><TxDtls><RmtInf><Ustrd>bank_transaction.remittance_infoAggregated from all <TxDtls> children, joined with |.
Parsed from <Ustrd>: text before DES:bank_transaction.payer_namePattern A: text before first DES: occurrence.
Parsed from <Ustrd>: after ID:bank_transaction.payer_idPattern A: digits after ID: token.
Parsed from <Ustrd>: INV \d{4}...bank_transaction.invoice_numberPattern B: INV followed by 4+ digits, optional -\d+.
<NtryDtls><TxDtls><Refs><EndToEndId>bank_transaction.end_to_end_idOnly stored if value is not all zeros (placeholder 0000000000 is ignored).
<RltdPties><Dbtr><Nm>bank_transaction.debtor_nameCopied as-is.
<RltdPties><Dbtr><Acct>bank_transaction.debtor_accountCopied as-is.
<Dbtr><Id><OrgId><Othr><Id>bank_transaction.debtor_org_idCopied as-is.
<RltdPties><Cdtr><Nm>bank_transaction.creditor_nameCopied as-is.
<RltdPties><Cdtr><Acct>bank_transaction.creditor_accountCopied as-is.
<RltdAgts><DbtrAgt><FinInstnId><Nm>bank_transaction.debtor_agent_nameCopied as-is.
<RltdAgts><CdtrAgt><FinInstnId><Nm>bank_transaction.creditor_agent_nameCopied as-is.
<TxId>bank_transaction.tx_idCopied as-is.
<Prtry><Tp>bank_transaction.entry_class_typeCopied as-is (e.g., ENTRY CLASS).
<Prtry><Ref>bank_transaction.entry_class_refCopied as-is (e.g., CCD, CTX).
<Purp><Prtry>bank_transaction.purposeCopied as-is.
<AmtDtls><TxAmt><Amt>bank_transaction.original_amountFX original amount (display/audit only; never used as the transaction amount).
<AmtDtls><TxAmt><Amt @Ccy>bank_transaction.original_currencyCurrency of the original amount before conversion.
<AmtDtls><TxAmt><CcyXchg><XchgRate>bank_transaction.exchange_ratedecimal(15,6).
<Avlbty><Dt><NbOfDays>bank_transaction.availability_days0 = same-day availability.
<NtryRef>bank_transaction.raw_entry_refPreserved for reference only. Not used for deduplication.
Detected from BIC/routingbank_transaction.source_bankBOFA, JPM, or CNB.
<Ntry><BookgDt> (full datetime)bank_transaction.booking_date_timeISO datetime string preserved alongside the date-only booking_date.

4.2 bank_transaction → cash_receipt (Cash Receipt Generation) ​

Source Table.FieldTarget Table.FieldTransform
bank_transaction.statuscash_receipt.entry_statusCopied as-is (PDNG or BOOK).
bank_transaction.bank_reference_idcash_receipt.bank_ref_idCopied as-is.
bank_transaction.bank_transaction_idcash_receipt.bank_transaction_idCopied as-is (FK link).
bank_transaction.booking_datecash_receipt.booking_dateCopied as-is.
bank_transaction.booking_datecash_receipt.deposit_dateSame value as booking_date on creation.
bank_transaction.remittance_infocash_receipt.remittance_infoCopied as-is.
bank_transaction.bank_account_idcash_receipt.bank_account_idCopied as-is.
bank_transaction.bank_reference_idcash_receipt.cash_receipt_refCopied as-is (receipt reference = bank reference on creation).
bank_file.file_namecash_receipt.filenameName of the bank file that contained this transaction.
bank_transaction.amountcash_receipt.original_receipt_amtCopied as-is (decimal(15,2)).
bank_transaction.amountcash_receipt.receipt_amtSame as original_receipt_amt (no FX conversion applied at ingestion time).
bank_transaction.amountcash_receipt.net_receipt_amtSame as receipt_amt on creation (no adjustments yet).
bank_transaction.currencycash_receipt.original_currency_cdCopied as-is.
bank_transaction.currencycash_receipt.currency_cdCopied as-is.
—cash_receipt.posting_status_cdDefaulted to U (Unposted).

5. Cross-References ​

DocumentRelationship
Bank Ingestion Data ModelAll tables, fields, status codes, and unique constraints referenced in this document are defined there.
Cash Receipts Data Modelcash_receipt.bank_transaction_id → bank_transaction.bank_transaction_id; cash_receipt.bank_ref_id + cash_receipt.bank_account_id form the composite link back to the bank transaction.
Cash Receipts ProceduresProcedure 2.7 (Generate Cash Receipt) triggers the default split creation described in full in the Cash Receipts Procedures document.
Worksheets ProceduresWorksheets are created downstream of splits, which are created downstream of cash receipts, which are created by procedure 2.7. No direct FK from worksheets to bank ingestion tables.
Accounting Data Modelbank_file_balance records (closing booked balance CLBD) are used as reconciliation anchors against GL cash account balances.
Parties Data Modelbank_account (FK target throughout this domain) is a shared reference table; party_bank_account links parties to their bank accounts for outbound payments.

Confidential. For internal use only.

Copyright © 2025, United Talent Agency. All rights reserved.