Mass Settlement Data Model

1. Executive Summary

Purpose

The Mass Settlement (MS) data model covers the pre-settlement approval layer that sits upstream of the traditional worksheet/settlement flow. An operator groups cash receipt splits and their target receivables into a settlement packet, stages tentative allocations, and submits the packet for agent team approval. Only after the packet is released are the behind-the-scenes worksheets and settlements created.

Scope

Covered:

• draft_allocation — Staging table for tentative cash-to-receivable assignments. No accounting impact. Deleted when "Create Settlement" is clicked.
• settlement_packet — Header approval entity for the MS workflow. Drives the agent approval lifecycle before funds are finalized.
• settlement_packet_item — Per-receivable line within a packet. Tracks amounts applied and the behind-the-scenes worksheet/settlement created.
• settlement_packet_status_history — Immutable audit trail of all packet status transitions.

Not covered (documented separately):

• participant_settlement, payment_item, outbound_payment_execution — the downstream settlement and payment flow triggered after a packet is released. See Settlements Data Model.
• cash_receipt_split, cash_receipt_worksheet, cash_receipt_application — the worksheet layer that MS creates behind the scenes on release. See Worksheets Data Model.
• billing_item, billing_item_detail — the receivables being settled. See Billing Items Data Model.

---

2. Data Model

2.1 Entity-Relationship Diagram

erDiagram
    settlement_packet ||--o{ settlement_packet_item : "has items"
    settlement_packet ||--o{ settlement_packet_status_history : "has history"
    settlement_packet ||--o{ draft_allocation : "staged against"
    settlement_packet_item }o--o| billing_item : "receivable"
    settlement_packet_item }o--o| cash_receipt_split : "funded by"
    settlement_packet_item }o--o| cash_receipt_worksheet : "created (on release)"
    settlement_packet_item }o--o| participant_settlement : "created (on release)"
    draft_allocation }o--o| cash_receipt_split : "staged from"
    draft_allocation }o--o| billing_item : "staged against"

    settlement_packet {
        uuid settlement_packet_id PK
        varchar packet_name
        integer client_id FK
        decimal total_settlement_amt
        integer receivable_count
        varchar packet_status_cd
        varchar current_approver_role
        jsonb summary_custom_lines
        boolean mgmt_approval_required
        boolean signed_deposit_release_required
    }

    settlement_packet_item {
        uuid settlement_packet_item_id PK
        uuid settlement_packet_id FK
        integer billing_item_id FK
        integer cash_receipt_split_id FK
        decimal rev_amt_applied
        decimal pay_amt_applied
        decimal passthrough_amt_applied
        integer passthrough_party_id FK
        integer cash_receipt_worksheet_id
        integer participant_settlement_id
        varchar item_status_cd
    }

    draft_allocation {
        serial draft_allocation_id PK
        integer client_id
        integer cash_receipt_split_id
        integer billing_item_id
        decimal rev_amt
        decimal pay_amt
        decimal passthrough_amt
        integer passthrough_party_id
        decimal wht_amt
        varchar wht_type_cd
        uuid settlement_packet_id FK
    }

    settlement_packet_status_history {
        uuid settlement_packet_status_history_id PK
        uuid settlement_packet_id FK
        varchar from_status_cd
        varchar to_status_cd
        varchar action_cd
        varchar approver_role
        text comment_text
        integer action_by_user_id FK
        timestamp action_dt
    }

---

2.2 settlement_packet

The approval header for a Mass Settlement session. One packet per client per submit action. Groups splits and receivables for agent review before any accounting records are created.

Field	Type	Required	Default	Description
settlement_packet_id	uuid	Yes	Random	Primary key. UUID to support distributed generation.
packet_name	varchar(255)	Yes	—	Human-readable label. Not unique — the same client can legitimately have multiple packets per day (multiple deals/venues). Identity is the UUID, not the name.
client_id	integer	Yes	—	FK to party. The client (artist/talent) whose receivables are being settled.
total_settlement_amt	decimal(20,2)	Yes	0.00	Denormalized sum of all item amounts. Maintained in sync with settlement_packet_item rows by the application layer.
receivable_count	integer	Yes	0	Denormalized count of items. Maintained in sync with settlement_packet_item rows.
packet_status_cd	varchar(50)	Yes	—	Approval lifecycle status. See Section 3.1.
current_approver_role	varchar(50)	No	—	Role of the current required approver (e.g., AGENT). Null when not awaiting approval.
created_via_cd	varchar(20)	Yes	MS	Origin of the packet. MS = Mass Settlement screen.
summary_custom_lines	jsonb	No	[]	Operator-authored free-form lines for the Settlement Summary PDF. Shape: [{ id, label, amount }]. Used to document commission destinations (e.g., management splits) for parity with artist statements.
mgmt_approval_required	boolean	Yes	false	Soft-gate flag set at submit time. When true, a management approval document upload task surfaces in the approval History panel until the document is attached. Does not block status transitions.
signed_deposit_release_required	boolean	Yes	false	Soft-gate flag set at submit time. When true, a signed deposit release upload task surfaces in the History panel. Does not block status transitions.
submitted_dt / submitted_by_user_id	timestamp / integer	No	—	Populated when packet transitions to SUBMITTED.
approved_dt / approved_by_user_id	timestamp / integer	No	—	Populated when packet transitions to APPROVED_AGENT.
released_dt / released_by_user_id	timestamp / integer	No	—	Populated when packet transitions to RELEASED.
rejected_dt / rejected_by_user_id	timestamp / integer	No	—	Populated when packet is rejected.
rejection_reason	varchar(2000)	No	—	Free-text reason entered by the rejecting approver.
returned_dt / returned_by_user_id	timestamp / integer	No	—	Populated when a released packet is returned via the worksheet return flow.

NOTE

Pure audit columns (created_dt, updated_dt, created_by_user_id, updated_by_user_id) are omitted from the table above unless they carry business meaning.

---

2.3 draft_allocation

Lightweight staging for tentative cash-to-receivable assignments. Users park splits against receivables as placeholders before formally creating settlements. These records carry no accounting impact — they are pure bookkeeping until the operator clicks "Create Settlement."

Field	Type	Required	Default	Description
draft_allocation_id	serial	Yes	Auto	Primary key.
client_id	integer	Yes	—	The client whose packet this draft belongs to.
cash_receipt_split_id	integer	Yes	—	The split providing the funds.
billing_item_id	integer	Yes	—	The receivable being allocated against.
billing_item_detail_id_rev	integer	No	—	FK to the REV detail row on the billing item.
billing_item_detail_id_pay	integer	No	—	FK to the PAY detail row on the billing item.
rev_amt	numeric(15,2)	Yes	0	Amount allocated to the REV (commission) side.
pay_amt	numeric(15,2)	Yes	0	Amount allocated to the PAY (artist) side.
passthrough_amt	numeric(15,2)	Yes	0	Amount routed directly to a third party — not commission, not artist share.
passthrough_party_id	integer	No	—	FK to party. Recipient of the passthrough amount.
wht_amt	numeric(15,2)	Yes	0	Withholding tax amount: cash routed to a tax authority (e.g., US NRA WHT). Distinct from passthrough_amt — WHT carries compliance-reporting obligations and materializes as billing_item_tax records at settlement creation.
wht_type_cd	varchar(50)	No	—	Tax-type code driving how the WHT is materialized (e.g., WH_US_NRA, WH_UK_FEU).
settlement_packet_id	uuid	No	—	FK to settlement_packet. Null until the draft is associated with a packet.
created_by_user_id	integer	No	—	FK to users. Operator who staged the allocation.

IMPORTANT

Draft allocations are ephemeral. When the operator clicks "Create Settlement," the service reads all draft_allocation rows for the packet, converts them into settlement_packet_item rows, and deletes the draft records. Do not treat draft_allocation as a persistent ledger entry — it is a UI staging area only.

NOTE

The distinction between wht_amt and passthrough_amt: a passthrough is a generic third-party routing (e.g., co-manager share); WHT is a tax liability with compliance reporting. Both are allocated at draft time, but WHT materializes as billing_item_tax + a payout to the tax-authority party at settlement creation, while passthrough materializes as a direct payment_item of type P.

---

2.4 settlement_packet_item

One row per receivable per packet. Created when the operator clicks "Create Settlement," replacing the corresponding draft_allocation rows. Tracks the behind-the-scenes worksheet and settlement created during the MS release flow.

Field	Type	Required	Default	Description
settlement_packet_item_id	uuid	Yes	Random	Primary key.
settlement_packet_id	uuid	Yes	—	FK to settlement_packet (cascade delete).
billing_item_id	integer	Yes	—	FK to billing_item. The receivable being settled.
billing_item_detail_id_rev	integer	No	—	FK to the REV detail row.
billing_item_detail_id_pay	integer	No	—	FK to the PAY detail row.
cash_receipt_split_id	integer	No	—	FK to cash_receipt_split. The split funding this receivable.
rev_amt_applied	decimal(15,2)	No	—	REV amount applied.
pay_amt_applied	decimal(15,2)	No	—	PAY amount applied.
passthrough_amt_applied	decimal(15,2)	No	—	Passthrough amount applied.
passthrough_party_id	integer	No	—	FK to party. Passthrough recipient.
cash_receipt_worksheet_id	integer	No	—	Populated during createSettlements — the worksheet created for this split. Null until the packet is released.
participant_settlement_id	integer	No	—	Populated during createSettlements — the settlement created for this item. Null until the packet is released.
item_status_cd	varchar(20)	Yes	PENDING	Per-item processing status. See Section 3.2.

NOTE

Multiple items from the same split share a single worksheet — one worksheet per split grouping. cash_receipt_worksheet_id will be identical across all items that share a cash_receipt_split_id.

NOTE

cash_receipt_worksheet_id and participant_settlement_id are null until the packet reaches RELEASED and createSettlements runs. These are back-populated references without physical FK constraints to avoid circular dependency issues during the creation sequence.

NOTE

Pure audit columns (created_dt, updated_dt, created_by_user_id, updated_by_user_id) are omitted from the table above unless they carry business meaning.

---

2.5 settlement_packet_status_history

Immutable audit trail of every status transition on a packet. One row per action — never updated or deleted.

Field	Type	Required	Default	Description
settlement_packet_status_history_id	uuid	Yes	Random	Primary key.
settlement_packet_id	uuid	Yes	—	FK to settlement_packet (cascade delete).
from_status_cd	varchar(50)	No	—	Status before the transition. Null for the initial creation event.
to_status_cd	varchar(50)	Yes	—	Status after the transition.
action_cd	varchar(50)	Yes	—	The action taken. Values: SUBMIT, APPROVE, REJECT, RESUBMIT, RELEASE, RETURN.
approver_role	varchar(50)	No	—	Role of the actor (e.g., AGENT).
comment_text	text	No	—	Optional comment or rejection reason supplied by the actor.
action_by_user_id	integer	Yes	—	FK to users. The user who performed the action.
action_dt	timestamp	Yes	Now	When the action occurred.

---

3. Status Lifecycle

3.1 Packet Status (settlement_packet.packet_status_cd)

Status	Description	Allowed Transitions
IN_PROGRESS	Staging phase; draft allocations being built in the MS workspace. No materialized worksheets yet.	→ DRAFT
DRAFT	Materialized packet; worksheets and settlements created. Ready to submit. Also the state after rejection.	→ SUBMITTED
SUBMITTED	Submitted; pending Agent review.	→ APPROVED_AGENT, → REJECTED_AGENT
APPROVED_AGENT	Agent approved; pending Submitter final review.	→ APPROVED_SUBMITTER, → REJECTED_SUBMITTER
APPROVED_SUBMITTER	Submitter approved; pending CAM review.	→ APPROVED_CAM, → REJECTED_CAM
APPROVED_CAM	CAM approved; pending VP Finance. VP Finance approval auto-triggers release.	→ RELEASED
REJECTED_AGENT	Agent rejected. Returns to DRAFT for revision.	→ SUBMITTED (via resubmit)
REJECTED_SUBMITTER	Submitter rejected. Returns to DRAFT for revision.	→ SUBMITTED (via resubmit)
REJECTED_CAM	CAM rejected. Returns to DRAFT for revision.	→ SUBMITTED (via resubmit)
RELEASED	VP Finance approved; release auto-triggered. Worksheets approved, payment items live.	→ RETURNED
RETURNED	A released packet was returned via the worksheet return flow. Terminal.	—

stateDiagram-v2
    [*] --> IN_PROGRESS : Staging
    IN_PROGRESS --> DRAFT : Create Settlement (materialized)
    DRAFT --> SUBMITTED : Submit for Approval
    SUBMITTED --> APPROVED_AGENT : Agent approves
    SUBMITTED --> REJECTED_AGENT : Agent rejects
    APPROVED_AGENT --> APPROVED_SUBMITTER : Submitter approves
    APPROVED_AGENT --> REJECTED_SUBMITTER : Submitter rejects
    APPROVED_SUBMITTER --> APPROVED_CAM : CAM approves
    APPROVED_SUBMITTER --> REJECTED_CAM : CAM rejects
    APPROVED_CAM --> RELEASED : VP Finance approves (auto-releases)
    REJECTED_AGENT --> DRAFT : Revert to DRAFT
    REJECTED_SUBMITTER --> DRAFT : Revert to DRAFT
    REJECTED_CAM --> DRAFT : Revert to DRAFT
    RELEASED --> RETURNED : Worksheet return

Transition: DRAFT → SUBMITTED

• Trigger: Operator clicks "Submit for Approval."
• Side-effects: submitted_dt and submitted_by_user_id populated. mgmt_approval_required and signed_deposit_release_required flags set. History record written (SUBMIT). Document upload tasks surface in the History panel if either flag is true.

Transition: SUBMITTED → APPROVED_AGENT

• Trigger: Agent approves the packet.
• Side-effects: approved_dt and approved_by_user_id populated. History record written (APPROVE).

Transition: SUBMITTED → REJECTED_AGENT

• Trigger: Agent rejects the packet.
• Side-effects: rejection_reason populated. rejected_dt and rejected_by_user_id populated. History record written (REJECT).

Transition: REJECTED_AGENT → SUBMITTED

• Trigger: Operator revises and resubmits.
• Side-effects: History record written (RESUBMIT). Previous rejection fields remain on the packet for audit.

Transition: APPROVED_AGENT → RELEASED

• Trigger: Operator (or system) triggers release.
• Side-effects: createSettlements runs — one worksheet per split, settlements created for each item, settlement_packet_item.cash_receipt_worksheet_id and participant_settlement_id back-populated. All draft_allocation rows for the packet deleted. released_dt and released_by_user_id populated. History record written (RELEASE).

Transition: RELEASED → RETURNED

• Trigger: A worksheet created by this packet is returned.
• Side-effects: returned_dt and returned_by_user_id populated. History record written (RETURN). Downstream settlement reversal follows the standard worksheet return rules (see Settlements Data Model).

---

3.2 Settlement Packet Item Status (settlement_packet_item.item_status_cd)

Status	Description
PENDING	Item added to packet; no worksheet created yet.
APPLIED	Worksheet created; cash applied (worksheet in P status).
SETTLED	Settlement created; worksheet in T (Settled) status, parked for approval.
PASSTHROUGH	Direct payment — no settlement commission split.
RELEASED	Worksheet approved (T → A); payment items created.
FAILED	Error occurred during worksheet/settlement creation.

---

4. Business Rules

• Packet name is not an identity. packet_name is a human-readable label. Multiple packets for the same client on the same day are expected (multiple deals/venues). Identity is always settlement_packet_id.

• Draft allocations are ephemeral. draft_allocation rows are deleted on "Create Settlement." They are a UI staging area, not a ledger. Do not query them for accounting purposes.

• WHT vs. passthrough. draft_allocation.wht_amt is operator-allocated cash routed to a tax authority; it materializes as billing_item_tax + a payout at creation time. passthrough_amt is a generic third-party routing with no tax-compliance implications.

• One worksheet per split. During createSettlements, items sharing the same cash_receipt_split_id are grouped under a single worksheet. settlement_packet_item.cash_receipt_worksheet_id will be the same across items in the same split group.

• Soft-gate flags do not block transitions. mgmt_approval_required and signed_deposit_release_required surface document upload tasks in the UI but do not prevent approval or release. They are visibility signals, not hard gates.

• Status history is immutable. settlement_packet_status_history rows are never updated or deleted. Every state change — including resubmissions after rejection — produces a new row.

• Cascade delete on packet removal. settlement_packet_item and settlement_packet_status_history rows cascade-delete when a packet is deleted. draft_allocation rows reference the packet via FK but are managed explicitly by the application layer.

---

5. Code Master Values

5.1 PACKET_STATUS_CD

Code	Description	Behavior / When Used
IN_PROGRESS	Staging	Draft allocations being built. No materialized worksheets.
DRAFT	Materialized	Worksheets and settlements created. Ready to submit. Also post-rejection state. Default on materialization.
SUBMITTED	Submitted for approval	Awaiting Agent review.
APPROVED_AGENT	Agent approved	Awaiting Submitter final review.
APPROVED_SUBMITTER	Submitter approved	Awaiting CAM review.
APPROVED_CAM	CAM approved	Awaiting VP Finance. VP Finance approval auto-releases.
REJECTED_AGENT	Agent rejected	Returns to DRAFT for revision.
REJECTED_SUBMITTER	Submitter rejected	Returns to DRAFT for revision.
REJECTED_CAM	CAM rejected	Returns to DRAFT for revision.
RELEASED	Released	VP Finance approved; worksheets approved; payment items live. Read-only.
RETURNED	Returned	Post-release return via worksheet return flow. Terminal.

Default on creation: IN_PROGRESS (staging); advances to DRAFT on materialization.

---

5.2 ITEM_STATUS_CD

Code	Description	Behavior / When Used
PENDING	No worksheet yet	Default when item is added to packet.
APPLIED	Worksheet created	Cash applied; worksheet in P status.
SETTLED	Settlement created	Worksheet in T status; parked for approval.
PASSTHROUGH	Direct payment	No settlement commission split.
RELEASED	Worksheet approved	Payment items created; worksheet in A status.
FAILED	Error	Error during worksheet/settlement creation.

Default on creation: PENDING

---

5.3 ACTION_CD (status history)

Code	Description
SUBMIT	Initial submission for approval.
APPROVE	Agent approved.
REJECT	Agent rejected.
RESUBMIT	Operator resubmitted after rejection.
RELEASE	Packet released; settlements created.
RETURN	Packet returned post-release.

---

6. Cross-References

Document	Relationship
Settlements Data Model	Downstream of this model. settlement_packet_item.participant_settlement_id links to participant_settlement. The packet release triggers the standard D→T→A settlement lifecycle documented there.
Worksheets Data Model	settlement_packet_item.cash_receipt_worksheet_id links to cash_receipt_worksheet. MS creates one worksheet per split during release.
Billing Items Data Model	settlement_packet_item.billing_item_id and draft_allocation.billing_item_id reference the receivables being settled.
Cash Receipts Data Model	draft_allocation.cash_receipt_split_id and settlement_packet_item.cash_receipt_split_id reference the splits providing funds.