Work orders

Header + output line items + recipe snapshot + receipts. WOs convert materials into finished goods at a work site (a org.locations row). The recipe (which inputs are needed for each output line) is snapshotted from bom_items at the time the WO line is created — see work_order_inputs below. Status is an org.wo_status ENUM stored directly on the row.

ER diagram

org.work_orders

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
wo_number	VARCHAR(100)	NOT NULL. UNIQUE per (org_id, wo_number).
status	wo_status	NOT NULL. ENUM. See values below.
work_site_id	UUID FK	NOT NULL. → org.locations(id) ON DELETE RESTRICT.
placed_at	DATE	NOT NULL when status ≥ Placed. Matches the Placed transition.
expected_delivery_date	DATE	Target completion date. Not a lifecycle date.
production_started_at	DATE	Matches Production status.
received_at	DATE	Matches Received status.
cancelled_at	DATE	Matches Cancelled status.
notes	TEXT
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().
updated_at	TIMESTAMPTZ	NOT NULL. Default NOW(). Auto-updated by trigger.

org.work_order_items

The SKUs the WO is producing.

Column	Type	Notes
id	UUID PK
org_id	UUID FK
work_order_id	UUID FK	NOT NULL. → org.work_orders(id) ON DELETE CASCADE.
sku_id	UUID FK	NOT NULL. → org.skus(id) ON DELETE RESTRICT.
quantity	INTEGER	NOT NULL. CHECK ≥ 1. SKU output is in sales-unit integers.
conversion_cost	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0. Planned labor / overhead per unit produced. User-entered at WO creation as a planning estimate.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().

Material cost per unit is not stored on this row — it's derived at consume time from work_order_inputs and current material costs. conversion_cost covers the labor/overhead estimate that doesn't have a derivable source.

org.work_order_inputs

Recipe snapshot. Copied from bom_items at the moment a work_order_items row is created, so future BOM edits don't change what's already in flight.

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
work_order_id	UUID FK	NOT NULL. → org.work_orders(id) ON DELETE CASCADE. Denormalized for query simplicity.
work_order_item_id	UUID FK	NOT NULL. → org.work_order_items(id) ON DELETE CASCADE. The output line this input feeds.
material_id	UUID FK	→ org.materials(id) ON DELETE RESTRICT. XOR with input_sku_id.
input_sku_id	UUID FK	→ org.skus(id) ON DELETE RESTRICT. XOR with material_id.
quantity	NUMERIC(18,6)	NOT NULL. CHECK > 0. Per unit of output — total consumption = work_order_items.quantity × work_order_inputs.quantity.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().

Constraints:

• XOR CHECK: ((material_id IS NOT NULL AND input_sku_id IS NULL) OR (material_id IS NULL AND input_sku_id IS NOT NULL)).
• No UI to edit the snapshot rows in v1. Per-WO recipe overrides are deferred — see the L1 Out of scope list.

org.work_order_receipts

Column	Type	Notes
id	UUID PK
org_id	UUID FK
work_order_id	UUID FK	NOT NULL. → org.work_orders(id) ON DELETE CASCADE.
receipt_date	DATE	NOT NULL. Default CURRENT_DATE.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().

org.work_order_receipt_items

Column	Type	Notes
id	UUID PK
org_id	UUID FK
receipt_id	UUID FK	NOT NULL. → org.work_order_receipts(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 output is integer.
unit_cost	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0. Actual unit cost at receipt (material + conversion) — flows to FG ledger.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().

Status values

org.wo_status ENUM: Draft → Entered → Placed → Production → In Transit → Partial → Received → Completed, plus terminal Cancelled.

Documents

Files attached to a work order live in the polymorphic org.documents table with entity_type = 'work_order'. Useful for work instructions, quality control sheets, supplier specs.

Cross-references

• Drives both inventory ledgers: WO completion writes a RECEIVE event to the finished goods ledger, and the corresponding CONSUME events on the materials ledger for the BOM-resolved inputs.
• L3 workflow logic reads status directly — the enum value is the stable contract.

Notes

• No is_active — cancellation is status = Cancelled.
• Lifecycle dates match statuses: placed_at (Placed), production_started_at (Production), received_at (Received), cancelled_at (Cancelled). Draft has no date — created_at answers that. Partial has no dedicated date — the first receipt's receipt_date is the de facto answer.
• Material cost is derived, not stored on work_order_items. Conversion cost is the only WO-line cost column.