Database System Project

A full-stack authentication system with user signup, login, OTP verification, and password reset functionality using Node.js, Express, PostgreSQL, and JWT.

Features

• ✅ User Registration - Multi-step signup with OTP verification
• ✅ Login - Username/password authentication with JWT tokens
• ✅ OTP Verification - Email-based OTP (no expiry)
• ✅ Forgot Password - 3-step password reset flow with OTP
• ✅ Password Security - bcrypt hashing for all passwords
• ✅ JWT Authentication - Access tokens for protected routes
• ✅ Responsive UI - Bootstrap-style HTML forms with error handling

Project Structure

.
├── index.js                 # Main server entry point
├── db.js                    # PostgreSQL connection pool
├── package.json             # Dependencies
├── setup.sql               # Database schema
├── queries.sql             # SQL query playground
├── README.md               # This file
├── middleware/
│   └── authorization.js    # JWT authentication middleware
├── routes/
│   └── auth-routes.js      # All authentication routes
└── utils/
    ├── jwt-helpers.js      # JWT token generation
    ├── send_email.js       # OTP generation and email sending
    └── of_no_use.txt       # Deprecated utils

Installation

Prerequisites

• Node.js (v14+)
• PostgreSQL
• npm

Setup Steps

1. Clone the repository

   git clone <your-repo>
   cd DB\ Course\ Project

2. Install dependencies

   npm install

3. Create .env file in project root:

   EMAIL_USER=your-email@gmail.com
   EMAIL_PASS=your-app-password
   JWT_SECRET=your-jwt-secret-key
   DATABASE_URL=postgresql://user:password@localhost:5432/db_course_project

4. Create PostgreSQL database

   psql -U postgres -f setup.sql

5. Start the server

   node index.js

   Server runs on http://localhost:3000

API Routes

Authentication Routes

GET Routes (Serve HTML Pages)

Route	Description
GET /api/auth/login	Login page
GET /api/auth/signup	Signup page
GET /api/auth/forgot-password	Password reset page

POST Routes (API Endpoints)

1. User Registration - Step 1: Send OTP

POST /api/auth/signup-request
Body: {
  "username": "john_doe",
  "email": "john@example.com",
  "password": "password123",
  "confirm_password": "password123"
}
Response: { "message": "OTP sent successfully" }

2. User Registration - Step 2: Verify OTP & Create Account

POST /api/auth/verify-otp
Body: {
  "email": "john@example.com",
  "otp": "123456"
}
Response: { 
  "message": "Account created successfully",
  "user": { "id": 1, "username": "john_doe", "email": "john@example.com" }
}

3. User Login

POST /api/auth/login
Body: {
  "username": "john_doe",
  "password": "password123"
}
Response: { "accessToken": "jwt-token-here" }

4. Forgot Password - Step 1: Request OTP

POST /api/auth/login/forgot-password
Body: {
  "email": "john@example.com",
  "username": "john_doe"
}
Response: { "message": "OTP sent successfully" }

5. Forgot Password - Step 2: Verify OTP

POST /api/auth/verify-reset-otp
Body: {
  "email": "john@example.com",
  "otp": "123456"
}
Response: { "message": "OTP verified successfully. You can now reset your password." }

6. Forgot Password - Step 3: Reset Password

POST /api/auth/reset-password
Body: {
  "email": "john@example.com",
  "newPassword": "newpassword123",
  "confirmPassword": "newpassword123"
}
Response: { 
  "message": "Password reset successfully",
  "user": { "id": 1, "username": "john_doe", "email": "john@example.com" }
}

User Flows

Registration Flow

1. User visits /api/auth/signup
2. Enters username, email, password
3. Clicks "Send OTP"
4. OTP sent to email
5. User enters OTP
6. Clicks "Verify & Create Account"
7. Account created, redirected to login

Login Flow

1. User visits /api/auth/login
2. Enters username and password
3. Receives JWT access token
4. Token stored in browser for authenticated requests

Password Reset Flow

1. User visits /api/auth/forgot-password or clicks "Forgot password?" on login
2. Enters email and username
3. Clicks "Send OTP"
4. OTP sent to email
5. User enters OTP
6. Clicks "Verify OTP"
7. Enters new password
8. Password updated in database
9. Redirected to login page

Database Schema

Users Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Key Files Explained

auth-routes.js

Contains all authentication endpoints and HTML pages for:

• Login page
• Signup page (2-step)
• Forgot password page (3-step)

send_email.js

• generateOTP() - Generates 6-digit OTP
• send_and_generate_OTP(email) - Sends OTP via email
• verifyOTP(email, otp) - Verifies OTP (no expiry)
• storeSignupData(email, data) - Stores temporary signup data
• getSignupData(email) - Retrieves and clears signup data

jwt-helpers.js

• jwtToken(id, username) - Generates access and refresh tokens

OTP System

• No Expiry: OTPs remain valid until used
• In-Memory Storage: Uses Map() (production should use Redis/DB)
• Email-Based: Sent via Gmail (requires app-specific password)
• 6-Digit: Random 6-digit code

Security Notes

• ⚠️ Change JWT_SECRET in production
• ⚠️ Use HTTPS in production
• ⚠️ For production, store OTPs in Redis with expiry
• ⚠️ Add rate limiting to prevent abuse

Dependencies

{
  "express": "^4.x.x",
  "pg": "^8.x.x",
  "bcrypt": "^5.x.x",
  "jsonwebtoken": "^9.x.x",
  "nodemailer": "^6.x.x",
  "dotenv": "^16.x.x"
}

Usage Example

# Start server
node index.js

# Open browser
http://localhost:3000

# Click Login
http://localhost:3000/api/auth/login

# Or Sign Up
http://localhost:3000/api/auth/signup

Author

DB Course Project Team