orders — Phase 8

Schema locked 2026-06-10. 7 tables, 133 cols: order_header (37), order_line (26), order_payment (20), order_fulfillment (19), order_fulfillment_line (13), order_template (9), order_template_line (9). (+1 search_vector on order_header added 2026-06-11 — Search FTS touch.)

Orders is the commercial promise layer — what the customer has agreed to buy before the physical handoff at POS. Design rules:

• Table is named order_header (not order) — disambiguates from the SQL reserved word and signals header/line pattern.
• Orders owns its own payment schedule (order_payment). A deposit or installment is a payment against a future sale; it is not a taxable event.
• Link-don't-convert: order_header.fulfilled_sale_id → pos.sale. The POS sale is the legal taxable event; the order is the pre-sale commitment. Orders do not convert to sales — they are linked to the sale created at fulfillment.
• Tax is estimated/display-only at the order stage. Legal tax lines finalize at POS via pos.sale_line_tax.
• Reserve-on-confirm, not on quote. Backordered lines hold stock_reservation_id = NULL.
• order_type is the intent discriminator (quote/order/special_order/preorder). Back-order status is captured at line level via order_line.fulfillment_status = 'backordered'.

Cross-Phase Foreign Keys — orders

FK column	Target	Status
*.tenant_id	platform.tenant	Phase 1 locked — enforced
order_header.site_id, order_fulfillment.site_id	multi_loc.site	Phase 7 locked — enforced
order_header.confirmed_by_user_id, order_header.cancelled_by_user_id, order_header.created_by_user_id, order_fulfillment.picked_by	identity.identity_user	Phase 7 locked — enforced
order_header.customer_id, order_template.customer_id	crm.customer	Phase 7 locked — enforced; NULLABLE (quotes may be anonymous)
order_fulfillment.ship_to_address_id	crm.address	Phase 7 locked — enforced; NULLABLE
order_line.variant_id, order_line.substituted_from_variant_id, order_fulfillment_line.variant_id, order_template_line.variant_id	inventory.item_variant	Phase 7 locked — enforced
order_line.stock_reservation_id	inventory.stock_reservation	Phase 7 locked — enforced; NULLABLE (backordered lines)
order_header.fulfilled_sale_id	pos.sale	Phase 8 (pos locked 2026-06-10) — enforced
order_payment.pos_sale_payment_id	pos.sale_payment	Phase 8 (pos locked 2026-06-10) — enforced; NULLABLE
order_header.draft_po_id	purchasing	FORWARD-REF / deferred — purchasing not yet built; plain UUID, no FK
order_payment.charge_account_ref	billing A/R	FORWARD-REF / text seam — billing not yet built; stored as text; FK added when billing locks
order_fulfillment.tracking_number, order_fulfillment.carrier	Module 14 (delivery/routing)	FORWARD-REF / deferred — module 14 deferred indefinitely; columns are plain text; FK/enum added when module 14 locks

---

orders.order_header — order header

The root record for every order. Created when an order, quote, special order, or preorder is opened. Owns the full commercial lifecycle from draft through fulfillment and closure.

Tenant-scoped. Transactional — carries site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	FK → multi_loc.site — site where the order was taken
order_number	text	NOT NULL	—	Human-facing reference. One sequential series per tenant across all order_type values.
order_type	text	NOT NULL	'order'	CHECK IN ('quote','order','special_order','preorder') — intent discriminator; back-order status lives at line level
customer_id	UUID	nullable	—	FK → crm.customer. NULLABLE for anonymous quotes. Service-enforced: required when order_type != 'quote'.
status	text	NOT NULL	'draft'	CHECK IN ('draft','quote_sent','confirmed','fulfilling','partially_fulfilled','fulfilled','closed','cancelled','expired')
priority	text	NOT NULL	'standard'	CHECK IN ('standard','rush','vip')
po_number	text	nullable	—	Customer's purchase order number (B2B); mirrors pos.sale
job_reference	text	nullable	—	Contractor job/project reference; mirrors pos.sale
subtotal_cents	bigint	NOT NULL	0	Service-maintained cache: SUM(order_line.line_total_cents)
discount_total_cents	bigint	NOT NULL	0	Service-maintained cache: SUM(order_line.discount_amount_cents)
estimated_tax_cents	bigint	NOT NULL	0	DISPLAY ONLY — estimated tax for quote/order display. Legal tax finalizes at POS via pos.sale_line_tax.
estimated_total_cents	bigint	NOT NULL	0	Service-maintained cache (display): subtotal_cents − discount_total_cents + estimated_tax_cents
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
deposit_required_cents	bigint	nullable	—	NULL = no deposit policy for this order. When set, OrderService enforces a deposit order_payment before confirming.
balance_due_cents	bigint	NOT NULL	0	Service-maintained cache: estimated_total_cents − SUM(order_payment.amount_paid_cents)
quote_expires_at	timestamptz	nullable	—	Auto-expire to status = 'expired' when reached. Service-enforced: required when order_type = 'quote'; default now() + 30 days.
accepted_at	timestamptz	nullable	—	Timestamp of quote acceptance
accepted_by	text	nullable	—	Acceptance evidence (e.g. email, initials, signature reference)
requested_date	date	nullable	—	Customer's requested fulfillment date
expected_date	date	nullable	—	Estimated fulfillment / arrival date (special-order ETA)
draft_po_id	UUID	nullable	—	FORWARD-REF — purchasing not yet built; plain UUID, no FK. Links to the purchase order raised to fulfil this order.
fulfilled_sale_id	UUID	nullable	—	FK → pos.sale. Link-don't-convert: the POS sale created at counter handoff. Same-phase enforced (pos locked 2026-06-10).
note	text	nullable	—	Order-level free-text note
attributes	JSONB	nullable	'{}'	No prescribed shape — vertical/custom extension point; consumer owns key hygiene. Example: {"vertical_key":"nursery","custom":{...}}
confirmed_at	timestamptz	nullable	—	Service-enforced: non-null when status = 'confirmed'
confirmed_by_user_id	UUID	nullable	—	FK → identity.identity_user. Service-enforced: non-null when status = 'confirmed'.
cancelled_at	timestamptz	nullable	—	Service-enforced: non-null when status = 'cancelled'
cancelled_by_user_id	UUID	nullable	—	FK → identity.identity_user. Service-enforced: non-null when status = 'cancelled'.
cancellation_reason	text	nullable	—	Service-enforced: non-null when status = 'cancelled'
closed_at	timestamptz	nullable	—	Timestamp of order closure (post-fulfillment admin close)
created_by_user_id	UUID	nullable	—	FK → identity.identity_user — staff member who created/took the order
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
search_vector	tsvector	NOT NULL	—	GENERATED ALWAYS AS (to_tsvector('english', coalesce(order_number,'') || ' ' || coalesce(po_number,'') || ' ' || coalesce(job_reference,''))) STORED. (FTS touch 2026-06-11 — Search module.) Note: no customer name snapshot on orders.order_header — customer name search routes through CRM. Maintained by Postgres; never written directly.

37 columns. (FTS touch 2026-06-11: +1 search_vector. Was 36 cols.)

Indexes:

• PK on id
• on (tenant_id)
• on (tenant_id, status, requested_date) — order dashboard: status + date filter
• on (tenant_id, customer_id) WHERE deleted_at IS NULL — customer's open orders
• on (tenant_id, quote_expires_at) WHERE order_type = 'quote' AND status = 'quote_sent' — quote expiry sweep
• UNIQUE on (tenant_id, order_number) WHERE deleted_at IS NULL
• GIN on (search_vector) — full-text search (tsvector)
• GIN on (order_number gin_trgm_ops) — fuzzy / partial / prefix order number search (pg_trgm)

---

orders.order_line — order line items

One row per item on an order. Tracks quantities across the fulfillment lifecycle (reserved, fulfilled, backordered, cancelled) and captures estimated line-level tax for display.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
order_id	UUID	NOT NULL	—	FK → orders.order_header
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
line_type	text	NOT NULL	'standard'	CHECK IN ('standard','comp','substitution'). line_type != 'standard' requires note (service-enforced).
quantity	numeric	NOT NULL	—	CHECK (quantity > 0). Supports fractional quantities (weight/volume-priced items).
reserved_qty	numeric	NOT NULL	0	CHECK (reserved_qty >= 0). Quantity committed via inventory.stock_reservation.
fulfilled_qty	numeric	NOT NULL	0	CHECK (fulfilled_qty >= 0). Quantity physically handed over.
backordered_qty	numeric	NOT NULL	0	CHECK (backordered_qty >= 0). Quantity awaiting stock.
cancelled_qty	numeric	NOT NULL	0	CHECK (cancelled_qty >= 0). Quantity cancelled from this line.
fulfillment_status	text	NOT NULL	'pending'	CHECK IN ('pending','reserved','backordered','partially_fulfilled','fulfilled','cancelled'). Rollup invariant: reserved + fulfilled + backordered + cancelled ≤ quantity (service-enforced).
stock_reservation_id	UUID	nullable	—	FK → inventory.stock_reservation. NULL for backordered lines or non-stocked line types.
unit_price_cents	bigint	NOT NULL	—	Price resolved by PricingService at order creation time
price_override	boolean	NOT NULL	false	True when staff manually overrode the price
price_override_reason	text	nullable	—	Required when price_override = true (service-enforced)
discount_amount_cents	bigint	NOT NULL	0	Line-level absolute discount amount
line_subtotal_cents	bigint	NOT NULL	—	Service-maintained cache: unit_price_cents × quantity
line_total_cents	bigint	NOT NULL	—	Service-maintained cache: line_subtotal_cents − discount_amount_cents
estimated_line_tax_cents	bigint	NOT NULL	0	DISPLAY ONLY — estimated per-line tax for quote display. Legal line-level tax at pos.sale_line_tax.
substitution_allowed	boolean	NOT NULL	true	Staff may fulfil with a substitute variant
substituted_from_variant_id	UUID	nullable	—	FK → inventory.item_variant. Set when this line is a substitution; records the originally-requested variant.
expected_arrival_at	timestamptz	nullable	—	ETA for backordered / special-order lines
note	text	nullable	—	Line-level note. Required when line_type != 'standard' (service-enforced).
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

26 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (order_id) — all lines for an order
• on (order_id, fulfillment_status) — backordered/partial status per order (L-2)
• on (tenant_id, variant_id) — cross-order lines by variant

---

orders.order_payment — order payment schedule

One row per installment or payment stage on an order. Tracks the payment schedule (deposit, milestone, balance, final) and records actual payments. A deposit is a payment against the order balance — not a taxable event; legal tax finalizes at POS.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
order_id	UUID	NOT NULL	—	FK → orders.order_header
sequence	integer	NOT NULL	—	Installment order (1 = deposit)
payment_stage	text	NOT NULL	—	CHECK IN ('deposit','milestone','balance','final')
status	text	NOT NULL	'scheduled'	CHECK IN ('scheduled','due','paid','failed','refunded','cancelled')
amount_due_cents	bigint	NOT NULL	—	Planned amount for this installment. CHECK (amount_due_cents >= 0).
amount_paid_cents	bigint	NOT NULL	0	Actual amount collected. CHECK (amount_paid_cents >= 0).
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
due_at	timestamptz	nullable	—	Due date for this installment
paid_at	timestamptz	nullable	—	Timestamp when payment was received
payment_method	text	nullable	—	CHECK (NULL OR IN ('card','cash','check','ach','charge_account')). NULL until payment is processed. store_credit/gift_card excluded in v1.0 — final tender at POS via fulfilled_sale_id (see Design Notes F-1).
stripe_payment_intent_id	text	nullable	—	Set by PaymentsService for card payments
charge_account_ref	text	nullable	—	FORWARD-REF / text seam — billing not yet built; stores charge-account reference as text; FK added when billing locks
pos_sale_payment_id	UUID	nullable	—	FK → pos.sale_payment. Links this installment to the POS tender row when payment is collected at counter. Same-phase enforced (pos locked 2026-06-10).
refunded_amount_cents	bigint	NOT NULL	0	Amount refunded from this payment. CHECK (refunded_amount_cents >= 0). CHECK (refunded_amount_cents <= amount_paid_cents).
note	text	nullable	—	Payment-level note
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

20 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (order_id) — all payments for an order
• on (tenant_id, status, due_at) WHERE status IN ('scheduled','due') — overdue payment sweep

---

orders.order_fulfillment — fulfillment batch header

One row per fulfillment event for an order. A single order may have multiple fulfillment batches (e.g. partial ship + later pickup). Owns the fulfillment type and operational state. Full routing, carrier integration, and proof-of-delivery deferred to Module 14.

Tenant-scoped. Transactional — carries site_id.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
order_id	UUID	NOT NULL	—	FK → orders.order_header
site_id	UUID	NOT NULL	—	FK → multi_loc.site — fulfillment origin site
fulfillment_type	text	NOT NULL	—	CHECK IN ('pickup','delivery','ship','counter_handoff')
status	text	NOT NULL	'pending'	CHECK IN ('pending','picking','staged','ready','handed_over','cancelled')
staging_code	text	nullable	—	Physical staging location code (shelf / bay / rack)
picked_by	UUID	nullable	—	FK → identity.identity_user — staff who picked this batch
picked_at	timestamptz	nullable	—	Timestamp picking was completed
staged_at	timestamptz	nullable	—	Timestamp batch was staged
ready_at	timestamptz	nullable	—	Timestamp batch was marked ready for customer
handed_over_at	timestamptz	nullable	—	Timestamp of physical handoff (to customer or carrier)
ship_to_address_id	UUID	nullable	—	FK → crm.address. Required when fulfillment_type = 'ship' (service-enforced).
tracking_number	text	nullable	—	DEFERRED (Module 14) — shipping tracking reference; no FK until delivery module locks
carrier	text	nullable	—	DEFERRED (Module 14) — carrier name/code; no FK or enum until delivery module locks
note	text	nullable	—	Fulfillment-level note
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

19 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (order_id) — all fulfillments for an order
• on (status) — all picking/staged/ready fulfillments across sites
• on (site_id, status) — staged/ready batches at a site (fulfillment dashboard, L-1)

---

orders.order_fulfillment_line — fulfillment line items

One row per order line per fulfillment batch. Tracks the quantity split across batches and the picking outcome for each line.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
order_fulfillment_id	UUID	NOT NULL	—	FK → orders.order_fulfillment
order_line_id	UUID	NOT NULL	—	FK → orders.order_line
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant — denormalised for picking; matches order_line.variant_id unless a substitution occurred
quantity_requested	numeric	NOT NULL	—	Qty assigned to this batch. CHECK (quantity_requested > 0).
quantity_picked	numeric	NOT NULL	0	Qty physically picked. CHECK (quantity_picked >= 0). CHECK (quantity_picked <= quantity_requested).
quantity_fulfilled	numeric	NOT NULL	0	Qty confirmed handed over. CHECK (quantity_fulfilled >= 0). CHECK (quantity_fulfilled <= quantity_requested).
status	text	NOT NULL	'pending'	CHECK IN ('pending','picked','fulfilled','short','cancelled'). 'short' = could not fulfil full requested qty.
note	text	nullable	—	Line-level picking note
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

13 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (order_fulfillment_id) — all lines for a fulfillment batch
• on (order_line_id) — fulfillment lines for a given order line

---

orders.order_template — order template header

A named order template for recurring orders (e.g. "Smith Property Weekly Maintenance"). Applied via OrderService.applyTemplate() to create a new order_header pre-populated with lines. Prices resolved fresh at apply time.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
customer_id	UUID	nullable	—	FK → crm.customer. NULL = reusable across any customer.
name	text	NOT NULL	—	Template name; unique per tenant (soft-delete safe)
is_active	boolean	NOT NULL	true	Inactive templates are hidden from the UI but not deleted
note	text	nullable	—	Template-level note (carried to order on apply)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

9 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (tenant_id, customer_id) WHERE is_active = true — active templates by customer
• UNIQUE on (tenant_id, name) WHERE deleted_at IS NULL

---

orders.order_template_line — order template line items

One row per item in an order template. Quantities are defaults adjustable at apply time. Prices not stored — resolved fresh by PricingService when the template is applied.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
order_template_id	UUID	NOT NULL	—	FK → orders.order_template
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
quantity	numeric	NOT NULL	—	Default quantity; adjustable when template is applied
note	text	nullable	—	Line note carried to order line on apply
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

9 columns.

Indexes:

• PK on id
• on (tenant_id)
• on (order_template_id) — all lines for a template

---

Migration order within orders: orders.order_template → orders.order_header → orders.order_line → orders.order_payment → orders.order_fulfillment → orders.order_fulfillment_line → orders.order_template_line → (add intra-orders FK constraints for resolved forward-refs)

Design notes — orders

• Commercial promise vs. operational handoff: orders owns the commercial commitment (what was agreed, at what price, to whom). order_fulfillment / order_fulfillment_line own the operational handoff. Full routing and carrier integration deferred to Module 14.
• Tax model: estimated_tax_cents (header) and estimated_line_tax_cents (line) are DISPLAY ONLY — used for quote display and order summary. The legal taxable transaction and jurisdiction-level tax lines are finalized by POS at fulfillment via pos.sale + pos.sale_line_tax. Basis: CDTFA present-vs-future-sale intent — a deposit is a payment against a future sale, not a taxable event.
• Reserve-on-confirm: InventoryService writes inventory.stock_reservation when an order is confirmed (not on draft/quote). Backordered lines hold stock_reservation_id = NULL; backordered quantity tracks in order_line.backordered_qty.
• Link-don't-convert: order_header.fulfilled_sale_id → pos.sale. A POS sale is created at counter handoff; the order is linked to it. Orders do not "convert" to sales.
• Service-layer invariants (documented; DB CHECKs not used — rules involve cross-column or cross-table conditions):
  • customer_id required when order_type != 'quote'.
  • confirmed_at + confirmed_by_user_id non-null when status = 'confirmed'.
  • cancelled_at + cancelled_by_user_id + cancellation_reason non-null when status = 'cancelled'.
  • quote_expires_at required when order_type = 'quote'; default now() + 30 days; auto-expire to status = 'expired' when reached.
  • line_type != 'standard' requires note.
  • ship_to_address_id required when order_fulfillment.fulfillment_type = 'ship'.
• Deferred GAP (F-1): store_credit and gift_card are intentionally excluded from order_payment.payment_method in v1.0. v1.0 assumes final tender (including any stored-value redemption) occurs at POS via fulfilled_sale_id. Both live in the pos schema; their reachability outside a POS transaction is a post-Orders design decision. Trigger: when an online-only (non-POS) order tender flow is defined.

---