autotask-dwh-mcp-server

A read-only Model Context Protocol server for the Autotask Report Data Warehouse (Microsoft SQL Server, views only). It lets a model explore the warehouse schema and run read-only SQL, without touching the Autotask REST API.

It runs as a STDIO subprocess inside our MCPHub instance and is launched via npx -y github:SSIG-IT/autotask-dwh-mcp-server, exactly like SSIG-IT/3cx-mcp-server.

What is the Autotask Data Warehouse?

The Autotask Report Data Warehouse is a nightly-refreshed, read-only copy of Autotask PSA data, exposed as SQL Server views (all named wh_*). It is strong for financial, contract, project and history reporting across all customers at once, without loading the live Autotask API. For live ticket detail and any write, keep using the existing Autotask REST MCP — the two complement each other.

This server is read-only by construction (see Two-layer read-only).

Related MCP server: VAST DB MCP Server

Prerequisites

• Node.js 20 LTS or newer (built and tested on Node 22). See the engines field in package.json.

• Network path to the warehouse from an IP that Datto has allowlisted — see the hard requirement below.

Install & build

npm install
npm run build          # tsc -> dist/
node dist/index.js     # starts the STDIO server

npm install runs the prepare script, so dist/ is built automatically (this is also how npx -y github:SSIG-IT/autotask-dwh-mcp-server builds itself on the consumer's machine).

Tools

All tools are annotated readOnlyHint: true, destructiveHint: false, idempotentHint: true, openWorldHint: false, and expose an outputSchema (structured output).

Resource warehouse://guide

Domain notes for writing correct queries (non-obvious names, the measured ticket-vs-task rule, the time join key, financial views, UI→DWH terminology). Referenced from the query tool description. A model should read it before writing queries.

Key fact it carries: there is no wh_ticket view. Tickets and project tasks share wh_task, distinguished by project_id IS NULL for tickets (verified against live data 2026-08-30).

Environment variables

Credentials come only from the environment — nothing about the target server is hardcoded, and the password / connection string are never logged. Copy .env.example to a local .env for testing (that .env is gitignored).

Without MSSQL_HOST/MSSQL_DATABASE/MSSQL_USER/MSSQL_PASSWORD the server still starts and answers list_views / describe_view from the bundled schema; query / last_load return a clear "unreachable" message naming the missing variables.

Local testing with the MCP Inspector

npx @modelcontextprotocol/inspector node dist/index.js

The Inspector shows the four tools (with descriptions and output schemas) and the warehouse://guide resource. On a dev machine the schema tools work from the bundled snapshot; query / last_load return the friendly unreachable message.

To point it at the real DB from an allowlisted host, use Node's native env-file loader:

node --env-file=.env dist/index.js
# or, with the Inspector:
npx @modelcontextprotocol/inspector node --env-file=.env dist/index.js

Hard requirement: IP allowlist

The warehouse only accepts connections from static IP addresses allowlisted at Datto (max three). Every query times out unless the connecting host's IP is on that allowlist, regardless of the code. This was verified: on 2026-08-30 the MCPHub VPS egress IP connected in ~179 ms, so no extra support case is needed for it. From a non-allowlisted machine (e.g. the Windows dev box) every DB connection times out by design — that is expected, not a bug.

Daily reload window

The warehouse is fully reloaded once per day (global customer: ~16:00 ET plus up to four hours). During the reload, running queries are aborted and connections are dropped. The server retries once on a dropped connection and otherwise returns Data Warehouse unreachable or reloading (daily refresh window); retry shortly.

Two-layer read-only

1. The Datto-provided read-only login.

2. A statement guard (src/guard.ts): it strips comments and string/identifier literals first, then requires a single statement whose first keyword is SELECT or WITH, and rejects INSERT/UPDATE/DELETE/MERGE/DROP/ALTER/CREATE/TRUNCATE/EXEC/EXECUTE/GRANT/REVOKE/INTO and sp_/xp_ on word boundaries, plus any statement-separating semicolon.

Deployment

See DEPLOYMENT.md for MCPHub, a generic STDIO client (Claude Desktop and similar), and the Streamable HTTP mode. Real passwords never appear there — the production value comes from the MCPHub config / MyGlue.

License

MIT — see LICENSE.

NOTES — what was verified against which source, and deliberate deviations

Per the build brief, the blueprint was checked against the current MCP spec, SDK and driver before coding. Nothing collided with a measured fact in the handover Addendum (ticket/task rule, 381-view schema, reachability), so no build-stop was required.

Verified live (2026-08-31):

• MCP spec version 2026-07-28 is current (from https://modelcontextprotocol.io/sitemap.xml). The live Tools spec page (https://modelcontextprotocol.io/specification/2026-07-28/server/tools) confirms tool annotations (readOnlyHint, destructiveHint, idempotentHint, openWorldHint), outputSchema + structuredContent, resources, and the stdio / Streamable HTTP transports. Backward-compat guidance (also emit the JSON as a text block) is followed: every tool returns both a text block and structuredContent.

• SDK API was verified against the installed package, not from memory: McpServer, registerTool({ inputSchema, outputSchema, annotations }, cb), registerResource, StdioServerTransport, StreamableHTTPServerTransport all resolve from @modelcontextprotocol/sdk/server/*. The legacy server.tool() / setRequestHandler are not used. SDK README: https://raw.githubusercontent.com/modelcontextprotocol/typescript-sdk/main/README.md

• node-mssql 12.7.0 (matches the client the Addendum measured with). encrypt: true, trustServerCertificate: true, connectionTimeout, requestTimeout, and pool confirmed. The runtime sql.valueHandler map was confirmed in the installed source.

Deliberate deviations from "newest", each justified:

1. SDK: pinned v1 @modelcontextprotocol/sdk@1.30.0, not the new v2 (@modelcontextprotocol/server@2.0.0, released with the 2026-07-28 spec). The blueprint targets exactly the v1 McpServer + registerTool API; MCPHub and the sibling 3cx-mcp-server packaging assume v1; v1.x is still maintained and already provides everything required (Zod input and output schemas, annotations, structuredContent, stdio + Streamable HTTP). For a server that must run unattended behind MCPHub, the proven line beats a sub-one-cycle-old major. The wire protocol version is negotiated at runtime, so this does not forfeit any 2026-07-28 tool feature. Revisit once v2 has soaked and MCPHub is confirmed against it.

2. TypeScript ^5.7, not the latest 7.0.2 (the native-port compiler). Emitted JS is identical; the mature ESM/NodeNext toolchain removes needless risk from the first clean build. Trivial to bump later.

3. Decimal fidelity via sql.valueHandler mapping decimal/numeric → String (Addendum correction 5). Caveat: the handler receives the value after tedious has parsed the TDS bytes into a JS number, so a value exceeding double precision (> ~15 significant digits) could already be rounded before stringify. For the money-scale columns in this warehouse this is not a practical concern; there is no lossless decimal-as-string path in the current node-mssql/tedious without dropping to raw TDS.

4. max_rows enforced as a hard JS cap after fetch (Addendum correction 2), with MSSQL_MAX_ROWS as the ceiling the per-call max_rows can only lower. TOP injection is best-effort only and is skipped for WITH/CTEs.

Also applied from the Addendum: robust SQL guard (correction 1), lazy/crash-safe connect (correction 3), stdout discipline — logs to stderr only (correction 4), parameterized schema lookups (correction 6), and the corrected time join (wh_time_item.time_item_id = wh_time_subitem.time_item_id; no task_id on the subitem) in the grounding text (correction 7).

Open until the VPS deploy (needs real DB access; do not run from the dev box): last_load smoke test, and a decimal probe SELECT TOP 3 contract_id, total_amount, our_cost, rate FROM wh_posted_overall. The project_id IS NULL ticket/task rule is already measured and embedded.