# autotask-dwh-mcp-server

A read-only [Model Context Protocol](https://modelcontextprotocol.io) 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](https://github.com/samanhappy/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](#two-layer-read-only)).

## Prerequisites

- **Node.js 20 LTS or newer** (built and tested on Node 22). See the `engines` field in
  [`package.json`](package.json).
- Network path to the warehouse **from an IP that Datto has allowlisted** — see the
  [hard requirement](#hard-requirement-ip-allowlist) below.

## Install & build

```bash
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`](.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

```bash
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:

```bash
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`](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](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`](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.