Agent skills
dataverse-create-schema

Agent skill

dataverse-create-schema

View SKILL.md on GitHub Repository

Stars 163

Forks 31

Install this agent skill to your Project

npx add-skill https://github.com/majiayu000/claude-skill-registry/tree/main/skills/data/dataverse-create-schema

SKILL.md

dataverse-create-schema

description: Create or update Dataverse schema components (entities, attributes, relationships, option sets) via Web API tags: [dataverse, schema, entities, attributes, relationships, powershell] techStack: [dataverse, power-platform, powershell, web-api] appliesTo: ["create entity", "add column", "create table", "dataverse schema", "add field", "create relationship"] alwaysApply: false

Purpose

Tier 1 Component Skill - Creates or updates Dataverse schema components programmatically using the Dataverse Web API and PowerShell. This skill provides patterns for:

Creating global option sets (choice columns)
Creating new entities (tables)
Adding attributes (columns) to entities
Creating lookup relationships (1:N)
Creating many-to-many relationships (N:N)
Extending existing entities with new fields

Why Web API Instead of PAC CLI?

PAC CLI (v1.46+) doesn't have direct pac table create or pac column create commands
Web API provides full control over all metadata properties
Idempotent scripts can safely re-run without errors
Supports all attribute types and relationship configurations

When to Use

User says "create Dataverse entity", "add column to table", "create relationship"
Task has tags: dataverse, schema, entity, table
Need to create/modify Dataverse schema programmatically
Deploying schema changes to multiple environments
Schema definition exists in design docs or POML task files

Prerequisites

1. Azure CLI Authentication

Required for obtaining Dataverse access tokens:

powershell

# Login to Azure (if not already)
az login

# Verify account
az account show

2. PAC CLI Authentication (for verification)

powershell

# Authenticate to target environment
pac auth create --environment https://spaarkedev1.crm.dynamics.com

# Verify connection
pac auth list

3. Target Environment URL

Standard environments:

Environment	URL
Dev	`https://spaarkedev1.crm.dynamics.com`

Core Patterns

Authentication and Headers

powershell

# Get OAuth token using Azure CLI
$Environment = "spaarkedev1.crm.dynamics.com"
$token = (az account get-access-token --resource "https://$Environment" --query accessToken -o tsv)

# Standard headers for Dataverse Web API
$headers = @{
    "Authorization" = "Bearer $token"
    "OData-MaxVersion" = "4.0"
    "OData-Version" = "4.0"
    "Content-Type" = "application/json"
    "Accept" = "application/json"
    "Prefer" = "return=representation"
}

$BaseUrl = "https://$Environment/api/data/v9.2"

API Helper Function

powershell

function Invoke-DataverseApi {
    param(
        [string]$Token,
        [string]$BaseUrl,
        [string]$Endpoint,
        [string]$Method = "GET",
        [object]$Body = $null
    )

    $uri = "$BaseUrl/$Endpoint"
    $headers = @{
        "Authorization" = "Bearer $Token"
        "OData-MaxVersion" = "4.0"
        "OData-Version" = "4.0"
        "Content-Type" = "application/json"
        "Accept" = "application/json"
    }

    $params = @{
        Uri = $uri
        Headers = $headers
        Method = $Method
    }

    if ($Body) {
        $params.Body = ($Body | ConvertTo-Json -Depth 20 -Compress)
    }

    return Invoke-RestMethod @params
}

Label Helper

powershell

function New-Label {
    param([string]$Text)
    return @{
        "@odata.type" = "Microsoft.Dynamics.CRM.Label"
        "LocalizedLabels" = @(
            @{
                "@odata.type" = "Microsoft.Dynamics.CRM.LocalizedLabel"
                "Label" = $Text
                "LanguageCode" = 1033  # English
            }
        )
    }
}

Attribute Type Definitions

String Attribute

powershell

function New-StringAttribute {
    param(
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$Description,
        [int]$MaxLength = 200,
        [bool]$Required = $false
    )
    return @{
        "@odata.type" = "Microsoft.Dynamics.CRM.StringAttributeMetadata"
        "SchemaName" = $SchemaName
        "RequiredLevel" = @{ "Value" = if ($Required) { "ApplicationRequired" } else { "None" } }
        "MaxLength" = $MaxLength
        "DisplayName" = New-Label -Text $DisplayName
        "Description" = New-Label -Text $Description
    }
}

Memo (Multiline Text) Attribute

powershell

function New-MemoAttribute {
    param(
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$Description,
        [int]$MaxLength = 100000
    )
    return @{
        "@odata.type" = "Microsoft.Dynamics.CRM.MemoAttributeMetadata"
        "SchemaName" = $SchemaName
        "RequiredLevel" = @{ "Value" = "None" }
        "MaxLength" = $MaxLength
        "DisplayName" = New-Label -Text $DisplayName
        "Description" = New-Label -Text $Description
    }
}

Integer Attribute

powershell

function New-IntegerAttribute {
    param(
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$Description,
        [bool]$Required = $false,
        [int]$MinValue = -2147483648,
        [int]$MaxValue = 2147483647
    )
    return @{
        "@odata.type" = "Microsoft.Dynamics.CRM.IntegerAttributeMetadata"
        "SchemaName" = $SchemaName
        "RequiredLevel" = @{ "Value" = if ($Required) { "ApplicationRequired" } else { "None" } }
        "DisplayName" = New-Label -Text $DisplayName
        "Description" = New-Label -Text $Description
        "MinValue" = $MinValue
        "MaxValue" = $MaxValue
    }
}

Boolean Attribute

Note: Boolean attributes require an OptionSet definition with TrueOption/FalseOption.

powershell

function New-BooleanAttribute {
    param(
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$Description
    )
    return @{
        "@odata.type" = "Microsoft.Dynamics.CRM.BooleanAttributeMetadata"
        "SchemaName" = $SchemaName
        "RequiredLevel" = @{ "Value" = "None" }
        "DisplayName" = New-Label -Text $DisplayName
        "Description" = New-Label -Text $Description
        "OptionSet" = @{
            "TrueOption" = @{
                "Value" = 1
                "Label" = New-Label -Text "Yes"
            }
            "FalseOption" = @{
                "Value" = 0
                "Label" = New-Label -Text "No"
            }
        }
    }
}

Picklist (Choice) Attribute with Global Option Set

powershell

function New-PicklistAttribute {
    param(
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$Description,
        [string]$GlobalOptionSetName,
        [bool]$Required = $false
    )
    return @{
        "@odata.type" = "Microsoft.Dynamics.CRM.PicklistAttributeMetadata"
        "SchemaName" = $SchemaName
        "RequiredLevel" = @{ "Value" = if ($Required) { "ApplicationRequired" } else { "None" } }
        "DisplayName" = New-Label -Text $DisplayName
        "Description" = New-Label -Text $Description
        "GlobalOptionSet@odata.bind" = "/GlobalOptionSetDefinitions(Name='$GlobalOptionSetName')"
    }
}

DateTime Attribute

powershell

function New-DateTimeAttribute {
    param(
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$Description
    )
    return @{
        "@odata.type" = "Microsoft.Dynamics.CRM.DateTimeAttributeMetadata"
        "SchemaName" = $SchemaName
        "RequiredLevel" = @{ "Value" = "None" }
        "DisplayName" = New-Label -Text $DisplayName
        "Description" = New-Label -Text $Description
        "Format" = "DateAndTime"
        "DateTimeBehavior" = @{ "Value" = "UserLocal" }
    }
}

Entity Operations

Create New Entity

powershell

function New-Entity {
    param(
        [string]$Token,
        [string]$BaseUrl,
        [string]$SchemaName,
        [string]$DisplayName,
        [string]$PluralName,
        [string]$Description,
        [bool]$IsAutoNumber = $false
    )

    $entityDef = @{
        "@odata.type" = "Microsoft.Dynamics.CRM.EntityMetadata"
        "SchemaName" = $SchemaName
        "DisplayName" = New-Label -Text $DisplayName
        "DisplayCollectionName" = New-Label -Text $PluralName
        "Description" = New-Label -Text $Description
        "OwnershipType" = "OrganizationOwned"
        "IsActivity" = $false
        "HasNotes" = $false
        "HasActivities" = $false
        "PrimaryNameAttribute" = "sprk_name"
        "Attributes" = @(
            @{
                "@odata.type" = "Microsoft.Dynamics.CRM.StringAttributeMetadata"
                "SchemaName" = "sprk_name"
                "RequiredLevel" = @{ "Value" = "ApplicationRequired" }
                "MaxLength" = 200
                "DisplayName" = New-Label -Text "Name"
                "Description" = New-Label -Text "Primary name field"
                "IsPrimaryName" = $true
                "AutoNumberFormat" = if ($IsAutoNumber) { "{SEQNUM:6}" } else { $null }
            }
        )
    }

    Invoke-DataverseApi -Token $Token -BaseUrl $BaseUrl -Endpoint "EntityDefinitions" -Method "POST" -Body $entityDef
    Write-Host "  Created entity: $SchemaName" -ForegroundColor Green
}

Add Attribute to Existing Entity

powershell

function Add-EntityAttribute {
    param(
        [string]$Token,
        [string]$BaseUrl,
        [string]$EntityLogicalName,
        [hashtable]$AttributeDef
    )

    $endpoint = "EntityDefinitions(LogicalName='$EntityLogicalName')/Attributes"
    Invoke-DataverseApi -Token $Token -BaseUrl $BaseUrl -Endpoint $endpoint -Method "POST" -Body $AttributeDef
    Write-Host "    Created: $($AttributeDef.SchemaName)" -ForegroundColor Green
}

Check If Entity/Attribute Exists

powershell

function Test-EntityExists {
    param([string]$Token, [string]$BaseUrl, [string]$LogicalName)
    try {
        Invoke-DataverseApi -Token $Token -BaseUrl $BaseUrl -Endpoint "EntityDefinitions(LogicalName='$LogicalName')" -Method "GET" | Out-Null
        return $true
    } catch { return $false }
}

function Test-AttributeExists {
    param([string]$Token, [string]$BaseUrl, [string]$EntityLogicalName, [string]$AttributeLogicalName)
    try {
        Invoke-DataverseApi -Token $Token -BaseUrl $BaseUrl -Endpoint "EntityDefinitions(LogicalName='$EntityLogicalName')/Attributes(LogicalName='$AttributeLogicalName')" -Method "GET" | Out-Null
        return $true
    } catch { return $false }
}

Relationship Operations

Create Lookup (1:N) Relationship

CRITICAL: Lookup attributes CANNOT be created directly via AttributeMetadata. You MUST create them via RelationshipDefinitions.

powershell

function New-OneToManyRelationship {
    param(
        [string]$Token,
        [string]$BaseUrl,
        [string]$ReferencedEntity,      # Parent entity (1 side)
        [string]$ReferencingEntity,     # Child entity (N side)
        [string]$LookupSchemaName,      # e.g., "sprk_parentid"
        [string]$LookupDisplayName,
        [string]$LookupDescription,
        [bool]$Required = $false,
        [string]$DeleteBehavior = "RemoveLink"  # RemoveLink, Restrict, Cascade
    )

    $relationshipSchemaName = "sprk_$($ReferencedEntity)_$($ReferencingEntity)_$($LookupSchemaName -replace 'sprk_', '')"

    $relationshipDef = @{
        "@odata.type" = "Microsoft.Dynamics.CRM.OneToManyRelationshipMetadata"
        "SchemaName" = $relationshipSchemaName
        "ReferencedEntity" = $ReferencedEntity
        "ReferencingEntity" = $ReferencingEntity
        "CascadeConfiguration" = @{
            "Assign" = "NoCascade"
            "Delete" = $DeleteBehavior
            "Merge" = "NoCascade"
            "Reparent" = "NoCascade"
            "Share" = "NoCascade"
            "Unshare" = "NoCascade"
        }
        "Lookup" = @{
            "SchemaName" = $LookupSchemaName
            "DisplayName" = New-Label -Text $LookupDisplayName
            "Description" = New-Label -Text $LookupDescription
            "RequiredLevel" = @{ "Value" = if ($Required) { "ApplicationRequired" } else { "None" } }
        }
    }

    Invoke-DataverseApi -Token $Token -BaseUrl $BaseUrl -Endpoint "RelationshipDefinitions" -Method "POST" -Body $relationshipDef
    Write-Host "    Created lookup: $LookupSchemaName" -ForegroundColor Green
}

Delete Behavior Options

Behavior	Description
`Cascade`	Delete child records when parent is deleted
`Restrict`	Prevent parent deletion if children exist
`RemoveLink`	Clear the lookup value on children (default)

Create Many-to-Many (N:N) Relationship

powershell

function New-ManyToManyRelationship {
    param(
        [string]$Token,
        [string]$BaseUrl,
        [string]$Entity1LogicalName,
        [string]$Entity2LogicalName,
        [string]$RelationshipSchemaName  # e.g., "sprk_entity1_entity2"
    )

    $relationshipDef = @{
        "@odata.type" = "Microsoft.Dynamics.CRM.ManyToManyRelationshipMetadata"
        "SchemaName" = $RelationshipSchemaName
        "Entity1LogicalName" = $Entity1LogicalName
        "Entity2LogicalName" = $Entity2LogicalName
        "Entity1AssociatedMenuConfiguration" = @{
            "Behavior" = "UseCollectionName"
            "Group" = "Details"
            "Order" = 10000
        }
        "Entity2AssociatedMenuConfiguration" = @{
            "Behavior" = "UseCollectionName"
            "Group" = "Details"
            "Order" = 10000
        }
        "IntersectEntityName" = $RelationshipSchemaName
    }

    Invoke-DataverseApi -Token $Token -BaseUrl $BaseUrl -Endpoint "RelationshipDefinitions" -Method "POST" -Body $relationshipDef
    Write-Host "  Created N:N: $RelationshipSchemaName" -ForegroundColor Green
}

Global Option Set Operations

Create Global Option Set

powershell

function New-GlobalOptionSet {
    param(
        [string]$Token,
        [string]$BaseUrl,
        [string]$Name,
        [string]$DisplayName,
        [string]$Description,
        [hashtable[]]$Options  # @{ Value = 0; Label = "Option1" }, ...
    )

    $optionSetDef = @{
        "@odata.type" = "Microsoft.Dynamics.CRM.OptionSetMetadata"
        "Name" = $Name
        "DisplayName" = New-Label -Text $DisplayName
        "Description" = New-Label -Text $Description
        "IsGlobal" = $true
        "OptionSetType" = "Picklist"
        "Options" = @(
            $Options | ForEach-Object {
                @{
                    "Value" = $_.Value
                    "Label" = New-Label -Text $_.Label
                }
            }
        )
    }

    Invoke-DataverseApi -Token $Token -BaseUrl $BaseUrl -Endpoint "GlobalOptionSetDefinitions" -Method "POST" -Body $optionSetDef
    Write-Host "  Created option set: $Name" -ForegroundColor Green
}

# Example usage:
$statusOptions = @(
    @{ Value = 0; Label = "Pending" },
    @{ Value = 1; Label = "Running" },
    @{ Value = 2; Label = "Completed" },
    @{ Value = 3; Label = "Failed" }
)
New-GlobalOptionSet -Token $token -BaseUrl $baseUrl -Name "sprk_status" -DisplayName "Status" -Description "Execution status" -Options $statusOptions

Get Global Option Set (for referencing in picklist attributes)

powershell

function Get-GlobalOptionSet {
    param([string]$Token, [string]$BaseUrl, [string]$Name)
    return Invoke-DataverseApi -Token $Token -BaseUrl $BaseUrl -Endpoint "GlobalOptionSetDefinitions(Name='$Name')" -Method "GET"
}

Publishing Customizations

After creating schema components, publish to make them available:

powershell

function Publish-Customizations {
    param(
        [string]$Token,
        [string]$BaseUrl,
        [string[]]$EntityLogicalNames  # Optional - specific entities only
    )

    if ($EntityLogicalNames -and $EntityLogicalNames.Count -gt 0) {
        $entityXml = ($EntityLogicalNames | ForEach-Object { "<entity>$_</entity>" }) -join ""
        $publishXml = @{
            "ParameterXml" = "<importexportxml><entities>$entityXml</entities></importexportxml>"
        }
    } else {
        $publishXml = @{
            "ParameterXml" = "<importexportxml><entities/></importexportxml>"
        }
    }

    Invoke-DataverseApi -Token $Token -BaseUrl $BaseUrl -Endpoint "PublishXml" -Method "POST" -Body $publishXml
    Write-Host "Customizations published" -ForegroundColor Green
}

Script Execution Order

When creating schema, follow this order to avoid dependency issues:

Phase 1: Global Option Sets
    ↓ (Option sets must exist before picklist attributes)
Phase 2: Lookup Reference Entities (small lookup tables)
    ↓ (Reference entities must exist before lookups to them)
Phase 3: Extend Existing Entities (add fields to existing tables)
    ↓ (Existing entities get new fields)
Phase 4: Create New Entities with Attributes and Relationships
    ↓ (New entities created with all components)
Phase 5: Create N:N Relationships
    ↓ (Both entities must exist first)
Phase 6: Publish Customizations

Reference Scripts

The following scripts in the repository demonstrate these patterns:

Main Schema Deployment

projects/ai-node-playbook-builder/scripts/Deploy-PlaybookNodeSchema.ps1

Complete 5-phase deployment script
Creates option sets, entities, attributes, lookups
Idempotent (checks for existing items before creating)

Add Missing Attributes

projects/ai-node-playbook-builder/scripts/Fix-PlaybookNodeAttributes.ps1

Pattern for adding attributes to existing entities
Focused on a single entity or set of entities
Useful for incremental schema updates

Create N:N Relationships

projects/ai-node-playbook-builder/scripts/Create-NNRelationships.ps1

Pattern for many-to-many relationships
Includes boolean attribute creation with OptionSet

Existing Script Pattern Reference

scripts/Deploy-ChartDefinitionEntity.ps1

Original pattern script
Shows entity creation with custom fields

Common Errors and Solutions

Error	Cause	Solution
`LookupAttributeMetadata cannot be created through the SDK`	Tried to create lookup via Attributes endpoint	Use RelationshipDefinitions endpoint with OneToManyRelationshipMetadata
`DefaultValue property does not exist`	DefaultValue not valid for some attribute types	Remove DefaultValue from IntegerAttributeMetadata
`An unexpected error occurred`	Transient API error or malformed request	Re-run script (idempotent design), check JSON structure
`Entity with name X already exists`	Entity already created	Add existence check before creation
`GlobalOptionSet not found`	Option set referenced before creation	Create global option sets in Phase 1

Verification

After deployment, verify schema:

powershell

# Verify entity exists
$entity = Invoke-DataverseApi -Token $token -BaseUrl $baseUrl `
    -Endpoint "EntityDefinitions(LogicalName='sprk_myentity')?`$select=LogicalName"

# Verify attributes
$attrs = Invoke-DataverseApi -Token $token -BaseUrl $baseUrl `
    -Endpoint "EntityDefinitions(LogicalName='sprk_myentity')?`$expand=Attributes(`$select=LogicalName,AttributeType)"

$attrs.Attributes | Where-Object { $_.LogicalName -like "sprk_*" } |
    ForEach-Object { "$($_.LogicalName) ($($_.AttributeType))" }

# Verify relationships
$rels = Invoke-DataverseApi -Token $token -BaseUrl $baseUrl `
    -Endpoint "EntityDefinitions(LogicalName='sprk_myentity')/ManyToOneRelationships?`$select=SchemaName,ReferencedEntity"

# Verify N:N relationships
$nn = Invoke-DataverseApi -Token $token -BaseUrl $baseUrl `
    -Endpoint "EntityDefinitions(LogicalName='sprk_myentity')/ManyToManyRelationships?`$select=SchemaName"

Integration with Other Skills

Skill	Relationship
`dataverse-deploy`	Deploy solutions after schema creation
`task-execute`	May invoke this skill for tasks with `dataverse`, `schema` tags
`adr-aware`	ADR-022 requires unmanaged solutions

ADR Compliance

ADR-022: All schema changes must use unmanaged solutions only
Schema created via Web API is automatically unmanaged
Never deploy managed solutions to dev environments

Example: Complete Entity Creation

powershell

# Create entity with all attribute types
$token = (az account get-access-token --resource "https://spaarkedev1.crm.dynamics.com" --query accessToken -o tsv)
$baseUrl = "https://spaarkedev1.crm.dynamics.com/api/data/v9.2"

# 1. Create option set
New-GlobalOptionSet -Token $token -BaseUrl $baseUrl -Name "sprk_mystatus" -DisplayName "My Status" -Description "Status options" -Options @(
    @{ Value = 0; Label = "Draft" },
    @{ Value = 1; Label = "Active" },
    @{ Value = 2; Label = "Archived" }
)

# 2. Create entity
New-Entity -Token $token -BaseUrl $baseUrl -SchemaName "sprk_myentity" -DisplayName "My Entity" -PluralName "My Entities" -Description "Sample entity"

# 3. Add attributes
Add-EntityAttribute -Token $token -BaseUrl $baseUrl -EntityLogicalName "sprk_myentity" -AttributeDef `
    (New-StringAttribute -SchemaName "sprk_code" -DisplayName "Code" -Description "Unique code" -MaxLength 50 -Required $true)

Add-EntityAttribute -Token $token -BaseUrl $baseUrl -EntityLogicalName "sprk_myentity" -AttributeDef `
    (New-MemoAttribute -SchemaName "sprk_notes" -DisplayName "Notes" -Description "Additional notes" -MaxLength 100000)

Add-EntityAttribute -Token $token -BaseUrl $baseUrl -EntityLogicalName "sprk_myentity" -AttributeDef `
    (New-PicklistAttribute -SchemaName "sprk_status" -DisplayName "Status" -Description "Record status" -GlobalOptionSetName "sprk_mystatus")

# 4. Create lookup (if parent entity exists)
New-OneToManyRelationship -Token $token -BaseUrl $baseUrl -ReferencedEntity "sprk_parententity" -ReferencingEntity "sprk_myentity" `
    -LookupSchemaName "sprk_parentid" -LookupDisplayName "Parent" -LookupDescription "Parent record" -Required $true -DeleteBehavior "Cascade"

# 5. Publish
Publish-Customizations -Token $token -BaseUrl $baseUrl -EntityLogicalNames @("sprk_myentity")

Skill created from Task 009 implementation patterns. For questions, see the reference scripts in projects/ai-node-playbook-builder/scripts/.

Maintainer

majiayu000 Core maintainer

Source details

Full Name: majiayu000/claude-skill-registry
Branch: main
Path in repo: skills/data/dataverse-create-schema
License: MIT License

Featured Tools

Join Our Newsletter

Maintain .agent state files. Use at session start, after meaningful steps, and before concluding: read/update constitution/memory/focus/issues/baseline consistently.

163 31

Explore

Didn't find tool you were looking for?

Search AI Tools

Install this agent skill to your Project

SKILL.md

dataverse-create-schema

Purpose

When to Use

Prerequisites

1. Azure CLI Authentication

2. PAC CLI Authentication (for verification)

3. Target Environment URL

Core Patterns

Authentication and Headers

API Helper Function

Label Helper

Attribute Type Definitions

String Attribute

Memo (Multiline Text) Attribute

Integer Attribute

Boolean Attribute

Picklist (Choice) Attribute with Global Option Set

DateTime Attribute

Entity Operations

Create New Entity

Add Attribute to Existing Entity

Check If Entity/Attribute Exists

Relationship Operations

Create Lookup (1:N) Relationship

Delete Behavior Options

Create Many-to-Many (N:N) Relationship

Global Option Set Operations

Create Global Option Set

Get Global Option Set (for referencing in picklist attributes)

Publishing Customizations

Script Execution Order

Reference Scripts

Main Schema Deployment

Add Missing Attributes

Create N:N Relationships

Existing Script Pattern Reference

Common Errors and Solutions

Verification

Integration with Other Skills

ADR Compliance

Example: Complete Entity Creation

Recommended Agent Skills

agent-ops-spec

agent-ops-state

agent-ops-spec

agent-ops-testing

agent-ops-testing

agent-ops-state