SMS DAB Enterprise
Student Exam Guide

This is a Store Management System (SMS) built for DAB Enterprise LTD — a company in Kigali City that sells building tools and construction materials. The system is a web application that replaces their manual paper-based tracking. It allows the company to:

• Record items received into the store (Stock In) — products like Cement, Steel bars, Ceramic tiles, etc.
• Record items issued out of the store (Stock Out)
• Track which employee (User) performed each transaction
• Generate a daily stock status report showing remaining quantities for every item
• Manage user accounts with secure login

It's built with Node.js + Express on the backend and React.js + Tailwind CSS on the frontend, with a MySQL database.

The system uses JWT (JSON Web Token) authentication, not session-based authentication.

How it works step by step:

• User enters username and password on the login form
• Frontend sends a POST request to /api/auth/login
• Backend fetches the user from MySQL, compares the password to the bcrypt hash using bcrypt.compare()
• If correct, the server signs a JWT token with jwt.sign({ id, username }, JWT_SECRET, { expiresIn: '8h' })
• Token is sent back and stored in localStorage as sms_token
• Every future request includes the header Authorization: Bearer <token>
• The auth middleware in middleware/auth.js verifies it with jwt.verify()

Why JWT? It is stateless — the server doesn't need to store sessions. It is scalable and the standard for REST APIs.

There are 3 ways to test the endpoints:

1. Through the Frontend UI (easiest to show inspector):

• Open http://localhost:5173 in the browser
• Register an account, then login
• Use the forms on Stock In and Stock Out pages
• Check the Reports page to see the result

2. Using Browser DevTools:

• Open DevTools (F12) → Network tab
• Every API call made by the frontend shows here with request/response details

3. Direct API test:

• Open http://localhost:5000/health in browser → should show {"ok":true}
• Open http://localhost:5000/api/stockin without token → should show {"error":"Unauthorized"}

In src/db.js, the ensureDbAndTables() function runs at server startup. It:

• First connects to MySQL without specifying a database
• Runs CREATE DATABASE IF NOT EXISTS SMS
• Then creates a connection pool to the SMS database
• Runs CREATE TABLE IF NOT EXISTS users (...)
• Runs CREATE TABLE IF NOT EXISTS stock_in (...)
• Runs CREATE TABLE IF NOT EXISTS stock_out (...) with foreign keys to both stock_in and users

This means the examiner only needs to have MySQL running — they don't need to create any database or tables manually. The app creates everything automatically.

The database has 3 tables (entities):

• users — id (PK), User_Name, Password, created_at
• stock_in — id (PK), ItemName, Description, QuantityIn, TotalQuantityIn, SupplierName, StockInDate, userId (FK → users.id), created_at
• stock_out — id (PK), QuantityOut, TotalQuantityOut, StockOutDate, stockInId (FK → stock_in.id), userId (FK → users.id), created_at

Relationships:

• One User can record many StockIn records (1:N)
• One User can record many StockOut records (1:N)
• One StockIn record can have many StockOut records (1:N) — e.g. Cement received in bulk can be issued out multiple times

Both foreign keys in stock_out use ON DELETE CASCADE, meaning if a parent record is deleted, the child records are deleted too.

Search works on both Stock In and Stock Out pages.

On the frontend: There's a search input field. As the user types and submits, the search term is passed as a query parameter to the API, e.g. GET /api/stockin?search=cement

On the backend (stockIn.js): If a search param exists, the SQL query adds a WHERE clause using LIKE: The %cement% pattern matches any record containing the word "cement" anywhere in the field. For Stock Out, search works on ItemName and User_Name.

The report is generated by the GET /api/reports/stock-status endpoint in src/routes/reports.js.

It runs a single SQL query that:

• JOINs stock_in with stock_out using LEFT JOIN (to include items with zero stock-out)
• Groups by ItemName
• Calculates SUM(QuantityIn) as TotalQuantityReceived
• Calculates COALESCE(SUM(QuantityOut), 0) as TotalQuantityIssuedOut (0 if never issued)
• Subtracts to get RemainingQuantity

The frontend ReportsPage fetches this and displays it as a styled table.

Difficulty 1: Calculating TotalQuantityIn automatically
The challenge was that TotalQuantityIn should be a running cumulative total for the same item across all transactions, not just the current record. I solved this by using a MySQL transaction — before inserting, I query SELECT COALESCE(SUM(QuantityIn),0) FROM stock_in WHERE ItemName=? to get the existing total, then add the new quantity.

Difficulty 2: Preventing over-issuing stock
When recording Stock Out, the system needs to verify there's enough stock remaining. I solved this by querying TotalQuantityIn and comparing against existing TotalQuantityOut before allowing the insert. If QuantityOut > remaining, the API returns a 400 error with the exact remaining quantity.

Difficulty 3: CORS between frontend and backend
The frontend runs on port 5173 and backend on 5000. Browsers block cross-origin requests by default. I fixed this by configuring CORS in server.js: cors({ origin: 'http://localhost:5173', credentials: true }).

The project uses 3 React hooks:

useState: Manages local component state. Used for:

• Form field values (e.g. the Add StockIn form)
• The list of records fetched from API
• Loading states and error messages

useEffect: Runs side effects. Used to:

• Fetch all stock records when the page first loads (useEffect(() => { fetchData() }, []))
• Re-fetch data after create/update/delete operations

useContext (via useAuth custom hook): Reads authentication state from AuthContext. Used in:

• Navbar — to show the logged-in username and logout button
• LoginPage — to call the login() function after successful login
• App.jsx Protected component — to check if user exists before allowing access

The app uses React Router v6 (installed as react-router-dom).

In App.jsx:

• BrowserRouter wraps the entire app to enable URL-based routing
• Routes and Route components map URLs to page components
• /login → LoginPage (public)
• /stockin, /stockout, /reports → wrapped in <Protected>
• Protected component checks useAuth().user — if null, redirects to /login with <Navigate>

The Navbar uses <Link to="/stockin"> for client-side navigation (no full page reload).

The .env file stores environment-specific configuration as key-value pairs. It is loaded by the dotenv package when the server starts (require('dotenv').config()).

Variables stored:

• DB_HOST — MySQL server hostname (localhost)
• DB_USER — MySQL username (root)
• DB_PASSWORD — MySQL password
• DB_NAME — Database name (SMS)
• DB_PORT — MySQL port (3306)
• JWT_SECRET — Secret key used to sign JWT tokens
• PORT — Port the Express server listens on (5000)

Why? So passwords and secrets are not hardcoded in source files. Different environments (development, production) can have different values without changing code.

The backend prevents this at the API level. In src/routes/stockOut.js, the POST endpoint:

• Fetches the StockIn record to get TotalQuantityIn
• Queries total already issued: SELECT SUM(QuantityOut) FROM stock_out WHERE stockInId=?
• Calculates remaining = TotalQuantityIn - existingTotal
• If QuantityOut > remaining → returns HTTP 400 with error message: "Insufficient stock. Remaining: X"

The frontend also shows this error message to the user. The database transaction is rolled back so no partial data is saved.

CORS stands for Cross-Origin Resource Sharing. Browsers enforce a security policy that blocks JavaScript from making requests to a different origin (different port = different origin).

The problem: The React frontend runs on localhost:5173 (Vite dev server). The Express backend runs on localhost:5000. When the frontend tries to call the backend, the browser blocks it by default.

The solution: In server.js, the CORS middleware is configured to allow requests from http://localhost:5173 specifically: This tells the browser: "it is safe to receive responses from this server even though the ports are different."

Installation: Tailwind was installed as a dev dependency along with PostCSS and Autoprefixer.

Configuration files:

• tailwind.config.js — tells Tailwind to scan ./src/**/*.{js,jsx} files for class names
• postcss.config.js — PostCSS plugin that processes Tailwind
• src/index.css — contains the 3 Tailwind directives: @tailwind base, @tailwind components, @tailwind utilities

Usage: Utility classes are applied directly in JSX, e.g.: Responsive classes like max-w-7xl, flex, grid, px-4, py-8 handle layout and spacing. Hover states like hover:bg-emerald-900 handle interactions.

Nodemon is a development tool that automatically restarts the Node.js server whenever a file changes. Without it, every time you edit backend code you would have to manually stop and restart the server.

It is installed as a devDependency in backend-project/package.json. The dev script in package.json uses it: For the exam, npm start (using plain node) is fine since we are not actively editing code.