Integrated Web-Based Inventory Tracking System

Executive Summary

The system records every receipt, issue, and transfer in the warehouse in a single database. Warehouse staff enter movements in the browser, while production and purchasing read the same stock position without waiting for an end-of-day report.

Background

Stock movements used to be logged across several separate spreadsheets and reconciled at the end of each day. That pattern caused three measurable problems:

• Differences between records and physical stock were only found a day after they happened.
• Re-typing data between sheets introduced typos in item codes.
• There was no trail showing who changed a quantity, or when.

Solution Architecture

The architecture has three layers: a web interface, a REST service, and a relational database. Every quantity change is stored as an immutable movement row. Stock position is calculated from the sum of movements, so the full history can always be traced.

Entity	Purpose
items	Master record of code, unit, and minimum stock level
movements	Receipts, issues, and transfers
users	Accounts and roles: warehouse, production, purchasing

Technical Implementation

The service is built with Node.js and Express. Input is validated on the server, and movement operations are wrapped in database transactions so a failed request never leaves half-saved data.

SELECT i.code, i.name,
       SUM(m.quantity) AS stock
FROM items i
LEFT JOIN movements m ON m.item_id = i.id
GROUP BY i.id
HAVING stock < i.minimum_stock;

This query drives the low-stock alert list. An index on movements.item_id was added after the execution plan showed a full table scan.

Results & Impact

After two usage cycles, the daily reconciliation no longer requires manual preparation. Gaps between records and physical stock dropped, and every correction now has an author and a timestamp. The next planned step is scheduled CSV report export.