Microsoft Dataverse

View saved version →

---
name: dv-admin
description: Environment-level Dataverse administration — bulk delete, retention/archival, organization settings, OrgDB settings, recycle bin, audit, and the 37 allowlisted PPAC toggles. Use when the user wants to clean up data at scale, configure audit, change environment settings, manage retention policies, or list/cancel ERP batch jobs.
---

# Skill: Environment Admin — Bulk Delete, Retention, Org Settings, OrgDB, Recycle Bin

> ## ⚠️ Critical safety rules — read first
>
> 1. **Bulk delete is irreversible and bypasses the recycle bin.** `pac data bulk-delete schedule` without `--fetchxml` deletes every record in the table. Refuse to run until the user explicitly says ALL (or ALL RECORDS) **and** the entity logical name — e.g., `"yes, delete ALL records in contact"`. Bare `"yes"` rejected. Empty-filter FetchXML does NOT bypass this gate. See [Bulk Delete Commands](#bulk-delete-commands) for the full rule and disambiguation flow.
> 2. **Settings allowlist is hard.** Only the 37 PPAC toggles in [Allowed settings](#allowed-settings--hard-allowlist) may be read or updated. Any other setting **must be refused**: *"That setting is out of scope for dv-admin. Use the Power Platform admin center."*
> 3. **Recycle bin disable is PATCH, never DELETE.** `PATCH statecode=1, statuscode=2, isreadyforrecyclebin=false`. DELETE enqueues async opt-out and orphans per-entity configs — see [`references/recycle-bin.md`](references/recycle-bin.md).
> 4. **System tables warning.** Unfiltered bulk delete on `systemuser`, `businessunit`, `organization`, or `role` breaks the environment. Warn additionally before running.

**Four mechanisms — pick based on where the setting lives:**

| Mechanism | Use for | How |
|---|---|---|
| **PAC CLI** (`pac org update-settings` / `list-settings`) | Columns on the `organization` entity (audit, plugin trace, typeahead, quick find, canvas/flow solutions, email validation, audit retention) | `--name <column> --value <value>` — accepts any org column, not just the legacy audit ones |
| **Python SDK — OrgDB XML** | Keys inside the `orgdborgsettings` XML blob (MCP, search, Fabric, Work IQ, TDS endpoint, attachment security, ownership, address records, block unmanaged, delete users, Excel AI) | Read XML → parse → modify → PATCH whole blob back on `organizations({id})` |
| **Python SDK — recyclebinconfigs** | Recycle bin on/off + retention days | CREATE/PATCH `recyclebinconfigs` entity record |
| **Python SDK — settingdefinition + organizationsettings** | App-level / plan-level security role toggles | Look up `settingdefinition` by `uniquename` → CREATE or PATCH `organizationsettings` row with `value` |

ERP batch admin: see [`references/erp-batch.md`](references/erp-batch.md).

Do NOT write Python scripts for operations PAC CLI can handle. Do NOT mix mechanisms (e.g., don't hand-PATCH an org column that PAC CLI already covers).

## Skill boundaries

Use **dv-data** for record CRUD and sample data, **dv-metadata** for tables / columns / relationships, **dv-query** for reading records, **dv-solution** for solution export/import, **dv-security** for roles and self-elevate, `pac admin --help` for tenant governance (DLP, env lifecycle).

## Allowed settings — hard allowlist

The 37 PPAC toggles below are the only ones this skill may read or update (35 unique backend keys — `SearchAndCopilotIndexMode` and `auditretentionperiodv2` each cover two toggles). Examples that are **out of scope and must be refused** per the safety rule above: `sessiontimeoutinmins`, `isautosaveenabled`, `IsShadowLakeEnabled`, `IsArchivalEnabled`. Do not run `pac org list-settings` without `--filter`, and do not dump the whole `orgdborgsettings` XML for a non-allowlisted setting.

### Organization entity columns — use PAC CLI (14)

Use `pac org list-settings --filter <column>` to read, `pac org update-settings --name <column> --value <value>` to write. PAC CLI accepts any column on the organization entity — not just the legacy audit ones.

| # | PPAC label | Column | Type |
|---|---|---|---|
| 1 | Start Auditing | `isauditenabled` | bool |
| 2 | Audit user access (Log access) | `isuseraccessauditenabled` | bool |
| 3 | Start Read Auditing (Read logs to Purview) | `isreadauditenabled` | bool |
| 4 | Plugin trace log setting | `plugintracelogsetting` | int: `0` Off, `1` Exception, `2` All |
| 5 | Single table search option | `tablescopeddvsearchinapps` | bool |
| 6 | Prevent slow keyword filter for quick find terms | `allowleadingwildcardsinquickfind` | int: `0` prevent, `1` allow (UI "prevent=On" flips to `0`) |
| 7 | Quick Find record limits | `quickfindrecordlimitenabled` | bool |
| 8 | Use quick find view for searching on grids/subgrids | `usequickfindviewforgridsearch` | bool |
| 9 | Canvas apps in Dataverse solutions by default | `enablecanvasappsinsolutionsbydefault` | bool |
| 10 | Cloud flows in Dataverse solutions by default | `enableflowsinsolutionbydefault` | bool (note: `solution` singular) |
| 11 | Enable email address validation (preview) | `isemailaddressvalidationenabled` | bool |
| 12 | Minimum number of characters to trigger typeahead | `lookupcharactercountbeforeresolve` | int (0–MAX_INT, null = feature off) |
| 13 | Delay between character inputs that trigger a search | `lookupresolvedelayms` | int ms (default 250) |
| 14 | Audit log retention policy / Custom retention period (days) | `auditretentionperiodv2` | int days (`-1` = Forever; presets 30/90/180/365/730/2555; max 365000) |

### OrgDB XML keys — use Python SDK on `orgdborgsettings` blob (17)

PascalCase is significant (`IsMCPEnabled`, not `IsMcpEnabled`). Full key list with PPAC labels and types lives in [`references/orgdb-settings.md`](references/orgdb-settings.md). Notable keys: `IsMCPEnabled`, `IsMCPPreviewEnabled`, `SearchAndCopilotIndexMode`, `IsLinkToFabricEnabled`, `IsFabricVirtualTableEnabled`, `ShowDataInM365Copilot`, `EnableWorkIQ`, `IsLockdownOfUnmanagedCustomizationEnabled`, `EnableSecurityOnAttachment`, `EnableTDSEndpoint`, `AllowAccessToTDSEndpoint`, `EnableOwnershipAcrossBusinessUnits`, `CreateOnlyNonEmptyAddressRecordsForEligibleEntities`, `EnableDeleteAddressRecords`, `BlockDeleteManagedAttributeMap`, `EnableSystemUserDelete`, `IsExcelToExistingTableWithAssistedMappingEnabled`.

**`SearchAndCopilotIndexMode`** is one int (0–3) that encodes two UI toggles — Dataverse search × M365 Copilot search:

| Value | Dataverse search | M365 Copilot search |
|---|---|---|
| `0` | Off | On |
| `1` | On | On |
| `2` | Off | Off |
| `3` | On | Off |

### recyclebinconfigs entity — use Python SDK (2, org-level only)

Two toggles operate on the org-level `recyclebinconfigs` row (filtered by the organization entity's MetadataId): on/off (`statecode` + `statuscode` + `isreadyforrecyclebin`) and cleanup days (`cleanupintervalindays`). Per-table toggles are **out of scope** — refuse requests like "enable recycle bin for `contact` only". Full lifecycle in [`references/recycle-bin.md`](references/recycle-bin.md).

### settingdefinition + organizationsettings — use Python SDK (2)

Two allowlisted toggles, both bool stored as string: `PowerAppsAppLevelSecurityRolesEnabled` (canvas apps), `PlanShareSecurityRolesEnabled` (plan designer). Read default from `settingdefinition`, CREATE/PATCH `organizationsettings` for the override. Full Python in [`references/settings-overrides.md`](references/settings-overrides.md).

## Preview Before Running

- **Destructive / stateful** (bulk delete schedule/cancel/pause/resume, settings updates, recycle bin toggle, role assignment, self-elevate, retention set) — preview in prose: what's changing, new value, target environment(s). Use placeholders (`<ENV_URL>`) for unknowns and ask for missing values in the same turn. Skip the raw CLI block.
- **Read-only** (list-settings, show job, read OrgDB / recycle bin status) — one-sentence prose preview is enough.

The user must be able to evaluate the action from your first response. A bare *"which environment?"* fails; a one-line prose preview passes.

### Examples

**Pause bulk delete (destructive, ID supplied):**
- ❌ "The command requires approval. Please confirm to pause the job."
- ✅ "I'll pause bulk delete job `<job-id>` on the active environment. Confirm to proceed."

**Audit status across N environments (read-only, multi-call):**
- ❌ Sequential `pac org fetch` per env, or starting with Python/SDK because it "feels like a query."
- ✅ "I'll run `pac org list-settings --filter audit` in parallel across all N environments (one `&`-batch, single `wait`)."

## How to Read or Update Org Settings

**Org settings always go through `pac org list-settings` / `pac org update-settings`** — never raw Web API, FetchXML, PowerShell, or Python for org columns. Use `--filter <substring>` for category reads in one call. Multi-environment work runs in parallel via `&` + `wait` in ONE bash call.

**Single setting:**
```bash
pac org list-settings --filter isauditenabled --environment <url>
```

**Category read** (returns every match, e.g. all audit settings in one call):
```bash
pac org list-settings --filter audit --environment <url>
```

**Multi-environment — parallel in ONE bash call:**
```bash
pac org list-settings --filter audit --environment <url1> &
pac org list-settings --filter audit --environment <url2> &
pac org list-settings --filter audit --environment <url3> &
wait
```

If `pac org list-settings` fails for a setting, that setting is NOT an org column — check the mechanism routing in the four-mechanism table at the top, then use the appropriate Python pattern. Do NOT fall back to Web API, PowerShell, FetchXML, or `pac org fetch` for org columns.

---

## Prerequisites

- PAC CLI **latest .NET Framework build** — `pac data bulk-delete`/`pac data retention` are only in the .NET Framework build (not the `dotnet tool` version). Check `pac help`; if it shows `.NET 10`/`.NET 8`, run `pac install latest && pac use latest`.
- Authenticated (`pac auth create`), active profile (`pac auth list`), and System Administrator privilege on the target environment.
- **Headless hosts**: SDK handles most settings (they are `organization` records); service principal for PAC-only ones. See `dv-connect/references/headless-hosts.md`.

## Multi-Environment Operations — Always Parallel

The same `&` + `wait` pattern from `list-settings` applies to every multi-env operation (`update-settings`, `bulk-delete`, etc.) — N backgrounded calls in ONE bash call, never sequential or `for` loops.

---

## Common Mistakes — Do NOT Use These

These flags do not exist. Using them will produce errors.

### Bulk Delete
| Wrong | Correct |
|-------|---------|
| `--filter` | use `--fetchxml` with a FetchXML string |
| `--query` / `--where` / `--condition` | use `--fetchxml` |
| `--date` / `--before` / `--older-than` | encode date in FetchXML `<condition>` |
| `--job-id` | use `--id` |
| `--all` / `--purge` / `--truncate` | omit `--fetchxml` to target all records (warn user first) |

### Retention
| Wrong | Correct |
|-------|---------|
| `--fetchxml` | use `--criteria` (same FetchXML format, different flag name) |
| `--filter` / `--query` / `--policy` | use `--criteria` |
| `--enable` / `--activate` | use `pac data retention enable-entity` |
| `--table` | use `--entity` |
| `--operation-id` / `--job-id` / `--guid` | use `--id` |

### Org Settings
| Wrong | Correct |
|-------|---------|
| `--enable-audit` / `--audit` | use `--name isauditenabled --value true` |
| `--trace` / `--plugin-trace` / `--logging` | use `--name plugintracelogsetting --value 2` |
| `--setting` / `--key` / `--flag` | use `--name` |
| String values like `"all"` or `"enabled"` for option sets | use integers: `0`, `1`, `2` |

---

## Bulk Delete Commands

### Schedule a Bulk Delete Job

```bash
pac data bulk-delete schedule --entity activitypointer \
    --fetchxml "<fetch><entity name='activitypointer'><filter><condition attribute='createdon' operator='lt' value='2024-01-01'/></filter></entity></fetch>"
pac data bulk-delete schedule --entity email \
    --fetchxml "<fetch><entity name='email'><filter><condition attribute='createdon' operator='lt' value='2024-06-01'/></filter></entity></fetch>" \
    --job-name "Cleanup old emails" --recurrence "FREQ=DAILY;INTERVAL=1"
```

| Argument | Alias | Required | Description |
|----------|-------|----------|-------------|
| `--entity` | `-e` | Yes | Logical name of the table |
| `--fetchxml` | `-fx` | No | FetchXML filter. **See the hard-stop rule below — if omitted, ALL records in the table are deleted.** |
| `--job-name` | `-jn` | No | Descriptive name for the job |
| `--start-time` | `-st` | No | ISO 8601 start time. Defaults to now |
| `--recurrence` | `-r` | No | RFC 5545 pattern (e.g., `FREQ=DAILY;INTERVAL=1`) |
| `--environment` | `-env` | No | Target environment URL |

### Hard stop: no `--fetchxml` means ALL records

`pac data bulk-delete schedule` without `--fetchxml` targets every record in the table and is irreversible (does not go through recycle bin). Required gate:

1. **Refuse until the user explicitly acknowledges** with the word ALL (or ALL RECORDS) **and** the entity logical name — e.g., `"yes, delete ALL records in contact"`. Bare `"yes"` rejected.
2. **Disambiguate vague asks** ("clean up old emails") — propose a FetchXML filter with date / statecode / owner conditions before showing any command.
3. **Empty-filter FetchXML doesn't bypass the gate** — `<filter/>` or `<filter><condition><value/></condition></filter>` still targets every record.
4. **Scope:** applies to `bulk-delete schedule` only. `cancel`, `pause`, `resume`, `show`, `list` don't need it.

For system tables (`systemuser`, `businessunit`, `organization`, `role`), additionally warn that unfiltered bulk delete breaks the environment.

### Manage Jobs

```bash
pac data bulk-delete list --environment https://myorg.crm.dynamics.com
pac data bulk-delete show --id <job-id>
pac data bulk-delete pause --id <job-id>
pac data bulk-delete resume --id <job-id>
pac data bulk-delete cancel --id <job-id>
```

---

## Retention / Archival Commands

Data retention moves old records to long-term storage without permanently deleting them.

### Agentic Flow

```
Step 1: pac data retention enable-entity --entity activitypointer
Step 2: pac data retention list
Step 3: pac data retention set --entity activitypointer --criteria "<fetchxml>..."
Step 4: pac data retention show --id <config-id>
```

### Commands

```bash
pac data retention enable-entity --entity activitypointer --environment https://myorg.crm.dynamics.com
pac data retention set --entity activitypointer \
    --criteria "<fetch><entity name='activitypointer'><filter><condition attribute='createdon' operator='lt' value='2023-01-01'/></filter></entity></fetch>"
pac data retention list --environment https://myorg.crm.dynamics.com
pac data retention show --id <config-id>
pac data retention status --id <operation-id>
```

| Argument | Alias | Required | Description |
|----------|-------|----------|-------------|
| `--entity` | `-e` | Yes | Logical name of the table |
| `--criteria` | `-c` | Yes | FetchXML defining which records to archive |
| `--start-time` | `-st` | No | ISO 8601 start time. Defaults to now |
| `--recurrence` | `-r` | No | RFC 5545 recurrence pattern |
| `--environment` | `-env` | No | Target environment URL |

### Retention vs Bulk Delete

| Scenario | Use |
|----------|-----|
| Data no longer needed, permanently delete | **Bulk Delete** |
| Data must be preserved for compliance | **Retention** (archive) |

---

## Organization Settings Commands

### List Settings

```bash
pac org list-settings --environment https://myorg.crm.dynamics.com
pac org list-settings --filter isauditenabled --environment https://myorg.crm.dynamics.com
```

### Update a Setting

```bash
pac org update-settings --name isauditenabled --value true --environment https://myorg.crm.dynamics.com
pac org update-settings --name plugintracelogsetting --value 2 --environment https://myorg.crm.dynamics.com
```

**Args:** `--name <column>` (required), `--value <value>` (required; `true`/`false` for bool, int for option sets), `--environment <url>` (optional). Allowed columns are the 14 listed in [Allowed settings](#allowed-settings--hard-allowlist) — anything else is out of scope.

**Batch workflow:** `pac admin list` → filter targets → confirm → run all `update-settings` calls in parallel (`&` + `wait`) → render summary table.

---

## Advanced Settings (Python SDK — PAC CLI Cannot Handle These)

OrgDB, recycle bin, and settings-definition overrides each need raw Web API or the Python SDK — PAC CLI does not cover them. The four-mechanism routing table at the top of this skill ([§ Skill](#skill-environment-admin--bulk-delete-retention-org-settings-orgdb-recycle-bin)) maps each setting to its mechanism. Sub-sections below summarise the patterns and link to the references for full Python.

### OrgDB Settings (orgdborgsettings XML)

OrgDB settings live as PascalCase XML elements inside the `orgdborgsettings` column of the `organizations` entity. PAC CLI cannot read or write these — use raw Web API.

**Quick reference:** `GET /organizations?$select=organizationid,orgdborgsettings` → parse XML with `xml.etree.ElementTree` → modify or `SubElement` → `PATCH /organizations({id})` with the serialized XML.

For the read / update / remove Python patterns and the 17-key allowlist, see [`references/orgdb-settings.md`](references/orgdb-settings.md). Keys are case-sensitive (`IsMCPEnabled`, not `IsMcpEnabled`).

### Recycle Bin Configuration

Recycle bin settings live in the `recyclebinconfigs` entity (NOT `orgdborgsettings`). PAC CLI cannot manage them — use raw Web API.

**Quick reference:** filter `recyclebinconfigs` by `_extensionofrecordid_value eq <ORG_ENTITY_METADATA_ID>` (the org-level metadata ID is a system constant: `e1bd1119-6e9d-45a4-bc15-12051e65a0bd`).

- **Enable:** PATCH `statecode=0, statuscode=1, isreadyforrecyclebin=true` (or POST a new config). `isreadyforrecyclebin: true` is required to force the synchronous opt-in path.
- **Disable:** PATCH `statecode=1, statuscode=2, isreadyforrecyclebin=false`. **Do NOT DELETE** — it enqueues async opt-out and orphans per-entity configs.
- **Drain in-flight `ProcessRecycleBin` async jobs** (`operationtype eq 50, statecode ne 3`) before any second toggle.

For the full Python lifecycle (read / enable / disable / async-drain helper), the cache-vs-DB race explanation, and the per-table out-of-scope rule, see [`references/recycle-bin.md`](references/recycle-bin.md).

### Settings-Definition Overrides (app/plan security roles)

Two allowlisted toggles (`PowerAppsAppLevelSecurityRolesEnabled`, `PlanShareSecurityRolesEnabled`) live in a join: `settingdefinition` (defaults) + `organizationsettings` (overrides). PAC CLI doesn't manage these.

**Quick reference:** look up the `settingdefinitionid` by `uniquename`, then either CREATE an `organizationsettings` row with `value` (string `"true"`/`"false"`) or PATCH the existing row. DELETE on the override row reverts to the default.

For the read + idempotent CREATE/PATCH Python and the gating notes, see [`references/settings-overrides.md`](references/settings-overrides.md).

## Operational confirmation rules

The four rules in the safety callout at the top of this file cover the irreversible / destructive cases. The rules below cover the non-destructive but still impactful operations:

- Confirm before changing org settings that affect all users.
- For multi-environment updates, show the list of target environments and get confirmation first.
- For OrgDB settings, warn that incorrect values can break environment features.
- For recycle bin cleanup interval changes, warn that reducing the interval permanently deletes recycled records sooner.
- For recycle bin enable/disable specifically: always set `isreadyforrecyclebin` explicitly (true on enable, false on disable), and drain any in-flight `ProcessRecycleBin` async jobs before any second toggle. Omitting these can produce `EntityBinUpdateAction called for entity <x> which is not enabled for RecycleBin` on unrelated platform operations.

Referenced files: 4

View saved version →

---
name: dv-connect
description: One-step setup for a Dataverse environment — installs tools, authenticates, registers the MCP server, and writes `.env`. Use when starting a new project, switching environments, fixing authentication, or troubleshooting an MCP connection that won't come up.
---

# Skill: Connect

One-step, idempotent Dataverse connection. Each step checks if it's already done and skips.

> **Environment-First Rule** — All metadata and plugin registrations are created **in the environment** via API/scripts, then pulled into the repo. Never hand-write solution XML to create components.

**Execute steps in order; do not skip ahead.** **Exception:** Step 0 can short-circuit the flow if the workspace is already set up.

> **Host entry test (FIRST — before Step 0).** A **local Windows/macOS host is capable by default, whichever agent drives it** — it can run the CLIs, use persistent credentials, and host local MCP servers; an approval/sandbox gate isn't a constraint, and a **missing CLI = install it**. **Constrained only when** a runtime **can't start**, auth **can't persist**, or the host is explicitly **ChatGPT Work Mode / Codex cloud / CI / no-keyring Linux** (deterministic table: [headless-hosts.md](references/headless-hosts.md)). **Constrained →** install **only** Python + pip deps, `.env`, `scripts/auth.py`, verify `python scripts/auth.py --check`, **skip** CLI / PAC / MCP. **Capable →** normal flow below.

---

## Step 0: Detect existing setup (run this first)

Before touching anything, check whether this workspace is already connected to a Dataverse environment. Repeating setup on an already-configured workspace overwrites `.env`, re-registers MCP, and wastes time.

Run these checks in order. If **all four pass**, skip straight to Step 7 (final verification) and stop there.

1. **`.env` is present and complete** — file exists at the workspace root and contains non-empty values for `DATAVERSE_URL`, `TENANT_ID`, and `MCP_CLIENT_ID`
2. **MCP is registered** — `.mcp.json` (Claude Code) or the equivalent Copilot / Cursor config file has a `dataverse-*` server entry pointing at the `DATAVERSE_URL` from `.env`
3. **Both auth surfaces match `.env`** — `dataverse auth who` shows a profile whose `Environment Url` matches `DATAVERSE_URL`, AND `pac org who` against a PAC profile for the same URL succeeds. (DV CLI auth covers Connect / Data / Query / Metadata / MCP / Python; PAC auth covers `dv-solution` and `dv-admin`. Both are front-loaded at connect time so neither prompts later.)
4. **Python SDK is importable and current** — `python -c "from PowerPlatform.Dataverse.client import DataverseClient; import pandas; from importlib.metadata import version; v=version('PowerPlatform-Dataverse-Client'); assert int(v.split('.')[0])>=1, f'SDK {v} is outdated, need >=1.0.0'"` exits 0

**If all pass:** Refresh `DATAVERSE_PLUGIN_VERSION` in `.env` if it's stale, confirm the detected setup (URL, profile, MCP server), and jump to Step 7. Do not otherwise rewrite `.env`, re-register MCP, or re-run `pip install`.

**If any check fails:** Proceed through the normal flow (Steps 1–7), but still use each step's own skip condition. A partially-configured workspace doesn't need a full redo — e.g., if only `.env` and MCP are missing but tools and auth are fine, start at Step 2 or Step 3.

---

## Step 1: Ensure tools are installed

Check each tool independently -- report all missing tools at once. See [tools-setup.md](references/tools-setup.md) for install commands.

| Tool | Check |
|---|---|
| Python 3 | `python --version` |
| Git | `git --version` |
| Node.js | `node --version` |
| PAC CLI | `pac` (prints version banner; `pac --version` is not valid) (see [tools-setup.md](references/tools-setup.md) if not in PATH) |
| Dataverse CLI | `npm list -g @microsoft/dataverse` |
| .NET SDK | `dotnet --version` |
| Azure CLI | `az --version` |

.NET SDK is needed for PAC CLI but NOT for the Dataverse CLI (the npm package bundles its own runtime). Node.js powers the Dataverse CLI npm package (`@microsoft/dataverse`), which is used as the MCP proxy and for scripted data plane actions. Azure CLI is used as a fallback for environment discovery when PAC CLI isn't available (see [mcp-configuration.md](references/mcp-configuration.md) Step 3b). GitHub CLI is not needed for connecting — it's used later for ALM/CI/CD scenarios (see `dv-solution`).

If any tool is missing, install it (see [tools-setup.md](references/tools-setup.md)), then verify. If `winget` installs a tool but it's not in PATH, ask the user to restart the terminal.

After Python is confirmed, check if deps are already present before installing:
```
python -c "from PowerPlatform.Dataverse.client import DataverseClient; import azure.identity, msal, msal_extensions, requests, pandas; print('OK')"
```
If it prints `OK`, skip pip. Otherwise:
```
pip install --upgrade azure-identity requests PowerPlatform-Dataverse-Client pandas msal msal-extensions
```

`msal` + `msal-extensions` let `scripts/auth.py` reuse the `dataverse auth create` cache -- one sign-in for CLI, MCP, Python.

After Node.js is confirmed, install the Dataverse CLI **only if missing** (do not re-run on every connect -- on managed devices each `@latest` fetch can trigger npm-registry security prompts; see [tools-setup.md](references/tools-setup.md)):
```
npm install -g @microsoft/dataverse@latest
```

**Skip condition:** All tools present, Python SDK installed, and `pandas` importable (`python -c "import pandas"`).

---

## Step 2: Discover and select the environment

Before asking the user for a URL, check what's already available.

> **Auth tool choice.** Two tools, two AAD apps, two caches — front-load both at connect:
>
> 1. **`dataverse auth create`** (app `0c412cc3-…`) covers DV CLI + MCP + Python.
> 2. **`pac auth create`** (PAC's own app) covers `dv-solution` + `dv-admin`.

Check for an existing DV CLI profile first, then fall back to PAC for environment discovery if needed:

```
dataverse auth list
dataverse auth who
pac auth list   # PAC profiles are still useful for env discovery / pac org list
```

**If `dataverse auth who` shows a profile and its environment matches the user's target:**
- Reuse it. Set `DATAVERSE_URL` and `TENANT_ID` from the profile.

**If no DV CLI profile exists (or it points at the wrong environment):**
- Ask: "Do you want to connect to an existing environment or create a new one?"

**Before selecting, check for tenant/region mismatch.** If the target URL uses a different region than the authenticated account's environments, create a new profile for the correct tenant rather than reuse the old one:

```
dataverse auth create --environment <url>          # interactive (WAM broker on Windows → no browser tab)
dataverse auth create --environment <url> --deviceCode   # headless / remote / SSH
```

If the user hits an admin-consent error, the CLI prints the correct scope-scoped consent URL to share with a tenant admin — do not synthesize one.

**To switch between existing DV CLI profiles:**
```
dataverse auth select --name <profile-name>
```

**To create a new environment** (requires admin permissions):
```
pac admin create --name "<name>" --type "<type>" --region "<region>"
```
If this fails with permissions error, guide the user to [Power Platform Admin Center](https://admin.powerplatform.microsoft.com/) to create it, then connect.

**Confirm connection:**
```
dataverse auth who
dataverse org who      # or: pac org who
```
Parse the output to extract `DATAVERSE_URL`, `TENANT_ID`, and — on ERP-linked envs — `ERP_URL` (see [`erp-detection.md`](references/erp-detection.md)).

If neither command shows a tenant ID, fall back to:
```bash
curl -sI https://<org>.crm.dynamics.com/api/data/v9.2/ \
  | grep -i "WWW-Authenticate" \
  | sed -n 's|.*login\.microsoftonline\.com/\([^/]*\).*|\1|p'
```

### Step 2b: Front-load PAC CLI auth for the same environment

PAC uses its own AAD app, so a separate sign-in is required for `dv-solution` and `dv-admin` — do it now.

```
pac auth list                                       # skip if a profile for $DATAVERSE_URL exists
pac auth create --name <orgid> --environment <DATAVERSE_URL>
```

Use the same account as Step 2. If PAC CLI is not installed, skip with a note that `dv-solution` / `dv-admin` will need it later.


---

## Step 3: Create .env

Present authentication options:

> How would you like to authenticate with Dataverse?
> 1. **Interactive login (recommended)** — Sign in via browser. No app registration needed. Token stays cached across sessions.
> 2. **Service principal (for CI/CD)** — Uses CLIENT_ID and CLIENT_SECRET from an Azure app registration.

Write `.env` directly — do not instruct the user to create it:

Detect the current tool (Claude or Copilot) from context and set `MCP_CLIENT_ID` automatically:
- Claude (CLI or VSCode extension): `0c412cc3-0dd6-449b-987f-05b053db9457`
- GitHub Copilot: `aebc6443-996d-45c2-90f0-388ff96faa56`

Also set plugin attribution variables for User-Agent tagging. **Fill in the two literals below from your own context** — you (the agent) loaded this plugin, so you already know both values:

- `PLUGIN_VERSION` — the `version` field of your loaded plugin manifest (e.g. `"1.5.0"`). At runtime, `auth.py` re-reads this from the live manifest via host env vars; this `.env` entry is a fallback for offline cases.
- `AGENT` — your host identity, one of: `claude-code`, `copilot`, `cursor`, `codex`, or `unknown`. Must match an entry in `_ALLOWED_AGENTS` in `auth.py` — if you don't recognize your host, use `unknown`.

```python
# Substitute these two literals from your loaded plugin context.
# Do NOT leave the angle-bracket placeholders — replace with real values.
plugin_version = "<plugin manifest version, e.g. 1.5.0>"
agent_host = "<your host name: claude-code | copilot | cursor | codex | unknown>"

with open(".env", "w") as f:
    f.write(f"DATAVERSE_URL={dataverse_url}\n")
    f.write(f"TENANT_ID={tenant_id}\n")
    f.write(f"MCP_CLIENT_ID={mcp_client_id}\n")
    f.write(f"DATAVERSE_PLUGIN_VERSION={plugin_version}\n")
    f.write(f"DATAVERSE_PLUGIN_AGENT={agent_host}\n")
    f.write(f"SOLUTION_NAME={solution_name}\n")
    f.write(f"PUBLISHER_PREFIX=\n")  # filled in when solution is created
    f.write(f"PAC_AUTH_PROFILE=nonprod\n")
    if client_id:
        f.write(f"CLIENT_ID={client_id}\n")
    if client_secret:
        f.write(f"CLIENT_SECRET={client_secret}\n")
```

Ensure `.env` is in `.gitignore`:

```python
import os

GITIGNORE_ENTRIES = [
    ".env", ".vscode/settings.json", ".claude/mcp_settings.json",
    ".token_cache.bin", ".dataverse/", "*.snk", "__pycache__/", "*.pyc",
    "solutions/*.zip", "plugins/**/bin/", "plugins/**/obj/",
]
gitignore = open(".gitignore").read() if os.path.exists(".gitignore") else ""
missing = [e for e in GITIGNORE_ENTRIES if e not in gitignore]
if missing:
    with open(".gitignore", "a") as f:
        f.write("\n" + "\n".join(missing) + "\n")
```

**Skip condition:** `.env` already exists with all required values.

---

## Step 4: Set up project structure (new projects only)

If this is a new project (no `scripts/` directory):

```
mkdir -p solutions plugins scripts
```

Copy plugin scripts:
```
cp .github/plugins/dataverse/scripts/auth.py scripts/
```

Copy `templates/CLAUDE.md` to the repo root if it doesn't exist. Replace placeholders (`{{DATAVERSE_URL}}`, `{{SOLUTION_NAME}}`, `{{PUBLISHER_PREFIX}}`) with values from `.env`.

**Skip condition:** `scripts/auth.py` exists.

---

## Step 5: Verify the connection

```
dataverse auth who
pac org who
python scripts/auth.py --check
```

`--check` makes a **real data-plane call** (not just a token) — the only proof the org is actually reachable; a token can be minted while the org domain is blocked. All must resolve the same user/environment, proving the DV CLI cache, the PAC profile (Step 2b), and Python's reuse of the shared cache are wired.

**If any fail:**
- `dataverse auth who` fails → re-run Step 2.
- `pac org who` fails → re-run Step 2b.
- `python scripts/auth.py --check` prints a device-code URL → browser/WAM cache has no Python-reusable token. **Auto-fix:** re-run `dataverse auth create --environment <url> --deviceCode`, then retry. If it *still* prompts, check `pip show msal msal-extensions`. **Headless hosts** (ChatGPT / Codex cloud / CI): `dataverse auth create` can't persist here — don't loop (see [headless-hosts.md](references/headless-hosts.md)).
- `python scripts/auth.py --check` prints `NOT REACHABLE` with a connection/timeout error → the org domain is blocked by network egress, not auth. Do NOT report success or a count — see [headless-hosts.md](references/headless-hosts.md) remediation.
- Other Python error → check SDK install and `.env`.

Before metadata work, also confirm the account has the `prvCreateEntity` customization privilege — see [tools-setup.md](references/tools-setup.md#privilege-preflight).

---

## Step 6: Configure MCP server

**Skip this step** if MCP is already configured:
- `.mcp.json` or `~/.copilot/mcp-config.json` or `~/.cursor/mcp.json` or `~/.codex/config.toml` contains a Dataverse server entry
- `claude mcp list` shows a `dataverse-*` server registered

If MCP is not configured, follow [mcp-configuration.md](references/mcp-configuration.md):

1. Detect which tool the user is running (Copilot, Claude, Cursor, or Codex) from context
2. Set `MCP_CLIENT_ID` based on tool choice
3. Get environment URL from `.env`
4. Default to GA endpoint (`/api/mcp`)
5. Register the MCP server per host (see the per-host blocks below)
6. Handle admin consent and allowlist — prefer `dataverse mcp allow <MCP_CLIENT_ID>` over the portal (one-time per tenant/environment)

**Plugin attribution for MCP:** This plugin uses the **stdio proxy** transport (`npx @microsoft/dataverse mcp <url>`). When registering it, include `DATAVERSE_OPERATION_CONTEXT` in the env block so the CLI appends it to its User-Agent on requests to `/api/mcp`. Build the value from `.env`:

```
DATAVERSE_OPERATION_CONTEXT=app=dataverse-skills/{DATAVERSE_PLUGIN_VERSION};skill=mcp-direct;agent={DATAVERSE_PLUGIN_AGENT}
```

For Claude Code (`claude mcp add -t stdio`), pass it via `-e DATAVERSE_OPERATION_CONTEXT=...`. For Copilot/Cursor JSON configs, add it to the `"env"` object in the stdio server entry; for Codex, add it to its `[mcp_servers.<name>.env]` table.

**Important:** MCP configuration requires an editor/CLI restart.

**For Copilot:** Write the JSON config, then:
> ✅ Dataverse MCP server configured. **Restart your editor** for changes to take effect.

**For Claude:** Run the `claude mcp add` command, then warn the user about the auth popup that will appear on next launch:
> ✅ Dataverse MCP server registered. Restart Claude Code to enable MCP tools.
> Remember to **use `claude --continue` to resume the session** without losing context.
>
> On restart, a browser window may open to sign in to your Dataverse environment (the MCP proxy authenticating on your behalf). See [mcp-configuration.md](references/mcp-configuration.md) for details.

**For Cursor:** Write the JSON config, then:
> ✅ Dataverse MCP server `dataverse-{orgid}` configured in `~/.cursor/mcp.json`. **Reload the Cursor window** (Ctrl+Shift+P → "Developer: Reload Window") for the new MCP server to appear under Settings → Tools & MCPs.
>
> On first use the `npx @microsoft/dataverse` proxy signs in via browser device code, then reuses the shared cache silently. See [mcp-configuration.md](references/mcp-configuration.md).

**For Codex:** Write the TOML config to `~/.codex/config.toml`. Codex loads MCP tools only at startup, so don't claim they're callable until the user restarts. Tell the user:
> ✅ Dataverse MCP server `dataverse-{orgid}` configured in `~/.codex/config.toml`. **Restart Codex** (CLI) or reload the Codex IDE to load the MCP tools.

---

## Step 7: Final verification

After the editor/CLI restarts, **both** of these must succeed before declaring the setup complete:

**Check 1: `claude mcp list` (or Copilot equivalent) shows ✓ Connected**
```
claude mcp list
```
This proves the MCP server process starts and speaks the MCP protocol. It does NOT by itself prove that data operations work — authentication, environment allowlisting, and endpoint reachability are only exercised on the first real tool call.

**Check 2: Agent successfully lists tables via `describe`/`search` and returns data**
> "List the tables in my Dataverse environment."

This proves end-to-end wiring: auth, tenant consent, environment allowlist, and endpoint reachability are all correct. If the agent falls back to PAC CLI or Web API, see [mcp-configuration.md](references/mcp-configuration.md) troubleshooting.

Only when **both** checks pass is the setup verified.

**Interpreting failures:**

- If Check 1 fails (server not ✓ Connected): the MCP server itself cannot start. Re-run Step 6 and check that `npx`/Node.js are installed and the MCP registration succeeded.
- If Check 1 passes but Check 2 fails (server starts but `describe`/`search` errors): the server can speak MCP but cannot reach or read Dataverse. Run `--validate` below to diagnose.

**Diagnostic — `--validate` (for failure investigation only):**
```
npx @microsoft/dataverse mcp {DATAVERSE_URL} --validate
```
This exercises two Dataverse MCP endpoints with a fresh authentication handshake and reports detailed errors (auth, allowlist, consent, endpoint reachability):

- **GA / Production endpoint** — `{DATAVERSE_URL}/api/mcp`. This is the one the plugin actually uses at runtime.
- **Preview endpoint** — `{DATAVERSE_URL}/api/mcp_preview`. Opt-in per environment; not used by the plugin.

**Do not use `--validate` as a success gate on first-time setup.** On a freshly configured workspace, the token cache hasn't warmed up, so `--validate` can fail with `MsalClientException` or `403` while MCP is actually working fine on subsequent real calls. Reserve `--validate` for diagnosing a confirmed failure in Check 1 or Check 2.

**How to read `--validate` output:**

- **Look at the GA / Production endpoint (`/api/mcp`) result first.** If this passes, MCP will work for normal plugin usage regardless of what the Preview endpoint reports.
- **A `403 Forbidden` on the Preview endpoint (`/api/mcp_preview`) is expected for most environments.** Preview is opt-in per environment; if your environment hasn't enabled it, the Preview check will always fail. This does not indicate a broken setup.
- **Ignore the overall exit code and the `⚠ Partial success` warning in this case.** The validator returns exit code `1` (failure) unless BOTH `/api/mcp` and `/api/mcp_preview` pass. Because most environments don't enable the Preview endpoint, `--validate` will exit `1` even when MCP is fully functional via the GA endpoint. Focus on per-endpoint results, not the aggregate status.
- **If the GA endpoint (`/api/mcp`) fails:** that's the real signal to investigate — auth, tenant consent, environment allowlist, or endpoint reachability.

### MCP Server Capabilities

For what MCP can and can't do (data CRUD + batch up to 25, table/column creation incl. choice/lookup, `search`/`describe`, file upload/download) versus the SDK / Web API, see the **overview** skill's Tool Capabilities matrix.

After verifying MCP works, tell the user:

> ✅ Connected to Dataverse at `{DATAVERSE_URL}`. Tools installed, authenticated, MCP live.
>
> You can now:
> - Create tables, columns, and relationships (`dv-metadata`)
> - Write and import data (`dv-data`)
> - Query and analyze data (`dv-query`)
> - Export and promote solutions (`dv-solution`)
>
> To create your first solution, see the `dv-solution` skill.
> To load sample data (accounts, contacts, opportunities), ask: "Load demo data into my Dataverse environment."

---

## Supported Agents

This plugin's skill files are natively loaded by both **GitHub Copilot CLI** and **Claude Code CLI** when installed as a plugin. No manual context-loading is needed — both agents discover and invoke skills automatically.

The PAC CLI commands, Python scripts, and XML templates work identically in both environments.

Referenced files: 4

View saved version →

---
name: dv-data
description: Record-level CRUD and bulk operations — create, update, delete, upsert, CSV import, multi-table foreign-key loads, AI-generated sample data. Use when the user wants to write, modify, seed, or import data records into Dataverse tables.
---

# Skill: Data — Create, Update, Delete, and Bulk Import

> **This skill uses Python and the Dataverse CLI.** Do not use Node.js, JavaScript, or any other language for Dataverse scripting. If you are about to run `npm install` or write a `.js` file, STOP — you are going off-rails. See the overview skill's Hard Rules.

Use the official Microsoft Power Platform Dataverse Client Python SDK for all data write operations.

**Official SDK:** https://github.com/microsoft/PowerPlatform-DataverseClient-Python
**PyPI package:** `PowerPlatform-Dataverse-Client` (this is the only official one — do not use `dataverse-api` or other unofficial packages)
**Status:** GA (`1.0.0`, Production/Stable)

## Skill boundaries

| Need | Use instead |
|---|---|
| Query or read records | **dv-query** |
| Create tables, columns, relationships, forms, views | **dv-metadata** |
| Export or deploy solutions | **dv-solution** |
| ERP writes | See [`references/erp-writes.md`](references/erp-writes.md) |

---

## Choosing MCP, CLI, or SDK for writes

**CLI fast path:** If `dataverse auth who` shows an active profile, CLI commands (`data create/update/delete/upsert/associate/upload`) work immediately — no `.env`, `auth.py`, or pip needed. SDK and bulk operations still need workspace setup.

**If MCP tools are available** (`create_record`, `update_record`, `delete_record`), they are the quickest path for a **small, interactive** set of writes — up to 25 records per call, no script needed. **The Dataverse CLI** (`dataverse data create/update/upsert/delete`) handles single-record writes, associate/disassociate, and file uploads as headless one-liners. **The SDK** is the default for bulk writes beyond 25, data transformation, retry logic, CSV import, or SDK-only operations (upsert with alternate keys — MCP has no upsert tool). Pick the surface that fits the volume and shape of the work.

## When you script a write, use the SDK — not hand-rolled HTTP

The MCP/CLI/SDK choice is capability-based (above; and see the overview's **Tool Capabilities** / Hard Rule 2). This section is narrower: **once you've decided to write via a script**, use the SDK for anything in its "supports" list rather than hand-rolled `urllib`/`requests` — the SDK carries the auth, paging, and retry those re-implement. For the rare operation the SDK doesn't cover, use the `dataverse api` escape hatch — not hand-rolled `urllib`.

**Correct import** (always preceded by `sys.path.insert` in a full script — see Setup below):
```
from auth import get_client
```

**WRONG for SDK-supported operations:**
```
from auth import get_token, load_env  # WRONG for SDK-supported ops
import requests                        # WRONG for SDK-supported ops
```

`get_token()` and `requests` exist ONLY for genuine gaps with no managed path (global option sets, unbound actions) — and even then prefer the managed `dataverse api` escape hatch. Forms/views, aggregation, and N:N reads are all covered by the SDK; see **dv-query** and **dv-metadata**.

---

## What This SDK Supports (Data Operations)

- Record writes: create, update, delete
- Record reads within write workflows (e.g., lookup resolution) — for standalone queries see **dv-query**
- Upsert (with alternate key support)
- Bulk operations: `CreateMultiple`, `UpdateMultiple`, `UpsertMultiple`
- File column uploads (chunked for files >128MB)
- Context manager with HTTP connection pooling

## What This SDK Does NOT Support

Forms/views (`systemform`/`savedquery`) **are** ordinary records — create/modify them with `client.records.*` (see **dv-metadata**), and read N:N with `records.list(expand=...)`. For the genuine gaps below, prefer the managed `dataverse api` escape hatch over raw `urllib`:
- Global option sets — see **dv-metadata**
- N:N record association — CLI `dataverse data associate`, or `POST /api/data/v9.2/<entity>(<id>)/<nav-property>/$ref`
- `$apply` aggregation — use `client.query.fetchxml()`; see **dv-query**
- Unbound actions (e.g., `PublishXml`, `InstallSampleData`) — `dataverse api request`/`invoke`
- DeleteMultiple, general OData batching

### Dataverse CLI data examples (copy-paste ready)

All `dataverse` commands take `--context` for skill attribution (global flag).

```bash
# Create a record (--table is the EntitySet name)
dataverse data create --table accounts --data '{"name":"Contoso"}' --return --json --context "app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>"

# Update by GUID
dataverse data update --table accounts --id <guid> --data '{"name":"Contoso (updated)"}' --json --context "app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>"

# Upsert by alternate key (idempotent — safe to re-run)
dataverse data upsert --table accounts --key "accountnumber='ACC-001'" --data '{"name":"Contoso Ltd"}' --json --context "app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>"

# Delete (--no-confirm skips the prompt)
dataverse data delete --table accounts --id <guid> --no-confirm --context "app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>"

# Associate two records (N:N or lookup)
dataverse data associate --table accounts --id <guid> --relationship contact_customer_accounts --related contacts --related-id <contact-guid> --context "app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>"

# Disassociate (N:N — pass --related-id; clear a lookup — omit --related-id)
dataverse data disassociate --table accounts --id <guid> --relationship contact_customer_accounts --related-id <contact-guid> --context "app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>"

# Upload a file to a file column (--table takes LogicalName, not EntitySet)
dataverse data upload --table account --id <guid> --column new_document --file report.pdf --context "app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>"

# Describe entity schema (attributes, relationships, actions)
dataverse data describe --table account --include all --json --context "app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>"

# Invoke a discovered custom API by name (use 'api list' to find names)
dataverse api invoke <CustomApiName> --target dataverse --param Input=value --context "app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>"

# Raw API escape hatch for built-in actions (--target is required)
dataverse api request --target dataverse --path "/api/data/v9.2/WhoAmI" --context "app=dataverse-skills/<ver>;skill=dv-data;agent=<agent>"
```

---

## Setup

```python
import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_client

# get_client sets a plugin attribution context on the User-Agent header.
# Do not modify the context value — it is a closed schema for server-side
# telemetry (app/skill/agent). Never include secrets or PII.
client = get_client("dv-data")
```

`get_client(skill)` handles auth, environment URL, and plugin attribution (User-Agent tagging). See `scripts/auth.py`.

For scripts that run to completion: wrap in `with DataverseClient(...) as client:` for automatic connection cleanup (recommended). For notebooks and interactive sessions, the explicit client above is simpler.

---

## Field Name Casing Rule

Getting this wrong causes 400 errors.

| Property type | Convention | Example | When used |
|---|---|---|---|
| **Structural** (columns) | LogicalName — always lowercase | `new_name`, `new_priority` | Record payload keys |
| **Navigation** (lookups) | Navigation Property Name — case-sensitive, matches `$metadata` | `new_AccountId` | `@odata.bind` keys |

The SDK lowercases structural keys automatically but preserves `@odata.bind` key casing.

---

## Create a Record

```python
guid = client.records.create("new_ticket", {
    "new_name": "Ticket 001",
    "new_priority": 100000002,          # choice column — integer value, not string
    "new_AccountId@odata.bind": "/accounts(<account-guid>)",
})
print(f"Created: {guid}")
```

**`@odata.bind` notes:**
- Key is the Navigation Property Name: `new_AccountId@odata.bind` (the SDK preserves casing automatically, but matching the schema name is still the correct form)
- Value is `"/<EntitySetName>(<guid>)"` — e.g., `"/accounts(<guid>)"`
- If you just created the lookup column, wait 5–10 seconds before inserting. Metadata propagation delays cause "Invalid property" errors.
- Choice columns use integer values, not strings: `"new_priority": 100000002` (not `"High"`)

### Common `@odata.bind` patterns

| Lookup | Correct key | Wrong |
|---|---|---|
| Custom: `new_AccountId` | `new_AccountId@odata.bind` | ~~`new_accountid@odata.bind`~~ |
| System polymorphic: `customerid` | `customerid_account@odata.bind` | ~~`customerid@odata.bind`~~ |
| System: `parentcustomerid` | `parentcustomerid_account@odata.bind` | ~~`_parentcustomerid_value@odata.bind`~~ |

### Find the Navigation Property Name

After creating a lookup via SDK: `result.lookup_schema_name` is the navigation property name.

For existing system tables, query:
```
GET /api/data/v9.2/EntityDefinitions(LogicalName='<entity>')/ManyToOneRelationships
  ?$select=ReferencingEntityNavigationPropertyName,ReferencedEntity
```

---

## Update a Record

```python
client.records.update("new_ticket", "<record-guid>",
    {"new_status": 100000001})
```

---

## Delete a Record

```python
client.records.delete("new_ticket", "<record-guid>")
```

---

## Bulk Create (SDK uses CreateMultiple internally)

```python
records = [{"new_name": f"Ticket {i}", "new_priority": 100000000} for i in range(500)]
guids = client.records.create("new_ticket", records)
print(f"Created {len(guids)} records")
```

Volume guidance: CLI `dataverse data create` for one-off records. MCP `create_record` batches up to 25 per call. SDK `CreateMultiple` for larger bulk.

**Important:** The SDK sends all records in a single POST to `CreateMultiple`. It does **not** chunk automatically. Dataverse has no fixed record count limit — the constraints are payload size and request timeout (SDK default: 120s for POST). For larger datasets, you **must** chunk in your script. The `bulk_upsert` and `bulk_create` helpers below use adaptive chunking: start at 1,000, double on success (up to 4,000), halve on payload/timeout failure, and cap at the last successful size. Tables with few columns can handle larger chunks than tables with many columns.

---

## Bulk Update

```python
# Broadcast same change to multiple records
client.records.update("new_ticket",
    [id1, id2, id3],
    {"new_status": 100000001})
```

---

## DataFrame Write-Back

To create or update records from a pandas DataFrame, use the `client.dataframe` namespace (`create`/`update`). This is documented in **dv-query** but is a write operation — include it in your data write workflow:

```python
# Update records — DataFrame must include the primary key column
client.dataframe.update("opportunity", df_updates, id_column="opportunityid")

# Create records — returns a Series of new GUIDs
guids = client.dataframe.create("opportunity", df_new_records)
```

See **dv-query** for the full `client.dataframe` write reference; for reads use `client.query.builder(...).execute().to_dataframe()`.

---

## Upsert (Alternate Keys)

Idempotent — re-running the same import does not create duplicates. The alternate key must be defined on the table first — see **dv-metadata**.

**Do NOT include alternate key columns in the record body.** The alternate key identifies the record; the record body contains the data to set. If the same column appears in both, `UpsertMultiple` fails with "An unexpected error occurred" (single upsert tolerates it, bulk does not).

```python
from PowerPlatform.Dataverse.models.upsert import UpsertItem

client.records.upsert("account", [
    UpsertItem(
        alternate_key={"accountnumber": "ACC-001"},
        record={"name": "Contoso Ltd", "description": "Primary account"},
    ),
    UpsertItem(
        alternate_key={"accountnumber": "ACC-002"},
        record={"name": "Fabrikam Inc"},
    ),
])
```

---

## Bulk Import from CSV

> **For imports that may be re-run** (most real-world cases), use `UpsertItem` with alternate keys instead of `create()` — see [`references/multi-table-fk-import.md`](references/multi-table-fk-import.md). The `create()` pattern here is for one-shot loads only.

| Volume | Tool | Why |
|---|---|---|
| 1 record | CLI `dataverse data create` or MCP `create_record` | No script needed |
| 2–25 records | MCP `create_record` | Batches up to 25 per call |
| 25+ records | SDK `client.records.create(table, list)` | Uses CreateMultiple; chunk large datasets (start at 1K, adapt) |

```python
import csv, os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_client

# get_client sets a plugin attribution context on the User-Agent header.
# Do not modify the context value — it is a closed schema for server-side
# telemetry (app/skill/agent). Never include secrets or PII.
client = get_client("dv-data")

with open("data/customers.csv", newline="", encoding="utf-8") as f:
    rows = list(csv.DictReader(f))

records = [{"new_name": row["name"], "new_email": row["email"]} for row in rows]

# SDK sends all in one POST — chunk to avoid payload/timeout limits
# Start at 1000; for narrow tables (few columns) you can go higher
chunk_size = 1000
for i in range(0, len(records), chunk_size):
    guids = client.records.create("new_customer", records[i:i + chunk_size])
    print(f"Imported {i + len(guids)}/{len(records)} customers", flush=True)
```

### Lookup resolution during import

If the CSV has a human-readable key (e.g., `customer_email`) but Dataverse needs a GUID, pre-resolve with a lookup dict:

```python
# Build email -> GUID map first
email_to_guid = {}
for r in client.records.list("new_customer", select=["new_customerid", "new_email"]):
    email_to_guid[r["new_email"]] = r["new_customerid"]

# Use it during import
records = []
for row in rows:
    customer_guid = email_to_guid.get(row["customer_email"])
    if not customer_guid:
        print(f"Skipping row — unknown email: {row['customer_email']}")
        continue
    records.append({
        "new_channel": row["channel"],
        "new_CustomerId@odata.bind": f"/new_customers({customer_guid})",  # verify entity set name via EntityDefinitions
    })

guids = client.records.create("new_interaction", records)
```

### Required field discovery for system tables

Before bulk-creating in a system table (account, contact, opportunity):
1. Create a single test record with your intended minimal payload
2. If `HttpError` 400 is raised, the error message names the missing required field
3. Some required fields are plugin-enforced and not visible in `describe`
4. Delete the test record, then proceed with bulk create

---

## Multi-Table Import with FK Dependencies

When importing data across multiple tables with foreign key relationships, the import must run in dependency order with `UpsertItem` + alternate keys (idempotent, safe for re-runs).

**Quick reference:**
1. Create tables with source ID columns + alternate keys + lookup relationships (see **dv-metadata**).
2. Import Level 0 (no FK deps) tables in parallel via `ThreadPoolExecutor`. Sequential chunks within each table (concurrent writes deadlock).
3. Build source-ID → GUID maps by querying back (upsert doesn't return GUIDs).
4. Repeat per dependency level — Level 1 needs Level 0's maps for `@odata.bind`.

For the full pattern — adaptive `bulk_upsert` helper, composite-key handling, post-import verification, and the first-time `bulk_create` variant — see [`references/multi-table-fk-import.md`](references/multi-table-fk-import.md).

Key invariants (apply even without reading the reference):

- **Parallelize across tables at the same level**, sequential between levels, sequential chunks within a table.
- **Alternate key columns must NOT also appear in the record body** — `UpsertMultiple` fails.
- **Catch per-table failures** in the executor — one table failing must not kill the others.
- Start `chunk_size=1000`; the helper ramps up adaptively.

## Error Handling

```python
from PowerPlatform.Dataverse.core.errors import HttpError

try:
    guid = client.records.create("new_ticket", {"new_name": "Test"})
except HttpError as e:
    print(f"Status {e.status_code}: {e.message}")
    if e.details:
        print(f"Details: {e.details}")
    # 400 — bad field name, @odata.bind format, or missing required field
    # 403 — check security roles
    # 404 — table or record not found
    # 429 — rate limited; SDK retries automatically, reduce batch size if persistent
```

---

## Writing ERP data

On ERP-linked envs, writes to ERP entities do not go through the Python SDK. See [`references/erp-writes.md`](references/erp-writes.md).

---

## Windows Scripting Notes

- **ASCII only** in `.py` files — curly quotes and em dashes cause `SyntaxError` on Windows.
- **No `python -c` for multiline code** — write a `.py` file instead.
- **Generate GUIDs in scripts**: `str(uuid.uuid4())`, not shell backtick substitution.

---

## Sample Data Generation

Generate realistic sample records inline — schema-driven, table-agnostic, PII-safe defaults (`@example.com` emails, `555-01xx` phones).

**Quick reference:** confirm environment + count + table → query `EntityDefinitions(LogicalName='<table>')/Attributes?$filter=AttributeOf eq null` for required columns → dispatch by `AttributeType` (String / Memo / Integer / DateTime / Picklist / etc.) → `client.records.create()` (use `CreateMultiple` for count >= 10).

For the schema-driven `fake()` template, the `EntityDefinitions` query, and the safety rules, see [`references/sample-data-generation.md`](references/sample-data-generation.md).

Key invariants:
- Skip Lookup, Uniqueidentifier, State, Status, Owner, Customer fields unless the user explicitly provides values.
- `UserLocalizedLabel` may be null — dereference safely.

### Confirmation-flow examples

**Generate N sample records (destructive — preview the snippet, ask for env):**
- ❌ "Which environment should I target? Please provide the Dataverse URL."
- ✅ "I'll run the Sample Data Generation snippets with `TABLE=\"contact\"`, `COUNT=20`. Uses `CreateMultiple`, `.example.com` emails, `555-01xx` phones, against the active `pac auth list` environment. Confirm to proceed, or specify a different environment."

**Sample data on a custom entity (schema unknown — prose is enough):**
- ❌ "I need more info about the entity. What are the required fields?"
- ✅ "Custom entity — I'll query `EntityDefinitions` for `cr123_project` to discover required columns, then generate 5 records inline mapping each column to a generator by `AttributeType` and call `client.records.create(\"cr123_project\", records)`. Confirm to proceed, or tell me a different count."

Referenced files: 3

View saved version →

---
name: dv-metadata
description: Dataverse schema authoring and inspection — tables, columns, relationships, forms, and views. Use when the user wants to define, evolve, or inspect the data model — add a column, create a table, set up a lookup, customize a form, build a view, or list existing columns and relationships.
---

# Skill: Metadata — Making Changes

**Before the first metadata change in a session:**
1. **Confirm the target environment** with the user — see the Multi-Environment Rule in dv-overview.
2. **Confirm the solution** — ask "What solution should these components go into?" If `SOLUTION_NAME` is in `.env`, confirm it. If no solution exists yet, **you MUST ask the user** for the solution name and publisher prefix before creating anything. The publisher prefix is **permanent** — it cannot be changed after components are created with it.

**STOP and ask the user:**
> "What solution name and publisher prefix should I use? The prefix (e.g., `contoso`, `lit`, `soc`) is permanent on every table and column."

Then query existing publishers and show them — the user may want to reuse one:

```python
# Publisher discovery + solution creation — use SDK (never raw Web API).
# See dv-solution for the full publisher discovery flow.
publishers = client.records.list("publisher",
    filter="customizationprefix ne 'none' and uniquename ne 'MicrosoftCorporation'",
    select=["publisherid", "uniquename", "friendlyname", "customizationprefix"], top=10)
# MANDATORY: Show existing publishers to user and ask which to use or create new
```

After user confirms, create using SDK:

```python
publisher_id = client.records.create("publisher", {
    "uniquename": "<name>", "friendlyname": "<display>",
    "customizationprefix": "<prefix>",  # from user input, NOT hardcoded
    "description": "<desc>",
})
solution_id = client.records.create("solution", {
    "uniquename": "<SolutionName>", "friendlyname": "<Display Name>",
    "version": "1.0.0.0",
    "publisherid@odata.bind": f"/publishers({publisher_id})",
})
```

Never create tables or columns outside a solution.

3. Pass `solution="<UniqueName>"` in every SDK call, or include `"MSCRM.SolutionName": "<UniqueName>"` on every raw Web API call.

## Skill boundaries

| Need | Use instead |
|---|---|
| Create, update, or delete data records | **dv-data** |
| Query or read records | **dv-query** |
| Export or deploy solutions | **dv-solution** |
| ERP schema | see erp-target.md |

---

## How Changes Are Made: Environment-First

**Do not write solution XML by hand to create new tables, columns, forms, or views.**

The environment validates metadata far more reliably than an agent editing XML. The correct workflow is:

1. **Make the change in the environment** via the Dataverse MetadataService API (or `pac` commands where available)
2. **Pull the change into the repo** via `pac solution export` + `pac solution unpack`
3. **Commit the result**

The exported XML is generated by Dataverse itself and is always valid. Hand-written XML is fragile — a single incorrect attribute or missing element causes an import failure with an opaque error.

The only time you write files directly is when editing something that already exists in the repo (e.g., tweaking an existing view's columns or modifying a form layout you've already pulled).

**CLI schema inspection:** `dataverse data describe --table account --include all --json` (uses LogicalName). API discovery: `dataverse api list --target dataverse`.

---

## Creating a Table

**If creating multiple tables for a data import**, also see these sections later in this skill:
- **Idempotent Table Creation** — check-first pattern for re-runnable scripts
- **Alternate Keys** — required for upsert; create immediately after each table
- **Metadata Propagation Delays and Lock Contention** — phased creation to avoid lock errors

**Prefer the SDK for table creation** — use raw Web API only when you need full control over OwnershipType, HasActivities, or other advanced properties the SDK doesn't expose.

**SDK approach (use this by default):**

```python
import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_client

# get_client sets a plugin attribution context on the User-Agent header.
# Do not modify the context value — it is a closed schema for server-side
# telemetry (app/skill/agent). Never include secrets or PII.
client = get_client("dv-metadata")

info = client.tables.create(
    "new_ProjectBudget",
    {"new_Amount": "decimal", "new_Description": "string"},
    solution="MySolution",
    primary_column="new_Name",
    display_name="Project Budget",  # human-readable name; plural auto-appends "s"
)
print(f"Created: {info['table_schema_name']}")
```

**Web API fallback (ONLY when you need OwnershipType, HasActivities, or other properties the SDK doesn't expose):**

```python
# Helper for Label boilerplate
def label(text):
    return {"@odata.type": "Microsoft.Dynamics.CRM.Label",
            "LocalizedLabels": [{"@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                                  "Label": text, "LanguageCode": 1033}]}

entity = {
    "@odata.type": "Microsoft.Dynamics.CRM.EntityMetadata",
    "SchemaName": "new_ProjectBudget",
    "DisplayName": label("Project Budget"),
    "DisplayCollectionName": label("Project Budgets"),
    "Description": label(""),
    "OwnershipType": "UserOwned",
    "HasActivities": False, "HasNotes": False, "IsActivity": False,
    "PrimaryNameAttribute": "new_name",
    "Attributes": [{
        "@odata.type": "Microsoft.Dynamics.CRM.StringAttributeMetadata",
        "SchemaName": "new_name",
        "DisplayName": label("Name"),
        "RequiredLevel": {"Value": "ApplicationRequired"},
        "MaxLength": 100, "IsPrimaryName": True,
    }]
}
# POST to /api/data/v9.2/EntityDefinitions with MSCRM.SolutionUniqueName header
```

---

## Column Naming: Avoid `*Id` Suffix Collisions

**Never name a regular column with an `Id` suffix** (e.g., `prefix_CountryId`). Dataverse auto-generates a navigation property with the `Id` suffix when you create a lookup — if a regular column with that name exists, lookup creation fails with a schema name collision.

- WRONG: `prefix_DepartmentId` (int) — collides with auto-generated lookup
- RIGHT: `prefix_SrcDepartmentId` or `prefix_DepartmentSourceId`

---

## Adding Columns

**SDK approach (preferred):**

```python
created = client.tables.add_columns(
    "new_ProjectBudget",
    {"new_Description": "string", "new_Amount": "decimal", "new_Active": "bool"},
)
print(created)  # ['new_Description', 'new_Amount', 'new_Active']
```

Supported type strings: `"string"` / `"text"`, `"int"` / `"integer"`, `"decimal"` / `"money"`, `"float"` / `"double"`, `"datetime"` / `"date"`, `"bool"` / `"boolean"`, `"file"`, and `Enum` subclasses (for local option sets).

**Choice (picklist) column via SDK:**

```python
from enum import IntEnum

class BudgetStatus(IntEnum):
    DRAFT = 100000000
    APPROVED = 100000001
    REJECTED = 100000002

created = client.tables.add_columns(
    "new_ProjectBudget",
    {"new_Status": BudgetStatus},
)
```

**Web API approach (needed for column types the SDK doesn't support — e.g., currency with precision, memo with custom max length):**

```python
# Currency column
attribute = {
    "@odata.type": "Microsoft.Dynamics.CRM.MoneyAttributeMetadata",
    "SchemaName": "new_amount",
    "DisplayName": {"@odata.type": "Microsoft.Dynamics.CRM.Label",
                    "LocalizedLabels": [{"@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                                          "Label": "Amount", "LanguageCode": 1033}]},
    "RequiredLevel": {"Value": "None"},
    "MinValue": 0,
    "MaxValue": 1000000000,
    "Precision": 2,
    "PrecisionSource": 2
}
# POST to /api/data/v9.2/EntityDefinitions(LogicalName='new_projectbudget')/Attributes
```

---

## Lookup Columns and Relationships

**SDK approach — simple lookup (preferred):**

```python
result = client.tables.create_lookup_field(
    referencing_table="new_projectbudget",
    lookup_field_name="new_AccountId",
    referenced_table="account",
    display_name="Account",
    solution="MySolution",
)
print(f"Created lookup: {result.lookup_schema_name}")
```

**SDK approach — full control over 1:N relationship:**

```python
from PowerPlatform.Dataverse.models.relationship import (
    LookupAttributeMetadata,
    OneToManyRelationshipMetadata,
    CascadeConfiguration,
)
from PowerPlatform.Dataverse.models.labels import Label, LocalizedLabel
from PowerPlatform.Dataverse.common.constants import CASCADE_BEHAVIOR_REMOVE_LINK

lookup = LookupAttributeMetadata(
    schema_name="new_AccountId",
    display_name=Label(localized_labels=[LocalizedLabel(label="Account", language_code=1033)]),
)

relationship = OneToManyRelationshipMetadata(
    schema_name="account_new_projectbudget",
    referenced_entity="account",
    referencing_entity="new_projectbudget",
    referenced_attribute="accountid",
    cascade_configuration=CascadeConfiguration(delete=CASCADE_BEHAVIOR_REMOVE_LINK),
)

result = client.tables.create_one_to_many_relationship(lookup, relationship, solution="MySolution")
print(f"Created: {result.relationship_schema_name}")
```

**SDK approach — many-to-many relationship:**

```python
from PowerPlatform.Dataverse.models.relationship import ManyToManyRelationshipMetadata

relationship = ManyToManyRelationshipMetadata(
    schema_name="new_ticket_knowledgebase",
    entity1_logical_name="new_ticket",
    entity2_logical_name="new_knowledgebase",
)

result = client.tables.create_many_to_many_relationship(relationship, solution="MySolution")
print(f"Created: {result.relationship_schema_name}")
```

**Web API approach (fallback when SDK patterns don't suffice):**

```python
relationship = {
    "@odata.type": "Microsoft.Dynamics.CRM.OneToManyRelationshipMetadata",
    "SchemaName": "account_new_projectbudget",
    "ReferencedEntity": "account",
    "ReferencingEntity": "new_projectbudget",
    "Lookup": {
        "@odata.type": "Microsoft.Dynamics.CRM.LookupAttributeMetadata",
        "SchemaName": "new_AccountId",
        "DisplayName": {"@odata.type": "Microsoft.Dynamics.CRM.Label",
                        "LocalizedLabels": [{"@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                                              "Label": "Account", "LanguageCode": 1033}]},
        "RequiredLevel": {"Value": "None"}
    }
}
# POST to /api/data/v9.2/RelationshipDefinitions
```

**After creating a lookup — the @odata.bind navigation property:**

When you create records that set this lookup, you need the **navigation property name** for `@odata.bind`. The navigation property name is case-sensitive and must match the entity's `$metadata` (usually the SchemaName of the lookup field, e.g., `new_AccountId`):

| Navigation Property Name | `@odata.bind` key | Entity set |
|---|---|---|
| `new_AccountId` | `new_AccountId@odata.bind` | `/accounts(<guid>)` |
| `new_ParentTicketId` | `new_ParentTicketId@odata.bind` | `/new_tickets(<guid>)` |

**Common mistake:** Using the logical name (lowercase) like `new_accountid@odata.bind` returns a 400 error. Navigation property names are case-sensitive and must match the entity's `$metadata`.

---

## Adding a Table to a Solution

After creating a table via API, add it to your solution so it gets pulled on export:
```
pac solution add-solution-component \
  --solutionUniqueName <SOLUTION_NAME> \
  --component <SchemaName> \
  --componentType 1 \
  --environment <url>
```
Component type `1` = Entity (Table). See dv-solution for the full type code list.

Or via Web API:
```python
# POST to /api/data/v9.2/AddSolutionComponent
body = {
    "ComponentId": "<entity-metadata-id>",
    "ComponentType": 1,       # 1 = Entity
    "SolutionUniqueName": "<SOLUTION_NAME>",
    "AddRequiredComponents": True
}
```

---

## Forms and Views

`systemform` (forms) and `savedquery` (views) are **ordinary entities** — create/read/modify them with the **SDK's record CRUD** (no `urllib`). Only **publishing** (`PublishXml`, an unbound action) needs `dataverse api request`.

**Quick reference:**
- **Create form:** `client.records.create("systemform", {...formxml..., "type": 2})` (`2`=Main, `7`=Quick Create, `6`=Quick View, `11`=Card).
- **Modify form:** `records.list("systemform", filter=...)` for a template → mutate `formxml` → `records.update("systemform", id, {...})` → publish.
- **Publish:** `dataverse api request` POST `PublishXml` — required for changes to take effect.
- **Create view:** `client.records.create("savedquery", {...fetchxml..., ...layoutxml...})` (`0`=standard, `1`=advanced find, `2`=associated, `4`=quick find).

Full code, the template recipe, the `classid` table, and publish: [`references/forms-and-views.md`](references/forms-and-views.md).

Key invariants:
- All `id` attributes in form XML must be unique GUIDs (`str(uuid.uuid4()).upper()`).
- Do not use `python -c` for GUID generation on Windows — write a `.py` file.
- Forms must be published after every create or modify, otherwise changes are invisible to users.

---

## Business Rules

Create business rules in the Power Apps maker portal. They are too complex to write reliably as JSON/XAML. After creation, export+unpack the solution and commit the result.

---

## Publisher Prefix

All custom schema names must use your solution's publisher prefix (e.g., `new_`, `contoso_`). Find yours:
```
pac solution list --environment <url>
```
Or check `solutions/<SOLUTION_NAME>/Other/Solution.xml` after the first pull — look for `<CustomizationPrefix>`.

---

## FormXml Pitfalls

- **All `id` attributes must be valid GUIDs** in `{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}` format. Do not use strings like `"general"`.
- **`labelid` is also a GUID** — not a human-readable string.
- **Subgrid controls require a valid `<ViewId>`** — must be the GUID of an existing SavedQuery. Create the view first.
- **Cell, section, tab, and control IDs must all be unique** across the entire form.
- **Control `classid` values** — see the classid table above.

**Tip:** Create forms in the maker portal and pull via `pac solution export` — use the pulled XML as a template for programmatic creation.

---

## After Creating Columns: Report Logical Names

After creating columns (via Web API or MCP), **always report the actual logical names** to the user. Column names may be normalized or prefixed in ways the user doesn't expect. Summarize in a table:

| Display Name | Logical Name | Type |
|---|---|---|
| Email | cr9ac_email | String |
| Tier | cr9ac_tier | Picklist |
| Customer | cr9ac_customerid | Lookup |

This prevents downstream failures when the user tries to insert data using incorrect column names.

---

## Common Web API Error Codes

| Error Code | Meaning | Recovery |
|---|---|---|
| `0x80040216` | Transient metadata cache error. Column or table metadata not yet propagated. | Wait 3-5 seconds and retry. Usually succeeds on second attempt. |
| `0x80048d19` | Invalid property in payload. A field name doesn't match any column on the table. | Check logical column names — use `EntityDefinitions(LogicalName='...')/Attributes` to verify. |
| `0x80040237` | Schema name already exists. | Verify the column/table exists before creating a new one — it may have been created by a previous timed-out call. |
| `0x8004431a` | Publisher prefix mismatch. | Ensure all schema names use the solution's publisher prefix. |
| `0x80060891` | Metadata cache not ready after table creation. | Call `GET EntityDefinitions(LogicalName='...')` first to force cache refresh, then retry. |

Always translate error codes to plain English before presenting them to the user.

---

## Metadata Propagation Delays and Lock Contention

After creating tables / columns / alternate keys, Dataverse runs internal metadata operations (index build, cache propagation) for 3–30 seconds. Submitting another metadata operation while these run causes lock-contention errors.

**Mitigation — phased creation, not interleaved.** Create ALL tables → wait 15–30s → create ALL alternate keys → wait 15–30s → create ALL lookups. Do NOT interleave operations on the same table.

**Symptoms** (any of these means propagation isn't done):
- Picklist column creation fails with `0x80040216`
- Lookup `@odata.bind` fails with "Invalid property"
- `update_table` (MCP) fails with "EntityId not found in MetadataCache"
- Lookup or alternate-key creation fails with "another customization operation is running"

For the `retry_metadata` helper that catches transient lock errors and the full phased-creation sequence, see [`references/metadata-propagation.md`](references/metadata-propagation.md).

## Inspect Existing Schema

Before changing a model, inspect what already exists. These read-only calls return raw
metadata dictionaries (PascalCase property names) and are safe to run anytime.

> Assumes `client` from the auth setup shown earlier in this skill (`from auth import get_client`).

```python
# Columns on a table (optionally filtered / projected)
columns = client.tables.list_columns("account", select=["LogicalName", "AttributeType", "SchemaName"])
for col in columns:
    print(f"{col['LogicalName']} ({col.get('AttributeType')})")

# All relationships for one table (1:N, N:1, and N:N combined)
rels = client.tables.list_table_relationships("account")
for rel in rels:
    print(f"{rel['SchemaName']} -> {rel.get('@odata.type')}")

# All relationships in the environment (optionally filtered)
all_rels = client.tables.list_relationships(select=["SchemaName", "ReferencedEntity", "ReferencingEntity"])
```

For SQL-queryable column discovery (virtual/computed columns excluded), `dv-query` also has
`client.query.sql_columns(table)`.

## Session Closing: Pull to Repo

**After every metadata session, perform the pull-to-repo sequence** — see dv-overview "After Any Change: Pull to Repo" for the full export/unpack/commit commands.

If you used the `MSCRM.SolutionName` header during creation, verify components were added before exporting:
```python
sol = client.records.list("solution",
    filter="uniquename eq '<SOLUTION_NAME>'", select=["solutionid"], top=1).first()
if sol is not None:
    components = client.records.list("solutioncomponent",
        filter=f"_solutionid_value eq {sol['solutionid']}",
        select=["componenttype", "objectid"])
    print(f"{len(components)} components in the solution")
```

---

## Idempotent Table Creation

When creating tables programmatically (e.g., a schema setup script that may be re-run), use a check-first pattern — query `client.tables.get()` before creating. This is explicit, avoids masking unrelated errors, and lets you branch logic based on whether the table was created or reused:

```python
def ensure_table(client, schema_name, columns, solution, primary_column="prefix_Name", display_name=None):
    existing = client.tables.get(schema_name)
    if existing:
        print(f"Reusing: {schema_name}")
        return existing
    info = client.tables.create(schema_name, columns, solution=solution,
                                primary_column=primary_column, display_name=display_name)
    print(f"Created: {info['table_schema_name']}")
    return info
```

---

## Alternate Keys (Required for Upsert)

`UpsertMultiple` requires an alternate key on the column(s) Dataverse should use to identify existing records. Always create alternate keys on source-system ID columns (`prefix_Src*Id`) at schema-setup time so every import is idempotent.

**Quick reference:**
- SDK call: `client.tables.create_alternate_key(table, key_name, [columns], display_name=...)`. Composite keys: pass multiple columns.
- Use a check-first pattern with `client.tables.get_alternate_keys(table)` to skip keys that already exist — see `references/alternate-keys.md` for the `ensure_alternate_key` helper.
- Index creation is **async** — for large tables, poll `client.tables.get_alternate_keys(table)` until `status == "Active"` before using.
- Constraints: max 16 columns / 900 bytes / 10 keys per table; valid types are Integer / Decimal / String / DateTime / Lookup / OptionSet.

For SDK code samples (single + composite + idempotent + status-check), the agent decision rules for which column to pick (DB source vs Excel/CSV), and the failure-handling notes, see [`references/alternate-keys.md`](references/alternate-keys.md).

## EntityDefinitions Filter Limitation

**`startswith()` is NOT supported as a filter on `EntityDefinitions`.** This query will return a 400 error:

```
GET /api/data/v9.2/EntityDefinitions?$filter=startswith(LogicalName,'new_')  # BROKEN
```

To retrieve metadata for multiple custom tables, query each table individually:
```python
GET /api/data/v9.2/EntityDefinitions(LogicalName='new_projectbudget')?$select=LogicalName,EntitySetName
```

Or query all and filter client-side:
```python
GET /api/data/v9.2/EntityDefinitions?$select=LogicalName,EntitySetName
```

---

## MCP Table Creation Notes

When using MCP `create_table` or `update_table`:

- **Column types.** MCP handles most types (text, numeric, boolean, datetime, choice/multiselect, lookup/customer, file/image); global option sets, N:N, alternate keys, forms, views need SDK/Web API.
- **Timeouts / cache delays.** Creation may report a timeout or stale cache; always `describe('tables/{name}')` before retrying or a follow-up `update_table` (if the table exists, skip creation).
- **Self-referential lookups** (Parent → same table) are added via `update_table` after creation.

Referenced files: 3

View saved version →

---
name: dv-overview
description: Foundational cross-cutting context for Dataverse / Power Platform work — scope and the skill map, the tool-capability reference, the safety rules, and the safe change lifecycle. Use when the user mentions Dataverse, Dynamics 365, Power Platform, CRM, or ERP; load this first for orientation. Specialist skills self-route via their own frontmatter triggers.
---

# Skill: Overview — What to Use and When

Load this skill first for any Dataverse work — it holds the cross-cutting context every task needs: scope, the tool-capability reference, the hard rules, and the change lifecycle. It does **not** route; the agent auto-selects specialist skills via their own WHEN/DO NOT USE WHEN frontmatter triggers. Users describe what they want in plain English; the agent chains skills automatically and never asks the user to name a skill or command.

---

## What This Plugin Covers

Dataverse / Power Platform work for **every persona** — builders and agent devs, data scientists, environment admins, and business users — delivered by specialist skills. The agent loads and routes to these automatically via their frontmatter triggers — you never invoke them by name.

| Area | Skill |
| --- | --- |
| Connect, authenticate, configure MCP, verify the environment | `dv-connect` |
| Schema — tables, columns, relationships, forms, views; inspect existing schema | `dv-metadata` |
| Data writes — record CRUD, bulk create/update/upsert, CSV/FK-ordered import, sample data | `dv-data` |
| Data reads & analytics — OData queries, QueryBuilder, FetchXML (aggregation + N:N joins), DataFrames | `dv-query` |
| Solution ALM — create, export, import, pack/unpack, post-import validation | `dv-solution` |
| Environment administration — bulk delete, retention/archival, org & OrgDB settings, recycle bin | `dv-admin` |
| Security & access — roles, users, application users, business units, self-elevation (PAC CLI) | `dv-security` |

**Model-driven apps:** the building blocks (tables, forms, views) are covered by `dv-metadata`; composing the app shell itself — site map and navigation — is **not yet** a first-class skill.

**Out of scope:**

- **Canvas apps** — a different technology; use `pac canvas` or the maker portal
- **Power Automate flows** — use the maker portal or the Power Automate Management API
- **Azure infrastructure** beyond what's needed for service-principal setup
- **Business Central** or other Dynamics products

---

## Hard Rules

Safety rules (init, auth, env confirmation) are non-negotiable. Tool selection (Rules 1, 2, 4) is capability-based.

### 0. Check Init State First

Before writing ANY code or creating ANY files, **actively search your callable tools for any tool whose name or description contains `dataverse`** (tools may be registered under environment-specific names like `mcp__dataverse_<orgid>__read_query`, not just generic names). **If any Dataverse MCP tool is found, use it directly — skip the init check and all setup.** MCP auth is host-managed and does not need `.env` or `scripts/auth.py`. Never declare MCP unavailable based solely on the initially displayed tool list.

If no MCP tool is found, check for an existing CLI profile — it's the fastest path for data operations:

```bash
dataverse auth who
```

If that shows an active profile with an environment URL, use CLI directly for data operations (see `dv-query`/`dv-data` examples) — no `.env`, `auth.py`, or workspace setup needed. For explicit "connect" or "set up" requests, run `dv-connect` regardless — it configures MCP, SDK, and PAC.

If no CLI profile exists, check workspace init:

```bash
ls .env scripts/auth.py 2>/dev/null
```

- If BOTH exist: proceed to the task.
- If EITHER is missing: run `python <plugin-scripts>/auth.py --ping`. If it prints `REACHABLE` (exit 0), the workspace is bootable without pip -- confirm the URL and proceed. If `--ping` fails, **run `dv-connect`**.

### 1. Python for scripting; the CLIs and MCP are first-class

Python is the language for automation **logic** (transformation, control flow, retry, CSV). The toolchain (`scripts/auth.py`, the SDK, skill examples) is Python-based. But MCP tools, the Dataverse CLI (`dataverse`), the Python SDK, and the PAC CLI (`pac`) are all **first-class tool invocations** — use whichever fits. The Dataverse CLI has the same standing as `pac`, which is invoked freely across the solution and metadata skills.

**NEVER:**
- Write automation *logic* in JavaScript/TypeScript/Node.js (`npm`, `yarn`, `pnpm`, `package.json`, `node_modules/`)
- Use `@azure/msal-node`, `@azure/identity`, or any Node.js Azure SDK
- Implement a bespoke MSAL / device-code flow — auth is `scripts/auth.py`, `pac auth`, and the Dataverse CLI

**ALWAYS:**
- Use `pip install` and the Python SDK (`PowerPlatform-Dataverse-Client`) for data and schema logic
- Use `scripts/auth.py` for tokens/credentials; `azure-identity` (Python) for Azure credential flows
- Treat the Dataverse CLI (`dataverse`) and `pac` as allowed first-party CLIs

### 2. Pick the surface that fits — capability awareness, not a fixed order

No mandated tool order. Each surface has a capability profile; pick what fits the job and the surface you are already in — soft defaults, not a required sequence. The full matrix is in **Tool Capabilities** below; the principles:

- Prefer a managed surface (MCP, the Dataverse CLI, or the SDK) over hand-rolled raw OData — they carry auth, paging, retry, and geo routing that raw HTTP re-implements. When MCP can't handle it (bulk >25 records, large reads, advanced schema like forms/views/N:N relationships/global option sets/alternate keys, multi-step workflows, analytics, or MCP isn't available), the **Python SDK** is the default.
- **Raw Web API is the last-resort escape hatch** for surfaces with no managed path (unbound actions like `PublishXml`, global option sets, anything without a first-class SDK/CLI command) — and even then prefer `dataverse api` (managed auth, exit codes) over hand-rolled `urllib`/`get_token`. Forms/views are **not** raw-only (SDK `records.create`/`update` on `systemform`/`savedquery`; only `PublishXml` needs `dataverse api`). Aggregation/N:N joins aren't raw-only either: `client.query.fetchxml()` (aggregates + link-entity), or the CLI's `data associate` for N:N writes.
- If an SDK method fails or a PAC command seems missing, check the relevant skill before hand-rolling raw HTTP.

**Field casing:** `$select`/`$filter` use lowercase logical names (`new_name`). `$expand` and `@odata.bind` use Navigation Property Names that are case-sensitive and must match `$metadata` (e.g., `new_AccountId`). Getting this wrong causes 400 errors. **SDK record payloads:** provide the correct SchemaName casing on `@odata.bind` keys (e.g., `new_AccountId@odata.bind`); the SDK does not auto-correct wrong casing. **Raw Web API calls** (forms, views, metadata): casing is entirely manual — a lowercase `new_accountid@odata.bind` will 400.

**Publisher prefix:** Never hardcode a prefix (especially `new`); query existing publishers and ask the user. The prefix is permanent. See the solution skill's publisher discovery flow.

### 3. Use Documented Auth Patterns

Three entry points, one shared sign-in:
- **`dataverse auth create`** (Dataverse CLI) writes a shared MSAL token cache. That sign-in serves CLI, MCP proxy, **and** `scripts/auth.py` via `msal-extensions`.
- **`scripts/auth.py`** is the Python/SDK auth entry point. Order: service principal → shared CLI cache → device-code. Use `get_client(skill)` (SDK) or `get_plugin_headers(skill, get_token())` (raw Web API) — both stamp attribution.
- **`pac auth create`** (PAC CLI) authenticates `pac` for `dv-solution` and `dv-admin`.

**Telemetry attribution (keep it deterministic):** every request carries a closed-schema `app=dataverse-skills/<ver>;skill=<skill>;agent=<agent>` context so the server sees which skill routed each OData call. It is baked in — `get_client(skill)` and `get_plugin_headers(skill, ...)` stamp it on the SDK and raw-HTTP paths; the Dataverse CLI auto-stamps `DataverseCli/<ver>` + the command, and you add the skill with `--context "app=dataverse-skills/<ver>;skill=<skill>;agent=<agent>"` (the CLI wraps it in parentheses itself — do not pre-wrap). Never modify, omit, or free-form this context — it is a closed schema (allowlisted skill/agent, no PII).

**NEVER:**
- Read or parse raw token cache files (e.g., `tokencache_msalv3.dat`) — reuse the cache only through `scripts/auth.py` / `msal-extensions`
- Implement your own MSAL device-code flow
- Hard-code tokens or credentials in scripts
- Invent a new auth mechanism

If auth is expired or missing, re-run `dataverse auth create` (or `pac auth create`), or check `.env`. See the `dv-connect` skill.

### 4. Be honest about gaps — don't hallucinate

Each skill documents a tested sequence — follow it when it fits. The skills are the source of truth for the supported, non-deprecated API. If a call fails with `AttributeError`, the installed SDK version may not have it — check the skill's version note and use the documented alternative.

**The honesty guard:** if you hit a gap the skills don't cover, say so and suggest a workaround. **Do not hallucinate an unsupported path** — do not invent a method, parameter, or endpoint that isn't documented. If unsure, say so.

**Connectivity is not auth.** A `login.microsoftonline.com` token can succeed while the org's data-plane domain is unreachable (restricted-egress hosts like ChatGPT Work Mode). Never report a count or result you didn't get from a real call that returned — verify with `python scripts/auth.py --check`; if it fails, say "unreachable," never a fabricated number. On a constrained host, lead with the SDK, not CLI/MCP. See `dv-connect/references/headless-hosts.md`.

---

## Tool Capabilities — Which Tool for Which Job

Understanding the real limits of each tool prevents hallucinated paths. This is the one piece of context no individual skill owns.

| Tool | Use for | Does NOT support |
| --- | --- | --- |
| **MCP Server** | Data CRUD (create/read/update/delete records, batch up to 25 per call), table create/update/delete + column add (incl. local choice/multiselect + lookup/customer), schema + record inspection via `describe`, metadata search (`search`), data + file-content search (`search_data`, when Dataverse search is enabled), file upload/download | Forms, Views, **global** Option Sets, **N:N** relationships, alternate keys, Solutions (lookup + local choice/multiselect columns **are** supported via `create_table`/`update_table`). **Note:** table creation may timeout but still succeed — always `describe` (e.g. `describe('tables/{name}')`) before retrying. Run queries sequentially (parallel calls timeout). Column names with spaces normalize to underscores (e.g., `"Specialty Area"` → `cr9ac_specialty_area`). **SQL (`read_query`):** supports `JOIN`, `GROUP BY` (COUNT/SUM/AVG/MIN/MAX), `TOP`, `WHERE`, `ORDER BY`; does NOT support `DISTINCT`, `HAVING`, subqueries, `OFFSET`, `UNION`, `CASE`/`IF`, `CAST`/`CONVERT`, CTE, or date functions. For those, use `client.query.sql()` (also allows `DISTINCT`, <5K rows), `$apply`, or a builder->DataFrame with pandas — see `dv-query`. **Bulk:** MCP `create_record`/`update_record`/`delete_record` batch up to 25 records per call; for larger bulk use the SDK `CreateMultiple` — see `dv-data`. |
| **Python SDK (`dv-data`)** | Scripted data writes, especially at volume. Record CRUD, upsert (alternate keys), bulk create/update/upsert (CreateMultiple/UpdateMultiple/UpsertMultiple), CSV import with lookup resolution, file column uploads (chunked >128MB) | global Option Sets, record association (`$ref`), `$apply` aggregation, table/column/relationship creation (use `dv-metadata`), custom action invocation |
| **Python SDK (`dv-query`)** | Bulk reads and analytics. Multi-page record iteration, OData queries (select/filter/expand/orderby), QueryBuilder fluent API, GUID-free display (formatted values), `$expand` to resolve lookups, **aggregation and N:N joins via `client.query.fetchxml()`** (aggregate FetchXML + link-entity), pandas DataFrame handoff (`client.query.builder(...).execute().to_dataframe()`) for exports, Jupyter notebook snippets | OData `$apply` and N:N `$expand` on the **QueryBuilder** path — use `records.list(expand=...)` for N:N, or `fetchxml()` for aggregates (not raw `urllib`) |
| **Dataverse CLI (`dataverse`)** | Headless data plane: `data` CRUD, `associate`/`disassociate` (N:N + `$ref`), `data upload`; `api request`/`invoke` (Web API escape hatch); `api list`/`describe` (Custom API discovery) | Metadata/schema (use SDK — `dv-metadata`), solution ALM (use PAC), forms/views; **blocked on ChatGPT web / Codex cloud** (no .NET runtime) — use the SDK |
| **PAC CLI** | Solution export/import/pack/unpack, environment create/list/delete/reset, auth profile management, plugin updates (`pac plugin push` — first-time registration requires Web API), user/role assignment (`pac admin assign-user`), add solution components (`pac solution add-solution-component`) | Data CRUD, metadata creation (tables/columns/forms), listing solution components (no `list-components` — query `solutioncomponent` via SDK/CLI) |
| **Azure CLI** | App registrations, service principals, credential management | Dataverse-specific operations |
| **GitHub CLI** | Repo management, GitHub secrets, Actions workflow status | Dataverse-specific operations |
| **Raw Web API** (last resort) | Only when **no** managed surface exposes the operation — i.e. not doable via MCP, the Python SDK, the Dataverse CLI, or the `dataverse api` escape hatch. Genuine cases: unbound actions like `PublishXml`, global option sets, and similar edge cases (**not** forms/views — those are SDK record CRUD on `systemform`/`savedquery`). Even then, prefer `dataverse api` (managed auth + skill attribution) over hand-rolled `urllib`. | Functionally nothing (full OData/MetadataService) — but raw `urllib` bypasses managed auth, paging, retry, and skill attribution, so treat it as the path of last resort |

**Routing:** the table shows what each surface does; the *how to choose* principle (soft defaults, not a fixed order) is Hard Rule 2. MCP tools not in your list? Load `dv-connect`.

**Volume guidance:** CLI `dataverse data create/query/count` for one-off commands; MCP for up to ~25 records per call or simple filters; the SDK's `CreateMultiple` for larger bulk writes (chunk large sets starting ~1,000 — see `dv-data`) and `dv-query` for bulk reads; Web API for `$apply` aggregation.

**SDK method cheat-sheet** (anti-hallucination, *not* a preference signal): SDK method names are the least discoverable surface, so agents invent them. This maps common ops to the exact call. Each op is equally reachable via MCP/CLI per Hard Rule 2; see the noted skill for the full pattern.

| Operation | SDK call | Skill |
| --- | --- | --- |
| Create / update / delete records | `client.records.create()` / `.update()` / `.delete()` (pass a list for bulk) | `dv-data` |
| Upsert on an alternate key | `client.records.upsert()` | `dv-data` |
| Query / filter records | `client.records.list(...)` (flat) or `.list_pages(...)` (streaming) | `dv-query` |
| One record by GUID | `client.records.retrieve(table, guid)` (`None` if missing) | `dv-query` |
| Aggregation / server-side joins | `client.query.fetchxml(xml)` (aggregates + link-entity) | `dv-query` |
| Fluent query build (chainable) | `client.query.builder(Table).where(...).execute()` | `dv-query` |
| Limited SQL read | `client.query.sql("SELECT ...")` | `dv-query` |
| Load into pandas | `client.query.builder(table).select(...).execute().to_dataframe()` | `dv-query` |
| Upload to a file column | `client.files.upload(...)` | `dv-data` |
| Create tables / columns / lookups / N:N | `client.tables.create()` / `.add_columns()` / `.create_lookup_field()` / `.create_many_to_many_relationship()` | `dv-metadata` |
| Create an alternate key (enables upsert) | `client.tables.create_alternate_key(...)` | `dv-metadata` |
| Inspect existing schema | `client.tables.list_columns(table)` / `.list_table_relationships(table)` | `dv-metadata` |
| Create publisher / solution | `client.records.create("publisher" / "solution", {...})` | `dv-solution` |

### MCP Availability Check

If the user's request involves MCP — explicitly or implicitly — search your callable tools for any tool whose name or description contains `dataverse` (same search as Hard Rule 0).

**If MCP NOT available and user explicitly asked for MCP** ("use MCP to query"):
1. **Do NOT silently fall back** to the Python SDK or Web API
2. Tell the user: "Dataverse MCP tools aren't configured in this session yet."
3. Load `dv-connect` to set up the MCP server
4. After MCP is configured, **stop** — the session must restart for MCP tools to appear. Do not proceed with SDK.

**If MCP NOT available and user asked a data question** ("how many accounts?"):
1. Use the CLI (if profile exists) or SDK to answer. Do not block the user.
2. After answering, offer: "MCP would handle this conversationally — want me to set it up?"

The distinction matters: explicit MCP request → block and set up MCP; implicit question → answer with SDK, offer MCP setup.

**If MCP tools ARE available**, prefer MCP for simple reads/queries/small CRUD. Use the SDK only when a script is needed.

---

## The Change Lifecycle — Operate Safely

For any real change, walk these three steps in order: confirm **where**, confirm the **container**, then persist the **result**.

### Step 1 — Confirm the Environment (MANDATORY)

Dataverse work often spans multiple environments (dev, test, staging, prod) and multiple sets of credentials. **Never assume** the active PAC auth profile, values in `.env`, or anything from memory or a previous session reflects the correct target for the current task.

**Before the FIRST operation that touches a specific environment** — creating a table, deploying a plugin, pushing a solution, inserting data — you MUST:

1. Show the user the environment URL you intend to use
2. Ask them to confirm it is correct
3. Run `pac org who` to verify the active connection matches

> "I'm about to make changes to `<URL>`. Is this the correct target environment?"

**Do not proceed until the user explicitly confirms.** This is the single most important safety check in the plugin. Skipping it risks making irreversible changes to the wrong environment. Once confirmed for a session, you do not need to re-confirm for every subsequent operation in the same session against the same environment.

### Step 2 — Confirm the Solution (before any metadata change)

Before creating tables, columns, or other metadata, ensure a solution exists to contain the work:

1. Ask the user: "What solution should these components go into?"
2. If a solution name is in `.env` (`SOLUTION_NAME`), confirm it with the user
3. If no solution exists yet, **load the `dv-solution` skill** and follow its publisher discovery + solution creation flow. Use the SDK — **never raw Web API** — to create publisher and solution records:

```python
# Quick reference — full pattern with publisher discovery is in dv-solution
publisher_id = client.records.create("publisher", {
    "uniquename": "<name>", "friendlyname": "<display>",
    "customizationprefix": "<prefix>", "description": "<desc>",
})
solution_id = client.records.create("solution", {
    "uniquename": "<Name>", "friendlyname": "<Display>",
    "version": "1.0.0.0",
    "publisherid@odata.bind": f"/publishers({publisher_id})",
})
```

4. Pass `solution="<UniqueName>"` on all SDK calls, or include `"MSCRM.SolutionName": "<UniqueName>"` header on raw Web API metadata calls.

Creating metadata without a solution means it exists only in the default solution and cannot be cleanly exported or deployed. Always solution-first.

### Step 3 — Pull to Repo (MANDATORY)

Any time you make a metadata change (via MCP, Web API, or the maker portal), **you must** end the session by pulling:

```bash
pac solution export --name <SOLUTION_NAME> --path ./solutions/<SOLUTION_NAME>.zip --managed false
pac solution unpack --zipfile ./solutions/<SOLUTION_NAME>.zip --folder ./solutions/<SOLUTION_NAME>
rm ./solutions/<SOLUTION_NAME>.zip
git add ./solutions/<SOLUTION_NAME>
git commit -m "feat: <description>"
git push
```

The repo is always the source of truth.

---

## Scripts

The plugin ships `scripts/auth.py` (Azure Identity token/credential acquisition — used by all other scripts and the SDK). Any Web API call beyond a one-off query should be a Python script committed to `/scripts/`, using `scripts/auth.py` for tokens. For writes see `dv-data`; queries and analytics see `dv-query`; post-import validation see `dv-solution`.

---

## Windows Scripting

Platform-specific shell rules (ASCII in `.py`, no multiline `python -c`, PAC PowerShell wrapper, unbuffered background output) live in [`references/windows-scripting.md`](references/windows-scripting.md). Read it when running on Windows.

Referenced files: 2

View saved version →

---
name: dv-query
description: Bulk reads, multi-page iteration, and analytics over Dataverse data. Use when the user wants to read, list, filter, aggregate, group, join, or analyze records — including pandas DataFrame workflows and notebook exploration.
---

# Skill: Query — Read and Analyze Dataverse Records

> **This skill uses Python and the Dataverse CLI.** Do not use Node.js, JavaScript, or any other language for Dataverse scripting. See the overview skill's Hard Rules.

## Reads: prefer a managed surface, choose by shape

**Fast path for simple reads:** If `dataverse auth who` shows an active profile, skip workspace setup and query directly with the CLI examples below. No `.env`, `auth.py`, pip install, or PAC needed for data reads.

Pick **MCP, the Dataverse CLI, or the SDK by the shape of the read** — all three handle auth and retry (see the routing table below and the overview's **Tool Capabilities** / Hard Rule 2). MCP fits small, interactive reads; the CLI fits headless one-liners (OData, SQL, count); the SDK fits bulk iteration and analytics. For `$apply` aggregation and N:N `$expand`, prefer `client.query.fetchxml()` (aggregates + link-entity) or the managed `dataverse api` escape hatch; reach for hand-rolled `urllib`/`get_token()` **only** to stay in-process inside a tight Python loop (e.g. paging thousands of rows with client-side processing — see web-api-advanced.md).

### Dataverse CLI gotchas (custom tables + Windows)

When you drive the `dataverse` CLI directly (headless reads/CRUD; note the CLI needs .NET + a keyring, so it is blocked on ChatGPT web / Codex cloud — use the SDK there), two empirical traps:

- **Custom-table SQL pluralization.** `dataverse data query` in SQL mode auto-pluralizes the table name, and irregular plurals resolve wrong: `FROM im_category` looks up entity set `im_categorys` and returns a **404** that reads like "table missing." It is not — switch to OData mode with the explicit entity set: `dataverse data query --table im_categories --select im_name`. Discover the real `EntitySetName` from `EntityDefinitions` when unsure; never conclude the table doesn't exist from this 404.
- **Windows shell quoting.** Wrap the whole `--path` value in double quotes so `cmd.exe`/PowerShell don't treat `&` as a command separator. Keep `&` **literal** — it separates OData query options; encoding it to `%26` merges them and breaks the query. Encode only `$`->`%24` (in PowerShell a bare `$select` is read as a variable). If an unquoted `&` splits the command, the wrapper can exit nonzero *even when the API returned valid JSON* — quoting prevents it. (This is why the `dataverse api request` examples in other skills quote the path, use `%24`, and leave `&` literal.)

### Dataverse CLI query examples (copy-paste ready)

All `dataverse` commands take `--context` for skill attribution (global flag).

```bash
# OData filtered read (--table takes the EntitySet name, e.g. accounts not account)
dataverse data query --table accounts --select "name,accountid" --filter "name eq 'john'" --top 10 --json --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>"

# Count records
dataverse data count --table accounts --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>"

# SQL mode (uses the logical name, e.g. account not accounts)
dataverse data query --sql "SELECT name, accountid FROM account WHERE name LIKE '%john%'" --json --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>"

# Get single record by ID
dataverse data get --table accounts --id <guid> --json --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>"

# Raw API escape hatch
dataverse api request --target dataverse --path "/api/data/v9.2/accounts?%24select=name&%24top=5" --context "app=dataverse-skills/<ver>;skill=dv-query;agent=<agent>"
```

**ERP target is a separate path.** ERP (Finance and Operations), when linked to a Dataverse env, does not go through the Python SDK. See [`references/erp-reads.md`](references/erp-reads.md).

## How to Answer Data Questions

When the user asks a question about their data, pick the approach by **what they're asking**, not by which API you know:

| User asks... | Approach | Why |
|---|---|---|
| "show me open tickets" / simple filter | **MCP** `read_query`, **CLI** `dataverse data query --table ... --filter ...`, or `client.records.list(table, filter=...)` | Small result, no aggregation |
| "how many X" / simple count | **CLI** `dataverse data count --table ...`, **MCP** `read_query`, or `client.query.sql("SELECT COUNT(*) ...")` | Server-side count (no row download) |
| Single-table aggregation (most/sum/avg/top-N) | **`$apply`** (raw) or **`client.query.sql()`** GROUP BY | Both run server-side, return only grouped results |
| Cross-table aggregation | **`client.query.sql("...INNER JOIN...GROUP BY...")`** or **`client.query.fetchxml(...)`** (server-side); else builder->DataFrame + `pd.merge()` | `sql()` supports INNER/LEFT JOIN + GROUP BY; pandas merge for shapes SQL can't express |
| "show me X with related Y" / resolve lookups | `client.records.list(table, expand=...)` or **QueryBuilder** | Lookup resolution |
| "export this data" / bulk extract | **`client.query.builder(t).select(...).execute().to_dataframe()`** | Direct to DataFrame → CSV |
| "load into notebook" / interactive analysis | **`client.query.builder(t).select(...).execute().to_dataframe()`** | pandas native |
| "find duplicates" / complex filter | `client.records.list(table, filter=...)` or **QueryBuilder** | SDK handles pagination |
| Simple filtered read (<5K rows) | **CLI** `dataverse data query --sql "SELECT ..."`, or **`client.query.sql()`** | Lightweight single call |

**Key principle:** Let the server do the work. For single-table aggregation, use `$apply` (raw) or `client.query.sql()` GROUP BY — both run server-side and return only grouped results. For cross-table questions, prefer a server-side `sql()` JOIN (INNER/LEFT) or `fetchxml()` link-entity; when SQL can't express it, pull each table via `client.query.builder(t).select(...).execute().to_dataframe()` and `pd.merge()` — the merge is sub-second; the bottleneck is network transfer, which `select` minimizes.

**Always query the live Dataverse environment.** Do not query local copies, cached files, or source databases when the user expects results from Dataverse. The data in Dataverse is the source of truth.

---

## SQL Queries — `client.query.sql()`

`client.query.sql()` uses the Dataverse Web API `?sql=` parameter — a **T-SQL subset**. It **supports** `SELECT` / `SELECT DISTINCT` / `SELECT TOP N` (0-5000), `INNER JOIN` / `LEFT JOIN`, `WHERE`, `GROUP BY`, `ORDER BY`, `OFFSET`/`FETCH`, and `COUNT/SUM/AVG/MIN/MAX`. It does **NOT** support `SELECT *`, subqueries, CTEs, `HAVING`, `UNION`, `RIGHT`/`FULL`/`CROSS JOIN`, `CASE`, or string/date/math functions. Results are capped at ~5,000 rows.

**When to use:** Fast filtered reads on tables with <5K rows. For these, it's significantly faster (~2-6s) than page iteration or DataFrames because it's a single HTTP call.

```python
# Fast filtered read on small tables (<5K rows)
results = client.query.sql(
    "SELECT TOP 100 name, estimatedvalue "
    "FROM opportunity "
    "WHERE statecode = 0 "
    "ORDER BY estimatedvalue DESC"
)
for r in results:
    print(f"{r['name']}: ${r.get('estimatedvalue', 0):,.0f}")
```

**Do NOT use for:** Tables >5K rows (results silently truncated), `SELECT *`, subqueries/CTEs, `HAVING`, `UNION`, `RIGHT`/`FULL`/`CROSS JOIN`, or functions. `INNER`/`LEFT JOIN` and `GROUP BY` **are** supported — use them for server-side joins/aggregation on <5K-row results; for larger or unsupported shapes use `fetchxml()` or `$apply`.

## FetchXML — server-side joins and aggregates

For SQL-JOIN scenarios or aggregates the OData builder cannot express, use FetchXML. `client.query.fetchxml(xml)` returns an inert query object — no HTTP is made until you call `.execute()` (eager, all pages) or `.execute_pages()` (lazy, one page at a time). Both return `QueryResult` pages with `.to_dataframe()`.

```python
query = client.query.fetchxml("""
  <fetch top="50">
    <entity name="account">
      <attribute name="name" />
      <link-entity name="contact" from="parentcustomerid" to="accountid" alias="c" link-type="inner">
        <attribute name="fullname" />
      </link-entity>
    </entity>
  </fetch>
""")

result = query.execute()          # collect all pages
df = result.to_dataframe()

# Or stream one page at a time for large results:
for page in query.execute_pages():
    print(page.to_dataframe().shape)
```

## Discover queryable columns — `client.query.sql_columns()`

Before writing a SQL or `$select` read, list the columns the SQL endpoint can actually query — virtual and computed lookup-display columns are excluded. Each entry has `name`, `type`, `is_pk`, `is_name`, and `label`.

```python
for c in client.query.sql_columns("account"):
    print(f"{c['name']:30s} {c['type']:20s} PK={c['is_pk']}")
```

For deeper schema inspection — full column metadata and table relationships — use `dv-metadata`
(`client.tables.list_columns()`, `client.tables.list_relationships()`,
`client.tables.list_table_relationships()`).

## Skill boundaries

| Need | Use instead |
|---|---|
| Create, update, delete records (Dataverse) | **dv-data** |
| Query, create, update, delete records (ERP) | See [`references/erp-reads.md`](references/erp-reads.md) and [`erp-writes`](../dv-data/references/erp-writes.md) |
| Create tables, columns, relationships | **dv-metadata** |
| Export or deploy solutions | **dv-solution** |

---

## Setup

```python
import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_client

# get_client sets a plugin attribution context on the User-Agent header.
# Do not modify the context value — it is a closed schema for server-side
# telemetry (app/skill/agent). Never include secrets or PII.
client = get_client("dv-query")
```

`get_client(skill)` handles auth, environment URL, and plugin attribution (User-Agent tagging). See `scripts/auth.py`. For scripts that run to completion, wrap the returned client in a `with` statement for automatic connection cleanup. For ERP, use ERP MCP or the Dataverse CLI `--target erp` path — see [`references/erp-reads.md`](references/erp-reads.md).

---

## Field Name Casing Rule

Getting this wrong causes 400 errors.

| Property type | Convention | Example | When used |
|---|---|---|---|
| **Structural** (columns) | LogicalName — always lowercase | `new_name`, `new_priority` | `$select`, `$filter`, `$orderby` |
| **Navigation** (lookups) | Navigation Property Name — case-sensitive, matches `$metadata` | `new_AccountId` | `$expand` |

- System table navigation properties (e.g., `parentaccountid`, `ownerid`): lowercase
- Custom lookup navigation properties: case-sensitive, match `$metadata` SchemaName (e.g., `new_AccountId`)

---

## Query Records

`client.records.list()` is the primary read method on the GA SDK. It collects all pages and returns a flat `QueryResult` you iterate directly (records, not pages). For very large result sets, `client.records.list_pages()` streams one `QueryResult` per HTTP page. **Always use `select=` to limit columns.**

```python
# list() -- flat QueryResult, iterate records directly
result = client.records.list(
    "new_ticket",
    select=["new_name", "new_priority", "new_status"],
    filter="new_status eq 100000000",
    orderby=["new_name asc"],
    top=50,
)
for r in result:
    print(r["new_name"], r["new_priority"])

print(f"{len(result)} tickets")   # QueryResult supports len(), indexing, .first(), .to_dataframe()
```

For large tables where you do not want every row in memory at once, stream pages:

```python
for page in client.records.list_pages("new_ticket", select=["new_name"], page_size=200):
    for r in page:          # each page is a QueryResult
        print(r["new_name"])
```

Each record is a `Record` object that supports dict-like access: `r["column"]`, `r.get("column")`, `r.keys()`. Do not use `r.data.get()` -- use `r.get()` directly.

> **Migrating from `records.get()`:** `records.get()` is deprecated on the GA SDK. Replace `for page in client.records.get(...): for r in page:` with `for r in client.records.list(...):` (flat), or keep the page loop using `list_pages(...)`. Replace a by-GUID `records.get(table, guid)` with `records.retrieve(table, guid)` (returns `None` if not found).

---

## Fetch a Single Record by ID

`client.records.retrieve()` returns the record, or `None` if no row has that GUID (no exception on 404).

```python
record = client.records.retrieve("new_ticket", "<record-guid>",
    select=["new_name", "new_priority", "new_status"])
if record is not None:
    print(record["new_name"])
else:
    print("Ticket not found")
```

---

## $select with Lookup Columns (GUID-free display)

To show display names instead of GUIDs, request the formatted value annotation via `include_annotations`:

```python
for r in client.records.list("opportunity",
    select=["name", "estimatedvalue", "_parentaccountid_value"],
    include_annotations="OData.Community.Display.V1.FormattedValue",
):
    account_name = r.get("_parentaccountid_value@OData.Community.Display.V1.FormattedValue")
    print(f"{r['name']} — {account_name}")
```

**You MUST pass `include_annotations`** — without it, the `Prefer: odata.include-annotations` header is not sent and formatted values are not in the response. Use `"*"` for all annotations or the specific annotation name above.

Formatted values are available for lookup, choice, status, and owner fields.

---

## $expand — Resolve Lookup to Full Related Record

```python
for r in client.records.list("opportunity",
    select=["name", "estimatedvalue"],
    expand=["parentaccountid($select=name)"],   # nested $select avoids fetching all account columns
):
    account = r.get("parentaccountid") or {}
    print(f"{r['name']} — {account.get('name', 'Unknown')}")
```

Always use nested `$select` inside `$expand` — without it, Dataverse returns every column on the related entity, which wastes bandwidth and memory.

### $expand with multiple custom lookups

```python
for r in client.records.list(
    "new_ticket",
    select=["new_name", "new_priority", "new_status"],
    expand=["new_CustomerId($select=new_name)", "new_AgentId($select=new_name)"],  # nested $select + case-sensitive nav props
):
    customer = r.get("new_CustomerId") or {}
    agent    = r.get("new_AgentId") or {}
    print(f"{r['new_name']} | {customer.get('new_name','')} | {agent.get('new_name','')}")
```

> `expand` uses the Navigation Property Name (`new_CustomerId`), not the lowercase logical name (`new_customerid`). Using lowercase causes a 400 error.

---

## Advanced query patterns (raw Web API)

`$apply` aggregation and N:N `$expand` on the OData path are raw-only. Note the SDK **does** cover most aggregation/joins — `client.query.sql()` (INNER/LEFT JOIN, GROUP BY, COUNT/SUM/AVG) and `client.query.fetchxml()` (aggregate + link-entity). Reach for raw Web API only for the `$apply` transform and N:N `$expand`. See [`references/web-api-advanced.md`](references/web-api-advanced.md) for full code samples.

**Quick reference:**
- **`$expand` on N:N relationships:** `GET /<entitySet>?$expand=<n:n_nav>($select=...)` — single page only; follow `@odata.nextLink` for >5,000 results.
- **`$apply` for aggregations:** runs server-side, returns grouped results in one call. Patterns: `groupby((col),aggregate(metric with sum as total))`, `aggregate($count as count)`, `aggregate(amount with average as avg)`. 50K source-record limit.
- **Cross-table aggregation:** `$apply` only works within one entity set. Prefer `client.query.sql()` (INNER/LEFT JOIN + GROUP BY) or `fetchxml()` link-entity; else pull each table via `client.query.builder(t).select(...).execute().to_dataframe()` → `pd.merge()` → `groupby()`. Always pass `select`; without it transfers 10-20x more data.

## QueryBuilder — Fluent Query API

Chainable builder for complex queries that would be awkward as a single OData URL or FetchXML string. Full reference and examples in [`references/querybuilder.md`](references/querybuilder.md).

## Jupyter Notebook Setup

For interactive querying in notebooks (auth + DataverseClient + DataFrame display), see [`references/jupyter-setup.md`](references/jupyter-setup.md).

## Querying ERP data

On ERP-linked envs, ERP reads do not go through `DataverseClient`. Use ERP MCP or `dataverse data query/get/count --target erp`. See [`references/erp-reads.md`](references/erp-reads.md).

## Common Query Errors

| Status | Cause | Fix |
|---|---|---|
| 400 | Wrong field casing in `$select`/`$filter` (must be lowercase LogicalName) or `$expand` (must be case-sensitive Navigation Property Name) | Verify names via `EntityDefinitions(LogicalName='...')/Attributes` |
| 400 | Unsupported SQL — MCP `read_query` rejects DISTINCT/HAVING/subqueries/OFFSET/UNION/CAST/CONVERT/CASE/date-functions (but **allows** JOIN + GROUP BY); `client.query.sql()` rejects `SELECT *`/subqueries/CTE/HAVING/UNION/RIGHT/FULL/CROSS JOIN/functions (but **allows** INNER/LEFT JOIN, GROUP BY, DISTINCT) | Use `fetchxml()`/`$apply` for shapes `sql()` can't express, or pandas for cross-table |
| 404 | Table logical name not found | Check spelling — use `client.tables.get("<name>")` to verify |
| 429 | Rate limited | SDK retries automatically; reduce page size or add delays between pages |

For `HttpError` handling in SDK scripts, see the error handling pattern in **dv-data**.

---

## Windows Scripting Notes

- **ASCII only** in `.py` files — curly quotes and em dashes cause `SyntaxError` on Windows.
- **No `python -c` for multiline code** — write a `.py` file instead.
- **Generate GUIDs in scripts**: `str(uuid.uuid4())`, not shell backtick substitution.

Referenced files: 4

View saved version →

---
name: dv-security
description: Security-role assignment, user access, application users, business units, and admin self-elevation in Dataverse environments. Use when the user wants to give someone access, grant a role, become an admin, or add a service principal.
---

# Skill: Security — Role Assignment and Self-Elevation

**This skill uses first-party CLIs — PAC CLI for role changes, Dataverse CLI to verify.** Do NOT write Python scripts for role operations.

## Preview Before Running

Role grants and self-elevate are destructive (they change security posture and are logged to Purview). Before running, preview the action in plain prose — target user, role, environment(s) — using placeholders (`<ENV_URL>`, `<USER_EMAIL>`) for anything unknown, and ask for confirmation and missing values in the same turn. Skip the raw `pac admin` block; the user shouldn't have to read CLI syntax to approve a security change.

**Key principle:** the user should be able to evaluate what's about to happen from your first response. A bare *"which environment?"* fails that test; a one-line prose preview passes it.

### Examples

**Assign role (user given, env missing):**
- ❌ "Which environment should I target?"
- ✅ "I'll assign **System Administrator** to `user@contoso.com` on `<ENV_URL>`. Confirm to proceed and provide the target environment URL (or 'all' to list and batch)."

**Admin access across all environments:**
- ❌ "Please provide your email address."
- ✅ "I'll list your environments, then assign **System Administrator** in parallel on each one for `<YOUR_UPN>`. If `assign-user` fails on any environment, I'll fall back to self-elevate (logged to Purview) for that one. Confirm to proceed and provide your UPN."

## Skill boundaries

| Need | Use instead |
|---|---|
| Create or modify tables, columns, relationships | **dv-metadata** |
| Manage org settings, audit, bulk delete, retention | **dv-admin** |
| Query or read records | **dv-query** |
| Write, update, or delete records | **dv-data** |
| Tenant-level governance (DLP, env lifecycle) | `pac admin --help` |

## Prerequisites

- PAC CLI installed and authenticated (`pac auth create`)
- System Administrator role in target environment (or Global/PP/D365 Admin for self-elevate)
- Active auth profile: `pac auth list`
- **Headless / restricted-egress hosts**: SDK handles role / user / business-unit ops; service principal for PAC-only ops; verify egress with `python scripts/auth.py --check`. See `dv-connect/references/headless-hosts.md`.

---

## Assign a Security Role to a User

```bash
pac admin assign-user --user <email-or-object-id> --role "System Administrator" --environment <url>
```

### Arguments

| Argument | Alias | Required | Description |
|----------|-------|----------|-------------|
| `--user` | `-u` | Yes | User email (UPN) or Azure AD object ID |
| `--role` | `-r` | Yes | Security role name (e.g., `System Administrator`, `Basic User`) |
| `--environment` | `-env` | Yes | Target environment URL or ID |
| `--application-user` | `-au` | No | Treat user as an application user (service principal) |
| `--business-unit` | `-bu` | No | Business unit ID. Defaults to the caller's business unit |

---

## Verify the assignment — exit code 0 is not proof

`pac admin assign-user` **exits 0 even when it fails** (unresolved environment, wrong role name, unknown user). Never treat a clean exit as success.

1. **Read the output, not just the exit code.** A failed run still exits 0 but prints an error (`environment ... not found`, `role ... does not exist`). Stop if the output contains an error.
2. **Confirm against the exact `--environment` you used** — do not re-resolve or shorten it; a different id silently "succeeds" on the wrong org. Query the user's roles with a Dataverse CLI read:

```bash
# Resolve the user's systemuserid, then list their assigned roles.
# --context carries plugin/skill/agent attribution on the managed CLI call.
dataverse api request --target dataverse --method GET \
  --path "/api/data/v9.2/systemusers?%24select=systemuserid&%24filter=internalemailaddress eq 'user@contoso.com'" \
  --environment <same-url-as-assign> \
  --context "app=dataverse-skills/<ver>;skill=dv-security;agent=<agent>"
dataverse api request --target dataverse --method GET \
  --path "/api/data/v9.2/systemusers(<systemuserid>)/systemuserroles_association?%24select=name" \
  --environment <same-url-as-assign> \
  --context "app=dataverse-skills/<ver>;skill=dv-security;agent=<agent>"
```

If the first query returns no row, the sign-in identity may live on `domainname` (the AAD UPN) rather than `internalemailaddress` (Primary Email) — retry with `%24filter=domainname eq '<upn>'`, or `azureactivedirectoryobjectid eq '<objectid>'` when you assigned by object id. A missing row is not proof the grant failed.

If the target role is absent, the assignment did not take — re-run, read the output, or fall back to self-elevate.

---

## Batch Workflow: Assign Role Across Multiple Environments

Run in parallel — never sequentially:

```
Step 1: pac admin list                                              -> Get all environments
Step 2: Filter by type if needed (e.g., Developer, Sandbox)        -> Identify targets
Step 3: Confirm with user — show list of target environments
Step 4: Run ALL assignments in a single bash call:
```

```bash
pac admin assign-user --user user@contoso.com --role "System Administrator" --environment https://dev1.crm.dynamics.com &
pac admin assign-user --user user@contoso.com --role "System Administrator" --environment https://dev2.crm.dynamics.com &
pac admin assign-user --user user@contoso.com --role "System Administrator" --environment https://dev3.crm.dynamics.com &
wait
```

```
Step 5: Verify each landed (exit 0 is not proof — see above), then report ("Assigned + verified on 3/3 environments")
```

**Important**: Always confirm which environments will be affected before assigning roles, and verify each assignment landed — a clean exit code does not prove success.

---

## Tenant Admin Self-Elevation (Fallback)

**Self-elevation is materially different from assigning a role to another user.** `pac admin assign-user <other>` grants privilege *to someone else*; `pac admin self-elevate` grants privilege *to the caller*. The risk profile and audit posture are different, so the confirmation protocol is stricter.

If `pac admin assign-user` fails with "user has not been assigned any roles", use:

```bash
pac admin self-elevate --environment https://myorg.crm.dynamics.com
```

- Requires Global Admin, Power Platform Admin, or Dynamics 365 Admin
- All elevations are logged to Microsoft Purview
- Uses the active auth profile if `--environment` is omitted

### Self-elevation confirmation protocol (stricter than assign-user)

Before running `pac admin self-elevate`, the agent MUST:

1. **State the risk explicitly.** Include this wording (or equivalent) in the pre-run summary:
   > "This grants YOU System Administrator on `<env>`. The action is logged to Microsoft Purview with your identity and timestamp."
2. **Capture a reason.** Ask for a one-line reason — ticket ID, incident number, or a free-form note such as `"dev sandbox access — no ticket"`. Echo the reason back in the pre-run summary so the user sees what will be on the record.
3. **Wait for an explicit confirmation AFTER the user has seen both (1) and (2).** Do NOT accept a bare "yes" given before the risk statement and reason are on screen.
4. **Do NOT silently fall back.** If `pac admin assign-user` fails, surface the failure first, then offer `self-elevate` with this protocol — never chain them automatically.

**Flow**: Always try `pac admin assign-user` first. `admin self-elevate` is the documented fallback, gated by the protocol above.

**CLI fallback**: If `pac admin self-elevate` errors out, self-elevate manually via **Power Platform Admin Center** → select the environment → **Access** → **System Administrator role**. All elevations are still logged to Purview. (In PAC CLI 2.6.4 the command fails with `bolt.authentication.http.AuthenticatedClientException` / `ApiVersionInvalid` because the CLI sends an empty `api-version=` to the backend.)

---

## Safety Rules

- **Always confirm** before assigning System Administrator role
- Show the list of target environments before batch operations
- Self-elevation is logged and auditable — warn the user

View saved version →

---
name: dv-solution
description: Dataverse solution lifecycle — create, export, import, promote across environments, and validate deployments. Use when the user wants to package customizations, deploy to another environment, or move work between dev / test / prod.
---

# Skill: Solution

Create, export, unpack, pack, import, and validate Dataverse solutions via PAC CLI. Includes post-import validation using the Python SDK.

> **Headless / restricted-egress hosts**: use the raw Web API (`ExportSolution` / `ImportSolution`) for the online steps. `pac solution pack`/`unpack` are local file operations (no auth) but need a host that can run PAC -- do them on a capable machine or CI runner. Verify egress with `python scripts/auth.py --check`. See `dv-connect/references/headless-hosts.md`.

## Skill boundaries

| Need | Use instead |
|---|---|
| Create tables, columns, relationships, forms, views | **dv-metadata** |
| Create, update, or delete data records | **dv-data** |
| Query or read records | **dv-query** |
| Connect to Dataverse / set up MCP | **dv-connect** |

---

## Create a New Solution

**Use the Python SDK for publisher and solution record creation — not raw HTTP.** Publishers and solutions are standard Dataverse tables. `client.records.create()` and `client.records.list()` handle auth, pagination, and error handling automatically, avoiding the URL encoding, header boilerplate, and GUID-parsing bugs that raw `urllib` calls introduce.

### Step 1: Find or Create the Publisher

Every solution belongs to a publisher. The publisher's `customizationprefix` (e.g., `contoso`, `sa`, `lit`) is prepended to every custom table, column, and relationship schema name. **This prefix is effectively permanent** — existing components keep their prefix forever, even if you change the publisher later.

**Never use the default `new` prefix.** It provides no organizational identity, risks naming collisions, and signals the developer did not follow best practices.

**Discovery flow — always run this before creating a publisher:**

```python
import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_client

# get_client sets a plugin attribution context on the User-Agent header.
# Do not modify the context value — it is a closed schema for server-side
# telemetry (app/skill/agent). Never include secrets or PII.
client = get_client("dv-solution")

# 1. Query for existing non-Microsoft publishers
publishers = client.records.list(
    "publisher",
    filter="customizationprefix ne 'none' and uniquename ne 'MicrosoftCorporation' and uniquename ne 'Microsoftdynamic'",
    select=["publisherid", "uniquename", "friendlyname", "customizationprefix"],
    top=10,
)

if publishers:
    # Show existing publishers and ask user which to use
    print("Existing publishers in this environment:")
    for p in publishers:
        print(f"  {p['uniquename']} (prefix: {p['customizationprefix']}_)")
    # ASK THE USER: "Which publisher should this solution use?"
    # Or: "Should I reuse '<name>' (prefix: <prefix>_)?"
    publisher_id = publishers[0]["publisherid"]  # after user confirms
else:
    # No custom publisher exists — ASK THE USER for prefix
    # "What publisher prefix should I use? (e.g., 'contoso', 'sa', 'lit' — 2-8 lowercase chars)"
    publisher_id = client.records.create("publisher", {
        "uniquename": "<publisheruniquename>",
        "friendlyname": "<Publisher Display Name>",
        "customizationprefix": "<prefix>",   # from user input, NOT 'new'
        "description": "<description>",
    })
```

**Rules:**
- **Always ask the user** before creating a new publisher or choosing a prefix. Never hardcode a prefix.
- The prefix must match any tables already created in the solution — you cannot mix prefixes.
- One publisher can own many solutions. Reuse an existing publisher when possible.

### Step 2: Create the Solution Record

Use the SDK to create the solution record (preferred over raw Web API):

```python
import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_client

# get_client sets a plugin attribution context on the User-Agent header.
# Do not modify the context value — it is a closed schema for server-side
# telemetry (app/skill/agent). Never include secrets or PII.
client = get_client("dv-solution")

# Create the solution record
solution_id = client.records.create("solution", {
    "uniquename": "<UniqueName>",
    "friendlyname": "<Display Name>",
    "version": "1.0.0.0",
    "publisherid@odata.bind": "/publishers(<publisher_guid>)",
})
print(f"Created solution: {solution_id}")
```

The required fields:
```
Table:  solution
Fields: uniquename    = "<UniqueName>"
        friendlyname  = "<Display Name>"
        version       = "1.0.0.0"
        publisherid   = <publisher GUID from step 1>
```

> **Note:** There is no `pac solution create` command. PAC CLI handles export/import/pack/unpack, not solution record creation. Use the SDK or Web API to create the record.

### Step 3: Add Components

Use `pac solution add-solution-component` to add tables, forms, views, and other components:
```
pac solution add-solution-component \
  --solutionUniqueName <UniqueName> \
  --component <ComponentSchemaName> \
  --componentType <TypeCode> \
  --environment <url>
```

> **Note:** PAC CLI uses camelCase args here (`--solutionUniqueName`, `--componentType`), not kebab-case.

Common component type codes:
| Type Code | Component |
|---|---|
| 1 | Entity (Table) |
| 2 | Attribute (Column) |
| 26 | View |
| 60 | Form |
| 61 | Web Resource |
| 300 | Canvas App |
| 371 | Connector |

Repeat the command for each component you need to add.

### Alternative: Auto-add via MSCRM.SolutionName Header

When creating metadata via the Web API, include the `MSCRM.SolutionName` header to auto-add components to the solution:
```python
headers = {
    "Authorization": f"Bearer {token}",
    "Content-Type": "application/json",
    "MSCRM.SolutionName": "<UniqueName>"
}
```

**Important:** After using this approach, verify components were added by querying the `solutioncomponent` table with the SDK (`pac solution list-components` is not available in current PAC):
```python
sol = client.records.list("solution",
    filter="uniquename eq '<UniqueName>'", select=["solutionid"], top=1).first()
if sol is not None:
    components = client.records.list("solutioncomponent",
        filter=f"_solutionid_value eq {sol['solutionid']}",
        select=["componenttype", "objectid"])
    print(f"{len(components)} components in the solution")
```

If the header was misspelled or the solution doesn't exist, components will be created in the default solution instead — silently. Always verify.

## Find the Solution Name

Before exporting, confirm the exact unique name:
```
pac solution list --environment <url>
```
The `UniqueName` column is what you pass to other commands. Display names have spaces; unique names do not.

## Pull: Export + Unpack

> **Confirm the target environment before exporting or importing.** Run `pac auth list` + `pac org who`, show the output to the user, and confirm it matches the intended environment. Developers work across multiple environments — do not assume.

Export the solution as unmanaged (source of truth):
```
pac solution export \
  --name <UniqueName> \
  --path ./solutions/<UniqueName>.zip \
  --managed false \
  --environment <url>
```

Unpack into editable source files:
```
pac solution unpack \
  --zipfile ./solutions/<UniqueName>.zip \
  --folder ./solutions/<UniqueName> \
  --packagetype Unmanaged
```

> **Windows file-lock race.** Run export and unpack as **separate** commands (as above); chaining them immediately can hit a transient ZIP file-lock right after export. If `unpack` fails with a lock / "in use" error, retry after a moment, and verify the unpacked folder has the expected components before deleting the zip.

Delete the zip — the unpacked folder is the source:
```
rm ./solutions/<UniqueName>.zip
```

Commit:
```
git add ./solutions/<UniqueName>
git commit -m "chore: pull <UniqueName> baseline"
git push
```

## Push: Pack + Import

Pack the source files back into a zip:
```
pac solution pack \
  --zipfile ./solutions/<UniqueName>.zip \
  --folder ./solutions/<UniqueName> \
  --packagetype Unmanaged
```

Import (async recommended for large solutions):
```
pac solution import \
  --path ./solutions/<UniqueName>.zip \
  --environment <url> \
  --async \
  --activate-plugins
```

## Poll Import Status

After async import, check the job:
```
pac solution list --environment <url>
```

## Post-Import Validation

After importing a solution, verify that components are live. Use the Python SDK to check directly — no external scripts needed.

### Check a table exists

```python
info = client.tables.get("<logical_name>")
if info:
    print(f"[PASS] Table '{info.logical_name}' exists")
else:
    print(f"[FAIL] Table '<logical_name>' not found")
```

### Check a form is published

```python
forms = client.records.list(
    "systemform",
    filter="objecttypecode eq '<entity>' and type eq <form_type_code>",
    select=["name", "formid"],
    top=5,
)
# Form type codes: 2 = main, 7 = quick create
```

### Check a view exists

```python
views = client.records.list(
    "savedquery",
    filter="returnedtypecode eq '<entity>'",
    select=["name", "savedqueryid", "statuscode"],
    top=10,
)
```

### Check a user's role assignment (N:N `$expand`)

`records.list` passes `$expand` straight through, so read the N:N navigation property directly with the SDK:

```python
users = list(client.records.list(
    "systemuser",
    filter="internalemailaddress eq '<email>'",   # fallback: domainname eq '<upn>'
    select=["fullname"],
    expand=["systemuserroles_association($select=name)"],
    top=1,
))
roles = [r["name"] for r in users[0].get("systemuserroles_association", [])] if users else []
```

Alternatively, the managed **Dataverse CLI** escape hatch (`dataverse api request` — not `urllib`), or FetchXML with a link-entity:

```bash
dataverse api request --target dataverse --method GET \
  --path "/api/data/v9.2/systemusers?%24filter=internalemailaddress eq '<email>'&%24select=fullname&%24expand=systemuserroles_association(%24select=name)&%24top=1" \
  --environment <DATAVERSE_URL> \
  --context "app=dataverse-skills/<ver>;skill=dv-solution;agent=<agent>"
```

The response `value[0].systemuserroles_association` is the list of assigned roles (each with `name`).

### Check import errors

```python
jobs = client.records.list(
    "importjob",
    select=["importjobid", "solutionname", "startedon", "completedon", "progress"],
    orderby=["startedon desc"],
    top=5,
)
```

For detailed error history, also query `msdyn_solutionhistory`:

```python
history = client.records.list(
    "msdyn_solutionhistory",
    filter="msdyn_status eq 1",  # 1 = failed
    select=["msdyn_name", "msdyn_starttime", "msdyn_exceptionmessage"],
    orderby=["msdyn_starttime desc"],
    top=5,
)
```

### Validation error reference

| Error | Cause | Fix |
| --- | --- | --- |
| Table not found after import | Component not in solution | Add via `pac solution add-solution-component` |
| Form check fails immediately | Publishing is async | Wait 30 seconds and retry |
| Role not assigned | User not provisioned | Assign the role via `pac admin assign-user` or the Power Platform Admin Center |
| Import job at 0% | Import still running | Poll again in 60 seconds |

## Notes

- Always use `--managed false` / `--packagetype Unmanaged` for the development solution. Managed packages are for deployment to downstream environments (test, prod).
- `--activate-plugins` ensures any registered plugins in the solution are activated on import.
- If you see "solution already exists" errors, use `--import-mode ForceUpgrade` to overwrite.
- Large solutions (Sales, Customer Service) can take 10–20 minutes to import. Be patient and poll rather than re-importing.
- All validation queries above require auth. Use `scripts/auth.py` for credential/token acquisition. See `dv-query` for SDK query patterns and `dv-data` for write patterns.