Sales orders

Header + line items + timeline notes + documents. Two status fields: order_status (org.order_status ENUM) and fulfillment_status (org.fulfillment_status ENUM), both stored directly on the row.

ER diagram

org.sales_orders

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
customer_id	UUID FK	NOT NULL. → org.customers(id) ON DELETE RESTRICT.
so_number	VARCHAR(100)	Optional. Partial UNIQUE (org_id, so_number) WHERE so_number IS NOT NULL.
order_channel_id	UUID FK	NOT NULL. → org.order_channels(id) ON DELETE RESTRICT.
order_status	order_status	NOT NULL. ENUM. See values below.
fulfillment_status	fulfillment_status	NOT NULL. ENUM. See values below.
fulfillment_location_id	UUID FK	→ org.locations(id) ON DELETE SET NULL.
broker_id	UUID FK	→ org.brokers(id) ON DELETE SET NULL. Snapshot — defaulted from customer but overridable per SO.
broker_fee_percent	NUMERIC(10,6)	CHECK 0..100. Snapshot — defaulted from customer.
broker_fees	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0. User-entered; may differ from the calculated order_value × broker_fee_percent / 100.
fulfillment_costs	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0.
freight_costs	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0.
promo_costs	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0.
other_costs	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0.
mabd	DATE	NOT NULL. "Must Arrive By Date" — the customer-facing delivery commitment.
placed_at	DATE	NOT NULL. Default CURRENT_DATE. Matches Received status.
entered_at	DATE	Matches Entered status.
shipped_at	DATE	Matches Shipped status.
invoiced_at	DATE	Matches Invoiced status.
paid_at	DATE	Matches Paid status.
rejected_at	DATE	Matches Rejected status.
notes	TEXT
qbo_id	VARCHAR(100)	UNIQUE per (org_id, qbo_id). QBO sync identifier.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().
updated_at	TIMESTAMPTZ	NOT NULL. Default NOW(). Auto-updated by trigger.

org.sales_order_items

Column	Type	Notes
id	UUID PK
org_id	UUID FK
sales_order_id	UUID FK	NOT NULL. → org.sales_orders(id) ON DELETE CASCADE.
sku_id	UUID FK	NOT NULL. → org.skus(id) ON DELETE RESTRICT.
quantity	INTEGER	NOT NULL. CHECK ≥ 1. Default 1. SKU sales-unit integer.
unit_price	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0.

UNIQUE (sales_order_id, sku_id) — one line per SKU per SO.

org.sales_order_timeline_notes

Append-only audit-style notes pinned to a status.

Column	Type	Notes
id	UUID PK
org_id	UUID FK
sales_order_id	UUID FK	NOT NULL. → org.sales_orders(id) ON DELETE CASCADE.
order_status	order_status	NOT NULL. ENUM. The status the note was pinned to.
note	TEXT	NOT NULL.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().

Documents

Files attached to a sales order are stored in the polymorphic org.documents table with entity_type = 'sales_order' and entity_id = sales_orders.id. Same shape used by purchase orders and work orders.

Status values

Two independent ENUMs.

• org.order_status: Received → Entered → Shipped → Invoiced → Paid, with branches Rejected, In-Dispute, Completed.
• org.fulfillment_status: Pending → Partial → Fulfilled.

order_status tracks the commercial lifecycle; fulfillment_status tracks the warehouse lifecycle.

Cross-references

• Driver of the finished goods inventory ledger: SO line items create DEMAND / ALLOCATE / CONSUME events as the SO moves through fulfillment.

Notes

• No is_active — terminal states live on order_status (Rejected, Completed).
• Aggregate order value is computed on read — no order_value column. SUM(sales_order_items.quantity * sales_order_items.unit_price) per API response.
• Inventory cost (COGS) is computed on read from the matching FG ledger CONSUME events — no inventory_cost column on the header.
• Lifecycle date rules:
  • Milestones (Received, Entered, Shipped, Invoiced, Paid) — each has a date column.
  • Single-event branches (Rejected) — rejected_at.
  • Transient / multi-occurrence states (In-Dispute) — no date column. The SO can enter and exit dispute multiple times; history is captured in sales_order_timeline_notes.
  • Terminal archived state (Completed) — no date column. Exact moment isn't load-bearing; check timeline notes if needed.