Settlements Data Model

1. Executive Summary

Purpose

The settlements and payments data model manages the final stages of the cash-application lifecycle: determining how the PAY portion of applied cash is divided among the client's party (artist, manager, lawyer, business manager, etc.), and then transmitting those funds to the bank. Settlements are created after cash applications are finalized on a worksheet, dividing the PAY amount among deal parties. Payment items track who is owed and how much, while outbound payment execution records capture every bank API transmission attempt.

Scope

Covered:

participant_settlement — Header record for a settlement; groups one or more items that define the split of PAY
participant_settlement_item — Line-level detail for each payee within a settlement
payment_item — Business record representing "who we owe" — one per outbound payment request
outbound_payment_execution — Technical record representing "did the API call succeed?" — one per bank transmission attempt

Not covered (documented separately):

cash_receipt_payout — Links worksheets to payment items and settlement items; holds payout status — see Worksheets Data Model
cash_receipt_application — Cash applications that settlements are created against — see Worksheets Data Model
cash_receipt_worksheet — Worksheet lifecycle that drives settlement and payment creation — see Worksheets Data Model

2. Data Model

2.1 Entity-Relationship Diagram

mermaid

erDiagram
    participant_settlement ||--o{ participant_settlement_item : "has items"
    participant_settlement_item }o--o| payment_item : "linked on approval"
    payment_item ||--o{ outbound_payment_execution : "has execution attempts"
    cash_receipt_application }o--o| participant_settlement : "linked via participant_settlement_id"
    cash_receipt_payout }o--o| participant_settlement_item : "references item"
    cash_receipt_payout }o--o| payment_item : "linked on approval"
    payment_item }o--o| party : "payee"
    payment_item }o--o| bank_account : "payee destination bank"
    payment_item }o--o| bank_account : "source UTA bank"
    payment_item }o--o| deal : "deal context"
    participant_settlement_item }o--o| party : "payee"
    participant_settlement_item }o--o| bank_account : "payee bank"

    participant_settlement {
        serial participant_settlement_id PK
        varchar participant_settlement_status_cd
        boolean participant_settlement_overrided_ind
        varchar participant_settlement_comment
        timestamp returned_dt
        integer returned_by_user_id
    }

    participant_settlement_item {
        serial participant_settlement_item_id PK
        integer participant_settlement_id FK
        integer payment_party_id FK
        integer payment_party_bank_id FK
        boolean participant_settlement_commission_flat_ind
        decimal participant_settlment_commission_perc
        decimal participant_settlement_commission_amt
        varchar calc_level_cd
        varchar participant_settlement_item_comment
        date payment_date
        integer payment_item_id FK
        boolean do_not_send_ind
    }

    payment_item {
        serial payment_item_id PK
        varchar payment_item_type_cd
        integer uta_entity_id FK
        integer department_id FK
        integer deal_id FK
        integer client_id FK
        integer buyer_id FK
        integer payment_party_id FK
        integer payment_party_bank_id FK
        integer participant_settlement_item_id
        decimal payment_item_amt
        varchar payment_item_currency_cd
        date payment_date
        varchar posting_status_cd
        date posting_dt
        varchar payment_execution_status_cd
        integer source_account_id FK
        varchar return_reason_cd
        boolean do_not_send_ind
    }

    outbound_payment_execution {
        uuid outbound_payment_execution_id PK
        integer payment_item_id FK
        integer bank_profile_id
        varchar bank_profile_name
        varchar execution_status_cd
        text generated_payload
        varchar payload_format
        varchar payment_schema
        varchar bank_reference_id
        integer http_response_code
        text error_message
        decimal payment_amount
        varchar payment_currency
    }

2.2 `participant_settlement`

The settlement header. Each record groups one or more items that together define how the PAY amount for a set of receivables is divided among deal parties.

Field	Type	Required	Default	Description
`participant_settlement_id`	`serial`	Yes	Auto	Primary key.
`participant_settlement_status_cd`	`varchar(50)`	No	—	Settlement lifecycle status. Values: `D` (Draft), `T` (Settled), `A` (Approved), `R` (Returned), `P` (Paid). See Section 3.
`participant_settlement_overrided_ind`	`boolean`	No	—	`true` when the settlement split has been manually overridden from the deal-level defaults.
`participant_settlement_comment`	`varchar(500)`	No	—	Free-text comment.
`returned_dt`	`timestamp`	No	—	Populated when settlement is returned.
`returned_by_user_id`	`integer`	No	—	FK to `users`. User who returned the settlement.

NOTE

The settlement header does not directly reference a worksheet. The linkage is indirect: cash_receipt_application.participant_settlement_id connects applications on a worksheet to their settlement. All applications linked to one settlement must belong to the same worksheet.

NOTE

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

2.3 `participant_settlement_item`

Each item represents one payee's share within a settlement. A settlement for a deal with three parties (artist, manager, lawyer) would have three items.

Field	Type	Required	Default	Description
`participant_settlement_item_id`	`serial`	Yes	Auto	Primary key.
`participant_settlement_id`	`integer`	No	—	FK to `participant_settlement`. Groups this item under its header.
`payment_party_id`	`integer`	No	—	FK to `party`. The payee receiving this portion.
`payment_party_bank_id`	`integer`	No	—	FK to `bank_account`. The payee's bank account for this payment.
`participant_settlement_commission_flat_ind`	`boolean`	No	—	`true` = flat dollar amount; `false` = percentage-based commission.
`participant_settlment_commission_perc`	`decimal(7,4)`	No	—	Commission percentage (e.g., `85.0000` for 85%). Used when `participant_settlement_commission_flat_ind = false`.
`participant_settlement_commission_amt`	`decimal(15,2)`	No	—	Calculated or manually entered commission amount in dollars.
`calc_level_cd`	`varchar(3)`	No	`'DNI'`	Controls deduction handling. `DNI` = Do Not Ignore Deductions (commission calculated after deductions); `IGN` = Ignore Deductions (commission calculated on gross). See Section 5.
`participant_settlement_item_comment`	`varchar(500)`	No	—	Free-text comment for this line item.
`payment_date`	`date`	No	—	Requested payment date. Transferred to `payment_item.payment_date` and `cash_receipt_payout.payment_date` on approval. If future-dated, the payment item starts in `WAITING` status.
`payment_item_id`	`integer`	No	—	FK to `payment_item`. Populated at worksheet approval when the payment item is created from this settlement item. `null` during Draft and Settled status.
`do_not_send_ind`	`boolean`	No	`false`	When `true`, the payment should not be transmitted to the bank. Causes payment item to start in `WAITING` status.

WARNING

The column participant_settlment_commission_perc contains a known typo (missing 'e' in "settlement"). This is preserved in the schema for backward compatibility and must be used as-is in all queries.

NOTE

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

2.4 `payment_item`

The business-level payment record. Represents "who we owe and how much." Created at worksheet approval from cash_receipt_payout records. Each payment item can have multiple outbound_payment_execution attempts.

Field	Type	Required	Default	Description
`payment_item_id`	`serial`	Yes	Auto	Primary key.
`payment_item_type_cd`	`varchar(50)`	No	—	Type of payment. Values: `S` (Settlement), `P` (Passthrough), `L` (Ledger), `R` (Reversal), `V` (VAT Pass-Through). See Section 5.
`uta_entity_id`	`integer`	No	—	FK to `uta_entity`. The UTA legal entity making the payment.
`department_id`	`integer`	No	—	FK to `department`. UTA department associated with this payment.
`agent_group_id`	`integer`	No	—	Reference to agent group. Not a formal FK constraint.
`payment_item_name`	`varchar(500)`	No	—	Descriptive name for the payment.
`deal_id`	`integer`	No	—	FK to `deal`. The deal this payment relates to.
`client_id`	`integer`	No	—	FK to `party`. The client (artist/talent) associated with this payment.
`contracted_party_id`	`integer`	No	—	FK to `party`. The contracted party on the billing item.
`buyer_id`	`integer`	No	—	FK to `party`. The buyer from the original deal.
`payment_party_id`	`integer`	No	—	FK to `party`. The actual payee receiving the funds.
`payment_party_bank_id`	`integer`	No	—	FK to `bank_account`. The payee's destination bank account.
`participant_settlement_item_id`	`integer`	No	—	Logical FK to `participant_settlement_item` (no physical constraint to avoid circular dependency). Links back to the settlement item that generated this payment.
`payment_item_amt`	`decimal(15,2)`	No	—	Amount to be paid.
`payment_item_currency_cd`	`varchar(10)`	No	—	ISO currency code (e.g., `USD`, `GBP`, `EUR`).
`payment_date`	`date`	No	—	Requested execution date. If future-dated, payment item starts in `WAITING` status.
`payment_item_comment`	`varchar(500)`	No	—	Free-text comment.
`posting_status_cd`	`varchar(50)`	No	—	GL posting status. Values: `U` (Unposted), `P` (Posted), `X` (Skipped). See Section 3.
`posting_dt`	`date`	No	—	Date the payment was posted to GL. Set when `posting_status_cd` transitions to `P`.
`payment_clearing_status_ind`	`boolean`	No	—	Indicates whether this payment has cleared the bank.
`payment_execution_status_cd`	`varchar(20)`	No	—	Outbound payment execution status. Values: `WAITING`, `PENDING`, `PROCESSING`, `SENT`, `ACKNOWLEDGED`, `PAID`, `FAILED`, `CANCELLED`. See Section 3.
`source_account_id`	`integer`	No	—	FK to `bank_account`. The UTA source bank account used as the debtor/payor in the outbound payment.
`return_reason_cd`	`varchar(20)`	No	—	Reason code when the payment is returned or cancelled (e.g., `WORKSHEET_RETURN`).
`returned_dt`	`timestamp`	No	—	Timestamp when the payment item was returned or cancelled.
`returned_by_user_id`	`integer`	No	—	FK to `users`. User who returned or cancelled the payment item.
`do_not_send_ind`	`boolean`	No	`false`	When `true`, the payment should not be transmitted to the bank. Causes the item to remain in `WAITING` status.

IMPORTANT

The participant_settlement_item_id column is a logical FK without a physical foreign key constraint. This is intentional to avoid a circular dependency between payment_item and participant_settlement_item (which itself has a FK back to payment_item).

NOTE

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

2.5 `outbound_payment_execution`

The technical execution record. Each row represents one attempt to transmit a payment to a bank API. A single payment_item can have multiple execution records (e.g., initial attempt fails, retry succeeds).

Field	Type	Required	Default	Description
`outbound_payment_execution_id`	`uuid`	Yes	Random UUID	Primary key. UUID rather than serial to support distributed generation.
`payment_item_id`	`integer`	No	—	FK to `payment_item`. The business payment this execution attempt belongs to.
`bank_profile_id`	`integer`	Yes	—	Legacy bank identifier. `1` = CNB, `2` = JPM, `3` = BofA. Retained for backward compatibility; adapter selection now uses `payment_schema`.
`bank_profile_name`	`varchar(50)`	No	—	Human-readable bank name (e.g., `City National Bank`).
`execution_status_cd`	`varchar(20)`	Yes	`'CREATED'`	Technical execution status. Values: `CREATED`, `SENT`, `ACKNOWLEDGED`, `FAILED`. See Section 3.
`generated_payload`	`text`	No	—	The full JSON or XML payload sent to the bank. Stored for debugging and audit. Populated before transport is attempted.
`payload_format`	`varchar(20)`	No	—	Format of `generated_payload`. Values: `JSON`, `XML`.
`payment_schema`	`varchar(50)`	No	—	Determines which adapter was used. Values: `CNB_EASI_LINK`, `ISO20022_PAIN001`, `BOFA_CASHPRO`, `JPM_GLOBAL_PAY`. Derived from the `PAYMENT_REQUEST_SCHEMA` code attribute on the bank, not from bank identity directly.
`bank_reference_id`	`varchar(100)`	No	—	Reference ID returned by the bank on successful submission. Used for status polling and reconciliation.
`http_response_code`	`integer`	No	—	HTTP response code from the bank API call.
`error_message`	`text`	No	—	Error details when execution fails.
`requested_execution_date`	`date`	No	—	Snapshot of the requested payment date at time of execution.
`payment_amount`	`decimal(15,2)`	No	—	Snapshot of the payment amount at time of execution.
`payment_currency`	`varchar(10)`	No	—	Snapshot of the currency code at time of execution.
`service_level`	`varchar(20)`	No	—	Payment method used. Values: `WIRE`, `ACH`. Derived from the payee's `party_bank_account.preferred_payment_method`.
`last_polled_at`	`timestamp`	No	—	Timestamp of the most recent status poll against the bank API.
`poll_count`	`integer`	No	`0`	Number of times this execution has been polled for status.
`status_history`	`jsonb`	No	—	Array of status poll results. Each entry contains: `pollNumber`, `bankStatus`, `mappedStatus`, `timestamp`, `details`.

NOTE

The outbound_payment_execution table stores snapshots of amount, currency, and execution date at the time of transmission. These snapshots do not change if the parent payment_item is later modified, providing a reliable audit trail of what was actually sent to the bank.

NOTE

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

3. Status Lifecycle

This domain contains four distinct status lifecycles that interact with each other. Understanding their relationships is critical for correct implementation.

3.1 Settlement Status (`participant_settlement.participant_settlement_status_cd`)

Status	Code	Description	Allowed Transitions
Draft	`D`	Settlement created; items are being defined by the user.	→ Settled (`T`)
Settled	`T`	All items finalized. Payout records have been created.	→ Approved (`A`)
Approved	`A`	Settlement approved. Payment items created and ready for bank transmission.	→ Returned (`R`), → Paid (`P`)
Returned	`R`	Settlement returned during worksheet return. Sealed, read-only.	—
Paid	`P`	All payment items linked to this settlement have reached `PAID` status. Terminal.	—

mermaid

stateDiagram-v2
    [*] --> D : Settlement created
    D --> T : Worksheet transitions to Settled
    T --> A : Worksheet transitions to Approved
    A --> R : Worksheet returned
    A --> P : All payment items reach PAID

Transition: (new) → Draft (`D`)

Trigger: User selects PAY receivables on a worksheet and saves a new settlement.
Preconditions: Worksheet must be in Applied (P) status. All selected receivables must belong to the same deal.
Side-effects: Settlement header and items created. cash_receipt_application.participant_settlement_id populated for each linked application.

Transition: Draft (`D`) → Settled (`T`)

Trigger: Worksheet transitions from Applied (P) to Settled (T).
Preconditions: All PAY applications on the worksheet must have settlements. Settlement payout amounts (type S on cash_receipt_payout) must balance against total PAY applied.
Side-effects: cash_receipt_payout records created for each settlement item (if not already created). Settlement status bulk-updated for all settlements on the worksheet.

Transition: Settled (`T`) → Approved (`A`)

Trigger: Worksheet transitions from Settled (T) to Approved (A).
Preconditions: Settlement must be in Settled status. Approval performed by a user with the Settlement Approver role.
Side-effects: payment_item records created from cash_receipt_payout records. payment_item_id back-linked to both cash_receipt_payout.payment_item_id and participant_settlement_item.payment_item_id.

Transition: Approved (`A`) → Returned (`R`)

Trigger: Worksheet return initiated by an authorized user.
Preconditions: Settlement must be in Approved status.
Side-effects: If ALL payment items are unlocked (not yet sent), settlement is fully reversed. If ANY payment item is locked (PROCESSING, SENT, ACKNOWLEDGED, or PAID), the entire settlement and all its items are locked and copied to the replacement draft as read-only context. Original worksheet sealed as Returned.

Transition: Approved (`A`) → Paid (`P`)

Trigger: All payment_item records linked to this settlement's items reach PAID status.
Preconditions: All payment_item.payment_execution_status_cd values for this settlement must be PAID.
Side-effects: Terminal state. No further transitions.

IMPORTANT

Settlement status transitions are forward-only. A settlement never moves backward (e.g., Approved never reverts to Draft). The three-document model (original sealed, reversal worksheet, replacement draft) is used instead of backward transitions.

3.2 Payment Item Execution Status (`payment_item.payment_execution_status_cd`)

Status	Code	Description	Allowed Transitions
Waiting	`WAITING`	Held — future-dated or flagged do-not-send.	→ `PENDING`
Pending	`PENDING`	Ready for pickup by the payment processor.	→ `PROCESSING`, → `CANCELLED`
Processing	`PROCESSING`	Locked by processor; payload being built and transmitted.	→ `SENT`, → `FAILED`
Sent	`SENT`	Successfully transmitted to bank. Locked.	→ `ACKNOWLEDGED`, → `FAILED`
Acknowledged	`ACKNOWLEDGED`	Bank confirmed receipt or completion. Locked.	→ `PAID`
Paid	`PAID`	Bank confirmed funds transferred. Locked. Terminal.	—
Failed	`FAILED`	Transmission or bank processing failed. Eligible for retry.	→ `PENDING`
Cancelled	`CANCELLED`	Voided during worksheet return. Terminal.	—

mermaid

stateDiagram-v2
    [*] --> WAITING : Future date or do_not_send_ind=true
    [*] --> PENDING : Ready immediately
    WAITING --> PENDING : Date arrives or hold released
    PENDING --> PROCESSING : Processor picks up
    PROCESSING --> SENT : Bank API call succeeds
    PROCESSING --> FAILED : Bank API call fails
    SENT --> ACKNOWLEDGED : Bank confirms via poll
    SENT --> FAILED : Bank reports failure via poll
    ACKNOWLEDGED --> PAID : Bank confirms funds transferred
    FAILED --> PENDING : Retry
    PENDING --> CANCELLED : Worksheet return voids payment

Transition: (new) → Waiting (`WAITING`)

Trigger: Payment item created at worksheet approval when payment_date is in the future or do_not_send_ind = true.
Preconditions: Worksheet transitions to Approved (A).
Side-effects: Payment held; not eligible for processing by the payment processor.

Transition: (new) → Pending (`PENDING`)

Trigger: Payment item created at worksheet approval when payment_date is current or past and do_not_send_ind = false.
Preconditions: Worksheet transitions to Approved (A).
Side-effects: Payment eligible for pickup by the payment processor.

Transition: Waiting (`WAITING`) → Pending (`PENDING`)

Trigger: Payment date arrives or hold is released (do_not_send_ind set to false).
Preconditions: Date check passes and do_not_send_ind = false.
Side-effects: Payment becomes eligible for processing.

Transition: Pending (`PENDING`) → Processing (`PROCESSING`)

Trigger: Payment processor picks up the item.
Preconditions: Processor locks the item before building the bank payload.
Side-effects: Prevents concurrent processing. outbound_payment_execution record created with execution_status_cd = 'CREATED'.

Transition: Processing (`PROCESSING`) → Sent (`SENT`)

Trigger: Bank API call succeeds; adapter confirms successful transmission.
Preconditions: Bank adapter reports successful HTTP response.
Side-effects: outbound_payment_execution updated to SENT. Bank reference ID stored. Payment is now locked — cannot be reversed, voided, or modified.

Transition: Sent (`SENT`) → Acknowledged (`ACKNOWLEDGED`)

Trigger: Bank confirms receipt via status poll.
Preconditions: Status poller receives COMPLETED status from bank.
Side-effects: outbound_payment_execution updated to ACKNOWLEDGED. GL posting becomes eligible.

Transition: Acknowledged (`ACKNOWLEDGED`) → Paid (`PAID`)

Trigger: Bank confirms funds transferred (final confirmation).
Preconditions: Final bank confirmation received.
Side-effects: Terminal state. posting_status_cd can transition to P (Posted).

Transition: Processing (`PROCESSING`) → Failed (`FAILED`)

Trigger: Bank API call fails (HTTP error, validation error, bank rejection).
Preconditions: Adapter reports failure.
Side-effects: outbound_payment_execution updated to FAILED. Payment item reverted to PENDING to allow retry.

Transition: Sent (`SENT`) → Failed (`FAILED`)

Trigger: Bank reports failure via status poll.
Preconditions: Status poller receives FAILED or REVERSED status from bank.
Side-effects: outbound_payment_execution status updated. error_message and status_history appended.

Transition: Pending (`PENDING`) → Cancelled (`CANCELLED`)

Trigger: Worksheet return voids the payment item.
Preconditions: Payment item has not yet been sent (not in a locked status).
Side-effects: return_reason_cd and returned_dt populated. Payment item removed from processing queue. Terminal state.

IMPORTANT

Locked statuses: PROCESSING, SENT, ACKNOWLEDGED, PAID. A payment item in any of these statuses cannot be reversed, voided, or modified. Its associated settlement and PAY application are also locked as a unit. Statuses WAITING, PENDING, FAILED, CANCELLED, and null are considered unlocked.

3.3 Payment Item GL Posting Status (`payment_item.posting_status_cd`)

Status	Code	Description	Allowed Transitions
Unposted	`U`	Default on creation. No GL entry exists.	→ `P`, → `X`
Posted	`P`	GL journal entry has been created. `posting_dt` is set.	—
Skipped	`X`	Payment was cancelled or returned before GL posting. No GL entry needed.	—

mermaid

stateDiagram-v2
    [*] --> U : Payment item created
    U --> P : GL posting process runs after bank confirmation
    U --> X : Payment voided before being sent

Transition: (new) → Unposted (`U`)

Trigger: Payment item created at worksheet approval.
Preconditions: None.
Side-effects: No GL entry created.

Transition: Unposted (`U`) → Posted (`P`)

Trigger: GL posting process runs after bank confirmation.
Preconditions: payment_execution_status_cd must be ACKNOWLEDGED or PAID.
Side-effects: GL journal entry created. posting_dt set on the payment item.

Transition: Unposted (`U`) → Skipped (`X`)

Trigger: Payment item cancelled or returned before being sent.
Preconditions: Payment was never sent (never posted to GL).
Side-effects: No GL entry created or reversed.

WARNING

You cannot reverse a GL entry that was never posted. If a payment item has posting_status_cd = 'U' (never posted) and is being reversed, the reversal must set posting_status_cd = 'X' (Skipped) — NOT P (Posted). Posting a reversal for a transaction that was never posted creates a GL imbalance.

3.4 Outbound Payment Execution Status (`outbound_payment_execution.execution_status_cd`)

Status	Code	Description	Allowed Transitions
Created	`CREATED`	Record initialized; payload being built.	→ `SENT`, → `FAILED`
Sent	`SENT`	Payload successfully transmitted to bank API.	→ `ACKNOWLEDGED`, → `FAILED`
Acknowledged	`ACKNOWLEDGED`	Bank confirmed completion via status polling. Terminal (success).	—
Failed	`FAILED`	Error during transmission or bank rejected. Terminal (failure).	—

mermaid

stateDiagram-v2
    [*] --> CREATED : Execution record initialized
    CREATED --> SENT : Bank API call succeeds
    CREATED --> FAILED : Bank API call fails
    SENT --> ACKNOWLEDGED : Status poll returns COMPLETED
    SENT --> FAILED : Status poll returns FAILED or REVERSED

Transition: (new) → Created (`CREATED`)

Trigger: Payment processor begins building the bank payload.
Preconditions: Parent payment_item is in PROCESSING status.
Side-effects: generated_payload saved before transport is attempted.

Transition: Created (`CREATED`) → Sent (`SENT`)

Trigger: Bank API call succeeds; HTTP response indicates acceptance.
Preconditions: Successful HTTP response from bank.
Side-effects: bank_reference_id stored. http_response_code captured. Parent payment_item.payment_execution_status_cd updated to SENT.

Transition: Created (`CREATED`) → Failed (`FAILED`)

Trigger: Bank API call fails during initial transmission (HTTP error, timeout, or bank rejection).
Preconditions: Failure response received from bank.
Side-effects: error_message captured. Parent payment_item.payment_execution_status_cd reverted to PENDING.

Transition: Sent (`SENT`) → Acknowledged (`ACKNOWLEDGED`)

Trigger: Status poll returns COMPLETED from bank.
Preconditions: Bank confirms the payment has been processed or funds transferred.
Side-effects: Parent payment_item.payment_execution_status_cd updated to PAID. status_history appended.

Transition: Sent (`SENT`) → Failed (`FAILED`)

Trigger: Status poll returns FAILED or REVERSED from bank.
Preconditions: Bank reports the payment failed after initial acceptance.
Side-effects: Parent payment_item.payment_execution_status_cd updated to FAILED. status_history appended.

NOTE

Execution records are immutable once terminal (ACKNOWLEDGED or FAILED). A failed execution is never retried — instead, a new execution record is created for the same payment_item_id. This preserves a complete audit trail of all transmission attempts.

4. Validation & Database Constraints

Unique Constraints

There are no composite unique constraints defined at the database level on these tables beyond the primary keys. Business-level uniqueness is enforced at the service layer.

Business Validation

Settlement creation requires Applied worksheet: Settlements can only be created when the parent worksheet is in Applied (P) status. Creating a settlement on a Draft or Approved worksheet is not permitted.
Same-deal receivables: All selected receivables for a settlement must belong to the same deal (same billing_item.deal_id). A single settlement cannot span multiple deals.
PAY-only settlements: Settlements divide the PAY portion only. REV applications do not participate in settlements directly. Selecting a REV application for settlement creation is not permitted.
Commission sum balance: The participant_settlement_commission_amt values across all items in a settlement must sum to the total PAY applied for the linked applications. This balance is required for the worksheet to transition from Applied to Settled.
Settlement payout balance: Settlement payout amounts (type S on cash_receipt_payout) must equal total PAY applied for the worksheet to transition from Applied (P) to Settled (T). This is the settleable validation check.
Override tracking: When participant_settlement_overrided_ind = true, the settlement commission percentages and amounts were manually entered rather than derived from deal party defaults. Deal-level commission defaults are loaded from deal_party during settlement creation, but users can override any value.
Deduction calculation level: calc_level_cd = 'DNI' (Do Not Ignore Deduction) calculates commission on the net amount (after deductions). calc_level_cd = 'IGN' (Ignore Deduction) calculates commission on the gross amount (before deductions). The default is DNI.
Payment item creation trigger: Payment items are created at worksheet approval (transition to A), not at settlement creation or settlement save. Each cash_receipt_payout with payment_item_id IS NULL generates one payment_item. Payouts with zero amounts are skipped.
Payment item initial status logic: The initial payment_execution_status_cd on a newly created payment_item is WAITING when payment_date is in the future or do_not_send_ind = true; otherwise PENDING.
Settlement-level locking: If any participant_settlement_item within a settlement has a payment_item in a locked status (PROCESSING, SENT, ACKNOWLEDGED, or PAID), then all items in that settlement are locked. A single locked payment locks the entire settlement.
Bilateral locking on return: A locked settlement also locks all PAY applications linked to that settlement (via cash_receipt_application.participant_settlement_id), all payouts linked to that settlement's items, and the REV applications positionally paired with the locked PAY applications.
Worksheet return — locked vs. unlocked payments: On worksheet return, locked payments (PROCESSING, SENT, ACKNOWLEDGED, PAID) cannot be reversed. Their payment items, settlement items, and connected PAY/REV applications are copied to the replacement draft as read-only context. Unlocked payments (WAITING, PENDING, FAILED, null) are voided by setting payment_execution_status_cd to CANCELLED. These are not copied to the replacement draft; users add fresh applications and settlements manually.
Circular dependency avoidance: payment_item.participant_settlement_item_id is a logical FK with no physical foreign key constraint. This avoids a circular dependency between payment_item and participant_settlement_item, which itself holds a FK to payment_item.
Decimal precision: All monetary *_amt fields use decimal(15,2) (up to $9,999,999,999,999.99). The participant_settlment_commission_perc field uses decimal(7,4). Balance comparison tolerance is 0.005.

5. Code Master Values

5.1 `PARTICIPANT_SETTLEMENT_STATUS_CD`

Used by participant_settlement.participant_settlement_status_cd.

Code	Description	Behavior / When Used
`D`	Draft	Settlement created; items being defined by the user. Default on creation.
`T`	Settled	All items finalized. Payout records created. Worksheet can transition to Settled.
`A`	Approved	Settlement approved. Payment items created. Worksheet can transition to Approved.
`R`	Returned	Settlement returned during worksheet return. Sealed, read-only.
`P`	Paid	All payment items have reached `PAID` status. Terminal state.

Default on creation: D

5.2 `PAYMENT_ITEM_TYPE_CD`

Used by payment_item.payment_item_type_cd and cash_receipt_payout.payment_item_type_cd.

Code	Description	Behavior / When Used
`S`	Settlement	Payment derived from a `participant_settlement_item`. The standard flow for PAY distributions.
`P`	Passthrough	Direct payment to a party, not routed through the settlement process.
`L`	Ledger	Payment related to a client ledger entry (loan repayment, on-account distribution).
`R`	Reversal	Reversal of a prior payment. Created during worksheet return for locked items.
`V`	VAT Pass-Through	VAT-related pass-through payment. Used for UK VAT withholding scenarios.

NOTE

The seed data labels L as "Loan" and R as "Refund". In practice, the system uses L for all ledger-based payments and R for all reversal payments. The code master descriptions reflect the original naming.

Default on creation: S for standard settlement-derived payments.

5.3 `POSTING_STATUS_CD`

Used by payment_item.posting_status_cd.

Code	Description	Behavior / When Used
`U`	Unposted	Default on creation. No GL entry exists. Payment awaiting bank confirmation before GL posting.
`P`	Posted	GL journal entry has been created after bank confirmation (`ACKNOWLEDGED` or `PAID`). `posting_dt` is set.
`X`	Skipped	Payment was cancelled or returned before being sent. No GL entry was ever created, so no GL reversal is needed.

Default on creation: U

5.4 `CALC_LEVEL_CD`

Used by participant_settlement_item.calc_level_cd.

Code	Description	Behavior / When Used
`DNI`	Do Not Ignore Deductions	Commission is calculated on the net amount (after deductions applied via `cash_receipt_application_deduction`). Default.
`IGN`	Ignore Deductions	Commission is calculated on the gross amount (before deductions). Used when deal terms specify gross commission calculation.

Default on creation: DNI

5.5 Payment Execution Status Codes

The payment_item.payment_execution_status_cd values are application-level enumerations, not stored in code_master.

Code	Description	Behavior / When Used
`WAITING`	Held	Future-dated or flagged `do_not_send_ind = true`. Not yet eligible for processing.
`PENDING`	Ready	Ready for pickup by the payment processor.
`PROCESSING`	Locked	Locked by processor; payload being built and transmitted.
`SENT`	Transmitted	Successfully transmitted to bank. Locked. Cannot be reversed or voided.
`ACKNOWLEDGED`	Bank Confirmed	Bank confirmed receipt or completion. Locked. GL posting eligible.
`PAID`	Funds Transferred	Bank confirmed funds transferred. Locked. Terminal.
`FAILED`	Failed	Transmission or bank processing failed. Eligible for retry (reverts to `PENDING`).
`CANCELLED`	Voided	Voided during worksheet return. Terminal.

Default on creation: WAITING (future-dated or do_not_send_ind = true) or PENDING (otherwise).

5.6 Outbound Payment Execution Status Codes

The outbound_payment_execution.execution_status_cd values are application-level enumerations, not stored in code_master.

Code	Description	Behavior / When Used
`CREATED`	Initialized	Record created; payload being built by the bank adapter.
`SENT`	Transmitted	Payload successfully transmitted to bank API. `bank_reference_id` stored.
`ACKNOWLEDGED`	Confirmed	Bank confirmed completion via status polling. Terminal (success).
`FAILED`	Error	Error during transmission or bank rejected. Terminal (failure). New execution record created for retry.

Default on creation: CREATED

5.7 Payout Status Codes

The cash_receipt_payout.payout_status_cd values are application-level enumerations, not stored in code_master. Documented here for cross-reference; cash_receipt_payout is fully documented in the Worksheets Data Model.

Code	Description	Behavior / When Used
`PENDING`	Awaiting payment	Payout created; awaiting payment processing. Default on creation.
`PAID`	Paid	Linked `payment_item` has reached `PAID` status. Terminal.
`RETURNED`	Returned	Payout returned during worksheet return.
`CANCELLED`	Voided	Payout voided. Terminal.

Default on creation: PENDING

5.8 Payment Schema Codes

Used by outbound_payment_execution.payment_schema. These are application-level constants, not stored in code_master.

Code	Description	Behavior / When Used
`CNB_EASI_LINK`	City National Bank EASI Link	CNB proprietary JSON format. Adapter selection based on `PAYMENT_REQUEST_SCHEMA` code attribute on the bank.
`ISO20022_PAIN001`	Generic ISO 20022	Standard ISO 20022 pain.001.001.03 XML. Used as fallback.
`BOFA_CASHPRO`	Bank of America CashPro	ISO 20022 with BofA CashPro extensions.
`JPM_GLOBAL_PAY`	J.P. Morgan Global Payments	ISO 20022 with JPM Global Payments extensions.

6. Cross-References

Document	Relationship
Worksheets Data Model	`cash_receipt_application.participant_settlement_id` → `participant_settlement.participant_settlement_id`. Applications link to settlements. `cash_receipt_payout` bridges the worksheet to payment items; `participant_settlement_item_id` and `payment_item_id` on `cash_receipt_payout` link the three domains together. Worksheet status lifecycle drives settlement creation (Applied), payout creation (Settled), and payment item creation (Approved).
Billing Items Data Model	`billing_item_detail` (PAY type) is the source receivable that settlements divide. The deal, client, buyer, UTA entity, and department from the billing item are propagated to `payment_item` at creation time. Deductions from `billing_item_deduction` and `cash_receipt_application_deduction` affect settlement commission calculations depending on `calc_level_cd`.
Parties Data Model	`participant_settlement_item.payment_party_id` and `payment_item.payment_party_id` → `party.party_id`. Deal parties are loaded as commission defaults during settlement creation from `deal_party`.
Parties Data Model	`payment_item.payment_party_bank_id` (payee destination) and `payment_item.source_account_id` (UTA source/debtor). The source account's bank drives adapter selection for `outbound_payment_execution`.
Accounting Data Model	`payment_item.posting_status_cd` and `posting_dt` track GL posting. GL posting occurs only after bank confirmation (`ACKNOWLEDGED` or `PAID`). Cancelled payments use `posting_status_cd = 'X'` to indicate no GL entry was ever posted.

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Settlements Data Model ​

1. Executive Summary ​

Purpose ​

Scope ​

2. Data Model ​

2.1 Entity-Relationship Diagram ​

2.2 participant_settlement ​

2.3 participant_settlement_item ​

2.4 payment_item ​

2.5 outbound_payment_execution ​

3. Status Lifecycle ​

3.1 Settlement Status (participant_settlement.participant_settlement_status_cd) ​

Transition: (new) → Draft (D) ​

Transition: Draft (D) → Settled (T) ​

Transition: Settled (T) → Approved (A) ​

Transition: Approved (A) → Returned (R) ​

Transition: Approved (A) → Paid (P) ​

3.2 Payment Item Execution Status (payment_item.payment_execution_status_cd) ​

Transition: (new) → Waiting (WAITING) ​

Transition: (new) → Pending (PENDING) ​

Transition: Waiting (WAITING) → Pending (PENDING) ​

Transition: Pending (PENDING) → Processing (PROCESSING) ​

Transition: Processing (PROCESSING) → Sent (SENT) ​

Transition: Sent (SENT) → Acknowledged (ACKNOWLEDGED) ​

Transition: Acknowledged (ACKNOWLEDGED) → Paid (PAID) ​

Transition: Processing (PROCESSING) → Failed (FAILED) ​

Transition: Sent (SENT) → Failed (FAILED) ​

Transition: Pending (PENDING) → Cancelled (CANCELLED) ​

3.3 Payment Item GL Posting Status (payment_item.posting_status_cd) ​

Transition: (new) → Unposted (U) ​

Transition: Unposted (U) → Posted (P) ​

Transition: Unposted (U) → Skipped (X) ​

3.4 Outbound Payment Execution Status (outbound_payment_execution.execution_status_cd) ​

Transition: (new) → Created (CREATED) ​

Transition: Created (CREATED) → Sent (SENT) ​

Transition: Created (CREATED) → Failed (FAILED) ​

Transition: Sent (SENT) → Acknowledged (ACKNOWLEDGED) ​

Transition: Sent (SENT) → Failed (FAILED) ​

4. Validation & Database Constraints ​

Unique Constraints ​

Business Validation ​

5. Code Master Values ​

5.1 PARTICIPANT_SETTLEMENT_STATUS_CD ​

5.2 PAYMENT_ITEM_TYPE_CD ​

5.3 POSTING_STATUS_CD ​

5.4 CALC_LEVEL_CD ​

5.5 Payment Execution Status Codes ​

5.6 Outbound Payment Execution Status Codes ​

5.7 Payout Status Codes ​

5.8 Payment Schema Codes ​

6. Cross-References ​

Settlements Data Model

1. Executive Summary

Purpose

Scope

2. Data Model

2.1 Entity-Relationship Diagram

2.2 `participant_settlement`

2.3 `participant_settlement_item`

2.4 `payment_item`

2.5 `outbound_payment_execution`

3. Status Lifecycle

3.1 Settlement Status (`participant_settlement.participant_settlement_status_cd`)

Transition: (new) → Draft (`D`)

Transition: Draft (`D`) → Settled (`T`)

Transition: Settled (`T`) → Approved (`A`)

Transition: Approved (`A`) → Returned (`R`)

Transition: Approved (`A`) → Paid (`P`)

3.2 Payment Item Execution Status (`payment_item.payment_execution_status_cd`)

Transition: (new) → Waiting (`WAITING`)

Transition: (new) → Pending (`PENDING`)

Transition: Waiting (`WAITING`) → Pending (`PENDING`)

Transition: Pending (`PENDING`) → Processing (`PROCESSING`)

Transition: Processing (`PROCESSING`) → Sent (`SENT`)

Transition: Sent (`SENT`) → Acknowledged (`ACKNOWLEDGED`)

Transition: Acknowledged (`ACKNOWLEDGED`) → Paid (`PAID`)

Transition: Processing (`PROCESSING`) → Failed (`FAILED`)

Transition: Sent (`SENT`) → Failed (`FAILED`)

Transition: Pending (`PENDING`) → Cancelled (`CANCELLED`)

3.3 Payment Item GL Posting Status (`payment_item.posting_status_cd`)

Transition: (new) → Unposted (`U`)

Transition: Unposted (`U`) → Posted (`P`)

Transition: Unposted (`U`) → Skipped (`X`)

3.4 Outbound Payment Execution Status (`outbound_payment_execution.execution_status_cd`)

Transition: (new) → Created (`CREATED`)

Transition: Created (`CREATED`) → Sent (`SENT`)

Transition: Created (`CREATED`) → Failed (`FAILED`)

Transition: Sent (`SENT`) → Acknowledged (`ACKNOWLEDGED`)

Transition: Sent (`SENT`) → Failed (`FAILED`)

4. Validation & Database Constraints

Unique Constraints

Business Validation

5. Code Master Values

5.1 `PARTICIPANT_SETTLEMENT_STATUS_CD`

5.2 `PAYMENT_ITEM_TYPE_CD`

5.3 `POSTING_STATUS_CD`

5.4 `CALC_LEVEL_CD`

5.5 Payment Execution Status Codes

5.6 Outbound Payment Execution Status Codes

5.7 Payout Status Codes

5.8 Payment Schema Codes

6. Cross-References