autotask-dwh-mcp-server
This server is a read-only MCP gateway to the Autotask Report Data Warehouse (SQL Server views), letting you discover the schema and run safe, read-only analytical queries without touching the live Autotask API.
Explore the warehouse schema:
list_viewsshows allwh_*views with column counts;describe_viewreturns columns and SQL types for one view, with suggestions for misspelled names.Run read-only SQL:
queryexecutes exactly oneSELECTorWITHstatement, with a guard that rejects writes, DDL, procedures, and multi-statement input; results come back as structured data plus a compact text table.Control result size: rows are capped by
max_rows(up to the server ceiling of 500), andtruncated/row_capflags make clear when results are incomplete.Check data freshness:
last_loadreports when the nightly warehouse reload finished and the data accuracy point, so you can avoid presenting stale data as current.Understand the data model: the
warehouse://guideresource is available to explain non-obvious naming, the ticket-vs-task rule, time joins, financial views, and business definitions.Works even without DB credentials: schema tools fall back to a bundled snapshot, while
query/last_loadreturn a clear unreachable message if required environment variables are missing.Safe for production use: tools are annotated read-only/idempotent, every query is logged to stderr for audit, and the server is designed as a stdio subprocess for MCPHub or other MCP clients.
Provides read-only access to the Datto Autotask Report Data Warehouse, enabling schema exploration and read-only SQL queries over Autotask PSA views for financial, contract, project, and history reporting.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@autotask-dwh-mcp-server Show total billed amount per customer for Q1 2025"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
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
enginesfield inpackage.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 serverThe 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 warehouse views (all |
| Columns + types for one view. Name comes from |
| Execute one read-only |
| Warehouse freshness from |
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 |
| yes (for DB access) | — | SQL Server host, e.g. |
| no |
| TCP port |
| yes (for DB access) | — | Database, e.g. |
| yes (for DB access) | — | Read-only login |
| yes (for DB access) | — | Password |
| no |
| Per-query timeout, seconds |
| no |
| Hard ceiling on returned rows (default tuned for aggregates; override per instance) |
| no |
|
|
| no |
| 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.jsThe 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.jsHard 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
The Datto-provided read-only login.
A statement guard (
src/guard.ts): it strips comments and string/identifier literals first, then requires a single statement whose first keyword isSELECTorWITH, and rejectsINSERT/UPDATE/DELETE/MERGE/DROP/ALTER/CREATE/TRUNCATE/EXEC/EXECUTE/GRANT/REVOKE/INTOandsp_/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-28is current (from https://modelcontextprotocol.io/sitemap.xml). The live Tools spec page (https://modelcontextprotocol.io/specification/2026-07-28/server/tools) confirms toolannotations(readOnlyHint,destructiveHint,idempotentHint,openWorldHint),outputSchema+structuredContent, resources, and thestdio/ Streamable HTTP transports. Backward-compat guidance (also emit the JSON as a text block) is followed: every tool returns both a text block andstructuredContent.SDK API was verified against the installed package, not from memory:
McpServer,registerTool({ inputSchema, outputSchema, annotations }, cb),registerResource,StdioServerTransport,StreamableHTTPServerTransportall resolve from@modelcontextprotocol/sdk/server/*. The legacyserver.tool()/setRequestHandlerare not used. SDK README: https://raw.githubusercontent.com/modelcontextprotocol/typescript-sdk/main/README.mdnode-mssql 12.7.0 (matches the client the Addendum measured with).
encrypt: true,trustServerCertificate: true,connectionTimeout,requestTimeout, andpoolconfirmed. The runtimesql.valueHandlermap was confirmed in the installed source.
Deliberate deviations from "newest", each justified:
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 v1McpServer+registerToolAPI; MCPHub and the sibling3cx-mcp-serverpackaging 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.TypeScript
^5.7, not the latest7.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.Decimal fidelity via
sql.valueHandlermappingdecimal/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.max_rowsenforced as a hard JS cap after fetch (Addendum correction 2), withMSSQL_MAX_ROWSas the ceiling the per-callmax_rowscan only lower.TOPinjection is best-effort only and is skipped forWITH/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 toolsdescribe_viewDescribe a warehouse viewARead-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)ARead-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 viewsARead-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 queryARead-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
Scored across 4 tools
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.
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.
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.
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
Related MCP Connectors
Amazon Ads, Seller Central, Vendor Central and AMC data for AI agents. Read-only by default.
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Query your org's data in natural language — read-only MCP access to SQL, NoSQL, files & warehouses.
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Related MCP Servers
- AlicenseNot gradedqualityDmaintenanceEnables AI assistants to safely query and explore SQL Server and PostgreSQL databases with read-only access, supporting schema discovery, relationship exploration, and query execution.3MIT
- AlicenseNot gradedqualityCmaintenanceGives 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
- FlicenseNot gradedqualityAmaintenanceEnables AI assistants to execute SQL, explore schemas, retrieve internal metrics, generate Plotly charts, and obtain table/database overviews from a connected data warehouse.-
- AlicenseBqualityBmaintenanceEnables AI agents to safely describe, query, and compute metrics across SQL databases using open-format semantic models, custom context, and skills, with read-only and dialect-checked execution.184 PyPIApache 2.0