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

Fanus — Wed, 24 Jul 2024 12:01:15 +0000

Introduction
Key Features
Database Design
API Development with Express and Node
Establishing Frontend and Backend Connection
Frontend Component Overview
User Interface Design
Integration Testing
Optimizations and Adjustments
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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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 (
    <Router>
      <Routes>
        <Route path="/signup" element={<Signup />} />
        <Route path="/login" element={<Login />} />
        <Route
          path="/"
          element={
            <ProtectedRoute>
              <MainLayout />
            </ProtectedRoute>
          }
        >
          <Route path="/" element={<Dashboard />} />
          <Route path="purchase" element={<Purchase />} />
          <Route path="sale" element={<Sales />} />
          <Route path="product" element={<Product />} />
          <Route path="suppliers" element={<Suppliers />} />
          <Route path="clients" element={<Clients />} />
        </Route>
      </Routes>
    </Router>
  );
};

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.

Login Component : The Login component provides a form for existing users to log in. It also uses Redux for form submission and state management.

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.

Client Component : The Clients component allow users to view, add, and update client information.

Supplier Component : The Suppliers component allows users to view, add, and update supplier information.

Product Component : The Products component allows users to view, add, and update product information.

Purchase Components : The Purchases component allows users to view and manage purchase orders.

Sale Components : The Sales component allows users to view and manage sales orders.

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.

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 (
    <div className="min-h-screen flex">
      <div className="drawer lg:drawer-open w-1/4">
        <input id="my-drawer-2" type="checkbox" className="drawer-toggle" />
        <div className="drawer-side">
          <label
            htmlFor="my-drawer-2"
            aria-label="close sidebar"
            className="drawer-overlay"
          ></label>
          <ul className="menu bg-base-300 p-5 w-80 min-h-full flex flex-col gap-5 text-base-content font-semibold text-md font-mont">
            <div className="w-full mb-6 flex items-center justify-center">
              <img className="w-[90px] h-[120px]" src={invento} alt="logo" />
            </div>

            <li>
              <Link to="/">Dashboard</Link>
            </li>
            <li>
              <Link to="/product">Products</Link>
            </li>
            <li>
              <Link to="/suppliers">Suppliers</Link>
            </li>
            <li>
              <Link to="/clients">Clients</Link>
            </li>
            <li>
              <Link to="/purchase">Purchases</Link>
            </li>
            <li>
              <Link to="/sale">Sales</Link>
            </li>

            <div className="w-full p-5 bg-neutral rounded-lg flex items-center gap-4 mt-auto">
              <h1 className="font-mont text-lg text-base-content bg-base-200 px-4 py-2 rounded-md">
                {username[0] && username[0].toUpperCase()}
              </h1>

              <div className="flex flex-col gap-1">
                <p className="font-mont text-md">{truncatedUser}</p>
                <p>{truncatedEmail}</p>
              </div>

              <button
                className="p-2 bg-base-200 rounded-full flex justify-center"
                onClick={handleLogout}
              >
                <MdOutlineLogout className="text-xl" />
              </button>
            </div>
          </ul>
        </div>
      </div>

      <div className="w-full p-5 ">
        <Outlet />
      </div>
    </div>
  );
};

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.

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 <Navigate to="/login" />;

  }

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 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.

Daisy UI : daisyUI 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.

React Icons : I have also used 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 Hook Form : I have used for React Hook Form 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.

Tanstack Table : I integrated Tanstack Table into the application. This library allowed me to build and test interactive tables with advanced features like sorting, filtering, and pagination.

XLSX : 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.

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.

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.

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.

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.

I am eager to continue refining the system and exploring new features to further enhance its capabilities.

DEV Community: Fanus