Cash Receipts Data Model

1. Executive Summary

The cash receipts data model captures the ingestion, tracking, and subdivision of payments received from buyers. It is the entry point for all cash into the Client Processing system -- every dollar that arrives at a UTA bank account, whether via wire transfer, ACH, check, or manual entry, is recorded as a cash_receipt and flows through this model before it can be applied to receivables.

This document covers four tables:

Table	Role
cash_receipt	Header record for each payment received
cash_receipt_split	Subdivision of a receipt for parallel processing
cash_receipt_reference	Informational tags linking a split to clients, deals, buyers
cash_receipt_adjustment	Amendments to a receipt's net amount after initial capture

Not covered: Worksheets (cash_receipt_worksheet), applications (cash_receipt_application), payouts (cash_receipt_payout), client ledger entries (cash_receipt_client_ledger), and bank transaction ingestion tables (bank_transaction, bank_statement_file). These are documented in their own foundation pages.

Position in the financial flow:

Bank deposit arrives
  --> cash_receipt created (manual or bank file import)
    --> cash_receipt_adjustment (optional: bank fees, corrections)
      --> cash_receipt_split (auto-created 1:1, additional splits by user)
        --> cash_receipt_reference (informational: who is this money from/for?)
          --> cash_receipt_worksheet (downstream: apply cash to receivables)

IMPORTANT

A cash_receipt is never applied directly to receivables. All applications flow through cash_receipt_split and its associated cash_receipt_worksheet. The split is the bridge between receipt and worksheet.

---

2. Data Model

Entity Relationship Diagram

erDiagram
    cash_receipt ||--o{ cash_receipt_split : "subdivided into"
    cash_receipt ||--o{ cash_receipt_adjustment : "adjusted by"
    cash_receipt_split ||--o{ cash_receipt_reference : "tagged with"
    cash_receipt_split ||--o| cash_receipt_worksheet : "processed via"
    cash_receipt }o--|| bank_account : "received at"
    cash_receipt_split }o--o| file_upload : "created from"

    cash_receipt {
        serial cash_receipt_id PK
        integer bank_account_id FK
        date deposit_date
        date booking_date
        varchar cash_receipt_ref
        decimal original_receipt_amt
        varchar original_currency_cd
        varchar currency_cd
        real fx_rate
        decimal receipt_amt
        decimal net_receipt_amt
        varchar posting_status_cd
        varchar receipt_type_cd
        varchar entry_status
        varchar bank_ref_id
        integer bank_transaction_id
        text remittance_info
        integer locked_by_user_id
    }

    cash_receipt_split {
        serial cash_receipt_split_id PK
        integer cash_receipt_id FK
        integer split_sequence
        decimal split_amt
        varchar split_status_cd
        boolean tax_warning_ind
        jsonb tax_warning_json
        integer parent_split_id
        integer source_file_upload_id FK
    }

    cash_receipt_reference {
        serial cash_receipt_reference_id PK
        integer cash_receipt_split_id FK
        varchar type_cd
        varchar ref_or_id
    }

    cash_receipt_adjustment {
        serial cash_receipt_adjustment_id PK
        integer cash_receipt_id FK
        varchar adjustment_type_cd
        decimal adjustment_amt
        varchar posting_status_cd
        date posting_dt
    }

2.1 cash_receipt

The top-level record for a payment received. Can be created manually or auto-generated from a bank transaction during statement file ingestion.

Field	Type	Required	Default	Description
cash_receipt_id	serial	Yes	Auto	Primary key. Referenced by splits and adjustments.
bank_account_id	integer	No	--	FK to bank_account. The UTA bank account where funds arrived.
deposit_date	date	No	--	Date the deposit was received. For bank-originated receipts, set to booking_date.
booking_date	date	No	--	Snapshot of the bank transaction's booking date. More accurate than deposit_date for bank-originated receipts; may be null for manual receipts.
cash_receipt_ref	varchar(150)	No	--	User-visible reference number. For bank-originated receipts, populated from bank_ref_id.
cash_receipt_comment	varchar(255)	No	--	Free-text comment. Editable in all statuses including Voided.
filename	varchar(255)	No	--	Bank batch file name that created this receipt. null for manual receipts.
original_receipt_amt	decimal(15,2)	No	--	Amount as received, in the original currency. Immutable after creation unless an amount adjustment overrides it.
original_currency_cd	varchar(50)	No	--	ISO currency code of the received payment (code_master type CURRENCY_CD).
currency_cd	varchar(50)	No	--	Working currency after conversion. Equals original_currency_cd when no FX conversion has been applied.
fx_rate	real	No	--	Exchange rate used for currency conversion. null when currency_cd equals original_currency_cd.
receipt_amt	decimal(15,2)	No	--	Receipt amount in the working currency. Equals original_receipt_amt * fx_rate (or original_receipt_amt when no conversion).
net_receipt_amt	decimal(15,2)	No	--	Net amount available for splitting. receipt_amt minus sum of cash_receipt_adjustment.adjustment_amt. Must equal the sum of all non-voided split amounts.
receipt_type_cd	varchar(50)	No	'NORMAL'	Discriminator for receipt purpose. See Code Master Values.
posting_status_cd	varchar(50)	No	'U'	GL posting lifecycle status. See Status Lifecycle.
posting_dt	date	No	--	Date the receipt was posted to the General Ledger. null while posting_status_cd is U.
entry_status	varchar(20)	No	--	Mirrors the originating bank transaction status. null for manual receipts. See Code Master Values for ISO 20022 entry status codes.
bank_ref_id	varchar(100)	No	--	Account Servicer Reference from the bank (AcctSvcrRef). Unique within a bank account; used for deduplication.
bank_transaction_id	integer	No	--	Optional FK to bank_transaction for audit trail back to the raw bank data.
remittance_info	text	No	--	Snapshot of the bank's remittance description. Stored at receipt creation for reference; not updated afterward.
locked_by_user_id	integer	No	--	FK to users. When set, only this user may modify the receipt. null means unlocked.

NOTE

Decimal fields use precision=15, scale=2, supporting amounts up to $9,999,999,999,999.99. The fx_rate field uses real (single-precision float) because exchange rates do not require accounting-grade precision.

2.2 cash_receipt_split

Subdivides a receipt into portions, each of which can be processed independently through its own worksheet. A default 1:1 split is auto-created when a receipt is created; users may create additional splits to handle multi-party or multi-deal receipts.

Field	Type	Required	Default	Description
cash_receipt_split_id	serial	Yes	Auto	Primary key.
cash_receipt_id	integer	Yes	--	FK to cash_receipt. Every split belongs to exactly one receipt.
split_sequence	integer	Yes	--	Ordinal position within the receipt (1, 2, 3...). Auto-assigned on creation.
split_amt	decimal(15,2)	Yes	--	Dollar amount allocated to this split. All splits for a receipt must sum to cash_receipt.net_receipt_amt.
split_status_cd	varchar(1)	No	'N'	Processing status. See Status Lifecycle.
notes	text	No	--	Agent notes about this split (e.g., which client or deal it relates to).
submitted_by_user_id	integer	No	--	FK to users. User who submitted this split for review.
submitted_dt	timestamp	No	--	When the split was submitted.
reviewed_by_user_id	integer	No	--	FK to users. User who reviewed (approved or returned) this split.
reviewed_dt	timestamp	No	--	When the split was reviewed.
review_notes	text	No	--	Reviewer's notes (especially for returns).
attachment_url	varchar(500)	No	--	S3 URL for a supporting document attached to this split.
attachment_name	varchar(255)	No	--	Original filename of the attachment, for display purposes.
tax_warning_ind	boolean	No	false	Flag indicating whether withholding tax warnings apply to this split. Set automatically by the tax service when a party reference is added.
tax_warning_json	jsonb	No	--	Structured tax warning payload: { warnings: [...], calculatedDt, partyId, jurisdiction }. null when tax_warning_ind is false.
source_file_upload_id	integer	No	--	FK to file_upload. Tracks which statement file created this split. null for manual or default splits.
parent_split_id	integer	No	--	Self-reference. If this split was created by re-splitting another split, records the parent for audit trail.

IMPORTANT

Each split can have at most one active worksheet. The split is the bridge between the receipt and the worksheet -- you cannot create a worksheet directly on a receipt; you must go through a split. When a receipt is created, a default split and its worksheet are auto-created in a single operation.

2.3 cash_receipt_reference

Informational tags that link a split to domain entities (clients, deals, buyers, departments). These references are not FK-enforced -- they store identifier values as strings and are used for search filtering, suggested receivable matching, and tax warning calculations.

Field	Type	Required	Default	Description
cash_receipt_reference_id	serial	Yes	Auto	Primary key.
cash_receipt_split_id	integer	No	--	FK to cash_receipt_split. References are linked to splits, not directly to receipts.
type_cd	varchar(50)	Yes	--	Reference type discriminator. See Code Master Values for supported types.
ref_or_id	varchar(255)	Yes	--	The actual identifier value. For ID-based types (CLIENT_ID, DEAL_ID), this is the numeric ID stored as a string. For reference-based types (PAYMENT_TERM_REF, SALES_ITEM_REF), this is the reference string.

WARNING

References are informational only. The ref_or_id field is not a foreign key and is not validated against the referenced entity at the database level. The application layer resolves and validates references when they are used for receivable search or tax calculations. Do not use these references as the source of truth for tax calculations -- tax is derived from the billing item data on the worksheet, not from split references.

2.4 cash_receipt_adjustment

Records amendments to a receipt's net amount after initial capture. Common use cases include bank fees, wire charges, and corrective adjustments. Each adjustment modifies cash_receipt.net_receipt_amt.

Field	Type	Required	Default	Description
cash_receipt_adjustment_id	serial	Yes	Auto	Primary key.
cash_receipt_id	integer	Yes	--	FK to cash_receipt. The receipt being adjusted.
adjustment_type_cd	varchar(10)	No	'ADJ'	Type discriminator: ADJ (general adjustment) or TR (transfer).
adjustment_amt	decimal(15,2)	Yes	--	Adjustment amount. Positive values increase the net; negative values decrease it.
comment	varchar(255)	No	--	Explanation for the adjustment.
posting_status_cd	varchar(50)	No	'U'	GL posting status. Follows the same POSTING_STATUS_CD lifecycle as the parent receipt.
posting_dt	date	No	--	Date the adjustment was posted to the GL.

---

3. Status Lifecycle

3.1 cash_receipt.posting_status_cd -- GL Posting Status

The receipt's GL posting status controls what operations are permitted on the receipt.

Status	Code	Description	Allowed Transitions
Unposted	U	Receipt is staged for GL posting. All fields are editable (subject to field-level rules).	P, V
Posted	P	Receipt has been posted to the General Ledger. Only cash_receipt_comment and net_receipt_amt (via adjustments) can be modified.	V
Voided	V	Receipt has been cancelled. Only cash_receipt_ref and cash_receipt_comment can be modified. All splits are voided.	--

stateDiagram-v2
    [*] --> U : Receipt created
    U --> P : GL posting job runs
    U --> V : Zeroed out (all splits reduced to $0)
    P --> V : Zeroed out (all splits reduced to $0)

Transition: U --> P

• Trigger: GL posting batch job runs.
• Preconditions: Receipt has posting_dt set.
• Side-effects: posting_dt recorded on the receipt.

Transition: U --> V

• Trigger: adjustReceiptAmount reduces total to $0.
• Preconditions: All splits' worksheets must be in Draft status or have no applications.
• Side-effects: All splits voided; split worksheets deleted if empty Draft.

Transition: P --> V

• Trigger: adjustReceiptAmount reduces total to $0.
• Preconditions: All splits' worksheets must be in Draft status or have no applications.
• Side-effects: All splits voided; split worksheets deleted if empty Draft.

IMPORTANT

There is no void action independent of amount adjustment. Voiding is the natural consequence of reducing the receipt amount to zero. This design eliminates a separate void workflow and ensures the financial audit trail always shows why a receipt was cancelled (the adjustment records remain).

3.2 cash_receipt_split.split_status_cd -- Split Processing Status

The split status tracks the processing stage within the agent workflow (submission, review, application).

Status	Code	Description	Allowed Transitions
New	N	Split has been created but not yet submitted for review. Default status.	S, V
Submitted	S	Agent has submitted the split for review by a cash manager.	A, R
Approved	A	Cash manager has approved the split. Worksheet processing may proceed.	F, P, V
Revise	R	Cash manager has returned the split to the agent for corrections.	S
Fully Applied	F	All split funds have been applied via the worksheet.	--
Partially Applied	P	Some split funds have been applied; a balance remains.	--
Void	V	Split has been cancelled. Read-only.	--

stateDiagram-v2
    [*] --> N : Split created
    N --> S : Agent submits
    S --> A : Manager approves
    S --> R : Manager returns for revision
    R --> S : Agent resubmits
    A --> F : Worksheet fully applied
    A --> P : Worksheet partially applied
    N --> V : Split voided (receipt zeroed out)
    A --> V : Split voided (receipt zeroed out)
    P --> V : Split voided (receipt zeroed out)

Transition: N --> S

• Trigger: Agent submits split.
• Preconditions: None.
• Side-effects: submitted_by_user_id and submitted_dt recorded.

Transition: S --> A

• Trigger: Manager approves the split.
• Preconditions: Manager reviews references and amount.
• Side-effects: reviewed_by_user_id and reviewed_dt recorded.

Transition: S --> R

• Trigger: Manager returns split for revision.
• Preconditions: Manager provides review_notes.
• Side-effects: reviewed_by_user_id, reviewed_dt, and review_notes recorded.

Transition: R --> S

• Trigger: Agent corrects and resubmits.
• Preconditions: None.
• Side-effects: submitted_dt updated.

Transition: A --> F

• Trigger: Worksheet application consumes entire split amount.
• Preconditions: Worksheet applied amount equals split_amt.
• Side-effects: None.

Transition: A --> P

• Trigger: Worksheet application uses partial amount.
• Preconditions: Worksheet applied amount is less than split_amt.
• Side-effects: None.

Transition: N/A/P --> V

• Trigger: Parent receipt zeroed out.
• Preconditions: None.
• Side-effects: Associated Draft worksheet deleted if empty.

Modification rules by status:

Operation	N	S	A	R	F	P	V
Change amount	Yes	No	Yes	No	No	No	No
Transfer funds	Yes	No	Yes	No	No	No	No
Delete split	Yes	No	Yes	No	No	No	No
Update notes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Add references	Yes	Yes	Yes	Yes	No	No	No

NOTE

Splits can be modified when the associated worksheet is in Draft or Approved status. The system checks the worksheet status (not just the split status) before allowing structural changes like amount adjustments and fund transfers.

3.3 cash_receipt_adjustment.posting_status_cd -- Adjustment GL Posting Status

Follows the same POSTING_STATUS_CD pattern as the parent receipt.

Status	Code	Description	Allowed Transitions
Unposted	U	Adjustment staged for GL posting.	P, V
Posted	P	Adjustment posted to GL. Read-only.	V
Voided	V	Adjustment cancelled.	--

---

4. Validation & Database Constraints

Unique Constraints

Table	Constraint	Columns	Business Rule
cash_receipt	uq_cash_receipt_bank_ref	(bank_account_id, bank_ref_id)	Prevents duplicate receipts from the same bank transaction. A bank reference ID is unique within a given bank account.

Business Validation Rules

Receipt creation:

• cash_receipt.original_receipt_amt must be greater than zero.
• cash_receipt.original_currency_cd is required.
• If currency_cd differs from original_currency_cd, fx_rate must be provided and greater than zero.
• On creation, net_receipt_amt defaults to receipt_amt (no adjustments yet).
• A default 1:1 split is auto-created with a Draft worksheet.

Receipt updates (by posting status):

• Unposted (U): All fields are editable.
• Posted (P): Only cash_receipt_comment and net_receipt_amt (via adjustment action) can be modified.
• Voided (V): Only cash_receipt_ref and cash_receipt_comment can be modified.

Split amount invariant:

• The sum of all non-voided cash_receipt_split.split_amt for a receipt must equal cash_receipt.net_receipt_amt. This invariant is enforced at the application layer with a tolerance of 0.005 for floating-point comparison. Splits cannot be created without carving the amount from an existing split, ensuring the total is always balanced.

Split amount floor:

• split_amt cannot be reduced below the applied amount on the split's worksheet. If a split has $5,000 in applications, its amount cannot be set below $5,000.
• split_amt can be set to zero only if the worksheet is in Draft status with no applications.
• A split cannot be negative.

Split deletion rules:

• The last remaining split for a receipt cannot be deleted.
• If the split has funds remaining, a target split must be provided to receive the transferred funds.
• The split's worksheet must be in Draft status with no applications, or the split must have no worksheet.

Split combination rules:

• At least two splits must be selected.
• All splits must belong to the same receipt.
• None of the selected splits may have a worksheet. Worksheets must be deleted first.
• The combined amount is assigned to the split with the lowest sequence number; other splits are deleted.

Fund transfers between splits:

• Source and target splits must belong to the same receipt.
• Both splits must be modifiable (worksheet in Draft or Approved status).
• Transfer amount cannot exceed the source split's available balance.
• If a transfer empties the source split, the system attempts to auto-delete it (deletes the Draft worksheet if empty, then the split).

Adjustment rules:

• Adjustments can be positive or negative.
• Each adjustment recalculates cash_receipt.net_receipt_amt as receipt_amt - SUM(adjustment_amt).
• Adjustments on voided receipts are not permitted.

Reference rules:

• type_cd must be one of the supported reference types (see Code Master Values).
• ref_or_id is not validated against the referenced entity at the database level.
• References are informational; they do not enforce foreign key relationships.

---

5. Code Master Values

5.1 POSTING_STATUS_CD

Used by cash_receipt.posting_status_cd and cash_receipt_adjustment.posting_status_cd.

Code	Description	Behavior / When Used
U	Unposted	Default on creation. Receipt or adjustment is staged and awaiting GL posting. Editable.
P	Posted	GL posting batch has processed this record. posting_dt is set. Limited editability.
V	Voided	Record has been cancelled. Minimal editability (comments and references only).

Default on creation: U

5.2 RECEIPT_TYPE_CD

Used by cash_receipt.receipt_type_cd.

Code	Description	Behavior / When Used
NORMAL	Standard cash receipt	Default. Represents an actual payment received from a buyer. Worksheets follow standard Apply/Settle/Approve flow.
WRITE_OFF	Write-off receipt	System-generated receipt created by the write-off workflow. Has a 1:1 relationship with a write-off packet. Worksheets on write-off receipts skip the settlement step -- they are auto-applied to the written-off REV receivables only. PAY is never written off.

Default on creation: NORMAL

NOTE

WRITE_OFF receipts are created programmatically by the write-off service. They are not created manually by users. The receipt_type_cd drives conditional logic in the worksheet service to bypass settlement requirements.

5.3 ISO20022_ENTRY_STATUS

Used by cash_receipt.entry_status for bank-originated receipts.

Code	Description	Behavior / When Used
BOOK	Booked	Funds are settled and cleared. Safe to apply to receivables. Display color: green.
PDNG	Pending	Transaction is pending settlement. Apply with caution -- may reverse. Display color: yellow.
INFO	Information	No cash movement. Informational only. Do not create receipts. Display color: gray.
FUTR	Future	Future-dated transaction. Queue for future application. Display color: blue.

Default on creation: Set from the originating bank transaction. null for manual receipts.

WARNING

A PDNG receipt may transition to BOOK when a subsequent bank file confirms settlement. This transition is handled automatically by the syncFromBankTransaction method (the "Living Mirror" pattern). However, a PDNG receipt may also be reversed (via RVSL or ARET subfamily codes in a subsequent file), which requires unapplying any cash that was matched.

5.4 ADJUSTMENT_TYPE_CD

Used by cash_receipt_adjustment.adjustment_type_cd.

Code	Description	Behavior / When Used
ADJ	Adjustment	Default. General-purpose adjustment to the receipt's net amount (e.g., bank fees, corrections).
TR	Transfer	Adjustment resulting from a transfer operation. Used when moving amounts between receipts or during inter-receipt operations.

Default on creation: ADJ

5.5 SPLIT_STATUS_CD

Used by cash_receipt_split.split_status_cd.

Code	Description	Behavior / When Used
N	New	Default. Split has been created but not yet submitted for review.
S	Submitted	Agent has submitted the split for cash manager review.
A	Approved	Cash manager has approved the split. Worksheet processing may proceed.
R	Revise	Cash manager has returned the split for corrections.
F	Fully Applied	All split funds have been applied via the worksheet. Terminal state.
P	Partially Applied	Some split funds have been applied; a balance remains.
V	Void	Split has been cancelled. Terminal read-only state.

Default on creation: N

5.6 REFERENCE_TYPE_CD

Used by cash_receipt_reference.type_cd. These are application-defined constants, not stored in code_master.

Code	Description	Behavior / When Used
CLIENT_ID	Client identifier	Numeric party_id of the client. Used to filter receivable search and trigger tax warning calculations.
CONTRACTED_PARTY_ID	Contracted party identifier	Numeric party_id of the contracted party on the deal. May differ from the client.
DEAL_ID	Deal identifier	Numeric deal_id. Narrows receivable search to billing items for a specific deal.
BUYER_ID	Buyer identifier	Numeric party_id of the buyer who made the payment.
DEPARTMENT_ID	Department identifier	Numeric department_id. Narrows receivable search to a specific department.
PAYMENT_TERM_REF	Payment term reference	String reference to a payment term. Used for matching receipts to specific billing items.
SALES_ITEM_REF	Sales item reference	String reference to a sales item (revenue reference). Used for matching receipts to specific revenue lines.

NOTE

References are linked to splits, not to receipts. When a receipt has multiple splits, each split can have its own set of references pointing to different clients, deals, or buyers. This supports the common scenario where a single deposit contains payments for multiple deals or clients.

5.7 CURRENCY_CD

Used by cash_receipt.original_currency_cd and cash_receipt.currency_cd. Stored in code_master with type CURRENCY_CD.

Code	Description	Behavior / When Used
USD	United States Dollar	Default working currency.
GBP	British Pound Sterling	Used for UK-based deals.
EUR	Euro	Used for EU-based deals.
CAD	Canadian Dollar	Used for Canadian deals.

NOTE

Additional currency codes are configured in code_master as needed. The system supports any ISO 4217 currency code.

---

6. Cross-References

Document	Relationship
Worksheets	cash_receipt_split has at most one cash_receipt_worksheet. Worksheet is where cash is applied to receivables.
Billing Items	Receivables that cash receipts are applied against. Each has one REV detail and one PAY detail.
Settlements	Settlements divide PAY applications among the client's party. Created during worksheet Applied status.
Bank Ingestion	bank_transaction feeds cash_receipt via the sync/Living Mirror pattern.
Write-Offs	Write-off packets create WRITE_OFF type receipts for uncollectable receivables.
Parties	bank_account identifies which UTA account received the funds. Party references link splits to clients, buyers, and contracted parties.
Assignments	Task and responsibility assignments can be linked to receipts and splits.
Tax & Withholding	Split-level tax warnings are populated by WithholdingTaxService when party references are added.
Accounting	GL posting lifecycle for receipts and adjustments.