Write-Offs Data Model

1. Executive Summary

Purpose

The write-off data model supports the identification, approval, and execution of write-offs for uncollectable receivables. When a receivable is deemed uncollectable — whether due to aging, buyer insolvency, bankruptcy, or agent request — it is grouped into a write-off packet and routed through a multi-level approval chain before the write-off is executed against the general ledger.

The write-off domain is built around four tables: write_off_packet, packet_receivable, packet_status_history, and packet_document. Together they capture the grouping of receivables into approval units, the eligibility and documentation metadata for each receivable, a complete audit trail of every workflow action, and the evidentiary documents that justify the write-off.

Scope

Covered:

write_off_packet — Top-level container that groups one or more receivables into a single approval unit, tracking aggregate totals, workflow status, and the current position in the approval chain.
packet_receivable — Junction between a packet and the specific billing_item_detail records being written off; stores eligibility criteria and documentation preferences for each receivable.
packet_status_history — Immutable audit trail recording every status transition, approval action, rejection, and recovery event across the packet's lifecycle.
packet_document — Supporting documentation attached at the packet level (shared) or the individual receivable level, providing the evidentiary basis for the write-off.

Not covered (documented separately):

billing_item_detail (write-off fields) — see Billing Items Data Model
cash_receipt (write-off receipt creation) — see Cash Receipts Data Model

IMPORTANT

Only billing_item_detail records with billing_item_detail_type_cd = 'REV' can be written off. PAY-type details are never written off because the client's payout obligation persists regardless of collectability — the company absorbs the loss on its own commission (REV) while the PAY obligation remains unchanged. This is a foundational constraint enforced at both the validation and data-model levels.

2. Data Model

2.1 Entity-Relationship Diagram

mermaid

erDiagram
    write_off_packet ||--o{ packet_receivable : "contains"
    write_off_packet ||--o{ packet_status_history : "has history"
    write_off_packet ||--o{ packet_document : "has documents"
    packet_receivable ||--o{ packet_document : "has receivable-level documents"
    packet_receivable }o--|| billing_item_detail : "writes off"
    write_off_packet }o--|| party : "client"
    write_off_packet }o--o| cash_receipt : "offset receipt"
    billing_item_detail }o--o| write_off_packet : "written off by"

    write_off_packet {
        uuid write_off_packet_id PK
        varchar packet_name UK
        integer client_id FK
        varchar packet_eligibility_criteria_cd
        integer cash_receipt_id FK
        decimal total_commission_amt
        integer receivable_count
        varchar packet_status_cd
        varchar current_approver_role
        timestamp submitted_dt
        integer submitted_by_user_id FK
        timestamp completed_dt
        integer completed_by_user_id FK
        timestamp recovered_dt
        integer recovered_by_user_id FK
        timestamp rejected_dt
        integer rejected_by_user_id FK
        varchar rejection_reason
    }

    packet_receivable {
        uuid packet_receivable_id PK
        uuid write_off_packet_id FK
        integer billing_item_detail_id FK
        varchar eligibility_criteria_cd
        boolean use_packet_document_ind
    }

    packet_status_history {
        uuid packet_status_history_id PK
        uuid write_off_packet_id FK
        varchar from_status_cd
        varchar to_status_cd
        varchar action_cd
        varchar approver_role
        text comment_text
        integer action_by_user_id FK
        timestamp action_dt
    }

    packet_document {
        uuid packet_document_id PK
        uuid write_off_packet_id FK
        uuid packet_receivable_id FK
        varchar document_name
        varchar document_type_cd
        text document_url
        bigint document_size_bytes
        varchar mime_type
        integer uploaded_by_user_id FK
        timestamp uploaded_dt
    }

2.2 `write_off_packet`

The primary entity for grouping receivables into write-off submissions. Each packet belongs to a single client and tracks the approval workflow, aggregated totals, and key timestamps across its lifecycle.

Field	Type	Required	Default	Description
`write_off_packet_id`	`uuid`	Yes	Random UUID	Primary key.
`packet_name`	`varchar(255)`	Yes	—	Human-readable name for the packet. Must be unique across all packets (unique constraint).
`client_id`	`integer`	Yes	—	FK to `party.party_id`. The client whose receivables are being written off. All receivables in the packet must belong to this client.
`packet_eligibility_criteria_cd`	`varchar(50)`	No	—	Optional packet-level eligibility criteria code. When set, serves as the default eligibility suggestion for receivables added to the packet. See Section 5.4.
`cash_receipt_id`	`integer`	No	—	FK to `cash_receipt.cash_receipt_id`. Populated when the packet is approved and the write-off is executed, linking to the offsetting `WRITE_OFF` cash receipt entry.
`total_commission_amt`	`decimal(20,2)`	Yes	`'0.00'`	Denormalized. Sum of `billing_item_detail_amt` for all REV-type receivables in the packet. Recalculated by the aggregate calculator whenever receivables are added or removed. Drives approval routing thresholds.
`receivable_count`	`integer`	Yes	`0`	Denormalized. Count of REV-type receivables in the packet. Recalculated when receivables are added or removed.
`packet_status_cd`	`varchar(50)`	Yes	—	Current workflow status. See Section 3.
`current_approver_role`	`varchar(50)`	No	—	The role responsible for the next approval action. Null when no approval is pending (e.g., in `DRAFT`, `COMPLETE`, or `RECOVERED` status). See Section 5.2.
`submitted_dt`	`timestamp`	No	—	Timestamp when the packet was first submitted for approval.
`submitted_by_user_id`	`integer`	No	—	FK to `users.user_id`. The user who submitted the packet.
`completed_dt`	`timestamp`	No	—	Timestamp when the packet reached `COMPLETE` status (final approval granted).
`completed_by_user_id`	`integer`	No	—	FK to `users.user_id`. The user who executed the final approval.
`recovered_dt`	`timestamp`	No	—	Timestamp when the packet was recovered (write-off reversed).
`recovered_by_user_id`	`integer`	No	—	FK to `users.user_id`. The user who initiated recovery.
`rejected_dt`	`timestamp`	No	—	Timestamp of the most recent rejection. Cleared on resubmission.
`rejected_by_user_id`	`integer`	No	—	FK to `users.user_id`. The user who last rejected the packet.
`rejection_reason`	`varchar(2000)`	No	—	Free-text reason for the most recent rejection. Required when rejecting; cleared on resubmission.

NOTE

total_commission_amt and receivable_count are denormalized for two reasons: (1) query performance — list views and search results display aggregate totals without requiring joins to packet_receivable and billing_item_detail; and (2) approval routing — the status machine uses total_commission_amt to determine whether the approval chain must extend to the CFO or MD.

NOTE

Pure audit columns (created_by_user_id, created_dt, updated_by_user_id, updated_dt) are omitted from the table above as they carry no additional business meaning beyond standard audit tracking.

2.3 `packet_receivable`

The collection of receivables that belong to a write-off packet. Each row links one billing_item_detail to one write_off_packet and carries write-off metadata for that specific receivable.

Field	Type	Required	Default	Description
`packet_receivable_id`	`uuid`	Yes	Random UUID	Primary key.
`write_off_packet_id`	`uuid`	Yes	—	FK to `write_off_packet.write_off_packet_id`. Cascade delete: removing a packet removes all its receivables.
`billing_item_detail_id`	`integer`	Yes	—	FK to `billing_item_detail.billing_item_detail_id`. The specific receivable being written off. Must be of type `REV`.
`eligibility_criteria_cd`	`varchar(50)`	Yes	—	The reason this receivable qualifies for write-off. See Section 5.4.
`use_packet_document_ind`	`boolean`	Yes	`false`	When `true`, this receivable relies on packet-level documentation. When `false`, the receivable must have its own `packet_document` rows with a populated `packet_receivable_id`.

NOTE

2.4 `packet_status_history`

A complete, immutable audit trail of every status transition and workflow action performed on a packet. Records are only ever created, never updated or deleted (except by cascade when the parent packet is deleted in DRAFT status).

Field	Type	Required	Default	Description
`packet_status_history_id`	`uuid`	Yes	Random UUID	Primary key.
`write_off_packet_id`	`uuid`	Yes	—	FK to `write_off_packet.write_off_packet_id`. Cascade delete.
`from_status_cd`	`varchar(50)`	No	—	The status before the transition. Null for the initial `DRAFT` creation record.
`to_status_cd`	`varchar(50)`	Yes	—	The status after the transition.
`action_cd`	`varchar(50)`	Yes	—	The action that triggered this transition. See Section 5.5.
`approver_role`	`varchar(50)`	No	—	The role of the approver who performed this action. Null for non-approval actions such as `SUBMIT` or `CANCEL`. See Section 5.2.
`comment_text`	`text`	No	—	Optional comment. Required when `action_cd = 'REJECT'`.
`action_by_user_id`	`integer`	Yes	—	FK to `users.user_id`. The user who performed the action.
`action_dt`	`timestamp`	Yes	Now	Timestamp of the action.

2.5 `packet_document`

Document attachments providing evidence and justification for write-off requests. Documents can be associated at the packet level (shared across all receivables) or at the individual receivable level.

Field	Type	Required	Default	Description
`packet_document_id`	`uuid`	Yes	Random UUID	Primary key.
`write_off_packet_id`	`uuid`	Yes	—	FK to `write_off_packet.write_off_packet_id`. Every document belongs to a packet. Cascade delete.
`packet_receivable_id`	`uuid`	No	—	FK to `packet_receivable.packet_receivable_id`. When null, the document is a packet-level document shared across all receivables. When populated, the document is specific to one receivable. Cascade delete.
`document_name`	`varchar(255)`	Yes	—	Display name of the document.
`document_type_cd`	`varchar(50)`	Yes	—	Classification of the document. See Section 5.6.
`document_url`	`text`	Yes	—	Storage location (e.g., object storage URL or file system path).
`document_size_bytes`	`bigint`	No	—	File size in bytes.
`mime_type`	`varchar(100)`	No	—	MIME type of the uploaded file (e.g., `application/pdf`, `image/png`).
`uploaded_by_user_id`	`integer`	Yes	—	FK to `users.user_id`. The user who uploaded the document.
`uploaded_dt`	`timestamp`	Yes	Now	Timestamp of the upload.

3. Status Lifecycle

3.1 Packet Status (`write_off_packet.packet_status_cd`)

The write-off packet follows a forward-only approval workflow. Once a packet advances past a status, it never returns to that status. Rejected packets are resubmitted, restarting the approval chain from the Agent level.

Status	Code	Description	Allowed Transitions
Draft	`DRAFT`	Packet created, not yet submitted. Editable. Deletable.	→ `SUBMITTED`
Submitted	`SUBMITTED`	Submitted for approval. Awaiting Agent review.	→ `APPROVED_AGENT`, → `REJECTED_AGENT`
Approved (Agent)	`APPROVED_AGENT`	Approved by Agent. Awaiting Department Head review.	→ `APPROVED_DH`, → `REJECTED_DH`
Approved (Dept Head)	`APPROVED_DH`	Approved by Department Head. Awaiting VP review.	→ `APPROVED_VP`, → `REJECTED_VP`
Approved (VP)	`APPROVED_VP`	Approved by VP Client Accounting. Terminal approver for packets under $50,000.	→ `COMPLETE` (if `total_commission_amt` < $50,000), → `APPROVED_CFO`, → `REJECTED_CFO`
Approved (CFO)	`APPROVED_CFO`	Approved by CFO. Terminal approver for packets $50,000–$250,000.	→ `COMPLETE` (if `total_commission_amt` ≤ $250,000), → `APPROVED_MD`, → `REJECTED_MD`
Approved (MD)	`APPROVED_MD`	Approved by Managing Director. Required for packets exceeding $250,000.	→ `COMPLETE`
Rejected (Agent)	`REJECTED_AGENT`	Rejected by Agent. Packet returns to editable state.	→ `RESUBMITTED`
Rejected (Dept Head)	`REJECTED_DH`	Rejected by Department Head. Packet returns to editable state.	→ `RESUBMITTED`
Rejected (VP)	`REJECTED_VP`	Rejected by VP Client Accounting. Packet returns to editable state.	→ `RESUBMITTED`
Rejected (CFO)	`REJECTED_CFO`	Rejected by CFO. Packet returns to editable state.	→ `RESUBMITTED`
Rejected (MD)	`REJECTED_MD`	Rejected by Managing Director. Packet returns to editable state.	→ `RESUBMITTED`
Resubmitted	`RESUBMITTED`	Resubmitted after rejection. Re-enters at Agent approval.	→ `APPROVED_AGENT`, → `REJECTED_AGENT`
Complete	`COMPLETE`	Write-off executed. All required approvals obtained. GL entry created.	→ `RECOVERED`
Recovered	`RECOVERED`	Write-off reversed. Receivable is collectable again. Terminal state.	—

mermaid

stateDiagram-v2
    [*] --> DRAFT : Packet created
    DRAFT --> SUBMITTED : Submit
    SUBMITTED --> APPROVED_AGENT : Agent approves
    SUBMITTED --> REJECTED_AGENT : Agent rejects
    APPROVED_AGENT --> APPROVED_DH : Dept Head approves
    APPROVED_AGENT --> REJECTED_DH : Dept Head rejects
    APPROVED_DH --> APPROVED_VP : VP approves
    APPROVED_DH --> REJECTED_VP : VP rejects
    APPROVED_VP --> COMPLETE : total < $50K
    APPROVED_VP --> APPROVED_CFO : CFO required
    APPROVED_VP --> REJECTED_CFO : CFO rejects
    APPROVED_CFO --> COMPLETE : total ≤ $250K
    APPROVED_CFO --> APPROVED_MD : MD required
    APPROVED_CFO --> REJECTED_MD : MD rejects
    APPROVED_MD --> COMPLETE : MD approves
    REJECTED_AGENT --> RESUBMITTED : Resubmit
    REJECTED_DH --> RESUBMITTED : Resubmit
    REJECTED_VP --> RESUBMITTED : Resubmit
    REJECTED_CFO --> RESUBMITTED : Resubmit
    REJECTED_MD --> RESUBMITTED : Resubmit
    RESUBMITTED --> APPROVED_AGENT : Agent approves
    RESUBMITTED --> REJECTED_AGENT : Agent rejects
    COMPLETE --> RECOVERED : Recover

Transition: `DRAFT` → `SUBMITTED`

Trigger: User submits the packet for approval.
Preconditions: Packet contains at least one receivable; all receivables are of type REV; every receivable has an eligibility_criteria_cd; every receivable has documentation (packet-level or receivable-level); no receivable is in another active packet.
Side-effects: write_off_packet.submitted_dt and submitted_by_user_id are set; current_approver_role is set to AGENT; a packet_status_history record is created with action_cd = 'SUBMIT'.

Transition: `SUBMITTED` / `RESUBMITTED` → `APPROVED_AGENT`

Trigger: User with the AGENT role approves the packet.
Preconditions: current_approver_role = 'AGENT'.
Side-effects: current_approver_role advanced to DEPT_HEAD; a packet_status_history record is created with action_cd = 'APPROVE' and approver_role = 'AGENT'.

Transition: Any `APPROVED_` → Next `APPROVED_` or `COMPLETE`

Trigger: User with the corresponding approver role approves the packet.
Preconditions: current_approver_role matches the approving user's role.
Side-effects: current_approver_role advanced to the next role in the chain, or set to null if the packet completes; a packet_status_history record is created with action_cd = 'APPROVE' and approver_role set to the acting role. On COMPLETE: write_off_packet.completed_dt and completed_by_user_id are set; billing_item_detail.write_off_status_cd is set to 'WRITTEN_OFF' for each packet receivable; billing_item_detail.write_off_dt and write_off_packet_id are populated; billing_item_detail.exclude_from_cecl_ind is set to true; a WRITE_OFF type cash_receipt is created and linked via write_off_packet.cash_receipt_id.

Transition: Any active status → `REJECTED_*`

Trigger: Approver at the current level rejects the packet.
Preconditions: current_approver_role matches the rejecting user's role; a rejection reason is provided.
Side-effects: write_off_packet.rejected_dt, rejected_by_user_id, and rejection_reason are set; current_approver_role is cleared; a packet_status_history record is created with action_cd = 'REJECT' and comment_text populated.

Transition: `REJECTED_*` → `RESUBMITTED`

Trigger: User resubmits the rejected packet after corrections.
Preconditions: Packet is in any REJECTED_* status.
Side-effects: rejected_dt, rejected_by_user_id, and rejection_reason cleared; current_approver_role reset to AGENT; a packet_status_history record is created with action_cd = 'RESUBMIT'.

Transition: `COMPLETE` → `RECOVERED`

Trigger: User initiates recovery of a completed write-off.
Preconditions: packet_status_cd = 'COMPLETE'.
Side-effects: write_off_packet.recovered_dt and recovered_by_user_id are set; billing_item_detail.write_off_status_cd is set to 'RECOVERED' and recovered_dt is populated for each packet receivable; a reversal worksheet is created to post the offsetting GL entries; a packet_status_history record is created with action_cd = 'RECOVER'.

3.2 Write-Off Status on `billing_item_detail`

The billing_item_detail table independently tracks the write-off state of each individual receivable via write_off_status_cd.

Status	Code	Description	Allowed Transitions
Not Written Off	`NOT_WRITTEN_OFF`	Default. The receivable has not been written off. Active and collectable.	→ `WRITTEN_OFF`
Written Off	`WRITTEN_OFF`	Receivable has been written off. Set when the parent packet reaches `COMPLETE`. `write_off_dt` and `write_off_packet_id` populated; `exclude_from_cecl_ind` set to `true`.	→ `RECOVERED`
Recovered	`RECOVERED`	Write-off reversed. The receivable is collectable again. `recovered_dt` populated.	—

mermaid

stateDiagram-v2
    [*] --> NOT_WRITTEN_OFF : Default on creation
    NOT_WRITTEN_OFF --> WRITTEN_OFF : Packet reaches COMPLETE
    WRITTEN_OFF --> RECOVERED : Packet recovered

4. Validation & Database Constraints

Unique Constraints

Table	Constraint	Columns	Business Rule
`write_off_packet`	`write_off_packet_packet_name_unique`	`(packet_name)`	No two packets can share the same name.

Cascade Delete Constraints

Table	Parent	Behavior
`packet_receivable`	`write_off_packet`	Deleting a packet removes all its receivables.
`packet_status_history`	`write_off_packet`	Deleting a packet removes all its history.
`packet_document`	`write_off_packet`	Deleting a packet removes all its documents.
`packet_document`	`packet_receivable`	Deleting a receivable removes documents specific to that receivable.

Business Validation

REV-only constraint: Only billing_item_detail records with billing_item_detail_type_cd = 'REV' are eligible for write-off. Enforced at the application layer during packet_receivable creation and validated again before submission.
Single-packet exclusivity: A given billing_item_detail can belong to at most one active write-off packet at a time. "Active" means any status other than RECOVERED. Enforced at the application layer by checking billing_item_detail.write_off_packet_id during the add-receivable operation.
Client consistency: All receivables within a single packet must belong to the same client as write_off_packet.client_id. Validated when adding each receivable.
Eligibility criteria requirement: Every packet_receivable must have a non-null eligibility_criteria_cd before the packet can be submitted.
Documentation requirement: Each receivable must have supporting documentation — either packet-level (when use_packet_document_ind = true and at least one packet-level packet_document exists) or receivable-level (at least one packet_document with this packet_receivable_id).
Minimum amount threshold: Receivables with billing_item_detail_amt below $100.00 are not eligible for write-off, preventing the approval workflow overhead for trivially small amounts.
Aging threshold recommendation: Receivables outstanding for 180 or more days (calculated from invoice date) are automatically recommended for the AGED eligibility criteria. Younger receivables may still qualify under other criteria but require manual eligibility assignment.
Submission completeness: Before a packet can be submitted, it must contain at least one receivable; all receivables must be REV-type; every receivable must have an eligibility_criteria_cd; every receivable must have documentation; and no receivable may be in another active packet.

NOTE

PoC Artifact: Documentation validation is disabled in the PoC to simplify the demo workflow. The validation rules exist in the codebase but are bypassed. Re-enable for production deployment.

NOTE

PoC Artifact: The PoC simplifies the multi-level approval chain to a single APPROVED status (skipping APPROVED_AGENT, APPROVED_DH, APPROVED_VP, APPROVED_CFO, APPROVED_MD). The production system must implement the full role-based chain with amount-based routing. The PACKET_STATUS constants in the PoC DTO include all granular statuses for future use.

5. Code Master Values

5.1 `PACKET_STATUS_CD`

Used by write_off_packet.packet_status_cd.

Code	Description	Behavior / When Used
`DRAFT`	Packet created, not yet submitted	Default on creation. Editable and deletable. `current_approver_role` is null.
`SUBMITTED`	Submitted for approval	Set on first submission. `current_approver_role = 'AGENT'`.
`APPROVED_AGENT`	Approved by Agent	`current_approver_role` advances to `DEPT_HEAD`.
`APPROVED_DH`	Approved by Department Head	`current_approver_role` advances to `VP_CLIENT_ACCT`.
`APPROVED_VP`	Approved by VP Client Accounting	Terminal for packets under $50,000 (`→ COMPLETE`); otherwise `current_approver_role` advances to `CFO`.
`APPROVED_CFO`	Approved by CFO	Terminal for packets $50,000–$250,000 (`→ COMPLETE`); otherwise `current_approver_role` advances to `MD`.
`APPROVED_MD`	Approved by Managing Director	Always transitions to `COMPLETE`. Required for packets over $250,000.
`REJECTED_AGENT`	Rejected by Agent	Packet is editable. Can be resubmitted.
`REJECTED_DH`	Rejected by Department Head	Packet is editable. Can be resubmitted.
`REJECTED_VP`	Rejected by VP Client Accounting	Packet is editable. Can be resubmitted.
`REJECTED_CFO`	Rejected by CFO	Packet is editable. Can be resubmitted.
`REJECTED_MD`	Rejected by Managing Director	Packet is editable. Can be resubmitted.
`RESUBMITTED`	Resubmitted after rejection	Re-enters approval chain at `AGENT` level.
`COMPLETE`	Write-off executed	All approvals obtained. `billing_item_detail.write_off_status_cd` set to `WRITTEN_OFF`. Offsetting `cash_receipt` created.
`RECOVERED`	Write-off reversed	Terminal state. `billing_item_detail.write_off_status_cd` set to `RECOVERED`.

Default on creation: DRAFT

5.2 `current_approver_role`

Used by write_off_packet.current_approver_role and packet_status_history.approver_role. Defines the approval hierarchy and amount-based routing.

Code	Description	Behavior / When Used
`AGENT`	Agent	First-level review. Validates the business case for write-off. Set when packet is submitted or resubmitted.
`DEPT_HEAD`	Department Head	Departmental oversight. Reviews write-off impact on department financials. Set after Agent approval.
`VP_CLIENT_ACCT`	VP Client Accounting	Financial controls. Terminal approver for packets under $50,000. Set after Dept Head approval.
`CFO`	Chief Financial Officer	Executive financial authority. Terminal approver for packets $50,000–$250,000. Set after VP approval if `total_commission_amt >= $50,000`.
`MD`	Managing Director	Ultimate authority. Required for packets exceeding $250,000. Set after CFO approval if `total_commission_amt > $250,000`.

Default on creation: Null (no approval pending in DRAFT status)

5.3 Approval Amount Thresholds

The total_commission_amt on write_off_packet determines how deep the approval chain must go:

Total Commission Amount	Required Approvals
< $50,000	`AGENT` → `DEPT_HEAD` → `VP_CLIENT_ACCT` → `COMPLETE`
$50,000 – $250,000	`AGENT` → `DEPT_HEAD` → `VP_CLIENT_ACCT` → `CFO` → `COMPLETE`
> $250,000	`AGENT` → `DEPT_HEAD` → `VP_CLIENT_ACCT` → `CFO` → `MD` → `COMPLETE`

5.4 `eligibility_criteria_cd`

Used by packet_receivable.eligibility_criteria_cd and optionally by write_off_packet.packet_eligibility_criteria_cd as a packet-level default.

Code	Description	Behavior / When Used
`AGED`	Receivable outstanding for 180+ days	Automatically recommended when `billing_item_detail` has been outstanding ≥ 180 days from invoice date.
`UNCOLLECTIBLE`	Determined uncollectable through collection efforts	Manually assigned. Requires collection log documentation.
`BANKRUPTCY`	Buyer has entered bankruptcy proceedings	Manually assigned. Requires court documentation.
`AGENT_REQUEST`	Agent-initiated write-off based on business judgment	Manually assigned. Requires formal agent request document.

Default on creation: None (required before submission)

5.5 `action_cd`

Used by packet_status_history.action_cd.

Code	Description	Behavior / When Used
`CREATE`	Packet created	Written when a packet is first created (initial `DRAFT` history record).
`SUBMIT`	Initial submission for approval	Written when a `DRAFT` packet is submitted. `comment_text` not required.
`APPROVE`	Approval at the current level	Written at each approval step. `approver_role` populated. `comment_text` not required.
`REJECT`	Rejection at the current level	Written when any approver rejects. `comment_text` required.
`RESUBMIT`	Resubmission after rejection	Written when a rejected packet is resubmitted. `approver_role` is null.
`CANCEL`	Cancellation of the packet	Written if the packet is cancelled. `comment_text` required.
`RECOVER`	Recovery of a completed write-off	Written when a `COMPLETE` packet is recovered.
`COMPLETE`	Final completion of the write-off	Written when the packet reaches terminal `COMPLETE` status.

Default on creation: CREATE

5.6 `document_type_cd`

Used by packet_document.document_type_cd.

Code	Description	Behavior / When Used
`COLLECTION_LOG`	Record of collection attempts and communications	Used to document exhausted collection efforts for `UNCOLLECTIBLE` eligibility.
`CLIENT_COMM`	Client correspondence related to the receivable	Used for correspondence evidence.
`COURT_DOC`	Court filings, bankruptcy notices, or legal documents	Required for `BANKRUPTCY` eligibility.
`AGENT_REQUEST`	Formal write-off request from the responsible agent	Required for `AGENT_REQUEST` eligibility.
`OTHER`	Supporting documentation not fitting other categories	General-purpose.

Default on creation: None (required field)

5.7 `write_off_status_cd` (on `billing_item_detail`)

Used by billing_item_detail.write_off_status_cd. Tracks write-off lifecycle on each individual receivable, independent of the packet workflow.

Code	Description	Behavior / When Used
`NOT_WRITTEN_OFF`	Default. Receivable is active and collectable.	Default on `billing_item_detail` creation. `write_off_packet_id` is null. `exclude_from_cecl_ind` is `false`.
`WRITTEN_OFF`	Receivable has been written off.	Set when the parent `write_off_packet` reaches `COMPLETE`. `write_off_dt` and `write_off_packet_id` are populated. `exclude_from_cecl_ind` is set to `true`.
`RECOVERED`	Previously written-off receivable has been recovered.	Set when the parent `write_off_packet` is recovered. `recovered_dt` is populated on `billing_item_detail`.

Default on creation: NOT_WRITTEN_OFF

6. Cross-References

Document	Relationship
Billing Items Data Model	`packet_receivable.billing_item_detail_id` → `billing_item_detail.billing_item_detail_id`. The write-off fields on `billing_item_detail` (`write_off_status_cd`, `write_off_packet_id`, `write_off_dt`, `recovered_dt`, `exclude_from_cecl_ind`) are owned by the Billing Items model and updated as a side-effect of packet completion and recovery.
Cash Receipts Data Model	`write_off_packet.cash_receipt_id` → `cash_receipt.cash_receipt_id`. When a packet reaches `COMPLETE`, a `WRITE_OFF` type `cash_receipt` is created to record the offsetting GL entry. Write-off receipts bypass the standard settlement step in the worksheet service.
Parties Data Model	`write_off_packet.client_id` → `party.party_id`. Each packet is scoped to a single client; all receivables in the packet must belong to that client.

Interfaces

State Machine Proposal

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Glenn Initial Spec v53

Sample Files

Write-Offs Data Model

1. Executive Summary

Purpose

Scope

2. Data Model

2.1 Entity-Relationship Diagram

2.2 `write_off_packet`

2.3 `packet_receivable`

2.4 `packet_status_history`

2.5 `packet_document`

3. Status Lifecycle

3.1 Packet Status (`write_off_packet.packet_status_cd`)

Transition: `DRAFT` → `SUBMITTED`

Transition: `SUBMITTED` / `RESUBMITTED` → `APPROVED_AGENT`

Transition: Any `APPROVED_` → Next `APPROVED_` or `COMPLETE`

Transition: Any active status → `REJECTED_*`

Transition: `REJECTED_*` → `RESUBMITTED`

Transition: `COMPLETE` → `RECOVERED`

3.2 Write-Off Status on `billing_item_detail`

4. Validation & Database Constraints

Unique Constraints

Cascade Delete Constraints

Business Validation

5. Code Master Values

5.1 `PACKET_STATUS_CD`

5.2 `current_approver_role`

5.3 Approval Amount Thresholds

5.4 `eligibility_criteria_cd`

5.5 `action_cd`

5.6 `document_type_cd`

5.7 `write_off_status_cd` (on `billing_item_detail`)

6. Cross-References

Sample Deals

Touring Deal

Endorsement Deal

Sports Endo Deal

Write-Offs Data Model ​

1. Executive Summary ​

Purpose ​

Scope ​

2. Data Model ​

2.1 Entity-Relationship Diagram ​

2.2 write_off_packet ​

2.3 packet_receivable ​

2.4 packet_status_history ​

2.5 packet_document ​

3. Status Lifecycle ​

3.1 Packet Status (write_off_packet.packet_status_cd) ​

Transition: DRAFT → SUBMITTED ​

Transition: SUBMITTED / RESUBMITTED → APPROVED_AGENT ​

Transition: Any APPROVED_* → Next APPROVED_* or COMPLETE ​

Transition: Any active status → REJECTED_* ​

Transition: REJECTED_* → RESUBMITTED ​

Transition: COMPLETE → RECOVERED ​

3.2 Write-Off Status on billing_item_detail ​

4. Validation & Database Constraints ​

Unique Constraints ​

Cascade Delete Constraints ​

Business Validation ​

5. Code Master Values ​

5.1 PACKET_STATUS_CD ​

5.2 current_approver_role ​

5.3 Approval Amount Thresholds ​

5.4 eligibility_criteria_cd ​

5.5 action_cd ​

5.6 document_type_cd ​

5.7 write_off_status_cd (on billing_item_detail) ​

6. Cross-References ​

Write-Offs Data Model

1. Executive Summary

Purpose

Scope

2. Data Model

2.1 Entity-Relationship Diagram

2.2 `write_off_packet`

2.3 `packet_receivable`

2.4 `packet_status_history`

2.5 `packet_document`

3. Status Lifecycle

3.1 Packet Status (`write_off_packet.packet_status_cd`)

Transition: `DRAFT` → `SUBMITTED`

Transition: `SUBMITTED` / `RESUBMITTED` → `APPROVED_AGENT`

Transition: Any `APPROVED_` → Next `APPROVED_` or `COMPLETE`

Transition: Any active status → `REJECTED_*`

Transition: `REJECTED_*` → `RESUBMITTED`

Transition: `COMPLETE` → `RECOVERED`

3.2 Write-Off Status on `billing_item_detail`

4. Validation & Database Constraints

Unique Constraints

Cascade Delete Constraints

Business Validation

5. Code Master Values

5.1 `PACKET_STATUS_CD`

5.2 `current_approver_role`

5.3 Approval Amount Thresholds

5.4 `eligibility_criteria_cd`

5.5 `action_cd`

5.6 `document_type_cd`

5.7 `write_off_status_cd` (on `billing_item_detail`)

6. Cross-References