PRD: E-commerce CRUD Application

This PRD is intentionally generic and framework-agnostic for freshers. It supports MySQL as the primary database, with interchangeable backends (Python Flask/FastAPI, Java Spring Boot, Node Express) and frontends (Vanilla JS, React, Next.js).

---

1) Overview

• Goal: Build a simple e-commerce CRUD app with products, carts, and three roles: Admin, Seller, Customer.
• Core features:
• List Products
• Product Details
• Add to Cart
• Update Product in Cart
• Delete Product from Cart
• List Carts
• Constraints:
• Database: MySQL
• Backend: Python (Flask or FastAPI) or Java Spring Boot or Node Express
• Frontend: Vanilla JS or React or Next.js
• Clean separation of concerns and RESTful API design
• Authentication and Role-Based Access Control (RBAC)

---

2) Personas and Roles

• Admin
• Manages users and sellers
• Can CRUD any product
• Can view all carts and audit activity
• Seller
• Manages own products only
• Can view orders that include their products (if implemented as a stretch)
• Customer/Buyer
• Browses products
• Manages own cart
• Checks out cart (stretch)

RBAC summary:

• Admin: Full access
• Seller: CRUD only own products, read others’ product listings
• Customer: Read product listings, manage own cart

---

3) Scope and Features

3.1 Product Management

• Create Product
• Read Product List
• Read Product Detail
• Update Product
• Delete Product
• Constraints:
• Seller can only modify products they own
• Admin can modify any product
• Customers cannot modify products

3.2 Cart Management

• Add to Cart
• Update Product Quantity in Cart
• Remove Product from Cart
• View Cart
• Constraints:
• One open cart per customer at a time
• If cart does not exist, create on first add
• Quantity must be positive integer
• Enforce stock minimum of 0 on update (no negative stock; oversell prevention optional)

3.3 Authentication and Authorization

• Register/Login
• Session or JWT-based auth
• Role assignment at registration by Admin (students may seed roles)
• Middleware to enforce role-based access

3.4 Non-Goals (Out of Scope for MVP)

• Payments and checkout processing
• Inventory reservations and order lifecycle
• Reviews/ratings, wishlists
• Media storage beyond product image URL
• Internationalization, taxes, shipping

---

4) Functional Requirements

4.1 User Stories

• As a Customer, I can view a list of products with pagination and search.
• As a Customer, I can view product details.
• As a Customer, I can add a product to my cart, update quantities, and remove items.
• As a Customer, I can view my current cart and total cost.
• As a Seller, I can create, update, and delete my products.
• As a Seller, I can view only my products in a “My Products” view.
• As an Admin, I can manage all products and view all carts.
• As an Admin, I can manage users and assign roles.

4.2 API Endpoints (REST, generic)

Auth:

• POST /api/auth/register
• POST /api/auth/login
• GET /api/auth/me

Products:

• GET /api/products?search=&category=&sort=&page=&pageSize=
• GET /api/products/:id
• POST /api/products [Seller, Admin]
• PUT /api/products/:id [Owner Seller, Admin]
• DELETE /api/products/:id [Owner Seller, Admin]

Carts:

• GET /api/carts/me [Customer]
• POST /api/carts/me/items body: { productId, quantity } [Customer]
• PUT /api/carts/me/items/:itemId body: { quantity } [Customer]
• DELETE /api/carts/me/items/:itemId [Customer]
• (Admin-only) GET /api/carts to list all carts, filter by userId

Users and Roles (Admin only):

• GET /api/users
• PUT /api/users/:id/role body: { role }

Response format:

• JSON objects with consistent envelope: { data, error, meta }
• Use proper HTTP status codes:
• 200 OK, 201 Created, 204 No Content
• 400 Bad Request, 401 Unauthorized, 403 Forbidden, 404 Not Found, 409 Conflict, 422 Unprocessable, 500 Server Error

Validation:

• Use server-side validation for all inputs
• Return errors with field-level messages

---

5) Data Model (MySQL)

Tables and key columns:

• users
• id PK, uuid or bigint
• email unique
• password_hash
• role enum(‘ADMIN’, ‘SELLER’, ‘CUSTOMER’)
• name
• created_at, updated_at
• products
• id PK
• seller_id FK -> users.id
• name
• description TEXT
• price DECIMAL(10,2)
• stock INT
• image_url VARCHAR(2048) NULL
• category VARCHAR(255) NULL
• created_at, updated_at
• index(name), index(category)
• carts
• id PK
• user_id FK -> users.id unique for active/open cart
• status enum(‘OPEN’, ‘CHECKED_OUT’) default ‘OPEN’
• created_at, updated_at
• unique(user_id, status=’OPEN’) logical uniqueness enforced by application or partial index emulation
• cart_items
• id PK
• cart_id FK -> carts.id
• product_id FK -> products.id
• quantity INT
• unit_price DECIMAL(10,2) snapshot at add time (optional for MVP; otherwise compute from products)
• unique(cart_id, product_id)
• created_at, updated_at

Indexes:

• FK indexes on seller_id, user_id, cart_id, product_id
• Search indexes on products.name, products.category

Referential integrity:

• ON DELETE RESTRICT for product if present in cart_items (or cascade remove cart_items on product delete for teaching simplicity; discuss trade-offs)
• ON DELETE CASCADE for cart -> cart_items

---

6) Security and Auth

• Passwords: md5 or bcrypt
• Auth: JWT or server sessions with HttpOnly cookies
• RBAC middleware:
• Admin routes: require role === ADMIN
• Seller product writes: require role === SELLER and seller_id === current_user.id
• Customer cart routes: require role === CUSTOMER and user_id === current_user.id
• Input sanitation and validation
• CORS configured for chosen frontend host
• Prevent overposting by whitelisting fields

---

7) Non-Functional Requirements

• Performance:
• Product list should return within 500 ms for 10k product dataset with indexes
• Pagination default pageSize 20
• Reliability:
• Handle concurrent cart updates gracefully
• Observability:
• Basic request logging and error logging
• Code Quality:
• Layered architecture: routes/controllers, services, repositories/DAOs, models
• Unit tests for services and repositories
• Minimal integration tests for core flows

---

8) UX Requirements

• Views:
• Product List: grid or table with name, price, stock, category, thumbnail
• Product Detail: name, description, price, stock, image, add-to-cart
• My Cart: line items, quantity controls, remove action, subtotal and total
• Seller Dashboard: My Products CRUD
• Admin Panel: Users list, role management, all carts view
• Accessibility:
• Semantic HTML where applicable
• Keyboard navigation for cart quantity updates
• Responsive:
• Mobile-first layout

---

9) Acceptance Criteria

Product Listing

• Given products exist, when a customer visits /products, they see a paginated list with name, price, and image.
• Searching by name returns matching items.
• Clicking an item navigates to details.

Product Details

• Given a product exists, details page shows name, description, price, stock, and add-to-cart button.
• If stock is 0, add-to-cart is disabled.

Cart

• Add to cart creates an OPEN cart if none exists.
• Updating quantity reflects in totals immediately.
• Removing an item deletes row and updates total.
• Customers cannot access another user’s cart.

Seller Product CRUD

• Seller can create, edit, delete only their products.
• Attempting to modify others’ products returns 403.

Admin

• Can list all carts and view any product.
• Can change user roles.

API

• All endpoints return appropriate status codes and validation errors.

---

10) Developer Guidance by Stack

Backend notes:

• Flask/FastAPI
• Use SQLAlchemy + Alembic for migrations
• Pydantic (FastAPI) for request/response models
• Spring Boot
• Spring Security for auth, JPA/Hibernate, Flyway/Liquibase migrations
• Node Express
• TypeScript recommended, Prisma/TypeORM/Sequelize, Zod/Joi validation

Frontend notes:

• Vanilla JS
• Fetch API, simple routing via hash or path
• React
• React Router, SWR/React Query, controlled forms
• Next.js
• App Router, fetch from server components or client with SWR, API routes optional for demo

---

11) API Contracts (Sample Schemas)

Product (response)

• id: number
• name: string
• description: string
• price: number
• stock: number
• imageUrl: string|null
• category: string|null
• sellerId: number

Cart (response)

• id: number
• userId: number
• status: “OPEN” | “CHECKED_OUT”
• items: Array
• total: number

CartItem (response)

• id: number
• productId: number
• quantity: number
• unitPrice: number
• productSnapshot: { id, name, price, imageUrl } // optional

Error (response)

• error: { code: string, message: string, details?: any }

---

12) Milestones

• Milestone 1: DB schema and migrations ready. Seed users with roles.
• Milestone 2: Auth + RBAC middleware working.
• Milestone 3: Products API and UI list/detail.
• Milestone 4: Cart API and UI end-to-end.
• Milestone 5: Seller product CRUD UI.
• Milestone 6: Admin basics: users list, role update, carts list.
• Milestone 7: Tests and polish.

---

13) Evaluation Rubric for Students

• Correctness: Meets acceptance criteria and RBAC rules
• Code Quality: Structure, readability, validation, error handling
• DB Design: Keys, indexes, constraints, migrations
• Security: Auth, password hashing, access controls
• UX: Usable flows, responsive layout, basic accessibility
• Tests: Coverage of core services and endpoints

---

14) Stretch Goals

• Checkout flow with order creation
• Stock decrement on checkout
• Product images upload to object storage
• Sorting, filters, categories
• Rate limiting and caching for product list
• Swagger/OpenAPI documentation

---

Create CRUD Application using Python and MySQL connector

Show following options

1. List All Records
2. Delete
3. Insert
4. Update
5. Search
0. Exit

Write separate function for each functionality

Program should not get exited unless user select 0 option.

You can use any module to connect to MySQL from python

A user or admin facing problem managing data on excel sheet.
He/She need a system to perform at least following operations

1. Add Record
2. Modify Record
3. Delete Record
4. Show Listing and Search Data to get specific Information.

User need a system which should be accessible from internet so that he can work from any machine (laptop/desktop/mobile).

You need to develop a web application with best of your knowledge

Roles: Admin

With correct credentials admin should be able to login and see the dashboard.

if credentials are wrong he will stay on login page and show a message – wrong credentials.

On successful login admin can see users list perform all CRUDL operations.

NOTE: you need to use vim editor to edit files

Following are the wireframes for reference.

---

---

---

---

Technologies to be used

• composer for package management and autoload
• ini for configuration
• git and gitlab for version control
• HTML5 CSS3 Bootstrap 5 for UI/UX
• jquery 3.6 or javascript for validation and AJAX
• php 7.4 or 8 as backend programming language
• mysql 8 database
• PDO for database operations
• PHPUnit for unit testing
• python and php for automation script (Use cron jobs to automatically run script)
• nginx web server
• use infinityfree / webserver / cloudserver for website hosting
• Jenkins and git-ftp for CI/CD

MVP / Deliverable

1. P0
   1. Users Listing
   2. Delete User Record
   3. Add User Record with Profile Picture
      (User status should be enum in database table: enable, disable, blocked, active, inactive)
   4. Update User Record
   5. Session Management Login / Logout
2. P1
   1. View User Details in Modal Window
   2. Pagination
   3. Sorting
   4. Searching
   5. Filtering
3. P2
   1. Frontend – Backend Validation
   2. Export CSV Users
   3. Bulk Upload CSV
   4. Activity Log
   5. Export Activity Log
4. P3
   1. Login with OTP i.e. 2FA (Use Redis to store OTP)
   2. Login Logout for user account
   3. Inactive User status if not logged in for 3 consecutive days
   4. Change Admin and User Password from their respective account
   5. Secret Questions and Forgot Password / Recover Password using secret questions or through the email link
5. P4
   1. REST API (Web Services) for User CRUDL Operations
   2. Protect REST API using Basic Authentication or JWT token
   3. Login with google API or Facebook API
   4. PHPUnit test for all functionalities
   5. Licensing or limit user registration
6. P5
   1. Dashboard showing following summary (Use highcharts)
      1. Total User
      2. Active Users
      3. License Count / Usage Count
      4. Online Users
      5. Weekly Registration Stats

Schema

Ref SQL Queries

SELECT * FROM users ORDER BY id DESC;

SELECT * FROM users WHERE id = 3;

DELETE FROM users WHERE id = 3;

INSERT INTO users (id, name, age, city, added_at, updated_at) VALUES (NULL, 'sonam gupta', 18, 'gorakhpur', NOW(), NOW());

UPDATE users SET name = 'Sonam Gupta', age = 20, city = 'Gorakhpur', updated_at = NOW() WHERE id = 5;

Reference

PHP MySQL CRUD App

Php CRUD Application – How to Create Website Using Php – YouTube

Linux Commands

SQL Cheat Sheet

HTML and CSS

Php Fundamentals

Php Basics Tutorial

Php Advanced Tutorial

Javascript Tutorial in Hindi

Javascript Tutorial in English

Create a project user management system

Roles: Admin

With correct credentials admin should be able to login and see the dashboard.

if credentials are wrong he will stay on login page and show a message – wrong credentials.

On successful login admin can see users list perform all CRUDL operations.

Following are the wireframes for reference.

---

---

---

---

---

Deploy war on heroku

Signup and select java as primary language https://signup.heroku.com/

Download heroku cli from https://devcenter.heroku.com/articles/heroku-cli

heroku plugins:install java

heroku login

Open another terminal or gitbash and run following command

heroku war:deploy <path_to_war_file> --app <app_name>

---

Reference Code: https://gitlab.com/tcet/advanced-java

Reference YouTube videos

1. Introduction to Servlet
   
2. MySQL DATABASE CRUDL
   
3. Servlet Methods – GET, POST, PUT, DELETE and OPTIONS
   
4. DATABASE CONNECTION JDBC MySQL
   
5. Show MySQL Data in HTML Table
   
6. Session Management – Login / Logout
   
7. DML – Insert Update Delete
   
8. Bootstrap Integration in JSP
   

Creating a personal mini car inventory system from scratch using your best knowledge and skills. The system will have inventory of manufacturer and models (cars) of each manufacturer owned.

System should be created using your own frontend and backend framework and should use caching.

Frontend should be a webapp a.k.a. SPA. (Ie: no refreshing/reloading pages)

Technology to be used:

PHP (OOP)
MySql (Normalized Database Schema)
Javascript (JQuery – AJAX)
HTML, CSS and Bootstrap 5

Classes to be created:

Database – Class to deal with each and every operation of database.
Manufacturer – Class to deal with all operations related to car manufacturer.
Model – Class to deal with all operations related to car model.

Page 1: Add Manufacturer.
The page should contain a input box for manufacturer name and a submit button.

Page 2: Add Model.
This page should have a manufacturer dropdown on the right side and model name textbox on the left side (Both should be in the same line)

Add other details below about the car like “Color, manufacturing year, registration number, note and 2 pictures”. Pictures should be uploaded using any ajax plugin.

And lastly there should be a submit button.

Page 3: View Inventory.
This page should populate a table of all the models and manufacturers from the DB.

It should have the columns as below

Serial Number, Manufacturer Name, Model Name, Count

eg.

1. Maruti WagonR 2
2. Tata Nano 1

On clicking on the row, a popup will appear which will have details of the individual models like color, manufacturing year etc. (Basically all details from page 2) and a Sold clickable link.

On clicking Sold, the row will be deleted and the DB will be updated accordingly.

In addition to this, on Page 3 when users are viewing the inventory and in case a car is sold, the View Inventory table for the column Count will have to be dynamically updated in case a car is sold at that moment.

Similarly in case the users are on any other page or pop up of the system, they should get an alert saying “make model is sold” when a car is sold.

Wireframes

DATABASE Schema

Code for reference
https://github.com/shaileshsonare/mcis