Enterprise Inventory Sync for Shopify: The Architecture That Prevents Overselling

safetyBuffer] = await Promise.all([ this.adapters.erp.getOnHand(shopId, sku, locationId), this.adapters.wms.getAllocated(shopId, sku, locationId), this.adapters.pos.getPendingSales(shopId, sku, locationId), this.adapters.shopify.getOpenOrders(shopId, sku, locationId), this.adapters.config.getSafetyBuffer(shopId, sku), ]);

return { erpOnHand, wmsAllocated, posPending, shopifyOpen, safetyBuffer };

} }

**Architecture Decision:** The calculation fetches all components in parallel using `Promise.all`. This minimizes latency. The 30-second cache TTL ensures that concurrent requests for the same SKU share a single computation, reducing upstream load by orders of magnitude during traffic spikes.

#### 2. Atomic Conflict Resolution

Concurrent updates from multiple sources can cause race conditions. The atomic writer uses a Redis Lua script to enforce a sequence-based ordering, ensuring stale writes are discarded before reaching Shopify.

```typescript
import { RedisClient } from './redis-client';
import { ShopifyClient } from './shopify-client';

export class AtomicInventoryWriter {
  private redis: RedisClient;
  private shopify: ShopifyClient;

  constructor(redis: RedisClient, shopify: ShopifyClient) {
    this.redis = redis;
    this.shopify = shopify;
  }

  async commitUpdate(
    shopId: string,
    sku: string,
    locationId: string,
    quantity: number,
    sequence: number
  ): Promise<{ success: boolean; reason?: string }> {
    const metaKey = `inv:meta:${shopId}:${sku}:${locationId}`;
    
    // Lua script ensures atomic check-and-set of sequence
    const luaScript = `
      local currentSeq = tonumber(redis.call('HGET', KEYS[1], 'seq') or '0')
      if currentSeq >= tonumber(ARGV[1]) then
        return 0
      end
      redis.call('HSET', KEYS[1], 'seq', ARGV[1], 'qty', ARGV[2])
      redis.call('EXPIRE', KEYS[1], 300)
      return 1
    `;

    const result = await this.redis.eval(
      luaScript,
      1,
      metaKey,
      sequence.toString(),
      quantity.toString()
    );

    if (result === 0) {
      return { success: false, reason: 'stale_sequence' };
    }

    // Map internal SKU to Shopify IDs
    const mapping = await this.getShopifyMapping(shopId, sku, locationId);
    
    await this.shopify.inventory.setQuantity({
      inventoryItemId: mapping.inventoryItemId,
      locationId: mapping.locationId,
      quantity: quantity,
    });

    return { success: true };
  }

  private async getShopifyMapping(
    shopId: string,
    sku: string,
    locationId: string
  ) {
    // Implementation depends on your mapping store
    // Returns { inventoryItemId: string, locationId: string }
    throw new Error('Mapping lookup not implemented');
  }
}

Architecture Decision: Using a Redis Hash (HSET) allows storing metadata alongside the sequence number. The Lua script executes atomically, preventing distributed lock overhead. If the incoming sequence is older than the stored sequence, the write is rejected, preserving the integrity of the latest state.

3. Soft Reservation Engine

To prevent oversells during the gap between order placement and warehouse confirmation, the system applies an immediate soft reservation. This decrements the Shopify quantity without requiring WMS confirmation.

import { ShopifyClient } from './shopify-client';
import { Database } from './database';

interface ReservationRecord {
  orderId: string;
  sku: string;
  quantity: number;
  expiresAt: Date;
}

export class SoftReservationEngine {
  private shopify: ShopifyClient;
  private db: Database;
  private reservationTtlMs: number;

  constructor(shopify: ShopifyClient, db: Database) {
    this.shopify = shopify;
    this.db = db;
    this.reservationTtlMs = 24 * 60 * 60 * 1000; // 24 hours
  }

  async applyReservations(order: { id: string; lineItems: Array<{ sku: string; qty: number }> }) {
    const reservations: ReservationRecord[] = [];

    for (const item of order.lineItems) {
      const mapping = await this.getShopifyMapping(item.sku);
      
      // Use adjust API to decrement immediately
      await this.shopify.inventory.adjustQuantity({
        inventoryItemId: mapping.inventoryItemId,
        locationId: mapping.locationId,
        delta: -item.qty,
      });

      reservations.push({
        orderId: order.id,
        sku: item.sku,
        quantity: item.qty,
        expiresAt: new Date(Date.now() + this.reservationTtlMs),
      });
    }

    // Persist reservations for tracking and expiry
    await this.db.reservations.bulkInsert(reservations);
  }

  async releaseReservations(orderId: string) {
    const reservations = await this.db.reservations.findByOrderId(orderId);
    
    for (const res of reservations) {
      const mapping = await this.getShopifyMapping(res.sku);
      
      // Re-increment quantity
      await this.shopify.inventory.adjustQuantity({
        inventoryItemId: mapping.inventoryItemId,
        locationId: mapping.locationId,
        delta: res.quantity,
      });
    }

    await this.db.reservations.deleteByOrderId(orderId);
  }
}

Architecture Decision: The adjust API is preferred over set for reservations because it is relative and safer against concurrent modifications. Reservations have a 24-hour TTL; if the WMS does not confirm within this window, a background job releases the stock, preventing permanent inventory lockup.

4. Delta Sync and Reconciliation Pipeline

Event-driven updates provide freshness but lack completeness. A delta sync with watermarks catches missed events, while nightly reconciliation corrects accumulated drift.

import { RedisClient } from './redis-client';
import { SourceAdapters } from './adapters';
import { InventoryAggregator } from './aggregator';
import { AtomicInventoryWriter } from './writer';

export class DeltaSyncPipeline {
  private redis: RedisClient;
  private adapters: SourceAdapters;
  private aggregator: InventoryAggregator;
  private writer: AtomicInventoryWriter;

  constructor(
    redis: RedisClient,
    adapters: SourceAdapters,
    aggregator: InventoryAggregator,
    writer: AtomicInventoryWriter
  ) {
    this.redis = redis;
    this.adapters = adapters;
    this.aggregator = aggregator;
    this.writer = writer;
  }

  async runDeltaSync(sourceSystem: string) {
    const watermarkKey = `sync:watermark:${sourceSystem}`;
    const lastSync = await this.redis.get(watermarkKey) || '1970-01-01T00:00:00Z';

    // Fetch changes since last watermark
    const changes = await this.adapters[sourceSystem].getChangesSince(lastSync);
    
    // Deduplicate by SKU-location to minimize API calls
    const uniqueUpdates = new Map<string, any>();
    for (const change of changes) {
      const key = `${change.sku}:${change.locationId}`;
      // Keep only the latest change per pair
      if (!uniqueUpdates.has(key) || change.timestamp > uniqueUpdates.get(key).timestamp) {
        uniqueUpdates.set(key, change);
      }
    }

    let processed = 0;
    for (const [, change] of uniqueUpdates) {
      const netSellable = await this.aggregator.calculateNetSellable(
        change.shopId,
        change.sku,
        change.locationId
      );
      
      await this.writer.commitUpdate(
        change.shopId,
        change.sku,
        change.locationId,
        netSellable,
        change.timestamp
      );
      processed++;
    }

    // Advance watermark only after successful processing
    await this.redis.set(watermarkKey, new Date().toISOString());
    
    return { processed, sourceSystem };
  }
}

Architecture Decision: The watermark is advanced only after all updates are successfully committed. This prevents skipping events if the process crashes mid-batch. Deduplication reduces API calls by up to 90% for high-velocity items, as multiple changes to the same SKU within the window are collapsed into a single computation.

Pitfall Guide

1. The Phantom Allocation Trap

   • Explanation: Failing to subtract WMS allocated quantities from the ATS calculation. This causes Shopify to show stock that is already picked and packed in the warehouse.
   • Fix: Ensure the aggregation formula always includes wmsAllocated. Verify that the WMS adapter returns real-time allocation data, not just on-hand.

2. Watermark Drift

   • Explanation: Advancing the watermark before processing completes. If the sync fails, events are permanently skipped, leading to inventory drift.
   • Fix: Always advance the watermark after the batch is fully processed. Implement a checkpoint mechanism that allows resuming from the last successful item.

3. Redis Key Explosion

   • Explanation: Creating unique Redis keys for every SKU-location pair without a cleanup strategy can exhaust memory in large catalogs.
   • Fix: Use a consistent key naming convention and set appropriate TTLs. Monitor key count and memory usage. Consider using Redis Hashes to group metadata.

4. Hard Reservation Deadlocks

   • Explanation: Using hard reservations that never expire. If the WMS fails to confirm, inventory remains locked indefinitely.
   • Fix: Implement soft reservations with automatic expiry. Use a background job to scan for expired reservations and release stock.

5. Ignoring Safety Buffers in Calculation

   • Explanation: Calculating ATS without applying safety buffers. This exposes the store to oversells due to timing discrepancies or measurement errors.
   • Fix: Include safetyBuffer in the ATS formula. Configure buffers dynamically based on SKU velocity and lead time variability.

6. Race Conditions in Adjustment API

   • Explanation: Using the adjust API without idempotency keys can lead to double adjustments if the request is retried.
   • Fix: Always include an idempotency key in Shopify API calls. Store the key in the database and check before retrying.

7. Missing Audit Trails

   • Explanation: Failing to log inventory corrections makes it impossible to diagnose recurring discrepancies.
   • Fix: Log every inventory update, including the source, computed value, and discrepancy amount. Use these logs to trigger alerts for systemic issues.

Production Bundle

Action Checklist

• Define the ATS formula for each SKU class, including safety buffer rules.
• Implement the InventoryAggregator with parallel source fetching and caching.
• Deploy the AtomicInventoryWriter with Lua-based sequence checks.
• Configure the SoftReservationEngine with 24-hour expiry and auto-release.
• Set up the DeltaSyncPipeline with watermark management and deduplication.
• Schedule nightly reconciliation jobs with discrepancy thresholds.
• Implement observability metrics for ATS latency, discrepancy rates, and API success.
• Test failover scenarios, including source system downtime and Redis outages.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High-Velocity SKUs	ATS Aggregation + Soft Reservation	Minimizes oversell risk during traffic spikes.	Higher API usage; requires robust caching.
Low-Velocity SKUs	Nightly Reconciliation	Reduces operational overhead for stable items.	Lower API load; acceptable drift risk.
Legacy ERP Integration	Full Refresh Sync	Simplifies integration when events are unavailable.	High risk of overwrites; requires careful scheduling.
Multi-Location Fulfillment	Location-Specific ATS	Ensures accurate stock per fulfillment center.	Increased complexity; requires precise mapping.

Configuration Template

inventory_sync:
  aggregation:
    cache_ttl_seconds: 30
    parallel_fetch_timeout_ms: 5000
  reservations:
    ttl_hours: 24
    auto_release_enabled: true
  delta_sync:
    interval_minutes: 15
    batch_size: 100
    deduplication: true
  reconciliation:
    schedule: "0 2 * * *" # Daily at 2 AM
    discrepancy_threshold: 5
    alert_on_breach: true
  safety_buffers:
    strategy: dynamic # static, dynamic, velocity_based
    default_percentage: 10

Quick Start Guide

1. Map Your Inventory: Create a mapping table linking internal SKUs and locations to Shopify inventory item IDs and location IDs. Ensure all mappings are validated.
2. Deploy the Aggregator: Implement the InventoryAggregator class and configure it to fetch data from your ERP, WMS, POS, and Shopify. Set up Redis caching.
3. Enable Reservations: Integrate the SoftReservationEngine into your order processing workflow. Ensure reservations are applied immediately upon order creation and released on confirmation or expiry.
4. Run Delta Sync: Configure the DeltaSyncPipeline to run periodically. Verify watermarks are advancing and deduplication is reducing API calls.
5. Schedule Reconciliation: Set up the nightly reconciliation job. Monitor the discrepancy reports and adjust thresholds based on your tolerance for drift.

This architecture provides a robust foundation for enterprise inventory consistency. By treating inventory as a computed state and enforcing atomic updates, you eliminate oversells and ensure accurate stock visibility across all channels.