Dataverse Alternate Keys — Cross-Solution Integration

Overview

Alternate keys enable atomic upsert operations by business key instead of GUID. This eliminates race conditions in the non-atomic ListRecords → if-exists → Create/Update pattern used by CD-SolutionFeedCollector and Sync-SolutionAssessments.ps1.

Alternate Key Definitions

Assessment Upsert Key (fsi_AssessmentUpsertKey)

Table: fsi_controlassessment (Control Assessment)
Logical Name: fsi_assessmentupsertkey
Description: Enables atomic upsert of control assessments by business key (control + date + zone) instead of GUID. Eliminates the non-atomic list-then-create race condition in CD-SolutionFeedCollector and Sync-SolutionAssessments.ps1.

Key Columns

SchemaName	Logical Name	Type	Description
fsi_ControlMasterId	fsi_controlmasterid	Lookup	Reference to the control being assessed
fsi_AssessmentDate	fsi_assessmentdate	DateTime	Date of assessment (deduplication boundary)
fsi_Zone	fsi_zone	Choice	Governance zone (1/2/3)

Upsert Example

PATCH /api/data/v9.2/fsi_controlassessments(fsi_controlmasterid=<control-guid>,fsi_assessmentdate=2026-05-12,fsi_zone=1)
Content-Type: application/json

{
  "fsi_status": 1,
  "fsi_score": 100,
  "fsi_notes": "Automated assessment from ACV feed"
}

Dataverse will create a new record if the key combination does not exist, or update the existing record if it does — atomically.

Migration Path

1. Deploy alternate key using create_csi_alternate_keys.py
2. Wait for Dataverse to index the key (may take minutes for large tables)
3. Update CD-SolutionFeedCollector flow to use PATCH with key URL instead of ListRecords → Create/Update
4. Update Sync-SolutionAssessments.ps1 to use -UpsertByAlternateKey

---

Auto-generated from create_csi_alternate_keys.py definitions.