Client Framework

Appearance

Assignments Procedures ​

1. Executive Summary ​

This document specifies the step-by-step data mutation procedures for the assignments domain. Each procedure describes the exact sequence of database writes — inserts, updates, and history entries — required to create, transfer, transition, and cancel assignment records.

The assignments domain supports two categories of mutations:

  1. Responsibility mutations — creating a new responsibility assignment for a business entity and transferring that responsibility between users. These operations enforce the one-active-responsibility-per-entity invariant through application-layer checks and use a deactivate-then-create pattern for transfers.

  2. Task mutations — creating task assignments, transitioning tasks through a forward-only status lifecycle, updating task fields (title, assignee, due date), and cancelling sibling tasks when one is completed.

All mutations produce an immutable audit trail in the assignment_history table. No assignment mutation completes without a corresponding history entry.

Tables modified: assignment, assignment_history

2. Key Procedures ​

2.1 Create Responsibility Assignment ​

Operation: createResponsibility

Trigger: User explicitly assigns a single accountable person to a business entity (department, client, buyer, deal, meta-data pair, sales item, or payment term) for the first time.

Input Parameters:

  • entity_type_cd: String (required) — assignment.entity_type_cd; must be a responsibility-eligible type (DEPARTMENT, CLIENT, BUYER, DEAL, META_DATA_PAIR, SALES_ITEM, PAYMENT_TERM)
  • entity_id: Integer (conditional) — assignment.entity_id; required for integer-keyed entity types
  • entity_reference: String (conditional) — assignment.entity_reference; required for string-referenced entity types
  • meta_data_type_cd: String (conditional) — assignment.meta_data_type_cd; required when entity_type_cd = META_DATA_PAIR
  • meta_data_value: String (conditional) — assignment.meta_data_value; required when entity_type_cd = META_DATA_PAIR
  • meta_data_date_value: Date (optional) — assignment.meta_data_date_value
  • assigned_to_user_id: Integer (required) — assignment.assigned_to_user_id; FK to users.user_id
  • actor_id: String (required) — identifier for the user performing the operation

Assigns a single accountable user to a business entity at a specific hierarchy level. Enforces the constraint that exactly one active responsibility may exist per entity.

Step 1. Check for Existing Active Responsibility ​

  • Source: assignment table.
  • Action: SELECT from assignment.
  • Logic:
    • Query for a record matching all of: entity_type_cd = input entity_type_cd, entity key match (per polymorphic key pattern in Section 3.7), assignment_type_cd = 'RESPONSIBILITY', is_active_ind = true.
    • If a matching record exists, reject the operation with an error directing the caller to use the Transfer Responsibility procedure (Section 2.2) instead.

Step 2. Insert New Assignment Record ​

  • Source: Input parameters.
  • Action: INSERT into assignment.
  • Logic:
    • Set assignment_id = new random UUID.
    • Set assignment_type_cd = 'RESPONSIBILITY'.
    • Set entity_type_cd = input entity_type_cd.
    • Set entity_id = input entity_id (null if not applicable).
    • Set entity_reference = input entity_reference (null if not applicable).
    • Set meta_data_type_cd = input meta_data_type_cd (null unless entity_type_cd = META_DATA_PAIR).
    • Set meta_data_value = input meta_data_value (null unless entity_type_cd = META_DATA_PAIR).
    • Set meta_data_date_value = input meta_data_date_value (null if not provided).
    • Set assigned_to_user_id = input assigned_to_user_id.
    • Set task_status_cd = NULL.
    • Set task_title = NULL.
    • Set start_dt = NULL.
    • Set end_dt = NULL.
    • Set is_active_ind = true.
    • Set created_by, updated_by = acting user identifier; set created_dt, updated_dt = current timestamp.

Step 3. Record History Entry ​

  • Source: The assignment_id from Step 2.
  • Action: INSERT into assignment_history.
  • Logic:
    • Set assignment_history_id = new random UUID.
    • Set assignment_id = the assignment_id from Step 2.
    • Set action_cd = 'ASSIGNED'.
    • Set from_user_id = NULL (initial assignment has no predecessor).
    • Set to_user_id = input assigned_to_user_id.
    • Set from_status_cd = NULL, to_status_cd = NULL.
    • Set comment_text = NULL.
    • Set action_by_user_id = the user performing the action.
    • Set action_dt = current timestamp.

Side-effects:

  • None beyond the assignment and assignment_history inserts described above.

Postconditions:

  • Exactly one active responsibility exists for the given entity.
  • One new row in assignment with is_active_ind = true.
  • One new row in assignment_history with action_cd = 'ASSIGNED'.

2.2 Transfer Responsibility Assignment ​

Operation: transferResponsibility

Trigger: User transfers ownership of a responsibility from the current holder to a new user.

Input Parameters:

  • assignment_id: UUID (required) — assignment.assignment_id; must identify an active RESPONSIBILITY record
  • new_user_id: Integer (required) — users.user_id; must differ from the current assigned_to_user_id
  • actor_id: Integer (required) — identifier for the user performing the operation
  • reason: String (optional) — free-text transfer reason stored in assignment_history.comment_text

Transfers ownership of a responsibility from the current holder to a new user using a two-phase deactivate-then-create pattern. Both phases produce history entries.

Step 1. Validate the Existing Assignment ​

  • Source: assignment (record matching assignment_id).
  • Action: SELECT from assignment.
  • Logic:
    • Reject if the assignment does not exist.
    • Reject if assignment_type_cd != 'RESPONSIBILITY'.
    • Reject if is_active_ind = false.
    • Reject if the input new_user_id equals the current assigned_to_user_id.
    • Capture the current assigned_to_user_id as previous_user_id.

Step 2. Deactivate the Old Assignment ​

  • Source: assignment (the existing record identified in Step 1).
  • Action: UPDATE assignment.
  • Logic:
    • Set is_active_ind = false.
    • Set updated_by = acting user identifier.
    • Set updated_dt = current timestamp.

Step 3. Record DEACTIVATED History on the Old Assignment ​

  • Source: The original assignment_id (now deactivated).
  • Action: INSERT into assignment_history.
  • Logic:
    • Set assignment_history_id = new random UUID.
    • Set assignment_id = the original assignment_id.
    • Set action_cd = 'DEACTIVATED'.
    • Set from_user_id = previous_user_id.
    • Set to_user_id = NULL.
    • Set from_status_cd = NULL, to_status_cd = NULL.
    • Set comment_text = optional transfer reason.
    • Set action_by_user_id = the user performing the action.
    • Set action_dt = current timestamp.

Step 4. Create the New Assignment ​

  • Source: The deactivated assignment record from Step 2 (entity-identifying fields are copied verbatim).
  • Action: INSERT into assignment.
  • Logic:
    • Set assignment_id = new random UUID.
    • Set assignment_type_cd = 'RESPONSIBILITY'.
    • Copy entity_type_cd, entity_id, entity_reference, meta_data_type_cd, meta_data_value, meta_data_date_value from the old record.
    • Set assigned_to_user_id = input new_user_id.
    • Set task_status_cd = NULL, task_title = NULL, start_dt = NULL, end_dt = NULL.
    • Set is_active_ind = true.
    • Set created_by, updated_by = acting user identifier; set created_dt, updated_dt = current timestamp.

Step 5. Record REASSIGNED History on the New Assignment ​

  • Source: The assignment_id from Step 4 (the new record).
  • Action: INSERT into assignment_history.
  • Logic:
    • Set assignment_history_id = new random UUID.
    • Set assignment_id = the assignment_id from Step 4.
    • Set action_cd = 'REASSIGNED'.
    • Set from_user_id = previous_user_id.
    • Set to_user_id = input new_user_id.
    • Set from_status_cd = NULL, to_status_cd = NULL.
    • Set comment_text = optional transfer reason.
    • Set action_by_user_id = the user performing the action.
    • Set action_dt = current timestamp.

Side-effects:

  • The old assignment record is preserved as an inactive historical record with is_active_ind = false.

Postconditions:

  • The old assignment record has is_active_ind = false with a DEACTIVATED history entry.
  • A new assignment record exists with is_active_ind = true for the new user.
  • The new assignment has a REASSIGNED history entry capturing both from_user_id and to_user_id.
  • The one-active-responsibility-per-entity invariant is maintained.

IMPORTANT

The transfer creates a new assignment row rather than updating the existing one. This preserves the full chain of ownership: every past holder has their own assignment record (inactive) with its own history entries. The current holder is always the single record where assignment_type_cd = 'RESPONSIBILITY' and is_active_ind = true.

2.3 Create Task Assignment ​

Operation: createTask

Trigger: User creates a one-off work item tied to a transactional or organizational entity.

Input Parameters:

  • entity_type_cd: String (required) — assignment.entity_type_cd; any of the ten supported types
  • entity_id: Integer (conditional) — assignment.entity_id; required for integer-keyed entity types
  • entity_reference: String (conditional) — assignment.entity_reference; required for string-referenced entity types
  • meta_data_type_cd: String (conditional) — assignment.meta_data_type_cd; required when entity_type_cd = META_DATA_PAIR
  • meta_data_value: String (conditional) — assignment.meta_data_value; required when entity_type_cd = META_DATA_PAIR
  • meta_data_date_value: Date (optional) — assignment.meta_data_date_value
  • assigned_to_user_id: Integer (required) — assignment.assigned_to_user_id; FK to users.user_id
  • task_title: String (required) — assignment.task_title; minimum length 1
  • start_dt: Date (optional) — assignment.start_dt
  • end_dt: Date (optional) — assignment.end_dt
  • actor_id: String (required) — identifier for the user performing the operation

Creates a one-off work item tied to any supported entity type. Unlike responsibilities, multiple tasks can exist for the same entity simultaneously, and tasks carry a forward-only status lifecycle.

Step 1. Insert New Assignment Record ​

  • Source: Input parameters.
  • Action: INSERT into assignment.
  • Logic:
    • Set assignment_id = new random UUID.
    • Set assignment_type_cd = 'TASK'.
    • Set entity_type_cd = input entity_type_cd.
    • Set entity_id = input entity_id (null if not applicable).
    • Set entity_reference = input entity_reference (null if not applicable).
    • Set meta_data_type_cd = input meta_data_type_cd (null if not applicable).
    • Set meta_data_value = input meta_data_value (null if not applicable).
    • Set meta_data_date_value = input meta_data_date_value (null if not provided).
    • Set assigned_to_user_id = input assigned_to_user_id.
    • Set task_status_cd = 'OPEN' (always; not configurable on creation).
    • Set task_title = input task_title.
    • Set start_dt = input start_dt (null if not provided).
    • Set end_dt = input end_dt (null if not provided).
    • Set is_active_ind = true.
    • Set created_by, updated_by = acting user identifier; set created_dt, updated_dt = current timestamp.
    • No uniqueness check is performed — multiple tasks for the same entity are permitted.

Step 2. Record History Entry ​

  • Source: The assignment_id from Step 1.
  • Action: INSERT into assignment_history.
  • Logic:
    • Set assignment_history_id = new random UUID.
    • Set assignment_id = the assignment_id from Step 1.
    • Set action_cd = 'ASSIGNED'.
    • Set from_user_id = NULL.
    • Set to_user_id = input assigned_to_user_id.
    • Set from_status_cd = NULL, to_status_cd = NULL.
    • Set comment_text = NULL.
    • Set action_by_user_id = the user performing the action.
    • Set action_dt = current timestamp.

Side-effects:

  • None beyond the assignment and assignment_history inserts described above.

Postconditions:

  • One new row in assignment with task_status_cd = 'OPEN'.
  • One new row in assignment_history with action_cd = 'ASSIGNED'.

NOTE

Tasks are always initialized to OPEN status regardless of any input. The initial status is not configurable.

2.4 Update Task Status ​

Operation: updateTaskStatus

Trigger: User transitions a task assignment to a new status (e.g., begins work, completes work, marks as waiting).

Input Parameters:

  • assignment_id: UUID (required) — assignment.assignment_id; must identify a TASK-type record
  • new_status: String (required) — assignment.task_status_cd; the desired next status
  • actor_id: Integer (required) — identifier for the user performing the operation
  • reason: String (optional) — free-text reason stored in assignment_history.comment_text

Transitions a task assignment from its current status to a new status, subject to the forward-only state machine defined in Section 3.2.

Step 1. Validate the Transition ​

  • Source: assignment (record matching assignment_id).
  • Action: SELECT from assignment.
  • Logic:
    • Reject if the assignment does not exist.
    • Reject if assignment_type_cd != 'TASK'.
    • Reject if task_status_cd is null.
    • Read the current task_status_cd.
    • Check the requested new_status against the allowed transitions matrix (see Section 3.2). Reject if the transition is invalid.

Step 2. Update the Task Status ​

  • Source: assignment (record matching assignment_id).
  • Action: UPDATE assignment.
  • Logic:
    • Set task_status_cd = input new_status.
    • Set updated_by = acting user identifier.
    • Set updated_dt = current timestamp.

Step 3. Record History Entry ​

  • Source: The assignment_id and the status values captured in Step 1.
  • Action: INSERT into assignment_history.
  • Logic:
    • Set assignment_history_id = new random UUID.
    • Set assignment_id = input assignment_id.
    • Set action_cd = 'STATUS_CHANGED'.
    • Set from_user_id = NULL, to_user_id = NULL.
    • Set from_status_cd = the previous task_status_cd.
    • Set to_status_cd = input new_status.
    • Set comment_text = optional reason for the transition.
    • Set action_by_user_id = the user performing the action.
    • Set action_dt = current timestamp.

Side-effects:

  • None beyond the assignment update and assignment_history insert described above.

Postconditions:

  • The assignment's task_status_cd reflects the new status.
  • One new row in assignment_history with action_cd = 'STATUS_CHANGED'.

2.5 Update Task Fields ​

Operation: updateTask

Trigger: User edits one or more fields on an existing task (title, assignee, and/or due date).

Input Parameters:

  • assignment_id: UUID (required) — assignment.assignment_id; must identify a TASK-type record
  • task_title: String (optional) — assignment.task_title; new task title
  • assigned_to_user_id: Integer (optional) — assignment.assigned_to_user_id; new assignee
  • end_dt: Date or null (optional) — assignment.end_dt; new due date (explicit null clears the field)
  • actor_id: Integer (required) — identifier for the user performing the operation

Modifies editable fields on a task assignment. If the assignee changes, a REASSIGNED history entry is recorded. Changes to title and due date alone do not produce a history entry.

Step 1. Capture the Current Assignee ​

  • Source: assignment (record matching assignment_id).
  • Action: SELECT from assignment.
  • Logic:
    • Reject if the assignment does not exist.
    • Reject if assignment_type_cd != 'TASK'.
    • Read the current assigned_to_user_id and store as old_user_id.

Step 2. Update the Assignment Record ​

  • Source: Input parameters.
  • Action: UPDATE assignment.
  • Logic:
    • If task_title provided: set assignment.task_title = input task_title.
    • If assigned_to_user_id provided: set assignment.assigned_to_user_id = input assigned_to_user_id.
    • If end_dt provided (including explicit null): set assignment.end_dt = input end_dt.
    • Always set updated_by = acting user identifier.
    • Always set updated_dt = current timestamp.

Step 3. Conditionally Record Reassignment History ​

  • Source: The assignment_id and old_user_id from Step 1.
  • Action: INSERT into assignment_history (conditional).
  • Logic:
    • Only execute this step if assigned_to_user_id was provided AND differs from old_user_id.
    • Set assignment_history_id = new random UUID.
    • Set assignment_id = input assignment_id.
    • Set action_cd = 'REASSIGNED'.
    • Set from_user_id = old_user_id.
    • Set to_user_id = input assigned_to_user_id.
    • Set from_status_cd = NULL, to_status_cd = NULL.
    • Set comment_text = 'Task reassigned via edit'.
    • Set action_by_user_id = the user performing the action.
    • Set action_dt = current timestamp.

Side-effects:

  • None beyond the conditional assignment_history insert described in Step 3.

Postconditions:

  • The assignment record reflects the updated fields.
  • If the assignee changed, one new row in assignment_history with action_cd = 'REASSIGNED'.
  • If only title or due date changed, no history entry is created.

NOTE

PoC Artifact: Changes to task_title and end_dt alone do not produce a history entry. Only a change in assigned_to_user_id triggers a REASSIGNED history record. This is a PoC simplification; the production system may choose to audit all field changes.

2.6 Cancel Sibling Tasks ​

Operation: cancelSiblingTasks

Trigger: A task has been completed; the caller explicitly invokes this procedure to cancel all other active tasks for the same entity.

Input Parameters:

  • assignment_id: UUID (required) — assignment.assignment_id; the completed task used as the reference
  • actor_id: Integer (required) — identifier for the user performing the operation
  • reason: String (optional) — free-text cancellation reason stored in assignment_history.comment_text

Cancels all other active tasks for the same entity when a task is completed, preventing duplicate work when multiple users hold assignments for the same work item. Returns the count of tasks cancelled.

Step 1. Load the Reference Task ​

  • Source: assignment (record matching assignment_id).
  • Action: SELECT from assignment.
  • Logic:
    • Reject if the assignment does not exist.
    • Read entity_type_cd and entity key (entity_id or entity_reference) from the record.

Step 2. Find Sibling Tasks ​

  • Source: assignment table.
  • Action: SELECT from assignment.
  • Logic:
    • Query for all records matching: same entity_type_cd and entity key as the reference task, assignment_type_cd = 'TASK', assignment_id != the input assignment_id (exclude the triggering task).

Step 3. Cancel Each Eligible Sibling ​

  • Source: Each sibling task record from Step 2.
  • Action: For each eligible sibling, UPDATE assignment and INSERT into assignment_history.
  • Logic:
    • For each sibling, check whether its current task_status_cd permits a transition to 'CANCELLED' per the transition matrix in Section 3.2. Only siblings in OPEN, WORKING, or WAITING are eligible. Skip siblings already in COMPLETE or CANCELLED.
    • For each eligible sibling, UPDATE assignment: set task_status_cd = 'CANCELLED', set updated_by = acting user identifier, set updated_dt = current timestamp.
    • For each eligible sibling, INSERT into assignment_history: set assignment_history_id = new random UUID, set assignment_id = the sibling's assignment_id, set action_cd = 'CANCELLED', set from_user_id = NULL, to_user_id = NULL, set from_status_cd = the sibling's previous task_status_cd, set to_status_cd = 'CANCELLED', set comment_text = input reason or default 'Cancelled: sibling task {reference_assignment_id} was completed', set action_by_user_id = the user performing the action, set action_dt = current timestamp.
    • Increment a count for each sibling cancelled.

Side-effects:

  • One CANCELLED history entry created per cancelled sibling in assignment_history.

Postconditions:

  • All eligible sibling tasks now have task_status_cd = 'CANCELLED'.
  • One 'CANCELLED' history entry exists per cancelled sibling.
  • Tasks already in COMPLETE or CANCELLED status are unaffected.
  • Returns the count of sibling tasks cancelled.

IMPORTANT

Sibling cancellation is not automatic upon task completion. It is a separate, explicitly invoked procedure. The caller decides whether to trigger sibling cancellation after a task is marked COMPLETE. This keeps the completion and cancellation concerns decoupled.

2.7 Hierarchy Resolution for Responsibility Assignments ​

Operation: resolveResponsibleUser

Trigger: The system needs to determine the accountable person for an entity by walking up the four-level responsibility hierarchy.

Input Parameters:

  • sales_item_ref: String (optional) — assignment.entity_reference at Level 4; sales item reference
  • payment_term_ref: String (optional) — assignment.entity_reference at Level 4; payment term reference
  • meta_data: Array (optional) — array of {type_cd, value} pairs for META_DATA_PAIR lookups at Level 3
  • deal_reference: String (optional) — assignment.entity_reference at Level 3; deal reference string
  • client_id: Integer (optional) — assignment.entity_id at Level 2; client party ID
  • buyer_id: Integer (optional) — assignment.entity_id at Level 2; buyer party ID
  • department_id: Integer (optional) — assignment.entity_id at Level 1; department ID

Resolves the responsible person for a given entity by walking the hierarchy from most specific (Level 4) to broadest (Level 1). Short-circuits on the first active responsibility match found. This is a read-only procedure that does not mutate data.

Step 1. Execute Hierarchy Walk Lookups ​

  • Source: assignment table.
  • Action: Up to seven SELECT queries on assignment, executed in strict order; short-circuit on first match.
  • Logic:
    • Each lookup queries assignment WHERE entity_type_cd = the step's entity type, entity key matches per the polymorphic pattern, assignment_type_cd = 'RESPONSIBILITY', is_active_ind = true.
    • Execute lookups in this order, skipping any step where the corresponding input field is not provided:
      1. Level 4: SALES_ITEM — entity_reference = sales_item_ref
      2. Level 4: PAYMENT_TERM — entity_reference = payment_term_ref
      3. Level 3: META_DATA_PAIR — meta_data_type_cd + meta_data_value per each entry in meta_data array
      4. Level 3: DEAL — entity_reference = deal_reference
      5. Level 2: CLIENT — entity_id = client_id
      6. Level 2: BUYER — entity_id = buyer_id
      7. Level 1: DEPARTMENT — entity_id = department_id
    • Stop and return the result upon the first match.

Side-effects:

  • None. This is a read-only procedure.

Postconditions:

  • If a match is found: returns assigned_to_user_id, assigned_to_user_name, resolved_from_entity_type_cd, resolved_from_level (1–4), and assignment_id of the matched record.
  • If no match is found at any level: returns a "no responsible person found" result; the entity is considered unassigned.

NOTE

Task-level entity types (CASH_RECEIPT, CASH_RECEIPT_SPLIT, PAYMENT) do not participate in the hierarchy. They exist outside the resolution chain. Responsibility resolution applies only to the organizational hierarchy (Levels 1–4).

2.8 Responsibility Chain Resolution ​

Operation: resolveResponsibilityChain

Trigger: The system or user needs to view the complete responsibility picture across all hierarchy levels for a given entity, showing which level has an active owner and which levels are unassigned.

Input Parameters:

  • entity_type_cd: String (required) — the entity type to start from (e.g., DEPARTMENT, CLIENT, DEAL, SALES_ITEM, PAYMENT_TERM)
  • entity_key: String or Integer (required) — the entity_id or entity_reference of the starting entity

Builds the complete responsibility chain for an entity, showing every hierarchy level from broadest (Level 1) to most specific (Level 4). Unlike the walk-up resolution in Section 2.7, this procedure does not short-circuit — it queries every applicable level and returns the full picture. This is a read-only procedure that does not mutate data.

Step 1. Resolve Parent Entities ​

  • Source: Financial data model tables (department, party, deal, billing_item, revenue_items).
  • Action: SELECT from the appropriate parent tables based on the starting entity type.
  • Logic:
    • Based on entity_type_cd, query the parent entity chain to resolve all applicable hierarchy levels:
      • DEPARTMENT: query department for department_id, department_name.
      • CLIENT: query party for client name, then billing_item and department for department.
      • BUYER: query party for buyer name, then billing_item and department for department.
      • DEAL: query deal for deal reference and name, then billing_item, party, and department for client, buyer, and department.
      • SALES_ITEM: query revenue_items for sales item reference, then deal, party, and department for deal, client, buyer, and department.
      • PAYMENT_TERM: query billing_item for payment term reference, then deal, party, and department for deal, client, buyer, and department.

Step 2. Execute Per-Level Responsibility Lookups ​

  • Source: assignment table, queried once per resolved hierarchy level.
  • Action: For each resolved parent entity, SELECT from assignment using the same query pattern as Section 2.7, Step 1.
  • Logic:
    • For each resolved parent entity, execute a Find Active Responsibility lookup (query assignment WHERE entity type and key match, assignment_type_cd = 'RESPONSIBILITY', is_active_ind = true).
    • Build an ordered array of chain levels, each containing: level (1–4), entity_type_cd, entity key, entity_label (human-readable display name), assignment (the active responsibility at this level or null if unassigned), is_selected_level (whether this level corresponds to the entity originally queried).
    • The "effective" responsible person is the most specific level (highest level number) that has an active assignment.

Side-effects:

  • None. This is a read-only procedure.

Postconditions:

  • Returns an ordered array of chain levels from Level 1 (broadest) to Level 4 (most specific), each with the active responsibility at that level (or null if unassigned).
  • The "effective" responsible person is identified as the assignment at the most specific populated level.

NOTE

PoC Artifact: Parent entity resolution in the PoC uses direct database queries within a server action. In production, this resolution should be encapsulated in a dedicated service or repository to maintain the layered architecture.

3. Business Rules & Logic ​

3.1 Assignment Type Discrimination ​

Business rule: The assignment_type_cd field on assignment acts as a discriminator governing which fields are relevant and which business rules apply. A single table stores both tasks and responsibilities; the type code determines which subset of columns is active.

Data-level enforcement:

  • Read: assignment.assignment_type_cd — value must be 'TASK' or 'RESPONSIBILITY'.
  • Guard: For TASK type: reject if task_title is absent or empty; reject status transitions on responsibility records. For RESPONSIBILITY type: reject if task_status_cd is non-null on creation; reject task-specific edits.
  • Write: See the following discriminator behavior table enforced in the service layer:
AspectTASKRESPONSIBILITY
task_status_cdRequired; initialized to 'OPEN'Must be NULL
task_titleRequired; minimum length 1Must be NULL
start_dt, end_dtOptional date fieldsNot used
is_active_indAlways true (lifecycle via status)true for current, false for superseded
Uniqueness per entityMultiple allowedExactly one active
Hierarchy participationTask-level types do not participateLevels 1–4 participate in walk-up resolution

3.2 Task Status Transition Rules ​

Business rule: Task status transitions are forward-only. Once a task reaches a terminal state (COMPLETE or CANCELLED), no further transitions are permitted.

Data-level enforcement:

  • Read: assignment.task_status_cd — current status before any transition.
  • Guard: If the requested transition is not in the matrix below, reject the operation with an error.
  • Write: Set assignment.task_status_cd to the new status on successful validation.
Current StatusPermitted Next Statuses
OPENWORKING, CANCELLED
WORKINGWAITING, COMPLETE, CANCELLED
WAITINGWORKING, CANCELLED
COMPLETE(none — terminal)
CANCELLED(none — terminal)

3.3 Responsibility Uniqueness Invariant ​

Business rule: Exactly one active RESPONSIBILITY assignment may exist per (entity_type_cd, entity key) combination at any time. If an active responsibility already exists, the operation to create a new one is rejected; users must transfer instead.

Data-level enforcement:

  • Read: assignment WHERE entity_type_cd = input type, entity key matches, assignment_type_cd = 'RESPONSIBILITY', is_active_ind = true.
  • Guard: If any matching record exists, reject the Create Responsibility operation with a message directing the caller to use Transfer Responsibility.
  • Write: Proceed with INSERT only when no active responsibility is found.

IMPORTANT

This constraint is enforced at the application layer, not as a database unique index, because the composite key varies by entity type: integer-keyed entities use entity_type_cd + entity_id; string-referenced entities use entity_type_cd + entity_reference; meta-data pair entities use entity_type_cd + meta_data_type_cd + meta_data_value.

3.4 Transfer Mechanics: Deactivate-Then-Create ​

Business rule: Responsibility transfers never update the existing record in place. The old record is deactivated and a new record is created. This design ensures that every past holder has their own assignment row with its own history entries, and the complete chain of ownership is always recoverable.

Data-level enforcement:

  • Read: Current assignment record to capture entity-identifying fields and previous_user_id.
  • Guard: Reject if the assignment is not of type RESPONSIBILITY, is already inactive, or if the new assignee equals the current assignee.
  • Write: UPDATE existing record is_active_ind = false; INSERT new record with is_active_ind = true and all entity fields copied verbatim from the old record.

3.5 Sibling Task Cancellation ​

Business rule: When a task is completed, the system supports cancelling all other active tasks for the same entity. This is an opt-in operation, not automatic. Only siblings whose current task_status_cd permits a transition to CANCELLED are affected.

Data-level enforcement:

  • Read: assignment WHERE same entity_type_cd, same entity key, assignment_type_cd = 'TASK', assignment_id != the completed task.
  • Guard: Per sibling, check the status transition matrix. Skip siblings already in COMPLETE or CANCELLED.
  • Write: UPDATE eligible siblings to task_status_cd = 'CANCELLED'; INSERT assignment_history per sibling.

3.6 History Immutability ​

Business rule: The assignment_history table is append-only. Records are never updated or deleted through normal operations. Every mutation described in this document produces at least one history entry. The complete and tamper-resistant audit trail is a design guarantee.

Data-level enforcement:

  • Read: Not applicable.
  • Guard: No UPDATE or DELETE operations are permitted on assignment_history in standard workflows. The cascade-delete foreign key on assignment_history.assignment_id exists as a safety mechanism but is not exercised in standard workflows.
  • Write: INSERT only. Every procedure in Section 2 that mutates assignment also inserts a corresponding assignment_history record.

3.7 Entity Key Polymorphism ​

Business rule: The assignment table uses a polymorphic association pattern. The entity key is stored differently depending on entity_type_cd. Application-layer validation must ensure that entity keys reference valid records; these are logical associations, not database-enforced foreign keys.

Data-level enforcement:

  • Read: assignment.entity_type_cd determines which key field to use.
  • Guard: Reject if none of entity_id, entity_reference, or (meta_data_type_cd + meta_data_value) is populated for the given entity type.
  • Write: Populate only the appropriate key field(s) per entity type:
Entity TypeKey Field(s)
DEPARTMENTentity_id (integer, references department.department_id)
CLIENTentity_id (integer, references party.party_id)
BUYERentity_id (integer, references party.party_id)
DEALentity_reference (string, matches deal.deal_reference)
META_DATA_PAIRmeta_data_type_cd + meta_data_value
SALES_ITEMentity_reference (string, matches revenue_items.sales_item_ref)
PAYMENT_TERMentity_reference (string, matches billing_item.payment_term_ref)
CASH_RECEIPTentity_id (integer, references cash_receipt.cash_receipt_id)
CASH_RECEIPT_SPLITentity_id (integer, references cash_receipt_split.cash_receipt_split_id)
PAYMENTentity_id (integer, references payment_item.payment_item_id)

WARNING

These are logical associations, not database-enforced foreign keys. The polymorphic pattern trades referential integrity enforcement for flexibility. Application-layer validation must ensure that entity keys reference valid records.

3.8 Hierarchy Levels ​

Business rule: The four-level responsibility hierarchy determines assignment resolution order. Resolution walks from Level 4 (most specific) down to Level 1 (broadest). The first active responsibility found wins. Task-level entity types are excluded from hierarchy resolution entirely.

Data-level enforcement:

  • Read: assignment.entity_type_cd determines which hierarchy level a record belongs to.
  • Guard: Reject hierarchy resolution requests for task-level entity types (CASH_RECEIPT, CASH_RECEIPT_SPLIT, PAYMENT).
  • Write: Not applicable — hierarchy resolution is read-only.
LevelEntity TypesScope
Level 1 (broadest)DEPARTMENTOrganizational unit
Level 2CLIENT, BUYERParty relationship
Level 3DEAL, META_DATA_PAIREngagement or classification
Level 4 (most specific)SALES_ITEM, PAYMENT_TERMLine-item granularity
Task-level (no hierarchy)CASH_RECEIPT, CASH_RECEIPT_SPLIT, PAYMENTTransactional work items

4. Field Mapping & Transformation ​

4.1 Create Responsibility: Input to assignment Mapping ​

Source Table.FieldTarget Table.FieldTransform
Input entity_type_cdassignment.entity_type_cdCopied as-is; must be a responsibility-eligible type
Input entity_idassignment.entity_idCopied as-is; null for string-referenced and meta-data entities
Input entity_referenceassignment.entity_referenceCopied as-is; null for integer-keyed and meta-data entities
Input meta_data_type_cdassignment.meta_data_type_cdCopied as-is; null unless entity_type_cd = 'META_DATA_PAIR'
Input meta_data_valueassignment.meta_data_valueCopied as-is; null unless entity_type_cd = 'META_DATA_PAIR'
Input meta_data_date_valueassignment.meta_data_date_valueCopied as-is; optional
Input assigned_to_user_idassignment.assigned_to_user_idCopied as-is; FK to users.user_id
—assignment.assignment_type_cdSystem-generated; hardcoded to 'RESPONSIBILITY'
—assignment.is_active_indSystem-generated; hardcoded to true
—assignment.task_status_cdSystem-generated; hardcoded to NULL
—assignment.task_titleSystem-generated; hardcoded to NULL
—assignment.assignment_idSystem-generated (new UUID)

4.2 Create Task: Input to assignment Mapping ​

Source Table.FieldTarget Table.FieldTransform
Input entity_type_cdassignment.entity_type_cdCopied as-is; any of the ten supported types
Input entity_idassignment.entity_idCopied as-is; null for string-referenced and meta-data entities
Input entity_referenceassignment.entity_referenceCopied as-is; null for integer-keyed and meta-data entities
Input meta_data_type_cdassignment.meta_data_type_cdCopied as-is; null unless entity_type_cd = 'META_DATA_PAIR'
Input meta_data_valueassignment.meta_data_valueCopied as-is; null unless entity_type_cd = 'META_DATA_PAIR'
Input meta_data_date_valueassignment.meta_data_date_valueCopied as-is
Input assigned_to_user_idassignment.assigned_to_user_idCopied as-is; FK to users.user_id
Input task_titleassignment.task_titleCopied as-is; required, minimum length 1
Input start_dtassignment.start_dtCopied as-is; optional
Input end_dtassignment.end_dtCopied as-is; optional
—assignment.assignment_type_cdSystem-generated; hardcoded to 'TASK'
—assignment.task_status_cdSystem-generated; hardcoded to 'OPEN'
—assignment.is_active_indSystem-generated; hardcoded to true
—assignment.assignment_idSystem-generated (new UUID)

4.3 Transfer Responsibility: Old-to-New Assignment Mapping ​

When creating the replacement assignment during a transfer, entity-identifying fields are copied verbatim from the deactivated record.

Source Table.FieldTarget Table.FieldTransform
assignment.entity_type_cd (old record)assignment.entity_type_cdCopied verbatim
assignment.entity_id (old record)assignment.entity_idCopied verbatim
assignment.entity_reference (old record)assignment.entity_referenceCopied verbatim
assignment.meta_data_type_cd (old record)assignment.meta_data_type_cdCopied verbatim
assignment.meta_data_value (old record)assignment.meta_data_valueCopied verbatim
assignment.meta_data_date_value (old record)assignment.meta_data_date_valueCopied verbatim
Input new_user_idassignment.assigned_to_user_idNew user ID from input
—assignment.is_active_indSystem-generated; hardcoded to true
—assignment.assignment_type_cdSystem-generated; hardcoded to 'RESPONSIBILITY'
—assignment.assignment_idSystem-generated (new UUID)

4.4 History Entry: Action-to-Field Mapping ​

Each action_cd value populates different fields on the assignment_history record.

action_cdfrom_user_idto_user_idfrom_status_cdto_status_cdcomment_text
ASSIGNEDNULLAssigneeNULLNULLNULL
REASSIGNEDPrevious ownerNew ownerNULLNULLOptional reason
STATUS_CHANGEDNULLNULLPrevious statusNew statusOptional reason
DEACTIVATEDPrevious ownerNULLNULLNULLOptional transfer reason
CANCELLEDNULLNULLPrevious status'CANCELLED'Cancellation reason or sibling reference

5. Cross-References ​

DocumentRelationship
Assignments Data ModelFull schema definitions, status lifecycles, validation rules, and code master values for the assignment and assignment_history tables. The data model document is the authoritative reference for table structure; this procedures document describes the operations that mutate those tables.
Assignments QueriesRead operations that support and complement the procedures documented here, including Find Active Responsibility (used as a building block in hierarchy resolution and uniqueness checking), Find Sibling Tasks (used in the cancellation procedure), and the gap-detection queries that identify entities needing assignment.
Cash Receipts Data ModelTask assignments can be created for CASH_RECEIPT (assignment.entity_id → cash_receipt.cash_receipt_id) and CASH_RECEIPT_SPLIT (assignment.entity_id → cash_receipt_split.cash_receipt_split_id) entities. Receipt ingestion and split creation are upstream events that may trigger task creation.
Settlements Data ModelPayment items created from settlements may have task assignments for follow-up processing. The PAYMENT entity type on assignments references payment_item records via assignment.entity_id → payment_item.payment_item_id.
Deals, Sales Items & Payment Terms Data ModelResponsibility assignments at Level 3 (DEAL) reference deals by assignment.entity_reference matching deal.deal_reference. Parent entity resolution for chain building queries the deal table. SALES_ITEM assignments use assignment.entity_reference matching revenue_items.sales_item_ref; PAYMENT_TERM assignments use assignment.entity_reference matching billing_item.payment_term_ref.
Parties Data ModelResponsibility assignments at Level 2 (CLIENT, BUYER) reference parties by assignment.entity_id matching party.party_id. The assignment.assigned_to_user_id field references the users table (internal staff), not the party table (external entities).
Billing Items Data ModelParent entity resolution for the responsibility chain queries billing_item to navigate from a client or deal up to a department. The PAYMENT_TERM entity type references billing_item.payment_term_ref.

Procedure Dependency Graph ​

The following shows how procedures depend on each other and on query operations:

createResponsibility (2.1)
  depends on: findActiveResponsibility query (uniqueness check)
  writes to: assignment (INSERT), assignment_history (INSERT)

transferResponsibility (2.2)
  depends on: getAssignmentById query (validation)
  writes to: assignment (UPDATE old), assignment (INSERT new),
             assignment_history (INSERT x2: DEACTIVATED + REASSIGNED)

createTask (2.3)
  writes to: assignment (INSERT), assignment_history (INSERT)

updateTaskStatus (2.4)
  depends on: getAssignmentById query (validation + current status)
  depends on: Status Transition Matrix validation (Section 3.2)
  writes to: assignment (UPDATE), assignment_history (INSERT)

updateTask (2.5)
  depends on: getAssignmentById query (capture old assignee)
  writes to: assignment (UPDATE), assignment_history (INSERT, conditional)

cancelSiblingTasks (2.6)
  depends on: getAssignmentById query (load reference task)
  depends on: findSiblingTasks query
  depends on: updateTaskStatus logic (per sibling, validates transitions)
  writes to: assignment (UPDATE per sibling), assignment_history (INSERT per sibling)

resolveResponsibleUser (2.7)
  depends on: findActiveResponsibility query (up to 7 lookups)
  writes to: nothing (read-only)

resolveResponsibilityChain (2.8)
  depends on: parent entity resolution (financial data model queries)
  depends on: findActiveResponsibility query (per level)
  writes to: nothing (read-only)

NOTE

PoC Artifact: The PoC does not implement automatic task creation from upstream events. All task creation is user-initiated through the assignments UI. In the production system, the following events should trigger automatic task assignment:

  • New cash receipt ingested — create a CASH_RECEIPT task for the responsible user (resolved via hierarchy from receipt references).
  • New cash receipt split created — create a CASH_RECEIPT_SPLIT task.
  • Payment hold or failure — create a PAYMENT task for follow-up.

The hierarchy resolution procedure (Section 2.7) provides the mechanism for determining who to assign these auto-created tasks to. The chain resolution (Section 2.8) provides the reference-to-hierarchy mapping needed to populate the hierarchy context from receipt reference data.

Confidential. For internal use only.

Copyright © 2025, United Talent Agency. All rights reserved.