CRM Data Backfill Strategy

Version: 1.0 Date: December 2025

---

Overview

This document outlines the strategy for backfilling the CRM database with existing customer data from:

1. MCA v1 ZA Database - South African golf member data
2. MCA v1 UK Database - United Kingdom golf member data
3. SCL Service - Current membership data
4. TeeTime Service - Player and booking data
5. Messaging Service - Contact and consent data

---

Data Sources

MCA v1 ZA Database

Table	Records (Est.)	Key Fields
members	~50,000	id, email, phone, first_name, last_name, golf_rsa_id
clubs	~500	id, name, code
member_clubs	~80,000	member_id, club_id, status
handicaps	~200,000	member_id, handicap_index, effective_date

MCA v1 UK Database

Table	Records (Est.)	Key Fields
members	~30,000	id, email, mobile, forename, surname, cdh_id
clubs	~300	id, name, club_code
memberships	~50,000	member_id, club_id, membership_type

SCL Service

Table	Records (Est.)	Key Fields
Member	~10,000	id, tenantId, email, firstName, lastName, externalSubject
MemberClub	~15,000	memberId, clubId, joinedOn
SportMembership	~12,000	memberId, sportId, status

TeeTime Service

Table	Records (Est.)	Key Fields
Player	~100,000	id, email, phone, name, surname, handicap
PlayerClubMembership	~80,000	playerId, clubId
PlayerAssociation	~60,000	playerId, associationId, externalId

Messaging Service

Table	Records (Est.)	Key Fields
Contact	~80,000	id, tenantId, email, phoneNumber, emailOptIn, smsOptIn
ContactPreference	~150,000	contactId, channel, scope, optedIn
ConsentChangeLog	~50,000	contactId, channel, source, consentAfter

---

Backfill Phases

┌─────────────────────────────────────────────────────────────────────────────┐
│                         BACKFILL EXECUTION ORDER                            │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│   Phase 1: MCA v1 Data (Historical Foundation)                              │
│   ├── 1a. Export MCA v1 ZA members                                          │
│   ├── 1b. Export MCA v1 UK members                                          │
│   └── 1c. Transform and load as CustomerProfiles                            │
│           │                                                                 │
│           ▼                                                                 │
│   Phase 2: SCL Members (Current Membership)                                 │
│   ├── 2a. Export SCL Members                                                │
│   ├── 2b. Identity resolution against Phase 1 profiles                      │
│   └── 2c. Link or create profiles, set sclMemberId                          │
│           │                                                                 │
│           ▼                                                                 │
│   Phase 3: TeeTime Players (Golf Activity)                                  │
│   ├── 3a. Export TeeTime Players                                            │
│   ├── 3b. Identity resolution against existing profiles                     │
│   └── 3c. Link or create profiles, set teetimePlayerId                      │
│           │                                                                 │
│           ▼                                                                 │
│   Phase 4: Messaging Contacts (Communication Preferences)                   │
│   ├── 4a. Export Messaging Contacts                                         │
│   ├── 4b. Identity resolution against existing profiles                     │
│   └── 4c. Link profiles, sync consent data                                  │
│           │                                                                 │
│           ▼                                                                 │
│   Phase 5: Activity Backfill (Historical Timeline)                          │
│   ├── 5a. Import historical bookings as activities                          │
│   ├── 5b. Import historical messages as activities                          │
│   └── 5c. Import membership changes as activities                           │
│           │                                                                 │
│           ▼                                                                 │
│   Phase 6: Scoring & Segments (Computed Data)                               │
│   ├── 6a. Run batch engagement scoring                                      │
│   ├── 6b. Run batch churn risk scoring                                      │
│   └── 6c. Refresh dynamic segments                                          │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

---

Phase 1: MCA v1 Data Migration

1a. Export MCA v1 ZA

-- Export query for MCA v1 ZA
SELECT
  m.id AS mca_za_id,
  m.email,
  m.phone AS phone_number,
  m.first_name,
  m.last_name,
  m.date_of_birth,
  m.gender,
  m.golf_rsa_id,
  m.created_at,
  m.updated_at,
  m.status,
  -- Get home club
  (
    SELECT mc.club_id
    FROM member_clubs mc
    WHERE mc.member_id = m.id AND mc.is_home_club = true
    LIMIT 1
  ) AS home_club_id,
  -- Get latest handicap
  (
    SELECT h.handicap_index
    FROM handicaps h
    WHERE h.member_id = m.id
    ORDER BY h.effective_date DESC
    LIMIT 1
  ) AS handicap
FROM members m
WHERE m.email IS NOT NULL OR m.phone IS NOT NULL;

1b. Export MCA v1 UK

-- Export query for MCA v1 UK
SELECT
  m.id AS mca_uk_id,
  m.email,
  m.mobile AS phone_number,
  m.forename AS first_name,
  m.surname AS last_name,
  m.dob AS date_of_birth,
  m.gender,
  m.cdh_id,  -- UK equivalent of golf_rsa_id
  m.created_at,
  m.updated_at,
  m.status,
  -- Get home club
  (
    SELECT ms.club_id
    FROM memberships ms
    WHERE ms.member_id = m.id AND ms.is_home = true
    LIMIT 1
  ) AS home_club_id,
  -- Get handicap
  m.handicap_index AS handicap
FROM members m
WHERE m.email IS NOT NULL OR m.mobile IS NOT NULL;

1c. Transform and Load

// scripts/backfill/mca-v1-import.ts

interface McaV1Record {
  mca_za_id?: string;
  mca_uk_id?: string;
  email?: string;
  phone_number?: string;
  first_name?: string;
  last_name?: string;
  date_of_birth?: string;
  gender?: string;
  golf_rsa_id?: string;
  cdh_id?: string;
  handicap?: number;
  home_club_id?: string;
  status?: string;
  created_at?: string;
}

async function importMcaV1Records(
  records: McaV1Record[],
  region: 'ZA' | 'UK',
): Promise<ImportResult> {
  const results = { created: 0, skipped: 0, errors: [] };

  for (const record of records) {
    try {
      // Normalize data
      const email = record.email?.toLowerCase().trim() || null;
      const phone = normalizePhone(record.phone_number, region);
      const externalIdKey = region === 'ZA' ? 'mcaV1ZaId' : 'mcaV1UkId';

      // Skip if no identifiable info
      if (!email && !phone) {
        results.skipped++;
        continue;
      }

      // Check for existing profile
      const existing = await findExistingProfile(email, phone);

      if (existing) {
        // Update existing profile with MCA data
        await prisma.customerProfile.update({
          where: { id: existing.id },
          data: {
            externalIds: {
              ...(existing.externalIds as object),
              [externalIdKey]: record.mca_za_id || record.mca_uk_id,
              ...(record.golf_rsa_id && { golfRsaId: record.golf_rsa_id }),
              ...(record.cdh_id && { cdhId: record.cdh_id }),
            },
            // Only update if not already set
            firstName: existing.firstName || record.first_name,
            lastName: existing.lastName || record.last_name,
            handicap: existing.handicap ?? record.handicap,
          },
        });
      } else {
        // Create new profile
        await prisma.customerProfile.create({
          data: {
            tenantId: getTenantIdForRegion(region),
            email,
            phoneNumber: phone,
            firstName: record.first_name,
            lastName: record.last_name,
            displayName: `${record.first_name} ${record.last_name}`.trim(),
            dateOfBirth: record.date_of_birth ? new Date(record.date_of_birth) : null,
            gender: mapGender(record.gender),
            handicap: record.handicap,
            externalIds: {
              [externalIdKey]: record.mca_za_id || record.mca_uk_id,
              ...(record.golf_rsa_id && { golfRsaId: record.golf_rsa_id }),
              ...(record.cdh_id && { cdhId: record.cdh_id }),
            },
            source: `mca_v1_${region.toLowerCase()}`,
            status: mapStatus(record.status),
            lifecycleStage: 'CUSTOMER',
            firstSeenAt: record.created_at ? new Date(record.created_at) : new Date(),
          },
        });
        results.created++;
      }
    } catch (error) {
      results.errors.push({ record, error: error.message });
    }
  }

  return results;
}

function normalizePhone(phone: string | undefined, region: 'ZA' | 'UK'): string | null {
  if (!phone) return null;
  const digits = phone.replace(/\D/g, '');

  if (region === 'ZA') {
    // South African numbers
    if (digits.startsWith('27') && digits.length === 11) return `+${digits}`;
    if (digits.startsWith('0') && digits.length === 10) return `+27${digits.slice(1)}`;
  } else {
    // UK numbers
    if (digits.startsWith('44') && digits.length === 12) return `+${digits}`;
    if (digits.startsWith('0') && digits.length === 11) return `+44${digits.slice(1)}`;
  }

  return null; // Can't normalize
}

---

Phase 2: SCL Member Migration

2a. Export SCL Members

// Export from SCL database
const sclMembers = await sclPrisma.member.findMany({
  include: {
    memberClubs: { include: { club: true } },
    membershipTier: true,
    sportMemberships: true,
  },
});

2b. Identity Resolution

async function importSclMembers(members: SclMember[]): Promise<ImportResult> {
  for (const member of members) {
    // Resolve identity
    const { profile, isNew } = await identityService.resolve({
      tenantId: member.tenantId.toString(),
      email: member.email,
      // Map externalSubject to authUserId if it's an IDP ID
      authUserId: isIdpSubject(member.externalSubject) ? member.externalSubject : undefined,
    });

    // Update profile with SCL link
    await prisma.customerProfile.update({
      where: { id: profile.id },
      data: {
        sclMemberId: member.id,
        firstName: member.firstName || profile.firstName,
        lastName: member.lastName || profile.lastName,
        membershipStatus: member.status,
        membershipTier: member.membershipTier?.name,
        // ... other SCL-specific fields
      },
    });

    // Create activity for membership
    await createActivity({
      customerId: profile.id,
      type: 'MEMBER_CREATED',
      category: 'MEMBERSHIP',
      title: 'Membership imported from SCL',
      sourceService: 'scl',
      sourceId: member.id.toString(),
      occurredAt: member.createdAt,
    });
  }
}

---

Phase 3: TeeTime Player Migration

3a. Export TeeTime Players

const players = await teetimePrisma.player.findMany({
  include: {
    clubMemberships: { include: { club: true } },
    associations: true,
    preferences: true,
  },
});

3b. Identity Resolution & Link

async function importTeetimePlayers(players: TeetimePlayer[]): Promise<ImportResult> {
  for (const player of players) {
    // Build external IDs from associations
    const externalIds: Record<string, string> = {};
    for (const assoc of player.associations) {
      if (assoc.associationType === 'GOLF_RSA') {
        externalIds.golfRsaId = assoc.externalId;
      }
      if (assoc.associationType === 'MCA') {
        externalIds.mcaId = assoc.externalId;
      }
    }

    // Resolve identity
    const { profile, isNew } = await identityService.resolve({
      tenantId: player.homeClub?.tenantId?.toString() || 'default',
      email: player.email,
      phoneNumber: player.phone,
      externalIds,
    });

    // Update profile with TeeTime link
    await prisma.customerProfile.update({
      where: { id: profile.id },
      data: {
        teetimePlayerId: player.id,
        handicap: player.handicap ?? profile.handicap,
        handicapUpdatedAt: player.handicapUpdatedAt,
        homeClubId: player.homeClubId,
        homeClubName: player.homeClub?.name,
        // Merge external IDs
        externalIds: {
          ...(profile.externalIds as object),
          ...externalIds,
        },
      },
    });
  }
}

---

Phase 4: Messaging Contact Migration

4a. Export Messaging Contacts

const contacts = await messagingPrisma.contact.findMany({
  include: {
    contactPreferences: true,
  },
  where: {
    deletedAt: null,
  },
});

4b. Identity Resolution & Consent Sync

async function importMessagingContacts(contacts: MessagingContact[]): Promise<ImportResult> {
  for (const contact of contacts) {
    // Resolve identity
    const { profile } = await identityService.resolve({
      tenantId: contact.tenantId,
      email: contact.email,
      phoneNumber: contact.phoneNumber,
      authUserId: contact.authUserId,
    });

    // Update profile with Messaging link and consent
    await prisma.customerProfile.update({
      where: { id: profile.id },
      data: {
        messagingContactId: contact.id,
        emailOptIn: contact.emailOptIn,
        smsOptIn: contact.smsOptIn,
        // Sync other consent fields from preferences
      },
    });

    // Import consent history
    for (const pref of contact.contactPreferences) {
      await prisma.marketingConsent.create({
        data: {
          tenantId: contact.tenantId,
          customerId: profile.id,
          channel: mapChannel(pref.channel),
          scope: pref.scope || 'all',
          status: pref.optedIn ? 'OPTED_IN' : 'OPTED_OUT',
          consentedAt: pref.optedIn ? pref.createdAt : undefined,
          revokedAt: !pref.optedIn ? pref.updatedAt : undefined,
          source: 'IMPORT',
        },
      });
    }
  }
}

---

Phase 5: Activity Backfill

Historical Bookings

// Import TeeTime bookings as activities
const bookings = await teetimePrisma.teeTime.findMany({
  where: {
    createdAt: { gte: lookbackDate }, // e.g., last 2 years
  },
  include: {
    player: true,
    course: { include: { club: true } },
  },
});

for (const booking of bookings) {
  const profile = await findProfileByTeetimePlayer(booking.playerId);
  if (!profile) continue;

  await prisma.activity.create({
    data: {
      tenantId: profile.tenantId,
      customerId: profile.id,
      type: booking.status === 'COMPLETED' ? 'TEETIME_COMPLETED' : 'TEETIME_BOOKED',
      category: 'BOOKING',
      title: `Tee time at ${booking.course?.club?.name}`,
      sourceService: 'teetime',
      sourceId: booking.id,
      clubId: booking.course?.clubId,
      clubName: booking.course?.club?.name,
      occurredAt: booking.startTime,
      metadata: {
        courseId: booking.courseId,
        courseName: booking.course?.name,
        players: booking.playerCount,
      },
    },
  });
}

Historical Messages

// Import Messaging logs as activities
const messages = await messagingPrisma.messageLog.findMany({
  where: {
    createdAt: { gte: lookbackDate },
  },
});

for (const msg of messages) {
  const profile = await findProfileByMessagingContact(msg.contactId);
  if (!profile) continue;

  await prisma.activity.create({
    data: {
      tenantId: profile.tenantId,
      customerId: profile.id,
      type: mapMessageStatus(msg.status),
      category: 'COMMUNICATION',
      channel: msg.channel,
      title: `${msg.channel} message ${msg.status.toLowerCase()}`,
      sourceService: 'messaging',
      sourceId: msg.id,
      occurredAt: msg.createdAt,
      metadata: {
        templateId: msg.templateId,
        templateName: msg.templateName,
        campaignId: msg.campaignId,
      },
    },
  });
}

---

Phase 6: Compute Scores & Segments

Run Batch Scoring

// After all data is loaded, run scoring
await batchScoringJob.runBatchScoring();

Update Activity Metrics

// Update denormalized activity counts
await prisma.$executeRaw`
  UPDATE "CustomerProfile" cp
  SET
    "activityCount30d" = (
      SELECT COUNT(*)
      FROM "Activity" a
      WHERE a."customerId" = cp.id
        AND a."occurredAt" >= NOW() - INTERVAL '30 days'
    ),
    "activityCount90d" = (
      SELECT COUNT(*)
      FROM "Activity" a
      WHERE a."customerId" = cp.id
        AND a."occurredAt" >= NOW() - INTERVAL '90 days'
    ),
    "lastActivityAt" = (
      SELECT MAX(a."occurredAt")
      FROM "Activity" a
      WHERE a."customerId" = cp.id
    ),
    "bookingCountTotal" = (
      SELECT COUNT(*)
      FROM "Activity" a
      WHERE a."customerId" = cp.id
        AND a."category" = 'BOOKING'
    )
`;

Refresh Dynamic Segments

const dynamicSegments = await prisma.customerSegment.findMany({
  where: { type: 'DYNAMIC', isActive: true },
});

for (const segment of dynamicSegments) {
  await segmentationService.refreshDynamicSegment(segment.id);
}

---

Execution Checklist

Pre-Migration

• CRM database provisioned and schema migrated
• MCA v1 ZA database access confirmed
• MCA v1 UK database access confirmed
• SCL database access confirmed
• TeeTime database access confirmed
• Messaging database access confirmed
• Backfill scripts tested in staging

Phase 1: MCA v1

• Export MCA v1 ZA data
• Export MCA v1 UK data
• Run import script
• Verify record counts
• Spot-check identity resolution

Phase 2: SCL

• Export SCL Members
• Run import with identity resolution
• Verify sclMemberId links
• Verify membership data sync

Phase 3: TeeTime

• Export TeeTime Players
• Run import with identity resolution
• Verify teetimePlayerId links
• Verify handicap data sync

Phase 4: Messaging

• Export Messaging Contacts
• Run import with identity resolution
• Verify messagingContactId links
• Verify consent data sync

Phase 5: Activities

• Import historical bookings
• Import historical messages
• Import membership changes
• Verify activity timeline

Phase 6: Compute

• Run batch engagement scoring
• Run batch churn risk scoring
• Update activity metrics
• Refresh dynamic segments

Post-Migration

• Verify total profile count
• Verify identity resolution accuracy (sample audit)
• Verify cross-service links
• Run data quality report
• Enable real-time event sync

---

Data Quality Checks

-- Profile count by source
SELECT source, COUNT(*) FROM "CustomerProfile" GROUP BY source;

-- Linked vs unlinked profiles
SELECT
  COUNT(*) AS total,
  COUNT("sclMemberId") AS has_scl,
  COUNT("teetimePlayerId") AS has_teetime,
  COUNT("messagingContactId") AS has_messaging,
  COUNT(CASE WHEN "sclMemberId" IS NOT NULL AND "teetimePlayerId" IS NOT NULL THEN 1 END) AS scl_and_teetime,
  COUNT(CASE WHEN "sclMemberId" IS NOT NULL AND "teetimePlayerId" IS NOT NULL AND "messagingContactId" IS NOT NULL THEN 1 END) AS all_three
FROM "CustomerProfile";

-- Duplicate email check
SELECT email, COUNT(*) AS count
FROM "CustomerProfile"
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

-- Activity distribution
SELECT category, COUNT(*) FROM "Activity" GROUP BY category;

-- Score distribution
SELECT
  CASE
    WHEN "engagementScore" >= 80 THEN 'High (80-100)'
    WHEN "engagementScore" >= 50 THEN 'Medium (50-79)'
    WHEN "engagementScore" >= 20 THEN 'Low (20-49)'
    ELSE 'Very Low (0-19)'
  END AS score_band,
  COUNT(*)
FROM "CustomerProfile"
GROUP BY 1;

---

Rollback Plan

If migration fails:

1. Stop event consumers - Prevent new data from coming in
2. Truncate CRM tables - Clear partially migrated data
3. Fix issues - Address root cause
4. Re-run migration - Start fresh

-- Emergency rollback (use with caution!)
TRUNCATE TABLE
  "CustomerProfile",
  "IdentityAlias",
  "Activity",
  "CustomerNote",
  "CustomerSegment",
  "CustomerSegmentMembership",
  "MarketingConsent",
  "CampaignInteraction",
  "AttributionEvent",
  "SyncLog"
CASCADE;