# Dataverse MCP Server

A production-grade [Model Context Protocol (MCP)](https://modelcontextprotocol.io) server for Microsoft Dataverse, built with Node.js/TypeScript. Connects Claude Desktop directly to your Dataverse / Power Platform environment with full CRUD, bulk operations, FetchXML, Dataverse actions, and metadata discovery.

---

## Tool Catalog

### Auth
| Tool | Description |
|------|-------------|
| `whoami` | Show the currently signed-in Microsoft account |
| `sign_out` | Sign out and clear the cached credentials |

### Read
| Tool | Description |
|------|-------------|
| `get_record` | Fetch a single record by GUID with optional field selection and expand |
| `query_records` | Query records using OData `$filter`, `$select`, `$orderby`, `$top`, `$expand`, and pagination |
| `execute_fetchxml` | Run a FetchXML query — supports aggregates, linked entities, and grouping |

### Write
| Tool | Description |
|------|-------------|
| `create_record` | Create a single record, returns the new GUID |
| `update_record` | Update specific fields on an existing record (PATCH — only provided fields change) |
| `upsert_record` | Create or update a record with a known GUID (PUT semantics) |
| `delete_record` | Delete a single record by GUID |
| `associate_records` | Link two records via a navigation property |
| `disassociate_records` | Remove a relationship link between two records |

### Bulk
| Tool | Description |
|------|-------------|
| `bulk_create_records` | Create up to **1000 records** in one call via OData `$batch`. Tracks per-record success/failure. |
| `bulk_update_records` | Update up to **1000 records** in one call via OData `$batch`. Each item requires a `recordId` and `data` (PATCH semantics). Tracks per-record success/failure. |
| `bulk_delete_records` | Delete up to **1000 records** in one call via OData `$batch`. Tracks per-record success/failure. |
| `batch_transaction` | Execute up to 100 mixed operations atomically — if any fails, all roll back |

### Actions
| Tool | Description |
|------|-------------|
| `execute_action` | Execute a Dataverse bound or unbound action (e.g. `WinOpportunity`, custom workflow actions) |

### Metadata
| Tool | Description |
|------|-------------|
| `describe_table` | Full schema for a table: all columns, types, required levels, and all relationships. Cached 5 min. |
| `list_tables` | List all available tables, filterable by name or custom-only. Cached 5 min. |
| `get_option_set_values` | Fetch all choice values (code + label) for a local choice column or global option set |
| `refresh_metadata_cache` | Invalidate cached schema so the next call fetches fresh data from Dataverse |

---

## Features

- **Authentication** — Microsoft Device Code flow (MSAL). No client secret or redirect URI needed. Silent token renewal; persistent token cache.
- **Retry with backoff** — Automatic exponential backoff on `429 Too Many Requests` and `5xx` errors, with `Retry-After` header support.
- **Metadata caching** — `describe_table` and `list_tables` results cached in-memory (TTL configurable, default 5 min), eliminating redundant API calls.
- **Atomic batch transactions** — `batch_transaction` wraps all operations in a single OData changeset. Dataverse rolls back everything on any failure.
- **Structured logging** — JSON-formatted logs to `stderr` (stdout is reserved for MCP JSON-RPC). Log level configurable via env var.
- **Strong input validation** — All tool inputs validated with Zod before hitting the API. Validation errors return clear, actionable messages.
- **Request timeouts** — All HTTP calls have a configurable timeout (default 30s). Batch calls have extended timeouts (60–120s).

---

## Prerequisites

- Node.js 18+
- A Microsoft Dataverse / Power Platform environment URL
- A Microsoft account with access to that environment

No Azure App Registration is required — the server uses the well-known Azure CLI public client by default.

---

## Setup

### 1. Install dependencies

```bash
npm install
```

### 2. Configure environment

**macOS / Linux:**
```bash
cp .env.example .env
```

**Windows (PowerShell):**
```powershell
copy .env.example .env
```

Edit `.env` — only `DATAVERSE_URL` is required:

```env
# Required
DATAVERSE_URL=https://yourorg.crm.dynamics.com

# Optional — only needed if you want your own Azure App Registration
# AZURE_CLIENT_ID=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
# AZURE_TENANT_ID=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

# Optional — tuning
# TOKEN_CACHE_PATH=./.token-cache.json
# LOG_LEVEL=info                  # debug | info | warn | error
# REQUEST_TIMEOUT_MS=30000
# MAX_RETRIES=3
# METADATA_CACHE_TTL_MS=300000
```

### 3. Build

```bash
npm run build
```

---

## Claude Desktop Integration

### 1. Locate your configuration file

- **macOS:** `~/Library/Application Support/Claude/claude_desktop_config.json`
- **Windows:** `%APPDATA%\Claude\claude_desktop_config.json`

### 2. Add the server configuration

Add the following to the `mcpServers` section of your configuration file, adjusting the paths to match your installation:

#### macOS
```json
{
  "mcpServers": {
    "dataverse": {
      "command": "node",
      "args": ["/Users/YOUR_USERNAME/path/to/dataverse-mcp/dist/index.js"],
      "env": {
        "DATAVERSE_URL": "https://yourorg.crm.dynamics.com",
        "TOKEN_CACHE_PATH": "/Users/YOUR_USERNAME/path/to/dataverse-mcp/.token-cache.json"
      }
    }
  }
}
```

#### Windows
```json
{
  "mcpServers": {
    "dataverse": {
      "command": "node",
      "args": ["C:\\Users\\YOUR_USERNAME\\path\\to\\dataverse-mcp\\dist\\index.js"],
      "env": {
        "DATAVERSE_URL": "https://yourorg.crm.dynamics.com",
        "TOKEN_CACHE_PATH": "C:\\Users\\YOUR_USERNAME\\path\\to\\dataverse-mcp\\.token-cache.json"
      }
    }
  }
}
```

> **Note:** On Windows, make sure to use double backslashes (`\\`) in paths.

On first use, a device code login prompt will appear in the Claude Desktop logs. Open the URL shown and enter the code to authenticate. Subsequent calls use the cached token silently.

---

## Usage Examples

### Query records with a filter

```
Show me all active accounts in Dataverse, just the name and email fields
```

Claude calls `query_records`:
```json
{
  "tableName": "account",
  "select": ["name", "emailaddress1"],
  "filter": "statecode eq 0",
  "orderBy": "name asc",
  "top": 50
}
```

### Discover a table's schema before writing

```
What fields does the 'contact' table have?
```

Claude calls `describe_table` → returns all attributes, types, required levels, and relationships.

### Create a single record

```json
{
  "tableName": "contact",
  "data": {
    "firstname": "Jane",
    "lastname": "Smith",
    "emailaddress1": "jane@contoso.com"
  }
}
```

### Bulk update records

```json
{
  "tableName": "contact",
  "items": [
    { "recordId": "00000000-0000-0000-0000-000000000001", "data": { "jobtitle": "Manager" } },
    { "recordId": "00000000-0000-0000-0000-000000000002", "data": { "jobtitle": "Director", "emailaddress1": "d@contoso.com" } }
  ]
}
```

Returns per-record success/failure — partial success is fully supported.

### Bulk create 1000 records

```json
{
  "tableName": "contact",
  "items": [
    { "firstname": "Alice", "emailaddress1": "alice@contoso.com" },
    { "firstname": "Bob",   "emailaddress1": "bob@contoso.com" }
  ]
}
```

Returns per-record success/failure — partial success is fully supported.

### Atomic batch transaction

```json
{
  "operations": [
    { "type": "create", "tableName": "account", "data": { "name": "Contoso" } },
    { "type": "update", "tableName": "contact", "recordId": "00000000-...", "data": { "jobtitle": "CEO" } },
    { "type": "delete", "tableName": "lead",    "recordId": "00000000-..." }
  ]
}
```

All three operations succeed together or all roll back — no partial state.

### FetchXML for complex queries

```json
{
  "tableName": "account",
  "fetchXml": "<fetch aggregate='true'><entity name='account'><attribute name='revenue' aggregate='sum' alias='total_revenue'/><filter><condition attribute='statecode' operator='eq' value='0'/></filter></entity></fetch>"
}
```

### Execute a Dataverse action

```json
{
  "actionName": "WinOpportunity",
  "parameters": {
    "OpportunityClose": { "subject": "Won deal", "opportunityid": { "@odata.type": "Microsoft.Dynamics.CRM.opportunity", "opportunityid": "00000000-..." } },
    "Status": 3
  },
  "boundTableName": "opportunity",
  "boundRecordId": "00000000-..."
}
```

---

## Architecture

```
src/
├── config.ts                        # Env config + validation
├── index.ts                         # MCP server + dynamic tool dispatch
├── auth/
│   └── AuthManager.ts               # MSAL token lifecycle (silent → device code)
├── services/dataverse/
│   ├── DataverseClient.ts           # HTTP client, retry, timeout, auth interceptors
│   ├── BatchBuilder.ts              # OData $batch body construction
│   ├── BatchParser.ts               # Multipart response parser
│   ├── MetadataCache.ts             # TTL-based metadata cache
│   └── types.ts                     # Shared interfaces
├── tools/
│   ├── registry.ts                  # Tool name → handler map
│   ├── schemas.ts                   # Zod input schemas
│   ├── definitions.ts               # MCP ListTools definitions
│   └── handlers/
│       ├── auth.ts                  # whoami, sign_out
│       ├── read.ts                  # get_record, query_records, execute_fetchxml
│       ├── write.ts                 # create, update, upsert, delete, associate, disassociate
│       ├── bulk.ts                  # bulk_create, bulk_delete, batch_transaction
│       ├── metadata.ts              # describe_table, list_tables, refresh_metadata_cache
│       └── actions.ts               # execute_action
└── utils/
    ├── logger.ts                    # Structured JSON logger → stderr
    ├── errors.ts                    # Typed errors + MCP error formatter
    └── retry.ts                     # Exponential backoff + Retry-After
```

---

## How Bulk Operations Work

OData `$batch` packs multiple operations into a single HTTP request. This server processes up to 100 records per batch request, chunking automatically for larger payloads.

**Parallel bulk** (`bulk_create_records`, `bulk_delete_records`): each operation is in its own changeset — partial success is tracked per record. One failure does not block others.

**Atomic batch** (`batch_transaction`): all operations share a single changeset. Dataverse treats it as a transaction — any failure rolls back all operations in the batch.

---

## Running Tests

```bash
npm test
```

60 tests across 6 suites: tool handler validation, schema validation, batch response parsing, retry logic.

---

## Optional: Custom Azure App Registration

If you need specific API permissions or want to restrict the application identity, register your own app:

1. [portal.azure.com](https://portal.azure.com) → **Azure Active Directory** → **App registrations** → **New registration**
2. Supported account types: **Single tenant**
3. Platform: **Mobile and desktop applications** (no redirect URI needed for device code flow)
4. **API permissions** → **Add a permission** → **Dynamics CRM** → **Delegated** → `user_impersonation`
5. **Grant admin consent**
6. Copy the **Application (client) ID** and **Directory (tenant) ID** to your `.env`