Skip to main content
Glama
autotask-dwh-mcp-server

autotask-dwh-mcp-server

by SSIG-IT

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: mcp-sql-agent

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

The built dist/ is committed to the repo, so npx -y github:SSIG-IT/autotask-dwh-mcp-server runs with no build step — important because the MCPHub container installs with --omit=dev, where the TypeScript compiler is absent. When you change anything under src/, run npm run build and commit the updated dist/. (Build tooling stays in devDependencies; prepublishOnly rebuilds on an npm publish.)

Tools

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

Tool

Purpose

list_views(filter?)

List warehouse views (all wh_*) with column counts, from live INFORMATION_SCHEMA (bundled snapshot as fallback). Optional case-insensitive name filter. Discovery entry point.

describe_view(view)

Columns + types for one view. Name comes from list_views; an unknown name returns closest matches.

query(sql, max_rows?)

Execute one read-only SELECT/WITH statement. Returns columns + rows as structuredContent plus a compact text table. Rows hard-capped by max_rows (ceiling = MSSQL_MAX_ROWS, default 500); if more rows match, the result carries truncated: true and a note so the count is never mistaken for the total. Every executed statement is logged to stderr (audit).

last_load()

Warehouse freshness from warehouse_last_load: Last_Load (refresh completed — the reliable "fresh" signal) and Backup_Taken (data accurate up to). First smoke test after deploy.

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).

Variable

Required

Default

Meaning

MSSQL_HOST

yes (for DB access)

—

SQL Server host, e.g. reports18.autotask.net

MSSQL_PORT

no

1433

TCP port

MSSQL_DATABASE

yes (for DB access)

—

Database, e.g. TF_000000_WH

MSSQL_USER

yes (for DB access)

—

Read-only login

MSSQL_PASSWORD

yes (for DB access)

—

Password

MSSQL_QUERY_TIMEOUT

no

30

Per-query timeout, seconds

MSSQL_MAX_ROWS

no

500

Hard ceiling on returned rows (default tuned for aggregates; override per instance)

TRANSPORT

no

stdio

stdio (production) or http (local testing)

HTTP_PORT

no

3000

Port for the optional HTTP transport

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.

Available Tools

4 tools
describe_viewDescribe a warehouse viewA
Read-onlyIdempotent

Return the column names and SQL types of ONE warehouse view. Take the exact view name from list_views, and call this before writing a query so you only reference columns that exist. Source is the live INFORMATION_SCHEMA (bundled snapshot as fallback; the 'source' field says which). On an unknown or misspelled name it returns found=false plus the closest matching view names as suggestions - retry with one of those. Traps: wh_task holds BOTH tickets and project tasks (a ticket has project_id IS NULL); time is split across wh_time_item (header, carries task_id) and wh_time_subitem (the hours), joined on time_item_id. See warehouse://guide for the ID-resolution map that turns *_id columns into names.

Inputs: view (required). Returns: hint, view, found, source, +2 more

last_loadWarehouse freshness (last_load)A
Read-onlyIdempotent

Return the warehouse freshness markers from the special object warehouse_last_load (this object is NOT a wh_ view and does NOT appear in list_views). Last_Load is the timestamp the daily full reload finished - the only reliable 'data is fresh' signal; Backup_Taken is the point in time up to which the data is accurate. Use it to check how current the data is, and as the first smoke test after deploy. No parameters. The warehouse is only a DAILY snapshot, so for any time-critical question check this first and report the data's age (Last_Load) rather than presenting a possibly day-old value as current. See warehouse://guide for the overall data model.

Inputs: no named parameters. Returns: last_load, backup_taken

list_viewsList warehouse viewsA
Read-onlyIdempotent

List the Autotask Data Warehouse views (read-only reporting views, all named wh_*) with their column counts. Use this FIRST to discover which view holds the data you need, then describe_view for its columns and query to read rows. Source is the live INFORMATION_SCHEMA; if the DB is unreachable it falls back to the bundled schema snapshot (the 'source' field says which). Trap: there is NO wh_ticket view - tickets live in wh_task with project_id IS NULL. Optional case-insensitive substring filter on the view name. See warehouse://guide for the data model and how to resolve *_id columns to names.

Inputs: filter. Returns: count, views, source

queryRun a read-only SQL queryA
Read-onlyIdempotent

Execute EXACTLY ONE read-only SQL statement against the Autotask Report Data Warehouse and return its columns and rows (structuredContent plus a compact text table). The statement MUST be a single SELECT or WITH (CTE) query. The read-only guard REJECTS everything else: INSERT, UPDATE, DELETE, MERGE, DROP, ALTER, CREATE, TRUNCATE, EXEC/EXECUTE, GRANT, REVOKE, SELECT ... INTO, stored/extended procedures (sp_/xp_), and any second statement (a statement-separating semicolon). Rows are hard-capped by max_rows (ceiling 500). READ the resource warehouse://guide before writing queries - this warehouse uses non-obvious names. Key traps: there is NO wh_ticket / wh_time_entry / wh_ticket_note view; tickets AND project tasks share wh_task, where a ticket has project_id IS NULL and a project task has project_id IS NOT NULL; join hours as wh_time_item.time_item_id = wh_time_subitem.time_item_id (task_id lives only on wh_time_item); money columns (wh_posted_overall, wh_billing_item) come back as decimal strings. Almost every *_id column is a foreign key: resolve the IDs that matter to human-readable names by JOINing the matching lookup view (see the 'ID RESOLUTION' map in warehouse://guide) and return names, not raw IDs, unless the user explicitly asks for IDs. If the result comes back with truncated=true it is INCOMPLETE (more rows matched than the cap) - narrow with WHERE/GROUP BY, aggregate, or raise max_rows; do not treat the returned count as the total. Data freshness: the warehouse is a DAILY full snapshot, not a live system - values can be up to a day old; for time-critical questions call last_load and state the load time, or use the live Autotask source instead of presenting a stale figure as current. For business terms (revenue, cost, margin, open ticket, billed/worked hours, utilization, active contract/employee) use the canonical definition from the BUSINESS GLOSSARY in warehouse://guide instead of interpreting them freely, and state the definition and time window you used.

Inputs: sql (required), max_rows. Returns: rows, columns, row_cap, truncated, +2 more

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    A4.7/5.0

    Scored across 4 tools

    Disambiguation5/5

    Each tool targets a separate stage of the reporting workflow: discovering views, inspecting schema, checking freshness, and running read-only queries. There is no purpose overlap or ambiguity in choosing among them.

    Naming Consistency4/5

    list_views and describe_view follow a clear verb_noun pattern, and query is a readable bare-verb action. last_load breaks the pattern as a noun phrase, so the set is mostly consistent but not perfectly uniform.

    Tool Count5/5

    Four tools is well-scoped for a read-only warehouse access server: discovery, schema inspection, freshness check, and query execution cover the domain without redundancy or bloat.

    Completeness5/5

    The tool surface fully covers the core workflow for a reporting DWH: find the right view, inspect its columns, verify data freshness, and query it with a robust SQL guard. No obvious dead ends or missing operations for the stated read-only purpose.

    Maintenance

    ActivityStale
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      Not graded
      quality
      D
      maintenance
      Enables AI assistants to safely query and explore SQL Server and PostgreSQL databases with read-only access, supporting schema discovery, relationship exploration, and query execution.
      3
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Gives an LLM read-only, schema-aware SQL database access so users can ask business questions in plain English and receive the exact result set, with malformed queries returned as errors for automatic retry and correction.
      MIT