---
apiVersion: data-spec/v1
id: campaignmemberstatus-backfill-empty-campaigns-20260422
description: "Backfill default CampaignMemberStatus rows for existing Campaigns with no statuses"
issue: "#1519"
created: 2026-04-22
scope: production-only
status: pending
tables: [Campaign, CampaignMemberStatus]
depends_on: [campaign-default-member-statuses-flow-20260421]
---

# CampaignMemberStatus Backfill For Empty Campaigns

## Summary

Backfills the standard `Sent` and `Responded` `CampaignMemberStatus` rows for
existing `Campaign` records that currently have no non-deleted
`CampaignMemberStatus` rows at all.

This closes the runtime-picklist gap for pre-existing campaigns without
overwriting partially customized campaigns that already have one or more status
rows.

## Changes

### 1. Seed default CampaignMemberStatus rows for campaigns with zero statuses

```sql
INSERT INTO CampaignMemberStatus (
  Id,
  CampaignId,
  Label,
  IsDefault,
  HasResponded,
  SortOrder,
  IsDeleted,
  CreatedDate,
  LastModifiedDate
)
SELECT
  CONCAT('cms-backfill-', c.Id, '-', seed.LabelSlug) AS Id,
  c.Id AS CampaignId,
  seed.Label,
  seed.IsDefault,
  seed.HasResponded,
  seed.SortOrder,
  0 AS IsDeleted,
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
FROM Campaign c
JOIN (
  SELECT 'Sent' AS Label, 'sent' AS LabelSlug, 1 AS IsDefault, 0 AS HasResponded, 1 AS SortOrder
  UNION ALL
  SELECT 'Responded', 'responded', 0, 1, 2
) seed
WHERE COALESCE(c.IsDeleted, 0) = 0
  AND NOT EXISTS (
    SELECT 1
    FROM CampaignMemberStatus cms
    WHERE cms.CampaignId = c.Id
      AND COALESCE(cms.IsDeleted, 0) = 0
  );
```

**Idempotency**: The `NOT EXISTS` guard scopes inserts to campaigns that still
have zero non-deleted status rows. After the first successful run, those
campaigns no longer match the predicate, so re-running the spec is a no-op.

## Verification

```sql
SELECT COUNT(*) AS campaigns_without_statuses
FROM Campaign c
WHERE COALESCE(c.IsDeleted, 0) = 0
  AND NOT EXISTS (
    SELECT 1
    FROM CampaignMemberStatus cms
    WHERE cms.CampaignId = c.Id
      AND COALESCE(cms.IsDeleted, 0) = 0
  );
```

Expected:

- `campaigns_without_statuses = 0` for the population this spec targets

## Rollback

```sql
DELETE FROM CampaignMemberStatus
WHERE Id LIKE 'cms-backfill-%-sent'
   OR Id LIKE 'cms-backfill-%-responded';
```