Deals, Sales Items and Payment Terms Data Model

1. Executive Summary

Purpose

The Deals, Sales Items and Payment Terms domain captures the foundational commercial agreements between UTA and the buyers who pay for a client's services. Every downstream financial operation — revenue recognition, billing, cash receipts, settlements, and payments — traces back to a deal. A deal establishes the gross revenue that a buyer will pay for a client's services. Sales items and payment terms arrive in sales blocks; those blocks are processed into revenue items and billing items — the receivables that the cash management workflow applies payments against.

Scope

Covered:

deal — Core agreement record: reference, name, date range, and active status
deal_party — Links a deal to its participating parties with commission structure and payment routing
sales_item — Revenue line item arriving through a sales_block batch; versioned via sales_item_ref + sales_item_ver
sales_item_party — Links a sales item to its parties with per-party commission terms
sales_meta_data — Extensible key-value metadata attached to a sales item via sales_item_ref
sales_block — Batch container for sales items and payment terms received for processing
payment_term — Payment schedule entry within the sales block pipeline; specifies due date and gross amount per buyer

Not covered (documented separately):

Revenue items (revenue_items, revenue_item_schedules) — see Billing Items Data Model
Billing items (billing_item, billing_item_detail) — see Billing Items Data Model
Parties (party, party_role, party_bank_account) — see Parties Data Model

2. Data Model

2.1 Entity-Relationship Diagram

mermaid

erDiagram
    deal ||--o{ deal_party : "has parties"
    deal ||--o{ sales_item : "has sales items"
    sales_block ||--o{ sales_item : "contains"
    sales_block ||--o{ payment_term : "contains"
    sales_item ||--o{ sales_item_party : "has parties"
    sales_item ||--o{ sales_meta_data : "has metadata (via sales_item_ref)"
    deal_party }o--|| party : "references"
    sales_item_party }o--|| party : "references"
    payment_term }o--|| party : "payment party"

NOTE

payment_term belongs to the sales block pipeline, grouped alongside sales_item within a sales_block. Its parent join is through sales_block_id, not directly through sales_item. The diagram above shows the grouping relationship via sales_block.

2.2 `deal`

The central record for a commercial agreement between UTA and a buyer on behalf of a client. Each deal has a unique reference, a name, a date range, and an active/inactive flag.

Field	Type	Required	Default	Description
`deal_id`	`serial`	Yes	Auto	Primary key. Referenced by `deal_party`, `sales_item`, and downstream `billing_item` records.
`deal_reference`	`varchar(500)`	No	—	External or business reference (Deal Part Reference). Used for agent-facing identification.
`deal_name`	`varchar(500)`	No	—	Name or title of the deal.
`deal_start_dt`	`date`	No	—	Start date of the deal.
`deal_end_dt`	`date`	No	—	End date of the deal.
`active_ind`	`boolean`	No	—	`true` = deal is currently active; `false` = deal is inactive.

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 `deal_party`

Links a deal to each participating party and records that party's commission structure and bank account for payment routing. A deal may have multiple parties (e.g., client, buyer, agent, attorney).

Field	Type	Required	Default	Description
`deal_party_id`	`serial`	Yes	Auto	Primary key.
`deal_id`	`integer`	No	—	FK to `deal.deal_id`. Every `deal_party` belongs to exactly one deal.
`party_id`	`integer`	No	—	FK to `party.party_id`. The party associated with the deal.
`party_role_type_cd`	`varchar(50)`	No	—	Party's role on the deal (e.g., `AGENT`, `CLIENT`, `BUYER`). See Section 5.
`deal_party_commission_flat_ind`	`boolean`	No	—	`true` = flat dollar commission; `false` = percentage-based commission.
`deal_party_commission_perc`	`decimal(7,4)`	No	—	Commission percentage off the net amount. Range: 0.0000 to 999.9999. See precision note below.
`deal_party_commission_amt`	`decimal(15,2)`	No	—	Total commission amount (flat amount or computed from percentage).
`bank_account_id`	`integer`	No	—	FK to `bank_account`. Used for payment routing to the party's bank account.

NOTE

deal_party_commission_perc uses decimal(7,4) (max 999.9999%), the wider commission percentage format used at the deal-party level. Compare with sales_item and sales_item_party, which use decimal(5,4) (max 9.9999%) for sales-item-level commission percentages.

2.4 `sales_item`

Represents a revenue line item that arrives through the sales block pipeline. Each sales item belongs to one sales_block and is associated with one deal. Sales items are versioned via sales_item_ref + sales_item_ver to support receiving updated data while preserving history.

Field	Type	Required	Default	Description
`sales_item_id`	`serial`	Yes	Auto	Primary key.
`sales_block_id`	`integer`	No	—	Logical FK to `sales_block.sales_block_id`. The ingest batch this item arrived in. Not enforced as a database FK.
`sales_item_ref`	`varchar(500)`	No	—	Stable reference identifier across versions. Used to link `sales_meta_data` records (soft join by value).
`sales_item_ver`	`integer`	No	—	Version number. Incremented each time an updated version of this item is sent.
`deal_id`	`integer`	No	—	Logical FK to `deal.deal_id`. Not enforced as a database FK.
`name`	`varchar(200)`	No	—	Name or description of the sales item.
`uta_entity_id`	`integer`	No	—	FK to `uta_entity`. The UTA legal entity that books the revenue.
`department_id`	`integer`	No	—	FK to `department`. The UTA department responsible for this deal.
`client_entity_id`	`integer`	No	—	FK to `party`. The client (artist) associated with this item.
`contracted_party_id`	`integer`	No	—	FK to `party`. The contracted party (loan-out or individual). May differ from `client_entity_id`.
`buyer_entity_id`	`integer`	No	—	FK to `party`. The buyer who will pay.
`agent_group_id`	`integer`	No	—	FK to the agent group responsible.
`currency_cd`	`varchar(10)`	No	—	ISO 4217 currency code for this item's amounts.
`gross_amt`	`decimal(15,2)`	No	—	Gross revenue amount for this sales item.
`uta_commission_type`	`varchar(50)`	No	—	Commission calculation method: `P` (Percent) or `F` (Flat). See Section 5.
`uta_commission_perc`	`decimal(5,4)`	No	—	UTA commission percentage. Range: 0.0000 to 9.9999. Used when `uta_commission_type` = `P`.
`uta_commission_amt`	`decimal(15,2)`	No	—	Computed or flat UTA commission amount.
`revenue_start_dt`	`date`	No	—	Revenue recognition start date.
`revenue_end_dt`	`date`	No	—	Revenue recognition end date.
`rev_rec_style_cd`	`varchar(1)`	No	—	Revenue recognition style (`I`, `M`, or `C`). See Section 5.
`revenue_date_status_cd`	`varchar(50)`	No	—	Whether revenue dates are confirmed or estimated. See Section 5.
`sales_item_status_cd`	`varchar(50)`	No	—	Status of the sales item (`U`, `C`, or `M`). See Section 3.
`service_country_cd`	`varchar(2)`	No	—	ISO 3166-1 alpha-2 country code where the service was performed. Drives tax jurisdiction determination.
`service_state_cd`	`varchar(10)`	No	—	State/province code for sub-national tax jurisdiction.

NOTE

Entity reference fields (uta_entity_id, department_id, client_entity_id, contracted_party_id, buyer_entity_id, agent_group_id) and deal_id, sales_block_id are logical foreign keys — they are not enforced with database REFERENCES constraints.

2.5 `sales_item_party`

Links a sales item to the parties involved and records per-party commission terms at the sales item level. One row per party per sales item.

Field	Type	Required	Default	Description
`sales_item_party_id`	`serial`	Yes	Auto	Primary key.
`sales_item_id`	`integer`	No	—	Logical FK to `sales_item.sales_item_id`. Not enforced as a database FK.
`party_id`	`integer`	No	—	Logical FK to `party.party_id`. Not enforced as a database FK.
`sales_item_party_commission_flat_ind`	`boolean`	No	—	`true` = flat dollar commission; `false` = percentage-based.
`sales_item_party_commission_perc`	`decimal(5,4)`	No	—	Commission percentage off the net amount. Range: 0.0000 to 9.9999.
`sales_item_party_commission_amt`	`decimal(15,2)`	No	—	Total commission amount (flat or computed).

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.6 `sales_meta_data`

Extensible key-value metadata attached to a sales item via sales_item_ref. Supports typed values (string and date) and optional date-bounded effective ranges. Used to carry supplementary attributes that do not fit the core sales_item schema.

Field	Type	Required	Default	Description
`sales_meta_data_id`	`serial`	Yes	Auto	Primary key.
`sales_item_ref`	`varchar(500)`	No	—	Soft join to `sales_item.sales_item_ref`. Matched by value — not a database FK. Targets all versions of the referenced item.
`meta_data_type_cd`	`varchar(50)`	No	—	Type or category of the metadata entry. Application-defined.
`meta_data_value`	`varchar(500)`	No	—	String value of the metadata.
`meta_data_date_value`	`date`	No	—	Date-typed value of the metadata. Alternative to `meta_data_value` for date fields.
`comment`	`varchar(2000)`	No	—	Free-text comment or notes on the metadata entry.
`start_dt`	`date`	No	—	Effective start date for this metadata entry.
`end_dt`	`date`	No	—	Effective end date for this metadata entry.

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.7 `sales_block`

A batch container for sales data received for processing. A sales block groups one or more sales_item records and their associated payment_term records. The block tracks its processing status and any errors encountered during processing.

Field	Type	Required	Default	Description
`sales_block_id`	`serial`	Yes	Auto	Primary key. Referenced by `sales_item.sales_block_id` and `payment_term.sales_block_id`.
`process_status_cd`	`varchar(1)`	No	—	Processing status (`U`, `P`, `X`, or `F`). See Section 3.
`error_description`	`varchar(500)`	No	—	Error message if processing failed. Populated when `process_status_cd` = `F`.

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.8 `payment_term`

A payment schedule entry within the sales block pipeline. Each payment_term specifies one installment: who pays, how much, and when. Payment terms within a sales_block are versioned via payment_term_ref + payment_term_ver.

Field	Type	Required	Default	Description
`payment_term_id`	`serial`	Yes	Auto	Primary key.
`sales_block_id`	`integer`	No	—	Logical FK to `sales_block.sales_block_id`. Groups payment terms with their associated sales items within the same ingest batch. Not enforced as a database FK.
`payment_term_ref`	`varchar(500)`	No	—	Stable reference identifier across versions. Linked to `billing_item.payment_term_ref` downstream.
`payment_term_ver`	`integer`	No	—	Version number. Incremented each time an updated version of this term is sent.
`name`	`varchar(200)`	No	—	Name or description of the payment term.
`payment_party_id`	`integer`	No	—	FK to `party.party_id`. The buyer or party expected to pay this installment.
`gross_amt`	`decimal(15,2)`	No	—	Gross amount due for this payment term. Becomes the basis for the downstream `billing_item.gross_amt`.
`due_dt`	`date`	No	—	Due date for this payment installment.
`due_date_status_cd`	`varchar(50)`	No	—	Whether the due date is confirmed or estimated. Application-defined values.

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

3.1 Sales Block Processing Status

sales_block.process_status_cd tracks the result of processing the ingest batch into revenue items and billing items.

Status	Code	Description	Allowed Transitions
Unprocessed	`U`	Block has been received but not yet processed	→ Processed (`P`), → Skipped (`X`), → Failed (`F`)
Processed	`P`	Block successfully processed; revenue items and billing items created	—
Skipped	`X`	Block was intentionally skipped (e.g., duplicate, invalid)	—
Failed	`F`	Processing failed; see `error_description` for details	—

mermaid

stateDiagram-v2
    [*] --> U : Block received
    U --> P : Processing succeeds
    U --> X : Block skipped
    U --> F : Processing fails

Transition: Unprocessed → Processed

Trigger: The block processing job runs and successfully creates downstream revenue and billing records.
Preconditions: Block process_status_cd is U. No prior processing attempt has run for this block.
Side-effects: revenue_items and billing_item / billing_item_detail records created for each sales item and payment term in the block. process_status_cd set to P.

Transition: Unprocessed → Skipped

Trigger: The block processing job determines the block should not be processed (e.g., identified as a duplicate or otherwise invalid).
Preconditions: Block process_status_cd is U.
Side-effects: process_status_cd set to X. No downstream records created.

Transition: Unprocessed → Failed

Trigger: The block processing job encounters an unrecoverable error.
Preconditions: Block process_status_cd is U.
Side-effects: process_status_cd set to F. error_description populated with the failure reason. No downstream records created.

3.2 Sales Item Status

sales_item.sales_item_status_cd uses the REVENUE_ITEM_STATUS_CD code master.

Status	Code	Description	Allowed Transitions
Unconfirmed	`U`	Sales item is pending confirmation	→ Confirmed (`C`), → Missed (`M`)
Confirmed	`C`	Sales item has been confirmed; revenue will be recognized	—
Missed	`M`	Sales item did not materialize; revenue will not be recognized	—

mermaid

stateDiagram-v2
    [*] --> U : Sales item created
    U --> C : Confirmed
    U --> M : Missed

NOTE

The Final attribute (stored in code_attribute) marks C and M as terminal states. U is the only non-final status. This attribute is used in downstream revenue recognition logic to determine whether a sales item should be included in billing.

Transition: Unconfirmed → Confirmed

Trigger: User or upstream system confirms that the engagement will take place.
Preconditions: sales_item_status_cd is U.
Side-effects: sales_item_status_cd set to C. Downstream revenue recognition may proceed.

Transition: Unconfirmed → Missed

Trigger: User or upstream system marks the engagement as not materializing.
Preconditions: sales_item_status_cd is U.
Side-effects: sales_item_status_cd set to M. Revenue will not be recognized for this item.

3.3 Revenue Date Status

sales_item.revenue_date_status_cd uses the REVENUE_ITEM_DATE_STATUS_CD code master.

Status	Code	Description	Allowed Transitions
Unconfirmed	`U`	Revenue dates are estimated and not yet confirmed	→ Confirmed (`C`)
Confirmed	`C`	Revenue dates have been confirmed	—

Transition: Unconfirmed → Confirmed

Trigger: User confirms the revenue start and end dates.
Preconditions: revenue_date_status_cd is U.
Side-effects: revenue_date_status_cd set to C. Confirmed dates are used for revenue recognition scheduling.

3.4 Deal Active Status

The deal table uses a simple active_ind boolean rather than a status code lifecycle. A deal is either active (true) or inactive (false). There are no formal status transitions or lifecycle guards — the flag is updated in-place by users.

4. Validation & Database Constraints

Unique Constraints

Table	Constraint	Columns	Business Rule
(none)	—	—	No unique constraints are defined on these tables beyond the primary keys.

Business Validation

Logical FK integrity: sales_item.deal_id, sales_item.sales_block_id, sales_item_party.sales_item_id, sales_item_party.party_id, and all entity references (uta_entity_id, department_id, client_entity_id, contracted_party_id, buyer_entity_id, agent_group_id) are logical relationships matched by value. They are not enforced as database FK constraints. Application code is responsible for integrity.
sales_meta_data soft join: sales_meta_data.sales_item_ref is matched by value to sales_item.sales_item_ref. It targets all versions of the referenced item (i.e., all rows sharing the same sales_item_ref), not a specific sales_item_id.
Versioning via sales_item_ref + sales_item_ver: A given version of a sales item is uniquely identified by the combination of sales_item_ref and sales_item_ver. No database unique constraint enforces this; uniqueness is maintained by the application.
Commission precision: deal_party_commission_perc uses decimal(7,4) (max 999.9999%). sales_item and sales_item_party commission percentages use decimal(5,4) (max 9.9999%). The wider precision on deal_party is intentional and supports override scenarios.

5. Code Master Values

All code values are stored in the code_master table with code_master_type identifying the code family, unless otherwise noted.

5.1 `COMMISSION_TYPE_CD`

Used by sales_item.uta_commission_type to determine how UTA's commission is calculated.

Code	Description	Behavior / When Used
`P`	Percent	Commission computed as `gross_amt × uta_commission_perc`. `uta_commission_amt` stores the computed result.
`F`	Flat	Commission is a fixed dollar amount stored directly in `uta_commission_amt`. `uta_commission_perc` is ignored.

Default on creation: Not defaulted — must be provided.

5.2 `REVENUE_ITEM_STATUS_CD`

Used by sales_item.sales_item_status_cd.

Code	Description	Behavior / When Used
`U`	Unconfirmed	Default. Sales item is pending confirmation. Revenue recognition is not yet triggered.
`C`	Confirmed	Sales item confirmed. Revenue recognition proceeds. Terminal state (`Final` = true in `code_attribute`).
`M`	Missed	Engagement did not materialize. Revenue will not be recognized. Terminal state (`Final` = true in `code_attribute`).

Default on creation: U

5.3 `REVENUE_ITEM_DATE_STATUS_CD`

Used by sales_item.revenue_date_status_cd.

Code	Description	Behavior / When Used
`U`	Unconfirmed	Revenue dates are estimated. Revenue recognition scheduling uses estimated dates.
`C`	Confirmed	Revenue dates have been confirmed. Revenue recognition scheduling uses confirmed dates.

Default on creation: U

5.4 `REVENUE_ITEM_REC_STYLE_CD`

Used by sales_item.rev_rec_style_cd to govern how revenue is spread over time.

Code	Description	Behavior / When Used
`I`	Immediate	All revenue recognized at once, on the start date.
`M`	Monthly	Revenue spread evenly across months in the `revenue_start_dt` to `revenue_end_dt` range.
`C`	Cash	Revenue recognized when cash is received (i.e., when a cash receipt is applied).

Default on creation: Not defaulted — must be provided.

5.5 `SALES_BLOCK_STATUS_CD`

Used by sales_block.process_status_cd to track batch processing status.

Code	Description	Behavior / When Used
`U`	Unprocessed	Default. Block received and awaiting processing job.
`P`	Processed	Block successfully processed; downstream records created. Terminal state.
`X`	Skipped	Block intentionally skipped (duplicate or invalid). Terminal state.
`F`	Failed	Processing failed; `error_description` contains the reason. Terminal state.

Default on creation: U

5.6 `PARTY_ROLE_TYPE_CD`

Used by deal_party.party_role_type_cd to identify a party's role on the deal. Values are stored in code_master.

Code	Description	Behavior / When Used
`AGENT`	Agent	UTA agent responsible for the deal.
`CLIENT`	Client	The talent or artist who is the subject of the deal.
`BUYER`	Buyer	The buyer who will pay.
`ATT`	Attorney	Client's attorney.
`BM`	Business Manager	Client's business manager.
`ABM`	Accountant / Business Manager	Combined accountant and business manager role.
`ACC`	Accountant	Client's accountant.
`MC`	Management Company	Personal management company.
`MGR`	Manager	Client's personal manager.
`PM`	Personal Manager	Personal manager (alternate code).
`BA`	Business Affairs	Business affairs representative.
`BP`	Business Partner	Business partner.
`LO`	Loanout	Loanout entity for the client.
`PROD`	Producer	Producer associated with the deal.
`PUB`	Publicist	Client's publicist.
`NET`	Network	Network or broadcaster.
`PC`	Production Company	Production company.
`REC`	Record Company	Record label or record company.
`STU`	Studio	Film or television studio.
`UTA`	UTA	UTA itself as a party.
`UTAP`	UTA Package	UTA package deal role.
`UKF`	UK FEU	UK Fees and Earnings Unit role, used for UK tax withholding.
`PRM`	PR / Media	Public relations or media role.
`P`	Participant	Generic participant.
`OTH`	Other	Catch-all for roles not listed above.

Default on creation: Not defaulted — must be assigned when creating the deal_party record.

NOTE

Alternate codes (AGT, CLNT, BUY) exist in some legacy data. They map to the same roles as AGENT, CLIENT, and BUYER respectively. New records should use the canonical codes.

6. Cross-References

Document	Relationship
Billing Items Data Model	`billing_item.deal_id` → `deal.deal_id`. `billing_item.payment_term_ref` matches `payment_term.payment_term_ref` by value. `revenue_items.sales_item_ref` matches `sales_item.sales_item_ref` by value. Sales block processing creates `revenue_items` and `billing_item` / `billing_item_detail` records for each payment term in the block.
Parties Data Model	`deal_party.party_id` → `party.party_id`. `sales_item_party.party_id` → `party.party_id`. `payment_term.payment_party_id` → `party.party_id`. Entity references `client_entity_id`, `contracted_party_id`, `buyer_entity_id` on `sales_item` all reference `party.party_id`.
Tax & Withholding Data Model	`sales_item.service_country_cd` and `service_state_cd` drive tax jurisdiction determination for withholding calculations on downstream worksheets.

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Deals, Sales Items and Payment Terms Data Model ​

1. Executive Summary ​

Purpose ​

Scope ​

2. Data Model ​

2.1 Entity-Relationship Diagram ​

2.2 deal ​

2.3 deal_party ​

2.4 sales_item ​

2.5 sales_item_party ​

2.6 sales_meta_data ​

2.7 sales_block ​

2.8 payment_term ​

3. Status Lifecycle ​

3.1 Sales Block Processing Status ​

Transition: Unprocessed → Processed ​

Transition: Unprocessed → Skipped ​

Transition: Unprocessed → Failed ​

3.2 Sales Item Status ​

Transition: Unconfirmed → Confirmed ​

Transition: Unconfirmed → Missed ​

3.3 Revenue Date Status ​

Transition: Unconfirmed → Confirmed ​

3.4 Deal Active Status ​

4. Validation & Database Constraints ​

Unique Constraints ​

Business Validation ​

5. Code Master Values ​

5.1 COMMISSION_TYPE_CD ​

5.2 REVENUE_ITEM_STATUS_CD ​

5.3 REVENUE_ITEM_DATE_STATUS_CD ​

5.4 REVENUE_ITEM_REC_STYLE_CD ​

5.5 SALES_BLOCK_STATUS_CD ​

5.6 PARTY_ROLE_TYPE_CD ​

6. Cross-References ​

Deals, Sales Items and Payment Terms Data Model

1. Executive Summary

Purpose

Scope

2. Data Model

2.1 Entity-Relationship Diagram

2.2 `deal`

2.3 `deal_party`

2.4 `sales_item`

2.5 `sales_item_party`

2.6 `sales_meta_data`

2.7 `sales_block`

2.8 `payment_term`

3. Status Lifecycle

3.1 Sales Block Processing Status

Transition: Unprocessed → Processed

Transition: Unprocessed → Skipped

Transition: Unprocessed → Failed

3.2 Sales Item Status

Transition: Unconfirmed → Confirmed

Transition: Unconfirmed → Missed

3.3 Revenue Date Status

Transition: Unconfirmed → Confirmed

3.4 Deal Active Status

4. Validation & Database Constraints

Unique Constraints

Business Validation

5. Code Master Values

5.1 `COMMISSION_TYPE_CD`

5.2 `REVENUE_ITEM_STATUS_CD`

5.3 `REVENUE_ITEM_DATE_STATUS_CD`

5.4 `REVENUE_ITEM_REC_STYLE_CD`

5.5 `SALES_BLOCK_STATUS_CD`

5.6 `PARTY_ROLE_TYPE_CD`

6. Cross-References