Dataverse Schema Alm

Dataverse schema + ALM via solutions

Overview

Two intertwined concerns: (1) designing Dataverse schema so it survives solution export/import cleanly, and (2) moving that schema plus its dependent components (flows, apps, connection references, environment variables) between environments without manual prompts. Get schema design wrong and solution layering fights you later. Get connection reference / environment variable mapping wrong and pipelines stall on interactive prompts or import silently with broken flows.

Core principle: every schema component you create belongs to exactly one solution/publisher for its lifetime, and every environment-specific value (a connection, a URL, a GUID) must be resolved through an environment variable or connection reference — never hardcoded — or ALM breaks the moment you leave dev.

When to use

• Creating or modifying tables, columns, relationships, choices, alternate keys, business rules, views, or forms
• Setting up a new solution, deciding managed vs. unmanaged, or organizing multiple solutions/publishers
• Building a pipeline (pac CLI, Power Platform Build Tools, GitHub Actions) to move a solution dev → test → prod
• Anything involving connection references or environment variables failing to resolve on import, or import prompting for values in an unattended pipeline

Schema design quick reference

| Component | Key rule | Gotcha | |---|---|---| | Table | Set publisher prefix before creating anything — it's permanent for that table | Can't move a managed table to a different publisher without delete + recreate (data loss) | | Column | Choose type carefully; type changes are limited after data exists | Choice vs. global choice: local choices can't be reused across tables, global choices can | | Relationship | 1:N owned by the "one" side; N:N creates an intersect table | Deleting a table deletes its relationships and any dependent forms/views that reference it | | Alternate key | Use for integration with external systems that lack the Dataverse GUID | Key indexing is asynchronous (Pending → Active) — don't rely on it immediately after creation; columns used in keys can't contain / # * % & : \ ? + if you'll use them in Web API GET/PATCH | | Business rule / view / form | Nested components — they exist only inside a table and travel with it in a solution | Editing a managed component creates a dependency layer; you must edit it via an unmanaged solution, not in place |

Full component list: Solution components.

Schema design decisions

These are the calls that are expensive to reverse once data or dependent components exist — think them through before creating anything, not after.

Alternate key vs. relying on the GUID. Default to the GUID; only add an alternate key when an external system needs to identify a row without knowing the Dataverse GUID (integration/upsert scenarios). Reasons to hesitate:

• Key index build is asynchronous (Pending → Active). Don't build integration logic that assumes the key is enforceable immediately after creation — a duplicate insert can slip through in the Pending window.
• If the key will be used in Web API GET/PATCH calls (alternate-key addressing in the URL), none of the key's columns can contain / # * % & : \ ? +. If you only need a uniqueness constraint and won't address rows by key in the URL, this restriction doesn't matter — don't let it drive column design unnecessarily.
• Alternate keys enforce uniqueness retroactively against existing data. Adding one to a table that already has duplicate values fails outright — dedupe first.

1:N vs. N:N vs. child table. Default to 1:N owned by the "one" side unless you specifically need many-to-many association (N:N, which silently creates an intersect table you'll rarely interact with directly) or the child rows have no independent existence/security context of their own (child table — IsChildEntity, inherits privileges from the parent, deleted automatically when the parent is deleted, never user/team owned). Picking N:N when a 1:N would do adds an invisible intersect table to every future solution-dependency and data-migration conversation; picking child table for something that later needs its own security role is a rebuild, not a patch.

Extending an out-of-box table (Account, Contact, etc.) vs. adding a new custom table. If you're customizing a system table, use table segmentation — add only the specific components you changed (e.g., one renamed column) to your solution, not the whole table. Adding the entire out-of-box table pulls in components already present in every target environment and creates unnecessary solution-layer conflicts on import/upgrade.

Choice vs. global choice. If more than one table/column will ever reuse the same option set, use a global choice from the start. Converting a local choice to global later means recreating the column — the option values don't migrate automatically.

Solution layering — the mental model

• Unmanaged layer: one shared layer per environment; all dev customization happens here. This is your source of truth — export it as unmanaged and check it into source control.
• Managed layers: stacked, last-installed-on-top. Each managed solution import is a build artifact, never hand-edited. Test/UAT/prod should contain managed solutions only.
• Never import a managed solution into the same environment that owns the originating unmanaged solution — you need a separate environment to test the managed package.
• Use one publisher per solution family and don't split tables across solutions that both "own" them — creates cross-solution dependency headaches on upgrade/delete.
• Rule of thumb: multiple solutions with dedicated dev environments (base solution → app solutions layered on top) for large multi-team projects; a single unmanaged solution per dev environment for small/medium projects.

Details: Solution concepts, Solution layers, Organize your solutions.

pac CLI workflow quick reference

| Goal | Command | |---|---| | Auth to an environment | pac auth create --url https://org.crm.dynamics.com --name dev | | Auth non-interactively (pipeline) | pac auth create --url --applicationId --clientSecret --tenant | | Export unmanaged (source of truth) | pac solution export --name MySolution --path .\out\MySolution.zip | | Export managed (deploy artifact) | pac solution export --name MySolution --managed --path .\out\MySolution_managed.zip | | Unpack for source control | pac solution unpack --zipfile .\out\MySolution.zip --folder .\src\MySolution | | Pack from source | pac solution pack --zipfile .\out\MySolution_managed.zip --folder .\src\MySolution --packagetype Managed | | Generate deployment settings template | pac solution create-settings --solution-zip .\out\MySolution.zip --settings-file .\deploymentSettings.json | | Import with settings (unattended) | pac solution import --path .\out\MySolution_managed.zip --settings-file .\deploymentSettings.json --async | | Clone (add new components to solution) | pac solution clone --name MySolution |

--managed is a switch, not --managed true — pac solution export --managed (no value). Getting this wrong is a common mistake when writing pipeline scripts from memory.

pac solution clone vs pac solution export: use clone when adding new components to a solution project (it produces a buildable .cdsproj folder); use export when you just need the current solution content as a zip, no new components. Full command list: pac solution reference.

Deployment settings, connection references, environment variables

This is the step that trips up unattended pipelines.

MANDATORY — read the entire file before generating or debugging a deployment settings JSON, a connection reference mapping, or an unattended solution import: deployment-settings-and-connection-references.md in this skill. It covers the settings JSON schema, exactly where ConnectionId values come from, environment variable data types (including the two that need extra setup — Secret and Data source), what must exist in the target environment before import, per-environment file layout for pipelines, and a symptom → root cause table for the failures this scenario produces. Do not answer a connection-reference or deployment-settings question from the summary below alone — the reference file has the specifics that prevent the two most common mistakes (wrong --managed flag syntax, assuming the pipeline can generate a ConnectionId).

One-line summary: connections are credentialed resources that must be created in the target environment out-of-band (a human or a script authenticates them) before an automated import — the import wires existing connections to connection references, it never creates or authenticates a connection itself.

Common mistakes

| Mistake | Fix | |---|---| | Hardcoding a URL or GUID that differs per environment | Use an environment variable, not a literal value in a flow/app | | Treating ConnectionId in deployment settings as something the pipeline can generate | Pre-create the connection in the target environment first; only then capture its GUID | | Editing a managed component directly in the target environment | Edit it in an unmanaged solution in dev, re-export, re-import | | Splitting the same table across two solutions with different publishers | Pick one publisher/solution as the table's owner; use table segmentation for extending out-of-box tables instead | | Assuming pac solution export --managed true works | It's a boolean switch: --managed present = managed, absent = unmanaged | | Storing environment variable secrets/values in the exported solution zip | Strip current values before export, or use a dedicated environment-variable-only solution kept out of source control for sensitive values |

Quick reference: connection reference vs environment variable

| | Connection reference | Environment variable | |---|---|---| | Points at | An authenticated connection (credentials) | A config value (text, number, JSON, secret, or data source) | | Used by | Solution-aware flows always; canvas apps only for non-OAuth/implicit-shared connections | Flows, canvas apps, plug-ins, model-driven Power BI tiles | | Created in target env before import? | Yes — the underlying connection must exist | No — value is supplied via deployment settings or the import UI prompt | | Enables flow auto-turn-on after import | Yes, if connection is owned/shared correctly | N/A |

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: RyanMakesAndBreaksStuff
• Source: RyanMakesAndBreaksStuff/Custom-Codex-Claude-Plugins-and-Skills
• License: MIT

Install and usage instructions live in the source repository linked above.