Order management with delivery clustering
A MySQL-backed order-management console in Streamlit: full CRUD over customers and orders, K-means clustering of customers into delivery zones, driver assignment and reassignment, with audit-log and backup tables doing real DBMS work.
The brief
A delivery-operations console for a DBMS course: add, view, edit and remove customers and orders through a Streamlit app; cluster customers by latitude and longitude into delivery zones with K-means; assign drivers to zones and reassign orders between them. Every reassignment writes to a driver log, and originals are preserved in a backup table. A query console runs arbitrary SQL against the schema.
The schema is the point
Eight tables: orders, customers, clusters, delivery partners, a driver log, a reassigned-orders table, a partner-pincode mapping and an order backup. That covers normalization, foreign-key relationships, audit logging and backup strategy, which is the course material expressed as a working system rather than an ER diagram in a slide deck.
The analytics twist (geo-clustering for logistics) is a smaller sibling of the rerouting recommender I later shipped at CommunityFarm: the same instinct, first practised here against a course schema.
The honest bit
Course-project code: queries are built with string formatting, which is SQL-injection-prone and would never survive a review at work. It demonstrated what it needed to demonstrate, and the distance between this and, say, Bill2Go’s server-validated write paths is a fair measure of the intervening three years.