Full-Stack · Database · Rebuilt in 2026
A storefront and manager tools for a grocery store, on a relational MySQL database behind a Flask API. Orders take stock in a single atomic step, so two shoppers can never buy the same last item, and the tests prove it against a real MySQL server.
What it does
Categories, products, customers, orders and order items, with foreign keys, unique SKUs and emails, CHECK constraints (stock can't go negative), and indexes on the columns the app searches and sorts by.
JSON endpoints for searching the catalog, adding and editing products, restocking, placing orders, moving them through their statuses, and running reports. Every page in the app is built on it.
An order takes stock with UPDATE … WHERE stock_qty >= qty, inside one transaction. If any item is short, the whole order rolls back and the shopper is told which item, with their cart intact.
Search stock, see what's low or out, restock (added in SQL, so it never overwrites a sale happening at the same moment), add products, and retire them without breaking past orders.
Orders move from processing to shipped to delivered. Cancelling a processing order puts its items back on the shelf; the API refuses moves that don't make sense, like un-delivering.
Revenue by category, top sellers, low stock and orders by status, written as readable SQL that the reports page shows. Revenue uses the price each item sold for, not today's price.
How an order works
The storefront calls GET /api/products with search, category, price and in-stock filters; MySQL does the filtering and sorting.
The cart lives in the browser. Checkout sends one POST /api/orders with the shopper and each product's quantity.
In one transaction, each product's stock drops only if enough is left, in id order so concurrent orders can't deadlock. Any shortfall rolls everything back.
The order and its items are saved with the prices paid, so later price changes never rewrite history or revenue.
See it running
Screenshots from the app running locally with its sample data (flask seed): a 23-product store with two weeks of orders.
# app/orders.py: take the stock, or take nothing for product_id in sorted(lines): # id order: no deadlocks result = session.execute( update(Product) .where(Product.id == product_id, Product.is_active.is_(True), Product.stock_qty >= lines[product_id]) .values(stock_qty=Product.stock_qty - lines[product_id]) ) if result.rowcount != 1: short.append(product_id) if short: raise OrderError("Some items don't have enough stock.", status=409, details={"short": short}) # ...then save the order with the prices paid, and commit. # Any error above rolls the whole transaction back.
✓ Checking and taking the stock is one statement, so the database decides who gets the last item.
Tested
Every push runs the test suite twice in GitHub Actions: on SQLite, and on a MySQL 8.4 server.
Tech Stack
Run it yourself
pip install -r requirements.txt
export DATABASE_URL="mysql+pymysql://user:pass@localhost/grocery" # or skip for SQLite
flask --app app init-db
flask --app app seed
flask --app app run
About this project
I first built this store app with Flask and MySQL. In 2026 I rebuilt it from scratch, with a tested order system and real manager tools.