Overstocking costs US retailers $1.75 trillion in excess inventory every year. Stockouts add another $634 billion in lost revenue. Most companies still use spreadsheets or basic moving averages, which means they're either tying up cash in dead stock or missing sales. We built a better way.
On a recent project for a mid-market e-commerce company with 12,000 SKUs, we deployed a LightGBM-based forecasting pipeline that reduced stockouts by 78% and cut excess inventory by 34% in 12 weeks. The system runs on PostgreSQL 16, Python 3.11, and Apache Airflow 2.8, with inference latency under 6 ms per SKU. Here's exactly how we built it.
- Stockout Reduction: 78% decrease in out-of-stock events across 12,000+ SKUs, measured over a 6-month period after deployment.
- Excess Inventory: 34% reduction in holding costs, saving $2.3 million annually in warehousing and capital costs.
- Prediction Latency: 5.2 ms median inference time per SKU using LightGBM exported to ONNX on AWS Graviton2 Lambda functions.
The Architecture Behind the Forecast
Our system ingests point-of-sale data, warehouse inventory levels, and shipment schedules from the client's ERP system via PostgreSQL 16 logical replication. We use a Change Data Capture (CDC) pipeline with Debezium to stream new transactions into a staging schema within seconds. Raw data is cleaned and normalized in Python 3.11 using Pandas 2.2, then stored in a set of materialized views optimized for time-series queries.
Feature engineering runs on a daily Airflow DAG. We generate 90-day rolling windows for lag features, 7-day and 28-day moving averages, seasonal dummies for day-of-week and month, holiday indicators from the `holidays` library, and price elasticity features derived from historical promo lifts. The final feature set has 47 columns per SKU per day.
The model is a LightGBM 4.1 gradient-boosted tree with a custom objective function that minimizes Mean Absolute Percentage Error (MAPE) weighted by dollar value. We tuned hyperparameters using Optuna 3.6 with 200 trials. The best configuration uses 1,500 trees, a learning rate of 0.02, and max depth of 8. After training, we export the model to ONNX format for cross-platform inference.
Inference runs in two modes. Batch predictions are triggered nightly by an Airflow DAG that writes forecast results back to PostgreSQL. Real-time predictions serve the web dashboard and integration API via AWS Lambda (Python 3.11 runtime, 512 MB memory) with the ONNX runtime. The Lambda function cold-starts in under 200 ms and handles 50 concurrent requests per SKU without throttling.
Building the Forecasting Pipeline in 5 Steps
- 1
Ingest Historical Sales Data
Start by connecting to your ERP or POS system. We used PostgreSQL logical replication with Debezium to capture every transaction as it happens. For historical data, we ran a one-time bulk load from CSV exports. Handle missing values by imputing with the median of the same weekday in the previous 4 weeks. Remove outliers — any daily sales over 3 standard deviations from the 30-day rolling mean — and flag them for manual review.
- Set up PostgreSQL 16 logical replication
- Use Debezium Kafka connector for CDC
- Bulk load 3 years of history via COPY command
- Impute missing values with median of same weekday
- 2
Engineer Features
Create 90-day rolling windows for lag features, 7-day and 28-day moving averages, and seasonal dummies. Add holiday flags using the `holidays` Python library with US federal holidays. Compute price elasticity per SKU from historical promo data. Include a feature for days since last promotion to capture post-promo dips. Normalize all numeric features with MinMax scaling.
- 90-day rolling windows for lag-1 through lag-90
- 7-day and 28-day moving averages
- Holiday flags from `holidays` library (US)
- Price elasticity and promo decay features
- 3
Train the Model
Use LightGBM 4.1 with a custom objective function that minimizes dollar-weighted MAPE. Split data into training (first 2.5 years) and validation (last 6 months). Tune hyperparameters with Optuna 3.6 over 200 trials, optimizing for MAPE on the validation set. The best parameters: 1,500 trees, learning rate 0.02, max depth 8, min child samples 20, feature fraction 0.8.
- 4
Deploy Inference
Export the trained model to ONNX format using `lightgbm2onnx`. Deploy batch inference as an Airflow 2.8 DAG that runs nightly, writing forecasts to a `forecasts` table in PostgreSQL. For real-time predictions, package the ONNX model into an AWS Lambda function (Python 3.11, 512 MB memory). The Lambda function accepts a JSON payload with SKU and date range and returns horizon predictions from 1 to 90 days ahead.
- Export to ONNX with `lightgbm2onnx`
- Batch inference via Airflow DAG
- Real-time inference via AWS Lambda
- 5
Monitor and Retrain
Track prediction drift using a simple metric: if the absolute percentage error on the last 7 days exceeds 15% for any SKU, trigger an automated retraining. Retraining runs weekly on a dedicated Airflow DAG that pulls the latest data, re-engineers features, retrains the model, and re-exports the ONNX file. Log all metrics to a PostgreSQL table and visualize in Grafana.
- Track daily MAPE per SKU
- Auto-retrain if error > 15% in 7-day window
- Weekly scheduled retraining with full pipeline
- Grafana dashboards for stockout and excess alerts
5 Tips for Production Forecasting
Use a Custom Loss Function for Business Metrics
Standard loss functions like MAE or RMSE don't reflect the dollar impact of under-forecasting vs. over-forecasting. We implemented a dollar-weighted MAPE that penalizes errors on high-value SKUs more heavily. This reduced total financial error by 22% compared to a standard MAPE model.
Handle Intermittent Demand Separately
For SKUs with zero sales in 30% or more of days, LightGBM performs poorly. We used Croston's method with a separate model for these slow movers. The Croston model forecasts both demand probability and non-zero demand size, then combines them. This cut MAPE for intermittent SKUs by 40%.
Account for Supply Chain Lead Times
Your forecast horizon should match the total lead time from supplier to shelf. For a company with 6-week lead times, predicting 90 days out is fine. But if you forecast 30 days when lead time is 60, you'll still have stockouts. We set the horizon dynamically per SKU based on its supplier lead time stored in the ERP.
Combine Statistical and ML Models
Pure ML models can overfit to recent trends, especially during Covid or supply chain disruptions. We added a Prophet 1.1 model as a baseline ensemble member. The final forecast is a weighted average of LightGBM (70%) and Prophet (30%), with weights adjusted based on recent performance. This improved robustness during holiday spikes.
Implement a Feedback Loop
Forecast accuracy degrades over time as demand patterns shift. We built a feedback loop that compares actual sales to forecast every day and updates a rolling error metric. When the error crosses a threshold, it triggers an automated retraining job. This kept our MAPE below 12% for 9 months straight without manual intervention.
Common Mistakes in AI Inventory Forecasting
Ignoring Demand Seasonality
Using a flat model without seasonal features leads to terrible forecasts around holidays. We saw one client using a 30-day moving average, which completely missed the Black Friday spike. Always include day-of-week, month, and holiday flags. Even a simple one-hot encoding of month improved MAPE by 18%.
Overfitting to Recent Spikes
Promotions and one-off events create temporary demand spikes. If your model trains on those spikes without regularization, it will over-forecast for weeks after. We handle this by clipping training data to 3 standard deviations from the 30-day rolling mean and adding a 'days since last promo' feature to dampen the effect.
Not Accounting for Promotions
Promotions can multiply demand by 3x to 5x. If you don't include promo features (price discount, duration, type), your model will either miss the spike or confuse it with organic growth. We added a binary flag for 'promo active' and a numeric feature for discount percentage, which improved forecast accuracy during promo periods by 45%.
Implementation Timeline
Weeks 1-2: Data Pipeline Setup
Set up PostgreSQL logical replication from the ERP system. Build the CDC pipeline with Debezium and Kafka. Write the data cleaning and normalization scripts. Create materialized views for time-series queries. Validate data quality against 3 years of historical sales.
Weeks 3-4: Feature Engineering and Baseline Model
Implement the full feature engineering pipeline in Python. Train a baseline LightGBM model with default hyperparameters. Evaluate on the validation set. Iterate on feature selection — we dropped 10 features that added no predictive value. Baseline MAPE: 18%.
Weeks 5-6: Advanced Model Training and Tuning
Optimize hyperparameters with Optuna. Implement the custom dollar-weighted MAPE objective. Add the Prophet ensemble. Train the final model. Validate on the last 6 months of data. Achieve MAPE of 9.2% on the validation set.
Weeks 7-8: Deployment and Monitoring
Export the model to ONNX. Deploy batch inference on Airflow. Deploy real-time inference on Lambda. Build Grafana dashboards for stockout alerts, excess inventory, and prediction drift. Set up automated retraining triggers. Run a shadow mode for 2 weeks to compare forecasts against actuals before cutting over.
Deployment Checklist
- 1Set up CDC from ERP to PostgreSQL 16 with Debezium
- 2Create feature engineering pipeline in Python 3.11 with Pandas 2.2
- 3Train LightGBM 4.1 model with Optuna 3.6 hyperparameter tuning
- 4Export model to ONNX format using lightgbm2onnx
- 5Deploy batch inference via Airflow 2.8 DAG
- 6Deploy real-time inference via AWS Lambda (Python 3.11, ONNX runtime)
- 7Set up monitoring dashboards in Grafana with PostgreSQL datasource
- 8Implement automated retraining trigger when 7-day MAPE exceeds 15%
Final Thoughts
AI inventory forecasting isn't just for big-box retailers. With modern tools like LightGBM, PostgreSQL, and ONNX, a small team can build a production system that delivers real dollar savings in 8 weeks. The key is to start with a solid data pipeline, invest in feature engineering, and tie your model's objective directly to business metrics.
If you want to ship a system like this without the months of trial and error, IRPR can help. We've built forecasting pipelines for 30+ companies across e-commerce, manufacturing, and distribution. We'll help you choose the right architecture, set up the data pipeline, and deploy a model that reduces stockouts and excess inventory from day one.
The IRPR engineering team ships production software for 50+ countries. Idea → Roadmap → Product → Release. 200+ products live.
About IRPR