# 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](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](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](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](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.