Inventory Optimization System
for Gas Station Networks
Sandia Oil's sales and inventory data lived in legacy files no one could query. Their spoilage decisions lived in store managers' heads. Orderly Systems pulled it all into one place, built dashboards that put waste front and center, and let the team ask plain-English questions instead of running reports.
Overview
Sandia Oil runs gas station convenience stores across New Mexico and Arizona. Their POS data sat in formats from another era. Their best store managers had ordering and spoilage instincts that never made it past their own four walls. Orderly Systems built one platform that brings every store's data into the same place, surfaces spoilage by item and store, and turns plain-English questions into answers in seconds. The system is live at app.sandiaoil.com.
The Challenge
Sandia needed to pull every store's history out of legacy files, capture the spoilage logic that good managers used by feel, and put it in a tool the rest of the network could use without a SQL class.
The system had to handle messy legacy formats (fixed-width MS-DOS text, old Excel files, RTF), keep small details intact (like leading zeros in product IDs), and run inside Sandia's existing AWS account. Orderly Systems owned the full path from raw data file to plain-English answer.
Our Approach
Orderly Systems delivered in four phases, validating each one before adding the next.
Phase 1: Foundation & Data Ingestion
Established AWS infrastructure and secure data intake. Configured S3 bucket, AWS Transfer Family SFTP endpoint with Lambda authentication, and Terraform layers for reproducible deployments.
Phase 2: ETL & Database
Designed PostgreSQL schema with tables for sales, purchases, inventory, and waste/spoilage. Built Python loaders for each data type, handling fixed-width parsing, date inference, and edge cases.
Phase 3: Application Layer
Built the Next.js 16 + React 19 frontend and Fastify backend. Implemented Cognito authentication, conversation persistence, and dashboard API endpoints. Deployed to ECS Fargate.
Phase 4: Intelligence & Dashboards
Integrated AWS Bedrock (Claude Sonnet) for natural language → SQL. Added spoilage-first dashboard with 15 views: KPIs, charts, item-level drill-downs, quadrant analysis, and store comparison.
Key Deliverables
Data Ingestion
- SFTP endpoint with Lambda auth
- S3 bucket structure by data type
- Seven Python loaders for legacy formats
- Column mapping documentation
Database
- PostgreSQL RDS in private subnet
- Tables for sales, purchases, inventory, waste
- Schema documentation with query examples
Web Application
- Next.js 16 + React 19 + Tailwind
- Chat interface with natural language → SQL
- Conversation history and follow-up suggestions
- Dashboard: Overview, Items, Drill-down, Compare
Dashboard Views
- KPIs: spoilage $, trend, rate
- Charts: by store, department, vs sales
- Item-level: quadrant, dead stock, comparison
- Global filters persisted in URL
Infrastructure
- Terraform (3 layers, S3 backend)
- VPC, ALB, ECS Fargate, RDS, Cognito
- GitHub Actions for deployment
Outcomes
Sales transactions loaded and queryable
SQL required, natural language queries for ad-hoc analysis
Dashboard views for instant spoilage and sales insights
Items tracked across the network
Spoilage visibility by store, department, and item
All stores in one platform, single source of truth
Sandia now has one knowledge layer for the whole network. Every sale, every count, and every spoilage event lives in one place. Managers ask questions in plain English. As Sandia adds stores or new data sources, the system grows with them, and the next decision is one prompt away from an answer.
Technologies Used
Is your data trapped in old systems?
Orderly Systems builds the knowledge layer that lets a business run on more than gut feel. If your decisions live in a few people's heads and your data lives in files no one can query, we should talk.