Skip to content

Inventory and multi-warehouse

inventory-and-multi-warehouse

Track stock per location with on-hand, reserved, and available quantities, reserve at checkout, and allocate orders across warehouses without overselling. Use when adding warehouses or stores as stock locations, reservations, transfers, or order routing.

Version 0.3.2  ·  MIT  ·  Source  ·  Rule

Install this skill

Copy the skill and its rule into your project:

npx @sadrmanesh/commerce-agent-skills add inventory-and-multi-warehouse

The skill goes to .agents/skills/ and the rule to .agents/rules/. See Command line for other directories.

A single stock number per product stops working once there are two places that ship, or a checkout that holds stock while the shopper pays. Model quantities per location and per state, and change them only through movements. Platform notes are in the platform notes.

Procedure

  1. Key stock by purchasable unit (SKU or variation) and location. A product with sizes has one row per size per location.
  2. Keep separate quantities: on_hand (physically there), reserved (held for unpaid or unshipped orders), and available = on_hand - reserved - safety_stock. Sell only from available.
  3. Change stock only by writing a movement row: receipt, sale reservation, release, shipment, return, adjustment, or transfer. The current quantity is the sum of movements, or a cached total updated in the same transaction.
  4. Reserve at order creation with a conditional update, such as UPDATE stock SET reserved = reserved + :qty WHERE sku = :sku AND location = :loc AND on_hand - reserved - safety_stock >= :qty. Zero rows updated means not enough stock.
  5. Give each reservation an expiry. Release it when payment fails or the hold expires. Convert it to a shipment when the order ships.
  6. Allocate an order to locations with an explicit policy, then reserve at those locations. Store the allocation on the order lines.

Allocation

A common policy, in order:

  1. Only locations that can ship to the destination and are active.
  2. One location that can fill every line, the closest or cheapest first.
  3. Otherwise split by line across the fewest locations.
  4. Otherwise back-order or reject the line, as the store policy says. Never reserve more than available.

Run node scripts/allocate-stock.mjs order.json stock.json to test a policy on sample data. It returns the allocation per line and any shortfall, and never allocates more than a location has available.

Transfers

A transfer is two movements: out of the source when it ships, and into the destination when it is received. Stock in transit is in neither available figure. Record incoming at the destination if buyers need to see it.

Counts and adjustments

A stock count writes an adjustment movement for the difference, with a reason and the user. Do not overwrite on_hand directly, or the history and any reservations made during the count are lost.

Gotchas

  • Reading available stock and then writing it in a second statement oversells under load. Check and reserve in one conditional update or a row lock.
  • Decrementing on_hand at checkout and again at shipment counts the sale twice. Reserve at checkout, then move reserved to shipped.
  • A parent product's total is not a sellable quantity. Sell from the variation's row.
  • Safety stock that is not subtracted from available lets the last units sell online while the store floor still shows them.
  • Returns go back into stock only after inspection. Use a separate location or state such as quality_control until then.

Verification

  • Two concurrent checkouts for the last unit: one reserves, the other is refused.
  • An expired unpaid reservation returns its quantity to available.
  • An order for two lines that one warehouse can fill is not split.
  • A transfer in transit does not appear as available at either location.
  • The movement log sums to the current on_hand for every SKU and location.

Evaluation

Worked examples and the checks they must satisfy are in evals/tasks.json.

References

Platform notes

WooCommerce

WooCommerce core keeps one stock_quantity per product or variation, with manage_stock and stock_status. A variation with manage_stock set to parent uses the parent's quantity. Core has no locations. Multi-location stock comes from an extension or an external inventory system that writes the summed available back to WooCommerce.

  • Core reduces stock when an order is paid or moves to processing, completed, or on hold. During checkout it reserves stock for pending orders, and the Hold stock setting cancels unpaid pending orders after that many minutes.
  • When an external system is the source of truth, turn off core stock reduction for those products or the store will reduce twice.
  • Write stock through the REST API products/{id} or products/{id}/variations/{id} with stock_quantity, or the batch endpoint for many items.

Shopify

Shopify tracks quantities per inventory item per location with named states, including available, committed, incoming, on_hand, and reserved. Change them with the Admin GraphQL inventory mutations, such as inventoryAdjustQuantities for deltas, and give every change a reason. Fulfillment orders assign order lines to locations, and a merchant can move them.

Your own system

A minimal schema:

CREATE TABLE stock_levels (
  sku TEXT NOT NULL,
  location_id TEXT NOT NULL,
  on_hand INTEGER NOT NULL DEFAULT 0 CHECK (on_hand >= 0),
  reserved INTEGER NOT NULL DEFAULT 0 CHECK (reserved >= 0),
  safety_stock INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (sku, location_id)
);

CREATE TABLE stock_movements (
  id BIGSERIAL PRIMARY KEY,
  sku TEXT NOT NULL,
  location_id TEXT NOT NULL,
  kind TEXT NOT NULL,          -- receipt, reserve, release, ship, return, adjust, transfer_out, transfer_in
  quantity INTEGER NOT NULL,   -- signed
  reference TEXT,              -- order, transfer, or count id
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Update stock_levels and insert the movement in one transaction.

Evaluation

Fixtures from evals/tasks.json. See evaluation fixtures for how the checks work.

reserve-last-unit

Two shoppers check out the last unit of a SKU at the same time. Write the stock reservation so only one succeeds.

Must include: reserved, available, conditional update

Must not include: SELECT stock_quantity then UPDATE

Worked example

Keep on_hand and reserved per SKU and location, and compute available as on hand minus reserved minus safety stock.

Reserve with one conditional update: UPDATE stock_levels SET reserved = reserved + 1 WHERE sku = 'MUG-RED' AND location_id = 'main' AND on_hand - reserved - safety_stock >= 1. The first checkout updates one row and continues. The second updates zero rows and is told the item is out of stock. Insert a reserve movement in the same transaction, and release it if payment fails.

split-order

An order has two lines. Warehouse A has both items, warehouse B is closer but has only one. Decide the allocation and explain the policy.

Must include: one location, split, allocation

Must not include: always split

Worked example

The policy fills the order from one location when any eligible location can ship every line, because a split doubles shipping cost and parcels. Warehouse A can, so the allocation is both lines from A, even though B is closer.

Only when no single location can fill the order do we split, using the fewest locations and preferring ones already used. Store the allocation on each order line and reserve at A in the same transaction.