---
apiVersion: data-spec/v1
id: campaignmemberstatus-default-sort-order-flow-20260421
description: "Register a runtime flow that assigns MAX(SortOrder)+1 when CampaignMemberStatus.SortOrder is omitted"
issue: "#1496"
created: 2026-04-21
scope: production-only
status: pending
tables: [FlowDefinition, Flow]
depends_on: []
---

# CampaignMemberStatus Default Sort Order Flow

## Summary

Registers a runtime `RecordBeforeSave` flow on `CampaignMemberStatus` that fills
`SortOrder` with `MAX(existing SortOrder) + 1` within the same Campaign when the
incoming record omits the field.

The flow metadata source of truth lives at
`backend/internal/features/automation/testdata/campaign_member_status_flows/CampaignMemberStatus_Default_Sort_Order.flow.json`.

## Changes

### 1. Upsert the `CampaignMemberStatus_Default_Sort_Order` flow definition and active version

```sql
SET @flow_definition_insert_id := 'flowdef-campaignmemberstatus-default-sort-order-20260421';
SET @flow_version_insert_id := 'flowver-campaignmemberstatus-default-sort-order-v1-20260421';

INSERT INTO FlowDefinition (
  Id,
  DeveloperName,
  MasterLabel,
  Description,
  ProcessType,
  TriggerType,
  TriggerObjectOrEvent,
  ActiveVersionId,
  LatestVersionId,
  IsDeleted
) VALUES (
  @flow_definition_insert_id,
  'CampaignMemberStatus_Default_Sort_Order',
  'CampaignMemberStatus Default Sort Order',
  'When SortOrder is omitted on create, assign MAX(SortOrder) + 1 within the same Campaign.',
  'AutoLaunchedFlow',
  'RecordBeforeSave',
  'CampaignMemberStatus',
  NULL,
  NULL,
  0
)
ON DUPLICATE KEY UPDATE
  MasterLabel = VALUES(MasterLabel),
  Description = VALUES(Description),
  ProcessType = VALUES(ProcessType),
  TriggerType = VALUES(TriggerType),
  TriggerObjectOrEvent = VALUES(TriggerObjectOrEvent),
  IsDeleted = 0,
  LastModifiedDate = CURRENT_TIMESTAMP;

SET @flow_definition_id := (
  SELECT Id
  FROM FlowDefinition
  WHERE DeveloperName = 'CampaignMemberStatus_Default_Sort_Order'
  LIMIT 1
);

INSERT INTO Flow (
  Id,
  DefinitionId,
  MasterLabel,
  VersionNumber,
  Status,
  Description,
  ProcessType,
  Metadata,
  IsDeleted
) VALUES (
  @flow_version_insert_id,
  @flow_definition_id,
  'CampaignMemberStatus Default Sort Order',
  1,
  'Active',
  'When SortOrder is omitted on create, assign MAX(SortOrder) + 1 within the same Campaign.',
  'AutoLaunchedFlow',
  CONVERT(FROM_BASE64('eyJhcGlWZXJzaW9uIjo2Mywic3RhdHVzIjoiQWN0aXZlIiwibGFiZWwiOiJDYW1wYWlnbk1lbWJlclN0YXR1cyBEZWZhdWx0IFNvcnQgT3JkZXIiLCJkZXNjcmlwdGlvbiI6IldoZW4gU29ydE9yZGVyIGlzIG9taXR0ZWQgb24gY3JlYXRlLCBhc3NpZ24gTUFYKFNvcnRPcmRlcikgKyAxIHdpdGhpbiB0aGUgc2FtZSBDYW1wYWlnbi4iLCJzdGFydCI6eyJsb2NhdGlvblgiOjQwLCJsb2NhdGlvblkiOjAsImNvbm5lY3RvciI6eyJ0YXJnZXRSZWZlcmVuY2UiOiJMb29rdXBIaWdoZXN0U29ydE9yZGVyIn0sIm9iamVjdCI6IkNhbXBhaWduTWVtYmVyU3RhdHVzIiwidHJpZ2dlclR5cGUiOiJSZWNvcmRCZWZvcmVTYXZlIiwicmVjb3JkVHJpZ2dlclR5cGUiOiJDcmVhdGUiLCJmaWx0ZXJMb2dpYyI6ImFuZCIsImZpbHRlcnMiOlt7ImZpZWxkIjoiU29ydE9yZGVyIiwib3BlcmF0b3IiOiJJc0JsYW5rIn1dfSwidmFyaWFibGVzIjpbeyJuYW1lIjoiJFJlY29yZCIsImRhdGFUeXBlIjoiU09iamVjdCIsImlzSW5wdXQiOnRydWUsImlzT3V0cHV0Ijp0cnVlLCJvYmplY3RUeXBlIjoiQ2FtcGFpZ25NZW1iZXJTdGF0dXMifSx7Im5hbWUiOiJsYXRlc3RTb3J0T3JkZXIiLCJkYXRhVHlwZSI6Ik51bWJlciIsInZhbHVlIjoiIn1dLCJyZWNvcmRMb29rdXBzIjpbeyJuYW1lIjoiTG9va3VwSGlnaGVzdFNvcnRPcmRlciIsImxhYmVsIjoiTG9va3VwIGhpZ2hlc3Qgc29ydCBvcmRlciIsImxvY2F0aW9uWCI6NDAsImxvY2F0aW9uWSI6MTIwLCJvYmplY3QiOiJDYW1wYWlnbk1lbWJlclN0YXR1cyIsImZpbHRlckxvZ2ljIjoiYW5kIiwiZmlsdGVycyI6W3siZmllbGQiOiJDYW1wYWlnbklkIiwib3BlcmF0b3IiOiJFcXVhbFRvIiwidmFsdWUiOnsiZWxlbWVudFJlZmVyZW5jZSI6IiRSZWNvcmQuQ2FtcGFpZ25JZCJ9fV0sIm91dHB1dEFzc2lnbm1lbnRzIjpbeyJmaWVsZCI6IlNvcnRPcmRlciIsImFzc2lnblRvUmVmZXJlbmNlIjoibGF0ZXN0U29ydE9yZGVyIn1dLCJnZXRGaXJzdFJlY29yZE9ubHkiOnRydWUsInNvcnRGaWVsZCI6IlNvcnRPcmRlciIsInNvcnRPcmRlciI6IkRFU0MiLCJjb25uZWN0b3IiOnsidGFyZ2V0UmVmZXJlbmNlIjoiSGFzRXhpc3RpbmdTdGF0dXNlcyJ9fV0sImRlY2lzaW9ucyI6W3sibmFtZSI6Ikhhc0V4aXN0aW5nU3RhdHVzZXMiLCJsYWJlbCI6IkhhcyBleGlzdGluZyBzdGF0dXNlcyIsImxvY2F0aW9uWCI6MjYwLCJsb2NhdGlvblkiOjEyMCwicnVsZXMiOlt7Im5hbWUiOiJoYXNMYXRlc3RTb3J0T3JkZXIiLCJjb25kaXRpb25Mb2dpYyI6ImFuZCIsImNvbmRpdGlvbnMiOlt7ImxlZnRWYWx1ZVJlZmVyZW5jZSI6ImxhdGVzdFNvcnRPcmRlciIsIm9wZXJhdG9yIjoiSXNOb3RCbGFuayJ9XSwiY29ubmVjdG9yIjp7InRhcmdldFJlZmVyZW5jZSI6IkFzc2lnbk5leHRTb3J0T3JkZXIifX1dLCJkZWZhdWx0Q29ubmVjdG9yIjp7InRhcmdldFJlZmVyZW5jZSI6IkFzc2lnbkZpcnN0U29ydE9yZGVyIn19XSwiYXNzaWdubWVudHMiOlt7Im5hbWUiOiJBc3NpZ25OZXh0U29ydE9yZGVyIiwibGFiZWwiOiJBc3NpZ24gbmV4dCBzb3J0IG9yZGVyIiwibG9jYXRpb25YIjo1MDAsImxvY2F0aW9uWSI6NjAsImFzc2lnbm1lbnRJdGVtcyI6W3siYXNzaWduVG9SZWZlcmVuY2UiOiIkUmVjb3JkLlNvcnRPcmRlciIsIm9wZXJhdG9yIjoiQXNzaWduIiwidmFsdWUiOnsiZWxlbWVudFJlZmVyZW5jZSI6ImxhdGVzdFNvcnRPcmRlciJ9fSx7ImFzc2lnblRvUmVmZXJlbmNlIjoiJFJlY29yZC5Tb3J0T3JkZXIiLCJvcGVyYXRvciI6IkFkZCIsInZhbHVlIjoxfV19LHsibmFtZSI6IkFzc2lnbkZpcnN0U29ydE9yZGVyIiwibGFiZWwiOiJBc3NpZ24gZmlyc3Qgc29ydCBvcmRlciIsImxvY2F0aW9uWCI6NTAwLCJsb2NhdGlvblkiOjE4MCwiYXNzaWdubWVudEl0ZW1zIjpbeyJhc3NpZ25Ub1JlZmVyZW5jZSI6IiRSZWNvcmQuU29ydE9yZGVyIiwib3BlcmF0b3IiOiJBc3NpZ24iLCJ2YWx1ZSI6MX1dfV19Cg==') USING utf8mb4),
  0
)
ON DUPLICATE KEY UPDATE
  MasterLabel = VALUES(MasterLabel),
  Status = VALUES(Status),
  Description = VALUES(Description),
  ProcessType = VALUES(ProcessType),
  Metadata = VALUES(Metadata),
  IsDeleted = 0,
  LastModifiedDate = CURRENT_TIMESTAMP;

SET @flow_version_id := (
  SELECT Id
  FROM Flow
  WHERE DefinitionId = @flow_definition_id
    AND VersionNumber = 1
  LIMIT 1
);

UPDATE FlowDefinition
SET
  ProcessType = 'AutoLaunchedFlow',
  TriggerType = 'RecordBeforeSave',
  TriggerObjectOrEvent = 'CampaignMemberStatus',
  ActiveVersionId = @flow_version_id,
  LatestVersionId = @flow_version_id,
  IsDeleted = 0,
  LastModifiedDate = CURRENT_TIMESTAMP
WHERE Id = @flow_definition_id;
```

**Idempotency**: The flow definition is keyed by unique `DeveloperName`, and the
flow version is keyed by `(DefinitionId, VersionNumber)`. Re-running the spec
updates the same logical flow metadata and re-points `ActiveVersionId` /
`LatestVersionId` to the current version row.

## Rollback

```sql
DELETE f
FROM Flow f
JOIN FlowDefinition fd ON fd.Id = f.DefinitionId
WHERE fd.DeveloperName = 'CampaignMemberStatus_Default_Sort_Order';

DELETE FROM FlowDefinition
WHERE DeveloperName = 'CampaignMemberStatus_Default_Sort_Order';
```