Inventory and ERP sync

01Warehouses, stock levels and movements

A warehouse is any place that holds stock: a central store, a shop branch, a supplier's dropship stock. A stock level is the current quantity of one variant in one warehouse. The key idea is that you never just overwrite that number. Every change is a stock movement — a row saying "+20 received from purchase order 118", "−2 sold in order 5531", "−1 damaged", "+3 count correction". The stock level is the sum of movements, cached for speed. Like a bank statement, the movement history explains every number, lets you rebuild levels after a bug, and gives reports such as stock valuation, dead stock and sell-through. Stock counts (stocktakes) compare physical counts with the system and create correction movements for the difference.

1CREATE TABLE stock_movements (
2  id            BIGSERIAL PRIMARY KEY,
3  variant_id    BIGINT NOT NULL,
4  warehouse_id  BIGINT NOT NULL,
5  quantity      INTEGER NOT NULL,          -- positive in, negative out
6  reason        TEXT NOT NULL,             -- sale, receipt, transfer_out, count, waste
7  reference     TEXT,                      -- order or purchase order id
8  created_by    BIGINT,
9  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
10);
11 
12-- Rebuild or verify cached levels from history
13SELECT variant_id, warehouse_id, SUM(quantity) AS on_hand
14FROM stock_movements
15GROUP BY variant_id, warehouse_id;

02Available stock and reservations

"On hand" is what is physically in the warehouse. "Available" is what you can still promise: on hand minus reserved. A reservation is created when an order is placed and paid (or while payment is pending, with an expiry), and becomes a real outgoing movement when the order is picked and shipped. If the order is cancelled or the payment fails, the reservation is released. The storefront should show available stock, summed across the warehouses that can serve the customer. Fulfilment rules choose the source: the nearest branch, the main warehouse, a supplier who ships directly, or backorder (sell now, deliver when restocked). Make these rules explicit, because "which warehouse ships this?" affects delivery time, cost and accounting.

03Transfers, purchase orders and suppliers

A transfer moves stock between warehouses in two steps: an outgoing movement when it leaves, and an incoming one when it arrives. In between, the goods are "in transit" and belong to neither shelf — model that state, or stock seems to vanish. Purchase orders (POs) are how stock enters: you order from a supplier, the PO has expected quantities and costs, and goods receipts record what actually arrived, which may be partial or different. Each receipt creates incoming movements and updates cost price, which feeds margin and valuation reports. With good data you can generate reorder suggestions: average daily sales times supplier lead time plus safety stock, compared with available and incoming stock.

• Statuses for POs: draft → sent → partially received → received → closed.
• Record unit cost on receipt; margins depend on it.
• Track supplier performance: late deliveries and quantity differences.

04Syncing with accounting and ERP

An ERP (enterprise resource planning) or accounting system is the business's back office: products, prices, customers, invoices and stock for tax purposes. First decide ownership per data type. A common split: the ERP owns product codes, prices and official stock; the shop owns descriptions, images, SEO and online orders. Then sync in the direction of ownership. Most ERP APIs are slow or batch-oriented, so sync with scheduled jobs: pull changed prices and stock every few minutes, push new orders and returns as they happen through a queue, run a full comparison nightly. Make each job idempotent by matching on stable external IDs, process in batches, and record a sync log with counts and errors. Let admins switch individual jobs on and off from settings, which is invaluable during ERP maintenance.

1// Laravel scheduler: each sync is a separate, non-overlapping job
2use Illuminate\Support\Facades\Schedule;
3 
4Schedule::job(new SyncPricesFromErp)->everyFiveMinutes()
5    ->withoutOverlapping()->when(fn () => settings('erp.sync_prices'));
6 
7Schedule::job(new SyncStockFromErp)->everyTenMinutes()
8    ->withoutOverlapping()->when(fn () => settings('erp.sync_stock'));
9 
10Schedule::job(new CompareCatalogueWithErp)->dailyAt('03:00')
11    ->withoutOverlapping();
12 
13Schedule::job(new CheckErpHealth)->everyFiveMinutes(); // alerts admins on failures

05Conflicts, health checks and safety nets

Syncs fail in quiet ways: the ERP is down, a product code is renamed, a price import contains a zero. Protect yourself. Health checks track the last successful run per job and alert when it is too old or the error rate jumps. Sanity rules reject suspicious changes — a price dropping by 90%, all stock becoming zero — and hold them for review. Keep the previous values so an admin can revert a bad price import in one click. For conflicts, the owner wins: if both sides changed a field, take the value from the system that owns it and log the overwrite. A nightly comparison report lists products that exist on one side only or differ, so people fix data at the source instead of patching the shop.

06Fiscal receipts

In many countries, each sale must be registered with the tax authority through a fiscal device or cloud service, which returns a fiscal receipt number (often printed or shown as a QR code). Returns need their own fiscal return receipts linked to the original. Treat this like a payment integration: create a receipt record with a status, send it through a queue with retries, store the fiscal number on the order, and alert on failures. Retry only when it is safe — if the service may have registered the receipt before the timeout, check first to avoid duplicates. Branches or tills that can work offline need an offline mode that queues receipts and submits them when the connection returns, within the time the law allows.

07Consistency between movements and cached levels

Movements are the history and stock levels are a cache, so they must never drift. Write both in the same transaction: insert the movement and update the level with a conditional statement that cannot go negative unless backorders are allowed. Lock order matters when one operation touches several rows, such as a transfer or a multi-line order: always lock rows in a consistent order (for example by variant_id, then warehouse_id) to avoid deadlocks. Keep a nightly verification job that sums movements per variant and warehouse and compares them with the cached level; any difference is a bug to investigate, not something to silently fix. For high write volumes, movements can be partitioned by month, and old months summarised into opening balances.

1BEGIN;
2-- 1. Decrease the cached level only if enough is available
3UPDATE stock_levels
4SET on_hand = on_hand - 3
5WHERE variant_id = 42 AND warehouse_id = 1 AND on_hand - reserved >= 3;
6-- 0 rows → ROLLBACK and report out of stock
7 
8-- 2. Record why it changed
9INSERT INTO stock_movements (variant_id, warehouse_id, quantity, reason, reference)
10VALUES (42, 1, -3, 'sale', 'order:5531');
11COMMIT;

08Inventory valuation and cost methods

Stock is money on shelves, so finance asks what it is worth and what each sale cost. That depends on a cost method. Weighted average cost recalculates the unit cost on every receipt: (old quantity × old cost + received quantity × new cost) / total quantity. It is simple and common in ERPs. FIFO (first in, first out) assumes the oldest units are sold first, so each sale consumes cost layers from the oldest receipt; it needs per-receipt layers but reflects rising prices accurately. Whichever you choose, it must match the ERP or accounting system, otherwise margins in the shop and in the books disagree. Record the cost on the sale movement at the time of sale, just like price snapshots on orders, so later receipts do not rewrite historical margins.

• Weighted average: one cost per variant and warehouse, updated on receipt.
• FIFO: cost layers per receipt, consumed oldest first.
• Returns re-enter stock at the cost they left with, not today's cost.

09Integration patterns: polling, events and the outbox

There are two ways to learn about changes in another system: polling (ask "what changed since time T?" on a schedule) and events (the other system pushes webhooks or messages). Many ERPs only support polling. Poll with a watermark — the last updated timestamp or change ID you processed — stored per job, and overlap the window slightly to tolerate clock skew, relying on idempotent upserts to make re-reads harmless. For data you send, use the transactional outbox pattern: in the same database transaction as the order, insert a row into an outbox table; a worker reads the outbox and sends it to the ERP, retrying until acknowledged. This guarantees that a committed order is eventually exported, and that a rolled-back order never is, without distributed transactions.

1CREATE TABLE outbox (
2  id           BIGSERIAL PRIMARY KEY,
3  topic        TEXT NOT NULL,        -- erp.order_created, erp.return_created
4  payload      JSONB NOT NULL,
5  attempts     INT NOT NULL DEFAULT 0,
6  sent_at      TIMESTAMPTZ,
7  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
8);
9 
10-- Worker: claim a batch without blocking other workers
11SELECT id, topic, payload FROM outbox
12WHERE sent_at IS NULL
13ORDER BY id
14LIMIT 50
15FOR UPDATE SKIP LOCKED;

10Multi-warehouse allocation and split shipments

With several warehouses, every order poses a small optimisation problem: which locations should fulfil which lines? Common rules, in priority order: ship everything from one location if possible (one parcel is cheaper), prefer the location closest to the customer, avoid draining a branch's display stock, and use supplier dropship or backorder only as a last resort. Write the rules as a scoring function and record the decision on the order, so warehouse staff and support can see why a location was chosen. Split shipments need their own records: one order, several shipments, each with lines, tracking and status, and the order becomes delivered only when all shipments are. Reservations should be made per warehouse at checkout, not later, otherwise the allocation can fail after the customer has paid.