Multi-Channel Inventory Management System

1. Serialization by SKU, not globally#

The problem with a single queue is that you create an artificial bottleneck. The solution is to use SQS FIFO queues with MessageGroupId = SKU.

SQS FIFO guarantees that messages with the same MessageGroupId are processed in order and one at a time, while messages with a different MessageGroupId are processed in parallel. This gives you exactly what you need: serialization within the same SKU and full concurrency across different SKUs.

// When confirming the purchase

$this->messageBus->dispatch(new ConfirmStockReservation(
    sku: 'SKU-4532',
    reservationId: $reservationId,
    quantity: 2,
    channel: 'web'
), [
    new AmazonSqsFifoStamp('SKU-4532'), // MessageGroupId
    new AmazonSqsDeduplicationStamp($reservationId), // prevents duplicates
]);

With this, 500 purchases involving 500 different SKUs can be processed in parallel. Three purchases for the same SKU are serialized. You no longer need SELECT FOR UPDATE inside the worker because SQS FIFO already provides mutual exclusion per SKU.

---

2. Robust Reservation Model — Not Just a Cache#

The reservation cannot live only in Redis. You need dual persistence:

stock_reservations (MySQL)

─────────────────────────────

id              CHAR(36) PK      -- UUID
sku             VARCHAR(32)
channel         VARCHAR(16)
quantity        INT
status          ENUM('active','confirmed','expired','cancelled')
expires_at      DATETIME
created_at      DATETIME

The flow would be:

When reserving: a single atomic MySQL transaction does two things — inserts the reservation and decrements available stock. Redis is invalidated afterwards. There is no window where Redis and MySQL disagree about availability.

// Inside a transaction

$this->connection->beginTransaction();

$affected = $this->connection->executeUpdate(
    'UPDATE stock SET available = available - :qty
     WHERE sku = :sku AND available >= :qty',
    ['sku' => $sku, 'qty' => $quantity]
);

if ($affected === 0) {
    $this->connection->rollBack();
    throw new InsufficientStockException($sku);
}

$this->connection->insert('stock_reservations', [
    'id' => $reservationId,
    'sku' => $sku,
    'quantity' => $quantity,
    'status' => 'active',
    'expires_at' => new \DateTimeImmutable('+15 minutes'),
    // ...
]);

$this->connection->commit();

$this->cache->delete("stock:{$sku}");

When the TTL expires: a cron job (every minute is sufficient) looks for expired reservations, returns the stock, and marks them as expired. This also happens inside an atomic transaction, so if it fails there is no inconsistency — it simply retries during the next cycle.

If Redis goes down: the system continues to work because MySQL is the source of truth. Reads become slower (cache miss → direct query), but you don't lose reservations or oversell.

---

3. Traceability — Lightweight Event Sourcing#

To know why stock has its current value, you need an immutable movement log. Full Event Sourcing (with projections and all the associated complexity) isn't necessary; a movement ledger is enough:

stock_movements (MySQL)

───────────────────────

id              BIGINT AUTO_INCREMENT PK

sku             VARCHAR(32)

type            ENUM('purchase_order','sale','return','reservation',
                     'reservation_expired','adjustment','transfer')

quantity        INT          -- positive = inbound, negative = outbound

reference_id    VARCHAR(64)  -- sale, return, PO ID...

channel         VARCHAR(16)

created_at      DATETIME

created_by      VARCHAR(64)  -- user or system

The golden rule is that you never perform a direct UPDATE on the available field. Every stock operation inserts a movement, and current stock can be recalculated by summing the movements. In practice, you keep a denormalized available field for performance, but you always update it within the same transaction as the movement INSERT.

This gives you two powerful capabilities. First, auditing: when the warehouse says "there are 43 units here but the system says 47," you can run SELECT * FROM stock_movements WHERE sku = 'X' ORDER BY created_at and see exactly where the discrepancy occurred. Second, reconciliation: you can verify at any time that SUM(quantity) FROM stock_movements WHERE sku = 'X' matches the available field in the stock table. If they don't match, you have a bug and can detect it immediately.

The 8% discrepancies reported by the warehouse are probably caused by the fact that the system currently performs direct UPDATEs without leaving an audit trail, so nobody knows whether the problem is a bug, theft, or a receiving/counting error.

---

4. Returns in < 5 Minutes#

The return flow goes through the same messaging system but with its own queue (or MessageGroupId):

Return received (warehouse/POS/web)

  → Event: ReturnReceived { sku, qty, orderId, channel }

  → SQS FIFO queue (GroupId = SKU)

  → Worker:

      1. INSERT stock_movement (type='return', qty=+N)

      2. UPDATE stock SET available = available + N

      3. Invalidate Redis cache

      4. Publish StockUpdated event → marketplace sync queue

Since the worker already exists and processes messages within seconds, the bottleneck is reduced to how long it takes the warehouse to register the return in the system.

If they use a barcode scanner that triggers the event, you're comfortably below the 5-minute target. The current 30-minute problem is caused by a manual process involving human intervention — so the solution is not purely technical; it also requires an operational workflow change.

---

5. Reads in < 200ms#

Your intuition about using Redis was correct, but with some nuances:

• Cache-aside per SKU with a generous TTL (1h, not 24h — with 8,000 SKUs and frequently changing stock, 24h is too aggressive and risks stale reads).
• Active invalidation on every stock movement (already implicit in the flows above).
• MySQL fallback with an index on stock(sku) that responds in < 5ms for a single primary-key lookup. You meet the < 200ms requirement even without caching for simple reads; the cache protects you from high concurrent volume during Black Friday, not from individual query latency.

For the marketplace with Amazon's < 500ms SLA, the endpoint doesn't even touch MySQL in the happy path — it reads directly from Redis.

---

Full Flow Overview#

                    ┌──────────┐
                    │ Channels │
                    │ Web/App/ │
                    │ POS/Mktpl│
                    └────┬─────┘
                         │
                    ┌────▼─────┐
                    │ GET /stock/{sku}
                    │ (Redis → MySQL fallback)
                    │ < 200ms
                    └────┬─────┘
                         │
                    ┌────▼─────┐
                    │ POST /reserve
                    │ Atomic MySQL Tx:
                    │ - Decrement available
                    │ - Insert reservation
                    │ - Insert movement
                    │ - Invalidate Redis
                    └────┬─────┘
                         │
              ┌──────────▼──────────┐
              │  SQS FIFO           │
              │  GroupId = SKU      │
              │  ConfirmReservation │
              └──────────┬──────────┘
                         │
              ┌──────────▼──────────┐
              │ Worker: confirms    │
              │ or rejects purchase │
              │ + movement log      │
              └──────────┬──────────┘
                         │
              ┌──────────▼──────────┐
              │ Outbox → SQS        │
              │ → Python Worker     │
              │ → Marketplace Sync  │
              └─────────────────────┘