Multi-Location Inventory Data Model: The Complete Engineering Reference

CREATE TABLE locations (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          VARCHAR(200) NOT NULL,
  code          VARCHAR(20) UNIQUE NOT NULL,  -- e.g., "WH-PUNE-01"
  type          VARCHAR(20) NOT NULL CHECK (type IN ('warehouse','depot','vehicle','transit','customer')),
  parent_id     UUID REFERENCES locations(id),  -- for hierarchy
  address       JSONB,  -- {street, city, state, pincode, lat, lng}
  is_active     BOOLEAN DEFAULT true,
  created_at    TIMESTAMPTZ DEFAULT now()
);

-- Index for hierarchy queries
CREATE INDEX idx_locations_parent ON locations(parent_id);

CREATE TABLE skus (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code            VARCHAR(50) UNIQUE NOT NULL,
  name            VARCHAR(200) NOT NULL,
  category        VARCHAR(100),
  unit_of_measure VARCHAR(20) NOT NULL,  -- 'pcs', 'kg', 'litre', 'box'
  barcode         VARCHAR(100),
  tracking_type   VARCHAR(20) DEFAULT 'none' CHECK (tracking_type IN ('none','batch','serial')),
  reorder_point   DECIMAL(10,3) DEFAULT 0,
  reorder_qty     DECIMAL(10,3) DEFAULT 0,
  is_active       BOOLEAN DEFAULT true,
  attributes      JSONB,  -- flexible product attributes
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_skus_barcode ON skus(barcode);
CREATE INDEX idx_skus_category ON skus(category);

CREATE TABLE stock_ledger (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sku_id          UUID NOT NULL REFERENCES skus(id),
  location_id     UUID NOT NULL REFERENCES locations(id),
  movement_type   VARCHAR(30) NOT NULL CHECK (movement_type IN (
                    'RECEIPT',        -- goods received from supplier
                    'DISPATCH',       -- goods dispatched to customer
                    'TRANSFER_OUT',   -- goods leaving a location in a transfer
                    'TRANSFER_IN',    -- goods arriving at a location in a transfer
                    'ADJUSTMENT_IN',  -- positive stock adjustment
                    'ADJUSTMENT_OUT', -- negative stock adjustment
                    'RETURN_IN',      -- customer return received
                    'RETURN_OUT'      -- goods returned to supplier
                  )),
  quantity        DECIMAL(10,3) NOT NULL,  -- positive = inbound, negative = outbound
  reference_type  VARCHAR(30),  -- 'purchase_order', 'sales_order', 'transfer_order', 'adjustment'
  reference_id    UUID,         -- FK to the relevant document
  batch_no        VARCHAR(100), -- for batch-tracked SKUs
  serial_no       VARCHAR(100), -- for serial-tracked SKUs
  unit_cost       DECIMAL(12,2),-- cost per unit at time of movement
  notes           TEXT,
  created_at      TIMESTAMPTZ DEFAULT now(),
  created_by      UUID NOT NULL  -- FK to users
);

-- Critical indexes for performance
CREATE INDEX idx_ledger_sku_location ON stock_ledger(sku_id, location_id);
CREATE INDEX idx_ledger_created_at ON stock_ledger(created_at DESC);
CREATE INDEX idx_ledger_reference ON stock_ledger(reference_type, reference_id);

CREATE MATERIALIZED VIEW current_stock AS
SELECT
  sku_id,
  location_id,
  SUM(quantity) AS quantity,
  SUM(quantity * COALESCE(unit_cost, 0)) / NULLIF(SUM(quantity), 0) AS avg_cost
FROM stock_ledger
GROUP BY sku_id, location_id
HAVING SUM(quantity) != 0;

CREATE UNIQUE INDEX idx_current_stock_pk ON current_stock(sku_id, location_id);

-- Refresh strategy: refresh after each batch of movements, or on a schedule
-- For real-time: use a trigger-maintained table instead of a materialised view

CREATE TABLE transfer_orders (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_no        VARCHAR(30) UNIQUE NOT NULL,  -- e.g., "TO-2026-001234"
  from_location   UUID NOT NULL REFERENCES locations(id),
  to_location     UUID NOT NULL REFERENCES locations(id),
  status          VARCHAR(20) DEFAULT 'DRAFT' CHECK (status IN (
                    'DRAFT','APPROVED','PICKING','IN_TRANSIT','DELIVERED','CANCELLED'
                  )),
  requested_by    UUID NOT NULL,
  approved_by     UUID,
  dispatched_at   TIMESTAMPTZ,
  delivered_at    TIMESTAMPTZ,
  vehicle_id      UUID REFERENCES locations(id),  -- the vehicle carrying the stock
  notes           TEXT,
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE transfer_order_lines (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  transfer_id     UUID NOT NULL REFERENCES transfer_orders(id),
  sku_id          UUID NOT NULL REFERENCES skus(id),
  requested_qty   DECIMAL(10,3) NOT NULL,
  picked_qty      DECIMAL(10,3),
  delivered_qty   DECIMAL(10,3),
  batch_no        VARCHAR(100)
);

DRAFT → APPROVED → PICKING → IN_TRANSIT → DELIVERED
                                        ↘ DISCREPANCY_REVIEW → DELIVERED
Any state → CANCELLED (with reason)

Client → Server: GET /sync?last_sync=2026-01-10T08:00:00Z&location_id=LOC-001
Server → Client: {
  server_time: "2026-01-10T09:15:00Z",
  changes: [
    { table: "skus", operation: "upsert", data: {...}, updated_at: "..." },
    { table: "transfer_orders", operation: "upsert", data: {...}, updated_at: "..." }
  ]
}

Client → Server: POST /sync/push {
  device_id: "...",
  changes: [
    { table: "stock_ledger", operation: "insert", data: {...}, client_time: "..." },
    { table: "transfer_order_lines", operation: "update", data: {...}, client_time: "..." }
  ]
}

ALTER TABLE stock_ledger ADD COLUMN idempotency_key VARCHAR(100) UNIQUE;

-- View: SKUs below reorder point at any location
CREATE VIEW reorder_alerts AS
SELECT
  cs.sku_id,
  s.code AS sku_code,
  s.name AS sku_name,
  cs.location_id,
  l.name AS location_name,
  cs.quantity AS current_stock,
  s.reorder_point,
  s.reorder_qty,
  (s.reorder_point - cs.quantity) AS shortage
FROM current_stock cs
JOIN skus s ON s.id = cs.sku_id
JOIN locations l ON l.id = cs.location_id
WHERE cs.quantity <= s.reorder_point
  AND s.reorder_point > 0
  AND s.is_active = true
  AND l.is_active = true;

POST https://einvoice1.gst.gov.in/EWayBillAPI/rest/ewayapi/generateEWayBill
Authorization: Bearer {token}
Content-Type: application/json

{
  "supplyType": "I",  // Inward
  "subSupplyType": "1",
  "docType": "CHL",  // Challan
  "docNo": "TO-2026-001234",
  "docDate": "10/01/2026",
  "fromGstin": "27XXXXX",
  "fromTrdName": "Source Company",
  "fromAddr1": "...",
  "toGstin": "29XXXXX",
  "toTrdName": "Destination Company",
  "itemList": [
    {
      "productName": "Product Name",
      "hsnCode": "1234",
      "quantity": 100,
      "qtyUnit": "NOS",
      "taxableAmount": 50000,
      "cgstRate": 9,
      "sgstRate": 9
    }
  ],
  "transMode": "1",  // Road
  "vehicleNo": "MH12AB1234"
}

GET    /api/v1/stock?location_id=&sku_id=&below_reorder=true
GET    /api/v1/stock/history?sku_id=&location_id=&from=&to=
POST   /api/v1/stock/movements          -- create a stock movement
GET    /api/v1/transfer-orders?status=&from_location=&to_location=
POST   /api/v1/transfer-orders          -- create transfer order
PATCH  /api/v1/transfer-orders/:id/status  -- advance state machine
GET    /api/v1/reports/stock-valuation  -- stock value by location
GET    /api/v1/reports/movement-summary -- movements by period
GET    /api/v1/sync?last_sync=&location_id=  -- mobile sync
POST   /api/v1/sync/push               -- mobile push changes