Bank Ingestion Queries

1. Executive Summary

This document catalogs every query operation in the Bank Ingestion domain — the reads, searches, upserts, and computed aggregations that drive bank file processing, transaction management, and daily cash position reporting within the Client Payments system.

The bank ingestion query surface falls into four functional areas:

File Ingestion Pipeline — Deduplication checks, file creation, metadata extraction (headers, balances, summaries), and file-to-account linking. These queries execute sequentially during file upload and parsing.
Transaction Upsert (Golden Record) — The core read-then-write cycle that enforces the (bank_account_id, bank_reference_id) composite unique key. Each incoming transaction is looked up first; if found, it is updated in place (status promotion, latest file reference); if not found, it is inserted as a new record. Every mutation produces a history record.
Search & Retrieval — Paginated and filtered reads for the bank files list, transaction browser, transaction detail, and transaction history timeline. These queries join across bank_transaction, bank_file, bank_account, and cash_receipt to produce enriched display records.
Dashboard Aggregation — Daily cash position summaries, account-level balance retrieval, file grouping (CAMT.053 with nested CAMT.052 children), and credit transaction status breakdowns. These queries combine multiple repository reads with in-memory aggregation.

This document references tables defined in the Bank Ingestion Data Model. Readers should be familiar with the golden record pattern, file-level vs. transaction-level deduplication, and the ISO 20022 status codes described there.

Tables in scope:

Table	Primary Role in Queries
`bank_file`	File metadata lookup, dedup checks, status updates, listing
`bank_file_account`	Junction queries for multi-account file resolution
`bank_file_header`	Header metadata retrieval for audit and grouping
`bank_file_balance`	Balance retrieval for daily cash position reporting
`bank_file_summary`	Aggregated credit/debit totals from parsed files
`bank_file_group`	Parent/child file grouping for CAMT.053/CAMT.052
`bank_transaction`	Golden record CRUD, search, enriched retrieval
`bank_transaction_history`	Audit timeline for transaction state changes

External tables joined in read queries: bank_account, bank (reference), cash_receipt (downstream linkage).

2. Key Queries

2.1 File Ingestion Pipeline

2.1.1 Check for Duplicate File

Operation: checkForDuplicate

Input Parameters:

contentHash: varchar(64) (optional) — bank_file.content_hash; SHA-256 hash of file content
sourceBankId: varchar(20) (optional) — bank_file.source_bank_id; bank identifier (e.g., BOFA)
messageId: varchar(100) (optional) — bank_file.message_id; from <MsgId> in XML group header

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file`	—	—	—	Single-table scan for hash or message ID match

Filters:

Strategy 1 (content hash): bank_file.content_hash = :contentHash — exact content duplicate detection; limit 1 row
Strategy 2 (semantic, executed only if Strategy 1 yields no match and both identifiers are provided): bank_file.source_bank_id = :sourceBankId AND bank_file.message_id = :messageId — limit 1 row

Computed Values:

isDuplicate: true if any strategy matches; false otherwise
reason: 'HASH' for Strategy 1 match; 'MESSAGE_ID' for Strategy 2 match; omitted when not a duplicate
originalFileId: bank_file.bank_file_id of the previously uploaded file when duplicate detected

Returns: One result object indicating whether the file is a duplicate, which strategy triggered, and the original file ID; if neither strategy matches, returns { isDuplicate: false }.

IMPORTANT

Duplicate files are rejected at upload time — no transactions are parsed or upserted. The unique index uq_bank_file_dedup on (source_bank_id, message_id) provides the database-level constraint backing Strategy 2.

2.1.2 Create Bank File Record

Operation: createBankFile

Input Parameters:

sourceBankId: varchar(20) (optional) — bank.bank_id; FK identifying the issuing bank
fileName: text (required) — original uploaded file name
fileType: varchar(20) (required) — CAMT052 or CAMT053
xsdName: varchar(50) (optional) — schema version (e.g., camt.053.001.02)
messageId: varchar(100) (optional) — from <MsgId>
contentHash: varchar(64) (optional) — SHA-256 of raw content
electronicSeqNb: integer (optional) — from <ElctrncSeqNb>
statementCurrency: varchar(10) (optional) — currency from statement level
rawContent: text (required) — full XML content preserved for audit and reprocessing
userId: varchar(100) (required) — audit: who uploaded

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file`	—	—	—	INSERT target

Filters:

None (insert operation)

Computed Values:

bank_file.status: defaulted to 'PENDING' on every new file insert
bank_file.is_duplicate: defaulted to false
bank_file.upload_date: set to now()

Returns: The newly created bank_file row with all columns via RETURNING *.

Variant: Create Duplicate Bank File

Operation: createDuplicateBankFile

Additional parameters: originalFileId: integer (required) — bank_file.bank_file_id of the previously uploaded original.

Differences from base: Sets bank_file.status = 'DUPLICATE', bank_file.is_duplicate = true, bank_file.duplicate_of_file_id = :originalFileId. All other fields identical to 2.1.2.

2.1.3 Create Bank File Account Link

Operation: createBankFileAccount

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id
bankAccountId: integer (required) — bank_account.bank_account_id
statementId: varchar(100) (optional) — from <StmtId>
statementFromDate: timestamp (optional) — statement period start
statementToDate: timestamp (optional) — statement period end
accountCurrency: varchar(10) (optional) — account currency within the file
transactionCount: integer (optional) — transaction count for this account in the file
userId: varchar(100) (required) — audit

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_account`	—	—	—	INSERT target; links file to one bank account

Filters:

None (insert operation)

Computed Values:

None

Returns: Void; no return value.

NOTE

Unique index uq_bank_file_account on (bank_file_id, bank_account_id) prevents duplicate links. Called once per account discovered in a multi-account file.

2.1.4 Create Bank File Header

Operation: createBankFileHeader

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id
messageId: varchar(100) (optional) — from <GrpHdr><MsgId>
creationDateTime: timestamp (optional) — from <GrpHdr><CreDtTm>
statementId: varchar(100) (optional) — from <StmtId> or <RptId>
electronicSequenceNumber: integer (optional) — from <ElctrncSeqNb>
legalSequenceNumber: integer (optional) — from <LglSeqNb>
statementFromDate: timestamp (optional) — statement period start
statementToDate: timestamp (optional) — statement period end
accountIban: varchar(34) (optional) — from <Acct><Id><IBAN>
accountOtherId: varchar(100) (optional) — from <Acct><Id><Othr><Id>
accountCurrency: varchar(10) (optional) — from <Acct><Ccy>
accountOwnerName: varchar(255) (optional) — from <Acct><Ownr><Nm>
servicerBic: varchar(11) (optional) — from <Svr><FinInstnId><BIC>
servicerName: varchar(255) (optional) — from <Svr><FinInstnId><Nm>
additionalInfo: text (optional) — from <AddtlInf>
userId: varchar(100) (required) — audit

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_header`	—	—	—	INSERT target; one row per file for audit

Filters:

None (insert operation)

Computed Values:

None

Returns: Void; no return value.

2.1.5 Create Bank File Balances

Operation: createBankFileBalances

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id; applies to all records in the batch
balances: array (required) — each element contains: bankAccountId (integer, optional), balanceType (varchar(10), required; e.g., OPBD, CLBD), amount (decimal(15,2), required), currency (varchar(10), required), creditDebitIndicator (varchar(10), optional; CRDT or DBIT), balanceDate (timestamp, optional), balanceDateTime (varchar(50), optional), availabilityDays (integer, optional), accountIban (varchar(34), optional), accountOtherId (varchar(100), optional), userId (varchar(100), required)

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_balance`	—	—	—	Batch INSERT target; one row per `<Bal>` element in the file

Filters:

None (batch insert operation)

Computed Values:

None

Returns: Void; no return value.

NOTE

A single-record variant createBankFileBalance is also available with the same parameters for inserting one balance at a time.

2.1.6 Create Bank File Summary

Operation: createBankFileSummary

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id
totalCreditEntries: integer (optional) — count of credit entries from <TxsSummry>
totalCreditAmount: decimal(15,2) (optional) — sum of credit amounts from <TxsSummry>
totalDebitEntries: integer (optional) — count of debit entries
totalDebitAmount: decimal(15,2) (optional) — sum of debit amounts
netEntryAmount: decimal(15,2) (optional) — credits minus debits as reported by the bank
userId: varchar(100) (required) — audit

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_summary`	—	—	—	INSERT target; one row per file capturing the `<TxsSummry>` block

Filters:

None (insert operation)

Computed Values:

None

Returns: Void; no return value.

2.2 Transaction Upsert (Golden Record)

2.2.1 Upsert Normalized Transactions

Operation: upsertNormalizedTransactions

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id; source file for this batch
bankAccountId: integer (required) — bank_account.bank_account_id; target bank account
transactions: array (required) — normalized transaction objects (one per parsed <Ntry> that passed the gatekeeper filter)
userId: varchar(100) (required) — audit

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_transaction`	—	—	—	Lookup by composite key; then UPDATE or INSERT target
`bank_transaction_history`	—	—	—	INSERT target; one history record per transaction touched

Filters:

Lookup step: bank_transaction.bank_account_id = :bankAccountId AND bank_transaction.bank_reference_id = :transactionId; limit 1 row
Reversal Strategy 1 (when isReversal = true): bank_transaction.bank_account_id = :bankAccountId AND bank_transaction.end_to_end_id = :endToEndId AND bank_transaction.direction = opposite direction AND bank_transaction.is_reversal = 0; order created_dt DESC; limit 1 row
Reversal Strategy 2 (fallback when Strategy 1 finds no match): bank_transaction.bank_account_id = :bankAccountId AND bank_transaction.amount = :amount AND bank_transaction.currency = :currency AND bank_transaction.direction = opposite direction AND bank_transaction.is_reversal = 0; order created_dt DESC; limit 1 row

Computed Values:

bank_transaction.status: 'PDNG' when source is CAMT.052 (intraday); 'BOOK' when source is CAMT.053 (end-of-day)
bank_transaction.reversal_status: 'PENDING' when isReversal = true; null for non-reversals
bank_transaction.direction: mapped from XML <CdtDbtInd>: CRDT → 'CREDIT', DBIT → 'DEBIT'
bank_transaction.reversal_of_transaction_id: set to the matched bank_transaction_id from Strategy 1 or Strategy 2 when a reversal original is found; null if no match
bank_transaction_history.action_type: 'CREATE' for new inserts; 'UPDATE' for existing record updates
bank_transaction_history.from_status: null for CREATE actions; previous bank_transaction.status for UPDATE actions

Returns: One result object with newCount (integer — number of transactions inserted) and updatedCount (integer — number of existing transactions updated).

IMPORTANT

On UPDATE, the following fields are refreshed from the newer file: status, booking_date, value_date, bank_file_id, debtor_name (if enriched), end_to_end_id (if enriched). All other fields are preserved from the original INSERT. The bank_file_id on bank_transaction always points to the latest source file; the full file history is preserved in bank_transaction_history.

NOTE

A legacy variant upsertTransactions (accepting GenericBankTransactionDTO objects) performs the same golden-record upsert but populates only a subset of fields. It exists for backward compatibility with the older adapter. New ingestion uses upsertNormalizedTransactions.

2.2.2 Update Bank File Status

Operation: updateBankFileStatus

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id; target file
status: varchar(50) (required) — new status ('PROCESSED', 'ERROR', or 'DUPLICATE')
transactionCount: integer (required) — count of transactions parsed and accepted by the gatekeeper filter
userId: varchar(100) (required) — audit

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file`	—	—	—	UPDATE target; called after all transaction upserts complete

Filters:

bank_file.bank_file_id = :bankFileId

Computed Values:

bank_file.updated_dt: set to now()

Returns: Void; no return value.

2.3 Search & Retrieval

2.3.1 Get All Bank Files

Operation: getAllBankFiles

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file`	—	—	—	Full table scan; no joins required

Filters:

None; all files are returned

Computed Values:

None

Returns: Array of all bank_file rows, all columns mapped to DTO, ordered by bank_file.upload_date descending.

2.3.2 Get Bank File by ID

Operation: getBankFileById

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id; target file primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file`	—	—	—	Single-row lookup by primary key

Filters:

bank_file.bank_file_id = :bankFileId; limit 1 row

Computed Values:

None

Returns: One bank_file row mapped to DTO, or null if not found.

2.3.3 Get Raw XML by File ID

Operation: getRawXmlByFileId

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id; target file primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file`	—	—	—	Narrow column selection for efficiency; only `raw_content` is fetched

Filters:

bank_file.bank_file_id = :bankFileId; limit 1 row

Computed Values:

None

Returns: The bank_file.raw_content string (original XML), or null if the file does not exist.

2.3.4 Get All Transactions (Enriched)

Operation: getAllTransactions

Input Parameters:

None

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_transaction`	LEFT	`bank_file`	`bank_transaction.bank_file_id = bank_file.bank_file_id`	Adds source file name and type for display
`bank_transaction`	LEFT	`bank_account`	`bank_transaction.bank_account_id = bank_account.bank_account_id`	Adds account name and number for display
`bank_transaction`	LEFT	`cash_receipt`	`bank_transaction.bank_account_id = cash_receipt.bank_account_id AND bank_transaction.bank_reference_id = cash_receipt.bank_ref_id`	Determines whether a cash receipt has been created from this transaction

Filters:

None; all transactions are returned

Computed Values:

None (all selected columns are direct field reads)

Returns: Array of all bank_transaction rows enriched with bank_file.file_name, bank_file.file_type, bank_account.bank_account_name, bank_account.bank_account_no, and cash_receipt.cash_receipt_id (null when unmatched), ordered by bank_transaction.created_dt descending.

NOTE

The composite join condition on cash_receipt (bank_account_id + bank_ref_id) determines whether a cash receipt has been created from the transaction. The presence of a non-null cash_receipt_id in the result drives the "Generate Receipt" button state in the UI.

2.3.5 Search Transactions

Operation: searchTransactions

Input Parameters:

bankReferenceId: varchar(100) (optional) — bank_transaction.bank_reference_id; partial match using ILIKE '%value%'
status: varchar(20) (optional) — bank_transaction.status; exact match (BOOK, PDNG, INFO, FUTR)
currency: varchar(10) (optional) — bank_transaction.currency; exact match
bankFileId: integer (optional) — bank_transaction.bank_file_id; exact match; filters to a specific file
bankAccountId: integer (optional) — bank_transaction.bank_account_id; exact match; filters to a specific account

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_transaction`	LEFT	`bank_file`	`bank_transaction.bank_file_id = bank_file.bank_file_id`	Adds source file name and type for display
`bank_transaction`	LEFT	`bank_account`	`bank_transaction.bank_account_id = bank_account.bank_account_id`	Adds account name and number for display
`bank_transaction`	LEFT	`cash_receipt`	`bank_transaction.bank_account_id = cash_receipt.bank_account_id AND bank_transaction.bank_reference_id = cash_receipt.bank_ref_id`	Determines cash receipt linkage

Filters:

bank_transaction.bank_reference_id ILIKE '%:bankReferenceId%' — if bankReferenceId provided
bank_transaction.status = :status — if status provided
bank_transaction.currency = :currency — if currency provided
bank_transaction.bank_file_id = :bankFileId — if bankFileId provided
bank_transaction.bank_account_id = :bankAccountId — if bankAccountId provided
All active filters are combined with AND logic

Computed Values:

None

Returns: Array of enriched bank_transaction rows matching all provided filters (same shape as getAllTransactions), ordered by bank_transaction.created_dt descending.

2.3.6 Get Transaction History

Operation: getTransactionHistory

Input Parameters:

bankTransactionId: integer (required) — bank_transaction.bank_transaction_id; target transaction primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_transaction_history`	LEFT	`bank_file`	`bank_transaction_history.bank_file_id = bank_file.bank_file_id`	Adds source file name and type to each history record

Filters:

bank_transaction_history.bank_transaction_id = :bankTransactionId

Computed Values:

None

Returns: Array of bank_transaction_history rows enriched with bank_file.file_name and bank_file.file_type, ordered by bank_transaction_history.created_dt descending, showing every state change from creation through all subsequent updates.

2.3.7 Get Bank File Header

Operation: getHeaderByFileId

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id; target file primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_header`	—	—	—	Single-row lookup; one header record per file

Filters:

bank_file_header.bank_file_id = :bankFileId; limit 1 row

Computed Values:

None

Returns: One bank_file_header row with all header fields (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), or null if no header record exists.

2.3.8 Get Bank File Balances

Operation: getBalancesByFileId

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id; target file primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_balance`	—	—	—	Single-table scan for all balances in the file

Filters:

bank_file_balance.bank_file_id = :bankFileId

Computed Values:

None

Returns: Array of balance rows for the file, each with balance_type, amount, currency, credit_debit_indicator, balance_date, and balance_date_time.

Variant: Get Bank File Balances by Account

Operation: getBalancesByFileIdAndAccount

Additional parameters: bankAccountId: integer (optional), accountNo: varchar(100) (optional), iban: varchar(34) (optional) — at least one account identifier should be provided.

Additional filters (OR logic): bank_file_balance.bank_account_id = :bankAccountId OR bank_file_balance.account_other_id = :accountNo OR bank_file_balance.account_iban = :iban. The file filter is always applied; the account conditions are combined with OR to accommodate the inconsistency between how banks report account numbers in CAMT files.

All other aspects identical to 2.3.8.

2.3.9 Get Bank File Summary

Operation: getBankFileSummary

Input Parameters:

bankFileId: integer (required) — bank_file.bank_file_id; target file primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_summary`	—	—	—	Single-row lookup; one summary record per file

Filters:

bank_file_summary.bank_file_id = :bankFileId; limit 1 row

Computed Values:

None

Returns: One bank_file_summary row with total_credit_entries, total_credit_amount, total_debit_entries, total_debit_amount, and net_entry_amount, or null if no summary record exists.

2.4 Reversal Management

2.4.1 Accept Reversal

Operation: acceptReversal

Input Parameters:

bankTransactionId: integer (required) — bank_transaction.bank_transaction_id; target transaction
acceptedBy: varchar(100) (required) — identifier of the user performing acceptance

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_transaction`	—	—	—	Validation read (Step 1), then UPDATE target (Step 2)

Filters:

Step 1 validation read: bank_transaction.bank_transaction_id = :bankTransactionId; limit 1 row; returns is_reversal and reversal_status for guard checks
Step 2 update: bank_transaction.bank_transaction_id = :bankTransactionId; only executed if is_reversal = 1 and reversal_status ≠ 'ACCEPTED'

Computed Values:

bank_transaction.reversal_status: set to 'ACCEPTED'
bank_transaction.reversal_accepted_at: set to now()
bank_transaction.reversal_accepted_by: set to :acceptedBy
bank_transaction.updated_dt: set to now()

Returns: One result object { success: true } on success, or { success: false, error: '...' } if the transaction is not found, is not a reversal, or has already been accepted.

2.4.2 Reject Reversal

Operation: rejectReversal

Input Parameters:

bankTransactionId: integer (required) — bank_transaction.bank_transaction_id; target transaction
rejectedBy: varchar(100) (required) — identifier of the user performing rejection

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_transaction`	—	—	—	Validation read (Step 1), then UPDATE target (Step 2)

Filters:

Step 1 validation read: bank_transaction.bank_transaction_id = :bankTransactionId; limit 1 row; returns is_reversal and reversal_status for guard checks
Step 2 update: bank_transaction.bank_transaction_id = :bankTransactionId; only executed if is_reversal = 1 and reversal_status = 'PENDING'

Computed Values:

bank_transaction.reversal_status: set to 'REJECTED'
bank_transaction.updated_dt: set to now()

Returns: One result object { success: true } on success, or { success: false, error: '...' } if the transaction is not found, is not a reversal, or is not in PENDING status.

2.5 File Grouping

2.5.1 Create Bank File Group

Operation: createBankFileGroup

Input Parameters:

parentFileId: integer (required) — bank_file.bank_file_id; the CAMT.053 end-of-day parent file
childFileId: integer (required) — bank_file.bank_file_id; the CAMT.052 intraday child file
bankAccountId: integer (optional) — bank_account.bank_account_id; scopes the grouping to a specific account
statementDate: timestamp (optional) — grouping date derived from file content
matchingCriteria: varchar(50) (required) — 'AUTO' (system-generated) or 'MANUAL' (user-initiated)
userId: varchar(100) (required) — audit

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_group`	—	—	—	INSERT target; links intraday child file to end-of-day parent file

Filters:

None (insert operation)

Computed Values:

None

Returns: Void; no return value.

NOTE

Unique index uq_bank_file_group on (parent_file_id, child_file_id, bank_account_id) prevents duplicate groupings.

2.5.2 Get File Group by Parent

Operation: getFileGroupByParent

Input Parameters:

parentFileId: integer (required) — bank_file.bank_file_id; the CAMT.053 parent file primary key

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_group`	LEFT	`bank_file`	`bank_file_group.child_file_id = bank_file.bank_file_id`	Adds child file metadata (name, type, status) for display

Filters:

bank_file_group.parent_file_id = :parentFileId

Computed Values:

None

Returns: Array of group records with bank_file_group_id, parent_file_id, child_file_id, bank_account_id, statement_date, matching_criteria, and child file fields aliased as child_file_name, child_file_type, child_status.

2.5.3 Get File Groups by Parent IDs (Batch)

Operation: getFileGroupsByParentIds

Input Parameters:

parentFileIds: integer[] (required) — array of bank_file.bank_file_id values for CAMT.053 parent files

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_group`	—	—	—	Single-table scan with IN filter across multiple parent IDs

Filters:

bank_file_group.parent_file_id IN (:parentFileIds[])
Short-circuits and returns an empty array immediately if parentFileIds is empty

Computed Values:

None

Returns: Array of group records with bank_file_group_id, parent_file_id, child_file_id, bank_account_id, and statement_date; empty array if parentFileIds is empty.

2.5.4 Find Matching Intraday Files

Operation: findMatchingIntradayFiles

Input Parameters:

sourceBankId: varchar(20) (required) — bank_file.source_bank_id; bank identifier (e.g., BOFA)
bankAccountId: integer (required) — bank_account.bank_account_id; target account
statementDate: timestamp (required) — date used to scope the grouping search

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file`	INNER	`bank_file_account`	`bank_file.bank_file_id = bank_file_account.bank_file_id`	Restricts to files linked to the specified bank account

Filters:

bank_file.source_bank_id = :sourceBankId
bank_file.file_type = 'CAMT052' — intraday files only
bank_file_account.bank_account_id = :bankAccountId
bank_file.is_duplicate = false — excludes duplicate files

Computed Values:

None

Returns: Array of matching CAMT.052 file records with bank_file_id, file_name, and upload_date; the auto-grouping caller then filters out files already linked to a parent before creating new group records.

2.5.5 Get Files Grouped by Date for Account

Operation: getFilesGroupedByDate

Input Parameters:

bankAccountId: integer (required) — bank_account.bank_account_id; target account
startDate: timestamp (required) — date range start for file filtering
endDate: timestamp (required) — date range end for file filtering

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_file_account`	—	—	—	Step 1: finds all `bank_file_id` values linked to the account
`bank_file`	—	—	—	Step 2: fetches full file records for those IDs
`bank_file_group`	—	—	—	Step 3: fetches group records for CAMT.053 parent IDs (via `getFileGroupsByParentIds`)

Filters:

Step 1: bank_file_account.bank_account_id = :bankAccountId
Step 2: bank_file.bank_file_id IN (IDs from Step 1); ordered by bank_file.upload_date DESC
Step 3: bank_file_group.parent_file_id IN (CAMT.053 IDs from Step 2)

Computed Values:

In-memory assembly: each CAMT.053 file becomes a parent node; its children are resolved by matching bank_file_group.child_file_id against Step 2 results; ungrouped CAMT.052 files (not referenced as any group's child) are returned as standalone parent nodes with empty child_files arrays

Returns: Array of hierarchical file group objects { parentFile, childFiles[], statementDate }, with all CAMT.053 files as parents and their grouped CAMT.052 files as children.

2.6 Dashboard Aggregation

2.6.1 Daily Cash Summary

Operation: getDailyCashSummary

Input Parameters:

date: string ISO date format (required) — target date (e.g., '2026-01-15')

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_transaction`	LEFT	`bank_file`	`bank_transaction.bank_file_id = bank_file.bank_file_id`	Via `getAllTransactions`; enriches transactions with file context
`bank_transaction`	LEFT	`bank_account`	`bank_transaction.bank_account_id = bank_account.bank_account_id`	Via `getAllTransactions`; enriches with account details
`bank_transaction`	LEFT	`cash_receipt`	composite on `bank_account_id + bank_ref_id`	Via `getAllTransactions`; determines receipt linkage
`bank_file_balance`	—	—	—	Via `getBalancesByFileIdAndAccount`; retrieves opening/closing balances per account

Filters:

Transactions filtered to target date range: bank_transaction.booking_date between start-of-day and end-of-day for :date
Per-account: filtered to bank_transaction.bank_account_id for each active account
Pending reversals excluded from aggregation: bank_transaction.is_reversal = 1 AND (bank_transaction.reversal_status = 'PENDING' OR bank_transaction.reversal_status IS NULL)

Computed Values:

opening_balance: from OPBD balance record; sign-adjusted using credit_debit_indicator (negative when 'DBIT')
closing_balance: from CLBD balance record; falls back to ITBD (interim booked) for intraday-only scenarios; sign-adjusted
net_change: closing_balance - opening_balance
total_credits: sum of bank_transaction.amount where direction = 'CREDIT'
total_debits: sum of bank_transaction.amount where direction = 'DEBIT'
credit_count, debit_count: count of transactions by direction
transactions_by_status: per-status breakdown of credit transaction counts and amounts (BOOK, PDNG, INFO, FUTR)
has_pending_items: true if any PDNG status transactions exist for this account
by_currency: per-currency breakdown of credits and debits for multi-currency accounts
Cross-account totals: sums of all per-account values for opening_balance, closing_balance, net_change, total_credits, total_debits, credit_count, debit_count, file_count, transaction_count, pending_count, pending_amount

Returns: One summary object with the target date, an accounts array (one entry per active bank account), and a totals object aggregating values across all accounts.

2.6.2 Credit Transaction Status

Operation: getCreditTransactionStatus

Input Parameters:

date: string ISO date format (required) — target date
bankAccountId: integer (optional) — bank_account.bank_account_id; narrows results to a specific account

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_transaction`	LEFT	`bank_file`	`bank_transaction.bank_file_id = bank_file.bank_file_id`	Via `getAllTransactions`
`bank_transaction`	LEFT	`bank_account`	`bank_transaction.bank_account_id = bank_account.bank_account_id`	Via `getAllTransactions`
`bank_transaction`	LEFT	`cash_receipt`	composite on `bank_account_id + bank_ref_id`	Via `getAllTransactions`; presence of `cash_receipt_id` determines receipt linkage

Filters:

bank_transaction.booking_date within target date range
bank_transaction.bank_account_id = :bankAccountId — if provided
bank_transaction.direction = 'CREDIT' — credit transactions only

Computed Values:

Per-currency aggregates: total_credits (count and sum of all credit transactions), with_receipt (count and sum where cash_receipt_id IS NOT NULL), booked_no_receipt (count and sum where status = 'BOOK' AND cash_receipt_id IS NULL), pending_no_receipt (count and sum where status = 'PDNG' AND cash_receipt_id IS NULL)
Cross-currency totals: same four aggregates summed across all currencies
currency: primary currency (most common in the result set)

Returns: One reconciliation status object with cross-currency aggregates (total_credits, with_receipt, booked_no_receipt, pending_no_receipt), a currency field, and a by_currency array for per-currency breakdowns.

2.6.3 File Groups for Account (Dashboard)

Operation: getFileGroupsForAccount

Input Parameters:

bankAccountId: integer (required) — bank_account.bank_account_id; target account
date: string ISO date format (required) — target date

Tables & Joins:

Base Table	Join	Joined Table	Condition	Purpose
`bank_transaction`	LEFT	`bank_file`	`bank_transaction.bank_file_id = bank_file.bank_file_id`	Via `getAllTransactions`; resolves file IDs relevant to this account and date
`bank_transaction`	LEFT	`bank_account`	`bank_transaction.bank_account_id = bank_account.bank_account_id`	Via `getAllTransactions`
`bank_transaction`	LEFT	`cash_receipt`	composite on `bank_account_id + bank_ref_id`	Via `getAllTransactions`
`bank_file_group`	—	—	—	Via `getFileGroupsByParentIds`; resolves CAMT.052 children for each CAMT.053 parent

Filters:

Transactions filtered to :bankAccountId and target date range by booking_date
File IDs resolved from the filtered transactions via bank_file_id
CAMT.053 file IDs used as parent IDs for group lookup

Computed Values:

In-memory assembly: CAMT.053 files become parent nodes with grouped CAMT.052 children; ungrouped CAMT.052 files appear as standalone parent nodes

Returns: Array of FileGroupData objects { parentFile, childFiles[], statementDate } representing the file hierarchy for the specified account and date.

3. Calculations & Formulas

3.1 Golden Record Status Resolution

text

IF source file_type = 'CAMT052' THEN bank_transaction.status = 'PDNG'
IF source file_type = 'CAMT053' THEN bank_transaction.status = 'BOOK'

Source: bank_file.file_type
Precision: N/A (status code assignment)
Example: A transaction first seen in an intraday CAMT.052 file is inserted as PDNG. When the end-of-day CAMT.053 file arrives with the same bank_reference_id, the record is updated in-place to BOOK.

3.2 Balance Sign Adjustment

text

signed_balance = IF bank_file_balance.credit_debit_indicator = 'DBIT'
                 THEN -1 * bank_file_balance.amount
                 ELSE bank_file_balance.amount

Source: bank_file_balance.amount, bank_file_balance.credit_debit_indicator
Precision: decimal(15,2)
Example: An opening balance of 1,000,000.00 with credit_debit_indicator = 'DBIT' yields a signed balance of -1,000,000.00.

3.3 Net Change Calculation

text

net_change = closing_balance - opening_balance

Source: bank_file_balance.amount where balance_type = 'CLBD' (or 'ITBD' fallback); bank_file_balance.amount where balance_type = 'OPBD'
Precision: decimal(15,2)
Fallback: When no CLBD record exists (intraday-only scenario), ITBD (interim booked) is used as the closing balance
Example: opening_balance = 5,000,000.00, closing_balance = 5,250,000.00 → net_change = 250,000.00

3.4 Reversal Direction Resolution

text

original_direction = IF reversal.direction = 'CREDIT'
                     THEN 'DEBIT'
                     ELSE 'CREDIT'

Source: bank_transaction.direction on the incoming reversal transaction
Precision: N/A (direction code assignment)
Example: A CREDIT reversal (ACH return) looks for its original as a DEBIT transaction on the same account with the same end_to_end_id.

3.5 Reversal Exclusion from Dashboard Aggregation

text

exclude_from_aggregation = bank_transaction.is_reversal = 1
                           AND (bank_transaction.reversal_status = 'PENDING'
                                OR bank_transaction.reversal_status IS NULL)

Source: bank_transaction.is_reversal, bank_transaction.reversal_status
Precision: N/A (boolean exclusion logic)
Note: The IS NULL check handles backward compatibility for transactions created before the reversal_status column was added.

3.6 File Deduplication Priority

text

1. content_hash match          → reason: 'HASH' (exact content duplicate)
2. source_bank_id + message_id → reason: 'MESSAGE_ID' (semantic duplicate)

Source: bank_file.content_hash, bank_file.source_bank_id, bank_file.message_id
Precision: N/A (deduplication logic)
Example: If Strategy 1 matches, Strategy 2 is not executed. A file flagged as a duplicate by either strategy is stored with status = 'DUPLICATE' and contributes no transactions to the golden record.

4. Cross-References

Document	Relationship
Bank Ingestion Data Model	Defines all table schemas, column types, constraints, indexes, status lifecycles, gatekeeper filtering rules, and code master values referenced by every query in this document
Cash Receipts Data Model	`bank_transaction.bank_reference_id` + `bank_transaction.bank_account_id` → `cash_receipt.bank_ref_id` + `cash_receipt.bank_account_id`; the enriched transaction queries join `cash_receipt` to determine reconciliation status
Cash Receipts Queries	Cash receipts are created from booked bank transactions; `bank_transaction.cash_receipt_status` tracks whether a receipt exists (`UNMATCHED`, `MATCHED`, `PARTIAL`)
Worksheets Data Model	Worksheets are created downstream of cash receipts, which originate from bank transactions; no direct query path exists from bank ingestion to worksheets
Accounting Data Model	`bank_file_balance` records (particularly `CLBD`) 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 Queries ​

1. Executive Summary ​

2. Key Queries ​

2.1 File Ingestion Pipeline ​

2.1.1 Check for Duplicate File ​

2.1.2 Create Bank File Record ​

Variant: Create Duplicate Bank File ​

2.1.3 Create Bank File Account Link ​

2.1.4 Create Bank File Header ​

2.1.5 Create Bank File Balances ​

2.1.6 Create Bank File Summary ​

2.2 Transaction Upsert (Golden Record) ​

2.2.1 Upsert Normalized Transactions ​

2.2.2 Update Bank File Status ​

2.3 Search & Retrieval ​

2.3.1 Get All Bank Files ​

2.3.2 Get Bank File by ID ​

2.3.3 Get Raw XML by File ID ​

2.3.4 Get All Transactions (Enriched) ​

2.3.5 Search Transactions ​

2.3.6 Get Transaction History ​

2.3.7 Get Bank File Header ​

2.3.8 Get Bank File Balances ​

Variant: Get Bank File Balances by Account ​

2.3.9 Get Bank File Summary ​

2.4 Reversal Management ​

2.4.1 Accept Reversal ​

2.4.2 Reject Reversal ​

2.5 File Grouping ​

2.5.1 Create Bank File Group ​

2.5.2 Get File Group by Parent ​

2.5.3 Get File Groups by Parent IDs (Batch) ​

2.5.4 Find Matching Intraday Files ​

2.5.5 Get Files Grouped by Date for Account ​

2.6 Dashboard Aggregation ​

2.6.1 Daily Cash Summary ​

2.6.2 Credit Transaction Status ​

2.6.3 File Groups for Account (Dashboard) ​

3. Calculations & Formulas ​

3.1 Golden Record Status Resolution ​

3.2 Balance Sign Adjustment ​

3.3 Net Change Calculation ​

3.4 Reversal Direction Resolution ​

3.5 Reversal Exclusion from Dashboard Aggregation ​

3.6 File Deduplication Priority ​

4. Cross-References ​

Bank Ingestion Queries

1. Executive Summary

2. Key Queries

2.1 File Ingestion Pipeline

2.1.1 Check for Duplicate File

2.1.2 Create Bank File Record

Variant: Create Duplicate Bank File

2.1.3 Create Bank File Account Link

2.1.4 Create Bank File Header

2.1.5 Create Bank File Balances

2.1.6 Create Bank File Summary

2.2 Transaction Upsert (Golden Record)

2.2.1 Upsert Normalized Transactions

2.2.2 Update Bank File Status

2.3 Search & Retrieval

2.3.1 Get All Bank Files

2.3.2 Get Bank File by ID

2.3.3 Get Raw XML by File ID

2.3.4 Get All Transactions (Enriched)

2.3.5 Search Transactions

2.3.6 Get Transaction History

2.3.7 Get Bank File Header

2.3.8 Get Bank File Balances

Variant: Get Bank File Balances by Account

2.3.9 Get Bank File Summary

2.4 Reversal Management

2.4.1 Accept Reversal

2.4.2 Reject Reversal

2.5 File Grouping

2.5.1 Create Bank File Group

2.5.2 Get File Group by Parent

2.5.3 Get File Groups by Parent IDs (Batch)

2.5.4 Find Matching Intraday Files

2.5.5 Get Files Grouped by Date for Account

2.6 Dashboard Aggregation

2.6.1 Daily Cash Summary

2.6.2 Credit Transaction Status

2.6.3 File Groups for Account (Dashboard)

3. Calculations & Formulas

3.1 Golden Record Status Resolution

3.2 Balance Sign Adjustment

3.3 Net Change Calculation

3.4 Reversal Direction Resolution

3.5 Reversal Exclusion from Dashboard Aggregation

3.6 File Deduplication Priority

4. Cross-References