IMS Pro — Simple Semester Project Report

1. Title Page

• Project Title: IMS Pro (Inventory Management System)

• Student Name(s): waleed

• Course Name & Code: DBMS

• Date: May 17, 2026

2. Introduction

• Project Background: IMS Pro is a web-based inventory management system developed to manage products, warehouses, stock levels, purchase orders, and audit logs for small-to-medium businesses. It improves inventory visibility and operational control through a centralized platform.

• Reference Application: The project is inspired by common ERP inventory modules (such as Odoo/NetSuite-style workflows) and adapted into a lightweight full-stack implementation.

• Project Objectives:

  • Maintain accurate and auditable stock records.
  • Support purchase order lifecycle (create → approve → receive).
  • Implement role-based access for Admin, Manager, Staff, and Viewer.
  • Provide reports and low-stock insights for decision-making.

3. System Overview

• Key Features:

  • Product and category management
  • Multi-warehouse stock tracking
  • Stock transactions (IN, OUT, ADJUSTMENT, TRANSFER)
  • Purchase order workflow with partial receiving
  • Audit logging for critical operations
  • Reports and dashboard analytics

• User Roles:

  • Admin: Full system access and data management
  • Manager: Approvals, monitoring, and reporting
  • Staff: Receiving, stock updates, and fulfillment actions
  • Viewer: Read-only access to operational data

4. Architectural Style

• Selected Style: Layered Client-Server architecture (React frontend client + Express service layer + MySQL database).

• Role of the client application:

  • React SPA sends HTTP requests (GET/POST/PUT) to backend REST API endpoints.
  • Client handles presentation, user actions (create PO, receive goods, adjust stock) and presents feedback (toasts, modals).

• Role of the database server:

  • MySQL stores normalized tables (product, stock, stock_transaction, purchase_order, po_item, audit_log, employee, warehouse).
  • Critical stock updates use a stored procedure RecordStockMovement to ensure ACID semantics (transactions + upsert behavior) and prevent negative stock.

• Data flow direction:

  • Client → Backend API → Database (execute queries / stored procedures) → Backend returns result → Client displays updates.

• Basic interaction examples:

  • Insert (create product):
    • Client: POST /api/v1/products { name, sku, reorder_level }
    • Server: validate → Product.create(...) → audit_log INSERT
    • Client: show success
  • Update (receive goods for PO):
    • Client: PUT /api/v1/purchase-orders/:id/receive { items }
    • Server: purchaseOrderService.receiveGoods(id, items) → calls stored procedure per item:
      • CALL RecordStockMovement(product_id, warehouse_id, 'IN', qty, ref_id=PO)
      • Insert stock_transaction and upsert stock row
      • Update PO item qty_received and PO status
    • Client: show success and refresh stock/PO
  • Fetch (low stock list):
    • Client: GET /api/v1/stock/alerts/low-stock
    • Server: stockRepo.findLowStock() raw query returns aggregated view
    • Client: display Low-Stock tab

Design Patterns

This project implements a set of well-known design patterns to keep the code modular, testable and extensible. A detailed, example-rich document is available at DESIGN_PATTERNS_DETAILED.md in the repository root (recommended for review during presentations).

Summary of patterns implemented:

Pattern	Purpose	Where to look
Repository	Centralized data access, pagination, audit	backend/src/repositories/base.repository.js + domain repos
Strategy	Pluggable stock valuation algorithms	backend/src/services/stockValuation.strategy.js
Command	Encapsulate inventory actions (adjust, transfer)	backend/src/commands/inventory.commands.js
Observer	Domain event bus and listeners (notifications, SSE)	backend/src/utils/domainEvents.js, registerDomainEventListeners.js
Factory	Notification channels + middleware factory for RBAC	backend/src/services/notification/notification.factory.js, backend/src/middleware/auth.js
Facade	Unified inventory overview endpoint	backend/src/services/inventory.facade.js
Builder	Fluent SQL/query builder for reports	backend/src/utils/reportQuery.builder.js
Adapter	Normalize external product import data	backend/src/utils/productImport.adapter.js
Real-Time / SSE	Server-Sent Events for live stock updates	backend/src/utils/stockStreamHub.js, frontend/src/pages/stock/StockPage.js
Dynamic UML	Auto-generate Mermaid diagrams from models/schema	backend/src/services/architecture.service.js, frontend/src/pages/admin/ArchitectureUMLPage.js
Enterprise Inventory	Bin/batch/serial tracking, schema extensions	backend/src/config/schema.sql, backend/src/models/Stock.js

For full implementation details, code samples, usage examples and where to open files during demonstrations, see DESIGN_PATTERNS_DETAILED.md in the repository root.

4. Database Design

• Database Type: MySQL 8.0 (InnoDB)

bd30bc5 (docs: add Design Patterns section to README)

• Justification: This style was selected because it separates concerns clearly (UI, business logic, data access), improves maintainability, and supports scalable feature additions.

• Diagram:

flowchart LR
  A[React Client]
  B[Express API]
  C[Service Layer]
  D[Repository Layer]
  E[(MySQL Database)]

  A --> B
  B --> C
  C --> D
  D --> E
  E --> D
  D --> C
  C --> B
  B --> A

Loading

5. Database Design

• Database Type: MySQL 8.0 (InnoDB)

• Schema Overview: Main entities/tables:

  • product (product_id, sku, name, reorder_level, reorder_qty)
  • warehouse (warehouse_id, warehouse_name, location)
  • stock (stock_id, product_id, warehouse_id, qty_on_hand)
  • stock_transaction (txn_id, product_id, warehouse_id, txn_type, quantity)
  • purchase_order (po_id, supplier_id, status)
  • po_item (po_item_id, po_id, product_id, qty_ordered, qty_received)
  • employee (emp_id, name, email, role)
  • audit_log (log_id, table_name, action, changed_at)

• ER Diagram:

erDiagram
  PRODUCT ||--o{ STOCK : has
  WAREHOUSE ||--o{ STOCK : stores
  PRODUCT ||--o{ PO_ITEM : ordered_in
  PURCHASE_ORDER ||--o{ PO_ITEM : contains
  WAREHOUSE ||--o{ PO_ITEM : destination
  PRODUCT ||--o{ STOCK_TRANSACTION : transacts
  WAREHOUSE ||--o{ STOCK_TRANSACTION : records
  EMPLOYEE ||--o{ STOCK_TRANSACTION : creates
  EMPLOYEE ||--o{ PURCHASE_ORDER : creates
  EMPLOYEE ||--o{ AUDIT_LOG : changes

Loading

• Sample Table Structure:

Column	Data Type	Key/Constraint
product_id	INT	PK, Auto Increment
sku	VARCHAR(50)	UNIQUE, NOT NULL
name	VARCHAR(200)	NOT NULL
reorder_level	INT	DEFAULT 10
reorder_qty	INT	DEFAULT 50
is_active	BOOLEAN	DEFAULT TRUE

6. Design Patterns

#	Pattern	Category	Purpose	Implementation Status
1	Repository	Architectural	Data access abstraction	✅ Repository classes in backend
2	Strategy	Behavioral	Pluggable algorithms	✅ Stock valuation strategy layer
3	Command	Behavioral	Encapsulate actions	✅ Inventory command objects
4	Observer	Behavioral	Event-driven notifications	✅ Domain event bus/listeners
5	Factory	Creational	Create channel-specific notifiers	✅ Notification factory
6	Facade	Structural	Unified cross-module interface	🟡 Partial: inventory/report aggregation exists; unified PO+stock+notification facade is pending
7	Builder	Creational	Fluent object construction	✅ Report query builder
8	Adapter	Structural	Normalize incompatible inputs	✅ Product import adapter

Summary:
The IMS implementation uses structural, behavioral, and creational patterns to keep business logic modular and maintainable.

Architecture & Domain Highlights

#	Feature	Category	Purpose	Implementation Status
1	Real-Time/SSE	Architectural	Live data updates	✅ EventSource-based stock updates
2	Dynamic UML	Architectural	Auto-generate architecture diagrams	✅ Mermaid-based reverse UML
3	Bin/Batch/Serial Tracking	Domain	Enterprise-grade inventory traceability	✅ Implemented in stock and stock-transaction workflows

7. Implementation

• Tools & Technologies:

  • Backend: Node.js, Express, Sequelize, MySQL
  • Frontend: React, react-router
  • Authentication/Security: JWT, bcrypt
  • Logging: Winston
  • Testing: Jest

• Functionalities Developed:

  • Product CRUD and validation
  • Stock aggregation and low-stock detection
  • Purchase order lifecycle with partial receives
  • Audit logging for create/update/delete operations
  • Reporting and analytics endpoints

• Screenshots: (All images previously present in README are retained below.)

8. Testing

• Testing Approach:

  • Manual testing of key UI workflows
  • Automated backend service tests with Jest

• Test Cases:

  • Full receive updates stock correctly
  • Partial receive updates PO status correctly
  • Validation blocks invalid inputs/operations
  • Audit logs are generated for critical actions

• Results: Core modules were validated for expected behavior; identified issues during development were fixed through iterative testing and re-verification.

9. Conclusion

• Summary: IMS Pro achieves a role-based, auditable inventory workflow with multi-warehouse tracking and PO lifecycle support.

• Challenges:

  • Ensuring stock consistency during transactional updates
  • Coordinating PO state transitions with stock updates

• Future Work:

  • Notification/email alerts for low stock
  • Advanced analytics and exportable reports
  • Automated reconciliation and forecasting support

10. References

• ERP/IMS workflow references (Odoo/NetSuite-style inventory concepts)
• Project source code: /backend and /frontend
• Template context provided by issue discussion