📚 Bookstore Management System - Backend API

Welcome to the backend API for the Bookstore Management System! This is a robust, full-featured REST API built with FastAPI that handles everything a modern online bookstore needs: from customer shopping carts and secure checkouts to automated admin inventory restocks, 7-day persistent authentications, and live database integrations.

🚀 Tech Stack

• Framework: FastAPI (High performance, easy to learn, fast to code, ready for production)
• Database: PostgreSQL (Robust relational database hosted on Render)
• ORM: SQLAlchemy (Object Relational Mapper for database interactions)
• Data Validation: Pydantic (Type hints at runtime for robust payload validation)
• Authentication: JWT (JSON Web Tokens) explicitly secured via HttpOnly, SameSite Cookies.
• Security: Passlib (bcrypt) for secure password hashing
• Package Manager: uv (Extremely fast Python package installer and resolver)

---

✨ Features & Functionality

This API is divided into two main roles: Customers (Standard Users) and Admins (Store Managers/Owners).

🔒 Authentication & Users (/auth, /users)

• Registration: New users can sign up providing a first_name and last_name. The system intelligently auto-generates unique usernames.
• Login: Secure login returning a JWT access token baked inside a secure browser cookie. The cookie implements max_age=604800 (7 days) for persistent logins without sacrificing security headers.
• Profile Management:
  • Users can view their profile (GET /users/me).
  • Users can dynamically update their names, addresses, and phone numbers (PUT /users/me).
  • Users can securely change their passwords (PUT /users/me/password).
• Admin Powers: Admins can permanently delete user accounts (DELETE /users/{user_id}).

📖 Book Catalog (/books)

• Public Browsing: Anyone (even unauthenticated users) can view the book catalog.
  • Supports pagination (skip, limit).
  • Supports dynamic fuzzy searching by Title, Author, or ISBN (using PostgreSQL pg_trgm extension for typo tolerance).
  • Supports array-based filtering by Category, Min Price, and Max Price.
  • Supports sorting results via sort_by (price_asc, price_desc, newest).
• Detailed Views: Fetching a single book (GET /books/{id}) automatically eager-loads and displays all user reviews.
• Admin Powers:
  • Add new books (POST /books/).
  • Update book details entirely (PUT /books/{id}) or partially (PATCH /books/{id}).
  • Upload high-quality book covers that are served statically (POST /books/{id}/cover).
  • Soft-delete books from the inventory, preserving historical sales data (DELETE /books/{id}).

🛒 Shopping Cart & Checkout (/sales)

• Cart Management:
  • Add items to cart (POST /sales/). The API validates that sufficient stock_quantity exists before allowing the addition.
  • View cart (GET /sales/cart). The API dynamically calculates the total_price based on current prices (and applies any active book discounts!).
  • Update quantities (PUT /sales/cart/{item_id}).
  • Remove items (DELETE /sales/cart/{item_id}).
• Checkout:
  • Process the entire cart (POST /sales/sale).
  • The API uses SQLAlchemy with_for_update() to lock the book rows, preventing race conditions if two customers try to buy the last copy simultaneously!
  • Upon successful checkout, stock is decremented, cart is cleared, and historical unit_price markers are permanently recorded in the Sale records.
• Order History: Customers can view their past orders and track their delivery status (GET /sales/history).
• Admin Powers:
  • Admins can update order statuses (e.g., from "Pending" to "Shipped" or "Delivered") (PUT /sales/{sale_id}/status).

⭐ Favorites & Reviews (/favorites, /books/{id}/reviews)

• Wishlists: Customers can save books for later (POST /favorites/).
• Reviews: Customers can leave exactly one review per book. The API actively prevents duplicate reviews from the same user.

📈 Admin Dashboard & Analytics (/admin)

• Dashboard Stats: An insanely fast endpoint (GET /admin/dashboard) designed for frontend UIs to display:
  • Total system revenue.
  • Total successful orders.
  • Total registered customers and catalog size.
  • Active low-stock alerts.
• Advanced SQL Analytics:
  • Top 5 Best Selling Books: Calculates lifetime copies sold.
  • Top 5 Highest Spending Customers: Calculates lifetime revenue per user.
  • Top Vendors: Calculates the admins who have sold the highest total quantity of books (GET /sales/top-vendors).
  • Best Deals: Retrieves the books with the highest active discount percentages (GET /books/best-deals).

📦 Automated Inventory Requisitions (/requisitions)

• Manual Orders: Admins can place a pending order to publishers to restock books (POST /requisitions/).
• Smart Auto-Ordering:
  • Admins can trigger POST /requisitions/auto.
  • The API scans for any book dipping below 10 copies in stock.
  • If a low-stock book has NO pending orders, the API calculates exactly how many copies of that book sold in the last 3 months (90 days).
  • It automatically drafts an order for exactly that many copies (or a default of 10 if it's a new book), ensuring the store never runs out of bestsellers!
• Receiving Inventory: When the publisher delivers the books, Admins hit PUT /requisitions/{id}/receive to mark it complete and magically increment the stock_quantity in the store!

---

⚡ Performance & Database Optimizations

To ensure the backend can handle thousands of concurrent users and massive order histories without slowing down, we implemented strategic PostgeSQL B-Tree Indexing and Query Optimization.

• Foreign Key Indexes: Every single foreign key in the database (user_id and book_id across the Carts, Sales, Reviews, Favorites, and Requisitions tables) is explicitly indexed (index=True).
• The Benefit: Without these indexes, an endpoint like "View Order History" (GET /sales/history) would force PostgreSQL to perform a Sequential Scan (checking every single row in the Sales table one by one). By creating indexes, Postgres instantly looks up the user's records in a highly optimized hash map, dropping query latency from hundreds of milliseconds (or worse at scale) down to virtually 1ms.
• Preventing N+1 Query Problems: In endpoints that return complex relationships (like fetching a single book and all of its reviews), we actively use SQLAlchemy's joinedload() (Eager Loading). Instead of Pydantic secretly triggering dozens of individual SQL queries behind the scenes while serializing the JSON response, we force SQLAlchemy to fetch the parent row and all child rows simultaneously in one single, highly-optimized SQL JOIN query.
• Asynchronous Background Tasks: Heavy operations, such as calculating the "Auto-Restock" algorithm (POST /requisitions/auto) for the entire catalog, are pushed to FastAPI BackgroundTasks. The API immediately returns a 202 Accepted response to the Admin, keeping the frontend snappy and preventing gateway timeouts, while the intensive database math is processed silently on a separate worker thread.
• Memory Management (Pagination): All endpoints capable of returning unbounded lists of records (such as Book Catalogs, Sales Histories, and Requisition Reports) proactively enforce strict skip and limit pagination (defaulting to 50 records per page). This prevents the dreaded "Out of Memory" crash when pulling tens of thousands of rows out of PostgreSQL into the Python runtime.
• Database Connection Pooling: The SQLAlchemy engine is tuned to handle High Availability scenarios. Instead of crashing PostgreSQL with "too many clients" during a traffic spike, the engine maintains a strict pool of 20 persistent connections, with a max_overflow of 10, and a pool_timeout. This gracefully queues incoming requests during extreme load, ensuring the database stays upright.
• API Rate Limiting (DDoS Protection): The application integrates SlowAPI to actively defend against bot spam and DDoS attacks. Public endpoints (like GET /books/) are strictly hard-capped at 60 requests per minute per IP address. If a user exceeds this limit, FastAPI automatically blocks them with a 429 Too Many Requests error, preserving server resources.
• Response Caching (FastAPI-Cache): The highly-trafficked book catalog uses fastapi-cache2. The backend executes the complex SQL queries for the book catalog once, and holds the JSON output in server RAM. For the next 60 seconds, any customer browsing the store receives an instant, pre-calculated response without the backend ever touching PostgreSQL.
• Strict Data Sanitization: Pydantic Field validations are aggressively enforced across all schemas (ge, le, min_length, max_length). This mathematically guarantees no blank strings, negative prices, or extreme cart quantities (like 1000 items) can bypass the API and corrupt the database.
• HTTP-Only Cookies & CORS: To prevent Cross-Site Scripting (XSS) attacks, the authentication system was migrated from raw Bearer tokens to secure, HttpOnly Set-Cookies. JWT access tokens are safely managed by the browser. Because of this, the FastAPI CORSMiddleware in src/main.py is strictly tuned to only allow requests from explicit frontend origins (e.g. http://localhost:5173) while rejecting wildcard requests.

🤖 The Data Seeding Tool (seed_db.py)

To prevent testing against an empty database, we included an automated seeder script! By running uv run python seed_db.py, the system:

1. Validates and generates 3 permanent system Admin users.
2. Iterates through 10 popular book genres.
3. Rapidly calls the Google Books API to scrape 100+ real-world books, accurate authors, ISBNs, and HTTPS cover thumbnail images.
4. Auto-assigns the inventory sequentially to the new Vendor Admins.

---

🏃‍♂️ How to Run the Project Local/Cloud

1. Prerequisites: Ensure you have Python and uv installed.
2. Environment Variables: Setup your database URL in the .env file (DATABASE_URL=postgresql://...).
3. Install Dependencies:

   uv sync

4. Start the Server:

   uv run uvicorn src.main:app --reload

5. View Documentation:
   • Navigate to: http://127.0.0.1:8000/docs.
   • You can login, authenticate, and test every single endpoint right from your browser!