CODE_MASTER Design & Convention

Overview

CODE_MASTER is the centralized reference-data management system for Client Processing. It stores all classification codes, status values, and enumeration codes used throughout the system. Instead of hard-coding business rules, CODE_MASTER enables flexible, audit-ready code management with optional attribute-driven behavior.

Architecture

Data Residency

• Location: Database resident (PostgreSQL code_master, code_detail, code_attribute tables)
• Rationale: Enables real-time changes, audit trail, and queries across all code domains without service restart
• Not a server-side cache: Codes are queried from the database; caching is handled at application layer if needed

Design Pattern

CODE_MASTER is attribute-driven, supporting both:

1. Simple codes: Static classification (e.g., document types: INVOICE, CREDIT_MEMO, STATEMENT)
2. Attribute-based codes: Codes with business-logic attributes (e.g., WORKSHEET_STATUS.APPROVED with attribute FINAL=true to trigger cash application)

Core Tables

1. code_master (Header)

Stores the master code definitions.

Field	Type	Notes
code_master_type	varchar(50)	Code domain (e.g., INVOICE_STATUS_CD, DOCUMENT_TYPE_CD) — part of PK
code_master_cd	varchar(50)	Code value (e.g., DRAFT, ISSUED, PAID) — part of PK
code_master_desc	varchar(255)	Human-readable description (e.g., "Invoice sent to recipient")
created_by, created_dt	varchar(255), timestamp	Audit trail
updated_by, updated_dt	varchar(255), timestamp	Audit trail

Primary Key: (code_master_type, code_master_cd) — guarantees one definition per code

2. code_detail (Hierarchies & Relationships)

Tracks parent-child relationships, mappings, and equivalencies between codes across different domains.

Field	Type	Notes
code_master_type	varchar(50)	Parent code domain — part of PK
code_master_cd	varchar(50)	Parent code value — part of PK
detail_code_master_type	varchar(50)	Child code domain — part of PK
detail_code_master_cd	varchar(50)	Child code value — part of PK
relationship_type	varchar(50)	CHILD, MAPS_TO, EQUIVALENT
sequence_no	integer	Order for multi-child relationships
active_ind	boolean	Enable/disable relationship without deletion
created_by, created_dt	varchar(255), timestamp	Audit trail
updated_by, updated_dt	varchar(255), timestamp	Audit trail

Primary Key: (code_master_type, code_master_cd, detail_code_master_type, detail_code_master_cd) — guarantees one relationship per pair

Use Cases:

• Family → SubFamily hierarchy: (ISO20022_FAMILY, RCDT) → (ISO20022_SUBFAMILY, DMCT, CHILD)
• Standard mappings: (BAI_CODE, 165) → (ISO20022_SUBFAMILY, APAC, MAPS_TO)
• Code equivalencies: Mark codes as equivalent across systems for reporting

3. code_attribute (Attribute-Driven Business Logic)

Stores key-value pairs attached to codes, enabling attribute-driven behavior without modifying application code.

Field	Type	Notes
code_master_type	varchar(50)	Code domain (e.g., WORKSHEET_STATUS_CD) — part of PK
code	varchar(50)	Code value (e.g., APPROVED) — part of PK
attribute	varchar(100)	Attribute name (e.g., FINAL, ALLOW_EDIT, TRIGGER_GL_POST) — part of PK
value	varchar(255)	Attribute value (e.g., true, false, N, Y, 180)
description	varchar(255)	Explanation of the attribute (e.g., "When true, cash is considered applied")
created_by, created_dt	varchar(255), timestamp	Audit trail
updated_by, updated_dt	varchar(255), timestamp	Audit trail

Primary Key: (code_master_type, code, attribute) — guarantees one value per attribute

Example: Cash Receipt Worksheet status behavior

code_master_type: WORKSHEET_STATUS_CD
code: APPROVED
attribute: FINAL
value: "true"
description: "When true, cash in this worksheet is considered applied"

code_master_type: WORKSHEET_STATUS_CD
code: APPROVED
attribute: TRIGGER_GL_POST
value: "true"
description: "When true, GL entries are generated upon reaching this status"

CODE Master Types (Domains)

Status Codes

INVOICE_STATUS_CD — Invoice lifecycle

Code	Description	Attributes
DRAFT	Invoice created, not yet sent	—
ISSUED	Invoice sent to recipient	Marks when 14-day clock starts for WGA
IN_REVISION	Invoice being corrected before reissue	—
REISSUED	Invoice reissued from prior version	Linked via reissued_from_invoice_id
PAID	Payment received and applied	—
VOID	Invoice cancelled and replaced	Immutable; original record preserved
CANCELLED	Invoice retracted before issuance	—

CREDIT_MEMO_STATUS_CD — Credit memo (adjustment) lifecycle

Code	Description
DRAFT	Adjustment created, not yet issued
ISSUED	Adjustment issued; terminal state

CLIENT_LETTER_STATUS_CD — Client letter (summarized statement) lifecycle

Code	Description
DRAFT	Letter created, not yet issued
ISSUED	Letter issued to recipient
VOID	Letter retracted

BILLING_ITEM_STATUS_CD — Billing item processing state

Code	Description
U	Unprocessed; waiting for billed or cancelled
B	Billed; invoiced to client
X	Cancelled; no longer valid
C	Collected; payment applied

CASH_RECEIPT_WORKSHEET_STATUS_CD — Worksheet classification

Code	Description	Attributes
DRAFT	Worksheet open for edits	—
APPLIED	Cash identified and applied to invoices	—
SETTLED	Cash settled and final	—
APPROVED	Approved for GL posting and payment	FINAL=true, TRIGGER_GL_POST=true
RETURNED	Rejected; cash returned to receipt	—

PARTICIPANT_SETTLEMENT_STATUS_CD — Participant settlement state (6-letter codes)

Code	Description
D	Draft; settlement under preparation
T	Settled; final calculation ready
A	Approved; authorized for payment
R	Returned; rejected and reopened
P	Paid; payment executed

SETTLEMENT_PACKET_STATUS_CD — Batch settlement processing

Multiple codes tracking the settlement pipeline (values to be documented per settlement service)

POSTING_STATUS_CD — GL posting state (universal across transactions)

Code	Description
U	Unposted; waiting for GL batch
P	Posted; GL entry created
X or V	Voided; GL entry reversed (immutable)
F	Failed; posting error

REVENUE_ITEM_STATUS_CD — Revenue line-item classification

Code	Description
U	Unearned; waiting for revenue trigger
C	Collected; revenue recognized
M	Modified; updated after recognition

REVENUE_ITEM_DATE_STATUS_CD — Revenue date validation

Code	Description
U	Unvalidated; date pending milestone confirmation
C	Confirmed; revenue date locked

Document & Scope Codes

DOCUMENT_TYPE_CD — Document classification

Code	Description	Notes
CI	Client Invoice	Standard invoice to client
BI	Booking Internal	Internal booking statement
DM	Deal Memo	Contract summary
CM	Credit Memo	Adjustment/correction document
FC	Financial Certificate	Accounting certification
CL	Client Letter	Summarized client statement (new)

SCOPE_CD — Client letter filtering scope

Code	Description
INVOICED_ONLY	Show only invoiced items
UNINVOICED_ONLY	Show only uninvoiced items (accruals, adjustments)
ALL	Show all items

SUMMARIZE_BY_CD — Client letter summarization method

Code	Description
NONE	Show all line items; no rollup
DEAL	Summarize by deal, with detail breakdown
SALES_ITEM	Summarize by sales item within deal

Adjustment & Reason Codes

ADJUSTMENT_TYPE_CD — Credit memo adjustment type

Code	Description	Sign Convention
FULL_CREDIT	Cancel/reverse invoice entirely	Negative (reduces amount due)
DEBIT	Supplemental billing	Positive (increases amount due)
CREDIT	Partial credit/discount	Negative (reduces amount due)

CREDIT_MEMO_REASON_CD — Why adjustment was issued

Code	Description
FULL_CANCELLATION	Original invoice is void; credit memo replaces it
PRICE_CORRECTION	Price error in original invoice
INCORRECT_VAT	Tax/withholding error
OTHER	Miscellaneous (see memo notes)

Task & Workflow Codes

TASK_STATUS_CD — Workflow task state

Code	Description
OPEN	Task created, not started
WORKING	Task in progress
WAITING	Task blocked awaiting external input
COMPLETE	Task finished
CANCELLED	Task abandoned

DOC_EXECUTION_STATUS_CD — Document generation state

Code	Description
PENDING	Queued for generation
PENDING_REVIEW	Generated; awaiting approval
APPROVED	Approved for delivery
REJECTED	Generation failed or denied
SUCCESS	Document delivered
PARTIAL	Some recipients failed
FAILURE	Generation error

GENERATION_STATUS_CD — Statement/report generation progress

Code	Description
PENDING	Generation queued
GENERATING	In progress
COMPLETED	Ready for download
FAILED	Generation error

Entity State Codes

ACCOUNT_STATUS_CD — General ledger account state

Code	Description
A	Active
I	Inactive

STATUS_CD (generic) — Default active/inactive indicator

Code	Description
ACTIVE	Entity is active
INACTIVE	Entity is inactive

Naming Convention

Code Master Type Naming

• Format: {DOMAIN}_{CONCEPT}_CD or {DOMAIN}_STATUS_CD
• Rationale: Clear, searchable, and explicitly marks that it's a CODE_MASTER type
• Examples:
  • INVOICE_STATUS_CD (status codes within invoicing domain)
  • DOCUMENT_TYPE_CD (document classification; cross-domain)
  • POSTING_STATUS_CD (GL posting state; universal)

Code Value Naming

• Short codes (preferred): 1–5 characters for database compactness and UI display
  • Examples: DRAFT, PAID, U, P, X
  • Use for high-volume fields (millions of rows) or frequent filtering
• Descriptive codes: Full words for clarity when code readability matters
  • Examples: FULL_CANCELLATION, PRICE_CORRECTION
  • Use for infrequently changed or audit-critical fields

Mixed Naming Across Domains

Some legacy domains use mixed approaches:

• Invoicing: Descriptive (DRAFT, ISSUED, PAID)
• GL: Short codes (U, P, X)
• Settlement: Short codes (D, T, A, R, P)

Future: Standardize on descriptive names for new code types; legacy codes remain as-is for backwards compatibility.

Audit & Change Tracking

All CODE_MASTER tables include audit columns:

• created_by, created_dt — record creator and timestamp
• updated_by, updated_dt — last modifier and timestamp

Change Control

• Adds: New codes are inserted with creator and timestamp; backward compatible
• Updates: Description or non-structural changes update updated_by and updated_dt
• Deletes: Not recommended — instead, use active_ind = false in code_detail to disable relationships without deleting
• Immutable codes: Code values themselves are immutable (PK); never rename or delete

Design Decisions Pending (CP-DM-001, CP-DM-002)

1. CODE_MASTER Location & Caching Strategy (CP-DM-001)

Status: DATABASE RESIDENT (confirmed in cp-poc)

Currently implemented as database tables. Options for future consideration:

• Caching Layer: Implement application-level caching (Redis, in-memory) for read-heavy workloads
• Change Notifications: Event-driven cache invalidation when codes are updated
• Read Replicas: Replicate CODE_MASTER to read-only instances for reporting

2. Attribute-Driven Business Logic (CP-DM-002)

Status: ARCHITECTURE DESIGNED; IMPLEMENTATION PENDING

The code_attribute table is built to support rules-driven behavior. Decision pending:

• Rules Engine: Centralize business logic in a rules engine that queries code_attribute at runtime
• Application Integration: Each service that depends on attributes reads them from code_attribute and acts accordingly
• Scope: Decide which behaviors should be attribute-driven (e.g., WORKSHEET_STATUS triggers) vs. hard-coded

3. Seeding & Naming Convention (CP-DM-003)

Status: PARTIAL — Some codes defined in schema; naming convention refinement pending

Current State:

• Code types are scattered across service initialization and seed data
• No unified seed file; each domain manages its own codes

Action Item:

• Consolidate all CODE_MASTER seed values into a single seed script
• Standardize naming convention (short vs. descriptive) per domain
• Document required codes for each service startup

Migration & Rollout

Adding New Codes

1. Insert row in code_master with new code_master_cd and description
2. If code has behavior attributes, insert corresponding rows in code_attribute
3. If code relates to other codes, insert rows in code_detail
4. No application restart required; services query the database

Adding New Code Attributes

1. Insert row in code_attribute with the attribute name and value
2. Update application code to check the attribute at runtime (if not yet implemented)
3. Audit trail preserved; attribute change history queryable

Removing or Deprecating Codes

• Unused codes: Safe to delete (full table delete with audit backup)
• In-use codes: Mark as inactive in code_detail relationships; keep the code_master row for historical traceability

References

• Data Model: Invoices & Statements, Cash Receipts, Billing Items, Worksheets
• Related Tasks: CP-DM-001 (location decision), CP-DM-002 (attribute-driven logic), CP-DM-003 (seeding & convention)
• Contact: Product / Finance Architecture for CODE_MASTER policy updates