Production Plan Variance MCP Server

A small MCP server that exposes the Production Plan Variance & Issue Tracking dashboard (erp.peopledesk.io/production-management/mes/ProductionPlanVariance) as five read-only tools, backed directly by the DWH database:

It queries these DWH tables (verified against the live schema): tblProductionPlanVarianceIssueArc, tblProductionPlanVarianceReasonArc, tblPlantArc, tblShopFloorArc.

Why this shape

You asked for something anyone can use via a JSON file, without anyone entering database credentials. So the design is:

• One server, hosted by you, holding the DB credentials in its own environment variables (.env, never committed, never sent to clients).

• Everyone else just drops mcp-config.json into their MCP client (Claude Desktop, Claude Code, etc.) — it only contains a URL, nothing secret. The server is the only thing that talks to SQL Server.

• All queries are fixed, parameterized SELECTs defined in src/queries.js — there is no free-form SQL tool exposed, so nobody using this MCP server can query outside the dashboard's own data or run writes.

⚠️ Before you deploy: confirm DB permissions

While building this, I verified the schema (tables/columns) using your mssql-test-server connection, but every actual row-level SELECT against tblProductionPlanVarianceIssueArc / tblPlantArc etc. failed — schema introspection worked, data queries didn't. That's almost certainly a permissions gap on that specific SQL login (mcp_user), not a problem with these queries. Before going live:

1. Create (or reuse) a SQL Server login with SELECT-only rights on the DWH tables listed above.

2. Test manually, e.g.:

   SELECT TOP 5 * FROM dbo.tblProductionPlanVarianceIssueArc;

3. Only then point DB_USER/DB_PASSWORD in .env at that login.

Deploy

Option A — your own server (nginx/Caddy in front)

npm install
cp .env.example .env    # fill in DB_SERVER / DB_USER / DB_PASSWORD
npm start                # listens on :3300, endpoint POST /mcp

Put it behind HTTPS on a domain reachable by your team, e.g. https://mcp.yourcompany.com/mcp.

Option B — Vercel (already set up in this repo)

The api/mcp.js + vercel.json files convert the same server into a Vercel serverless function, exposed at /mcp.

1. Push this folder to a GitHub repo (or run from the folder directly).

2. Install the CLI once: npm i -g vercel

3. From inside production-variance-mcp/, log into your own Vercel account and deploy:

   vercel login
   vercel --prod

4. In the Vercel dashboard → your project → Settings → Environment Variables, add DB_SERVER, DB_PORT, DB_DATABASE, DB_USER, DB_PASSWORD, DB_ENCRYPT, DB_TRUST_CERT (same values as .env.example), then redeploy so the function picks them up.

5. Your MCP endpoint is now https://<your-project>.vercel.app/mcp. Put that URL into mcp-config.json and hand it out.

Note: SQL Server must be reachable from Vercel's network (i.e. not firewalled to only your office IP) — check with whoever manages 203.202.241.211 if the connection times out after deploying. Also add some form of auth in front of /mcp (see note below) before this URL is public — right now anyone with the link can call these read-only tools.

Distribute

Edit mcp-config.json, replacing YOUR-HOSTED-DOMAIN with your real domain, then share that one file. Anyone adds it to their MCP client and immediately has read-only chat access to this dashboard's data — no install, no credentials, no SQL knowledge required.

Notes / things to double check on your end

• The dashboard's "Machine" column (e.g. "Aromatic Unit Line - 01") wasn't found on tblProductionPlanVarianceIssueArc in the schema I pulled — it's likely resolved via a production line/work-center table in your environment. list_plan_variance's query doesn't include it yet; add the appropriate JOIN in src/queries.js once you confirm the source table (candidates: tblProductionLineArc, tblWorkCenterArc).

• get_variance_summary's "Issues Missing" logic assumes it means "flagged variance with no reason/status recorded yet" — confirm this against the app's actual business logic and adjust QUERY_SUMMARY if different.

• Add auth (API key / OAuth) in front of /mcp before exposing it beyond your internal network — this build has none by default.