Invoices and Statements Data Model

1. Executive Summary

Purpose

The Invoices and Statements domain manages the generation, tracking, and storage of two categories of outbound financial documents: invoices and statements. Invoices are formal payment requests produced from billing items and transmitted to buyers or clients. Statements are summary PDF documents that consolidate financial activity for a client, settlement, or accounts-receivable aging view over a date range. Both document types produce PDF artifacts stored externally (object storage), with metadata retained in the database for retrieval, audit, and lifecycle management. This domain sits downstream of billing and upstream of cash collection: once billing items exist with confirmed amounts, invoices are generated; once payments flow through worksheets and settlements, statements are produced.

Scope

Covered:

invoice — Header record for a generated invoice, linking a set of billing items to a recipient, currency, and UTA entity.
invoice_number_sequence — Per-entity, per-year sequential counter used to generate unique invoice numbers.
invoice_bank_details — Payment instructions (wire, ACH, BACS, IBAN) displayed on invoice PDFs, keyed by UTA entity and currency.
invoice_correction_history — Audit trail for correction events (start, publish, cancel) on issued invoices.
credit_memo — Unified post-invoice adjustment document (full credit, debit, or credit).
credit_memo_line_item — Per-billing-item-detail breakdown within a credit memo.
credit_memo_number_sequence — Per-invoice uniqueness counter for credit memo numbers.
invoice_replacement_link — Three-way bridge linking a voided invoice, its full-credit adjustment, and the replacement invoice created by Cancel & Reissue.
generated_statement — Metadata and storage coordinates for generated PDF statements of any type.

Not covered (documented separately):

billing_item, billing_item_detail — The receivables that invoices are generated from. See Billing Items Data Model.
participant_settlement, payment_item — Settlement and payment records that settlement statements summarize. See Settlements Data Model.
party, address — Recipient and bank address records. See Parties Data Model.

2. Data Model

2.1 Entity-Relationship Diagram

mermaid

erDiagram
    invoice ||--o{ billing_item_document : "referenced via document_id (polymorphic)"
    invoice }o--|| uta_entity : "issued by"
    invoice }o--|| party : "recipient_party_id"
    invoice }o--o| party : "contracted_party_id"
    invoice ||--o{ invoice_correction_history : "has corrections"
    invoice ||--o{ credit_memo : "has adjustments"
    invoice }o--o| invoice : "reissued_from_invoice_id (self)"
    credit_memo ||--o{ credit_memo_line_item : "has line items"
    invoice_replacement_link }o--|| invoice : "original_invoice_id"
    invoice_replacement_link }o--|| invoice : "replacement_invoice_id"
    invoice_replacement_link }o--|| credit_memo : "full_credit_adjustment_id"
    invoice_number_sequence }o--|| uta_entity : "sequence per entity"
    invoice_bank_details }o--|| uta_entity : "bank details per entity"
    invoice_bank_details }o--o| address : "bank_address_id"
    generated_statement }o--o| party : "client_id"
    billing_item_document }o--|| billing_item : "billing_item_id"

    invoice {
        serial invoice_id PK
        varchar invoice_number UK
        integer uta_entity_id FK
        date issue_date
        date due_date
        varchar billing_terms_cd
        varchar invoice_type_cd
        varchar invoice_recipient_cd
        boolean multi_client_ind
        varchar currency_cd
        integer recipient_party_id FK
        varchar contracted_party_id FK
        varchar po_number
        varchar email_recipients
        boolean hide_billing_due_date_on_pdf
        boolean hide_service_period_on_pdf
        decimal total_gross_amt
        decimal total_line_tax_amt
        decimal withholding_amt
        decimal total_amt_due
        date invoice_sent_dt
        varchar status_cd
        integer reissued_from_invoice_id FK
    }

    credit_memo {
        serial credit_memo_id PK
        varchar adjustment_number UK
        integer invoice_id FK
        varchar adjustment_type_cd
        varchar reason_code_cd
        varchar status_cd
        date issue_date
        decimal subtotal
        decimal tax_total
        decimal total
    }

    credit_memo_line_item {
        serial credit_memo_line_item_id PK
        integer credit_memo_id FK
        integer billing_item_detail_id FK
        integer invoice_line_item_id FK
        decimal gross_amt
        decimal commission_amt
        decimal tax_amt
    }

    invoice_replacement_link {
        serial invoice_replacement_link_id PK
        integer original_invoice_id FK
        integer full_credit_adjustment_id FK
        integer replacement_invoice_id FK
        varchar reason
    }

    invoice_correction_history {
        uuid invoice_correction_history_id PK
        integer invoice_id FK
        integer correction_version
        varchar action_cd
        varchar performed_by
        timestamp performed_dt
        jsonb header_snapshot
    }

    invoice_number_sequence {
        serial invoice_number_sequence_id PK
        integer uta_entity_id FK
        integer current_year
        integer current_sequence
        varchar prefix
    }

    invoice_bank_details {
        serial invoice_bank_details_id PK
        integer uta_entity_id FK
        varchar currency_cd
        varchar bank_name
        integer bank_address_id FK
        varchar account_number
        varchar account_name
        varchar aba_routing_number
        varchar swift_code
        varchar iban
        varchar sort_code
        boolean is_active
    }

    generated_statement {
        serial generated_statement_id PK
        varchar statement_type_cd
        varchar statement_ref
        integer client_id FK
        date date_from
        date date_to
        varchar currency_cd
        varchar s3_bucket
        varchar s3_key
        varchar file_path
        varchar file_name
        integer file_size_bytes
        varchar generation_status_cd
        text error_message
        jsonb metadata
    }

    billing_item_document {
        serial billing_item_document_id PK
        integer billing_item_id FK
        varchar document_type_cd
        integer document_id
        varchar payment_term_ref
    }

    uta_entity {
        serial uta_entity_id PK
    }

    party {
        serial party_id PK
    }

    address {
        serial address_id PK
    }

    billing_item {
        serial billing_item_id PK
    }

2.2 `invoice`

Represents a generated invoice document. Each invoice belongs to exactly one UTA entity, one currency, and one recipient party. Invoices are created from selected billing item details and can be either Commission invoices (sent to clients for UTA's revenue share) or Total Due invoices (sent to buyers for the full amount owed).

Field	Type	Required	Default	Description
`invoice_id`	`serial`	Yes	Auto	Primary key. Referenced by `billing_item_document.document_id` for invoice types `CI` and `BI`.
`invoice_number`	`varchar(50)`	Yes	—	Human-readable invoice number. Format: `{prefix}-{year}-{sequence}`, e.g., `UTA_US-2026-000001`. Globally unique via `uq_invoice_number` constraint.
`uta_entity_id`	`integer`	Yes	—	FK to `uta_entity`. The UTA legal entity issuing this invoice. All billing items on the invoice must share this entity.
`issue_date`	`date`	Yes	—	Date the invoice was generated.
`due_date`	`date`	Yes	—	Payment deadline, calculated from `billing_terms_cd` offset applied to `issue_date`.
`billing_terms_cd`	`varchar(20)`	No	`'DUE_RECEIPT'`	Payment terms code (`BILLING_TERMS_CD`). Determines how many days after `issue_date` payment is due. See Section 5.
`invoice_type_cd`	`varchar(20)`	Yes	—	Type of invoice (`INVOICE_TYPE_CD`). `COMMISSION` invoices cover REV billing item details; `TOTAL_DUE` invoices cover PAY billing item details. See Section 5.
`invoice_recipient_cd`	`varchar(20)`	Yes	—	Who the invoice is addressed to (`INVOICE_RECIPIENT_CD`). See Section 5.
`multi_client_ind`	`boolean`	No	`false`	`true` when the invoice combines billing items from multiple clients. Only permitted for US entities with `invoice_recipient_cd = 'BUYER'`. UK entities always have `false`.
`currency_cd`	`varchar(10)`	Yes	—	Currency of the invoice (`CURRENCY_CD`). All billing items on the invoice must share this currency.
`recipient_party_id`	`integer`	Yes	—	FK to `party`. The bill-to party (client or buyer depending on `invoice_recipient_cd`).
`recipient_address_id`	`varchar(100)`	No	—	`auth_address_id` from the party addresses system, identifying the recipient's mailing address for the PDF.
`contracted_party_id`	`integer`	No	—	FK to `party`. The talent or artist for UK Total Due invoices where the contracted party differs from the recipient.
`contracted_address_id`	`varchar(100)`	No	—	`auth_address_id` for the contracted party's address, used on UK Total Due invoice PDFs.
`po_number`	`varchar(100)`	No	—	Optional purchase order reference number provided by the buyer. Displayed on the PDF.
`email_recipients`	`varchar(2000)`	No	—	JSON array of primary email addresses for invoice delivery. Informational — delivery is handled externally.
`email_cc_recipients`	`varchar(2000)`	No	—	JSON array of CC email addresses.
`hide_billing_due_date_on_pdf`	`boolean`	No	`false`	When `true`, the Billing Due Date column is hidden on the PDF.
`hide_service_period_on_pdf`	`boolean`	No	`false`	When `true`, the Service Period column is hidden on the PDF.
`hide_payments_applied_on_pdf`	`boolean`	No	`false`	When `true`, the Payments Applied and Balance Due sections are hidden on the PDF.
`hide_tax_notice_ind`	`boolean`	No	`false`	When `true`, the informational tax notice section is hidden. Applies to `TOTAL_DUE` invoices only.
`logo_type_cd`	`varchar(20)`	No	—	Overrides the subsidiary-based logo default on the PDF. Values: `ENTITY_LOGO`, `CLIENT_LOGO`.
`additional_notes`	`varchar(2000)`	No	—	Freeform notes printed on the PDF after the Amount Due line, before Payment Instructions.
`invoice_bank_details_id`	`integer`	No	—	FK to `invoice_bank_details`. Overrides the default entity+currency bank details for this invoice's PDF.
`contracted_party_bank_account_id`	`integer`	No	—	FK to `party_bank_account`. For Total Due invoices where the buyer pays the talent directly — bank details for the contracted party.
`include_check_instructions`	`boolean`	No	`false`	When `true`, check mailing instructions are shown on the PDF.
`check_instructions_only`	`boolean`	No	`false`	When `true`, bank wire/ACH instructions are hidden and only check instructions appear.
`check_mailing_instructions_id`	`integer`	No	—	FK to `check_mailing_instructions`. UTA check mailing address for USD invoices with non-UK entities.
`check_party_address_id`	`varchar(100)`	No	—	`party_addresses.party_addresses_id` for the contracted party's address option on check instructions.
`total_gross_amt`	`decimal(15,2)`	Yes	—	Total invoice amount before tax and withholding. Sum of `billing_item_detail.gross_amt` for all details on this invoice.
`total_commission_amt`	`decimal(15,2)`	No	—	UTA commission total. Populated for `COMMISSION` invoices only; `null` for `TOTAL_DUE` invoices.
`total_line_tax_amt`	`decimal(15,2)`	No	—	Sum of per-line taxes (VAT, HST, GST) across all invoice line items.
`withholding_type_cd`	`varchar(30)`	No	—	Type of withholding applied at the invoice level. Values: `WITHHOLDING_FEU`, `WITHHOLDING_NRA`. See Section 5.
`withholding_rate`	`decimal(7,4)`	No	—	Withholding rate applied (e.g., `0.2000` = 20%).
`withholding_amt`	`decimal(15,2)`	No	—	Total withholding amount deducted from `total_amt_due`.
`withholding_basis_amt`	`decimal(15,2)`	No	—	Amount that withholding was calculated on.
`withholding_override_ind`	`boolean`	No	`false`	`true` when the user manually modified the withholding amount.
`withholding_original_amt`	`decimal(15,2)`	No	—	System-calculated withholding before any user override. Preserved for audit.
`total_amt_due`	`decimal(15,2)`	No	—	Final amount due: `total_gross_amt + total_line_tax_amt - withholding_amt`.
`invoice_sent_dt`	`date`	No	—	Date the invoice was actually transmitted to the buyer. Distinct from `issue_date` (when it was generated). Null until sent. Starts the WGA 14-day late-pay clock for guild reporting.
`status_cd`	`varchar(20)`	Yes	`'DRAFT'`	Invoice lifecycle status (`INVOICE_STATUS_CD`). See Section 3.
`sent_by_user_id`	`integer`	No	—	FK to `users`. User who issued/sent the invoice.
`voided_by_user_id`	`integer`	No	—	FK to `users`. User who voided the invoice.
`voided_dt`	`date`	No	—	Date the invoice was voided.
`reissued_from_invoice_id`	`integer`	No	—	Self-referential FK to `invoice`. Populated on a replacement invoice created by the Cancel & Reissue flow — points to the original voided invoice. See `invoice_replacement_link` for the full three-way relationship.

NOTE

Pure audit columns (created_by, created_dt, updated_by, updated_dt) are omitted from detailed descriptions unless they carry business meaning. All transaction tables include these fields automatically.

2.3 `invoice_number_sequence`

Tracks sequential invoice numbers per UTA entity per calendar year. Each entity has its own number series with a configurable prefix. The sequence counter increments atomically on each invoice creation and resets to zero at the start of each new year.

Field	Type	Required	Default	Description
`invoice_number_sequence_id`	`serial`	Yes	Auto	Primary key.
`uta_entity_id`	`integer`	Yes	—	FK to `uta_entity`. The entity this sequence belongs to.
`current_year`	`integer`	Yes	—	Calendar year for this sequence counter. Unique per entity via `uq_invoice_seq_entity_year`.
`current_sequence`	`integer`	Yes	`0`	Last-used sequence number for this entity and year. Incremented atomically when a new invoice is created.
`prefix`	`varchar(10)`	Yes	—	Invoice number prefix for this entity. Examples: `UTA_US`, `UTA_UK`, `ML`, `UTA_S`, `RC`. Derived from `uta_entity.invoice_prefix`.

NOTE

Pure audit columns (created_by, created_dt, updated_by, updated_dt) are omitted from this table unless they carry business meaning.

2.4 `invoice_bank_details`

Stores bank payment instructions printed on invoice PDFs. Each record represents the bank details for a specific UTA entity and currency combination. This table is distinct from bank_account records used for cash receipt and outbound payment processing — invoice_bank_details exists solely for document presentation.

Field	Type	Required	Default	Description
`invoice_bank_details_id`	`serial`	Yes	Auto	Primary key.
`uta_entity_id`	`integer`	Yes	—	FK to `uta_entity`. The entity these bank details belong to. Unique per entity + currency via `uq_invoice_bank_entity_currency`.
`currency_cd`	`varchar(10)`	Yes	—	Currency these bank details apply to (`CURRENCY_CD`).
`bank_name`	`varchar(200)`	Yes	—	Name of the bank as it appears on the invoice PDF.
`bank_address_id`	`integer`	No	—	FK to `address`. Physical address of the bank branch. Joined fields appear on the PDF as the bank's mailing address.
`account_number`	`varchar(50)`	Yes	—	Bank account number.
`account_name`	`varchar(200)`	No	—	Account holder name displayed on the invoice.
`aba_routing_number`	`varchar(20)`	No	—	US domestic routing number (9 digits). Used for USD wire/ACH instructions.
`swift_code`	`varchar(20)`	No	—	SWIFT/BIC code (8–11 characters). Used for international wire instructions.
`iban`	`varchar(50)`	No	—	International Bank Account Number (up to 34 characters). Used for European payment instructions.
`sort_code`	`varchar(10)`	No	—	UK sort code (6 digits, formatted `XX-XX-XX`). Used for BACS payment instructions.
`is_active`	`boolean`	Yes	`true`	Whether these bank details are currently in use. Inactive records are retained for historical PDFs.

NOTE

Pure audit columns (created_by, created_dt, updated_by, updated_dt) are omitted from this table unless they carry business meaning.

2.5 `invoice_correction_history`

Immutable audit trail of correction events on an invoice. One row per event — never updated or deleted. A header snapshot captures correctable display fields at the time of each event so a full diff is available between consecutive entries.

Field	Type	Required	Default	Description
`invoice_correction_history_id`	`serial`	Yes	Auto	Primary key.
`invoice_id`	`integer`	Yes	—	FK to `invoice`.
`correction_version`	`integer`	Yes	—	Increments with each new correction round on the same invoice (1, 2, 3…).
`action_cd`	`varchar(30)`	Yes	—	Event type. Values: `CORRECTION_STARTED`, `CORRECTION_PUBLISHED`, `CORRECTION_CANCELLED`.
`performed_by`	`varchar(100)`	Yes	—	Username of the user who performed the action.
`performed_by_user_id`	`integer`	Yes	—	FK to `users`.
`performed_dt`	`timestamp`	Yes	Now	When the action occurred.
`header_snapshot`	`jsonb`	No	—	Snapshot of correctable display fields at the time of the action: `poNumber`, `emailRecipients`, `emailCcRecipients`, `hideBillingDueDateOnPdf`, `hideServicePeriodOnPdf`, `logoTypeCd`, `additionalNotes`, `invoiceBankDetailsId`, `contractedPartyBankAccountId`, `includeCheckInstructions`, `checkInstructionsOnly`, `checkMailingInstructionsId`, `checkPartyAddressId`. Financial fields are excluded — they cannot be changed via correction.

NOTE

To reconstruct what changed in a correction round, diff the header_snapshot of the CORRECTION_PUBLISHED row against the CORRECTION_STARTED row for the same correction_version.

2.6 `credit_memo`

A unified post-invoice adjustment document. Replaces the former separate credit-memo-for-void and supplemental-invoice mechanisms with a single entity covering all three post-issuance change types.

Field	Type	Required	Default	Description
`credit_memo_id`	`serial`	Yes	Auto	Primary key.
`adjustment_number`	`varchar(80)`	Yes	—	Human-readable number. Format: `CM-{invoiceNumber}` for credits; `DM-{invoiceNumber}` for debits. Globally unique.
`invoice_id`	`integer`	Yes	—	FK to `invoice`. The original invoice this adjustment belongs to.
`adjustment_type_cd`	`varchar(20)`	Yes	—	Type discriminator. Values: `FULL_CREDIT`, `CREDIT`, `DEBIT`. See Section 5.
`reason_code_cd`	`varchar(30)`	No	—	Reason for the adjustment. Values: `FULL_CANCELLATION`, `PRICE_CORRECTION`, `INCORRECT_VAT`, `OTHER`.
`reason_text`	`varchar(500)`	No	—	Free-text elaboration on the reason.
`status_cd`	`varchar(20)`	Yes	`DRAFT`	Lifecycle status. Values: `DRAFT`, `ISSUED`. No backward transitions — `ISSUED` = sent to recipient.
`issue_date`	`date`	Yes	—	Date the adjustment was issued.
`uta_entity_id`	`integer`	Yes	—	FK to `uta_entity`. Denormalized from the original invoice for fast lookup and PDF rendering.
`recipient_party_id`	`integer`	Yes	—	FK to `party`. Denormalized from the original invoice.
`currency_cd`	`varchar(10)`	Yes	—	Currency. Denormalized from the original invoice.
`invoice_type_cd`	`varchar(20)`	Yes	—	`COMMISSION` or `TOTAL_DUE`. Denormalized from the original invoice.
`subtotal`	`decimal(15,2)`	Yes	—	Absolute adjustment amount before tax. Always a positive value.
`tax_total`	`decimal(15,2)`	No	—	Absolute tax amount on the adjustment.
`total`	`decimal(15,2)`	Yes	—	Signed total: positive for `DEBIT`, negative for `CREDIT` and `FULL_CREDIT`.
`issued_by_user_id`	`integer`	No	—	FK to `users`. Set when `status_cd` transitions to `ISSUED`.
`issued_dt`	`date`	No	—	Date when the adjustment was issued.

IMPORTANT

total is signed: negative values for CREDIT and FULL_CREDIT, positive for DEBIT. subtotal and tax_total are always absolute (positive). The revised invoice balance formula is: revised_total = invoice.total_amt_due + SUM(credit_memo.total) for all non-voided adjustments on the invoice.

NOTE

US entities use the term "Credit Memo" / "Debit Memo"; UK entities use "Credit Note" / "Debit Note." The label is derived from uta_entity_id at display/PDF time — the same credit_memo row covers both territories.

2.7 `credit_memo_line_item`

Per-billing-item-detail breakdown within a credit memo. Mirrors invoice_line_item for adjustment documents.

Field	Type	Required	Default	Description
`credit_memo_line_item_id`	`serial`	Yes	Auto	Primary key.
`credit_memo_id`	`integer`	Yes	—	FK to `credit_memo`.
`billing_item_detail_id`	`integer`	Yes	—	FK to `billing_item_detail`. The detail this line adjusts.
`invoice_line_item_id`	`integer`	Yes	—	FK to `invoice_line_item`. The original invoice line this adjusts. One line per invoice line item (unique constraint).
`line_number`	`integer`	Yes	—	Display ordering.
`gross_amt`	`decimal(15,2)`	Yes	—	Absolute gross amount adjusted. Sign is derived from parent `adjustment_type_cd`.
`commission_amt`	`decimal(15,2)`	No	—	Absolute commission amount adjusted.
`tax_type_cd`	`varchar(30)`	No	—	Per-line tax type (e.g., `VAT`, `HST`, `GST`).
`tax_rate`	`decimal(7,4)`	No	—	Tax rate applied.
`tax_amt`	`decimal(15,2)`	No	—	Tax amount for this line.
`tax_basis_amt`	`decimal(15,2)`	No	—	Amount the tax rate was applied to.
`doc_memo`	`varchar(500)`	No	—	Optional custom memo displayed on the PDF for this line.

NOTE

For FULL_CREDIT adjustments, amounts are copied from the original invoice line items at void time. Amounts are always stored as absolute values — the sign of the adjustment is encoded in the parent credit_memo.adjustment_type_cd.

2.8 `credit_memo_number_sequence`

Per-invoice uniqueness counter for credit memo numbers. Ensures that a single invoice cannot have two adjustments with the same number.

This table functions analogously to invoice_number_sequence but is keyed by invoice_id rather than entity+year. One record per invoice; the counter increments each time a new adjustment is created against that invoice.

2.9 `invoice_replacement_link`

Encodes the three-way relationship created by the Cancel & Reissue flow: Original Invoice (VOID) → Full-Credit Adjustment → Replacement Invoice (DRAFT).

Field	Type	Required	Default	Description
`invoice_replacement_link_id`	`serial`	Yes	Auto	Primary key.
`original_invoice_id`	`integer`	Yes	—	FK to `invoice`. The original invoice that was cancelled (status: `VOID`). Unique — one replacement link per original invoice.
`full_credit_adjustment_id`	`integer`	Yes	—	FK to `credit_memo`. The `FULL_CREDIT` adjustment that formally cancels the original.
`replacement_invoice_id`	`integer`	Yes	—	FK to `invoice`. The new replacement invoice (status: `DRAFT` initially). Unique — an invoice can be the replacement for only one original.
`reason`	`varchar(500)`	No	—	Optional reason for the cancel & reissue.

NOTE

This record is created only when a FULL_CREDIT adjustment is issued with createReplacementInvoice = true. A plain void (no replacement) does not create this record.

NOTE

The replacement invoice also carries reissued_from_invoice_id pointing to the original for clone lineage. This table adds the formal three-way link required for navigation in both directions: original → replacement, replacement → original, and adjustment → both.

2.10 `generated_statement`

Tracks all generated PDF statement documents with metadata for retrieval and audit. The actual PDF file is stored externally (object storage or local file system); this table holds the storage coordinates, generation status, and generation parameters.

Field	Type	Required	Default	Description
`generated_statement_id`	`serial`	Yes	Auto	Primary key.
`statement_type_cd`	`varchar(50)`	Yes	—	Type of statement (`STATEMENT_TYPE_CD`). See Section 5.
`statement_ref`	`varchar(100)`	Yes	—	Unique reference identifier displayed on the PDF. Format varies by type: `CS-{timestamp}-{uuid}` for client statements, `SS-{timestamp}-{uuid}` for settlement statements. Uniqueness enforced at application layer.
`client_id`	`integer`	No	—	FK to `party`. The client or party this statement is generated for. `null` for AR statements not tied to a specific client.
`date_from`	`date`	No	—	Start of the date range covered by the statement.
`date_to`	`date`	No	—	End of the date range covered by the statement.
`currency_cd`	`varchar(10)`	No	—	Currency of the statement (`CURRENCY_CD`).
`s3_bucket`	`varchar(255)`	No	—	Object storage bucket name where the PDF is stored.
`s3_key`	`varchar(500)`	No	—	Object storage key (path) for the PDF file. Used by the download operation to retrieve the file.
`file_path`	`varchar(500)`	No	—	Local or temporary file system path. Used during development or before upload to object storage.
`file_name`	`varchar(255)`	No	—	Name of the generated PDF file.
`file_size_bytes`	`integer`	No	—	Size of the generated file in bytes.
`generation_status_cd`	`varchar(20)`	No	`'PENDING'`	Generation lifecycle status. See Section 3.
`error_message`	`text`	No	—	Error details if `generation_status_cd = 'FAILED'`.
`metadata`	`jsonb`	No	—	Flexible generation parameters and summary totals specific to the statement type. For client statements: `{ totalGross, totalCommission, totalNet, transactionCount, excludedPaymentItemIds }`. For settlement statements: `{ totalGuarantee, totalNetGross, totalCommissionDue, netAmount, engagementCount }`.
`generated_by`	`varchar(100)`	No	—	User or system process that initiated generation.
`generated_dt`	`timestamp`	No	`now()`	Timestamp when generation was initiated.

NOTE

Pure audit columns (created_by, created_dt, updated_by, updated_dt) are omitted from this table unless they carry business meaning.

3. Status Lifecycle

3.1 Invoice Status (`INVOICE_STATUS_CD`)

Status	Code	Description	Allowed Transitions
Draft	`DRAFT`	Created but not yet finalized. Amounts and line items can be modified.	→ `ISSUED`, → `VOID`
Issued	`ISSUED`	Finalized and transmitted to recipient. Amounts locked.	→ `IN_REVISION`, → `PAID`, → `VOID`
In Revision	`IN_REVISION`	User clicked "Correct Invoice." Display fields are editable; financial fields are locked.	→ `REISSUED`, → `ISSUED` (revision cancelled)
Reissued	`REISSUED`	Correction published. The invoice reflects updated display fields.	→ `PAID`, → `VOID`
Paid	`PAID`	Payment received and matched. Terminal state.	—
Void	`VOID`	Cancelled. Created when a full-credit adjustment is issued against the invoice. A `credit_memo` row is always present. Terminal state.	—
Cancelled	`CANCELLED`	Administrative cancellation without a credit memo (rare). Terminal state.	—

mermaid

stateDiagram-v2
    [*] --> DRAFT : Invoice created
    DRAFT --> ISSUED : Issue
    DRAFT --> VOID : Cancel (no CM needed)
    ISSUED --> IN_REVISION : User starts correction
    IN_REVISION --> REISSUED : Correction published
    IN_REVISION --> ISSUED : Correction cancelled
    ISSUED --> PAID : Payment matched
    REISSUED --> PAID : Payment matched
    ISSUED --> VOID : Full-credit adjustment issued
    REISSUED --> VOID : Full-credit adjustment issued

Transition: DRAFT → ISSUED

Trigger: User finalizes and issues the invoice to the recipient.
Preconditions: Invoice must have at least one billing item linked via billing_item_document. status_cd must be DRAFT.
Side-effects: status_cd set to ISSUED. sent_by_user_id populated. invoice_sent_dt set when the invoice is actually transmitted.

Transition: DRAFT → VOID

Trigger: User cancels the invoice before it has been sent.
Preconditions: status_cd must be DRAFT.
Side-effects: status_cd set to VOID. billing_item_document links retained as historical record. No credit memo is created for draft voids.

Transition: ISSUED → IN_REVISION

Trigger: User clicks "Correct Invoice."
Preconditions: status_cd must be ISSUED or REISSUED.
Side-effects: status_cd set to IN_REVISION. invoice_correction_history row written with action_cd = CORRECTION_STARTED and a JSON snapshot of correctable display fields. Financial fields (amounts, dates, billing terms) cannot be changed during correction.

Transition: IN_REVISION → REISSUED

Trigger: User clicks "Reissue" after completing corrections.
Preconditions: status_cd must be IN_REVISION.
Side-effects: status_cd set to REISSUED. invoice_correction_history row written with action_cd = CORRECTION_PUBLISHED. A new JSON snapshot captures the corrected display fields.

Transition: IN_REVISION → ISSUED (revision cancelled)

Trigger: User abandons the correction without publishing.
Side-effects: status_cd reverts to ISSUED. invoice_correction_history row written with action_cd = CORRECTION_CANCELLED.

Transition: ISSUED / REISSUED → PAID

Trigger: Cash receipt is applied against this invoice and matched by the user.
Side-effects: status_cd set to PAID.

Transition: ISSUED / REISSUED → VOID

Trigger: A FULL_CREDIT adjustment is issued against this invoice (Cancel & Reissue flow or plain void).
Side-effects: status_cd set to VOID. voided_by_user_id and voided_dt populated. A credit_memo row exists. If Cancel & Reissue was selected, an invoice_replacement_link and a new replacement invoice (status DRAFT) are also created.

IMPORTANT

PAID, VOID, and CANCELLED are terminal states. IN_REVISION does not lock the invoice for external consumers — it is a transient editing state that resolves to REISSUED or back to ISSUED.

NOTE

The correction flow (IN_REVISION → REISSUED) is for display field changes only (PO number, email recipients, logo, notes, bank details, PDF display flags). Financial fields (issue date, due date, billing terms, amounts) cannot be changed via correction. To change financial values, the invoice must be voided and a replacement issued via Cancel & Reissue.

3.2 Statement Generation Status (`generation_status_cd`)

Status	Code	Description	Allowed Transitions
Pending	`PENDING`	Statement generation has been requested but not yet started.	→ Generating (`GENERATING`)
Generating	`GENERATING`	PDF generation is in progress.	→ Completed (`COMPLETED`), → Failed (`FAILED`)
Completed	`COMPLETED`	PDF has been generated and stored. `s3_bucket`/`s3_key` or `file_path` are populated.	—
Failed	`FAILED`	Generation encountered an error. `error_message` contains details.	—

mermaid

stateDiagram-v2
    [*] --> PENDING : Statement requested
    PENDING --> GENERATING : Generation starts
    GENERATING --> COMPLETED : PDF stored successfully
    GENERATING --> FAILED : Error during generation

Transition: PENDING → GENERATING

Trigger: The generation process picks up the statement record and begins PDF creation.
Preconditions: generation_status_cd must be 'PENDING'.
Side-effects: generation_status_cd set to 'GENERATING'.

Transition: GENERATING → COMPLETED

Trigger: PDF file successfully generated and stored in object storage.
Preconditions: generation_status_cd must be 'GENERATING'. PDF buffer is non-empty.
Side-effects: generation_status_cd set to 'COMPLETED'. s3_bucket, s3_key, file_name, file_size_bytes, and metadata populated with storage coordinates and summary data.

Transition: GENERATING → FAILED

Trigger: An error is thrown during PDF generation or storage upload.
Preconditions: generation_status_cd must be 'GENERATING'.
Side-effects: generation_status_cd set to 'FAILED'. error_message populated with the error detail.

4. Validation & Database Constraints

Unique Constraints

Table	Constraint	Columns	Business Rule
`invoice`	`uq_invoice_number`	`(invoice_number)`	Each invoice number must be globally unique across all entities and years. Atomic sequence increment prevents duplicates under concurrent creation.
`invoice_number_sequence`	`uq_invoice_seq_entity_year`	`(uta_entity_id, current_year)`	One sequence record per entity per calendar year. Prevents duplicate counters for the same entity and year.
`invoice_bank_details`	`uq_invoice_bank_entity_currency`	`(uta_entity_id, currency_cd)`	One bank detail record per entity per currency. The system automatically selects the matching record when generating invoice PDFs.
`billing_item_document`	`uq_billing_item_document_item_type_doc`	`(billing_item_id, document_type_cd, document_id)`	Prevents duplicate links between the same billing item and the same document.

Business Validation

Single entity per invoice: All billing item details on an invoice must share the same uta_entity_id. This is enforced during the grouping step of invoice generation, before any records are written.
Single currency per invoice: All billing item details on an invoice must share the same currency_cd. Enforced during the same grouping step.
UK entity: one client per invoice: When uta_entity_id corresponds to the UK entity, multi_client_ind must be false. UK invoices always group by client in addition to entity and currency, regardless of the multi_client_ind flag on the request.
US entity multi-client restriction: US entities may combine multiple clients on a single invoice only when invoice_recipient_cd = 'BUYER' and the multiClientInvoice flag is explicitly set true on the create request.
Invoice type must match detail type: COMMISSION invoices require REV-type billing item details (billing_item_detail.billing_item_detail_type_cd = 'REV'). TOTAL_DUE invoices require PAY-type details. Mixing types within a single invoice is not permitted.
due_date derived from billing_terms_cd: due_date is always calculated as issue_date + offset(billing_terms_cd). The offset is 0 days for DUE_RECEIPT, 7 for NET_7, 14 for NET_14, 30 for NET_30, 45 for NET_45, and 60 for NET_60. The field is never set independently.
statement_ref uniqueness: Uniqueness of statement_ref in generated_statement is enforced at the application layer during generation (not by a database constraint). The reference is prefixed by statement type (CS-, SS-) and includes a timestamp and UUID segment to minimize collision risk.
Bank details selection is automatic: When generating an invoice PDF, the system matches invoice.uta_entity_id + invoice.currency_cd to invoice_bank_details.(uta_entity_id, currency_cd) to resolve the correct payment instructions. If no matching active record exists, the PDF is generated without payment instructions.

NOTE

PoC Artifact: In the PoC, invoice status transitions (Draft → Issued → Paid → Void) are not enforced by explicit state-machine guards in the service layer. updateInvoiceStatus accepts any target status. Production should enforce the allowed-transitions table in Section 3.1 as preconditions.

5. Code Master Values

5.1 `INVOICE_STATUS_CD`

Used by invoice.status_cd.

Code	Description	Behavior / When Used
`DRAFT`	Invoice created, not yet finalized.	Default on creation. Invoice is editable.
`ISSUED`	Invoice finalized and sent to recipient. Amounts are locked.	Set when user issues the invoice.
`IN_REVISION`	Correction in progress. Display fields editable; financial fields locked.	Set when user clicks "Correct Invoice."
`REISSUED`	Correction published. Display fields updated.	Set when user publishes the correction.
`PAID`	Payment received against this invoice.	Terminal state. Set when cash is matched.
`VOID`	Invoice cancelled via full-credit adjustment. A `credit_memo` row always exists.	Terminal state.
`CANCELLED`	Administrative cancellation without a credit memo.	Terminal state. Rare.

Default on creation: DRAFT

5.2 `INVOICE_TYPE_CD`

Used by invoice.invoice_type_cd.

Code	Description	Behavior / When Used
`COMMISSION`	Commission invoice sent to the client for UTA's revenue (REV) share.	Requires REV-type billing item details (`billing_item_detail_type_cd = 'REV'`). Populates `total_commission_amt`.
`TOTAL_DUE`	Full-amount invoice sent to the buyer for the total owed under the deal.	Requires PAY-type billing item details (`billing_item_detail_type_cd = 'PAY'`). `total_commission_amt` is not populated.

Default on creation: Set from the create request; no system default.

5.3 `INVOICE_RECIPIENT_CD`

Used by invoice.invoice_recipient_cd.

Code	Description	Behavior / When Used
`CLIENT`	Invoice is addressed to the client (talent/artist).	`recipient_party_id` is set to the client's `party_id`. Always results in one invoice per client.
`BUYER`	Invoice is addressed to the buyer (studio, brand, network, platform).	`recipient_party_id` is set to the buyer's `party_id`. US entities may optionally combine multiple clients into one invoice (`multi_client_ind = true`).

Default on creation: Set from the create request; no system default.

5.4 `BILLING_TERMS_CD`

Used by invoice.billing_terms_cd. Determines the due_date offset from issue_date.

Code	Description	Behavior / When Used
`DUE_RECEIPT`	Due upon receipt.	`due_date = issue_date` (0 days offset).
`NET_7`	Net 7 days.	`due_date = issue_date + 7 days`.
`NET_14`	Net 14 days.	`due_date = issue_date + 14 days`.
`NET_30`	Net 30 days.	`due_date = issue_date + 30 days`.
`NET_45`	Net 45 days.	`due_date = issue_date + 45 days`.
`NET_60`	Net 60 days.	`due_date = issue_date + 60 days`.

Default on creation: DUE_RECEIPT (applied when no override is provided on the create request).

5.5 `WITHHOLDING_TYPE_CD`

Used by invoice.withholding_type_cd. Invoice-level withholding applied on top of per-line billing_item_tax amounts.

Code	Description	Behavior / When Used
`WITHHOLDING_FEU`	UK Foreign Entertainers Unit	20% withholding on the invoice total for UK FEU-liable engagements.
`WITHHOLDING_NRA`	US Non-Resident Alien	30% withholding on the invoice total for US NRA-liable engagements.

5.6 `ADJUSTMENT_TYPE_CD` (on `credit_memo`)

Used by credit_memo.adjustment_type_cd.

Code	Description	Behavior / When Used
`FULL_CREDIT`	Full cancellation of the original invoice.	Created when an ISSUED or REISSUED invoice is voided. Sets `invoice.status_cd` to `VOID`. `total` is negative (signed). Sent as "Credit Memo" (US) or "Credit Note" (UK).
`CREDIT`	Partial credit — reduces the invoice balance.	Created for partial adjustments. `total` is negative.
`DEBIT`	Positive adjustment — increases the invoice balance.	Created when additional charges apply after issuance. `total` is positive. Sent as "Debit Memo" (US) or "Debit Note" (UK).

5.7 `ACTION_CD` (on `invoice_correction_history`)

Used by invoice_correction_history.action_cd.

Code	Description
`CORRECTION_STARTED`	User clicked "Correct Invoice." Invoice moved to `IN_REVISION`.
`CORRECTION_PUBLISHED`	User clicked "Reissue." Invoice moved to `REISSUED`.
`CORRECTION_CANCELLED`	User abandoned the correction. Invoice reverted to `ISSUED`.

5.9 `DOCUMENT_TYPE_CD` (on `billing_item_document`)

Used by billing_item_document.document_type_cd. Discriminates which document type the document_id references and which billing item detail type is relevant.

Code	Description	Behavior / When Used	document_id Points To
`CI`	Commission Invoice	Created when a `COMMISSION` invoice is generated. Links REV billing item details to the invoice.	`invoice.invoice_id`
`BI`	Buyer Invoice	Created when a `TOTAL_DUE` invoice is generated. Links PAY billing item details to the invoice.	`invoice.invoice_id`
`CM`	Credit Memo	Created when a credit memo is issued from an invoice. Tracks which billing items were affected by the adjustment.	`credit_memo.credit_memo_id`
`CL`	Client Letter	Created when a client letter is generated. Links billing item details included in the letter for audit trail.	`client_letter.client_letter_id`

Default on creation: Set from invoice type at the time of creation; no system default.

5.10 `STATEMENT_TYPE_CD`

Used by generated_statement.statement_type_cd.

Code	Description	Behavior / When Used
`CLIENT_STATEMENT`	Activity summary for a client over a date range.	Generated from posted `payment_item` records for the client in the given period. `statement_ref` prefixed `CS-`.
`SETTLEMENT_STATEMENT`	Settlement breakdown accompanying a payment to a client's party.	Generated from `participant_settlement_item` and `payment_item` records for the client in the given period. `statement_ref` prefixed `SS-`.
`AR_STATEMENT`	Accounts receivable aging summary.	Reserved for AR aging exports. Not tied to a specific client.
`INVOICE`	Invoice document stored as a generated statement record.	Used when invoice PDFs are persisted via the statement storage path rather than directly.

Default on creation: Set from the generation request; no system default.

5.11 `generation_status_cd`

Used by generated_statement.generation_status_cd.

Code	Description	Behavior / When Used
`PENDING`	Generation requested, not yet started.	Default on creation. Record created before PDF generation begins.
`GENERATING`	PDF generation in progress.	Set when the generation process begins.
`COMPLETED`	PDF generated and stored successfully.	Set after successful upload. `s3_bucket`, `s3_key`, `file_name`, and `file_size_bytes` are populated.
`FAILED`	Generation failed.	Set on exception. `error_message` field contains details.

Default on creation: PENDING

2.6 `invoice_line_item`

Represents individual line items on an invoice. Each line item links to a billing item detail (REV or PAY) and stores a snapshot of amounts at invoice generation time. For Commission Invoices (CI), links to REV details; for Buyer Invoices (BI), links to PAY details.

Field	Type	Required	Default	Description
`invoice_line_item_id`	`serial`	Yes	Auto	Primary key.
`invoice_id`	`integer`	Yes	—	FK to `invoice`. Parent invoice header.
`billing_item_detail_id`	`integer`	Yes	—	FK to `billing_item_detail`. The specific billing item detail (REV or PAY) being invoiced. Unique per invoice via `uq_invoice_line_item`.
`line_number`	`integer`	Yes	—	Line item display order on the invoice PDF.
`doc_memo`	`varchar(500)`	No	—	Customizable line item description for PDF display. If `null`, system uses `billing_item.billing_item_name`.
`gross_amt`	`decimal(15,2)`	Yes	—	Gross amount for this line. Snapshot captured at invoice generation time.
`commission_amt`	`decimal(15,2)`	No	—	Commission amount (UTA's share). Populated for REV details on Commission Invoices only.
`tax_type_cd`	`varchar(30)`	No	—	Tax type on this line (`LINE_TAX_TYPE_CD`): `VAT_ARTIST_FEE`, `VAT_COMMISSION`, `HST`, `GST`, or `null`. One per line max.
`tax_rate`	`decimal(7,4)`	No	—	Tax rate as decimal (e.g., `0.2000` = 20%).
`tax_amt`	`decimal(15,2)`	No	—	Calculated tax amount for this line.
`tax_basis_amt`	`decimal(15,2)`	No	—	Amount on which tax was calculated.
`tax_override_ind`	`boolean`	No	`false`	`true` if user manually modified the tax amount; `false` if system-calculated.
`tax_original_amt`	`decimal(15,2)`	No	—	System-calculated tax amount before user override. Used for audit trail.

2.7 `credit_memo`

Represents a unified adjustment/correction document created after an invoice is issued. Replaces the former void/credit and supplemental invoice mechanisms. A single entity covers all post-issuance change types: full cancellations (credit memos), partial adjustments (debit memos), and supplemental amounts.

Number Format: {invoiceNumber}-CM-{sequence} (e.g., UTA_US-2026-000001-CM-1)

Status Lifecycle: DRAFT → ISSUED (terminal)

US vs UK Terminology (derived from uta_entity_id at display time):

US entity: "Credit Memo"
UK entity: "Credit Note"

Field	Type	Required	Default	Description
`credit_memo_id`	`serial`	Yes	Auto	Primary key.
`adjustment_number`	`varchar(80)`	Yes	—	Human-readable adjustment number. Format: `{invoiceNumber}-CM-{sequence}`. Globally unique.
`invoice_id`	`integer`	Yes	—	FK to `invoice`. The original invoice this adjustment belongs to.
`adjustment_type_cd`	`varchar(20)`	Yes	—	Type of adjustment (`ADJUSTMENT_TYPE_CD`): `DEBIT` (supplemental), `CREDIT` (partial credit), or `FULL_CREDIT` (full cancellation). Discriminates behavior.
`reason_code_cd`	`varchar(30)`	No	—	Reason code (`CREDIT_MEMO_REASON_CD`): `FULL_CANCELLATION`, `PRICE_CORRECTION`, `INCORRECT_VAT`, `OTHER`.
`reason_text`	`varchar(500)`	No	—	Free-text reason for the adjustment.
`status_cd`	`varchar(20)`	Yes	`'DRAFT'`	Lifecycle status (`CREDIT_MEMO_STATUS_CD`). DRAFT = editable; ISSUED = sent to recipient.
`issue_date`	`date`	Yes	—	Date the credit memo was issued.
`uta_entity_id`	`integer`	Yes	—	FK to `uta_entity`. Denormalized from the original invoice for fast lookup.
`recipient_party_id`	`integer`	Yes	—	FK to `party`. The bill-to party (client or buyer). Denormalized from the original invoice.
`currency_cd`	`varchar(10)`	Yes	—	Currency (`CURRENCY_CD`). Denormalized from the original invoice.
`invoice_type_cd`	`varchar(20)`	Yes	—	Invoice type (`INVOICE_TYPE_CD`): `COMMISSION` or `TOTAL_DUE`. Denormalized from the original invoice.
`subtotal`	`decimal(15,2)`	Yes	—	Subtotal (before tax). Always an absolute (positive) value.
`tax_total`	`decimal(15,2)`	No	—	Total tax amount. Always absolute.
`total`	`decimal(15,2)`	Yes	—	Signed total: positive for DEBIT, negative for CREDIT/FULL_CREDIT. Formula: `revised_invoice_total = original_invoice.total_amt_due + SUM(adjustments.total)`.
`issued_by_user_id`	`integer`	No	—	FK to `users`. User who issued the credit memo. Set on DRAFT → ISSUED transition.
`issued_dt`	`date`	No	—	Date the credit memo was issued (state transition timestamp).

2.8 `credit_memo_line_item`

Per-billing-item-detail breakdown for a credit memo. Mirrors invoice_line_item for adjustment documents. Each row records the gross/commission amount adjusted for a specific billing item detail.

Field	Type	Required	Default	Description
`credit_memo_line_item_id`	`serial`	Yes	Auto	Primary key.
`credit_memo_id`	`integer`	Yes	—	FK to `credit_memo`. Parent adjustment.
`billing_item_detail_id`	`integer`	Yes	—	FK to `billing_item_detail`. The billing item detail being adjusted.
`invoice_line_item_id`	`integer`	Yes	—	FK to `invoice_line_item`. The original invoice line item this adjustment addresses. Direct FK replaces bridge-table logic. Unique per credit memo via `uq_cm_line_item`.
`line_number`	`integer`	Yes	—	Line item display order.
`gross_amt`	`decimal(15,2)`	Yes	—	Gross amount adjusted. Always absolute; sign derived from parent `adjustment_type_cd`.
`commission_amt`	`decimal(15,2)`	No	—	Commission amount. Always absolute; sign derived from parent.
`tax_type_cd`	`varchar(30)`	No	—	Per-line tax type (`LINE_TAX_TYPE_CD`).
`tax_rate`	`decimal(7,4)`	No	—	Per-line tax rate.
`tax_amt`	`decimal(15,2)`	No	—	Per-line tax amount.
`tax_basis_amt`	`decimal(15,2)`	No	—	Amount tax was calculated on.
`doc_memo`	`varchar(500)`	No	—	Optional custom memo for PDF display.

2.9 `credit_memo_number_sequence`

Tracks per-invoice, per-type-bucket counters for credit memo numbering. Only the CM (Credit Memo) bucket is currently active. Ensures one credit memo per invoice via unique constraint.

Field	Type	Required	Default	Description
`credit_memo_number_sequence_id`	`serial`	Yes	Auto	Primary key.
`invoice_id`	`integer`	Yes	—	FK to `invoice`. The invoice this counter belongs to. Unique per invoice+bucket via `uq_cm_num_seq_invoice_bucket`.
`type_bucket`	`varchar(2)`	Yes	—	Type bucket identifier. Currently only `CM` (Credit Memo) is active. Reserved buckets: `DM` (Debit Memo, reserved).
`current_sequence`	`integer`	Yes	`0`	Current sequence number. Incremented on each adjustment creation. Used to generate the `{sequence}` portion of `adjustment_number`.

2.10 `client_letter`

Represents Client Letters (displayed as "Invoice Summary" on PDF) showing outstanding balances for billing items. Shows original amount, collected amount, and remaining balance due. Used to generate a rollup view of what a client owes across multiple invoices and deals.

Number Format: CL-{year}-{sequence} (e.g., CL-2026-000001)

Status Lifecycle: DRAFT → ISSUED → VOID

Field	Type	Required	Default	Description
`client_letter_id`	`serial`	Yes	Auto	Primary key.
`client_letter_number`	`varchar(50)`	Yes	—	Human-readable client letter number. Format: `CL-{year}-{sequence}`. Globally unique.
`uta_entity_id`	`integer`	Yes	—	FK to `uta_entity`. One entity per letter.
`issue_date`	`date`	Yes	—	Date the client letter was generated.
`recipient_type_cd`	`varchar(20)`	Yes	—	Recipient type. Currently only `CLIENT` supported.
`amount_type_cd`	`varchar(10)`	Yes	—	Amount type: `REV` (UTA share) or `PAY` (client payout).
`currency_cd`	`varchar(10)`	Yes	—	Currency of the letter. One per letter.
`recipient_party_id`	`integer`	Yes	—	FK to `party`. The client receiving the letter.
`recipient_address_id`	`varchar(100)`	No	—	`auth_address_id` from party addresses. Recipient's mailing address for PDF.
`contracted_party_id`	`integer`	No	—	FK to `party`. The talent/artist (for UK Payout header).
`contracted_address_id`	`varchar(100)`	No	—	`auth_address_id` for contracted party's address.
`scope_cd`	`varchar(20)`	Yes	`'ALL'`	Filtering scope (`SCOPE_CD`): `INVOICED_ONLY`, `UNINVOICED_ONLY`, or `ALL`.
`summarize_by_cd`	`varchar(20)`	Yes	`'NONE'`	Grouping level (`SUMMARIZE_BY_CD`): `NONE` (detail line per billing item), `DEAL`, or `SALES_ITEM`.
`include_invoice_ref_ind`	`boolean`	No	`false`	`true` to display invoice number column on PDF.
`auto_invoiced_count`	`integer`	No	`0`	Number of Commission Invoices auto-created from this letter.
`total_gross_amt`	`decimal(15,2)`	Yes	—	Sum of all gross amounts on the letter.
`total_original_amt`	`decimal(15,2)`	Yes	—	Sum of REV or PAY amounts (before collections).
`total_collected_amt`	`decimal(15,2)`	Yes	—	Sum of cash applied/collected.
`total_balance_amt`	`decimal(15,2)`	Yes	—	Sum of open balances. Must be > 0 for items included on letter.
`status_cd`	`varchar(20)`	Yes	`'DRAFT'`	Lifecycle status (`CLIENT_LETTER_STATUS_CD`). DRAFT, ISSUED, VOID.

2.11 `client_letter_line_item`

Individual line items on a client letter. Each row can represent a single billing item detail or a summary of multiple items (when summarization is enabled).

Field	Type	Required	Default	Description
`client_letter_line_item_id`	`serial`	Yes	Auto	Primary key.
`client_letter_id`	`integer`	Yes	—	FK to `client_letter`. Parent letter.
`billing_item_detail_id`	`integer`	No	—	FK to `billing_item_detail`. The detail for this line. `null` if this is a summary line.
`line_number`	`integer`	Yes	—	Display order.
`deal_id`	`integer`	No	—	FK to `deal`. Used for DEAL-level grouping summaries.
`sales_item_id`	`integer`	No	—	Sales item ID (revenue_item). Used for SALES_ITEM-level grouping.
`touring_show_id`	`integer`	No	—	FK to `touring_show` (if applicable).
`display_name`	`varchar(500)`	Yes	—	Line item display name on PDF. Single billing item name or summary group name.
`invoice_numbers`	`varchar(500)`	No	—	Invoice references. Single invoice number or comma-separated list for summary lines.
`gross_amt`	`decimal(15,2)`	Yes	—	Gross amount. Snapshots at generation time; summed for summary lines.
`original_amt`	`decimal(15,2)`	Yes	—	REV or PAY amount.
`collected_amt`	`decimal(15,2)`	Yes	—	Cash applied.
`balance_amt`	`decimal(15,2)`	Yes	—	Open balance. Must be > 0.
`is_summary_line`	`boolean`	No	`false`	`true` if this line is a summary (deals or sales items rolled up).
`item_count`	`integer`	No	`1`	Number of billing items in this line. > 1 for summary lines.

2.12 `client_letter_line_item_detail`

Bridge table linking summary line items to their constituent billing item details. Used when summarize_by_cd = 'DEAL' or 'SALES_ITEM' to track which billing items are rolled up into each summary line. Enables drill-down from summary to detail.

Field	Type	Required	Default	Description
`client_letter_line_item_detail_id`	`serial`	Yes	Auto	Primary key.
`client_letter_line_item_id`	`integer`	Yes	—	FK to `client_letter_line_item`. Parent summary line item.
`billing_item_detail_id`	`integer`	Yes	—	FK to `billing_item_detail`. A constituent billing item in this summary line.

2.13 `client_letter_number_sequence`

Tracks sequential client letter numbers per UTA entity per year.

Field	Type	Required	Default	Description
`client_letter_number_sequence_id`	`serial`	Yes	Auto	Primary key.
`uta_entity_id`	`integer`	Yes	—	FK to `uta_entity`. Entity this sequence belongs to. Unique per entity+year via `uq_cl_seq_entity_year`.
`current_year`	`integer`	Yes	—	Calendar year for this counter.
`current_sequence`	`integer`	Yes	`0`	Current sequence number. Incremented on each letter creation.
`prefix`	`varchar(10)`	Yes	`'CL'`	Letter number prefix. Always `'CL'`.

6. Cross-References

Document	Relationship
Billing Items Data Model	`invoice` is linked to billing items through the `billing_item_document` bridge table (owned by the Billing Items area). Each `billing_item_document` row ties a `billing_item_id` to a `document_id` (which is an `invoice_id` for types `CI` and `BI`). The `document_type_cd` discriminates whether the invoice covers REV details (`CI`) or PAY details (`BI`). A billing item detail is considered "already invoiced" when a matching `billing_item_document` row exists for its parent `billing_item_id` and the relevant document type.
Settlements Data Model	`generated_statement` with `statement_type_cd = 'SETTLEMENT_STATEMENT'` is populated from `participant_settlement_item` and `payment_item` records. Settlement statements summarize how PAY was divided among the client's party over a period.
Parties Data Model	`invoice.recipient_party_id` and `invoice.contracted_party_id` reference `party`. `generated_statement.client_id` references `party`. Recipient and contracted party addresses are resolved from the party addresses system using `auth_address_id` values stored in `invoice.recipient_address_id` and `invoice.contracted_address_id`.
Deals, Sales Items & Payment Terms Data Model	Settlement statements join `deal` to group payment activity by engagement. `payment_item.deal_id` links posted payments back to their originating deal for statement grouping.

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Appendices

Appendices

Appendices

Invoices and Statements Data Model ​

1. Executive Summary ​

Purpose ​

Scope ​

2. Data Model ​

2.1 Entity-Relationship Diagram ​

2.2 invoice ​

2.3 invoice_number_sequence ​

2.4 invoice_bank_details ​

2.5 invoice_correction_history ​

2.6 credit_memo ​

2.7 credit_memo_line_item ​

2.8 credit_memo_number_sequence ​

2.9 invoice_replacement_link ​

2.10 generated_statement ​

3. Status Lifecycle ​

3.1 Invoice Status (INVOICE_STATUS_CD) ​

Transition: DRAFT → ISSUED ​

Transition: DRAFT → VOID ​

Transition: ISSUED → IN_REVISION ​

Transition: IN_REVISION → REISSUED ​

Transition: IN_REVISION → ISSUED (revision cancelled) ​

Transition: ISSUED / REISSUED → PAID ​

Transition: ISSUED / REISSUED → VOID ​

3.2 Statement Generation Status (generation_status_cd) ​

Transition: PENDING → GENERATING ​

Transition: GENERATING → COMPLETED ​

Transition: GENERATING → FAILED ​

4. Validation & Database Constraints ​

Unique Constraints ​

Business Validation ​

5. Code Master Values ​

5.1 INVOICE_STATUS_CD ​

5.2 INVOICE_TYPE_CD ​

5.3 INVOICE_RECIPIENT_CD ​

5.4 BILLING_TERMS_CD ​

5.5 WITHHOLDING_TYPE_CD ​

5.6 ADJUSTMENT_TYPE_CD (on credit_memo) ​

5.7 ACTION_CD (on invoice_correction_history) ​

5.9 DOCUMENT_TYPE_CD (on billing_item_document) ​

5.10 STATEMENT_TYPE_CD ​

5.11 generation_status_cd ​

2.6 invoice_line_item ​

2.7 credit_memo ​

2.8 credit_memo_line_item ​

2.9 credit_memo_number_sequence ​

2.10 client_letter ​

2.11 client_letter_line_item ​

2.12 client_letter_line_item_detail ​

2.13 client_letter_number_sequence ​

6. Cross-References ​

Invoices and Statements Data Model

1. Executive Summary

Purpose

Scope

2. Data Model

2.1 Entity-Relationship Diagram

2.2 `invoice`

2.3 `invoice_number_sequence`

2.4 `invoice_bank_details`

2.5 `invoice_correction_history`

2.6 `credit_memo`

2.7 `credit_memo_line_item`

2.8 `credit_memo_number_sequence`

2.9 `invoice_replacement_link`

2.10 `generated_statement`

3. Status Lifecycle

3.1 Invoice Status (`INVOICE_STATUS_CD`)

Transition: DRAFT → ISSUED

Transition: DRAFT → VOID

Transition: ISSUED → IN_REVISION

Transition: IN_REVISION → REISSUED

Transition: IN_REVISION → ISSUED (revision cancelled)

Transition: ISSUED / REISSUED → PAID

Transition: ISSUED / REISSUED → VOID

3.2 Statement Generation Status (`generation_status_cd`)

Transition: PENDING → GENERATING

Transition: GENERATING → COMPLETED

Transition: GENERATING → FAILED

4. Validation & Database Constraints

Unique Constraints

Business Validation

5. Code Master Values

5.1 `INVOICE_STATUS_CD`

5.2 `INVOICE_TYPE_CD`

5.3 `INVOICE_RECIPIENT_CD`

5.4 `BILLING_TERMS_CD`

5.5 `WITHHOLDING_TYPE_CD`

5.6 `ADJUSTMENT_TYPE_CD` (on `credit_memo`)

5.7 `ACTION_CD` (on `invoice_correction_history`)

5.9 `DOCUMENT_TYPE_CD` (on `billing_item_document`)

5.10 `STATEMENT_TYPE_CD`

5.11 `generation_status_cd`

2.6 `invoice_line_item`

2.7 `credit_memo`

2.8 `credit_memo_line_item`

2.9 `credit_memo_number_sequence`

2.10 `client_letter`

2.11 `client_letter_line_item`

2.12 `client_letter_line_item_detail`

2.13 `client_letter_number_sequence`

6. Cross-References