purchasing — Phase 8

Schema locked 2026-06-10 — 16 tables, 341 columns. Doc-only touch 2026-06-11: purchase_receipt.shipment_photo_ref FORWARD-REF label added (now closed by files.file, locked 2026-06-11 — no col change). FTS touch 2026-06-11: search_vector added to vendor (+1 col, 340 → 341).

Groups: Vendor Master (vendor, vendor_contact, vendor_address, vendor_item) / Purchase Orders (purchase_order, purchase_order_line, purchase_order_template, purchase_order_template_line) / Receiving (purchase_receipt, purchase_receipt_line) / Invoice + 3-Way Match (vendor_invoice, vendor_invoice_line, vendor_invoice_match) / Credit + Return (vendor_credit, vendor_return, vendor_return_line).

Design rules:

• Vendor master lives here (not CRM). PurchasingService is the cross-module API.
• vendor_invoice.status has no 'paid' value — Billing owns payment. billing_ap_ref, payment_status_ref, paid_at are Billing write-back columns; PurchasingService writes no value to them.
• vendor_invoice_match is mutable — rows updated as match decisions are revised. Audit module owns immutable history.
• Reserve-on-receipt: purchase_receipt_line creation triggers InventoryService to record stock inbound movement.
• vendor.status vocabulary: active / inactive / on_hold. NOT suspended/blocked — those are tenant-account lifecycle states at the platform layer.

Cross-Phase FK seams:

Column	References	Status
orders.order_header.draft_po_id	purchasing.purchase_order	CLOSED in this phase — ALTER TABLE orders.order_header ADD CONSTRAINT fk_draft_po FOREIGN KEY (draft_po_id) REFERENCES purchasing.purchase_order(id) runs at end of purchasing migration
purchase_receipt_line.inventory_movement_id	inventory.stock_movement	DEFERRED — plain UUID; FK added when Inventory movement log is confirmed stable. Trigger: first receiving flow implementation.
vendor_return_line.inventory_movement_id	inventory.stock_movement	DEFERRED — plain UUID; FK added when Inventory movement log is confirmed stable. Trigger: first return flow implementation.
vendor_invoice.billing_ap_ref	Billing	NEVER ENFORCED — plain text reference to Billing's AP record. Billing service writes this column via write-back.

Migration order: vendor → vendor_contact, vendor_address → vendor_item → purchase_order_template → purchase_order_template_line → purchase_order → purchase_order_line → purchase_receipt → purchase_receipt_line → vendor_invoice → vendor_invoice_line → vendor_invoice_match → vendor_credit → vendor_return → vendor_return_line → ALTER TABLE orders.order_header ADD CONSTRAINT fk_draft_po …

---

purchasing.vendor — 31 cols

Vendor master record. One row per vendor. Tenant-scoped; site-agnostic (master data).

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
code	text	NOT NULL	—	Short vendor code; UNIQUE (tenant_id, code) WHERE deleted_at IS NULL
name	text	NOT NULL	—	Display name
legal_name	text	nullable	—	Legal entity name (for 1099, contracts)
vendor_type	text	NOT NULL	'supplier'	CHECK IN ('supplier','service_provider','both')
status	text	NOT NULL	'active'	CHECK IN ('active','inactive','on_hold')
email	text	nullable	—	Primary contact email
phone	text	nullable	—	Primary contact phone
fax	text	nullable	—	Fax number
website	text	nullable	—
tax_id	text	nullable	—	EIN or SSN (masked at app layer)
is_1099	boolean	NOT NULL	false	Whether vendor receives 1099
payment_terms	text	nullable	—	CHECK (NULL OR IN ('net_7','net_15','net_30','net_45','net_60','net_90','cod','prepaid'))
currency_code	char(3)	NOT NULL	'USD'	ISO 4217; FK → shared.currency
account_number	text	nullable	—	Our account number with this vendor
credit_limit_cents	bigint	nullable	—	Vendor-extended credit limit
minimum_order_cents	bigint	nullable	—	Minimum order value
discount_percent	numeric(5,2)	nullable	—	Standing trade discount
lead_time_days	int	nullable	—	Default lead time in days
preferred_shipping_method	text	nullable	—
default_site_id	UUID	nullable	—	FK → multi_loc.site; default receiving site
notes	text	nullable	—	Internal notes
blackout_config	JSONB	nullable	'{}'	Vendor blackout dates/days. Example: {"dates": [{"start": "2024-12-24", "end": "2024-12-26", "reason": "Holiday closure"}], "days_of_week": [0]}
attributes	JSONB	nullable	'{}'	Vertical/custom extension point. No prescribed shape.
created_by_user_id	UUID	nullable	—	FK → identity.identity_user
updated_by_user_id	UUID	nullable	—	FK → identity.identity_user
search_vector	tsvector	NOT NULL	—	GENERATED ALWAYS AS (to_tsvector('english', coalesce(name,'') || ' ' || coalesce(code,'') || ' ' || coalesce(legal_name,''))) STORED. (FTS touch 2026-06-11 — Search module.) Maintained by Postgres; never written directly.

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

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, code) WHERE deleted_at IS NULL
• on (tenant_id, status) — vendor list filtered by status
• on (tenant_id, vendor_type) — filter by type
• GIN on (search_vector) — full-text search (tsvector)
• GIN on (name gin_trgm_ops) — fuzzy / partial / prefix vendor name search (pg_trgm)

---

purchasing.vendor_contact — 14 cols

Contacts at a vendor. One vendor has many contacts.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
first_name	text	NOT NULL	—
last_name	text	nullable	—
title	text	nullable	—	Job title
email	text	nullable	—
email_normalized	text	nullable	—	Lowercased/trimmed; uniqueness on this field
phone	text	nullable	—
phone_extension	text	nullable	—
is_primary	boolean	NOT NULL	false	One primary contact per vendor (service-enforced)

Indexes:

• PK on id
• on (tenant_id)
• on (vendor_id) — all contacts for a vendor

---

purchasing.vendor_address — 16 cols

Physical addresses for a vendor. One vendor has many addresses (billing, shipping, remittance).

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
address_type	text	NOT NULL	—	CHECK IN ('billing','shipping','remittance','other')
is_primary	boolean	NOT NULL	false	Primary address of this type (service-enforced)
address_line1	text	NOT NULL	—
address_line2	text	nullable	—
city	text	NOT NULL	—
us_state_code	char(2)	nullable	—	FK → shared.us_state
postal_code	text	nullable	—
country_code	char(2)	NOT NULL	'US'	FK → shared.country
notes	text	nullable	—
created_by_user_id	UUID	nullable	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• on (vendor_id) — all addresses for a vendor

---

purchasing.vendor_item — 22 cols

Vendor catalog: which items a vendor supplies, at what standing cost, in what purchase unit.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
vendor_sku	text	nullable	—	Vendor's own SKU for this item
vendor_item_name	text	nullable	—	Vendor's name for this item
cost_cents	bigint	NOT NULL	—	Standing catalog cost per purchase unit (current)
last_cost_cents	bigint	nullable	—	Prior cost — change-alert seam (spec 2.5)
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
purchase_uom_id	text	NOT NULL	—	Vendor purchase unit of measure; FK → shared.unit_of_measure
purchase_to_stock_factor	numeric(10,4)	NOT NULL	1	Stock units per vendor purchase unit (e.g. 6 = flat of 6)
minimum_order_qty	numeric(10,4)	nullable	—	Minimum order quantity
lead_time_days	int	nullable	—	Item-specific lead time override
is_preferred	boolean	NOT NULL	false	Preferred vendor for this item variant
is_active	boolean	NOT NULL	true	Whether still orderable
last_cost_updated_at	timestamptz	nullable	—	When cost_cents was last changed
barcode	text	nullable	—	Vendor item barcode
notes	text	nullable	—
attributes	JSONB	nullable	'{}'	Vertical/custom extension point. No prescribed shape.

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, vendor_id, variant_id) WHERE deleted_at IS NULL
• on (tenant_id, variant_id) — find all vendors for an item
• on (vendor_id, is_preferred) — preferred vendor lookup

---

purchasing.purchase_order — 38 cols

Purchase order header. Issued to a vendor for a set of line items.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
po_number	text	NOT NULL	—	UNIQUE (tenant_id, po_number) WHERE deleted_at IS NULL
site_id	UUID	NOT NULL	—	FK → multi_loc.site; receiving site
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
source_order_id	UUID	nullable	—	FK → orders.order_header; special-order trigger; closes orders.order_header.draft_po_id forward-ref
status	text	NOT NULL	'draft'	CHECK IN ('draft','pending_approval','approved','sent','partial','received','cancelled')
approval_status	text	nullable	—	CHECK (NULL OR IN ('pending','approved','rejected')); NULL when approval workflow not triggered
order_date	date	NOT NULL	—
expected_delivery_date	date	nullable	—
actual_delivery_date	date	nullable	—	Set on final receipt
payment_terms	text	nullable	—	Override vendor default; CHECK (NULL OR IN ('net_7','net_15','net_30','net_45','net_60','net_90','cod','prepaid'))
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
subtotal_cents	bigint	NOT NULL	0	SUM(purchase_order_line.line_total_cents)
tax_cents	bigint	NOT NULL	0
shipping_cents	bigint	NOT NULL	0
discount_cents	bigint	NOT NULL	0
total_cents	bigint	NOT NULL	0	subtotal_cents + tax_cents + shipping_cents − discount_cents
amount_invoiced_cents	bigint	NOT NULL	0	Cache: SUM(vendor_invoice.invoice_amount_cents) for POs matched to this order
amount_paid_cents	bigint	NOT NULL	0	Billing write-back cache; PurchasingService writes no value
reference_number	text	nullable	—	Vendor's order/confirmation number
tracking_number	text	nullable	—	Shipment tracking
carrier	text	nullable	—
ship_to_address_id	UUID	nullable	—	FK → purchasing.vendor_address (our address on file with vendor)
billing_address_id	UUID	nullable	—	FK → purchasing.vendor_address
notes	text	nullable	—	External notes (visible on PO)
internal_notes	text	nullable	—	Internal only
tags	text[]	nullable	—	Free-form tags for filtering
approved_by_user_id	UUID	nullable	—	FK → identity.identity_user; NOT NULL when approval_status = 'approved' (service-enforced)
approved_at	timestamptz	nullable	—	NOT NULL when approval_status = 'approved' (service-enforced)
sent_at	timestamptz	nullable	—	When emailed/transmitted to vendor
cancelled_at	timestamptz	nullable	—
cancellation_reason	text	nullable	—
created_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user
updated_by_user_id	UUID	nullable	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, po_number) WHERE deleted_at IS NULL
• on (tenant_id, vendor_id) — all POs for a vendor
• on (tenant_id, status) — PO list by status
• on (site_id, status) — receiving queue per site
• on (source_order_id) WHERE source_order_id IS NOT NULL — POs for a source order

---

purchasing.purchase_order_line — 24 cols

Individual line items on a purchase order. One PO has many lines. Carries dual-UOM cost snapshot frozen at PO time: vendor-UOM cost, conversion factor, and derived stock-unit cost. All three-way match comparisons use these frozen values.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
purchase_order_id	UUID	NOT NULL	—	FK → purchasing.purchase_order
line_number	int	NOT NULL	—	Display order; UNIQUE (purchase_order_id, line_number) WHERE deleted_at IS NULL
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
vendor_item_id	UUID	nullable	—	FK → purchasing.vendor_item; source catalog entry
ordered_qty	numeric(10,4)	NOT NULL	—	CHECK (> 0)
received_qty	numeric(10,4)	NOT NULL	0	Cache: SUM of receipt line accepted_qty; CHECK (>= 0)
invoiced_qty	numeric(10,4)	NOT NULL	0	Cache: SUM of vendor_invoice_line.billed_qty; CHECK (>= 0)
cancelled_qty	numeric(10,4)	NOT NULL	0	Quantity cancelled against this line; CHECK (>= 0)
purchase_unit_cost_cents	bigint	NOT NULL	—	Vendor-UOM cost — what the vendor bills per purchase unit
purchase_to_stock_factor	numeric(10,6)	NOT NULL	1	Snapshot at PO time: stock units per purchase unit (e.g. 6 if purchasing flats of 6)
stock_unit_cost_cents	bigint	NOT NULL	—	purchase_unit_cost_cents / purchase_to_stock_factor — stored, not computed
discount_amount_cents	bigint	NOT NULL	0	Line-level vendor discount (spec 2.15)
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
purchase_uom_id	text	NOT NULL	—	Vendor unit of measure; FK → shared.unit_of_measure
line_total_cents	bigint	NOT NULL	—	(purchase_unit_cost_cents × ordered_qty) − discount_amount_cents
expected_delivery_date	date	nullable	—	Line-level override
cancelled_at	timestamptz	nullable	—
notes	text	nullable	—
created_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• on (purchase_order_id) — all lines for a PO
• on (variant_id) — all PO lines for an item (reorder history)

---

purchasing.purchase_order_template — 9 cols

Saved template for recurring PO structures. One vendor has many templates.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
name	text	NOT NULL	—	Template display name
notes	text	nullable	—
created_by_user_id	UUID	nullable	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• on (vendor_id) — templates for a vendor

---

purchasing.purchase_order_template_line — 10 cols

Line items in a PO template.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
template_id	UUID	NOT NULL	—	FK → purchasing.purchase_order_template
line_number	int	NOT NULL	—	Display order
item_variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
qty	numeric(10,4)	NOT NULL	—	Default quantity
notes	text	nullable	—

Indexes:

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

---

purchasing.purchase_receipt — 23 cols

Receipt header recording goods received against a PO.

RLS: tenant-isolated 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
receipt_number	text	NOT NULL	—	UNIQUE (tenant_id, receipt_number) WHERE deleted_at IS NULL
purchase_order_id	UUID	NOT NULL	—	FK → purchasing.purchase_order
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
site_id	UUID	NOT NULL	—	FK → multi_loc.site
status	text	NOT NULL	'pending'	CHECK IN ('pending','complete','void')
received_at	timestamptz	NOT NULL	now()	When goods were physically received
ship_from_vendor_address_id	UUID	nullable	—	FK → purchasing.vendor_address; vendor location shipped from
carrier	text	nullable	—
tracking_number	text	nullable	—
packing_slip_ref	text	nullable	—	Vendor's packing slip reference
shipment_photo_ref	text	nullable	—	FORWARD-REF → files.file.id (Files module, locked 2026-06-11). R2 key / files.file record for the receiving-dock photo. visibility='private'. Doc-only touch 2026-06-11 — no col change.
freight_cents	bigint	NOT NULL	0	Freight charge recorded on this receipt
currency_code	char(3)	NOT NULL	'USD'	ISO 4217; genuine add — receipt-level cost recording
received_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user
verified_by_user_id	UUID	nullable	—	FK → identity.identity_user; double-count verifier for high-value receipts
note	text	nullable	—
attributes	JSONB	nullable	'{}'	Extension point. No prescribed shape.
created_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, receipt_number) WHERE deleted_at IS NULL
• on (tenant_id, purchase_order_id) — receipts for a PO
• on (site_id, status) — receiving queue per site

---

purchasing.purchase_receipt_line — 25 cols

Individual line items received. Each line triggers an InventoryService stock inbound movement.

RLS: tenant-isolated 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
purchase_receipt_id	UUID	NOT NULL	—	FK → purchasing.purchase_receipt
purchase_order_line_id	UUID	NOT NULL	—	FK → purchasing.purchase_order_line
variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
line_number	int	NOT NULL	—	Display order
expected_qty	numeric(10,4)	NOT NULL	—	From PO line at time of receipt
received_qty	numeric(10,4)	NOT NULL	—	Total quantity received (accepted + rejected)
accepted_qty	numeric(10,4)	NOT NULL	0	Quantity accepted into stock
rejected_qty	numeric(10,4)	NOT NULL	0	Quantity rejected on receipt
damaged_qty	numeric(10,4)	NOT NULL	0	Subset of rejected; physically damaged
over_short_qty	numeric(10,4)	NOT NULL	0	Positive = over-shipped; negative = short
stock_unit_cost_cents	bigint	NOT NULL	—	Cost recorded at receipt; used for inventory costing
currency_code	char(3)	NOT NULL	'USD'	ISO 4217; genuine add
inspection_status	text	NOT NULL	'pending'	CHECK IN ('pending','accepted','rejected','partial')
defect_code	text	nullable	—	Defect classification when inspection_status = 'rejected' or 'partial'
substitution_note	text	nullable	—	Note if received item substituted for ordered variant
destination_zone	text	nullable	—	Put-away zone; text reference to warehouse zone
lot_id	UUID	nullable	—	FK → inventory.lot; populated if item is lot-tracked
stock_movement_id	UUID	nullable	—	FORWARD-REF / deferred — plain UUID; FK → inventory.stock_movement added when movement log is stable
inspection_note	text	nullable	—	Free-text inspection notes
note	text	nullable	—	General line note
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id)
• on (purchase_receipt_id) — all lines for a receipt
• on (purchase_order_line_id) — match back to PO line
• on (variant_id) — receipt history for an item
• on (inspection_status) WHERE inspection_status NOT IN ('accepted') — open inspection items

---

purchasing.vendor_invoice — 33 cols

Vendor invoice. status has no 'paid' value — Billing owns payment. Three Billing write-back columns are set by BillingService only.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
site_id	UUID	NOT NULL	—	FK → multi_loc.site
invoice_number	text	NOT NULL	—	Vendor's invoice number; UNIQUE (tenant_id, vendor_id, invoice_number) WHERE deleted_at IS NULL
invoice_date	date	NOT NULL	—	Date on the invoice
due_date	date	nullable	—	Payment due date
received_at	timestamptz	NOT NULL	now()	When entered into the system
status	text	NOT NULL	'pending'	CHECK IN ('pending','approved','disputed','void') — no 'paid': Billing owns payment state
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
subtotal_cents	bigint	NOT NULL	—
tax_cents	bigint	NOT NULL	0
shipping_cents	bigint	NOT NULL	0
discount_cents	bigint	NOT NULL	0
invoice_amount_cents	bigint	NOT NULL	—	subtotal_cents + tax_cents + shipping_cents − discount_cents
amount_matched_cents	bigint	NOT NULL	0	Cache: SUM(vendor_invoice_match.matched_amount_cents)
amount_credited_cents	bigint	NOT NULL	0	Cache: total vendor credits applied to this invoice
notes	text	nullable	—
internal_notes	text	nullable	—
disputed_at	timestamptz	nullable	—	NOT NULL when status = 'disputed' (service-enforced)
dispute_reason	text	nullable	—
void_at	timestamptz	nullable	—	NOT NULL when status = 'void' (service-enforced)
void_reason	text	nullable	—
approved_by_user_id	UUID	nullable	—	FK → identity.identity_user
approved_at	timestamptz	nullable	—	NOT NULL when status = 'approved' (service-enforced)
billing_ap_ref	text	nullable	—	Billing write-back — reference to Billing AP record. PurchasingService writes no value.
payment_status_ref	text	nullable	—	Billing write-back — reflects Billing payment state. PurchasingService writes no value.
paid_at	timestamptz	nullable	—	Billing write-back — set by BillingService when payment clears. PurchasingService writes no value.
created_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user
updated_by_user_id	UUID	nullable	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, vendor_id, invoice_number) WHERE deleted_at IS NULL
• on (tenant_id, vendor_id) — all invoices for a vendor
• on (tenant_id, status) — invoice list by status
• on (tenant_id, due_date) WHERE status NOT IN ('void') — AP aging / overdue sweep

---

purchasing.vendor_invoice_line — 18 cols

Individual line items on a vendor invoice. line_type discriminates item lines (must have variant_id) from freight, tax, and misc charges. billed_qty is what the vendor claims; matched_qty is the rollup from vendor_invoice_match rows for this line.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_invoice_id	UUID	NOT NULL	—	FK → purchasing.vendor_invoice
purchase_order_line_id	UUID	nullable	—	FK → purchasing.purchase_order_line; NULL for non-PO charges
variant_id	UUID	nullable	—	FK → inventory.item_variant; NOT NULL when line_type = 'item' (service-enforced); NULL for freight/tax/misc
line_number	int	NOT NULL	—	Display order
line_type	text	NOT NULL	'item'	CHECK IN ('item','freight','tax','misc'); line_type = 'item' requires variant_id NOT NULL (service-enforced)
description	text	NOT NULL	—	Line description
billed_qty	numeric(10,4)	NOT NULL	—	Vendor-UOM quantity the vendor is billing; CHECK (> 0)
matched_qty	numeric(10,4)	NOT NULL	0	Cache: SUM of vendor_invoice_match.matched_qty for this line; CHECK (>= 0)
purchase_unit_cost_cents	bigint	NOT NULL	—	Cost per vendor UOM as billed
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
line_total_cents	bigint	NOT NULL	—	billed_qty × purchase_unit_cost_cents
tax_cents	bigint	NOT NULL	0	Line-level tax
notes	text	nullable	—

Removed from prior draft: qty (replaced by billed_qty + matched_qty split), uom_code (implicit from purchase_order_line for item lines; not applicable for freight/tax/misc), discount_percent (not applicable at invoice-line level in this model).

Indexes:

• PK on id
• on (tenant_id)
• on (vendor_invoice_id) — all lines for an invoice
• on (purchase_order_line_id) — link back to PO line
• on (line_type) WHERE line_type != 'item' — locate freight/tax/misc lines

---

purchasing.vendor_invoice_match — 16 cols

Three-way match junction: links a vendor invoice line to a receipt line. The junction is line-to-line (N:M) — a single invoice line may match multiple receipt lines and vice versa. Mutable — rows updated as match status progresses; Audit module owns immutable history.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_invoice_line_id	UUID	NOT NULL	—	FK → purchasing.vendor_invoice_line — links to the invoice LINE, not the header
purchase_receipt_line_id	UUID	NOT NULL	—	FK → purchasing.purchase_receipt_line
match_status	text	NOT NULL	—	CHECK IN ('matched','quantity_variance','price_variance','over_billed','under_billed','disputed','resolved')
matched_qty	numeric(10,4)	NOT NULL	—	Quantity covered by this match row
matched_amount_cents	bigint	NOT NULL	—	Amount covered by this match row
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
quantity_variance	numeric(10,4)	NOT NULL	0	Invoice qty minus receipt qty for this row
price_variance_cents	bigint	NOT NULL	0	Invoice amount minus PO/receipt expected amount
note	text	nullable	—	Explanation of variance or resolution
resolved_by_user_id	UUID	nullable	—	FK → identity.identity_user; NOT NULL when match_status = 'resolved' (service-enforced)
resolved_at	timestamptz	nullable	—	NOT NULL when match_status = 'resolved' (service-enforced)

Indexes:

• PK on id
• on (tenant_id)
• on (vendor_invoice_line_id) — all match rows for an invoice line
• on (purchase_receipt_line_id) — all match rows for a receipt line
• on (match_status) WHERE match_status IN ('quantity_variance','price_variance','over_billed','under_billed','disputed') — open discrepancies

---

purchasing.vendor_credit — 23 cols

Credit memo issued by a vendor. Applied against invoices to reduce amounts owed.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
site_id	UUID	NOT NULL	—	FK → multi_loc.site
credit_number	text	NOT NULL	—	UNIQUE (tenant_id, credit_number) WHERE deleted_at IS NULL
credit_date	date	NOT NULL	—	Date on credit memo
credit_type	text	NOT NULL	—	CHECK IN ('price_adjustment','return_credit','overpayment','other')
status	text	NOT NULL	'open'	CHECK IN ('open','partially_applied','fully_applied','void')
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
credit_amount_cents	bigint	NOT NULL	—	Total credit value
applied_amount_cents	bigint	NOT NULL	0	Cache: SUM of applications to invoices
remaining_amount_cents	bigint	NOT NULL	—	credit_amount_cents − applied_amount_cents; CHECK (>= 0)
vendor_invoice_id	UUID	nullable	—	FK → purchasing.vendor_invoice; source invoice if applicable
vendor_return_id	UUID	nullable	—	FK → purchasing.vendor_return; source return if applicable
notes	text	nullable	—
applied_at	timestamptz	nullable	—	When credit was fully applied (status = 'fully_applied')
applied_by_user_id	UUID	nullable	—	FK → identity.identity_user; actor who completed application
voided_at	timestamptz	nullable	—
voided_by_user_id	UUID	nullable	—	FK → identity.identity_user
created_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, credit_number) WHERE deleted_at IS NULL
• on (tenant_id, vendor_id) — credits for a vendor
• on (tenant_id, status) WHERE status NOT IN ('void','fully_applied') — open credits

---

purchasing.vendor_return — 22 cols

Return authorization and outbound shipment header for goods being sent back to a vendor — carries return reason, vendor-issued authorization number, expected credit amount, and shipping state. Each vendor_return_line triggers an InventoryService outbound stock movement on ship.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
site_id	UUID	NOT NULL	—	FK → multi_loc.site
purchase_order_id	UUID	nullable	—	FK → purchasing.purchase_order; source PO if applicable
return_number	text	NOT NULL	—	UNIQUE (tenant_id, return_number) WHERE deleted_at IS NULL
return_date	date	NOT NULL	—
status	text	NOT NULL	'draft'	CHECK IN ('draft','authorized','shipped','vendor_received','completed','cancelled')
rma_number	text	nullable	—	Vendor-issued RMA number; required (service-enforced) when status IN ('authorized','shipped','vendor_received','completed')
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
reason	text	NOT NULL	—	CHECK IN ('damaged','defective','wrong_item','overstock','quality','other')
carrier	text	nullable	—
tracking_number	text	nullable	—
notes	text	nullable	—
authorized_by_user_id	UUID	nullable	—	FK → identity.identity_user; NOT NULL when status ≠ 'draft' (service-enforced)
authorized_at	timestamptz	nullable	—	NOT NULL when status ≠ 'draft' (service-enforced)
shipped_at	timestamptz	nullable	—
cancelled_at	timestamptz	nullable	—
created_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, return_number) WHERE deleted_at IS NULL
• on (tenant_id, vendor_id) — returns for a vendor
• on (tenant_id, status) — returns by status

---

purchasing.vendor_return_line — 17 cols

Individual line items in a vendor return. Each line triggers an InventoryService stock outbound movement when shipped.

RLS: tenant-isolated 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
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_return_id	UUID	NOT NULL	—	FK → purchasing.vendor_return
purchase_order_line_id	UUID	nullable	—	FK → purchasing.purchase_order_line
purchase_receipt_line_id	UUID	nullable	—	FK → purchasing.purchase_receipt_line; source receipt line
item_variant_id	UUID	NOT NULL	—	FK → inventory.item_variant
line_number	int	NOT NULL	—	Display order
qty_returned	numeric(10,4)	NOT NULL	—
unit_cost_cents	bigint	NOT NULL	—	Cost at time of return
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
reason	text	nullable	—	Line-level reason override
lot_id	UUID	nullable	—	FK → inventory.lot
notes	text	nullable	—
inventory_movement_id	UUID	nullable	—	FORWARD-REF / deferred — plain UUID; FK → inventory.stock_movement added when movement log is stable

Indexes:

• PK on id
• on (tenant_id)
• on (vendor_return_id) — all lines for a return
• on (item_variant_id) — return history per item

---

Column counts: vendor(30) + vendor_contact(14) + vendor_address(16) + vendor_item(22) + purchase_order(38) + purchase_order_line(24) + purchase_order_template(9) + purchase_order_template_line(10) + purchase_receipt(23) + purchase_receipt_line(25) + vendor_invoice(33) + vendor_invoice_line(18) + vendor_invoice_match(16) + vendor_credit(23) + vendor_return(22) + vendor_return_line(17) = 340

---