RetailPro – Design and Implementation of a Web-based Sales and Inventory Management System for Small Retail Businesses

Abstract

RetailPro is a fully functional, full-stack web-based Sales and Inventory Management System designed and developed to address the operational challenges faced by small retail businesses in Pakistan. The system replaces manual, paper-based inventory and sales recording practices with a centralized, database-driven web application that delivers real-time stock visibility, transactional accuracy, automated low-stock alerting, and a complete audit trail for every stock change. The system uses a technology stack selected for affordability, reliability, and ease of use. The backend is developed in PHP 8 and provides four RESTful API endpoints across four files. The database is MySQL 8, hosted on XAMPP using localhost port 3306 with a database named retailpro. It uses the InnoDB storage engine to support secure transactions and enforce foreign key relationships. Apache, included with XAMPP, handles HTTP requests. The frontend is a 1,675-line single-page application (SPA) built with HTML5, CSS3, and Vanilla JavaScript. It does not use external frameworks, build tools, or npm, and communicates with the backend through JSON-based API calls using the browser's built-in fetch() function. The backend includes config.php, which creates the PDO database connection, enables exception-based error handling, disables emulated prepared statements, and provides shared helper functions used by all API endpoints. The auth.php endpoint authenticates users by checking their email, password, and active status with prepared SQL statements. The products.php endpoint supports full CRUD operations: retrieving active products with category names, adding new products with duplicate SKU checking and automatic category creation, updating selected product fields while validating SKU uniqueness, and performing soft deletion by setting is_active = 0 to preserve sales history. The sales.php endpoint records sales using ACID-compliant transactions. It locks the selected product row, checks stock availability, updates inventory, records the sale, creates an audit log in the stock_adjustments table, and rolls back the transaction if an error occurs. The frontend SPA includes five main modules: a Dashboard that displays live business statistics such as total products, stock value, today's sales, low-stock items, total revenue, top-selling products, and recent transactions; an Inventory module with filtering and stockadjustment features; a Products module for full CRUD operations; and a Sales module that provides sales history, date-range filtering, and a new sale form. The system was evaluated through surveys of ten retail businesses, user testing with five business owners, and concurrent transaction stress testing. The results showed that all project objectives were successfully achieved.