About the Project
Inspiration
Modern supply chains generate massive amounts of operational data—purchase orders, production milestones, shipping updates, and delivery confirmations. However, in many organizations this data is fragmented across systems and rarely analyzed beyond basic reporting.
The inspiration for FlowSight – Supply Chain Bottleneck Intelligence came from a simple observation:
delays are usually visible in the data long before they are noticed by humans.
Rather than building another transactional system, this project focuses on transforming existing operational events into actionable intelligence. The goal was to demonstrate how a relational database like MariaDB can power real-world analytics that help organizations identify bottlenecks, vendor issues, and delivery risks early.
What We Learned
This project reinforced several important lessons:
- Analytics-first design matters: Modeling lifecycle events as time-series data enables far more insight than flat status fields.
- SQL is extremely powerful: With proper indexing, aggregation, and window-style queries, MariaDB can handle complex analytics without external engines.
- Explainability beats black-box AI: Simple, transparent rules (e.g., SLA breaches, historical averages) are easier to trust and validate than opaque models.
- Schema design impacts performance: Small decisions like composite indexes and event normalization significantly affect query speed and clarity.
- Realistic data is essential: Generating believable seed data exposed edge cases that would never appear with random values.
How We Built the Project
Architecture Overview
The system follows a clean, layered architecture:
- Backend: Laravel handles business logic and exposes RESTful analytics APIs.
- Database: MariaDB serves as the primary data store and analytics engine.
- Analytics Layer: SQL queries compute lead times, SLA breaches, bottlenecks, and risk scores.
- Presentation: A lightweight dashboard (or API consumers) visualizes insights.
All analytics are computed directly from MariaDB using indexed queries and aggregations.
Data Modeling
At the core of FlowSight is an event-driven data model.
Each order progresses through multiple lifecycle events (e.g., CONFIRMED, PACKED, DISPATCHED, DELIVERED). Instead of overwriting a single status field, every transition is stored as a timestamped event.
This allows the system to calculate:
[ \text{Stage Duration} = t_{\text{next event}} - t_{\text{current event}} ]
By aggregating these durations across thousands of orders, FlowSight identifies which stages consistently consume the most time and where delays originate.
Analytics Implementation
Key analytics include:
Bottleneck Detection
Average and peak durations for each stage transition (e.g.,PACKED → DISPATCHED), ranked by impact.Vendor & Carrier Scorecards
On-time delivery rates and average lateness, derived from historical performance.SLA Breach Analysis
Comparison of actual stage durations against predefined SLA thresholds.Risk Scoring for Active Orders
Each active order receives a transparent risk score based on:- Missed promised delivery dates
- Time spent in the current stage vs. historical averages
- Vendor and carrier reliability
These metrics are computed using indexed joins, aggregations, and time-based SQL calculations directly in MariaDB.
Challenges Faced
Designing for Analytics, Not Just Transactions
It was tempting to store only the “current status” of an order. Instead, we had to deliberately design for historical analysis, which required an event-based schema and more careful querying.
Query Performance vs. Readability
Some analytics queries became complex due to multiple joins and time calculations. Balancing performance, clarity, and maintainability required thoughtful indexing and query decomposition.
Generating Realistic Data
Random data did not produce meaningful bottlenecks. We had to intentionally simulate:
- Vendor-specific delays
- Carrier reliability differences
- Seasonal variations in delivery times
This made the analytics far more realistic and insightful.
Avoiding Over-Engineering
While advanced machine learning could be added, we intentionally focused on SQL-driven intelligence to keep the system explainable, reliable, and aligned with the project goals.
Conclusion
FlowSight demonstrates that MariaDB is not just a storage layer, but a capable analytics engine when paired with thoughtful schema design and SQL-driven insights.
By focusing on real-world supply chain problems—bottlenecks, delays, and accountability—this project shows how data that already exists in organizations can be transformed into actionable intelligence without complex infrastructure.
The result is a practical, scalable proof-of-concept that highlights the power of relational databases in modern analytical applications.
Log in or sign up for Devpost to join the conversation.