Phase 7: Backfill

MCA v1 data migration.

---

Overview

This phase migrates existing customer data from MCA v1 (ZA and UK) into the new CRM platform.

See Backfill Strategy for detailed migration plan.

---

Key Steps

1. Export MCA v1 ZA data
2. Export MCA v1 UK data
3. Transform to CRM schema
4. Run identity resolution
5. Load into CRM database
6. Verify and reconcile

---

1. Export Scripts

MCA v1 ZA Export

// scripts/backfill/export-mca-za.ts

async function exportMcaZa(): Promise<void> {
  const members = await mcaZaDb.query(`
    SELECT
      id,
      email,
      phone_number,
      first_name,
      last_name,
      membership_tier,
      handicap,
      created_at
    FROM members
    WHERE status = 'active'
  `);

  await writeFile('mca-za-export.json', JSON.stringify(members, null, 2));
}

---

2. Transformation

// scripts/backfill/transform.ts

interface McaV1Record {
  id: string;
  email: string;
  phone_number: string;
  first_name: string;
  last_name: string;
  membership_tier: string;
  handicap: number;
  created_at: string;
}

interface CrmImportRecord {
  externalIds: { mcaV1ZaId: string };
  email: string;
  phoneNumber: string;
  firstName: string;
  lastName: string;
  membershipTier: string;
  handicap: number;
  createdAt: Date;
}

function transform(record: McaV1Record): CrmImportRecord {
  return {
    externalIds: { mcaV1ZaId: record.id },
    email: record.email?.toLowerCase().trim(),
    phoneNumber: normalizePhone(record.phone_number),
    firstName: record.first_name,
    lastName: record.last_name,
    membershipTier: record.membership_tier,
    handicap: record.handicap,
    createdAt: new Date(record.created_at),
  };
}

---

3. Import Service

// libs/crm/core/src/services/import.service.ts

@Injectable()
export class ImportService {
  constructor(
    private readonly prisma: PrismaService,
    private readonly identityService: IdentityResolutionService,
  ) {}

  async importBatch(
    tenantId: string,
    records: CrmImportRecord[],
    source: string,
  ): Promise<ImportResult> {
    const results = {
      total: records.length,
      created: 0,
      updated: 0,
      skipped: 0,
      errors: [] as string[],
    };

    for (const record of records) {
      try {
        const { profile, isNew } = await this.identityService.resolve({
          tenantId,
          email: record.email,
          phoneNumber: record.phoneNumber,
          externalIds: record.externalIds,
        });

        // Update profile with imported data
        await this.prisma.customerProfile.update({
          where: { id: profile.id },
          data: {
            firstName: profile.firstName || record.firstName,
            lastName: profile.lastName || record.lastName,
            membershipTier: record.membershipTier,
            handicap: record.handicap,
            externalIds: {
              ...((profile.externalIds as object) || {}),
              ...record.externalIds,
            },
          },
        });

        if (isNew) {
          results.created++;
        } else {
          results.updated++;
        }
      } catch (error) {
        results.errors.push(`${record.email}: ${error.message}`);
      }
    }

    return results;
  }
}

---

4. Reconciliation

After import, verify data integrity:

// scripts/backfill/reconcile.ts

async function reconcile(): Promise<void> {
  // Count comparison
  const mcaCount = await mcaZaDb.query('SELECT COUNT(*) FROM members');
  const crmCount = await crmDb.query(
    `SELECT COUNT(*) FROM "CustomerProfile" WHERE "externalIds"->>'mcaV1ZaId' IS NOT NULL`
  );

  console.log(`MCA ZA: ${mcaCount}, CRM: ${crmCount}`);

  // Sample verification
  const sample = await mcaZaDb.query('SELECT * FROM members LIMIT 10');
  for (const record of sample) {
    const crm = await crmDb.query(
      `SELECT * FROM "CustomerProfile" WHERE "externalIds"->>'mcaV1ZaId' = $1`,
      [record.id]
    );

    if (!crm) {
      console.error(`Missing: ${record.id}`);
    } else if (crm.email !== record.email.toLowerCase()) {
      console.error(`Email mismatch: ${record.id}`);
    }
  }
}

---

Next Steps

After completing Phase 7, proceed to Phase 8: Intelligence.