Shopify Demand & Inventory Copilot

A production-grade demand forecasting and inventory-risk system for Shopify merchants: a live GraphQL Admin API integration (OAuth install, webhooks, bulk operations), per-SKU forecasting with an eval-gated promotion process, and every forecast translated into an estimated dollar impact versus a naive baseline -- not just an accuracy score.

This is built as an installable, multi-tenant Shopify app (any merchant can install it via OAuth), not a tool wired to one personal store.

Status: Phase 4 of 4 (complete)

Phase	Scope	Status
0	Repo scaffold, schema, local Postgres	Done
1	Seasonality-aware data generation, Shopify OAuth install flow, webhook ingestion (HMAC-verified), bulk-operation reconciliation	Done
2	dbt aggregation (daily_sku_sales), naive baseline, backtest harness, $-impact methodology	Done
3	Per-SKU LightGBM model, MLflow tracking, promotion gate	Done -- see REPORTS.md
4	FastAPI serving (/forecast, /reorder, /impact-report), MCP tools, Streamlit dashboard, Evidently monitoring, full Docker Compose stack	Done (this pass)

Headline result (see REPORTS.md for the full, real MLflow-logged history): the promoted forecasting model has worse raw accuracy than the naive baseline (6.69 vs. 5.39 units MAE) but delivers +$11,593.21 over the baseline across a 12-week/24-SKU backtest, because it was trained toward the newsvendor-optimal quantile instead of the conditional mean. Three earlier, accuracy-competitive candidates were correctly rejected by the promotion gate before this fix, along with a deliberately weak model on every run -- real evidence the gate evaluates dollars, not accuracy.

What's real vs. what needs live Shopify credentials

There's no Shopify Partner account or dev store yet, so everything is built in two tracks:

• Runs today, no Shopify credentials needed: local Postgres, the seasonality/promo/catalog generators, data_generator/seed_local.py (writes synthetic order history directly into Postgres), the webhook listener + HMAC verification (tested with locally-crafted signed payloads).
• Implemented against the real Shopify API contract, but requires a live Partner dev store (or a real merchant install) to actually run: shopify_client/oauth.py (app install flow), data_generator/shopify_seed.py (seeds history via real orderCreate mutations), ingestion/bulk_backfill.py (weekly reconciliation via bulkOperationRunQuery).

Creating a Shopify Partner account and a free development store (no credit card, ~10 minutes) is what unlocks live end-to-end testing of the second track. Until then, the first track proves the ingestion/storage contract works correctly on its own.

Repo layout

data_generator/    seasonality + promo + catalog models, local and live seeding
shopify_client/    Admin GraphQL client, OAuth install flow, bulk operations
ingestion/         FastAPI app: webhook listener (HMAC-verified) + OAuth routes, bulk reconciliation job
dbt/               staging models + daily_sku_sales mart
forecasting/       naive baseline, $-value dollarization, backtest harness, LightGBM training, MLflow promotion gate
serving/           GET /forecast, /reorder, /impact-report business logic, mounted into ingestion's FastAPI app
mcp_server/        MCP tool wrappers over serving/, for use from Claude Desktop/Code or any MCP client
dashboard/         Streamlit dashboard leading with the live $-impact headline number
monitoring/        Evidently feature-drift report + a custom $-impact-degradation trend check
db/                schema.sql, tiny psycopg2 connection helper
tests/             pytest suite
.github/workflows/ CI (tests on push/PR) + scheduled weekly retrain (+ drift check)
Dockerfile         image shared by the api/dashboard/mlflow-ui docker-compose services
docker-compose.yml postgres + api + dashboard + mlflow-ui for local dev
REPORTS.md         Phase 3 baseline-vs-model comparison, accuracy and dollar terms

Running Phase 1 locally

1. Start Postgres

   docker compose up -d postgres
   

   This applies db/schema.sql automatically on first start.

2. Set up a virtualenv and install deps

   python -m venv .venv
   .venv/Scripts/activate   # or source .venv/bin/activate on macOS/Linux
   pip install -r requirements.txt
   cp .env.example .env
   

   Generate a TOKEN_ENCRYPTION_KEY:

   python -c "from cryptography.fernet import Fernet; print(Fernet.generate_key().decode())"
   

3. Seed 18-24 months of synthetic order history

   python -m data_generator.seed_local --months 20
   

   Re-run with --reset to regenerate. This writes a dev shop, a 24-SKU catalog with Pareto-ish popularity, and daily orders driven by a trend + weekday seasonality + quarterly/BFCM promo-spike curve.

4. Run the ingestion API

   uvicorn ingestion.main:app --reload
   

   GET /health, GET /auth?shop=... (install flow), POST /webhooks (HMAC-verified orders/create / orders/updated / inventory_levels/update).

5. Run the tests

   pytest tests/
   

   Covers: HMAC accepts a correctly-signed payload and rejects a forged or missing signature (both as pure-function checks and as full webhook-endpoint integration checks that assert nothing is written to the DB on rejection).

6. Build the daily_sku_sales mart with dbt

   dbt run --project-dir dbt --profiles-dir dbt
   

   Aggregates orders + order_line_items into daily per-SKU unit sales. The dbt profile defaults to the docker-compose Postgres credentials, so no extra config is needed for local dev.

7. Run the backtest harness

   python -m forecasting.backtest
   

   Replays the last 12 weeks for every product, using a trailing 4-week moving average as the naive baseline (forecasting/baseline.py), and writes each SKU-week's actual/predicted/baseline/dollar-impact into backtest_results. Phase 2 has no trained model yet, so predicted_units is deliberately set equal to the baseline -- the headline dollar impact should print as $0.00, which is the expected, correct result: it proves the harness and the dollarization math (forecasting/backtest.py::dollarize_forecast_error) are wired correctly without a real model muddying the signal. Phase 3's trained model plugs into this same harness as a genuinely different predicted_units and is expected to produce a non-zero (ideally positive) number.

8. Train the model, run the promotion gate, and see the real comparison

   python -m forecasting.train
   

   Trains a global LightGBM model (product_id as a categorical feature) plus a deliberately weak DummyRegressor "control" candidate, backtests both through the exact same harness as the baseline, logs everything to MLflow (MLFLOW_TRACKING_URI, defaults to a local sqlite:///mlflow.db so Model Registry stage transitions work without a real server), and only promotes a candidate to the Production stage if it beats both the baseline and whatever's currently deployed, in dollars (forecasting/registry.py). Prints a comparison table at the end; see REPORTS.md for the full real run history from this project's own development, including multiple rejections.

   mlflow ui --backend-store-uri sqlite:///mlflow.db
   

   to browse runs/versions/stage transitions in the MLflow UI.

Running Phase 4 locally

9. Serve live forecasts (after step 8 has promoted a model -- otherwise these fall back to the naive baseline)

   uvicorn ingestion.main:app --reload
   

   GET /forecast?shop_domain=...&sku=..., GET /reorder?shop_domain=...&sku=..., GET /impact-report?shop_domain=....

10. Run the dashboard

    streamlit run dashboard/app.py
    

    Leads with the live $-impact-vs-baseline headline number (serving/impact_report_service.py), then a per-SKU breakdown and the reorder-recommendations table.

11. Register the MCP server (wraps the same serving/ logic as three MCP tools: get_forecast_tool, get_reorder_recommendation_tool, get_impact_report_tool)

    claude mcp add shopico -- python -m mcp_server.server
    

    Or run directly with python -m mcp_server.server (stdio transport) to register with any other MCP client.

12. Check for degrading $-impact or feature drift

    python -m monitoring.drift_monitor
    

    Prints an early-half-vs-recent-half $-impact trend for the active model and writes an Evidently feature-drift HTML report to monitoring/reports/. Wired into the weekly retrain workflow as a post-training step.

13. Or run the whole stack in Docker

    docker compose up -d --build
    

    api (port 8000, ingestion + serving), dashboard (port 8501), mlflow-ui (port 5000) all bind-mount the host's ./mlflow.db, so they see whatever step 8 already promoted rather than starting from an empty registry.

Once a Partner dev store exists

python -m data_generator.shopify_seed --months 20   # seeds via real orderCreate mutations
python -m ingestion.bulk_backfill --shop-domain your-store.myshopify.com --access-token <token> --since-days 7

And point the app's /auth?shop=your-store.myshopify.com at a running ingestion.main:app (with SHOPIFY_APP_URL set to a publicly reachable URL, e.g. via ngrok for local dev) to exercise the full install flow.

Design notes worth knowing

• products.shopify_inventory_item_id is an addition beyond the original spec's schema: inventory_levels/update webhooks key off inventory_item_id, not SKU, so there's no way to route that event to a product row without it.
• Webhook ingestion and bulk-operation reconciliation intentionally write through the same handle_order_webhook function (ingestion/webhook_listener.py), so both paths agree by construction on overlapping records.
• Everything uses a single psycopg2 (sync) driver, no ORM -- matches the spec's "right-sized, not cloud-scale" philosophy (see original spec §7).
• dollarize_forecast_error's sign convention was corrected vs. the original spec pseudocode. The pseudocode's cost_of_error returns a positive cost for the stockout branch but a negative value for the overstock branch. Walk through a case where the model forecasts perfectly and the baseline over-forecasts: baseline_cost - model_cost should be positive (model saved money by avoiding the baseline's excess inventory), but the pseudocode's sign makes it negative -- and more negative the worse the baseline overstocks, inverting the stated "positive = value created" meaning. Fixed by having cost_of_error always return a positive cost magnitude in both branches. See forecasting/backtest.py's module docstring and tests/test_dollar_impact_calc.py::test_overstock_scenario_* for the hand-verified case that pins this down.
• The naive baseline (forecasting/baseline.py) is a trailing 4-week moving average of weekly units sold, computed strictly from weeks before the target week (no leakage) -- the standard, easy-to-hand-verify "seasonal naive" baseline for weekly-bucketed retail demand.
• A single global LightGBM model, not per-SKU models. The spec allows "per-SKU (or per-SKU-cluster, for low-volume SKUs)"; with ~600-900 days of history per SKU, one shared model with product_id as a categorical feature borrows statistical strength across SKUs far better than fitting 24 independent models in isolation, especially for the long-tail ones.
• Trained toward a cost-optimal quantile, not the conditional mean. dollarize_forecast_error's cost is asymmetric (stockouts cost a full unit's margin, overstock only a small holding fee), so a plain L2 regression model has no way to know that slight over-forecasting is usually cheaper than under-forecasting. This is the classic newsvendor problem; forecasting/train.py::newsvendor_critical_fractile derives the optimal LightGBM quantile objective alpha from each product's real unit economics rather than guessing. See REPORTS.md for what this fix actually changed (rejected → promoted, despite worse MAE).
• forecasting/backtest.py::write_backtest_results deletes existing rows for a model_version before inserting -- discovered this needed to be idempotent while iterating during Phase 3 development, when re-running training for the same day's model label was silently accumulating duplicate rows and corrupting total_dollar_impact's aggregation.
• The promotion gate uses MLflow's Model Registry Production stage (via MlflowClient.transition_model_version_stage) as the single source of truth for "what's currently deployed," rather than a custom state table. That API is deprecated as of MLflow 2.9 in favor of model aliases, but still functional as of 2.19 (used here) -- worth revisiting if this project upgrades MLflow significantly later.
• serving/model_loader.py reuses forecasting/train.py::make_predict_fn directly (loading the model via mlflow.lightgbm.load_model, not mlflow.pyfunc) so live inference goes through the exact same feature-building code path already validated in backtest -- no separate, potentially-drifted serving-time implementation. It caches the loaded model by MLflow registry version number (immutable once registered, unlike "Production" which changes meaning on every promotion), since callers that loop over many SKUs for one shop (dashboard/app.py, monitoring/drift_monitor.py) would otherwise reload the same model from disk once per SKU.
• forecasting/train.py never persisted the naive baseline's own backtest_results rows (only trained candidates go through write_backtest_results) -- a real gap this pass's serving/impact_report_service.py had to work around: if the baseline has no rows for a shop, it's recomputed live via forecasting.backtest.run_backtest instead of silently reporting a zero MAE.
• The reorder policy (serving/reorder_service.py) is a documented assumption, not real Shopify data: LEAD_TIME_WEEKS/SAFETY_STOCK_WEEKS/ OVERSTOCK_WEEKS_OF_COVER are a standard weeks-of-cover policy, overridable via env vars, until a live merchant's actual lead times exist.
• fastapi was bumped from 0.115.6 to 0.139.0 (and pydantic 2.10.5 to 2.11.0) in this pass: fastapi 0.115.6 pinned starlette<0.42, which can't coexist in one virtualenv with the starlette version streamlit 1.59.2 requires, and mcp 1.28.1 requires pydantic>=2.11. Existing tests (tests/test_webhook_hmac.py, the OAuth routes) still pass unchanged.
• The Docker image needs libgomp1 (apt-get install) -- python:3.11-slim doesn't include it, and LightGBM's native library won't load without it (OSError: libgomp.so.1: cannot open shared object file).
• Evidently alone only compares feature distributions, which wouldn't catch a model that's still "in-distribution" but has quietly stopped beating the baseline in dollars -- the metric this whole project is built around. So monitoring/drift_monitor.py runs a custom early-half-vs-recent-half $-impact trend check (flags >30% relative degradation, or a positive→negative flip) alongside Evidently's feature-drift report, rather than relying on feature drift as a proxy for the thing that actually matters.

All four planned phases are now complete. Natural next steps beyond the original scope: replacing MLflow's deprecated stage-based registry with aliases, real Shopify Partner dev-store credentials to exercise the OAuth/bulk paths end-to-end, and per-SKU (rather than catalog-average) lead times once real merchant inventory data exists.