Perishable-Goods Inventory and Location Tracking

0) Problem Restatement

Amazon asked: design a system that tracks inventory and location of perishable goods (milk, produce, medicine) across a distributed retail network: warehouses, trucks and stores. Unlike normal inventory, each unit has an expiry date, so the system must know how many of each product we have, where, and when each batch expires, send the soonest-expiring stock first, and alert before things spoil.

1) Requirements

• Receive stock in lots (batches) with an expiry date, quantity and location.
• Track movements: warehouse → truck → store shelf, sold, returned, discarded (expired or damaged).
• Real-time stock per product per location, with an expiry breakdown.
• Allocate orders and transfers FEFO (first-expiring, first-out).
• Alerts: "300 units at Store 12 expire within 2 days" (to discount or move them).
• Scanners at locations may be offline for a while.

2) Data Model

products:   sku, name, shelf_life_days, storage_temp_range
locations:  location_id, type (warehouse|truck|store), region
lots:       lot_id, sku, expiry_date, received_at, supplier
stock:      (location_id, lot_id) → quantity, version        -- current state
movements:  movement_id (UUID from scanner), lot_id, from_location, to_location, quantity,
            type (receive|transfer|sale|discard|adjust), ts, device_id        -- append-only log

The movements log is the source of truth (every change is an event), and stock is the current state derived from it (updated in the same transaction, or by a consumer).

3) Architecture

flowchart LR
    SC["Scanners / POS - offline capable"] -->|"movement events"| ING["Ingestion API"]
    ING --> K[("Kafka - movements by location")]
    K --> INV["Inventory service - update stock"]
    INV --> DB[("Stock + lots DB")]
    K --> EXP["Expiry monitor"]
    EXP --> AL["Alerts - discount / transfer"]
    OMS["Orders / replenishment"] --> ALLOC["FEFO allocator"]
    ALLOC --> DB
    DB --> DASH["Dashboards - stock by expiry"]

4) Key Flows

• Scan event: each movement has a client-generated UUID, so resending after an offline period is idempotent. The inventory service applies it: decrement the source location's lot quantity (never below 0; flag if it would be), and increment the destination.
• FEFO allocation: to fulfill an order at a location, pick lots ordered by expiry_date ascending (skipping lots that expire before the delivery date or below a minimum remaining shelf life), and reserve quantities with conditional updates (quantity >= requested).
• Expiry monitoring: a daily (or hourly) job scans lots by expiry date (indexed) and creates alerts per location: markdown pricing, transfer to a high-demand store, donate, or discard. Discards are recorded as movements.
• Reconciliation: periodic physical counts produce adjust movements, and big discrepancies are investigated (theft, scanning errors).

5) Scale and Reliability

• Partition movements by location, so events for one location stay in order.
• Offline scanners queue events locally with timestamps and sync later. The service orders by event time within the location.
• Cold chain: attach temperature sensor readings to trucks and lots. If the temperature goes out of range, mark affected lots as at-risk.
• Stock views per region are cached for dashboards, while allocation reads the strongly consistent DB.

6) Wrap-Up

Track stock by lot (with expiry) and location, with an append-only movements log as the source of truth and a current stock table updated idempotently from scanner events (UUIDs make offline resends safe). Allocate orders FEFO with conditional reservations, run expiry monitoring that triggers markdowns, transfers or discards before spoilage, reconcile with physical counts, and flag cold-chain temperature breaches on the affected lots.