Skip to content

Inventory dimensions

Backend domain: app/graphql/inventories/ Migration: add_inventory_dimensions RBAC Path / Resource: INVENTORIES

Overview

This feature adds three opt-in dimensions to inventory:

  1. Multi-location — every inventory row, log, lot, and cost layer carries a location_id. Each tenant has one default location (a "Main" row is seeded by the migration). Existing flows that don't specify a location keep using the default — backwards-compatible.
  2. Lot / serial tracking — opt-in per item via Item.trackingMode (NONE / LOT / SERIAL). When set, every receipt and outflow must carry a lotNumber. SERIAL items keep one InventoryLot row per unit (quantityReceived = 1); LOT items keep one row per batch.
  3. Costing methods — opt-in per item via Item.costingMethod (AVERAGE / FIFO / LIFO). FIFO/LIFO items maintain an InventoryCostLayer row per receipt; outflow consumes layers oldest-first (FIFO) or newest-first (LIFO) and returns the COGS. AVERAGE items continue to use Item.averageCost unchanged.

All three dimensions are additive: existing items default to costingMethod = AVERAGE, trackingMode = NONE, and the Main location, so untouched tenants see no behaviour change.

Setup prerequisites

  • After upgrade, exactly one InventoryLocation exists with name = 'Main', code = 'MAIN', isDefault = true.
  • Every existing Inventory and InventoryLog row has been backfilled to point at that location.
  • No item is opted into LOT/SERIAL tracking or FIFO/LIFO costing — those must be set explicitly per item.

Data model

Table Purpose Key columns
inventory_locations Tenant-scoped warehouses / storefronts. name, code (unique), is_default, archived
inventory_lots Per-(item, location, lot) tracking. lot_number, quantity_received, quantity_remaining, unit_cost, expiration_date, received_at
inventory_cost_layers FIFO/LIFO layers. unit_cost, quantity_received, quantity_remaining, received_at, lot_id (nullable)
inventory Existing per-item stock — now per-(item, location). + location_id; UNIQUE swapped to (item_id, location_id)
inventory_logs Existing movement history. + location_id, lot_id
items + costing_method (SmallInt enum), tracking_mode (SmallInt enum)

Partial unique index inventory_locations_is_default_uq enforces "exactly one default location per tenant".

GraphQL surface

Queries

inventoryLocations(includeArchived: Boolean = false): [InventoryLocation!]!
getInventoryLotsByItemId(itemId: UUID!, locationId: UUID): [InventoryLot!]!

# Existing query, no shape change:
getIventoryLogsByItemId(itemId: UUID!): [InventoryLog!]!

InventoryLocation fields: id, name, code, isDefault, archived, description. InventoryLot fields: id, itemId, locationId, lotNumber, quantityReceived, quantityRemaining, unitCost, expirationDate, receivedAt.

Mutations

createInventoryLocation(location: InventoryLocationInput!): InventoryLocation!
updateInventoryLocation(location: InventoryLocationInput!): InventoryLocation!
archiveInventoryLocation(locationId: UUID!): InventoryLocation!

# Existing mutations, with new optional fields on the input:
createManualInventoryAdjustment(manualInventoryAdjustment: ManualInventoryAdjustmentInput!): ManualInventoryAdjustment!

InventoryLocationInput: name, code?, isDefault?, description?, id? (required on update). Setting isDefault: true automatically clears the previous default in the same transaction.

ManualInventoryAdjustmentInput adds optional fields: - locationId: UUID (omit → default location) - lotNumber: String (required when item's trackingMode != NONE) - unitCost: Decimal (required when item's costingMethod is FIFO or LIFO and quantity is positive) - expirationDate: Date (LOT only)

PurchaseOrderDetailItemReceivedInput adds the same three optional fields (locationId, lotNumber, expirationDate). actualPrice already carries the unit cost.

ItemInput adds: - costingMethod: CostingMethod (AVERAGE | FIFO | LIFO, default AVERAGE) - trackingMode: TrackingMode (NONE | LOT | SERIAL, default NONE)

ItemResponse / ItemLiteResponse expose costingMethod and trackingMode.

Behaviour rules

  • Receipt on a LOT/SERIAL item without lotNumber → ValueError. The mutation fails; nothing is written.
  • Receipt on a FIFO/LIFO item without unitCost → ValueError.
  • Outflow on a FIFO/LIFO item with no open layers → consumes the shortfall at Item.lastUnitCost (falls back to averageCost, then 0). The log is still written so the audit trail stays continuous.
  • Outflow with lotNumber decrements that lot's quantityRemaining. Lot is not auto-deleted at zero; it stays for historical reads.
  • Archiving the default location → rejected. Mark another location default first.

Frontend contract

Screens that need to change:

  • Item form — add a "Costing method" picker (defaults AVERAGE) and a "Tracking mode" picker (defaults NONE). Disable changing trackingMode away from NONE for items that currently have stock (the backend will reject with a clear error).
  • Locations screen — new CRUD list, gated by INVENTORIES permission. Single "Set as default" toggle per row.
  • PO receipt screen — add optional Location picker (defaults Main) and Lot # / Expiration date inputs (shown only when the item is LOT/SERIAL).
  • Manual adjustment screen — same Location + Lot / Unit cost / Expiration fields. unitCost becomes required when item is FIFO/LIFO and quantity > 0.
  • Stock query — Item.inventory now returns the default location's row. Multi-location screens should fetch via inventoryLocations + a future per-location stock query (follow-up).

Open follow-ups

  • transferStock(itemId, fromLocationId, toLocationId, quantity) with in-transit balance.
  • Stocktake / cycle-count with variance JE.
  • Per-invoice-line locationId / lotNumber (today, invoices draw from default location).
  • itemCostReport query that returns COGS by costing method.
  • FEFO picking suggestion (earliest expiry first) for LOT-tracked items.