markdown
Built for the person operating the database, not the person who wrote it.
A dashboard where the database, not the app, does the heavy lifting — and the person clicking buttons never has to know that.
Someone running a warehouse doesn't think in INSERT statements. They think "we got a shipment in" or "we're low on this, order more." This project exists to let that person do exactly that — through forms and buttons — while everything underneath is handled with the same rigor a DBA would insist on: stored procedures, transactions, and views doing real multi-table work, invisibly.
- Why I Built This
- Who It's For
- Project Preview
- Features
- How to Use
- Data Flow
- SQL Logic
- Tech Stack
- Project Structure
- Running Locally
- Design Notes
- Reflection
- About the Author
In most small operations, whoever needs to log a new product or receive a shipment has two bad options: ping a developer every single time, or get handed MySQL Workbench and told to "be careful." Neither scales, and the second one is genuinely dangerous — because almost nothing in this schema is a one-table operation.
Adding a product isn't just a product row. It's a product row and an opening shipment record and a stock entry, created together. Receiving a reorder touches the reorder itself, the product's stock count, and a new stock-entry log — four writes across the system for one real-world event. If a person does that by hand across separate statements and one fails, or they run them in the wrong order, or they just fat-finger a value — the database ends up lying. A product that exists but has zero stock. A shipment with no product behind it. A reorder marked received that never actually updated stock.
I didn't want that failure mode to be possible, regardless of who's clicking the button or how much SQL they know. So the actual work of this project isn't the UI — it's pushing every multi-table write down into a MySQL stored procedure wrapped in a transaction, so the database itself enforces "all of this changes together, or none of it does." Streamlit's only job is collecting clean input and calling the procedure. It holds no business logic of its own — if you deleted main.py and called the procedures from a terminal, you'd get identical guarantees.
- A warehouse or inventory manager who needs to log stock without ever opening a query editor
- A small business owner running their own inventory without a dedicated data or IT team
- Anyone who needs to perform what a DBA would recognize as a real, multi-table database transaction — without knowing what that phrase means, and without being able to break it even if they tried
- 📊 Live dashboard metrics — supplier count, product count, category count, 3-month sales value, 3-month restock value, and items below reorder level with no pending reorder. These come straight from SQL aggregates rather than being pulled into pandas and summed in Python, so the numbers are always a live read of the database, not a cached recalculation.
- 🗃️ Three detail tables — supplier contact directory, products joined with supplier and stock info, and products currently needing reorder. Nobody has to run a join to see the full picture; it's already assembled.
- ➕ Add New Product — inserts the product and mirrors the initial stock as both a shipment record and a stock entry, in one stored procedure call. The person filling this form has no idea three tables just changed — and that's the point. They shouldn't have to.
- 📜 Product History — a unified timeline of every shipment and stock movement for a product, powered by a single SQL view merging two tables. The view exists so this timeline is defined once, in the database, instead of being re-joined by hand every time someone needs it.
- 🔁 Place Reorder — flags a product for restocking against its supplier, so nothing gets reordered against the wrong vendor.
- ✅ Receive Reorder — marks a reorder received, restocks the product, and logs the restock — four table changes wrapped in one transaction. This is the highest-stakes action in the app: if it applied only partially, you'd have a reorder marked "received" while the shelf count never moved. The transaction makes that impossible.
Live counts and values pulled straight from the database, plus supplier and product detail tables — the person opening the dashboard sees the current state of things with zero setup.
 |
 |
 |
Switch to Operational Tasks in the sidebar to choose one of four actions.
Add New Product — fill in name, category, price, stock, reorder level, and supplier. Behind this one form: a product row, a shipment record, and a stock entry, all committed together.
 |
 |
Product History — pick any product to see its full shipment and stock-entry timeline in one chronological list, not two tables the person has to mentally merge.
Place Reorder — select a product and quantity to flag it for restocking.
 |
 |
Receive Reorder — mark a placed reorder as received once stock physically arrives. One click; four tables update in lockstep behind it.
 |
 |
 |
Streamlit UI (main.py)
│
▼
db_functions.py ──► MySQL (project_py_sql)
│
┌─────────┴─────────┐
▼ ▼
product_inventory_history stored procedures
(VIEW — merges shipments (AddNewProductManualID,
+ stock_entries) MarkreorderAsRecieved)
The split here isn't arbitrary — it mirrors risk. Reads (metrics, tables, dropdowns, history) can't leave the database inconsistent no matter how they're written, so they go through as plain parameterized SQL. Anything that writes to more than one table goes through a stored procedure instead, so the line in the code is drawn exactly where the actual danger is: multi-table writes, and only multi-table writes, are wrapped in a transaction.
All SQL used by this project lives in sql/, kept separate from the app code so the logic that actually matters is easy to review on its own, without wading through UI code to find it:
sql/schema.sql— table definitions, theproduct_inventory_historyview, and both stored proceduressql/queries.sql— every querydb_functions.pyruns directly, organized by the function that uses it
| Technology | Role | Why It's Here |
|---|---|---|
| Python 3 | Core language | Glue between the UI and the database — deliberately thin |
| Streamlit | Dashboard UI | Fast to build a form-based front end without writing HTML/JS |
| MySQL | Data storage, stored procedures, views | Where the actual logic and integrity guarantees live |
| mysql-connector-python | Python ↔ MySQL connectivity | Calls stored procedures and runs parameterized queries |
| Pandas | Rendering query results as tables in the UI | Turns query output into something a non-technical user can read |
Inventory-Supply-Chain-Dashboard/
├── .streamlit/
│ └── secrets.toml.example # template — copy to secrets.toml locally
├── datasets/ # sample CSVs used to seed the database
│ ├── products.csv
│ ├── reorders.csv
│ ├── shipments.csv
│ ├── stock_entries.csv
│ └── suppliers.csv
├── sql/
│ ├── schema.sql # tables, view, stored procedures
│ └── queries.sql # every query used by db_functions.py
├── screenshots/ # images used in this README
├── db_functions.py # all database access lives here
├── main.py # Streamlit UI
├── requirements.txt
├── .gitignore
└── README.md
- Python 3.10+
- MySQL Server running locally
# Clone the repository
git clone https://github.com/Rushit004/InvenSQL.git
cd InvenSQL
# Create a virtual environment & install dependencies
python -m venv venv
venv\Scripts\activate # Windows
# source venv/bin/activate # macOS/Linux
pip install -r requirements.txt
# Set up the database
mysql -u root -p < sql/schema.sql
# then load the sample data from datasets/ into your tables
# Add your local MySQL credentials
mkdir .streamlit
copy .streamlit\secrets.toml.example .streamlit\secrets.toml
# edit secrets.toml with your MySQL host/user/password
# Run the app
streamlit run main.pyThis app connects to a local MySQL instance and isn't deployed publicly — several actions here (adding products, placing and receiving reorders) write directly to real data, so it's meant to be run and demoed locally rather than hosted with public write access.
- IDs are generated with
SELECT MAX(id) + 1inside each procedure, notAUTO_INCREMENT. This keeps ID assignment as a deliberate step inside the same transaction as the rest of the write, rather than an implicit side effect that happens outside the app's control. For a single-operator tool like this, that trade is worth it — every ID the app hands out is one it can account for. - Multi-table writes are stored procedures, not sequential Python calls. A chain of
INSERTs in Python has a gap between each call where a crash, a dropped connection, or a bad value leaves the database in a state nobody intended. A stored procedure wrapped in a transaction closes that gap entirely — it's one commit, or none.
This project was never really about the UI. It was an exercise in figuring out where logic belongs — and deciding, on purpose, that it didn't belong in Python. Every place where a naive version of this app would've chained together a few INSERT calls, I wrote a stored procedure instead. Every place I'd have re-joined the same two tables for the third time, I wrote a view once. The result is a small app that behaves like it should be trusted with someone else's real data — because the person using it isn't going to be checking my work.
Rushit Tholiya LinkedIn Profile