My‑Auth (Node / MySQL) – Authentication System

A privacy‑first, production‑ready authentication backend built with Node.js, Express, MySQL, and Google OAuth.
It supports:

• Email + password sign‑up & login
• OTP verification for account activation and password reset (sent via e‑mail)
• Stateless JWT access tokens + httpOnly refresh‑token cookies (rotation)
• Google OAuth 2.0 login through Passport
• Secure defaults – rate limiting, input validation, bcrypt hashing, HTTPS‑only cookies

---

Table of Contents

1. Project structure
2. Prerequisites
3. Setup & configuration
4. Running the API
5. Authentication flow diagrams
6. API reference
7. Testing
8. Deployment (Docker)
9. Security checklist

---

Project structure

node-auth-system/
├─ src/
│  ├─ config/               # env‑based config (db, mail, passport)
│  ├─ models/               # raw‑SQL helpers (User, OTP, RefreshToken)
│  ├─ routes/               # Express routers (auth)
│  ├─ middlewares/          # validation, rate‑limit, JWT guard
│  ├─ services/             # business logic (AuthService, GoogleService)
│  ├─ utils/                # crypto (OTP, token hash) & mailer wrapper
│  └─ server.js             # Express app bootstrap
├─ .env                      # **never commit** – contains secrets
├─ package.json
├─ Dockerfile (optional)
└─ README.md (this file)

---

Prerequisites

Tool	Minimum version
Node.js	v18 (LTS)
npm	9
MySQL	8.0 (InnoDB)
Docker (optional)	20
Google Cloud console access (for OAuth)	–

---

Setup & configuration

1. Clone & install

git clone <repo‑url>
cd node-auth-system
npm install

2. Create a MySQL database

CREATE DATABASE auth_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE auth_system;
-- run the schema (see src/models/*.js for column definitions)

The schema is reproduced in db/schema.sql for convenience.

3. Environment variables (.env)

# Server
PORT=3000
JWT_SECRET=<<<generate‑a‑256‑bit‑hex‑string>>>
SESSION_SECRET=<<<random‑string>>>          # only for Passport OAuth flow

# MySQL
DB_HOST=localhost
DB_PORT=3306
DB_USER=your_mysql_user
DB_PASSWORD=your_mysql_password
DB_NAME=auth_system

# SMTP (for OTP emails)
MAIL_HOST=smtp.example.com
MAIL_PORT=587
MAIL_SECURE=false               # true if using port 465
MAIL_USER=your_smtp_user
MAIL_PASS=your_smtp_pass
MAIL_FROM=no-reply@yourdomain.com

# Google OAuth
GOOGLE_CLIENT_ID=your-google-client-id.apps.googleusercontent.com
GOOGLE_CLIENT_SECRET=your-google-client-secret
GOOGLE_CALLBACK_URL=http://localhost:3000/auth/google/callback

# Front‑end URLs (used for redirects)
CLIENT_ORIGIN=http://localhost:3001
CLIENT_SUCCESS_URL=http://localhost:3001/profile

Generate a strong JWT_SECRET (e.g., openssl rand -hex 32).

4. Initialise the database (once)

npm run db:init   # optional script that runs the SQL in db/schema.sql

---

Running the API

npm run dev          # nodemon – watches for changes
# or
npm start            # node src/server.js (production)

The server starts on http://localhost:3000 (or the PORT you set).

Health checks

Endpoint	Purpose
GET /	Simple “alive” message
GET /health-db	Returns { "db": 1 } if DB connection works
GET /auth/google	Starts Google OAuth flow

---

Authentication flow diagrams

1️⃣ Email + Password registration

Client → POST /auth/register
   ├─ Validate payload (email, password)
   ├─ Check e‑mail uniqueness (User model)
   ├─ bcrypt.hash(password)
   ├─ INSERT user (is_verified = false)
   ├─ generateOtp(10 min) → store in OTP table
   └─ sendMail(email, OTP)

Result: user receives a 6‑digit OTP.

2️⃣ OTP verification (activate or password‑reset)

Client → POST /auth/verify-otp {email, otp}
   ├─ Find user by e‑mail
   ├─ SELECT * FROM otps WHERE user_id=? AND otp_code=? AND used=FALSE AND expires_at>NOW()
   ├─ If found → UPDATE otps SET used=TRUE
   ├─ If account not verified → UPDATE users SET is_verified=TRUE
   ├─ create JWT access token (15 min)
   └─ issueRefreshToken() → store hashed token, return raw token

Result: access token returned in JSON, refresh token set as httpOnly cookie.

3️⃣ Email + Password login

Client → POST /auth/login {email, password}
   ├─ Fetch user row
   ├─ bcrypt.compare(password, stored_hash)
   ├─ Verify is_verified
   ├─ Issue JWT + refresh token (as above)
   └─ Set httpOnly refresh cookie

4️⃣ Google OAuth

Client → GET /auth/google
   └─ Passport redirects to Google consent page
Google → redirects back to /auth/google/callback
   └─ Passport extracts profile (id, email)
   └─ AuthService.handleGoogleCallback()
        ├─ Find user by google_id OR INSERT new user (verified)
        ├─ Issue JWT + refresh token
        └─ Set refresh cookie & redirect to CLIENT_SUCCESS_URL

5️⃣ Forgot password

Client → POST /auth/forgot-password {email}
   ├─ (silently) find user
   ├─ generate OTP, store, send mail
   └─ Respond with generic success message

6️⃣ Reset password

Client → POST /auth/reset-password {email, otp, newPassword}
   ├─ Validate OTP (same logic as verify‑otp)
   ├─ bcrypt.hash(newPassword)
   ├─ UPDATE users SET password_hash=?
   └─ Revoke ALL refresh tokens for that user

7️⃣ Token refresh

Client → POST /auth/refresh (refresh cookie automatically sent)
   ├─ Read raw refresh token from cookie
   ├─ hashToken(raw) → lookup in refresh_tokens (valid & not revoked)
   ├─ Revoke old token (rotation)
   ├─ Issue new access JWT + new refresh token
   └─ Set new refresh cookie, return access token

8️⃣ Logout

Client → POST /auth/logout
   ├─ Read refresh cookie
   ├─ Revoke that token in DB
   └─ Clear cookie → user must login again

---

API reference

Method	Path	Body (JSON)	Success response	Errors
POST	/auth/register	{email, password}	{message, userId} (201)	400 (validation), 409 (email taken)
POST	/auth/verify-otp	{email, otp}	{accessToken, refreshToken}	400 (invalid/expired OTP)
POST	/auth/login	{email, password}	{accessToken} (refresh token set as cookie)	401 (bad credentials), 403 (unverified)
GET	/auth/google	–	302 redirect to Google	–
GET	/auth/google/callback	–	302 → CLIENT_SUCCESS_URL (cookies set)	401 (OAuth failure)
POST	/auth/forgot-password	{email}	{message} (generic)	–
POST	/auth/reset-password	{email, otp, newPassword}	{message}	400 (invalid OTP)
POST	/auth/refresh	– (refresh cookie)	{accessToken}	401 (invalid refresh)
POST	/auth/logout	– (refresh cookie)	{message}	–

All endpoints expect Content-Type: application/json and respond with JSON.

CORS is configured to allow the origin defined in CLIENT_ORIGIN and to expose credentials (cookies).

---

Testing

Unit tests (Jest)

npm i -D jest supertest
npm test

Mock mailer and the DB pool for isolated unit tests.

Integration test script (optional)

scripts/testFlow.js demonstrates a full register → verify → login cycle.

---

Deployment (Docker)

Dockerfile (simplified)

FROM node:18-alpine AS builder
WORKDIR /app
COPY package*.json ./
RUN npm ci --only=production
COPY . .
RUN npm run build   # if you have a build step (e.g., TypeScript)

FROM node:18-alpine
WORKDIR /app
COPY --from=builder /app .
ENV NODE_ENV=production
EXPOSE 3000
CMD ["node", "src/server.js"]

docker‑compose.yml (includes MySQL)

version: "3.9"
services:
  api:
    build: .
    ports:
      - "3000:3000"
    env_file: .env
    depends_on:
      - db
    restart: unless-stopped

  db:
    image: mysql:8
    environment:
      MYSQL_ROOT_PASSWORD: exampleRootPass
      MYSQL_DATABASE: auth_system
      MYSQL_USER: ${DB_USER}
      MYSQL_PASSWORD: ${DB_PASSWORD}
    volumes:
      - db_data:/var/lib/mysql
    ports:
      - "3306:3306"
    restart: unless-stopped

volumes:
  db_data:

Run with docker compose up -d.

---

Security checklist

Area	Mitigation
Password storage	bcrypt cost ≥12
OTP	cryptographically random 6‑digit code, 10 min TTL, stored in DB, used flag
JWT	short‑lived (15 min), signed with strong secret, sub claim only
Refresh token	random 64‑byte string, SHA‑256 hash stored, httpOnly + Secure + SameSite=Strict cookie, rotated on each use
Rate limiting	5 attempts / minute on /login & /forgot-password
Input validation	Joi schemas for every route
SQL injection	parameterised queries (pool.execute)
CSRF	Stateless API uses Authorization header; refresh cookie is SameSite=Strict
Transport security	Enforce HTTPS in production (e.g., behind Nginx, set trust proxy)
Error handling	Generic messages for password‑reset flow to avoid user enumeration
Session	Only used for OAuth handshake; otherwise completely stateless

---

License

MIT – feel free to adapt for your own projects or share with teammates.

---

Happy coding! If you run into any roadblocks, open an issue in the repo or ask for clarification.