Accounting Data Model

1. Executive Summary

Purpose

The accounting domain provides the General Ledger (GL) posting infrastructure for UTA Client Payments. It captures every financial event — revenue recognition, billing, cash receipt posting, cash application, payouts, FX adjustments, and AR true-ups — as double-entry transactions that can be transmitted to the enterprise GL system. Transactions are never created directly by users; they are produced by batch accounting jobs that scan for unposted source records and generate balanced debit/credit batches grouped by batch_id. The fiscal_period table controls which accounting period a transaction falls into and whether that period is open for new postings. The account table provides the chart of accounts that every transaction line posts against. The accounting_job_execution_history table records an audit trail of every batch run.

Scope

Covered:

transaction — The subledger journal. Every financial event produces one or more balanced debit/credit rows here, grouped into a batch that nets to zero.
account — The chart of accounts (GL account master). Each transaction line references an account.
fiscal_period — Accounting period calendar. Controls which period a transaction posts into and whether a period is open or closed.
accounting_job_execution_history — Audit log for batch accounting jobs. Records when each job type ran, what date it processed through, and whether it succeeded or failed.

Not covered (documented separately):

Cash receipts and splits — see Cash Receipts Data Model
Worksheets and cash applications — see Worksheets Data Model
Settlements and payouts — see Settlements Data Model
Billing items and revenue schedules — see Billing Items Data Model

2. Data Model

2.1 Entity-Relationship Diagram

mermaid

erDiagram
    transaction }o--o| account : "posts to"
    transaction }o--o| fiscal_period : "belongs to period"
    transaction }o--o| uta_entity : "belongs to entity"
    transaction }o--o| department : "belongs to department"
    transaction }o--o| party : "associated client"

    transaction {
        serial transaction_id PK
        varchar class_cd
        varchar source_cd
        integer source_id
        varchar source_ref
        varchar rev_ref
        varchar batch_id
        integer account_id FK
        varchar type_cd
        boolean reverse_ind
        decimal trans_amt
        decimal group_amt
        decimal reporting_amt
        varchar trans_currency_cd
        varchar group_currency_cd
        varchar reporting_currency_cd
        date transaction_ref_dt
        date posting_dt
        integer posting_period_id FK
        varchar posting_period_ref
        integer uta_entity_id FK
        integer department_id FK
        integer client_id FK
        varchar gl_status_cd
        date gl_posting_dt
    }

    account {
        serial account_id PK
        varchar account_class
        varchar account_description
        varchar account_number
        varchar account_full_name
        varchar status_cd
    }

    fiscal_period {
        serial fiscal_period_id PK
        date period_start_dt
        date period_end_dt
        date period_closed_dt
        integer period_year
        integer period_month
        varchar period_ref
        boolean current_ind
        boolean current_cash_ind
    }

    accounting_job_execution_history {
        serial accounting_job_execution_history_id PK
        varchar job_cd
        date effective_dt
        timestamp started_at
        timestamp completed_at
        varchar status_cd
        jsonb result_summary
        varchar created_by
    }

2.2 `transaction`

The subledger journal table. Each row is one side (debit or credit) of a double-entry posting. Rows are grouped into balanced batches via batch_id. Transactions are never inserted directly by users — they are produced by batch accounting jobs.

Field	Type	Required	Default	Description
`transaction_id`	`serial`	Yes	Auto	Primary key.
`class_cd`	`varchar(50)`	No	—	Transaction class. Categorizes the financial nature of the entry. See Section 5 for values (`REV`, `AR`, `CASH`, `TAX`, `FX`).
`source_cd`	`varchar(50)`	No	—	Source job code. Identifies which accounting job created this transaction. See Section 5 for values (`REV`, `BILL`, `CR`, `APP`, `PO`, `FX`, `TRUE`, `CL`).
`source_id`	`integer`	No	—	ID of the source record that triggered this transaction (e.g., a revenue schedule ID, a cash receipt ID, a cash receipt application ID).
`source_ref`	`varchar(255)`	No	—	Human-readable reference string for the source record (e.g., the sales item reference from the originating revenue item).
`rev_ref`	`varchar(255)`	No	—	Revenue reference. Links the transaction back to its originating revenue item for aggregation and reporting (e.g., period-end AR true-up candidates are identified by distinct `rev_ref` values).
`batch_id`	`varchar(100)`	Yes	—	Groups debits and credits into a balanced batch. All transactions sharing a `batch_id` must net to zero. Format: `YYYYMMDDHHMMSS` + zero-padded six-digit sequence number.
`account_id`	`integer`	No	—	FK to `account.account_id`. The GL account this entry posts to.
`type_cd`	`varchar(1)`	No	—	Debit/credit indicator. `D` = Debit, `C` = Credit. See Section 5.
`reverse_ind`	`boolean`	No	`false`	When `true`, this transaction is a reversal entry (negates a prior posting). Set to `true` for negative-amount transactions (e.g., write-off reversals).
`trans_amt`	`decimal(15,2)`	No	—	Amount in the transaction's original currency (`trans_currency_cd`).
`group_amt`	`decimal(15,2)`	No	—	Amount converted to the UTA entity's group currency (`group_currency_cd`).
`reporting_amt`	`decimal(15,2)`	No	—	Amount converted to the reporting currency, typically USD (`reporting_currency_cd`).
`trans_currency_cd`	`varchar(10)`	No	—	ISO currency code for `trans_amt`.
`group_currency_cd`	`varchar(10)`	No	—	ISO currency code for `group_amt`.
`reporting_currency_cd`	`varchar(10)`	No	—	ISO currency code for `reporting_amt` (typically `USD`).
`transaction_ref_dt`	`date`	No	—	The business date of the underlying event (e.g., the revenue recognition date, the receipt booking date). May differ from `posting_dt` when a transaction is recognized retroactively.
`posting_dt`	`date`	No	—	The date this transaction is posted to the subledger. Determined by posting-date logic: if the source record's creation date is before the driver date (revenue date, check date, due date), posts to the first day of the driver date's fiscal period; otherwise posts to the creation date.
`posting_period_id`	`integer`	No	—	FK to `fiscal_period.fiscal_period_id`. The accounting period this transaction belongs to.
`posting_period_ref`	`varchar(20)`	No	—	Denormalized period reference string (e.g., `2026-01`) for query convenience. Matches `fiscal_period.period_ref`.
`uta_entity_id`	`integer`	No	—	FK to `uta_entity.uta_entity_id`. The UTA legal entity this transaction belongs to.
`department_id`	`integer`	No	—	FK to `department.department_id`. The department associated with this transaction.
`client_id`	`integer`	No	—	FK to `party.party_id`. The client (artist/talent) associated with this transaction.
`gl_status_cd`	`varchar(1)`	No	—	GL transmission status. See Section 3 for the lifecycle. Values: `U` (Unposted), `P` (Posted), `X` (Excluded), `F` (Failed).
`gl_posting_dt`	`date`	No	—	The date this transaction was successfully transmitted to the enterprise GL. `null` while `gl_status_cd` is `U` or `F`.

2.3 `account`

The GL chart of accounts. Each account represents a line in the general ledger that transactions post against. Accounts are managed by the accounting team and loaded from the enterprise chart of accounts.

Field	Type	Required	Default	Description
`account_id`	`serial`	Yes	Auto	Primary key. Referenced by `transaction.account_id`.
`account_class`	`varchar(100)`	No	—	Classification grouping (e.g., `Deferred`, `Unbilled`, `Revenue`, `Liability`). Used for aggregating transaction totals by type in reporting and AR true-up calculations.
`account_description`	`varchar(500)`	No	—	Descriptive label for the account.
`account_number`	`varchar(50)`	No	—	The GL account number. Used for matching and reporting against the enterprise chart of accounts.
`account_full_name`	`varchar(500)`	No	—	Full display name including any hierarchical path. Shown in transaction display views.
`status_cd`	`varchar(20)`	No	—	Active/inactive flag. `A` = Active, `I` = Inactive. Only active accounts should receive new postings. See Section 3.

NOTE

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

2.4 `fiscal_period`

Defines accounting periods (one row per calendar month by default). Controls which period transactions post into, whether the period is open for new postings, and whether it is open for cash-related postings. A separate current_cash_ind allows cash processing to operate in a different open period from general accounting when needed.

Field	Type	Required	Default	Description
`fiscal_period_id`	`serial`	Yes	Auto	Primary key. Referenced by `transaction.posting_period_id`.
`period_start_dt`	`date`	No	—	First day of the accounting period (inclusive).
`period_end_dt`	`date`	No	—	Last day of the accounting period (inclusive).
`period_closed_dt`	`date`	No	—	Date the period was closed. `null` if still open. Once set, no new transactions should post to this period.
`period_year`	`integer`	No	—	Fiscal year (e.g., `2026`).
`period_month`	`integer`	No	—	Fiscal month (1–12).
`period_ref`	`varchar(20)`	No	—	Human-readable period reference (e.g., `2026-01`). Matches `transaction.posting_period_ref`.
`current_ind`	`boolean`	No	—	When `true`, this is the currently active accounting period. At most one period should have this flag set to `true` at any time. Updated by the accounting job runner before each job batch executes.
`current_cash_ind`	`boolean`	No	—	When `true`, this period is currently open for cash-related postings. Allows cash processing to operate independently from the general accounting period.

2.5 `accounting_job_execution_history`

Audit trail for batch accounting job executions. Each row records one run of one job type: when it started, when it finished, the as-of date it processed through, and its outcome. Populated automatically by the accounting job runner after each job completes (successfully or with failure).

Field	Type	Required	Default	Description
`accounting_job_execution_history_id`	`serial`	Yes	Auto	Primary key.
`job_cd`	`varchar(50)`	Yes	—	The job type code (e.g., `REV`, `BILL`, `CR`, `APP`, `PO`, `FX`, `TRUE`, `CL`). Matches the `source_cd` values on `transaction`. See Section 5.
`effective_dt`	`date`	Yes	—	The "as-of" date the job was run against. Transactions are created for source records on or before this date.
`started_at`	`timestamp`	Yes	Now	Timestamp when the job began executing.
`completed_at`	`timestamp`	No	—	Timestamp when the job finished. `null` if the job is still running.
`status_cd`	`varchar(20)`	Yes	—	Execution outcome. See Section 3. Values: `RUNNING`, `SUCCESS`, `FAILED`.
`result_summary`	`jsonb`	No	—	Structured summary of the job result. On success: `{ "processedCount": 42, "batchIds": [...] }`. On failure: `{ "error": "..." }`.
`created_by`	`varchar(100)`	No	—	User or system actor that initiated the job. Defaults to `SYSTEM` for scheduled runs.

3. Status Lifecycle

3.1 Transaction GL Status (`transaction.gl_status_cd`)

Tracks whether a subledger transaction has been transmitted to the enterprise GL system.

Status	Code	Description	Allowed Transitions
Unposted	`U`	Transaction created in the subledger but not yet sent to the enterprise GL. Default state for all new transactions.	→ Posted (`P`), → Excluded (`X`), → Failed (`F`)
Posted	`P`	Transaction successfully transmitted to and accepted by the enterprise GL. `gl_posting_dt` is set.	— (terminal)
Excluded	`X`	Transaction intentionally excluded from GL transmission (e.g., internal adjustments, test data).	— (terminal)
Failed	`F`	GL transmission was attempted but failed. Eligible for retry.	→ Posted (`P`), → Excluded (`X`)

mermaid

stateDiagram-v2
    [*] --> U : Transaction created by accounting job
    U --> P : GL transmission succeeds
    U --> X : Manually excluded
    U --> F : GL transmission fails
    F --> P : Retry succeeds
    F --> X : Manually excluded after failure

Transition: U → P

Trigger: GL posting batch transmits the transaction to the enterprise GL and receives confirmation.
Preconditions: gl_status_cd is U or F.
Side-effects: transaction.gl_posting_dt set to the transmission date; transaction.gl_status_cd set to P.

Transition: U → F

Trigger: GL posting batch attempts transmission but the enterprise GL rejects or does not respond.
Preconditions: gl_status_cd is U.
Side-effects: transaction.gl_status_cd set to F. The result_summary on the corresponding accounting_job_execution_history row records the error.

Transition: F → P

Trigger: GL posting batch retries the previously failed transaction and transmission succeeds.
Preconditions: gl_status_cd is F.
Side-effects: transaction.gl_posting_dt set; transaction.gl_status_cd set to P.

Transition: U/F → X

Trigger: Manual exclusion by an administrator (e.g., test data cleanup or internal adjustment identified as not requiring GL posting).
Preconditions: gl_status_cd is U or F.
Side-effects: transaction.gl_status_cd set to X. No GL entry is created.

3.2 Account Status (`account.status_cd`)

Controls whether an account can receive new transaction postings.

Status	Code	Description	Allowed Transitions
Active	`A`	Account is available for new postings.	→ Inactive (`I`)
Inactive	`I`	Account is retired. Existing transactions remain but no new postings should reference this account.	→ Active (`A`)

Transition: A → I

Trigger: Accounting team retires a GL account (e.g., chart of accounts restructuring).
Preconditions: None enforced at the database level.
Side-effects: Accounting job logic must exclude inactive accounts when selecting posting targets. Existing transaction rows referencing this account are unaffected.

3.3 Job Execution Status (`accounting_job_execution_history.status_cd`)

Tracks the outcome of a single batch accounting job run.

Status	Code	Description	Allowed Transitions
Running	`RUNNING`	Job is currently executing. `completed_at` is `null`.	→ Success (`SUCCESS`), → Failed (`FAILED`)
Success	`SUCCESS`	Job completed without errors. `result_summary` contains processing counts and batch IDs.	— (terminal)
Failed	`FAILED`	Job encountered an error and did not complete normally. `result_summary` contains the error message.	— (terminal)

mermaid

stateDiagram-v2
    [*] --> RUNNING : Job initiated
    RUNNING --> SUCCESS : Job completes without error
    RUNNING --> FAILED : Job encounters error

Transition: RUNNING → SUCCESS

Trigger: The accounting job completes all processing steps without throwing an error.
Preconditions: status_cd is RUNNING.
Side-effects: completed_at set to the current timestamp; result_summary populated with { processedCount, batchIds }.

Transition: RUNNING → FAILED

Trigger: The accounting job throws an unhandled exception during processing.
Preconditions: status_cd is RUNNING.
Side-effects: completed_at set to the current timestamp; result_summary populated with { error: "..." }.

4. Validation & Database Constraints

Unique Constraints

Table	Constraint	Columns	Business Rule
`transaction`	(none defined at DB level)	—	Uniqueness is enforced by the application-layer idempotency pattern: all prior transactions for a given `source_cd` + `source_id` combination are deleted before new ones are inserted.

Business Validation

Batch must net to zero: All transaction rows sharing a batch_id must have debits equal to credits (sum nets to zero). Enforced at the application layer during batch creation — no database constraint.
batch_id is required: transaction.batch_id has a NOT NULL constraint. Every transaction must belong to a batch, even if the batch contains only two rows (one debit, one credit).
Active-only posting: Only accounts with account.status_cd = A should receive new postings. Enforced at the application layer; no database constraint prevents posting to inactive accounts.
Closed-period guard: Once fiscal_period.period_closed_dt is set, no new transactions should post to that period. Enforced at the application layer.
Single current period: At most one fiscal_period row should have current_ind = true at any time. Enforced at the application layer: the period-setting operation resets all rows to false before setting the target period to true.
Period coverage: Each posting date should fall within exactly one fiscal period (no gaps, no overlaps between period_start_dt and period_end_dt). Enforced by the accounting team when defining periods.
job_cd, effective_dt, started_at, and status_cd are required: accounting_job_execution_history has NOT NULL constraints on these four fields. All other fields are optional.

Posting Date Logic

The accounting jobs apply a consistent rule to determine transaction.posting_dt:

If the source record's created_dt is before the driver date (e.g., revenue recognition date, check date, or billing due date), the posting_dt is set to the first day of the fiscal period that contains the driver date.
If the source record's created_dt is on or after the driver date, the posting_dt is set to the created_dt.

This ensures that retroactive entries land in the correct accounting period while current-day entries post as of their creation date.

IMPORTANT

The transaction_ref_dt always records the original business date of the event (the driver date), regardless of what posting_dt resolves to. This is the distinction between when something economically occurred versus when it was posted.

5. Code Master Values

5.1 `CLASS_CD` — Transaction Class

Used by transaction.class_cd. Categorizes the financial nature of a journal entry.

Code	Description	Behavior / When Used
`REV`	Revenue	Entries produced by the revenue recognition job (`source_cd` = `REV`). Records realized revenue from revenue schedules.
`AR`	Accounts Receivable	Entries produced by the billing job (`source_cd` = `BILL`). Records the AR balance created when billing items are processed.
`CASH`	Cash	Entries produced by the cash receipt job (`source_cd` = `CR`) and payout job (`source_cd` = `PO`). Records cash inflows (receipts) and outflows (payouts).
`TAX`	Tax / VAT	Entries related to withholding tax or VAT. Produced when tax deductions are applied.
`FX`	Foreign Exchange	Gain/loss entries from currency conversions. Produced by the FX adjustment job (`source_cd` = `FX`).

Default on creation: No default — set explicitly by each accounting job based on the nature of the source record.

5.2 `SOURCE_CD` — Transaction Source Job

Used by transaction.source_cd and accounting_job_execution_history.job_cd. Identifies which accounting job produced the transactions.

Code	Description	Source Entity	GL Posting Timing
`REV`	Revenue recognition job	Revenue schedules (`revenue_item_schedules`)	At job execution, when `revenue_dt` is on or before `effective_dt`
`BILL`	Billing job	Billing items (`billing_item`)	At job execution, when billing item is created/due
`CR`	Cash receipt posting job	Cash receipts (`cash_receipt`)	At job execution, after receipt is posted
`APP`	Cash application job	REV-type cash receipt applications (`cash_receipt_application`)	At job execution after worksheet reaches Applied (`P`) status
`PO`	Payout job	PAY-type cash receipt payouts (`cash_receipt_payout`)	At job execution, only after bank confirms payment (`ACKNOWLEDGED` or `PAID`)
`FX`	FX adjustment job	Currency conversion events	At job execution, when FX adjustments are processed
`TRUE`	AR true-up job	Period-end AR reconciliation (aggregated by `rev_ref`)	At job execution during period-end close
`CL`	Client ledger job	Client ledger entries (`cash_receipt_client_ledger`)	At job execution after worksheet reaches Applied (`P`) status

Default on creation: No default — set explicitly by the job that creates the transaction.

IMPORTANT

The distinction between APP and PO posting timing is critical. REV application transactions (APP) post to the GL at Apply time because the money stays in UTA accounts. PAY payout transactions (PO) post to the GL only after the bank confirms the payment has been sent — because the money must physically leave the building before the GL entry is made. This two-stage posting model is fundamental to the system's reversal and return logic.

5.3 `TYPE_CD` — Debit/Credit Indicator

Used by transaction.type_cd.

Code	Description	Behavior / When Used
`D`	Debit	The debit side of a double-entry journal entry.
`C`	Credit	The credit side of a double-entry journal entry.

Default on creation: No default — set explicitly based on which side of the journal entry this row represents.

5.4 `GL_STATUS_CD` — GL Transmission Status

Used by transaction.gl_status_cd. See Section 3.1 for the full lifecycle.

Code	Description	Behavior / When Used
`U`	Unposted	Default on creation. Transaction is in the subledger, awaiting GL transmission.
`P`	Posted	GL transmission succeeded. `gl_posting_dt` is set. Terminal state.
`X`	Excluded	Intentionally skipped. Used for internal adjustments or test data that should not reach the enterprise GL. Terminal state.
`F`	Failed	GL transmission was attempted but failed. Eligible for retry.

Default on creation: U

5.5 `ACCOUNT_STATUS_CD` — Account Active Status

Used by account.status_cd. See Section 3.2 for transitions.

Code	Description	Behavior / When Used
`A`	Active	Account is available for new transaction postings. Accounting jobs may select this account.
`I`	Inactive	Account has been retired. No new postings should reference it. Existing transactions are unaffected.

Default on creation: A

5.6 `JOB_STATUS_CD` — Job Execution Outcome

Used by accounting_job_execution_history.status_cd. See Section 3.3 for transitions.

Code	Description	Behavior / When Used
`RUNNING`	Job is currently executing	Set at job start. `completed_at` is `null`.
`SUCCESS`	Job completed successfully	Set at job end when no errors occurred. `result_summary` contains counts and batch IDs.
`FAILED`	Job encountered an error	Set at job end when an exception was thrown. `result_summary` contains the error message.

Default on creation: RUNNING

6. Cross-References

Document	Relationship
Worksheets Data Model	`cash_receipt_application` rows with `billing_item_detail_type_cd` = `REV` are the source records for `APP`-sourced transactions. `cash_receipt_client_ledger` rows are the source for `CL`-sourced transactions. Both post when the worksheet reaches Applied (`P`) status.
Settlements Data Model	`cash_receipt_payout` rows derived from PAY-type settlement items are the source for `PO`-sourced transactions. These post only after the bank confirms the payment (`ACKNOWLEDGED` or `PAID`).
Cash Receipts Data Model	Posted `cash_receipt` records are the source for `CR`-sourced transactions recording the cash inflow. `transaction.source_id` links back to `cash_receipt.cash_receipt_id`.
Billing Items Data Model	Created `billing_item` records are the source for `BILL`-sourced transactions establishing the AR balance. Revenue schedules (`revenue_item_schedules`) are the source for `REV`-sourced transactions.

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Accounting Data Model ​

1. Executive Summary ​

Purpose ​

Scope ​

2. Data Model ​

2.1 Entity-Relationship Diagram ​

2.2 transaction ​

2.3 account ​

2.4 fiscal_period ​

2.5 accounting_job_execution_history ​

3. Status Lifecycle ​

3.1 Transaction GL Status (transaction.gl_status_cd) ​

Transition: U → P ​

Transition: U → F ​

Transition: F → P ​

Transition: U/F → X ​

3.2 Account Status (account.status_cd) ​

Transition: A → I ​

3.3 Job Execution Status (accounting_job_execution_history.status_cd) ​

Transition: RUNNING → SUCCESS ​

Transition: RUNNING → FAILED ​

4. Validation & Database Constraints ​

Unique Constraints ​

Business Validation ​

Posting Date Logic ​

5. Code Master Values ​

5.1 CLASS_CD — Transaction Class ​

5.2 SOURCE_CD — Transaction Source Job ​

5.3 TYPE_CD — Debit/Credit Indicator ​

5.4 GL_STATUS_CD — GL Transmission Status ​

5.5 ACCOUNT_STATUS_CD — Account Active Status ​

5.6 JOB_STATUS_CD — Job Execution Outcome ​

6. Cross-References ​

Accounting Data Model

1. Executive Summary

Purpose

Scope

2. Data Model

2.1 Entity-Relationship Diagram

2.2 `transaction`

2.3 `account`

2.4 `fiscal_period`

2.5 `accounting_job_execution_history`

3. Status Lifecycle

3.1 Transaction GL Status (`transaction.gl_status_cd`)

Transition: U → P

Transition: U → F

Transition: F → P

Transition: U/F → X

3.2 Account Status (`account.status_cd`)

Transition: A → I

3.3 Job Execution Status (`accounting_job_execution_history.status_cd`)

Transition: RUNNING → SUCCESS

Transition: RUNNING → FAILED

4. Validation & Database Constraints

Unique Constraints

Business Validation

Posting Date Logic

5. Code Master Values

5.1 `CLASS_CD` — Transaction Class

5.2 `SOURCE_CD` — Transaction Source Job

5.3 `TYPE_CD` — Debit/Credit Indicator

5.4 `GL_STATUS_CD` — GL Transmission Status

5.5 `ACCOUNT_STATUS_CD` — Account Active Status

5.6 `JOB_STATUS_CD` — Job Execution Outcome

6. Cross-References