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

> A practical guide to building real-time inventory visibility in a custom ERP — stock movement events, warehouse locations, reorder automation, and live dashboards.

URL: https://www.matthewswong.com/en/blog/erp-inventory-real-time-tracking/

- Author: Matthews Wong (https://www.matthewswong.com/en/about/)
- Published: 2025-06-10
- Updated: 2025-06-10
- Category: ERP

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. In my freelance ERP consulting work, 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

I support six movement types in this system: 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.

> From my experience as a freelance ERP consultant: never allow direct edits to inventory quantities through the UI. All stock changes must go through the movement engine, even manual adjustments. This seems restrictive until the first audit — then the client is grateful they can trace every quantity change back to a user action and a timestamp. I implemented ADJ+ and ADJ- movement types specifically to handle cycle count corrections while preserving the audit trail.

## Multi-Location Warehouse Architecture

My 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 the client's warehouse team approximately 2 hours per day of manual stock checking.

> **Warning: Materialized View Staleness**

> If you use a materialized view for inventory totals (which is the right performance choice), you must handle the staleness window carefully. During a high-volume receiving session, the live quantity in the materialized view may lag by up to 5 minutes. I added a 'live balance' endpoint that does a real-time aggregation query for the specific item being viewed, falling back to the materialized view for dashboard summaries. Never show a stale total on a picking screen — a warehouse picker acting on stale data causes real operational problems.

## How I Approached This

The inventory module runs on NestJS with TypeORM. I 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, I 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. I 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.

## Sources & Further Reading

- Anchor Group — 29 Wholesale Inventory Management Statistics for 2025 — https://www.anchorgroup.tech/blog/wholesale-inventory-management-statistics
- NetSuite — 14 Top Inventory Management Trends to Know in 2025 — https://www.netsuite.com/portal/resource/articles/inventory-management/inventory-management-trends.shtml
- Omniful — Best ERP Systems for Inventory Management 2025 — https://www.omniful.ai/blog/best-erp-systems

> **Key takeaway:** Companies running real-time inventory tracking through an ERP report a 35 percent improvement in inventory accuracy and 40 percent better operational efficiency, yet only 23 percent of SMBs have invested in the tooling. The gap is usually process discipline at receiving, not the software.

## Source links

- https://www.anchorgroup.tech/blog/wholesale-inventory-management-statistics
- https://www.netsuite.com/portal/resource/articles/inventory-management/inventory-management-trends.shtml
- https://www.omniful.ai/blog/best-erp-systems

## FAQ

### Why track inventory as a stream of immutable events instead of updating a quantity field directly?

Tracking inventory as an event stream means the current stock level is always computed as the sum of all movements, which provides a built-in audit trail and makes historical reconstruction trivial. It also eliminates the 'ghost stock' problem where the quantity shows a number that nobody can account for. All changes — receipts, issues, adjustments, and returns — go through the movement engine, so every discrepancy can be traced back to a specific user action and timestamp.

### How does the system prevent stock from disappearing during a warehouse transfer?

Each transfer creates two movement rows atomically: a Goods Issue (GI) from the source bin and a Goods Receipt (GR) at the destination bin, both committed in the same PostgreSQL transaction. If either row fails to insert, neither is recorded. This prevents the 'in-transit limbo' scenario where stock vanishes mid-transfer and cannot be accounted for in either location.

### What is the trade-off of using a materialized view for inventory totals, and how is it mitigated?

A materialized view refreshed every 5 minutes gives dashboard queries fast performance but introduces a staleness window — during a high-volume receiving session, displayed quantities may lag by up to 5 minutes. To mitigate this, a separate 'live balance' endpoint performs a real-time aggregation query for any specific item being actively viewed, while the materialized view is only used for summary dashboards. Showing a stale total on a picking screen is explicitly avoided because warehouse staff acting on stale data causes real operational problems.

### How does reorder point automation work, and why does it create draft Purchase Requests instead of automatic POs?

A NestJS cron job runs nightly, checks every item whose current stock has dropped below its configured reorder_point, and creates a draft Purchase Request pre-filled with the preferred vendor and reorder quantity. It deliberately stops short of issuing automatic Purchase Orders so that a warehouse manager reviews and approves each morning, keeping a human in the loop. This approach saved the client's warehouse team approximately 2 hours per day of manual stock checking.

### How does the physical inventory count (stocktake) workflow preserve the audit trail?

The system uses a 'freeze and count' workflow: a warehouse location is locked to block any movements during counting, physical quantities are recorded item by item, variances against the system quantity are computed, and ADJ movements are created for each variance before the location is unfrozen. The variance report is saved as a document for auditor review, which is especially important for Indonesian businesses with PKWT contract audit requirements. A process that previously took a week of spreadsheet work now completes in a single day.

---

- HTML version: https://www.matthewswong.com/en/blog/erp-inventory-real-time-tracking/
- Bahasa Indonesia: https://www.matthewswong.com/id/blog/erp-inventory-real-time-tracking/
- Site index for agents: https://www.matthewswong.com/llms.txt
- Sitemap: https://www.matthewswong.com/sitemap.xml
- API description (OpenAPI): https://www.matthewswong.com/openapi.json