Real-Time Inventory Tracking in a Custom ERP: From Stock Movements to Live Dashboards

Companies that implement real-time inventory tracking through ERP systems report a 35% improvement in inventory accuracy and a 40% increase in operational efficiency. Yet only 23% of SMBs have invested in real-time inventory tools, according to Anchor Group research. In Indonesia, where supply chains are complex and warehouse operations often span multiple locations, real-time visibility is the difference between confident procurement decisions and costly stockouts. At Commsult, I built an inventory module that tracks every stock movement as an immutable event and surfaces live data to warehouse staff and management dashboards. Here's how it works.

The Inventory Event Model

The core design decision: track inventory as a stream of events, not a mutable quantity. Every receipt, issue, transfer, adjustment, or return creates an immutable stock_movements row. The current quantity for any item at any location is always computed as the sum of movements. This gives you a built-in audit trail, makes historical reconstruction trivial, and eliminates the 'ghost stock' problem where the quantity shows 50 but nobody can account for the discrepancy.

Stock Movement Types and the Ledger Model

We support six movement types: GR (Goods Receipt from PO), GI (Goods Issue to production or customer), TR (Transfer between locations), ADJ+ (Positive Adjustment), ADJ- (Negative Adjustment), and RTN (Return from customer or to vendor). Each movement records: item_id, location_id (warehouse bin), quantity (always positive), movement_type, reference_id (links to PO, SO, or transfer order), unit_cost, and created_by. The current stock query is simply SUM of quantity WHERE movement_type IN positive movements minus SUM WHERE negative. PostgreSQL handles this aggregation with a materialized view refreshed every 5 minutes for dashboard performance.

Inventory Event Ledger Architecture (NestJS + PostgreSQL)

  ┌─────────────────────────────────────────────────────┐
  │              TRANSACTION SOURCES                    │
  │  Purchase Receipt │ Sales Issue │ Transfer │ Adjust │
  └──────────────────────┬──────────────────────────────┘
                         │ create_movement()
                         ▼
  ┌─────────────────────────────────────────────────────┐
  │            stock_movements (append-only ledger)      │
  │──────────────────────────────────────────────────── │
  │  id            UUID     PRIMARY KEY                 │
  │  item_id       UUID     REFERENCES items(id)        │
  │  location_id   UUID     REFERENCES locations(id)    │
  │  quantity      NUMERIC  (+ve always)                │
  │  movement_type VARCHAR  GR|GI|TR|ADJ+|ADJ-|RTN     │
  │  reference_id  UUID     (PO, SO, or transfer_order) │
  │  unit_cost     NUMERIC                              │
  │  created_by    UUID     REFERENCES users(id)        │
  │  created_at    TIMESTAMPTZ DEFAULT NOW()            │
  └──────────────────────┬──────────────────────────────┘
                         │
          ┌──────────────┴──────────────┐
          ▼                             ▼
  ┌──────────────────┐        ┌─────────────────────────┐
  │ Materialized View│        │  WebSocket NOTIFY        │
  │ current_stock    │        │  → React UI updates live │
  │ (refresh 5min)  │        │  → No polling needed     │
  └──────────────────┘        └─────────────────────────┘

Multi-Location Warehouse Architecture

Our schema supports a hierarchical location model: Warehouse → Zone → Aisle → Bin. An item can exist in multiple bins simultaneously. A transfer order moves quantity from one bin to another, creating two movement rows (a GI from source and a GR at destination). The transfer is atomic — both rows commit in the same PostgreSQL transaction. If either fails, neither is recorded. This prevents the 'in-transit limbo' where stock disappears during a transfer.

Reorder Point Automation

Each item has a configurable reorder_point and reorder_qty. A NestJS cron job runs nightly and checks all items where current stock has dropped below the reorder point. For each match, it creates a draft Purchase Request in the procurement module, pre-filled with the preferred vendor and reorder quantity. The warehouse manager reviews and approves these draft PRs each morning — they're not automatic POs, to keep a human in the loop. This saved our client's warehouse team approximately 2 hours per day of manual stock checking.

-- PostgreSQL: Validated stock movement function
CREATE OR REPLACE FUNCTION create_movement(
  p_item_id       UUID,
  p_location_id   UUID,
  p_quantity      NUMERIC,
  p_type          VARCHAR,
  p_reference_id  UUID,
  p_unit_cost     NUMERIC,
  p_user_id       UUID
) RETURNS UUID AS $$
DECLARE
  v_current_stock NUMERIC;
  v_movement_id   UUID;
BEGIN
  -- Validate sufficient stock for outbound movements
  IF p_type IN ('GI', 'TR') THEN
    SELECT COALESCE(SUM(
      CASE WHEN movement_type IN ('GR','ADJ+','RTN') THEN quantity
           ELSE -quantity END
    ), 0)
    INTO v_current_stock
    FROM stock_movements
    WHERE item_id = p_item_id AND location_id = p_location_id;

    IF v_current_stock < p_quantity THEN
      RAISE EXCEPTION 'Insufficient stock: % available, % requested',
        v_current_stock, p_quantity;
    END IF;
  END IF;

  INSERT INTO stock_movements
    (item_id, location_id, quantity, movement_type,
     reference_id, unit_cost, created_by)
  VALUES
    (p_item_id, p_location_id, p_quantity, p_type,
     p_reference_id, p_unit_cost, p_user_id)
  RETURNING id INTO v_movement_id;

  -- Notify connected WebSocket clients
  PERFORM pg_notify('inventory_update',
    json_build_object('item_id', p_item_id,
                      'location_id', p_location_id)::text);
  RETURN v_movement_id;
END;
$$ LANGUAGE plpgsql;

How We Built This at Commsult

The inventory module runs on NestJS with TypeORM. We use a PostgreSQL function create_movement() that validates the movement (checks source stock is sufficient for issues and transfers), inserts the row, and triggers a NOTIFY to refresh connected clients via WebSocket. The React warehouse UI subscribes to this WebSocket and updates the displayed quantities in real time — no polling required. For mobile warehouse staff, we built a simple barcode-scan-and-confirm flow using a PWA (Progressive Web App) with camera barcode reading via QuaggaJS.

Live Dashboard for Management

Management gets a dashboard showing: total inventory value by category (unit cost × quantity), slow-moving items (no movement in 90 days), items below reorder point, items with negative stock (data integrity flag), and monthly inventory turnover by category. All these are PostgreSQL queries over the stock_movements table. The turnover metric — total quantity issued divided by average stock level — helps management identify which product categories need procurement strategy changes. We display it monthly and trend it over 12 months.

Handling Physical Inventory Count (Stocktake)

Annual or quarterly physical counts are a reality in Indonesian warehouses, especially for businesses with PKWT (contract) audit requirements. Our system supports a 'freeze and count' workflow: lock a location (no movements allowed during count), record the physical count quantities item by item, compute variances against the system quantity, create ADJ movements for each variance, and unfreeze the location. The variance report is saved as a document for auditor review. This process that used to take a week of spreadsheet work now completes in a day.