Inventory Optimization System
for Gas Station Networks
Ship AI Consulting built Sandia Oil's inventory platform, connecting their sales and inventory data, building dashboards that put spoilage front and center, and letting managers ask questions in plain English instead of running reports.
Overview
Sandia Oil runs gas station convenience stores across New Mexico and Arizona. They needed a clearer picture of what was on their shelves, and smarter guidance on what to order, when, and how much. Ship AI Consulting built an inventory optimization platform that pulls all that data into one place, surfaces spoilage insights, and lets managers ask questions in plain English instead of running complex reports. The system is live at app.sandiaoil.com.
The Challenge
The client needed a platform that could unify fragmented POS data, surface spoilage insights, and guide ordering decisions, without requiring managers to write SQL or navigate complex BI tools.
The solution had to handle fixed-width MS-DOS text, legacy Excel (.xls), and RTF formats; preserve data integrity (e.g., leading zeros in PLUs); and integrate with existing AWS infrastructure. Security, reliability, and maintainability were non-negotiable.
Our Approach
Ship AI Consulting took a phased, modular approach to delivery:
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
The platform is in production at app.sandiaoil.com, serving Sandia Oil with data-driven visibility into spoilage, sales, and inventory. Modular design, comprehensive documentation, and phased delivery ensure the system remains maintainable as requirements evolve toward demand forecasting and order optimization.
Technologies Used
Let's Build Something Together
Ship AI Consulting specializes in full-stack development, data pipelines, and cloud infrastructure. If you're planning a similar platform, whether for retail, inventory, or analytics, we'd welcome the chance to discuss your requirements.