Enterprise Inventory Sync for Shopify: The Architecture That Prevents Overselling

37% of retailers cite inventory accuracy as their top operational challenge (Shopify Commerce Trends, 2024). At the enterprise level, inventory exists simultaneously in an ERP, WMS, POS, and 3PL. Each applies independent transactions. None wait for the others.
Here is the complete architecture that keeps all of them in sync with Shopify.

The Core Problem: Single-System Sync Is Always Wrong
Most integrations copy one system's quantity to Shopify. That number is wrong the moment any other system processes a transaction.
The correct available-to-sell formula:
ATS = ERP on-hand − WMS allocated − POS pending sales − open Shopify orders − safety buffer
Every enterprise inventory sync must compute this formula, not copy a single number.

1. ATS Computation Across All Source Systems

jsasync function computeAvailableToSell(shop, sku, locationId) {
  const erpOnHand = await erpClient.getOnHandQuantity({
    materialCode: await lookupERPCode(shop, sku),
    plant:        await lookupERPPlant(shop, locationId),
  });

  const wmsAllocated = await wmsClient.getAllocatedQuantity({
    itemCode:    await lookupWMSCode(shop, sku),
    warehouseId: await lookupWMSWarehouse(shop, locationId),
  });

  const posPendingSales = await posClient.getPendingSalesQty({
    sku, locationId,
    since: await getLastERPSyncTimestamp(shop, locationId),
  });

  const shopifyPending = await db.query(
    `SELECT COALESCE(SUM(quantity), 0) AS pending
     FROM pending_shopify_orders
     WHERE shop = $1 AND sku = $2 AND location_id = $3
       AND status IN ('paid', 'partially_fulfilled')`,
    [shop, sku, locationId]
  ).then(r => parseInt(r.rows[0].pending));

  const safetyBuffer = await getMerchantSafetyBuffer(shop, sku);
  const rawAts = Math.max(0, erpOnHand - wmsAllocated - posPendingSales - shopifyPending);
  const safeAts = Math.max(0, rawAts - safetyBuffer);

  return { erpOnHand, wmsAllocated, posPendingSales, shopifyPending, rawAts, safeAts };
}

Cache the result in Redis with a 30-second TTL. Multiple concurrent workers hitting the same SKU should share one computation, not each make four upstream API calls.

1. Conflict Resolution: Atomic Sequence Number Check Two source systems trigger inventory events for the same SKU within milliseconds. Two concurrent ATS computations. Two Shopify API calls. The last write wins — but it may be the stale one.

jsasync function conflictSafeInventoryUpdate(shop, sku, locationId, newAts) {
  const seqKey   = `inv:seq:${shop}:${sku}:${locationId}`;
  const writeSeq = Date.now();

  const luaScript = `
    local current = redis.call('GET', KEYS[1])
    if current and tonumber(current) >= tonumber(ARGV[1]) then
      return 0
    end
    redis.call('SET', KEYS[1], ARGV[1], 'EX', 300)
    return 1
  `;

  const canWrite = await redis.eval(luaScript, 1, seqKey, writeSeq);
  if (!canWrite) return { status: 'stale_write_discarded' };

  const mapping = await db.query(
    'SELECT shopify_inventory_item_id, shopify_location_id FROM inventory_map WHERE shop = $1 AND sku = $2 AND location_id = $3',
    [shop, sku, locationId]
  );

  await shopifyAdmin.post('/inventory_levels/set.json', {
    inventory_item_id: mapping.rows[0].shopify_inventory_item_id,
    location_id:       mapping.rows[0].shopify_location_id,
    available:         newAts,
  });

  return { status: 'updated', sku, newAts };
}

Atomic Lua script. No distributed locks. Stale writes are discarded at the middleware layer before they reach Shopify.

1. Soft Reservation: Close the Oversell Window An order is placed. The WMS has not confirmed pick allocation yet. That gap — seconds to minutes under load — is enough to oversell.

jsasync function applySoftReservation(shop, shopifyOrder) {
  for (const item of shopifyOrder.line_items) {
    const mapping = await db.query(
      `SELECT shopify_inventory_item_id, shopify_location_id
       FROM inventory_map WHERE shop = $1 AND shopify_variant_id = $2`,
      [shop, item.variant_id]
    );
    if (!mapping.rows.length) continue;

    const { shopify_inventory_item_id, shopify_location_id } = mapping.rows[0];

    // Adjust (not set) — decrements by ordered quantity immediately
    await shopifyAdmin.post('/inventory_levels/adjust.json', {
      inventory_item_id:    shopify_inventory_item_id,
      location_id:          shopify_location_id,
      available_adjustment: -item.quantity,
    });
  }

  await db.query(
    `INSERT INTO inventory_reservations (shop, shopify_order_id, expires_at)
     VALUES ($1, $2, NOW() + INTERVAL '24 hours')`,
    [shop, shopifyOrder.id]
  );
}

24-hour expiry auto-releases if the WMS never confirms. Release explicitly on WMS pick confirmation or order cancellation.

1. Watermark Delta Sync: Reconciliation Pass Event-driven push delivers freshness. It does not deliver completeness. Message queues partition. Source systems go down. Events get lost.

jsasync function runInventoryDeltaSync(shop, sourceSystem) {
  const watermarkKey = `inv:watermark:${shop}:${sourceSystem}`;
  const lastSyncedAt = await redis.get(watermarkKey) || '1970-01-01T00:00:00Z';

  const changedItems = await fetchChangedItems(sourceSystem, lastSyncedAt);
  if (!changedItems.length) {
    await redis.set(watermarkKey, new Date().toISOString());
    return { synced: 0 };
  }

  // Deduplicate: only most recent state per SKU-location pair
  const uniquePairs = new Map();
  for (const item of changedItems) {
    const key = `${item.sku}:${item.locationId}`;
    if (!uniquePairs.has(key) || item.timestamp > uniquePairs.get(key).timestamp) {
      uniquePairs.set(key, item);
    }
  }

  let synced = 0;
  for (const [, item] of uniquePairs) {
    const { safeAts } = await computeAvailableToSell(shop, item.sku, item.locationId);
    await conflictSafeInventoryUpdate(shop, item.sku, item.locationId, safeAts);
    synced++;
  }

  // Advance watermark AFTER successful sync — never before
  await redis.set(watermarkKey, new Date().toISOString());
  return { synced };
}

Deduplication by SKU-location pair reduces API calls by up to 90% on high-velocity items. Advancing the watermark after sync prevents permanently skipping missed events on restart.

1. Nightly Full Reconciliation

jsasync function runFullReconciliation(shop) {
  const mappings = await db.query(
    'SELECT sku, location_id, shopify_inventory_item_id, shopify_location_id FROM inventory_map WHERE shop = $1 AND is_active = TRUE',
    [shop]
  );

  const results = { checked: 0, corrected: 0, discrepancies: [] };

  for (const row of mappings.rows) {
    results.checked++;
    const shopifyLevel = await shopifyAdmin.get(
      `/inventory_levels.json?inventory_item_ids=${row.shopify_inventory_item_id}&location_ids=${row.shopify_location_id}`
    );
    const shopifyQty = shopifyLevel.inventory_levels[0]?.available ?? 0;
    const { safeAts } = await computeAvailableToSell(shop, row.sku, row.location_id);
    const discrepancy = Math.abs(shopifyQty - safeAts);
    const threshold   = await getReconciliationThreshold(shop, row.sku);

    if (discrepancy > threshold) {
      results.discrepancies.push({ sku: row.sku, shopifyQty, safeAts, discrepancy });
      await shopifyAdmin.post('/inventory_levels/set.json', {
        inventory_item_id: row.shopify_inventory_item_id,
        location_id:       row.shopify_location_id,
        available:         safeAts,
      });
      results.corrected++;
    }
  }

  await db.query(
    'INSERT INTO reconciliation_reports (shop, run_at, results) VALUES ($1, NOW(), $2)',
    [shop, JSON.stringify(results)]
  );

  return results;
}

Every correction is stored. Operations and finance teams need the audit trail. A recurring correction on the same SKU-location pair signals a systemic real-time pipeline failure for that item.

Sync Strategy Reference

Strategy	Latency	Purpose	Failure Risk
Event-driven push	Seconds	Real-time inventory movements	Low with idempotency
Watermark delta sync	15 minutes	Catch missed events	Low with watermark
Nightly reconciliation	Hours	Correct accumulated drift	Low — corrective only
Soft reservation	Seconds	Close the oversell window	Low with 24hr expiry
Full refresh sync	Minutes to hours	Legacy systems without events	High — overwrites all

Observability Checklist

Metric	Alert Threshold	What It Signals
ATS computation latency (p95)	Over 5 seconds for high-velocity SKUs	Upstream API degradation
Nightly discrepancy rate	Rising trend over 3 nights	Real-time pipeline missing events
Unmapped SKU event rate	Any value above 0%	Silent inventory movements
Safety buffer breach rate	Above 5% of SKUs	Buffer absorbing real oversell risk
Shopify API success rate per location	Below 99.9%	Location ID mapping error
Soft reservation release lag	Over 30 minutes	WMS confirmation delay

Full guide with multi-location mapping schema, complete real-time pipeline implementation, and location routing patterns:
https://kolachitech.com/enterprise-inventory-sync-shopify