wms-inventory-warehouse-control

Warehouse Management & Inventory Control Skill Guide

This skill governs data modeling, stock movements, and operational workflows for high-reliability Warehouse Management Systems (WMS).

1. Physical Warehouse Hierarchy & Stock Movements

[ Warehouse Facility ]
         |
         +---> [ Zone: Ambient / Cold Storage / Hazardous ]
                 |
                 +---> [ Aisle ]
                         |
                         +---> [ Rack / Shelf ]
                                 |
                                 +---> [ Bin: Specific Pick Location (e.g. A-04-02-B) ]
                                         |
                                         v
                      [ Inventory Lot (SKU + Lot # + Expiry Date) ]

2. Production Database Schema & Ledger Architecture

Inventory must never be managed by simply mutating an integer count; all stock changes must use an immutable transaction ledger (Stock Move Log).

-- 1. Locations Hierarchy
CREATE TABLE warehouse_locations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    warehouse_code VARCHAR(16) NOT NULL,
    zone VARCHAR(32) NOT NULL,
    aisle VARCHAR(16) NOT NULL,
    rack VARCHAR(16) NOT NULL,
    bin VARCHAR(16) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    CONSTRAINT uq_bin_code UNIQUE (warehouse_code, zone, aisle, rack, bin)
);

-- 2. Inventory Batches / Lots (Supports FEFO: First Expired, First Out)
CREATE TABLE inventory_lots (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku VARCHAR(64) NOT NULL,
    lot_number VARCHAR(64) NOT NULL,
    manufactured_at DATE,
    expires_at DATE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);

-- 3. Immutable Stock Movement Ledger
CREATE TYPE movement_type AS ENUM ('RECEIPT', 'PUTAWAY', 'PICK', 'TRANSFER', 'CYCLE_COUNT_ADJUSTMENT');

CREATE TABLE stock_movements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    lot_id UUID NOT NULL REFERENCES inventory_lots(id),
    from_location_id UUID REFERENCES warehouse_locations(id), -- NULL for inbound receipts
    to_location_id UUID REFERENCES warehouse_locations(id),   -- NULL for shipped orders
    quantity NUMERIC(12, 4) NOT NULL CHECK (quantity > 0),
    move_type movement_type NOT NULL,
    reference_order_id VARCHAR(64),
    performed_by_user UUID NOT NULL,
    recorded_at TIMESTAMPTZ DEFAULT clock_timestamp()
);

CREATE INDEX idx_stock_movements_lot ON stock_movements(lot_id);
CREATE INDEX idx_stock_movements_to ON stock_movements(to_location_id);
CREATE INDEX idx_stock_movements_from ON stock_movements(from_location_id);