Inventory and procurement

Inventory & Procurement

When to Use

Use when designing or implementing product stock management, warehouse locations, order reservations, purchase orders, supplier goods receipts, stock adjustments, or customer/vendor returns.

1. Separate Physical Stock, Reservations, and Available-to-Promise

Never track inventory with a single mutable stock_count integer! Maintain distinct, auditable buckets per (tenant_id, warehouse_id, product_id):

1. On-Hand (qty_on_hand): Physical units currently sitting in the warehouse shelf/bin.
2. Reserved / Allocated (qty_reserved): Units committed to confirmed, unshipped Sales Orders or active production/pick jobs.
3. Available / Available-to-Promise (qty_available = qty_on_hand - qty_reserved): What can safely be promised/allocated to a new customer order right now without overselling.
4. Incoming / On-Order (qty_incoming): Units on confirmed Supplier Purchase Orders not yet received.
5. Quarantined / Damaged (qty_quarantined): Returned or damaged units awaiting inspection—excluded from qty_available!

2. Append-Only Inventory Movement Ledger

Every change to qty_on_hand or qty_reserved must be backed by an immutable inventory_movements ledger entry—never allow ad-hoc UPDATE inventory SET qty_on_hand = 50 without a movement record:

• Required Movement Fields: id, tenant_id, product_id, warehouse_id, movement_type (purchase_receipt, sales_reservation, sales_reservation_release, sales_shipment, customer_return, transfer_out, transfer_in, cycle_count_adjustment), qty_delta, reference_type (sales_order, purchase_order, fulfillment, rma, stock_count), reference_id, actor_id, and created_at.
• Concurrency & Non-Negative Stock Guard:
  • Lock the (warehouse_id, product_id) stock balance row (SELECT ... FOR UPDATE) when reserving or shipping stock, and enforce qty_on_hand >= 0 and qty_available >= 0 (unless explicit backorder/negative-stock policy is enabled for that SKU).

3. Procure-to-Pay (Purchase Orders & Three-Way Match)

• Purchase Order (PO) Lifecycle: draft $\rightarrow$ pending_approval $\rightarrow$ issued_to_vendor $\rightarrow$ partially_received $\rightarrow$ received $\rightarrow$ closed.
• Goods Receipt Note (GRN): Receiving goods against a PO line increments qty_received on the PO line, decreases qty_incoming, and posts a purchase_receipt movement incrementing qty_on_hand.
• Three-Way Match Readiness: Track qty_ordered (PO) vs. qty_received (Goods Receipt) vs. qty_billed (Vendor Bill) per line so finance can detect over-billing or short deliveries.

4. Customer Returns (RMA) & Restocking Disposition

When a customer returns goods against a delivered order/fulfillment:

• Validate qty_returned <= qty_delivered - qty_already_returned.
• Require an explicit Disposition Decision on receipt:
  • restock $\rightarrow$ increments qty_on_hand (sellable).
  • quarantine / scrap $\rightarrow$ routes to qty_quarantined or posts a write-off movement without inflating sellable stock.