Business states and integrity

Business States & Data Integrity

When to Use

Use when implementing state transitions, monetary/quantity calculations, inventory movements, document finalization, cancellations, or concurrent updates in CRM and ERP systems.

1. Orthogonal State Dimensions (Never Overload One status Column)

A SalesOrder has multiple independent lifecycle dimensions that progress in parallel:

• Order Lifecycle Status: draft $\rightarrow$ pending_approval $\rightarrow$ confirmed $\rightarrow$ completed / cancelled.
• Fulfillment Status: unfulfilled $\rightarrow$ partially_fulfilled $\rightarrow$ fulfilled $\rightarrow$ returned.
• Invoicing / Billing Status: uninvoiced $\rightarrow$ partially_invoiced $\rightarrow$ invoiced $\rightarrow$ credited.

Separating orthogonal statuses prevents impossible state combinations (e.g., an order that is partially_fulfilled and partially_invoiced at the same time).

2. Exact Money, Quantity, Unit-of-Measure & Currency Discipline

• Never Use IEEE-754 Float (number / FLOAT / DOUBLE) for Money or Inventory Quantities:
  • Store monetary amounts as exact integer minor units (amount_minor BIGINT, e.g., cents/fillér) or fixed-precision NUMERIC(19, 4), always paired with an explicit ISO 4217 currency code (EUR, HUF, USD).
  • Store quantities with explicit NUMERIC(18, 4) (or integer base units) paired with an explicit unit_of_measure (pcs, kg, m, hour).
• Explicit Rounding & Tax Rules:
  • Define line-level vs. document-level rounding (HALF_UP / HALF_EVEN) and net-to-gross vs. gross-to-net calculation rules consistently across the entire document chain so Quote Total == Order Total == Invoice Total without 1-cent rounding drift.
• Currency & UoM Conversion Snapshots:
  • When converting currencies or units of measure (e.g., 1 box = 24 pcs, EUR -> HUF), store the exact exchange_rate, rate_date, and conversion_factor applied on the transaction record.

3. Immutability of Finalized Documents & Corrective Entries

Once a business document (Confirmed Order, Posted Shipment, Issued Invoice, Inventory Ledger Entry) is finalized:

• Forbid Direct Destructive Edits or DELETEs: Do not allow overwriting quantities or deleting rows on finalized operational or financial documents.
• Use Explicit Reversal / Amendment Workflows:
  • Quote/Order changes after confirmation $\rightarrow$ create a new Quote Revision (v2) or a tracked Order Change / Amendment (re-triggering approval if thresholds are crossed).
  • Fulfillment/Return adjustments $\rightarrow$ record a compensating Return / RMA or Inventory Adjustment movement.
  • Billing corrections $\rightarrow$ issue a linked Credit Note / Storno document.

4. Concurrency Control on Shared Business Invariants

• Protect state transitions, sequence/document numbering (SO-2026-000142), and stock allocations with transactional row locks (SELECT ... FOR UPDATE) or optimistic version checks (WHERE id = $1 AND status = 'pending_approval' AND version = $2) so double-clicks or parallel workers never double-confirm an order or over-allocate stock.