Inventory DB (v0.0.1)

Authoritative database for product inventory levels, warehouse stock, and inventory movements

Database postgres@14 Residency: eu-west-1 Retention: 5y Access Mode: readWrite

What is this?

Inventory DB is the system of record for real-time inventory tracking across multiple warehouses and fulfillment centers. It maintains accurate stock levels, handles inventory reservations, and tracks all inventory movements (receipts, shipments, adjustments).

What does it store?

  • Inventory Levels: Current stock quantities by product and warehouse
  • Inventory Reservations: Temporary holds on inventory for pending orders
  • Stock Movements: Audit trail of all inventory transactions (in, out, adjustments)
  • Reorder Points: Low-stock thresholds and automatic reorder triggers
  • Warehouse Locations: Bin/shelf locations for physical inventory management

Who writes to it?

  • InventoryService manages stock levels, reservations, and adjustments
  • OrdersService creates reservations when orders are placed
  • Warehouse Management System updates physical inventory counts
  • Receiving System adds stock when shipments arrive

Who reads from it?

  • OrdersService checks stock availability before order confirmation
  • Inventory Service monitors low-stock alerts and reorder points
  • Analytics tracks inventory turnover and stock-out rates
  • Warehouse Operations for pick/pack workflows
  • Finance for inventory valuation reports

High-level data model

  • inventory_levels: Current quantities by product and warehouse (frequently updated)
  • inventory_reservations: Temporary allocations for pending orders (auto-expire)
  • stock_movements: Immutable log of all inventory changes
  • reorder_rules: Automated replenishment configuration

Common queries

-- Check available inventory for a product (excluding reservations)
SELECT
  il.warehouse_id,
  il.quantity_on_hand,
  COALESCE(SUM(ir.quantity), 0) AS quantity_reserved,
  il.quantity_on_hand - COALESCE(SUM(ir.quantity), 0) AS quantity_available
FROM inventory_levels il
LEFT JOIN inventory_reservations ir
  ON ir.product_id = il.product_id
  AND ir.warehouse_id = il.warehouse_id
  AND ir.expires_at > NOW()
WHERE il.product_id = $1
GROUP BY il.warehouse_id, il.quantity_on_hand;


-- Find low-stock items needing reorder
SELECT
  il.product_id,
  il.warehouse_id,
  il.quantity_on_hand,
  rr.reorder_point,
  rr.reorder_quantity
FROM inventory_levels il
JOIN reorder_rules rr
  ON rr.product_id = il.product_id
  AND rr.warehouse_id = il.warehouse_id
WHERE il.quantity_on_hand <= rr.reorder_point
  AND rr.enabled = true;


-- Track inventory movements for audit (last 30 days)
SELECT
  sm.movement_id,
  sm.product_id,
  sm.warehouse_id,
  sm.movement_type, -- 'RECEIPT', 'SHIPMENT', 'ADJUSTMENT', 'RETURN'
  sm.quantity_change,
  sm.reason,
  sm.created_at,
  sm.created_by
FROM stock_movements sm
WHERE sm.product_id = $1
  AND sm.created_at >= NOW() - INTERVAL '30 days'
ORDER BY sm.created_at DESC;


-- Calculate inventory turnover rate
SELECT
  p.product_id,
  p.product_name,
  SUM(CASE WHEN sm.movement_type = 'SHIPMENT' THEN ABS(sm.quantity_change) ELSE 0 END) AS units_sold_30d,
  AVG(il.quantity_on_hand) AS avg_inventory_level,
  (SUM(CASE WHEN sm.movement_type = 'SHIPMENT' THEN ABS(sm.quantity_change) ELSE 0 END) /
   NULLIF(AVG(il.quantity_on_hand), 0)) AS turnover_ratio
FROM stock_movements sm
JOIN inventory_levels il USING (product_id, warehouse_id)
JOIN products p USING (product_id)
WHERE sm.created_at >= NOW() - INTERVAL '30 days'
GROUP BY p.product_id, p.product_name
ORDER BY turnover_ratio DESC;

Inventory reservation flow

  1. Customer adds item to cart → soft reservation (not written to DB yet)
  2. Customer proceeds to checkout → OrdersService creates reservation with 15-minute expiry
  3. Payment successful → reservation converted to stock movement (SHIPMENT type)
  4. Payment failed or timeout → reservation auto-expires, stock released
  5. Order cancelled → reservation deleted, stock released immediately

Access patterns and guidance

  • Use indexed lookups by product_id and warehouse_id
  • Reservations have TTL; expired reservations cleaned up hourly
  • Stock movements are append-only for audit compliance
  • Use pessimistic locking for concurrent inventory updates
  • Read replicas for analytics to avoid impacting operational queries

Concurrency and consistency

  • Row-level locking: SELECT FOR UPDATE on inventory_levels during reservations
  • Atomic updates: All inventory changes in transactions (reserve + deduct + log)
  • Idempotency: Movement records include idempotency keys to prevent duplicates
  • Eventual consistency: Read model (inventory-readmodel container) synced asynchronously

Security and compliance

  • Inventory adjustments logged with user identity for audit trail
  • Role-based access: warehouse staff vs. system services
  • Financial impact tracked for high-value inventory movements
  • Historical data retained for 5 years (tax/audit requirements)

Requesting access

To request access to Inventory DB:

  1. Read-only access (for reporting):

    • Submit request via ServiceNow
    • Select “Database Access” → “Inventory DB (Read-Only)”
    • Approval from inventory team lead
    • Access granted within 24 hours

  2. Write access (for services):

    • Restricted to InventoryService and authorized systems only
    • New service integration requires architecture review
    • Contact #inventory-team for integration onboarding

  3. Warehouse operations access:

    • Access via Warehouse Management System only (no direct DB access)
    • Contact #warehouse-operations for WMS training

Contact:

Monitoring and alerts

  • Stock-out alerts (inventory level = 0 for critical products)
  • Negative inventory alerts (data integrity issue)
  • Reservation expiry rate (high rate indicates checkout abandonment)
  • Database lock contention (alert if wait time > 100ms)
  • Replication lag (alert at > 5 seconds)

Backup and disaster recovery

  • Continuous backups with 5-minute RPO
  • Point-in-time recovery window: 35 days
  • Cross-region backup replication for DR
  • Daily backup validation

Performance characteristics

  • Read latency: p99 < 20ms
  • Write latency: p99 < 50ms (includes locking)
  • Reservation throughput: 1,000+ reservations/second
  • Concurrent updates: Supports high concurrency with row-level locking

Local development

  • Connection string: INVENTORY_DB_URL environment variable
  • Docker setup: docker-compose up inventory-db
  • Seed data: npm run seed:inventory
  • Test data includes multiple warehouses and stock levels

Common issues and troubleshooting

  • Negative inventory: Check for missing rollback on failed reservations
  • Stuck reservations: Run cleanup job for expired reservations
  • Lock contention: Reduce transaction duration, consider optimistic locking
  • Inventory drift: Reconcile with physical counts, investigate stock movement gaps
  • Replication lag: Check network latency, increase replication capacity

Integration with read model

Inventory DB is the write-side (source of truth). The inventory-readmodel container provides optimized read queries:

  • Real-time sync via change data capture (CDC)
  • Denormalized views for fast lookups
  • Eventually consistent (typically < 1 second lag)
  • Used by high-traffic read operations (product pages, search)

For more information, see InventoryService documentation and Inventory Management Playbook.

Edit this page