## Table of Contents 1. [Introduction](#introduction) 2. [Key Features](#key-features) 3. [Database Design](#database-design) 4. [API Development with Express and Node](#api-development-with-express-and-node) 5. [Establishing Frontend and Backend Connection](#establishing-frontend-and-backend-connection) 6. [Frontend Component Overview](#frontend-component-overview) 7. [User Interface Design](#user-interface-design) 8. [Integration Testing](#integration-testing) 9. [Optimizations and Adjustments](#optimizations-and-adjustments) 10. [Conclusion](#conclusion) ##Introduction: As a junior developer, I embarked on this project to not only enhance my skills but also to create a practical tool that addresses real-world business needs. Invento combines a robust backend built with Node.js and Express, a dynamic frontend powered by React, and a seamless user experience facilitated by modern design principles. ##Key Features: - **User Authentication:** Secure signup and login functionality with JWT token-based authentication to ensure data privacy and secure access. - **Clients Management:** - **New Client:** Easily add new clients with all necessary details. - **Client List:** View and manage a detailed list of all clients. - **Suppliers Management:** - **New Supplier:** Add new suppliers seamlessly. - **Supplier List:** Access and manage a detailed list of all suppliers. - **Products Management:** - **New Product:** Add new products to the inventory. - **Product List:** View and manage an extensive list of all products in the inventory. - **Purchases Management:** - **New Purchase:** Record new purchases with dynamic forms. - **Purchase Summary:** Access detailed summaries of all purchase transactions. - **Sales Management:** - **New Sale:** Record new sales efficiently. - **Sale Summary:** View detailed summaries of all sales transactions. ##Database Design: I started by creating the database for the Invento app in MySQL Workbench. The database consists of several tables: `users`, `clients`, `suppliers`, `products`, `purchases`, `purchase_orders`, `sales`, `sale_orders`, and `inventory`. Below is the detailed design and structure of each table along with the associated queries. **Users Table :** The `users` table contains information about each user and includes an auto-incrementing ID, username, email and password. ``` CREATE TABLE `users` ( `id` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ); ``` **Clients Table :** The `clients` table stores client information. It has an auto-incrementing client ID, user ID (foreign key), name, address, email and phone number. ``` CREATE TABLE `clients` ( `client_id` INT NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `name` VARCHAR(255) NOT NULL, `address` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL, `phone` VARCHAR(50) NOT NULL, PRIMARY KEY (`client_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ); ``` **Suppliers Table :** The `suppliers` table stores supplier information. It has an auto-incrementing supplier ID, user ID (foreign key), name, address, email and phone number. ``` CREATE TABLE `suppliers` ( `supplier_id` INT NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `name` VARCHAR(255) NOT NULL, `address` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL, `phone` VARCHAR(50) NOT NULL, PRIMARY KEY (`supplier_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ); ``` **Products Table :** The `products` table contains product details. It has an auto-incrementing product ID, user ID (foreign key), name, purchase price, sale price, and a saleable status. ``` CREATE TABLE `products` ( `product_id` INT NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `name` VARCHAR(255) NOT NULL, `purchase_price` DECIMAL(10, 2) NOT NULL, `sale_price` DECIMAL(10, 2) NOT NULL, `saleable` ENUM('Yes', 'No') NOT NULL, PRIMARY KEY (`product_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ); ``` **Purchases Table :** The `purchases` table records purchase transactions. It includes an auto-incrementing purchase ID, user ID (foreign key), supplier ID (foreign key), product ID (foreign key), product name, purchase price, quantity, item total, discount, total, and date_time. ``` CREATE TABLE `purchases` ( `purchase_id` INT NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `supplier_id` INT NOT NULL, `product_id` INT NOT NULL, `product_name` VARCHAR(255) NOT NULL, `purchase_price` DECIMAL(10, 2) NOT NULL, `quantity` INT NOT NULL, `item_total` DECIMAL(10, 2) NOT NULL, `discount` DECIMAL(10, 2) NOT NULL, `total` DECIMAL(10, 2) NOT NULL, `date_time` DATETIME NOT NULL, PRIMARY KEY (`purchase_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`), FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`supplier_id`), FOREIGN KEY (`product_id`) REFERENCES `products`(`product_id`) ); ``` **Purchase Orders Table :** The `purchase_orders` table links purchase orders with users and purchases. ``` CREATE TABLE `purchase_orders` ( `purchase_order_id` INT NOT NULL AUTO_INCREMENT, `purchase_id` INT NOT NULL, `user_id` INT NOT NULL, PRIMARY KEY (`purchase_order_id`), FOREIGN KEY (`purchase_id`) REFERENCES `purchases`(`purchase_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ); ``` **Sales Table :** The `sales` table records sale transactions. It includes an auto-incrementing sale ID, user ID (foreign key), client ID (foreign key), product ID (foreign key), product name, sale price, quantity, item total, discount, total, date_time. ``` CREATE TABLE `sales` ( `sale_id` INT NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `client_id` INT NOT NULL, `product_id` INT NOT NULL, `product_name` VARCHAR(255) NOT NULL, `sale_price` DECIMAL(10, 2) NOT NULL, `quantity` INT NOT NULL, `item_total` DECIMAL(10, 2) NOT NULL, `discount` DECIMAL(10, 2) NOT NULL, `total` DECIMAL(10, 2) NOT NULL, `date_time` DATETIME NOT NULL, PRIMARY KEY (`sale_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`), FOREIGN KEY (`client_id`) REFERENCES `clients`(`client_id`), FOREIGN KEY (`product_id`) REFERENCES `products`(`product_id`) ); ``` **Sale Orders Table :** The `sale_orders` table links sale orders with users and sales. ``` CREATE TABLE `sale_orders` ( `sale_order_id` INT NOT NULL AUTO_INCREMENT, `sale_id` INT NOT NULL, `user_id` INT NOT NULL, PRIMARY KEY (`sale_order_id`), FOREIGN KEY (`sale_id`) REFERENCES `sales`(`sale_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ); ``` **Inventory Table :** The `inventory` table tracks the current stock of products. It has an auto-incrementing inventory ID, product ID (foreign key), product name, quantity, and user ID (foreign key). ``` CREATE TABLE `inventory` ( `inventory_id` INT NOT NULL AUTO_INCREMENT, `product_id` INT NOT NULL, `product_name` VARCHAR(255) NOT NULL, `quantity` INT NOT NULL, `user_id` INT NOT NULL, PRIMARY KEY (`inventory_id`), FOREIGN KEY (`product_id`) REFERENCES `products`(`product_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ); ``` **Triggers :** To automatically update the inventory based on sales and purchases, I created two triggers: - **Sales Insert Trigger :** This trigger updates the inventory after a new sale is recorded, decreasing the quantity of the sold product. ``` DELIMITER // CREATE TRIGGER `sales_insert` AFTER INSERT ON `sales` FOR EACH ROW BEGIN DECLARE current_quantity INT; SELECT `quantity` INTO current_quantity FROM `inventory` WHERE `product_id` = NEW.`product_id` AND `user_id` = NEW.`user_id`; IF current_quantity IS NULL THEN INSERT INTO `inventory` (`product_id`, `product_name`, `quantity`, `user_id`) VALUES (NEW.`product_id`, NEW.`product_name`, -NEW.`quantity`, NEW.`user_id`); ELSE UPDATE `inventory` SET `quantity` = `quantity` - NEW.`quantity` WHERE `product_id` = NEW.`product_id` AND `user_id` = NEW.`user_id`; END IF; END // DELIMITER ; ``` - **Purchase Insert Trigger :** This trigger updates the inventory after a new product is recorded, increasing the quantity of the purchased product. ``` DELIMITER // CREATE TRIGGER `purchase_insert` AFTER INSERT ON `purchases` FOR EACH ROW BEGIN DECLARE current_quantity INT; SELECT `quantity` INTO current_quantity FROM `inventory` WHERE `product_id` = NEW.`product_id` AND `user_id` = NEW.`user_id`; IF current_quantity IS NULL THEN INSERT INTO `inventory` (`product_id`, `product_name`, `quantity`, `user_id`) VALUES (NEW.`product_id`, NEW.`product_name`, NEW.`quantity`, NEW.`user_id`); ELSE UPDATE `inventory` SET `quantity` = `quantity` + NEW.`quantity` WHERE `product_id` = NEW.`product_id` AND `user_id` = NEW.`user_id`; END IF; END // DELIMITER ; ``` This database design ensures that all necessary data is captured and that the inventory remains accurate and up-to-date with every transaction. ##API Development with Express and Node: I developed various APIs to manage clients, inventory, products, purchases, sales, suppliers, and users. Additionally, I included authentication endpoints and middleware to secure the application. - **signupController :** This controller handles user registration and credential authentication. - **signup :** Registers a new user by checking if the username and email are already in use. If an existing user is found with the same username and email, a `409 conflict` status is returned with a message indicating that the user already exists. If no existing user is found, the password is hashed using bcrypt with a salt round of 12. The user's details, including the hashed password, are then inserted into the `users` table. Upon successful creation, a `201 Created` status with a success message is returned. If there is an error during the process, a `500 Internal Server Error` status is returned with a failure message. ```javascript import db from '../../db.js'; import bcrypt from 'bcrypt'; const signup = async (req, res) => { const {username, email, password} = req.body; const existingUser = await new Promise((resolve, reject) => { db.query('SELECT * FROM users WHERE username = ? AND email = ?', [username, email], (error, results) => { if(error) { reject(error); } else { resolve(results[0]); } }); }); if(existingUser) { return res.status(409).json({message: 'User already exists, please log in!'}); } const saltRounds = 12; const salt = bcrypt.genSaltSync(saltRounds); const hashedPassword = bcrypt.hashSync(password, salt); db.query('INSERT INTO users (username, email, password) VALUES (?, ?, ?)', [username, email, hashedPassword], (err) => { if(err) { return res.status(500).json({message: 'Failed to create User'}); } else { return res.status(201).json({message: 'User created successfully!'}); } }); }; export {signup}; ``` - **loginController :** This controller manages user authentication by verifying credentials during the login process. - **login :** Authenticates a user by verifying their username and password. It first checks if a user with the provided username exists in the database. If no user is found or if there's an error in the query, a `401 Unauthorized` status is returned with a message indicating invalid credentials. If the user is found, the provided password is compared with the stored hashed password using bcrypt. If the password is invalid, a `401 Unauthorized` status is returned. If the password is correct, a JWT token is generated with the user's ID and username, using a secret key from the environment variables and an expiration time of 1 hour. The response includes a `200 OK` status with the authentication token and a success message. ```javascript import bcrypt from "bcrypt"; import jwt from "jsonwebtoken"; import db from "../../db.js"; import dotenv from "dotenv"; dotenv.config(); const login = async (req, res) => { const { username, password } = req.body; db.query( "SELECT * FROM users WHERE username = ?", [username], async (error, results) => { if (error || results.length === 0) { return res .status(401) .json({ message: "User does not exist! Please Signup." }); } const user = results[0]; //check if the password is correct const isPasswordValid = await bcrypt.compare(password, user.password); if (!isPasswordValid) { return res .status(401) .json({ message: "Incorrect password!" }); } //Generate JWT Token const token = jwt.sign( { id: user.id, username: user.username }, process.env.JWT_SECRET, { expiresIn: "1h" } ); res .status(200) .json({ auth: true, token: token, message: "Successfully authenticated the token", }); } ); }; export { login }; ``` - **middleware :** This middleware is essential for verifying user authorization and ensuring that users have the necessary permissions to access protected resources. - **verifyToken :** This middleware function checks for a valid JWT token in the `Authorization` header of the request. If the token is missing, it responds with a `401 Unauthorized` status and a message indicating no token was provided. If a token is present, it attempts to verify the token using the secret key from the environment variables. If the token verification fails, it responds with a `500 Internal Server Error` status, including an error message and details. If the token is successfully verified, the user ID extracted from the token is added to the request object(`req.userId`), and the request proceeds to the next middleware or route handler. ```javascript import jwt from "jsonwebtoken"; import dotenv from "dotenv"; dotenv.config(); const verifyToken = (req, res, next) => { const token = req.headers["authorization"]; const authToken = token && token.split(" ")[1]; if (!authToken) { return res.status(401).json({ message: "No token provided" }); } jwt.verify(authToken, process.env.JWT_SECRET, (err, decoded) => { if (err) { return res .status(500) .json({ auth: false, message: "Failed to authenticate token", error: err, }); } req.userId = decoded.id; next(); }); }; export { verifyToken }; ``` - **userController :** This controller handles the retrieval of user data from the `users` table. - **getUserDetails :** Retrieves the details of a specific user based on their `userId`. It queries the `users` table to fetch the `id`, `username`, and `email` of the user. If the user is found, their details are returned in the response. If the user is not found or an error occurs, a `404` status with a message indicating "User not Found" is returned. ```javascript import db from "../../db.js"; const getUserDetails = async (req, res) => { const userId = req.userId; db.query( "SELECT id, username, email FROM users WHERE id = ?", [userId], (error, results) => { if (error || results.length === 0) { return res.status(404).json({ message: "User not Found" }); } const user = results[0]; return res.status(200).json(user); } ); }; export { getUserDetails }; ``` - **clientsController :** This controller manages the creation, retrieval, and updating of client records in the `clients` table. - **postClients :** Handles the creation of new client records. It receives client details such as `name`, `address`, `email` and `phone` from the request body and inserts them into the `clients` table associated with the authenticated user. On successful insertion, it responds with a confirmation message; Otherwise, it returns an error. - **updateClients :** Manages updates to existing client records. It updates details (`name`, `address`, `email`, `phone`) for a specified `client_id` and `user_id`. If the update operation is successful, it returns a confirmation message. If no client is found or if the user is not authorized, it returns an error message. - **getClients :** Retrieves all client records associated with the authenticated user. It queries the `clients` table for entries matching the `user_id`. If clients are found, they are returned in the response. If an error occurs, it returns an error message indicating the issue. ```javascript import db from "../../db.js"; const postClients = async (req, res) => { const user_id = req.userId; const { name, address, email, phone } = req.body; db.query( "INSERT INTO clients (user_id, name, address, email, phone) VALUES (?, ?, ?, ?, ?)", [user_id, name, address, email, phone], (error, results) => { if (error) { console.log(error); return res.status(404).json(error); } return res .status(200) .json({ message: "Client was posted successfully" }); } ); }; const updateClients = async (req, res) => { const userId = req.userId; const { client_id, name, address, email, phone } = req.body; const clientUpdateQuery = "UPDATE clients SET name = ?, address = ?, email = ?, phone = ? WHERE user_id = ? AND client_id = ?"; db.query( clientUpdateQuery, [name, address, email, phone, userId, client_id], (error, results) => { if (error) { return res.status(400).json(error); } if (results.affectedRows === 0) { return res .status(404) .json({ message: "Client not found or user not authorized" }); } return res .status(200) .json({ message: "Client has been successfully updated" }); } ); }; const getClients = async (req, res) => { const userId = req.userId; db.query( "SELECT * FROM clients WHERE user_id = ?", [userId], (error, results) => { if (error) { return res .status(404) .json({ message: "There are clients to this user" }); } const clients = results; return res.status(200).json(clients); } ); }; export { getClients, postClients, updateClients }; ``` - **suppliersController :** This controller manages the creation, retrieval, and updating of supplier records in the `suppliers` table. - **postSuppliers :** Handles the creation of new supplier records. It takes supplier details such as `name`, `address`, `email`, and `phone` from the request body and inserts them into the `suppliers` table. If the insertion is successful, it responds with a confirmation message; otherwise, it returns an error. - **updateSuppliers :** Manages updates to existing supplier records. It updates the details (`name`, `address`, `email`, `phone`) for a specified `supplier_id` and `user_id`. If the update operation is successful, it returns a confirmation message. If no supplier is found or if the user is not authorized, it returns an error message. - **getSuppliers :** Retrieves all supplier records associated with the authenticated user. It queries the `suppliers` table for entries matching the `user_id`. If suppliers are found, they are returned in the response; otherwise, an error message indicating that no suppliers were found for the user is returned. ```javascript import db from "../../db.js"; const postSuppliers = async (req, res) => { const userId = req.userId; const { name, address, email, phone } = req.body; db.query( "INSERT INTO suppliers (user_id, name, address, email, phone) VALUES (?, ?, ?, ?, ?)", [userId, name, address, email, phone], (error, result) => { if (error) { return res.status(404).json(error); } return res .status(200) .json({ message: "Supplier is posted succesfully!" }); } ); }; const updateSuppliers = async (req, res) => { const userId = req.userId; const { supplier_id, name, address, email, phone } = req.body; const productUpdateQuery = "UPDATE suppliers SET name = ?, address = ?, email = ?, phone = ? WHERE user_id = ? AND supplier_id = ?"; db.query( productUpdateQuery, [name, address, email, phone, userId, supplier_id], (error, results) => { if (error) { return res.status(400).json(error); } if (results.affectedRows === 0) { return res .status(404) .json({ message: "Supplier not found or user not authorized" }); } return res .status(200) .json({ message: "Supplier has been successfully updated" }); } ); }; const getSuppliers = async (req, res) => { const userId = req.userId; db.query( "SELECT * from suppliers WHERE user_id = ?", [userId], (error, results) => { if (error) { return res .status(404) .json({ message: "There are no supplier to this user." }); } const supplier = results; return res.status(200).json(supplier); } ); }; export { postSuppliers, getSuppliers, updateSuppliers }; ``` - **productsController :** This controller handles the creation, retrieval, and updating of product records in the `products` table. - **postProduct :** Manages the creation of new product entries. It processes the product details sent in the request body, including `name`, `purchase_price`, `sale_price`, and `saleable`, and inserts this information into the `products` table. If the insertion is successful, it responds with a confirmation message; otherwise, it returns an error. - **updateProduct :** Handles updates to existing product records. It updates product details such as `name`, `purchase_price`, `sale_price`, and `saleable` for a specified `product_id` and `user_id`. If the update operation is successful, it responds with a confirmation message. If no product is found or if the user is not authorized, it returns an error message. - **getProducts :** Retrieves all product records associated with the authenticated user. It queries the `products` table for entries matching the `user_id`. If products are found, they are returned in the response; otherwise, an error message indicating that no products were found for the user is returned. ```javascript import db from "../../db.js"; const postProduct = async (req, res) => { const userId = req.userId; const { name, purchase_price, sale_price, saleable } = req.body; db.query( "INSERT INTO products (user_id, name, purchase_price, sale_price, saleable) VALUES (?, ?, ?, ?, ?)", [userId, name, purchase_price, sale_price, saleable], (error, results) => { if (error) { return res.status(400).json(error); } return res .status(200) .json({ message: "Product has been successfully posted" }); } ); }; const updateProducts = async (req, res) => { const userId = req.userId; const { product_id, name, purchase_price, sale_price, saleable } = req.body; const productUpdateQuery = "UPDATE products SET name = ?, purchase_price = ?, sale_price = ?, saleable = ? WHERE user_id = ? AND product_id = ?"; db.query( productUpdateQuery, [name, purchase_price, sale_price, saleable, userId, product_id], (error, results) => { if (error) { return res.status(400).json(error); } if (results.affectedRows === 0) { return res .status(404) .json({ message: "Product not found or user not authorized" }); } return res .status(200) .json({ message: "Product has been successfully updated" }); } ); }; const getProducts = async (req, res) => { const userId = req.userId; db.query( "SELECT * FROM products WHERE user_id = ?", [userId], (error, results) => { if (error || results.length === 0) { return res .status(404) .json({ message: "There are no products to this user" }); } const products = results; return res.status(200).json(products); } ); }; export { getProducts, postProduct, updateProducts }; ``` - **purchaseController :** This controller manages the creation and retrieval of purchase data in the purchases table. - **postPurchase :** Handles the creation of new purchase records. It proceeds the purchase data sent in the request body, adds the `user_id` to each purchase entry, and inserts the data into the `purchases` table. If the insertion is successful, it responds with a success message; otherwise, it returns an error. - **getPurchase :** Retrieves purchase data for the authenticated user. It fetches and aggregates information from the `purchases`, `suppliers`, and `products` tables, including details such as supplier name, product name, purchase price, quantity, item total, discount, total, and date. The retrieved data is returned in a structured format, ordered by the date of the purchase. ```javascript import db from "../../db.js"; const postPurchase = async (req, res) => { const userId = req.userId; const purchaseData = req.body; purchaseData.forEach((purchase) => { purchase.user_id = userId; }); const postPurchaseQuery = "INSERT INTO purchases (user_id, supplier_id, product_id, product_name, purchase_price, quantity, item_total, discount, total) VALUES ?"; const values = purchaseData.map((purchase) => [ purchase.user_id, purchase.supplier_id, purchase.product_id, purchase.product_name, purchase.purchase_price, purchase.quantity, purchase.item_total, purchase.discount, purchase.total, ]); db.query(postPurchaseQuery, [values], (error, result) => { if (error) { return res.status(404).json({ error: "Failed to send purchase data" }); } return res .status(201) .json({ message: "Purchase data inserted successfully!" }); }); }; const getPurchase = async (req, res) => { const userId = req.userId; const purchaseDetailsQuery = ` SELECT p.purchase_id, s.name AS supplier_name, pr.name AS product_name, p.purchase_price AS order_amount, p.quantity, p.item_total, p.discount, p.total, DATE_FORMAT(p.date_time, '%d-%m-%Y') AS date FROM purchases p JOIN suppliers s ON p.supplier_id = s.supplier_id JOIN products pr ON p.product_id = pr.product_id WHERE p.user_id = ? GROUP BY p.purchase_id, p.supplier_id, s.name, p.purchase_price, p.quantity, p.item_total, p.discount, p.total, p.date_time ORDER BY p.date_time ASC`; db.query(purchaseDetailsQuery, [userId], (error, results) => { if (error) { return res.status(404).json({ error: "No purchase data found!" }); } const purchaseData = results; return res.status(200).json(purchaseData); }); }; export { postPurchase, getPurchase }; ``` - **saleController :** This controller manages the creation and retrieval of sales data in the sales table. - **postSale :** Handles the creation of new sales records. It processes the sale data sent in the request body, appends the `user_id` to each sale entry, and inserts the data into the `sales` table. If the insertion is successful, it responds with a success message; otherwise, it returns an error. - **getSaleData :** Retreives sales data for the authenticated user. It fetches and aggregates information from the `sales`, `clients`, and `products` tables, including details such as client name, product name, sale price, quantity, item total, discount, total and date. The retrieved data is returned in a structured format, ordered by the date of the sale. ```javascript import db from "../../db.js"; const postSale = async (req, res) => { const userId = req.userId; const saleData = req.body; saleData.forEach((sale) => { sale.user_id = userId; }); const postSaleQuery = "INSERT INTO sales (user_id, client_id, product_id, product_name, sale_price, quantity, item_total, discount, total) VALUES ?"; const values = saleData.map((sale) => [ sale.user_id, sale.client_id, sale.product_id, sale.product_name, sale.sale_price, sale.quantity, sale.item_total, sale.discount, sale.total, ]); db.query(postSaleQuery, [values], (error, result) => { if (error) { return res.status(404).json({ error: "Failed to send sale data" }); } return res .status(200) .json({ message: "Sale data has been inserted successfully!" }); }); }; const getSaleData = async (req, res) => { const userId = req.userId; const saleDataQuery = ` SELECT s.sale_id, c.name AS client_name, p.name AS product_name, s.sale_price AS order_amount, s.quantity, s.item_total, s.discount, s.total, DATE_FORMAT(s.date_time, '%d-%m-%Y') AS date FROM sales s JOIN clients c ON s.client_id = c.client_id JOIN products p ON s.product_id = p.product_id WHERE s.user_id = ? GROUP BY s.sale_id, s.client_id, c.name, s.sale_price, s.quantity, s.item_total, s.discount, s.total, s.date_time ORDER BY s.date_time ASC`; db.query(saleDataQuery, [userId], (error, result) => { if (error) { return res.status(404).json(error); } const saleData = result; return res.status(200).json(saleData); }); }; export { postSale, getSaleData }; ``` - **inventoryController :** The inventoryController contains methods to get counts and totals related to purchases, sales, purchase orders, sale orders, suppliers, clients, and inventory items. Each method queries the database based on the userId to aggregate and retrieve relevant data for the dashboard. ```javascript import db from '../../db.js'; const getPurchases = async (req, res) => { const userId = req.userId; const getTotalPurchase = `SELECT COUNT(purchase_id) as total_purchase FROM purchases WHERE user_id = ?`; db.query(getTotalPurchase, [userId], (error, results) => { if(error || results.length === 0) { return res.status(404).json(error); } return res.status(200).json(results); }); }; const getSales = async (req, res) => { const userId = req.userId; const getTotalSale = `SELECT COUNT(sale_id) as total_sales FROM sales WHERE user_id = ?`; db.query(getTotalSale, [userId], (error, results) => { if(error || results.length === 0) { return res.status(404).json(error); } return res.status(200).json(results); }); }; const getPurchaseOrders = async (req, res) => { const userId = req.userId; const getPurchaseOrder = `SELECT COUNT(purchase_order) as total_purchase_orders FROM purchase_orders WHERE user_id = ?`; db.query(getPurchaseOrder, [userId], (error, results) => { if(error || results.length === 0) { return res.status(404).json(error); } return res.status(200).json(results); }); }; const getSaleOrders = async (req, res) => { const userId = req.userId; const getSaleOrder = `SELECT COUNT(sale_order) as total_sale_order FROM sale_orders WHERE user_id = ?`; db.query(getSaleOrder, [userId], (error, results) => { if(error || results.length === 0) { return res.status(404).json(error); } return res.status(200).json(results); }); }; const getSuppliers = async (req, res) => { const userId = req.userId; const getSupplier = `SELECT COUNT(supplier_id) as total_suppliers FROM suppliers WHERE user_id = ?`; db.query(getSupplier, [userId], (error, results) => { if(error || results.length === 0) { return res.status(404).json(error); } return res.status(200).json(results); }); }; const getClients = async (req, res) => { const userId = req.userId; const getClient = `SELECT COUNT(client_id) as total_clients FROM clients WHERE user_id = ?`; db.query(getClient, [userId], (error, results) => { if(error || results.length === 0) { return res.status(404).json(error); } return res.status(200).json(results); }); }; const getInventory = async (req, res) => { const userId = req.userId; const getInventoryQuery = `SELECT SUM(quantity) as total_inventory_items FROM inventory WHERE user_id = ?`; db.query(getInventoryQuery, [userId], (error, results) => { if(error || results.length === 0) { return res.status(404).json(error); } return res.status(200).json(results); }); }; export { getPurchases, getSales, getPurchaseOrders, getSaleOrders, getSuppliers, getClients, getInventory } ``` ##Establishing Frontend and Backend Connection: To establish a connection between the frontend and backend in my project, I set up a secure and efficient database connection. I used the `mysql2` library for connecting to my MySQL database. To protect sensitive information, such as database credentials, I utilized environment variables through `dotenv`. By configuring the `mysql2` library's `createConnection` method, I established a connection to the database and exported this connection for use throughout the application. This setup ensured smooth communication between the frontend and backend services in my project. ```javascript import mysql2 from 'mysql2'; import dotenv from 'dotenv'; dotenv.config(); const db = mysql2.createConnection({ host: process.env.DB_HOST, user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_DATABASE }); export default db; ``` I have also set up a comprehensive routing system to manage all API interactions between the frontend and backend. Here's a summary of the routes and their purposes. - **Auth Routes (`authRoute`) :** - **POST / signup :** Handles user registration. The `signupController` processes new user signups. - **POST / login :** Manages user authentication. The `loginController` verifies user credentials and issues JWT tokens. ```javascript import express from 'express'; import {signup} from '../controllers/auth/signupController.js'; import { login } from '../controllers/auth/loginController.js'; const authRouter = express.Router(); authRouter.post('/signup', signup); authRouter.post('/login', login); export default authRouter; ``` - **User Route (`userRoute`) :** - **GET /user :** Fetches user details with `getUserDetails`, requiring token-based authentication. ```javascript import express from 'express'; import { getUserDetails } from '../controllers/api/userController.js'; import { verifyToken } from '../controllers/auth/authMiddleware.js'; const userRouter = express.Router(); userRouter.get("/user", verifyToken, getUserDetails); export default userRouter; ``` - **Client Routes (`clientRoute`) :** - **POST /clients/form :** Creates a new client. The `postClients` function processes the addition of new clients, protected by `verifyToken`, - **PUT /clients/update :** Updates existing client details using `updateClients`, with token verification. - **GET /clients :** Retrieves client data via `getClients`, requiring authentication. ```javascript import express from "express"; import { getClients, postClients, updateClients, } from "../controllers/api/clientsController.js"; import { verifyToken } from "../controllers/auth/authMiddleware.js"; const clientRouter = express.Router(); clientRouter.post("/clients/form", verifyToken, postClients); clientRouter.put("/clients/update", verifyToken, updateClients); clientRouter.get("/clients", verifyToken, getClients); export default clientRouter; ``` - **Supplier Routes (`saleRoute`) :** - **POST /suppliers/form :** Adds new suppliers using `postSuppliers`, with token-based access. - **PUT /suppliers/update :** Updates supplier information via `updateSuppliers`, requiring authentication. - **GET /suppliers :** Retrieves supplier data using `getSuppliers`, protected by `verifyToken`. ```javascript import express from "express"; import { getSuppliers, postSuppliers, updateSuppliers, } from "../controllers/api/suppliersController.js"; import { verifyToken } from "../controllers/auth/authMiddleware.js"; const supplierRouter = express.Router(); supplierRouter.post("/suppliers/form", verifyToken, postSuppliers); supplierRouter.put("/suppliers/update", verifyToken, updateSuppliers); supplierRouter.get("/suppliers", verifyToken, getSuppliers); export default supplierRouter; ``` - **Product Route (`productRoute`) :** - **POST /products/form :** Adds new products with `postProduct`, requiring user authentication. - **PUT /products/update :** Updates product details via `updateProducts`, with token verification. - **GET /products :** Lists all products using `getProducts`, protected by `verifyToken`. ```javascript import express from "express"; import { getProducts, postProduct, updateProducts, } from "../controllers/api/productsController.js"; import { verifyToken } from "../controllers/auth/authMiddleware.js"; const productRouter = express.Router(); productRouter.post("/products/form", verifyToken, postProduct); productRouter.put("/products/update", verifyToken, updateProducts); productRouter.get("/products", verifyToken, getProducts); export default productRouter; ``` - **Purchase Routes (`purchaseRoute`) :** - **POST /purchase/form :** Creates a new purchase entry. `postPurchase` handles this, with token-based access. - **GET /purchases :** Retrieves purchase data with `getPurchase`, requiring authentication. ```javascript import express from 'express'; import { postPurchase, getPurchase } from '../controllers/api/purchaseController.js'; import { verifyToken } from '../controllers/auth/authMiddleware.js'; const purchaseRouter = express.Router(); purchaseRouter.get("/purchases", verifyToken, getPurchase); purchaseRouter.post("/purchase/form", verifyToken, postPurchase); export default purchaseRouter; ``` - **Sale Route (`saleRoute`) :** - **POST /sale/form :** Handles new sale entries through `postSale`, protected by token verification. - **GET /sales :** Fetches sale data via `getSaleData`, with authentication. ```javascript import express from 'express'; import { postSale, getSaleData } from '../controllers/api/saleController.js'; import { verifyToken } from '../controllers/auth/authMiddleware.js'; const saleRouter = express.Router(); saleRouter.post("/sale/form", verifyToken, postSale); saleRouter.get("/sales", verifyToken, getSaleData); export default saleRouter; ``` - **Inventory Route (`inventoryRoute`) :** - **GET /dashboard/purchase :** Fetches purchase data. - **GET /dashboard/sales :** Retrieves sales data. - **GET /dashboard/purchaseorders :** Gets purchase order details. - **GET /dashboard/saleorders :** Accesses sale order information. - **GET /dashboard/suppliers :** Obtains supplier data. - **GET /dashboard/clients :** Retrieves client information. - **GET /dashboard/inventory :** Fetches overall inventory data. ```javascript import express from 'express'; import { getPurchases, getSales, getPurchaseOrders, getSaleOrders, getSuppliers, getClients, getInventory } from '../controllers/api/inventoryController.js'; import { verifyToken } from '../controllers/auth/authMiddleware.js'; const inventoryRouter = express.Router(); inventoryRouter.get("/dashboard/purchase", verifyToken, getPurchases); inventoryRouter.get("/dashboard/sales", verifyToken, getSales); inventoryRouter.get("/dashboard/purchaseorders", verifyToken, getPurchaseOrders); inventoryRouter.get("/dashboard/saleorders", verifyToken, getSaleOrders); inventoryRouter.get("/dashboard/suppliers", verifyToken, getSuppliers); inventoryRouter.get("/dashboard/clients", verifyToken, getClients); inventoryRouter.get("/dashboard/inventory", verifyToken, getInventory); export default inventoryRouter; ``` I used `express` to create the server and configured it to handle JSON requests using `body-parser`. I also enabled Cross-Origin Resource Sharing (CORS) to allow the frontend to communicate with the backend. Here's my index.js ```javascript import express from 'express'; import cors from 'cors'; import bodyParser from 'body-parser'; import authRouter from './src/routes/authRoute.js'; import userRouter from './src/routes/userRoute.js'; import clientRouter from './src/routes/clientRoute.js'; import supplierRouter from './src/routes/supplierRoute.js'; import productRouter from './src/routes/productRoute.js'; import purchaseRouter from './src/routes/purchaseRoute.js'; import saleRouter from './src/routes/saleRoute.js'; import inventoryRouter from './src/routes/inventoryRoute.js'; const app = express(); const PORT = process.env.PORT || 3000; app.use(bodyParser.json()); app.use(express.json()); app.use(cors()); app.use('/auth', authRouter); app.use('/api', userRouter); app.use('/api', clientRouter); app.use('/api', supplierRouter); app.use('/api', productRouter); app.use("/api", purchaseRouter); app.use("/api", saleRouter); app.use("/api", inventoryRouter); app.get("/", (req, res) => { res.json("Hello this is invento's backend!!!"); }); app.listen(PORT, () => { console.log(`Connected to Invento Backend ${PORT}`); }); ``` ##Frontend Component Overview: The frontend of the Invento application is built using React, Redux, Tailwind CSS, and Daisy UI. This section provides an overview of the main components and their roles within the application. - **Redux Store Configuration :** The Redux store is configured using `@reduxjs/toolkit` to manage the state of the Invento application efficiently. The store consolidates various slices of state, each managed by its respective reducer. - **Store Configuration :** The store is created using `configureStore` from `@reduxjs/toolkit`. This method simplifies the setup by automatically configuring the Redux DevTools and applying middleware. ```javascript import { configureStore } from "@reduxjs/toolkit"; import authReducer from "../slices/auth/authSlice"; import dashboardReducer from "../slices/api/dashboardSlice"; import productReducer from "../slices/api/productSlice"; import supplierReducer from "../slices/api/supplierSlice"; import clientReducer from "../slices/api/clientSlice"; import purchaseReducer from "../slices/api/purchaseSlice"; import saleReducer from "../slices/api/saleSlice"; import userReducer from "../slices/api/userSlice"; export const store = configureStore({ reducer: { auth: authReducer, dashboard: dashboardReducer, products: productReducer, suppliers: supplierReducer, clients: clientReducer, purchases: purchaseReducer, sales: saleReducer, users: userReducer, }, }); ``` - **App Component :** The `App` component serves as the root of the application. It handles routing and renders different components based on the current route. ```javascript import { BrowserRouter as Router, Routes, Route } from "react-router-dom"; import Signup from "./components/Auth/Signup"; import Login from "./components/Auth/Login"; import MainLayout from "./components/Home/MainLayout"; import Dashboard from "./components/Home/Dashboard"; import Purchase from "./components/Home/purchase/Purchase"; import Sales from "./components/Home/sale/Sales"; import Product from "./components/Home/product/Product"; import Suppliers from "./components/Home/suppliers/Suppliers"; import Clients from "./components/Home/clients/Clients"; import ProtectedRoute from "./components/ProtectedRoute"; const App = () => { return ( } /> } /> } > } /> } /> } /> } /> } /> } /> ); }; export default App; ``` - **Auth Components :** - **Signup Component :** The `Signup` component provides a form for new users to create an account. It uses Redux to handle form submission and state management. ![signup component](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bh9z0t4zilwz60ssc5a6.png) - **Login Component :** The `Login` component provides a form for existing users to log in. It also uses Redux for form submission and state management. ![login component](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qp00dvemi5eqvzlqvcer.png) - **Dashboard Component :** The Dashboard component is a protected route that is accessible only to authenticated users. It serves as the main interface for managing inventory, suppliers, clients, purchases, and sales. ![dashboard component](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/whyh35whisbw85gaw895.png) - **Client Component :** The `Clients` component allow users to view, add, and update client information. ![client form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y47npvn2e0fbceoy8a5y.png) ![clients list view](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wojjts0aec8grnzui6lx.png) - **Supplier Component :** The `Suppliers` component allows users to view, add, and update supplier information. ![supplier form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l74e17raknfsxcig4xev.png) ![suppliers list view](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fhqn27s7n1g1kgnyr5tm.png) - **Product Component :** The Products component allows users to view, add, and update product information. ![product form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eh47p0fresemasb6gxah.png) ![products list view](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ot2qdj23a8q7qzkarek7.png) - **Purchase Components :** The `Purchases` component allows users to view and manage purchase orders. ![purchase form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xo5c8t5xpxin535mfj81.png) ![purchases list view](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hbuk7dbfb6i76febo604.png) - **Sale Components :** The `Sales` component allows users to view and manage sales orders. ![sale form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cudq9e5vfqfj55jjw1tt.png) ![sales list view](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0e5ixkrn8g3nbrr06j0m.png) - **MainLayout Components :** The `MainLayout` component is a central layout for the Invento application. It provides the main navigation and structure for the app, ensuring a consistent user experience across different sections. ```javascript import { Outlet, Link } from "react-router-dom"; import invento from "../../../public/images/invento-logo.png"; import { MdOutlineLogout } from "react-icons/md"; import { useEffect } from "react"; import { useDispatch, useSelector } from "react-redux"; import { fetchUser } from "../../slices/api/userSlice"; import { logout } from "../../slices/auth/authSlice"; const MainLayout = () => { const dispatch = useDispatch(); const { users } = useSelector((state) => state.users); useEffect(() => { dispatch(fetchUser()); }, [dispatch]); const handleLogout = () => { dispatch(logout()); }; const { username = "", email = "" } = users || {}; const truncateString = (str, maxLength) => { if (str.length <= maxLength) { return str; } return str.substring(0, maxLength) + "..."; }; const truncatedEmail = truncateString(email, 14); const truncatedUser = truncateString(username, 14); return (

Dashboard
Products
Suppliers
Clients
Purchases
Sales

{username[0] && username[0].toUpperCase()}

{truncatedUser}

{truncatedEmail}

); }; export default MainLayout; ``` - **ProtectedRoute Component :** The `ProtectedRoute` component ensures that only authenticated users can access certain routes. If the user is not authenticated, they are redirected to the login page. ```javascript import React from "react"; import { useSelector } from "react-redux"; import { Navigate } from "react-router-dom"; const ProtectedRoute = ({ children }) => { const { token } = useSelector((state) => state.auth); if (!token) { return ; } return children; }; export default ProtectedRoute; ``` ##User Interface Design: The user interface (UI) design is crafted using Tailwind CSS and Daisy UI. These libraries provide a powerful combination for building responsive, visually appealing, and functional components with ease. - **Tailwind CSS :** [Tailwind CSS](https://tailwindcss.com/) is a utility-first CSS framework that allows you to rapidly build custom user interfaces without leaving your HTML. It provides a wide range of pre-defined classes to handle layout, spacing, typography, color, and more. ![Tailwind CSS](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fzvqptoxqt8j06fm4y0u.png) - **Daisy UI :** [daisyUI](https://daisyui.com/) is a plugin for Tailwind CSS that offers a set of accessible, customizable UI components. It extends Tailwind's functionality by providing pre-styled components, which makes it easier to maintain a consistent design throughout the application. ![daisyUI](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e4wuvu4l8thqznsdc77b.png) - **React Icons :** I have also used [`react-icons`](https://react-icons.github.io/react-icons/) which is a library that provides a wide range of icons from popular icon libraries such as Font Awesome, Material Icons, and more. ![React Icons](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q04qwj3759qj55i9ic9i.png) - **React Hook Form :** I have used for [React Hook Form](https://react-hook-form.com/) which is a library for managing form state and validation in React applications. It leverages React's hooks API to provide a seamless and efficient way to handle form inputs. ![React Hook Form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y04vxedkgahkr940r63k.png) - **Tanstack Table :** I integrated [Tanstack Table](https://tanstack.com/table/latest) into the application. This library allowed me to build and test interactive tables with advanced features like sorting, filtering, and pagination. ![Tanstack Table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/roykkq8k21vuyg514p53.png) - **XLSX :** [XLSX](https://www.npmjs.com/package/xlsx) is a library for parsing and writing Excel spreadsheet files. It enables the application to export data to Excel, which is a common requirement for inventory management systems. ![XLSX](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k2zb00wryy5vzs8m7mg5.png) ##Integration Testing: For integration testing of the invento application, I used Postman to thoroughly test API endpoints. This process involved sending requests to the server and verifying that the responses were correct, ensuring that the different parts of the application integrated seamlessly. ![Postman](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4lfhca1ryi7kpu0zz9sb.png) - **Endpoint Testing :** I created and executed various requests to test the functionality of each API endpoint, checking for accurate data handling and appropriate response codes. - **Response Validation :** I validated that the API returned the expected results and handled edge cases properly, including error scenarios. This comprehensive approach helped ensure that the API endpoints functioned correctly and integrated well with the rest of the application. ##Optimizations and Adjustments: Throughout the development of the Invento application, several optimizations and adjustments were implemented to enhance performance, maintainability, and user experience. Below are some key areas of focus: - **Form handling and Validation :** For efficient form handling and validation, React Hook Form was used. This library minimizes re-renders and improves performance, especially in large forms. ![large react form handling](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eja812uugs6jynuwljfu.png) - **Error Handling and User Feedback :** To provide clear feedback to users, comprehensive error handling was implemented across the application. Success and error toasts were added using Daisy UI's toast components, enhancing user experience by providing immediate feedback on actions. ![Login form validation](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/71wps28e9fxlkfpkajfw.png) ![succes toast](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mwit2sq9eqd27tpms0hn.png) ![failure toast](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ah0s2x34btcuvku4dur4.png) - **Data Export Functionality :** An export to Excel feature was added using the `XLSX` library. This allows users to export inventory data to an Excel file, providing a valuable tool for data analysis and reporting. - **API Integration and Caching :** API endpoints were optimized for better performance, and caching mechanisms were implemented where appropriate to reduce the load on the server and improve response times. - **Code Quality and Maintainability :** To ensure code quality and maintainability, ESLint and Prettier were integrated into the development workflow. This helps in maintaining a consistent code style and catching potential issues early. - **Security Enhancements :** Security measures were put in place, including input validation, authentication, and authorization checks, to protect the application from common vulnerabilities. By focusing on these optimizations and adjustments, the Invento application was made more robust, user-friendly, and performant, ensuring a high-quality experience for its users. ##Conclusion: In conclusion, developing the Invento inventory management system was a comprehensive and rewarding experience. I meticulously crafted both the frontend and backend components to ensure a seamless and efficient application. Utilizing technologies such as React, Tailwind CSS, Daisy UI, and various libraries like react-icons, react-hook-form, and xlsx, I was able to build a functional user interface. On the backend, I leveraged Express.js for server management and MySQL for database interactions, ensuring robust data handling and API performance. Throughout the development process, I focused on optimizing the application for performance and usability, making adjustments as needed to enhance its functionality. Integration testing with Postman allowed me to ensure that all API endpoints worked as intended, contributing to a stable and reliable application. Looking ahead, I am excited about the potential future enhancements for Invento. These include advanced features like automated reports, real-time updates, and improved user experience enhancements. This documentation not only captures the details of the development journey but also serves as a foundation for future improvements. Overall, building Invento was a valuable learning experience that reinforced my skills in full-stack development and provided me with insights into creating a well-rounded application. If you wish to delve deeper into the project's structure and see the code in action, you can visit my [Invento repository](https://github.com/FanusAlameen/invento-app). I am eager to continue refining the system and exploring new features to further enhance its capabilities.

fanus

Building an inventory management app: 'Invento' as a Beginner Developer

Join Our Newsletter. No Spam, Only the good stuff.