shiva-1993/petstore-scm-mcp-backend

🐾 Petopia Intelligence Hub

A pet-retail supply-chain platform where an AI agent reasons over a live data warehouse and tells you what to do — not just what happened.

  

Live demo: scm-using-mcp-and-llm.vercel.app · API: HF Space · Data source check: `/diagnostics`

🎯 Recruiter TL;DR

• —What it is: An end-to-end supply-chain intelligence platform for a premium pet retailer — a multi-LLM ReAct agent (over 54 MCP tools, including ad-hoc SQL) on top of a real PostgreSQL data warehouse, a dbt transformation layer (runnable on Postgres or DuckDB), a forecasting ensemble with intermittent-demand routing, a suite of operational intelligence engines (stockout, anomaly, what-if, recommendations), and 13 React dashboards.
• —Hardest problem solved: Wiring a tool-calling LLM agent to governed, tested data — raw tables seeded directly into Postgres, transformed by dbt with 26 passing data-quality tests, served to both the dashboards and the agent, with automatic CSV fallback if the database is unreachable.
• —What's genuinely real (not mocked): It's deployed (Vercel + HuggingFace Spaces + Neon Postgres), tested in CI, the fine-tune button actually retrains CatBoost with a leak-free temporal split, logs a versioned model registry, and persists the trained weights durably in Postgres so they survive Space restarts. Forecasts are backtested honestly (no in-sample leakage), and every agent run is traced (per-tool latency + estimated cost).

Overview — problem & motivation

Most "supply chain dashboards" show you what already happened and leave the thinking to you. Petopia flips that: you ask a question in plain English ("which SKUs are at stockout risk this week?"), and an agent reasons over the live warehouse, calls real tools, and answers with the actual numbers — then you can drill into the same data across thirteen dashboards.

It models a fictional premium Indian pet retailer, HUFT-style (Heads Up For Tails), with ~90 stores, 160 SKUs, 25k customers, and ~327k transaction line items across ~180k multi-item orders over three years of synthetic-but-realistic data (festival demand spikes, promotions, cold-chain SKUs, regional channels). Every line item carries an order_id and customer_id, so genuine basket and per-customer analytics (co-purchase, recommendations) are possible — not just SKU aggregates.

Who it's for / why it exists: This is a portfolio project built to demonstrate agentic + data-engineering skills for roles at pet-retail and e-commerce companies — and, concretely, as a working reference tool for a friend who is a data scientist at a pet-store company. The goal was breadth done properly: not one flashy model, but the full path from data generation → warehouse → transformation → serving → agent → UI → deployment, each piece honest and verifiable.

A note on the data: the dataset is synthetic and generated deterministically (data/generate_data.py, fixed seed). All "impact" below is therefore about engineering correctness and capability, not real business outcomes — no revenue or accuracy figures are claimed beyond what the code actually measures.

Features

• —🤖 Multi-LLM ReAct agent (BYOK) — Anthropic, OpenAI, Groq, and Google Gemini, hot-swappable in the UI; the key you type wins over any server key. Streams its reasoning and tool calls live over a WebSocket, with collapsible chain-of-thought and Markdown-rendered answers.
• —🧰 54-tool MCP server — the agent calls typed tools (inventory, forecasts, suppliers, analytics, stockout, anomaly, what-if) via the Model Context Protocol, in-process by default. No RAG, no embeddings — structured tool calls over live data.
• —🔎 Ad-hoc SQL + natural-language querying — a guarded run_sql_query tool lets the agent answer questions no fixed tool covers by writing a read-only SQL SELECT (single statement, file-access/DDL/multi-statement blocked, 100-row cap), executed on DuckDB over the data. The Ask Your Data dashboard adds a hybrid "ask in plain English" box: an LLM (BYOK) turns your question into SQL, drops it into an editor you can review/edit, then runs it through the same guard — open-ended querying with a visible, editable query.
• —🗄️ Real data warehouse — PostgreSQL (Neon) is the system of record, seeded directly from generation (no CSV middleman), with automatic CSV fallback and a /diagnostics endpoint that reports which source is live.
• —🔧 *dbt transformation layer (Postgres or DuckDB) — raw → staging → marts (`store_kpis`, `sku_performance`, `supplier_scorecard`, plus `co_purchase_pairs`, `customer_product_history`, `sku_days_of_supply`) with data-quality tests. The same models build on Neon Postgres (`db/run_dbt.py`) or, with zero database, on DuckDB over a Parquet raw layer* (db/build_marts.py) — the free-tier path.
• —🎯 Product recommendations (market basket) — a co-purchase engine computed from real multi-item order baskets that measures support, confidence, and lift, and ranks per-product suggestions by lift so genuine complements ("bought together") beat merely-popular items. Interactive per-product lookups (category → product → recommendations) plus a browse of the strongest pairings; served by the co_purchase_pairs mart with a live-compute fallback.
• —⏳ Stockout predictor — per-SKU sales velocity, days-to-zero, lead-time-aware reorder quantities, and critical/warning/watch/healthy/excess risk buckets.
• —🚨 Anomaly detection — four rules-based detectors: sales crashes (week-over-week revenue drops), overnight inventory spikes (data-entry risk), discount breaches (per-channel ceilings), and velocity-vs-stock risk.
• —🧪 What-if simulator — project the revenue impact of a discount (price elasticity estimated from history) or the days-of-cover, overstock risk, and ROI of a restock. Category and SKU pickers are populated from the live catalog, and each scenario runs on an explicit Simulate button.
• —🔮 Forecasting ensemble + intermittent-demand routing — Amazon Chronos-T5 + N-HiTS + CatBoost quantile blend (0.5 / 0.35 / 0.15), with graceful degradation. Lumpy/intermittent SKUs (long zero-runs) are auto-routed to Croston / TSB via Syntetos-Boylan classification before the ML models run.
• —⚙️ Real MLOps with durable weights — the Trigger fine-tune button retrains the served CatBoost on the latest demand using a leak-free temporal split (training cutoff before the validation window — no in-sample leakage), backtests it (sMAPE), and appends a version to a Postgres model registry. The trained weights are persisted durably as a blob in Postgres (last-N versions retained) and restored on boot — so a fine-tune survives HuggingFace Space restarts, whose container storage is otherwise ephemeral. One click trains, scores, and persists; the agent's forecasts then serve those weights.
• —📊 Agent observability — every assistant turn is logged with its tools, per-tool latency, status, and an estimated token cost, shown as a live "receipt" table.
• —🖥️ 13 animated dashboards — Executive, Inventory, Forecast, Suppliers, Stores, Analytics, Recommendations, Stockout, Anomaly, What-If, Ask Your Data (SQL console), AI Assistant, MLOps — React + Vite, with confidence-band charts, clickable drill-downs, and per-metric explanations.

Architecture

System topology

flowchart TD
    subgraph Client
        U[User's browser]
    end
    subgraph Vercel["Vercel — frontend"]
        FE[React + Vite SPA<br/>13 dashboards]
    end
    subgraph HF["HuggingFace Spaces — backend (Docker, CPU)"]
        API[FastAPI<br/>10 REST route modules + /ws/chat]
        AG[ReAct agent<br/>agent/agent.py]
        MCP[MCP server<br/>54 typed tools, in-process]
        FC[Forecasting ensemble<br/>Chronos + N-HiTS + CatBoost<br/>+ Croston/TSB routing]
        INT[Intelligence engines<br/>stockout · anomaly · what-if · recommend]
    end
    subgraph Neon["Neon — PostgreSQL"]
        RAW[(raw tables)]
        MARTS[(analytics marts<br/>+ model_registry<br/>+ model_artifacts weights<br/>+ agent_runs)]
    end
    LLM[LLM provider<br/>Anthropic / OpenAI / Groq / Gemini]
    CSV[(bundled CSVs<br/>offline fallback)]

    U -->|HTTPS| FE
    FE -->|REST /api/*| API
    FE -->|WebSocket /ws/chat| API
    API --> AG --> MCP
    API --> FC
    API --> INT
    AG -->|BYOK key| LLM
    MCP --> RAW
    API -->|reads marts| MARTS
    API -. fallback if DB down .-> CSV

Why this shape? The frontend and backend are deployed independently (Vercel's CDN for static React; HuggingFace Spaces' free Docker CPU for the Python stack) so each scales and redeploys on its own. The agent runs the MCP server in-process (BYPASS_MCP_HTTP=true) to avoid a second network hop and a second deployable — but the same code can run MCP as a standalone SSE/HTTP server when you want process isolation. The database is the source of truth, but the app degrades to bundled CSVs so it still runs for anyone who clones it without a Neon URL.

Data flow (ELT)

flowchart LR
    GEN[data/generate_data.py<br/>deterministic synthetic data<br/>orders + baskets + customers] -->|in-memory frames| SEED[db/seed.py<br/>direct write]
    SEED --> RAW[(Postgres: raw tables)]
    GEN -.->|fallback export| CSVF[(CSVs)]
    GEN -.->|Parquet raw layer| PARQ[(data/parquet/*.parquet)]
    RAW --> DBT[dbt staging views → marts<br/>+ data-quality tests]
    PARQ -.->|free-tier: DuckDB target| DBT
    DBT --> MARTS[(marts: store_kpis · sku_performance<br/>supplier_scorecard · co_purchase_pairs<br/>customer_product_history · sku_days_of_supply)]
    MARTS --> APP[FastAPI dashboards]
    RAW --> APP
    CSVF -. if DB unreachable .-> APP

Why ELT, not ETL? Load raw first, transform inside the warehouse with dbt. That keeps transformations version-controlled, testable, and re-runnable instead of buried in Python, and it's the pattern data teams actually use. The store dashboard reads its KPIs from the store_kpis mart and reports kpis_source: "dbt:store_kpis", falling back to raw pandas compute if dbt hasn't been built.

Two engines, one set of models. The dbt models are engine-agnostic. In production they build on Neon Postgres (python db/run_dbt.py build). With no database at all — the free-tier path — the same models build on DuckDB over a Parquet raw layer (python db/build_marts.py), landing marts in a local data/petopia.duckdb that the backend reads automatically via load_mart(). This is what lets the recommendation/analytics layer run and scale identically from a laptop to a warehouse by swapping the target, not the code.

ReAct agent loop

sequenceDiagram
    participant FE as Frontend (/ws/chat)
    participant WS as agent_ws.stream_agent
    participant AG as run_agent_with_steps
    participant LLM as LLM provider
    participant T as MCP tool
    participant DB as agent_runs (telemetry)

    FE->>WS: {message, provider, model, api_key}
    loop up to 20 iterations
        WS->>AG: next frame
        AG->>LLM: messages + tool schemas
        LLM-->>AG: thinking / tool_call / final
        alt tool call
            AG->>T: run tool
            T-->>AG: result
            WS-->>FE: step (tool + result)
        else final answer
            WS-->>FE: answer (Markdown)
        end
    end
    WS->>DB: record run (tools, latency, status, est cost)
    WS-->>FE: done

Honest forecasting — how the models avoid data leakage

A model can look great and be worthless if it's evaluated on data it trained on — the reported accuracy is then in-sample-optimistic and collapses the moment real out-of-sample data arrives. This project is careful to never do that:

• —Time-based (not random) splits. For time series, a random train/test split leaks the future into the past. The persistent CatBoost splits by date: cutoff = max(date) − 90d, train on date ≤ cutoff, validate on date > cutoff (forecasting/ml_forecast.py:_train_catboost). The validation window is genuinely the future the model never saw.
• —*Training cutoff before the holdout. The registry backtest trains on all-but-the-last-`horizon` days and scores the held-out tail: `train, test = series[:-horizon], series[-horizon:]` — the model is fit only* on train (backend/forecasting/training.py). No holdout day ever influences training.
• —Encoders and summary stats fit on training rows only. SKU/category encoders and per-SKU mean/std are computed from the training slice, so validation-period distributions can't bleak into the features the model learns from.
• —Causal features only. Lags and rolling windows look backward (values available at prediction time) — never forward.
• —sMAPE, reported out-of-sample. Accuracy is symmetric MAPE (robust to zero-demand days) measured on the held-out window — the number you see is the model's real generalisation error, not a leaked one.

The practical payoff: a fine-tune's reported score is honest from day one, so it can't silently degrade later simply because an in-sample metric stopped flattering it.

Tech Stack

Exact versions live in `backend/requirements.txt`, `requirements-dev.txt`, and `frontend/package.json`.

Skills Demonstrated

• —Data engineering / ETL-ELT pipeline design — deterministic generation (orders + baskets + customers) → direct-to-Postgres seeding → dbt staging/marts with data-quality tests; engine-portable models that also build on DuckDB over a Parquet raw layer for a zero-database free-tier path.
• —Production ML / MLOps — model-serving API separate from training; one-click CatBoost retrain with a leak-free temporal backtest; a versioned model registry and durable weight persistence in Postgres (blob store with last-N retention, restored on boot) so fine-tunes survive ephemeral container restarts; intermittent-demand routing (Croston/TSB) and a lift-based co-purchase recommendation mart.
• —LLM application development & agentic systems — multi-provider ReAct agent, MCP tool-calling, streaming, context-window compression.
• —Observability & monitoring — health + /diagnostics endpoints, structured per-run agent telemetry (latency, cost, status).
• —Cloud deployment — HuggingFace Spaces (Docker), Vercel, Neon Postgres; secrets via platform config, not in code.
• —CI/CD — GitHub Actions running backend pytest and frontend vitest + build on every push.
• —System design & architecture — documented tradeoffs (in-process vs HTTP MCP, ELT vs ETL, DB-first with CSV fallback).
• —Asynchronous/concurrent systems — async FastAPI, WebSocket streaming, telemetry written off the event loop.
• —Database design — relational schema with referential integrity enforced by dbt relationship tests.

Getting Started

Prerequisites: Python 3.11, Node 18+, and (optionally) a free Neon Postgres database. Without a database, the app runs off the bundled CSVs automatically.

# 1. Clone
git clone https://github.com/shiva-shivanibokka/SCM-using-MCP-and-LLM.git
cd SCM-using-MCP-and-LLM

# 2. Backend deps + environment
python -m venv venv && source venv/Scripts/activate   # Windows Git Bash; use bin/activate on macOS/Linux
pip install -r backend/requirements.txt
cp .env.example .env                                   # then edit .env (see below)

# 3a. (Optional) Use a real database — put your Neon URL in .env:
#    DATABASE_URL=postgresql://user:pass@ep-xxx.neon.tech/dbname?sslmode=require
python db/seed.py            # generate data + write straight to Postgres
python db/run_dbt.py build   # build dbt marts + run the data-quality tests

# 3b. (Optional, no database) Build the marts locally with dbt + DuckDB:
pip install -r requirements-dev.txt   # dbt-duckdb, duckdb, pyarrow
python data/generate_data.py          # write the CSVs
python db/build_marts.py              # Parquet raw layer → DuckDB marts (data/petopia.duckdb)

# 4. Run the backend
uvicorn backend.main:app --reload --port 8000          # http://localhost:8000/docs

# 5. Run the frontend (separate terminal)
cd frontend && npm install && npm run dev              # http://localhost:5173

Environment variables (all optional — see `.env.example`):

dbt + protobuf gotcha: dbt-core pins a newer protobuf than google-generativeai allows. Install the data-engineering tooling in its own virtualenv so it doesn't break the local Gemini provider: python -m venv .dbt-venv && .dbt-venv/Scripts/pip install -r requirements-dev.txt. The deployed backend never runs dbt, so production is unaffected.

Deployment from scratch

The live stack is Vercel (frontend) → HuggingFace Spaces (backend) → Neon (Postgres), all on free tiers. To reproduce it:

1. Database — Neon

1. 1.Create a free project at neon.tech and copy the connection string.
2. 2.Locally, put it in .env as DATABASE_URL=..., then seed and transform:

   python db/seed.py            # raw tables, directly into Postgres
   python db/run_dbt.py build   # marts + data-quality tests

2. Backend — HuggingFace Spaces (Docker)

1. 1.Create a new Space → SDK: Docker. The repo's root `Dockerfile` and the README frontmatter (app_port: 7860, colorFrom must be one of HF's allowed colors) configure it.
2. 2.Add the Space as a git remote and push:

   git remote add hf https://huggingface.co/spaces/<user>/<space>
   git push hf main

1. 1.In Space → Settings → Variables and secrets, add a secret DATABASE_URL (the same Neon string). Do not add LLM keys here — this is BYOK.
2. 2.Verify: visit https://<space>.hf.space/diagnostics — it should report "data_source": "postgres".

3. Frontend — Vercel

1. 1.Import the GitHub repo into Vercel.
2. 2.Settings → Root Directory = `frontend` (the app lives in a subfolder).
3. 3.The production API URL is baked in via `frontend/.env.production` (VITE_API_BASE), or set it as a Vercel env var.
4. 4.Push to main → Vercel auto-deploys. CORS on the backend already allows any *.vercel.app origin.

4. CI

`.github/workflows/backend.yml` runs pytest (generating data first), and `frontend.yml` runs vitest + npm run build on every push.

Usage

Ask the agent (AI Assistant tab, or over the WebSocket):

"Which SKUs are at stockout risk this week, and how much should I reorder?"
"Forecast demand for our top food SKU for 30 days"
"Are there any anomalies in sales or inventory right now?"
"What if we discount Grooming by 25% — what happens to revenue?"
"What's frequently bought together with our best-selling dog bed?"
"Rank suppliers by on-time delivery"
"Run SQL: top 5 cities by net revenue"   # ad-hoc, via the guarded SQL tool

Call the REST API directly:

curl https://shiva-1993-petstore-scm-mcp-backend.hf.space/api/executive/kpis
# → {"revenue": 494092688.0, "gross_margin_pct": 45.6, "inventory_value": 236831848.0, ...}

curl -X POST https://shiva-1993-petstore-scm-mcp-backend.hf.space/api/mlops/finetune
# → trains + persists CatBoost; returns
#   {"status":"completed","version":1,"backtest_smape":18.2,"val_mape":12.0,"durably_persisted":true,...}

curl -X POST https://shiva-1993-petstore-scm-mcp-backend.hf.space/api/intelligence/sql \
  -H "Content-Type: application/json" -d '{"sql":"SELECT city, SUM(net_revenue_inr) r FROM transactions GROUP BY 1 ORDER BY r DESC LIMIT 5"}'
# → guarded read-only SQL over the warehouse (DuckDB), returns {columns, rows, ...}

Interactive OpenAPI docs: `/docs` on the backend.

Project Structure

.
├── frontend/              # React + Vite SPA (13 dashboards, BYOK LLM selector, charts)
│   ├── src/pages/         # one component per dashboard
│   ├── src/components/    # KpiCard, Markdown, charts, LlmSelector, ...
│   └── src/hooks/         # useChat (WebSocket ReAct stream)
├── backend/               # FastAPI app — the deployed runtime
│   ├── main.py            # app + /health + /diagnostics
│   ├── api/routes/        # executive, inventory, forecast, suppliers, stores, analytics,
│   │                      #   mlops, chat, recommendations, intelligence
│   ├── data_access.py     # Postgres-first loaders + CSV fallback + load_mart() (Postgres→DuckDB)
│   ├── db.py              # SQLAlchemy engine from DATABASE_URL
│   ├── observability.py   # agent_runs telemetry
│   ├── agent_ws.py        # WebSocket adapter + per-tool timing
│   └── forecasting/       # chronos / nhits / catboost / ensemble / intermittent (Croston/TSB) /
│                          #   registry / training (leak-free retrain + durable persist)
├── intelligence/          # pure-compute engines shared by backend + agent:
│                          #   stockout, anomaly, whatif, sql (guarded NL→SQL / DuckDB)
├── forecasting/           # trainable persistent model (ml_forecast) + artifact_store
│                          #   (durable weight persistence to Postgres)
├── agent/                 # multi-provider ReAct agent + MCP client
├── mcp_server/            # MCP server (54 typed tools incl. run_sql_query, in-process or SSE)
├── dbt/                   # dbt project: staging views + marts, Postgres AND DuckDB targets
├── db/                    # seed.py (direct-to-DB), run_dbt.py (Postgres),
│                          #   build_marts.py + export_parquet.py (DuckDB free-tier path)
├── data/                  # generate_data.py (orders/baskets/customers) + CSV fallback snapshot
├── Dockerfile             # HF Spaces backend image
├── requirements-dev.txt   # dbt tooling (separate from runtime deps)
└── .github/workflows/     # backend + frontend CI

Legacy: legacy/ (the retired Gradio UI), and the root-level forecasting/, mlops/, knowledge/, and tests/ directories are from an earlier iteration of this project and are superseded by the backend/-scoped equivalents above. They're kept for history, not used by the deployed app.

Testing

All three run in GitHub Actions CI on every push. Coverage is focused on the data layer, routes, and registry/forecast contracts rather than an exhaustive line-coverage number.

Roadmap / Known limitations

• —Synthetic data only — no real business metrics are claimed; the data is generated, not sourced.
• —MCP tool count — 54 tools is intentionally near the practical ceiling; the next step is consolidation (fewer, composable tools) rather than more.
• —Agent eval harness — automated graded scenarios for the agent (assert correct tool selection + answers) is the highest-value next addition.
• —Exact token accounting — agent cost is currently estimated (chars/4 × per-provider rate); wiring real usage from each provider's response would make it exact.
• —Heavy forecasting models on free CPU — Chronos/N-HiTS run only where resources allow; the API degrades gracefully to CatBoost otherwise.
• —No auth — the demo is open; production use would need request auth and rate limiting.

License

MIT © 2026 Shivani Bokka.