lib-sql - Library Management System

Full-stack library management system with authentication, authorization, and book management.

Features

User Features:

• Register/login with bcrypt-hashed passwords and JWT tokens
• Add, update, and delete own books
• Search books by title (case-insensitive)
• View all books with author information
• Manage own profile

Admin Features: (Login: admin@gmail.com / adminonlyalllowed)

• View/delete all users
• Update/delete any book
• Register new users from dashboard

Technology Stack

• Backend: Node.js, Express 5.x
• Database: PostgreSQL with Sequelize ORM
• Authentication: bcrypt (password hashing) + JWT (12h expiration)
• Frontend: Vanilla JavaScript SPA (no frameworks)

---

Project Structure

index.js - Entry Point

• Loads models and defines relationships (User hasMany Books)
• Configures Express with CORS and JSON parsing
• Serves static files from public/ folder
• Syncs database with { force: false, alter: true }

config/database.js

• Configures Sequelize connection to PostgreSQL
• Uses environment variables from .env with fallback defaults

models/User.js

• Fields: name, email, password, isAdmin (default false)
• Email validation and unique constraint
• Password auto-hashed with bcrypt (10 rounds) in beforeCreate/beforeUpdate hooks
• validPassword(password) method: async bcrypt.compare for login

models/Book.js

• Fields: title, UserId (foreign key, auto-added by Sequelize)
• Linked to User via belongsTo relationship

middleware/auth.js

• Extracts JWT token from Authorization: Bearer <token> header
• Verifies token and attaches req.user with { id, email, isAdmin }
• Returns 401 if missing/invalid

route/users.js - User API

POST /users - Register new user

• Validates name, email, password
• Password auto-hashed in model hook
• Returns user without password

POST /users/login - Authenticate user

• Validates email/password with bcrypt.compare
• If admin@gmail.com and password matches, sets isAdmin: true in token
• Returns JWT token (12h expiration) + user data

GET /users (auth required)

• Admin: returns all users
• Regular: returns only current user
• Password excluded from response

PUT /users/:id (auth required)

• Admin: can update any user
• Regular: can only update self
• Returns 403 if unauthorized

DELETE /users/:id (auth required)

• Admin: can delete any user
• Regular: can only delete self
• Returns 403 if unauthorized

route/books.js - Book API

POST /books (auth required)

• Creates book linked to current user via UserId
• Validates title presence

GET /books?search=query (auth required)

• Returns all books with User data (password excluded)
• Optional search parameter filters by title (case-insensitive)
• Uses Sequelize Op.iLike for PostgreSQL pattern matching

PUT /books/:id (auth required)

• Admin: can update any book
• Regular: can only update own books
• Returns 403 if unauthorized

DELETE /books/:id (auth required)

• Admin: can delete any book
• Regular: can only delete own books
• Returns 403 if unauthorized

public/index.html - Frontend

• Single-page vanilla JS app with 5 views: landing, auth choice, login, signup, dashboard
• State: currentUser and authToken in memory (not persisted)
• View switching via showView() function
• Admin-only "Register New User" form (conditionally displayed)
• Book search with query parameter
• Authorization checks before delete operations

---

Security

✅ Passwords hashed with bcrypt (10 rounds) ✅ JWT tokens expire after 12 hours ✅ Authorization middleware on protected routes ✅ Ownership checks (users can only modify own resources) ✅ Admin bypass with isAdmin flag ✅ Passwords excluded from all API responses ✅ Sequelize ORM prevents SQL injection ✅ Environment variables for credentials

---

Admin System

Hardcoded admin account: admin@gmail.com / adminonlyalllowed

How it works:

1. Login checks if email is admin@gmail.com and password matches
2. Sets isAdmin: true in JWT token
3. Frontend shows 🔑 badge and admin-only UI elements
4. Backend allows admin to bypass ownership checks

Admin privileges:

• View/update/delete all users
• Update/delete any book
• "Register New User" form visible on dashboard

To change admin credentials: Edit condition in route/users.js login endpoint