BookTracker — Full-Stack Reading Tracker

A 4th Semester DBMS College Project built with React, Node.js, Express, and MySQL.

BookTracker is a full-stack web application that lets users discover books, track their reading progress, log reading sessions, write reviews, and manage a personal library — all powered by a relational MySQL database with views, triggers, and foreign key constraints.

---

🗂️ Table of Contents

• Tech Stack
• Features
• Folder Structure
• Database Design
• ER Diagram
• Getting Started
• Environment Variables
• API Reference
• Demo Credentials
• Screenshots

---

🛠️ Tech Stack

Layer	Technology
Frontend	React 18, Vite, React Router DOM v6, Tailwind CSS v3, Axios
Backend	Node.js, Express.js, ES Modules ("type": "module")
Database	MySQL 8.0+
Auth	JSON Web Tokens (JWT), bcryptjs
Dev Tools	Nodemon, Vite HMR

---

✨ Features

• Authentication — Register and login with JWT-based auth; protected routes throughout
• Book Discovery — Browse 12 sample books across 7 genres with genre filter tabs
• Search — Debounced full-text search by title, author, or genre
• Reading List — Add books with status: Want to Read, Currently Reading, or Completed
• Reading Sessions — Log pages read per session; MySQL trigger auto-updates progress
• Auto-completion — Book marked Completed automatically when all pages are read
• Reviews & Ratings — 5-star interactive rating with written review; one review per user per book (upsert)
• Favorites — Toggle-favorite any book; dedicated favorites page
• Dashboard — Greeting, stat cards, yearly goal progress bar, genre bar chart, currently reading progress bars, recently added, top rated
• Views — book_stats and user_stats MySQL views aggregate data for efficient querying
• Responsive UI — 1-col mobile → 2-col tablet → 4-col desktop grid; sidebar collapses on mobile

---

📁 Folder Structure

book-tracker/
├── database.sql                   # Full DB schema + sample data
├── backend/
│   ├── index.js                   # Express app entry point
│   ├── db.js                      # mysql2/promise pool
│   ├── .env                       # Environment variables (git-ignored)
│   ├── .env.example               # Template for environment variables
│   ├── package.json
│   ├── middleware/
│   │   └── auth.js                # JWT protect middleware
│   └── routes/
│       ├── auth.js                # POST /register, POST /login
│       ├── books.js               # GET /, /search, /top-rated, /:id
│       ├── readingList.js         # GET, POST, PUT, DELETE
│       ├── sessions.js            # POST /, GET /:book_id
│       ├── reviews.js             # GET ?book_id=, POST /
│       ├── favorites.js           # GET /, POST /toggle
│       └── dashboard.js           # GET / (aggregated dashboard data)
└── frontend/
    ├── index.html
    ├── package.json
    ├── vite.config.js
    ├── tailwind.config.js
    ├── postcss.config.js
    └── src/
        ├── main.jsx
        ├── App.jsx                # Router + PrivateRoute + layout
        ├── index.css              # Tailwind directives + global styles
        ├── api/
        │   └── axios.js           # Axios instance with JWT interceptor
        ├── context/
        │   └── AuthContext.jsx    # Auth state + login/logout
        ├── hooks/
        │   └── useToast.js        # Toast notification hook
        ├── components/
        │   ├── Navbar.jsx
        │   ├── PrivateRoute.jsx
        │   ├── BookCard.jsx
        │   ├── BookDetailModal.jsx
        │   ├── LogSessionModal.jsx
        │   ├── ReviewModal.jsx
        │   ├── ProgressBar.jsx
        │   ├── StarRating.jsx
        │   ├── Toast.jsx
        │   └── Spinner.jsx
        └── pages/
            ├── Login.jsx
            ├── Register.jsx
            ├── Dashboard.jsx
            ├── Discover.jsx
            ├── MyLibrary.jsx
            └── Favorites.jsx

---

🗄️ Database Design

Tables

Table	Description
users	Registered users with hashed passwords and reading goal
books	Book catalog with metadata (author, genre, pages, ISBN)
reading_list	Junction table — user's book status and reading progress
reviews	One review per user per book (rating + text)
reading_sessions	Individual reading sessions with page count and notes
favorites	User-favorited books (many-to-many)

Views

View	Description
book_stats	Books joined with avg rating, total reviews, total readers
user_stats	Users joined with books completed/reading/wishlist + total pages

Trigger

after_session_insert — fires AFTER INSERT ON reading_sessions.
Recalculates reading_list.pages_read as SUM(pages_this_session) for that user+book combination, ensuring the progress counter is always accurate regardless of how many sessions exist.

Key Constraints

• All user_id and book_id foreign keys use ON DELETE CASCADE
• UNIQUE KEY on (user_id, book_id) in reading_list, reviews, and favorites
• CHECK (rating BETWEEN 1 AND 5) on reviews.rating

---

🔗 ER Diagram

USERS ──────┬──── READING_LIST ────┬──── BOOKS
            │                      │
            ├──── REVIEWS ─────────┤
            │                      │
            ├──── READING_SESSIONS─┤
            │                      │
            └──── FAVORITES ───────┘

Cardinalities:

• USERS 1──∞ READING_LIST ∞──1 BOOKS
• USERS 1──∞ REVIEWS ∞──1 BOOKS
• USERS 1──∞ READING_SESSIONS ∞──1 BOOKS
• USERS 1──∞ FAVORITES ∞──1 BOOKS

---

🚀 Getting Started

Prerequisites

• Node.js 18+
• MySQL 8.0+
• npm

1. Clone the repository

git clone https://github.com/yourusername/book-tracker.git
cd book-tracker

2. Set up the database

mysql -u root -p < database.sql

This will:

• Create the book_tracker database
• Create all 6 tables with constraints
• Create 2 views (book_stats, user_stats)
• Create the after_session_insert trigger
• Insert 12 sample books and 3 demo users with sample data

3. Configure the backend

cd backend
cp .env.example .env

Edit .env with your MySQL credentials (see Environment Variables).

npm install
npm run dev

Backend starts at http://localhost:5000
You should see:

Server running on port 5000
Database connected

4. Start the frontend

cd ../frontend
npm install
npm run dev

Frontend starts at http://localhost:3000

---

⚙️ Environment Variables

Create backend/.env from the provided .env.example:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_mysql_password
DB_NAME=book_tracker
PORT=5000
JWT_SECRET=your_super_secret_jwt_key_change_this
JWT_EXPIRES_IN=7d

⚠️ Never commit .env to version control. It is already listed in .gitignore.

---

📡 API Reference

All protected routes require the header:

Authorization: Bearer <token>

Auth — /api/auth

Method	Endpoint	Body	Description
POST	/register	username, email, password, full_name?, reading_goal?	Register new user
POST	/login	email, password	Login, get JWT

Books — /api/books 🔒

Method	Endpoint	Params	Description
GET	/	?genre=	All books with user's status + fav
GET	/search	?q=	Search by title, author, genre
GET	/top-rated	—	Top 5 by avg rating (reviewed only)
GET	/:id	—	Single book with reviews

Reading List — /api/reading-list 🔒

Method	Endpoint	Body	Description
GET	/	?status=	User's books with percent_done
POST	/	book_id, status	Add/update book in reading list
PUT	/:book_id	status	Update status (auto sets dates)
DELETE	/:book_id	—	Remove book + all its sessions

Sessions — /api/sessions 🔒

Method	Endpoint	Body	Description
POST	/	book_id, pages_this_session, notes?	Log session (trigger fires, auto-complete check)
GET	/:book_id	—	All sessions for a book by current user

Reviews — /api/reviews 🔒

Method	Endpoint	Params / Body	Description
GET	/	?book_id=	All reviews for a book
POST	/	book_id, rating, review_text?	Create/update review (upsert)

Favorites — /api/favorites 🔒

Method	Endpoint	Body	Description
GET	/	—	All favorited books for current user
POST	/toggle	book_id	Add or remove favorite

Dashboard — /api/dashboard 🔒

Method	Endpoint	Description
GET	/	Returns user stats, currently reading, genres, recent, top rated

---

🔑 Demo Credentials

Three sample users are seeded with the database. All use the password password123.

Name	Email	Password
Arjun Sharma	arjun@example.com	password123
Priya Patel	priya@example.com	password123
Rahul Verma	rahul@example.com	password123

Passwords are stored as bcrypt hashes (10 rounds) — never in plain text.

---

📋 Sample Data Overview

• 12 books across 7 genres: Fiction, Self-Help, Finance, Fantasy, Dystopian, Classic, Spirituality
• 3 users each with a mix of completed, currently reading, and wishlist books
• 9 reviews with star ratings and written text
• 7 reading sessions tied to in-progress books
• 9 favorites spread across users

---

🎓 DBMS Concepts Demonstrated

Concept	Where Used
Normalization (3NF)	No repeating groups; all non-key columns depend on the full PK
Primary Keys	AUTO_INCREMENT PKs on every table
Foreign Keys	All junction tables reference users and books with ON DELETE CASCADE
Unique Constraints	Composite UNIQUE KEY (user_id, book_id) prevents duplicates
CHECK Constraints	rating BETWEEN 1 AND 5 enforced at DB level
Views	book_stats and user_stats for complex aggregations
Triggers	after_session_insert keeps pages_read consistent automatically
Aggregate Functions	AVG(), COUNT(), SUM(), COALESCE(), ROUND()
CASE expressions	Conditional counting in user_stats view
ON DUPLICATE KEY	Upsert pattern used in reading list, reviews
Connection Pooling	mysql2/promise pool for efficient DB connections

---

🔒 Security Practices

• Passwords hashed with bcryptjs (10 rounds) — never stored in plain text
• JWT tokens expire in 7 days; stored in localStorage
• All private routes protected by protect middleware
• req.user.user_id from JWT used everywhere — user IDs never trusted from request body
• Parameterised SQL queries via mysql2 prevent SQL injection
• .env excluded from version control via .gitignore

---

📄 License

This project is for educational purposes as part of a 4th Semester DBMS course project.

---

Built with ❤️ for the love of books and databases.