Inventory Table Updates Reference

This document describes which database tables are written to during every inventory-affecting operation, the cost accounting logic, and the full event-to-database flow for each process.

---

Tables Involved

1. inventory — Always updated

The primary table. One row per (businessId, locationId, productId, variantId?) tuple. Every operation that changes stock touches this table.

Each row tracks 10+ quantity/cost bucket pairs:

Bucket	Quantity Column	Cost Column
On-hand	quantity	cost
Reserved	reservedStock	reservedStockCost
In-transit outgoing	inTransitOutgoing	inTransitOutgoingCost
In-transit incoming	inTransitIncoming	inTransitIncomingCost
Pending inspection	pendingInspection	pendingInspectionCost
Damaged	damaged	damagedCost
Under repair	underRepair	underRepairCost
Quarantined	quarantined	quarantinedCost
To be refurbished	toBeRefurbished	toBeRefurbishedCost
Refurbishing	refurbishing	refurbishingCost
Detail count	quantityInventoryDetail	(count only)

---

2. product / productVariant — Inbound and production

Updated when stock is received (Purchase, GRN, Supplier Return reversal) and when a production run is completed. Not touched by sales, transfers, adjustments, or restaurant orders.

Column	Table	Updated for
cost	product	Simple products — WAC recalculated on receipt
avgCost	productVariant	Variant products — WAC recalculated on receipt
lastCost	productVariant	Variant products only — unit cost of the most recently received batch

product.cost is a WAC reference for pricing and new transaction costing, not the source of truth for inventory valuation. The authoritative stock value lives in inventory.cost.

lastCost (variant-only) captures the per-unit cost of the most recent receipt. It differs from avgCost, which is the running weighted average across all receipts.

---

3. inventory_ledger — Every operation

An append-only audit row is created for every stock movement. Key fields:

• sourceType: purchase, sale, goodsReceivedNote, inventoryTransfer, transferDispatchNote, transferGoodsReceipt, inventoryAdjustment, customerReturn, replacementIssueNote, repairIssueNote, creditNote, cancellation, materialConsumption, productionRun, restaurant_order
• movementType, stockBucket, quantity, cost, unitCost
• referenceId, referenceType — links to the source document
• idempotencyKey — MD5 hash preventing duplicate entries on event replay

---

4. inventory_detail — Serial/batch-tracked items

Created or deleted for items with serial numbers, batch numbers, or expiration dates. When records change they emit events that update inventory.quantityInventoryDetail.

---

5. product_cost_history — Inbound receipts only

Audit log for every WAC change. Created alongside product/productVariant updates.

Column	Value
previousCostAmount	WAC before this receipt
costAmount	WAC after this receipt
lastCostAmount	Unit cost of the received batch
quantityOnHand	Stock quantity before receipt
quantityReceived	Quantity received in this transaction
sourceType	purchase, goods_received_note, supplier_return, production_run
referenceId	ID of the triggering document

---

6. order — Restaurant payment only

The costDetail JSON column is written once, at full payment completion by OrderPaymentService / OrderBillPaymentService. It is a point-in-time snapshot of per-item costs captured from product.cost at the moment the order is paid.

{
  "items": [{ "productId": "...", "quantity": 2, "unitCost": 15.50, "totalCost": 31.00 }],
  "totalCost": 31.00,
  "snapshotAt": "2026-04-07T12:00:00Z"
}

This snapshot then drives BOM deduction (recipe ingredients deducted from inventory) and FEL sale record creation via the order.settled event.

---

Process × Table Matrix

Process	inventory	product / productVariant	inventory_ledger	inventory_detail	product_cost_history	order.costDetail
Purchase received	✅ qty+, cost+	✅ WAC (cost / avgCost + lastCost)	✅	✅ create	✅	❌
GRN received	✅ qty+, cost+	✅ WAC (cost / avgCost + lastCost)	✅	✅ create	✅	❌
Sale submitted	✅ qty−, cost−	❌	✅	✅ delete	❌	❌
Material consumption	✅ qty−, cost−	❌	✅	✅ delete	❌	❌
Restaurant order paid	✅ qty− (recipe ingredients)	❌	✅ (sourceType = restaurant_order)	❌	❌	✅ snapshot
Transfer — reserve	✅ qty− → reservedStock+	❌	✅	❌	❌	❌
Transfer — dispatch	✅ reservedStock− → inTransitOutgoing+ (origin), inTransitIncoming+ (dest)	❌	✅	✅ delete origin	❌	❌
Transfer — receipt	✅ inTransitOutgoing− (origin), inTransitIncoming− → qty+ (dest)	❌	✅	✅ create dest	❌	❌
Inventory adjustment (increase)	✅ qty+, cost+	❌	✅	✅ create	❌	❌
Inventory adjustment (decrease)	✅ qty−, cost−	❌	✅	✅ delete	❌	❌
Stock count posted	✅ via adjustment	❌	✅ via adjustment	✅ via adjustment	❌	❌
Production run completed	✅ inputs qty−, outputs qty+	✅ WAC (cost / avgCost + lastCost)	✅ (sourceType = productionRun)	✅ delete inputs, create outputs	✅	❌
Customer return (RETURN type)	✅ qty+, cost+	❌	✅	❌	❌	❌
Customer return (REPLACEMENT/REPAIR)	✅ pendingInspection+	❌	✅	❌	❌	❌
Return inspection → damaged	✅ pendingInspection− → damaged+	❌	✅	❌	❌	❌
Return inspection → repair	✅ pendingInspection− → underRepair+	❌	✅	❌	❌	❌
Replacement issue — reserve	✅ qty− → reservedStock+	❌	✅	❌	❌	❌
Replacement issue — completed	✅ reservedStock−	✅ reverse WAC	✅	❌	✅	❌
Repair issue — returned	✅ underRepair−	❌	✅	❌	❌	❌
FEL Credit Note certified	✅ qty+, cost+	❌	✅	✅ restore (serial/batch)	❌	❌
FEL Cancellation certified	✅ reverse sale	❌	✅	✅ restore (serial/batch)	❌	❌

---

Cost Accounting Logic

All cost updates use atomic SQL expressions — no read-then-write. This prevents race conditions under concurrent transactions.

WAC on inbound (Purchase / GRN)

newCost = (onHandQty × currentCost + receivedQty × unitCost) / (onHandQty + receivedQty)

Special case: when onHandQty <= 0, newCost = unitCost (no blending needed).

Where onHandQty is the quantity before the receipt. Implemented in update-product-costs.ts → updateProductCostsFromReceipt().

Simple products: updates product.cost Variant products: updates productVariant.avgCost + productVariant.lastCost

Cost removal on outbound (Sale / Consumption)

cost = ROUND(
  CASE
    WHEN (quantity - qty_to_decrease) <= 0 THEN 0
    ELSE cost - (cost / GREATEST(quantity, 1)) * qty_to_decrease
  END,
  6
)

When quantity reaches 0: cost is forced to 0, preventing fractional rounding residue. Negative stock: Not blocked by SQL — a warning is logged but the operation proceeds. GREATEST(quantity, 1) protects against division by zero.

Cost transfer between buckets (e.g., on-hand → reserved)

reserved_stock_cost = COALESCE(reserved_stock_cost, 0)
  + (cost / GREATEST(quantity, 1)) * qty_to_reserve

The per-unit cost (cost / GREATEST(quantity, 1)) is computed inline so that the proportional cost moves with the quantity.

Reverse WAC on supplier return (Replacement issue completed)

newCost = ((onHandQty + returnedQty) × currentCost - returnedQty × returnCost) / onHandQty

Implemented in update-product-costs.ts → updateProductCostsFromSupplierReturn().

---

Event → Handler → Tables Flow

Purchase / GRN Inbound

OnCreatePurchaseEvent / OnUpdateGoodsReceivedNoteEvent
  └─ InventoryInboundHandler
      └─ InventoriesService.processPurchaseEvent() / processGoodsReceivedNoteEvent()
          ├─ inventoriesRepository.increaseInventory()
          │   └─ inventory: quantity+, cost+
          ├─ inventoryLedgersService.createLedgerEntries()
          │   └─ inventory_ledger: INSERT rows (sourceType = purchase | goodsReceivedNote)
          ├─ inventoryDetailsService.createInventoryDetail()
          │   └─ inventory_detail: INSERT rows
          │   └─ inventory: quantityInventoryDetail+ (via OnCreateInventoryDetailEvent)
          └─ updateProductCostsFromReceipt()
              ├─ product.cost OR productVariant.avgCost + lastCost updated (WAC)
              └─ product_cost_history: INSERT row

Sale / Material Consumption Outbound

OnCreateSaleEvent / OnUpdateSaleEvent
  └─ InventoryOutboundHandler
      └─ InventoriesService.processSaleEvent()
          ├─ inventoriesRepository.decreaseInventory()
          │   └─ inventory: quantity−, cost− (atomic per-unit formula; cost → 0 when qty → 0)
          ├─ inventoryLedgersService.createLedgerEntries()
          │   └─ inventory_ledger: INSERT rows (sourceType = sale | materialConsumption)
          └─ decreaseQuantityInventoryDetailsFromItems()
              └─ inventory_detail: DELETE rows
              └─ inventory: quantityInventoryDetail− (via OnDeleteInventoryDetailEvent)

Restaurant Order Settlement

Restaurant orders are handled differently from retail sales. Inventory is not deducted at order creation or submission — only when payment is fully collected.

OrderPaymentService.recordPayment() — when remaining balance = 0
  ├─ order: costDetail = JSON snapshot of item costs (product.cost at payment time)
  ├─ order: status = PAID
  └─ emit "order.settled"
      └─ OnOrderSettledHandler
          ├─ BomDeductionService.calculateDeductions()
          │   └─ For each order_item: reads recipeSnapshotJsonb (voided items skipped)
          │   └─ Within a single transaction:
          │       ├─ inventoryLedgersService.createByRowsWithTransaction()
          │       │   └─ inventory_ledger: INSERT rows (sourceType = "restaurant_order")
          │       │      idempotencyKey: order:{orderId}:ingredient:{ingredientId}:item:{itemId}
          │       └─ inventoriesRepository.decreaseInventory() — grouped by (locationId, productId)
          │           └─ inventory: quantity−, cost− for each recipe ingredient
          └─ processSettlementSale()
              └─ Creates a sale record → fires OnCreateSaleEvent
                  └─ InventoryOutboundHandler processes it normally
                     (standard sale inventory deduction on product level)

Two ledger entries per restaurant order item: one from BOM deduction (recipe ingredients, sourceType = restaurant_order) and one from the settlement sale (the finished product, sourceType = sale).

Inventory Transfer (3 stages)

Stage 1 — InventoryTransfer APPROVED
  └─ inventoriesRepository.decreaseInventoryToReserveStock()
      └─ inventory (origin): quantity−, reservedStock+

Stage 2 — TransferDispatchNote APPROVED
  ├─ inventoriesRepository.decreaseReservedStockToInTransitOutgoing()
  │   └─ inventory (origin): reservedStock−, inTransitOutgoing+
  ├─ inventoriesRepository.increaseInTransitIncoming()
  │   └─ inventory (destination): inTransitIncoming+
  └─ inventory_detail (origin): DELETE rows

Stage 3 — TransferGoodsReceipt APPROVED
  ├─ inventoriesRepository.decreaseInTransitOutgoing()
  │   └─ inventory (origin): inTransitOutgoing−
  ├─ inventoriesRepository.decreaseInTransitIncoming()
  │   └─ inventory (destination): inTransitIncoming−, quantity+, cost+
  └─ inventory_detail (destination): INSERT rows

Each stage → inventory_ledger: INSERT rows

Production Run Completion

Production runs call the repository directly (not via event handlers). After completion a scoped OnProductionRunCompletedEvent is emitted — downstream listeners update product-level stock aggregates and trigger e-commerce sync.

ProductionRunsService.completeProductionRun() — status → COMPLETED
  ├─ Validate: inputs must be raw_material / component / packaging
  │            outputs must be finished_good
  ├─ Validate: sufficient quantity available at source location
  ├─ For each input (raw material consumed):
  │   ├─ inventoriesRepository.decreaseInventory()
  │   │   └─ inventory: quantity−, cost−
  │   └─ decreaseQuantityInventoryDetailsFromItems()
  │       └─ inventory_detail: DELETE rows
  ├─ For each output (finished good produced):
  │   ├─ ensureInventoryRecordsExist() — auto-creates row if missing
  │   ├─ inventoriesRepository.increaseInventory()
  │   │   └─ inventory: quantity+, cost+
  │   └─ increaseQuantityInventoryDetailsFromItems()
  │       └─ inventory_detail: INSERT rows
  ├─ updateProductCostsFromReceipt()
  │   ├─ product.cost OR productVariant.avgCost + lastCost updated (WAC)
  │   └─ product_cost_history: INSERT row (sourceType = "production_run")
  ├─ inventoryLedgersService.createByRowsWithTransaction()
  │   └─ inventory_ledger: INSERT rows
  │      (sourceType = "productionRun", referenceType = "productionRunInput" | "productionRunOutput")
  ├─ productionRun: status = COMPLETED
  └─ emit OnProductionRunCompletedEvent(userId, inputs, outputs)
      ├─ ProductInventoryListener — updates product-level stock counters
      └─ InventorySyncListener — syncs affected products to e-commerce

Stock Count Reconciliation

Stock counts never write to inventory directly — they create inventoryAdjustment documents and let the normal adjustment event chain handle the rest.

StockCountService.postSession() — session status = CLOSED or APPROVED
  ├─ Query stockCountPostSummaryV view for variance data
  ├─ For positive variances (actual > expected):
  │   └─ InventoryAdjustmentsService.createInventoryAdjustment(INCREASE)
  │       └─ inventoryAdjustment: INSERT row (docNumber = "SC-{sessionId}-increase")
  │       └─ inventory_ledger: INSERT rows
  │       └─ Emits OnCreateInventoryAdjustmentEvent
  │           └─ InventoryAdjustmentHandler → inventoriesRepository.increaseInventory()
  └─ For negative variances (actual < expected):
      └─ InventoryAdjustmentsService.createInventoryAdjustment(DECREASE)
          └─ inventoryAdjustment: INSERT row (docNumber = "SC-{sessionId}-decrease")
          └─ inventory_ledger: INSERT rows
          └─ Emits OnCreateInventoryAdjustmentEvent
              └─ InventoryAdjustmentHandler → inventoriesRepository.decreaseStockByInventoryAdjustment()

Customer Return (Replacement flow)

Stage 1 — CustomerReturn created (REPLACEMENT)
  └─ inventoriesRepository.increasePendingInspection()
      └─ inventory: pendingInspection+

Stage 2 — CustomerReturn updated (DAMAGED / REPAIR condition)
  ├─ inventoriesRepository.decreasePendingInspection()
  │   └─ inventory: pendingInspection−
  └─ inventoriesRepository.increaseDamaged() OR increaseUnderRepair()
      └─ inventory: damaged+ OR underRepair+

Stage 3a — ReplacementIssueNote → RESERVE_STOCK
  └─ inventoriesRepository.decreaseInventoryToReserveStock()
      └─ inventory: quantity−, reservedStock+

Stage 3b — ReplacementIssueNote → COMPLETED
  ├─ inventoriesRepository.decreaseReservedStock()
  │   └─ inventory: reservedStock−
  └─ updateProductCostsFromSupplierReturn()
      ├─ product.cost OR productVariant.avgCost updated (reverse WAC)
      └─ product_cost_history: INSERT row

Each stage → inventory_ledger: INSERT rows

FEL Reversals (Credit Note / Cancellation)

FEL reversals restore quantity, cost, and serial/batch detail records from the original sale document.

OnCertifyCreditNoteEvent / OnCertifyCancellationEvent
  └─ InventoryDetailAndFelHandler
      └─ InventoriesService.processCertifyCreditNoteEvent() / processCertifyCancellationEvent()
          ├─ Reads costDetail from the original sale for accurate cost restoration
          ├─ Within a single transaction:
          │   ├─ inventoryLedgersService.createByRowsWithTransaction()
          │   │   └─ inventory_ledger: INSERT rows (sourceType = creditNote | cancellation)
          │   ├─ inventoriesRepository.increaseInventory()
          │   │   └─ inventory: quantity+, cost+
          │   ├─ increaseQuantityInventoryDetailsFromItems()
          │   │   └─ inventory_detail: INSERT / update rows for serial/batch-tracked items
          │   └─ inventoriesRepository.increaseQuantityInventoryDetail()
          │       └─ inventory: quantityInventoryDetail+ (manual update — events not fired within trx)
          └─ emit OnInventoryIncreasedEvent

---

Key Design Rules

1. product.cost is NOT the inventory valuation source. It is the WAC reference for pricing and new transaction costing. The actual stock value lives in inventory.cost (sum of on-hand cost).

2. Cost must be updated in every bucket that holds value. When quantity moves between buckets (e.g., on-hand → reserved), the proportional cost moves with it using the per-unit formula.

3. Sales never update product.cost. Inbound receipts (Purchase, GRN, completed Replacement) and production run completions recalculate WAC on product/productVariant.

4. All writes happen inside a database.transaction() — inventory, ledger, details, and cost history are committed atomically.

5. Inventory rows are auto-created if missing. ensureInventoryRecordsExist() creates the row within the same transaction before updating, preventing FK violations.

6. Restaurant orders deduct via recipe ingredients, not the finished product. BOM deduction writes to both inventory_ledger and inventory (decreasing ingredient quantities) within one transaction; the settlement also creates a sale record for the finished menu item, which is handled by the normal sale outbound flow.

7. Stock counts never touch inventory directly. They generate inventoryAdjustment documents (one for increases, one for decreases) and rely on the adjustment event chain.

8. FEL reversals restore serial/batch detail records. Both inventory (quantity + cost) and inventory_detail (serial/batch rows) are restored within the same transaction, using the original sale or credit note detail JSON as the source of truth.