SupplyTrack Backend

SupplyTrack Backend is the Express and MariaDB/MySQL API for the SupplyTrack student-learning project. It teaches how a real ordering system can handle authentication, roles, products, carts, orders, pickup stations, and order tracking history.

This backend is intentionally written in normal JavaScript with clear files and direct SQL queries. The goal is for students to understand the flow before learning heavier abstractions.

What Students Will Learn

• How an Express server is organized.
• How routes connect URLs to controller functions.
• How controllers read request data, validate it, and run database queries.
• How JWT authentication protects private routes.
• How role-based access control keeps users inside their allowed dashboards.
• How bcrypt stores hashed passwords instead of plain text passwords.
• How Multer accepts product image uploads.
• How a cart becomes one or more supplier orders.
• How order status history is stored in an order_tracking table.
• How to seed a database with realistic test data.

Tech Stack

• Node.js
• Express.js
• MariaDB or MySQL
• mysql2
• JWT with jsonwebtoken
• bcrypt
• Multer
• dotenv
• CORS

Prerequisites

Install these before starting:

• Node.js
• npm
• MariaDB or MySQL server
• MariaDB/MySQL command-line client

Check your tools:

node -v
npm -v
mysql --version

On some machines the SQL client is named mariadb instead of mysql.

Folder Structure

supplytrack-backend/
  config/
    db.js
  controllers/
  middleware/
  routes/
  uploads/
    item-images/
  database/
    schema.sql
    seed.sql
  server.js
  package.json
  .env.example
  README.md

Important folders:

• config/db.js: creates the database connection pool.
• controllers/: contains the main business logic and SQL queries.
• middleware/: reusable request checks such as JWT auth, role checks, async error handling, and uploads.
• routes/: maps API endpoints to controller functions.
• uploads/item-images/: stores uploaded item images.
• database/schema.sql: creates all tables.
• database/seed.sql: inserts sample users, products, carts, orders, and tracking records.
• server.js: creates the Express app and mounts all routes.

Environment Variables

Copy the example file:

cp .env.example .env

Default .env.example values:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=root
DB_NAME=supplytrack_db
DB_PORT=3306
JWT_SECRET=supplytrack_student_secret
PORT=5000

Do not hardcode database credentials in source code. Controllers and config files read settings from process.env.

If port 5000 is already being used on your machine, change PORT in .env, then update the frontend VITE_API_URL to match.

Install Dependencies

From this backend folder:

npm install

Database Setup

Log in to MariaDB/MySQL:

mysql -uroot -proot

Create the database:

CREATE DATABASE IF NOT EXISTS supplytrack_db;
EXIT;

Import the schema and seed data from this folder:

mysql -uroot -proot supplytrack_db < database/schema.sql
mysql -uroot -proot supplytrack_db < database/seed.sql

The schema file drops and recreates tables. Running it again resets the database.

Verify seeded data:

mysql -uroot -proot supplytrack_db -e "SELECT id, full_name, email, role FROM users;"
mysql -uroot -proot supplytrack_db -e "SELECT COUNT(*) AS item_count FROM items;"

Expected seed data includes:

• 1 admin
• 3 customers
• 3 suppliers
• 2 pickup station officers
• 3 pickup stations
• 6 item categories
• 12 sample items
• sample carts
• sample orders
• sample order tracking records

Run the Backend

Development mode:

npm run dev

Production-style start:

npm start

Open this URL to confirm the API is running:

http://localhost:5000

Expected response:

{ "message": "SupplyTrack API is running" }

Default Login Accounts

All seeded accounts use this password:

password123

Accounts:

• Admin: admin@supplytrack.test
• Customer: customer@supplytrack.test
• Supplier: supplier@supplytrack.test
• Pickup Station Officer: station@supplytrack.test

Request Flow

A typical protected request works like this:

1. The user logs in through POST /api/auth/login.
2. The backend checks the email and bcrypt password hash.
3. The backend returns a JWT.
4. The frontend sends the token in the Authorization header.
5. authMiddleware.js verifies the token and sets req.user.
6. roleMiddleware.js checks whether the user has the correct role.
7. The controller runs the database query and returns JSON.

Authentication

The login response includes a JWT. Protected routes expect this header:

Authorization: Bearer your_token_here

The JWT contains basic user data such as user id, email, and role. It should not contain passwords or private secrets.

Role-Based Access

The system has four roles:

• admin
• customer
• supplier
• station_officer

Examples:

• Customers can manage only their own cart and orders.
• Suppliers can manage only their own items and supplier orders.
• Station officers can update only orders assigned to their station.
• Admins can view and manage system-wide records.

Image Uploads

Supplier item routes accept one file field named image.

Examples:

• POST /api/items
• PUT /api/items/:id

Uploaded files are stored in:

uploads/item-images/

They are served publicly from:

/uploads/item-images/file-name.jpg

Order Status Flow

SupplyTrack uses this order movement:

Pending
Accepted
Rejected
Packed
In Transit to Pickup Station
Arrived at Pickup Station
Ready for Pickup
Picked Up
Completed
Cancelled

Normal successful flow:

Pending -> Accepted -> Packed -> In Transit to Pickup Station -> Arrived at Pickup Station -> Ready for Pickup -> Picked Up -> Completed

Special endings:

• Rejected: supplier rejects the order.
• Cancelled: customer cancels before the order moves too far.

Tracking History

The orders table stores the current status.

The order_tracking table stores the full history of status changes. Each tracking row contains:

• order id
• status
• description
• user who made the update
• current location
• timestamp

This is useful because students can learn the difference between current state and historical records.

Main API Endpoints

Auth:

• POST /api/auth/register
• POST /api/auth/login
• GET /api/auth/me

Items:

• GET /api/items
• GET /api/items/:id
• POST /api/items
• PUT /api/items/:id
• DELETE /api/items/:id
• GET /api/items/supplier/my-items

Cart:

• GET /api/cart
• POST /api/cart/items
• PUT /api/cart/items/:id
• DELETE /api/cart/items/:id
• DELETE /api/cart/clear

Orders:

• POST /api/orders
• GET /api/orders/my-orders
• GET /api/orders/:id
• PUT /api/orders/:id/cancel
• PUT /api/orders/:id/confirm-received

Supplier Orders:

• GET /api/supplier/orders
• PUT /api/supplier/orders/:id/accept
• PUT /api/supplier/orders/:id/reject
• PUT /api/supplier/orders/:id/packed
• PUT /api/supplier/orders/:id/send-to-station

Pickup Station:

• GET /api/pickup-stations
• GET /api/station/orders
• PUT /api/station/orders/:id/arrived
• PUT /api/station/orders/:id/ready
• PUT /api/station/orders/:id/picked-up

Tracking:

• GET /api/tracking/order/:orderId

Profiles:

• GET /api/profile
• PUT /api/profile/supplier
• PUT /api/profile/customer

Categories:

• GET /api/categories
• POST /api/categories
• PUT /api/categories/:id

Admin:

• GET /api/admin/stats
• GET /api/admin/users
• GET /api/admin/items
• GET /api/admin/orders
• GET /api/admin/pickup-stations
• POST /api/admin/pickup-stations
• PUT /api/admin/pickup-stations/:id
• PUT /api/admin/users/:id/status

Try Login with curl

curl -X POST http://localhost:5000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"customer@supplytrack.test","password":"password123"}'

If login works, the response includes:

• message
• token
• user

Common Problems

Unknown database 'supplytrack_db'

Run the database setup commands again. This means the database has not been created or imported.

Access denied for user 'root'

Check DB_USER and DB_PASSWORD in .env.

ECONNREFUSED

MariaDB/MySQL is probably not running, or the backend port does not match the frontend URL.

Frontend login shows ERR_CONNECTION

Make sure the backend is running and the frontend .env uses the same backend port.

Uploaded images do not show

Check that VITE_UPLOADS_URL in the frontend points to the backend host and port.

Suggested Learning Path

1. Start with server.js to see how Express is created.
2. Read routes/authRoutes.js, then controllers/authController.js.
3. Read middleware/authMiddleware.js to understand JWT verification.
4. Read middleware/roleMiddleware.js to understand role checks.
5. Study database/schema.sql to understand table relationships.
6. Follow a customer order from cart to order in orderController.js.
7. Follow status updates in supplierOrderController.js and stationController.js.
8. Query order_tracking directly in SQL to see the history.