Purchase orders

Header + line items + receipts. The header carries target_type choosing whether the line items reference materials or SKUs. Status is an org.po_status ENUM stored directly on the row.

ER diagram

org.purchase_orders

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
po_number	VARCHAR(100)	NOT NULL. UNIQUE per (org_id, po_number).
vendor_id	UUID FK	NOT NULL. → org.vendors(id) ON DELETE RESTRICT.
target_type	po_target_type	NOT NULL. ENUM (materials, finished_goods). Default materials.
status	po_status	NOT NULL. ENUM. See values below.
ship_to_location_id	UUID FK	→ org.locations(id) ON DELETE SET NULL.
placed_at	DATE	NOT NULL. Default CURRENT_DATE. Matches Planning (initial).
ship_date	DATE	When the vendor ships from their end.
expected_delivery_date	DATE
received_at	DATE	Matches Received status.
paid_at	DATE	Matches Paid status.
cancelled_at	DATE	Matches Cancelled status.
shipping_costs	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0.
setup_costs	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0.
other_costs	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0. Catch-all for customs, duties, misc.
notes	TEXT
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().
updated_at	TIMESTAMPTZ	NOT NULL. Default NOW(). Auto-updated by trigger.

org.po_items

Column	Type	Notes
id	UUID PK
org_id	UUID FK
purchase_order_id	UUID FK	NOT NULL. → org.purchase_orders(id) ON DELETE CASCADE.
material_id	UUID FK	→ org.materials(id) ON DELETE RESTRICT. XOR with sku_id.
sku_id	UUID FK	→ org.skus(id) ON DELETE RESTRICT. XOR with material_id.
quantity	NUMERIC(18,6)	NOT NULL. CHECK > 0. Decimals allowed (matches bom_items).
unit_cost	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0.

Constraints:

• XOR CHECK: ((material_id IS NOT NULL AND sku_id IS NULL) OR (material_id IS NULL AND sku_id IS NOT NULL)).
• The chosen input kind must match the parent PO's target_type — application-enforced.

org.po_receipts

Column	Type	Notes
id	UUID PK
org_id	UUID FK
purchase_order_id	UUID FK	NOT NULL. → org.purchase_orders(id) ON DELETE CASCADE.
receipt_date	DATE	NOT NULL. Default CURRENT_DATE.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().
updated_at	TIMESTAMPTZ	NOT NULL. Default NOW(). Auto-updated by trigger.

org.po_receipt_items

Column	Type	Notes
id	UUID PK
org_id	UUID FK
receipt_id	UUID FK	NOT NULL. → org.po_receipts(id) ON DELETE CASCADE.
material_id	UUID FK	→ org.materials(id) ON DELETE RESTRICT. XOR with sku_id.
sku_id	UUID FK	→ org.skus(id) ON DELETE RESTRICT. XOR with material_id.
quantity	NUMERIC(18,6)	NOT NULL. CHECK > 0. Decimals allowed.
unit_cost	NUMERIC(18,6)	NOT NULL. CHECK ≥ 0. Default 0. Cost charged at receipt — may differ from the ordered po_items.unit_cost (price changes, fuel surcharge, etc.). This is what flows to the inventory ledger.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().

XOR CHECK: same as po_items. Application-enforced match with the parent PO's target_type.

Status values

org.po_status ENUM: Planning → Placed → In Transit → Partial → Received → Paid, plus terminal Cancelled.

Documents

Files attached to a purchase order live in the polymorphic org.documents table with entity_type = 'purchase_order'. Useful for vendor-issued PO PDFs, invoices, packing slips, shipping documents.

Cross-references

• Receipts drive entries on the materials inventory ledger (when target_type = 'materials') or the finished goods inventory ledger (when target_type = 'finished_goods').
• L3 workflow logic reads status directly — the enum value is the stable contract.

Notes

• No is_active — cancellation is status = Cancelled.
• Aggregate cost is computed on read: subtotal = SUM(po_items.quantity * po_items.unit_cost); total = subtotal + shipping_costs + setup_costs + other_costs.
• Header overhead is NOT capitalized into inventory. shipping_costs, setup_costs, other_costs stay at the PO header. Ledger entries get the vendor unit cost from po_receipt_items.unit_cost as-is.
• Lifecycle dates match statuses: placed_at (Planning, the initial state), received_at (Received), paid_at (Paid), cancelled_at (Cancelled). ship_date and expected_delivery_date are vendor-side dates, not lifecycle dates.