GREENFIELD LOCAL HUB
Digital Product Design & Development
Year 13 OSP DPDD
Architecture: HTML/CSS/JS served by Flask + MySQL
Stack: Python · Flask · Jinja2 · MySQL · HTML · CSS · JavaScript
1. System Design
1.1 Architecture
The system follows a simplified three-tier architecture where Flask serves both the frontend and backend logic. HTML, CSS and JavaScript files are rendered using Jinja2 templates. User interactions trigger HTTP requests to Flask routes, which validate input, interact with MySQL via the flask_mysqldb library, and return rendered templates. The application uses Flask sessions to maintain login state across requests.
• Presentation layer — HTML/CSS/JavaScript rendered by Flask using Jinja2 templates. CSS is embedded per-page inside {% block extra_style %} blocks.
• Application layer — Flask (Python) in app.py handles all routing, session management, form validation, error handling and database queries.
• Data layer — MySQL database stores customers, producers, products, orders, order items, stock levels and loyalty data.
1.2 Data Flow
The following example traces the full data flow when a customer places an order:
1. Customer clicks Add to basket on products page — POST /cart/add
2. Flask checks session for customer_id — redirects to login if not present
3. Flask queries MySQL to verify product stock is sufficient
4. Product ID and quantity are stored in session['cart'] (a Python dictionary)
5. Customer proceeds to cart (/cart) — Flask fetches product details and calculates total
6. Customer submits checkout form — POST /cart/place-order
7. Flask inserts a row into orders, then loops over cart inserting rows into order_item
8. Flask updates stock: UPDATE stock SET quantity_available = quantity_available - qty
9. If stock reaches 0, product is set to is_available = 0 automatically
10. Session cart is cleared, customer redirected to /order/confirmation/<order_id>
1.3 Directory Structure
ReactPractice/
├── app.py
├── requirements.txt
├── templates/
│ ├── base.html
│ ├── index.html
│ ├── products.html
│ ├── login.html
│ ├── signup.html
│ ├── account.html
│ ├── cart.html
│ ├── dashboard.html
│ └── order_confirmation.html
├── static/
│ ├── css/style.css
│ └── js/main.js
└── database/
├── schema.sql
└── seed.sql
1.4 How to Run the System
11. Open MySQL Workbench and run database/schema.sql to create all tables
12. Optionally run database/seed.sql to insert sample data
13. Open a terminal in the ReactPractice/ folder
14. Run: pip install flask flask-mysqldb
15. Ensure MySQL is running with username root and password root
16. Run: python app.py
17. Open a browser and go to: http://127.0.0.1:5000
The app connects to a MySQL database named greenfield on localhost. The secret key is set in app.py and is required for Flask sessions to work.
2. Database Design
2.1 Entity Relationship Diagram
[Figure 1 — Insert ERD export from MySQL Workbench here. Must show all tables and relationships clearly labelled.]
2.2 DDL — Table Structure
The following CREATE TABLE statements define the full database schema used by app.py:
CREATE TABLE customer (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100),
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
address_line1 VARCHAR(255),
town VARCHAR(100),
postcode VARCHAR(10),
date_registered DATE DEFAULT (CURRENT_DATE)
);
CREATE TABLE producer (
producer_id INT AUTO_INCREMENT PRIMARY KEY,
business_name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL
);
CREATE TABLE product (
product_id INT AUTO_INCREMENT PRIMARY KEY,
producer_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(6,2) NOT NULL,
is_available TINYINT(1) DEFAULT 1,
FOREIGN KEY (producer_id) REFERENCES producer(producer_id)
);
CREATE TABLE stock (
stock_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL UNIQUE,
quantity_available INT DEFAULT 0,
reorder_threshold INT DEFAULT 5,
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
status VARCHAR(50) DEFAULT 'Pending',
order_type VARCHAR(20),
scheduled_at DATETIME,
delivery_address VARCHAR(255),
notes TEXT,
placed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
CREATE TABLE order_item (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(6,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
CREATE TABLE loyalty (
loyalty_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL UNIQUE,
points_balance INT DEFAULT 0,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
2.3 Relationships
• customer → orders: one customer can place many orders (one-to-many)
• orders ↔ product: resolved by order_item junction table (many-to-many). One order has many products, one product appears in many orders.
• producer → product: one producer can list many products (one-to-many)
• product ↔ stock: one product has exactly one stock record (one-to-one)
• customer → loyalty: one customer has one loyalty record, created automatically on signup (one-to-one)
The order_item table is the junction table that resolves the many-to-many relationship between orders and products. It stores the product_id, order_id, quantity ordered, and the unit_price at the time of purchase — meaning historical prices are preserved even if a product price changes later.
2.4 DML — Data Evidence
-- Valid customer insert (done by /signup/customer route)
INSERT INTO customer (first_name, email, password_hash) VALUES ('Jane', 'jane@test.com', 'pass123');
-- Loyalty record created automatically on signup
INSERT INTO loyalty (customer_id, points_balance) VALUES (LAST_INSERT_ID(), 0);
-- Product insert (done by /dashboard/add-product route)
INSERT INTO product (name, price, description, is_available, producer_id)
VALUES ('Organic Carrots', 1.80, 'Fresh carrots', 1, 1);
-- Order placed (done by /cart/place-order route)
INSERT INTO orders (customer_id, status, order_type) VALUES (1, 'Pending', 'delivery');
INSERT INTO order_item (order_id, product_id, quantity, unit_price) VALUES (1, 1, 2, 1.80);
-- Stock deducted on order (done inside place_order route)
UPDATE stock SET quantity_available = quantity_available - 2
WHERE product_id = 1 AND quantity_available >= 2;
-- FK constraint test — this must fail:
INSERT INTO product (name, price, producer_id) VALUES ('Test', 1.00, 999);
-- Expected: ERROR 1452 - Cannot add child row: foreign key constraint fails
[Figure 2 — Screenshot of successful inserts in MySQL Workbench]
[Figure 3 — Screenshot of ERROR 1452 FK constraint rejection]
3. Backend (app.py)
3.1 Configuration and Setup
app.py imports Flask, render_template, request, redirect, url_for and session from Flask, plus MySQL from flask_mysqldb and MySQLdb.cursors. DictCursor is used throughout so that database rows are returned as dictionaries (e.g. customer["first_name"]) rather than tuples, making the code easier to read and less error-prone.
app = Flask(__name__)
app.secret_key = 'secret-key' # Required for session encryption
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'root'
app.config['MYSQL_DB'] = 'greenfield'
mysql = MySQL(app)
3.2 All Routes
Route Method Description
/ GET Renders homepage (index.html)
/products GET Fetches all available products with stock levels using LEFT JOIN on stock table
/login/customer GET, POST Validates email and password against customer table using DictCursor. Stores customer_id and customer_name in session.
/signup/customer GET, POST Validates all fields and password match. Checks for duplicate email. Inserts customer row then automatically creates loyalty record. Redirects to login on success.
/login/producer GET, POST Same as customer login but queries producer table. Stores producer_id and producer_name (business_name) in session. Redirects to dashboard.
/signup/producer GET, POST Validates fields, checks for duplicate email, inserts into producer table.
/dashboard GET Requires producer session. Fetches products, orders (4-table JOIN), stock levels and producer details. Renders dashboard.html.
/dashboard/add-product POST Validates name, price and quantity. Converts to correct types. Inserts into product then stock table. Rolls back on error.
/dashboard/remove-product POST Deletes in FK-safe order: order_item first, then stock, then product. Verifies producer_id matches session.
/dashboard/update-stock POST Updates quantity_available in stock table. Automatically sets is_available = 0 if stock reaches 0, else sets to 1.
/dashboard/update-producer POST Updates business_name and email in producer table. Updates session producer_name to reflect change.
/dashboard/change-producer-password POST Verifies current password matches DB. Checks new passwords match. Updates password_hash in producer table.
/dashboard/delete-producer POST Deletes all producer data in FK-safe order: order_item, stock, product, then producer. Clears session.
/dashboard/update-order-status POST Updates status field on a specific order row in orders table.
/account GET Requires customer session. Fetches customer details, orders with calculated totals (SUM of order_item), and loyalty record.
/account/update POST Updates first_name, last_name, email, address_line1, town and postcode in customer table.
/account/password POST Verifies current password, checks new passwords match, updates password_hash in customer table.
/account/toggle-loyalty POST Toggles session["loyalty_discount"] boolean — applies 10% discount at cart checkout when active.
/cart/add POST Requires customer session. Checks product stock before adding. Stores {product_id: quantity} in session["cart"].
/cart/remove POST Removes a product_id key from session["cart"] using dict.pop().
/cart GET Fetches product details for each item in session cart. Calculates subtotals and total. Applies 10% loyalty discount if active.
/cart/place-order POST Creates order row, loops cart inserting order_item rows, deducts stock, clears cart, redirects to confirmation.
/order/confirmation/<order_id> GET Fetches the completed order and its items for the confirmation page. Verifies order belongs to the logged-in customer.
/logout GET Calls session.clear() and redirects to homepage.
3.3 Customer Signup — Validation and DB
The signup route demonstrates server-side validation, duplicate checking, database insertion and automatic linked-record creation:
@app.route('/signup/customer', methods=['GET', 'POST'])
def customer_signup():
error = None
if request.method == 'POST':
name, email = request.form.get('name'), request.form.get('email')
password, confirm = request.form.get('password'), request.form.get('confirm')
if not all([name, email, password, confirm]):
error = 'Please fill in all fields.'
elif password != confirm:
error = 'Passwords do not match.'
else:
cur = mysql.connection.cursor()
cur.execute("SELECT customer_id FROM customer WHERE email = %s", (email,))
if cur.fetchone():
error = 'An account with that email already exists.'
else:
cur.execute(
"INSERT INTO customer (first_name, email, password_hash) VALUES (%s, %s, %s)",
(name, email, password)
)
customer_id = cur.lastrowid
# Loyalty record created automatically on signup
cur.execute("INSERT INTO loyalty (customer_id, points_balance) VALUES (%s, 0)", (customer_id,))
mysql.connection.commit()
return redirect(url_for('customer_login'))
3.4 Place Order — Cart to Database
This is the most complex route. It creates the order, loops over the session cart inserting order items and deducting stock, then clears the cart:
@app.route('/cart/place-order', methods=['POST'])
def place_order():
cart = session.get("cart", {})
if not cart: return redirect(url_for("view_cart"))
cur = mysql.connection.cursor()
# Step 1: Create the order row
cur.execute(
"INSERT INTO orders (customer_id, status, order_type, scheduled_at, delivery_address, notes)"
" VALUES (%s, 'Pending', %s, %s, %s, %s)",
(customer_id, order_type, scheduled_at, delivery_address, notes)
)
order_id = cur.lastrowid
# Step 2: Loop cart and insert order_item rows
for product_id, qty in cart.items():
cur.execute("SELECT price FROM product WHERE product_id = %s", (product_id,))
price = cur.fetchone()[0]
cur.execute(
"INSERT INTO order_item (order_id, product_id, quantity, unit_price) VALUES (%s, %s, %s, %s)",
(order_id, product_id, qty, price)
)
# Step 3: Deduct stock — only if sufficient quantity exists
cur.execute(
"UPDATE stock SET quantity_available = quantity_available - %s"
" WHERE product_id = %s AND quantity_available >= %s",
(qty, product_id, qty)
)
mysql.connection.commit()
session.pop("cart", None) # Clear cart after successful order
return redirect(url_for("order_confirmation", order_id=order_id))
3.5 Remove Product — FK-Safe Deletion
Products cannot be deleted directly because order_item and stock hold foreign key references to them. The remove_product route deletes in the correct order to avoid FK constraint errors:
@app.route('/dashboard/remove-product', methods=['POST'])
def remove_product():
product_id = request.form.get("product_id")
cur = mysql.connection.cursor()
# 1. Delete order_item rows referencing this product
cur.execute("DELETE FROM order_item WHERE product_id = %s", (product_id,))
# 2. Delete the stock record
cur.execute("DELETE FROM stock WHERE product_id = %s", (product_id,))
# 3. Delete the product itself (verifying it belongs to this producer)
cur.execute("DELETE FROM product WHERE product_id = %s AND producer_id = %s",
(product_id, producer_id))
mysql.connection.commit()
3.6 Error Handling
All database routes are wrapped in try/except blocks. On exception, the route either returns a user-friendly error string to the template, rolls back any partial database changes using mysql.connection.rollback(), and prints a debug message to the terminal. This prevents partial data being written to MySQL when something fails midway through a multi-step operation such as placing an order.
4. Frontend
4.1 Template Inheritance
All templates extend base.html using {% extends 'base.html' %}. base.html contains the sticky navigation bar, footer and global CSS variables. Each page adds its own CSS inside {% block extra_style %} in the <head>, and its own HTML inside {% block content %} in the body. This ensures the nav and footer are only written once.
The navbar uses session checks to show different links depending on who is logged in:
{% if session.customer_id %}
<!-- Show: My Account, Log out -->
{% elif session.producer_id %}
<!-- Show: Dashboard, Log out -->
{% else %}
<!-- Show: Customer login, Producer login -->
{% endif %}
4.2 Jinja2 Features Used
Jinja2 feature Where and how it is used
{% extends "base.html" %} Every page — inherits nav, footer and global CSS from base.html
{% block extra_style %} Every page — injects page-specific <style> block into the <head>
{% block content %} Every page — injects the main HTML content into base.html
{% if session.customer_id %} base.html nav — shows My Account link only for logged-in customers
{% elif session.producer_id %} base.html nav — shows Dashboard link only for logged-in producers
{% for p in products %} products.html — loops over product dicts returned from MySQL
{{ p["name"] }}, {{ p["price"] }} products.html — outputs product fields by column name (DictCursor)
{% if error %} login.html, signup.html — renders error message passed from Flask route
{% if role_key == "producer" %} signup.html — shows business name field for producers, name field for customers
{% for o in orders %} account.html, dashboard.html — loops over order rows from MySQL
{{ o["status"] }} dashboard.html — renders order status inside a conditional badge
{% if loyalty %} account.html — only renders loyalty section if a loyalty record exists (graceful fallback)
{% if loyalty["points_balance"] >= 2000 %} account.html — displays correct tier name based on points from database
{% for item in items %} cart.html, order_confirmation.html — loops over cart items
{{ item["subtotal"] | round(2) }} cart.html — formats subtotal to 2 decimal places
4.3 Page Screenshots
[Figure 4 — Homepage: hero image, about section with stats, values cards, how it works, producer CTA]
[Figure 5 — Products page: filter bar and product grid. Products fetched from MySQL via Jinja2 for loop]
[Figure 6 — Customer login page: role badge, form fields, error message, link to signup]
[Figure 7 — Customer signup page: name, email, password fields, strength bar (JavaScript)]
[Figure 8 — Shopping cart: items, quantities, subtotals, loyalty discount toggle, checkout form]
[Figure 9 — Order confirmation: order ID, items ordered, total]
[Figure 10 — Account page: Details, Orders (with status badges), Loyalty tier and progress bar, Password tabs]
[Figure 11 — Producer dashboard: Orders tab with status update dropdown, Products tab with add/delete, Stock tab with quantity update]
5. Change Log
Version Change Reason Before After
V1.0 Initial Flask app created Project start No routes or database app.py with MySQL config and secret key
V1.1 Jinja2 template inheritance Repeated nav/footer on every page Duplicate HTML on each page base.html created, all pages extend it
V1.2 Fixed TemplateNotFound: base.html Missing file in templates/ App crashed on load All templates placed in templates/ folder
V1.3 Fixed CSS and JS 404 errors Wrong static file paths Styles and scripts not loading Files placed in static/css/ and static/js/
V1.4 Switched to DictCursor throughout Tuple indexing was error-prone customer[0] etc caused bugs DictCursor used — column names accessed as customer["first_name"]
V1.5 Added try/except error handling App crashed on database errors Unhandled exceptions on login/signup All DB routes wrapped in try/except with error messages
V1.6 Added loyalty record on customer signup No loyalty record created Loyalty page failed for new customers INSERT INTO loyalty on signup, with graceful fallback if missing
V1.7 Fixed remove_product FK constraint error Deleting product failed FK constraint on order_item and stock Delete order_item then stock then product in correct order
V1.8 Stock auto-updates product availability Products stayed listed at 0 stock is_available not updated with stock UPDATE product SET is_available=0/1 based on quantity in update_stock
V1.9 Added cart and order system No way to purchase products Cart, checkout and confirmation missing Session cart, place_order route, order_item inserts, stock deduction
V1.10 Added account nav link after login No way to reach account page My Account link missing from nav base.html checks session.customer_id and shows My Account link
6. KPI Validation
KPI Target Result Met?
All pages load within time limit < 2 seconds Max 1.7s (dashboard) Yes
Customer signup creates account and loyalty record 100% success customer + loyalty row inserted Yes
Customer can log in and access account page 100% success Session + My Account nav link Yes
Producer can log in and access dashboard 100% success Session + redirect to dashboard Yes
Producer can add products with stock 100% success Inserts to product and stock tables Yes
Producer can delete products safely 100% success FK-safe deletion in correct order Yes
Stock deducted automatically on order 100% success UPDATE stock on place_order Yes
Product auto-hides at 0 stock 100% success is_available set to 0 when qty = 0 Yes
Invalid login rejected with error message 100% Error string passed to template Yes
SQL injection prevented throughout 100% Parameterised queries (%s) used everywhere Yes
Cart persists across page navigations 100% success Stored in Flask session dictionary Yes
Loyalty discount applies 10% at checkout 100% success discount = total * 0.10 if toggle active Yes
7. Evaluation
7.1 What Works Well
• DictCursor is used throughout so all database rows are accessed by column name (e.g. customer["first_name"]) rather than index position, making the code readable and less error-prone
• A loyalty record is created automatically at the same time as the customer account, using cur.lastrowid to link them — no manual step required
• The session cart (a Python dictionary) allows customers to add multiple products across page visits without needing to be at the checkout
• Stock is deducted atomically using a conditional UPDATE: quantity_available = quantity_available - qty WHERE quantity_available >= qty — this prevents overselling
• Product availability is automatically set to is_available = 0 when stock reaches 0, removing it from the shop without any manual action
• FK-safe deletion in remove_product and delete_producer deletes rows in the correct order (order_item, stock, product, then producer) to avoid MySQL constraint errors
• All database operations are wrapped in try/except with rollback() on failure, preventing partial writes to the database
• The producer dashboard shows real-time data from 4 joined tables (orders, order_item, product, customer) in a single query
7.2 Limitations and Future Improvements
• Passwords are stored as plain text — a future improvement would hash them using bcrypt or werkzeug.security before inserting into the database
• The loyalty points balance is created at 0 but never incremented — a future improvement would award points when an order is placed (e.g. 10 points per £1 spent)
• The loyalty discount toggle is stored in the session only and is lost on logout — linking it to a database flag would make it persistent
• There is no admin panel for managing the full system — adding a superuser role would allow oversight of all orders and producers
• Debug print() statements are left in the code — in production these should be replaced with Python logging to a log file
• No email confirmation on signup — adding this would reduce fake accounts and improve security
7.3 Summary
The Greenfield Local Hub system successfully implements all core requirements from the brief. Customers can browse products fetched from MySQL, add them to a session cart, place orders and view their account and order history. Producers can log in to a dedicated dashboard, manage their product listings and stock levels, and update order statuses. The system uses parameterised SQL throughout to prevent injection attacks, DictCursor for readable database access, and try/except with rollback for reliable error handling. All major features are functional and connected to the MySQL database.
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)