Bridging a Legacy Student ERP to Salesforce Education Data Architecture

// EDA Canonical Mapping: Legacy Student Record → EDA Objects
// Legacy ERP fields map to Contact, Affiliation, ProgramPlan, CourseConnection

legacyStudent = {
  student_id:   "STU-2024-00142",
  first_name:   "Maya",
  last_name:    "Rodriguez",
  email:        "[email protected]",
  program:      "BS Computer Science",
  enrollment_status: "active",   // → Affiliation.Status
  cohort_year:  2024,
  gpa:          3.71
}

// Webhook payload sent to Salesforce EDA on record change
POST /services/apexrest/eda/webhook/studentSync

{
  "event":       "student.updated",
  "timestamp":   "2024-03-15T09:22:11Z",
  "source":      "legacy-erp",
  "payload": {
    // Contact (primary person record)
    "Contact": {
      "Email":       "[email protected]",
      "FirstName":   "Maya",
      "LastName":    "Rodriguez",
      "University_ID__c": "STU-2024-00142"
    },
    // Affiliation: links Contact → Account (Academic Program)
    "Affiliation": {
      "Account__c":  "acct_id_for_CS_program", // lookup to Account
      "Role__c":     "Student",
      "Status__c":   "Current",                // map from enrollment_status
      "StartDate__c": "2024-08-20"
    },
    // ProgramPlan: captures declared program with cohort
    "ProgramPlan": {
      "Account__c":    "acct_id_for_CS_program",
      "EnrollmentStatus__c": "Full-Time",
      "ClassYear__c": 2024
    }
  }
}

// Apex trigger handler receiving the webhook
@RestResource(urlMapping='/eda/webhook/studentSync')
global class EDA_StudentWebhook {

  @HttpPost
  global static void handleStudentEvent() {
    Map<String, Object> req = (Map<String, Object>)
      JSON.deserializeUntyped(RestContext.request.requestBody.toString());

    Map<String, Object> pl = (Map<String, Object>) req.get('payload');
    Map<String, Object> c  = (Map<String, Object>) pl.get('Contact');
    Map<String, Object> aff= (Map<String, Object>) pl.get('Affiliation');

    // Upsert Contact by external ID
    Contact con = [SELECT Id FROM Contact
                   WHERE University_ID__c = :c.get('University_ID__c')];

    con.Email   = (String) c.get('Email');
    con.FirstName = (String) c.get('FirstName');
    con.LastName  = (String) c.get('LastName');
    update con;

    // Upsert Affiliation (replaces stale nightly batch)
    Affiliation__c affil = new Affiliation__c(
      Contact__c     = con.Id,
      Account__c     = (String) aff.get('Account__c'),
      Role__c        = (String) aff.get('Role__c'),
      Status__c      = (String) aff.get('Status__c'),
      StartDate__c   = Date.valueOf((String) aff.get('StartDate__c'))
    );
    upsert affil Contact__c Account__c; // composite key
  }
}

How we'd take this from discovery to a production-safe cutover — the phases, the canonical mapping, and the edge cases that bite.

Salesforce EDA models people and their academic relationships as a constellation of Contacts, Accounts, and Affiliations—each affiliation record capturing a role (Student, Applicant, Faculty) against an Account that represents a program, department, or institution. The legacy Student ERP, by contrast, stores enrollment as rows in a relational schema with student IDs, program codes, and term keys. This integration replaces a stale nightly batch with a webhook-driven bridge that maps registrar records to the EDA affiliation graph in near-real time.

Implementation Phases

1. Data Profiling and Schema Audit. Export the legacy ERP's core student, enrollment, and program tables. Document STUDENT_ID, PROGRAM_CD, ENROLL_STATUS, TERM_ID, ADVISOR_ID, and MATRICULATION_DT field semantics. Identify which columns are nullable, which codes are active vs. historical, and whether the ERP uses surrogate keys or natural identifiers. This profile informs the mapping layer and surfaces hidden gaps (e.g., missing birth dates, ambiguous name prefixes).
2. EDA Object Architecture Design. Define the Account hierarchy: one root institution Account, then child Academic Program Accounts derived from PROGRAM_CD. Each student's active enrollment becomes an Affiliation record linking their Contact to the appropriate Program Account with Role__c = 'Student' and Status__c set from ENROLL_STATUS. Advisor assignments become a second Affiliation on the same Program Account with Role__c = 'Faculty Advisor', referencing the advisor's Contact record.
3. Webhook Endpoint and Middleware Scaffolding. Configure the legacy ERP to emit HTTP POST events on INSERT/UPDATE to student enrollment endpoints. Build a middleware listener (e.g., MuleSoft, a serverless function, or a dedicated iPaaS connector) that receives the payload, validates the signature, and queues events for idempotent processing. Every webhook must include STUDENT_ID and a change-type indicator (CREATED, UPDATED, WITHDRAWN).
4. Canonical Field Mapping and Transformation Logic. Implement the mapping table (below) as idempotent upsert logic: match on EmployeeNumber for Contact lookup, match on Contact+Account+Role for Affiliation lookup. Use LastModifiedDate from the webhook payload to prevent stale writes. Transform legacy codes (e.g., ENROLL_STATUS = 'A') to EDA picklist values (Active).
5. Error Handling, Idempotency, and Dead-Letter Queue. Wrap each event processor in try/catch. On failure, write the original payload to a dead-letter object or external log with ERROR_REASON__c. Implement deduplication using a custom Legacy_Event_Key__c field (composite of STUDENT_ID + TERM_ID + CHANGE_TS) so re-delivered webhooks do not create duplicate Affiliations.
6. Initial Load (Bootstrap Sync). Before activating webhooks, run a one-time batch export from the ERP to backfill all historical Affiliations. Use bulk API 2.0 or batch Apex to upsert Contacts first (matching on EmployeeNumber), then upsert Affiliations (matching on Contact ID + Account ID + Role). Set IsActive__c on current-term affiliations only.
7. Parallel Validation and Reconciliation Report. Run the legacy ERP and EDA side-by-side for two academic terms. Generate a reconciliation CSV comparing record counts and field values per program. Flag Contacts missing affiliations, Affiliations with mismatched StartDate__c, and duplicate Contacts.
8. Go-Live Cutover. Enable webhook listeners in production. Disable the nightly batch job. Monitor for 72 hours: watch Affiliation counts per program, error rates in the dead-letter queue, and Contact duplication alerts. Retain the batch job in disabled state for 30 days as a fallback rollback path.

Canonical Field Mapping

Edge Cases

• Duplicate Contacts. If the legacy ERP issues a new STUDENT_ID to a returning student (re-admit), the bootstrap load will create a second Contact. Mitigation: match on Email or Birthdate + LastName in addition to EmployeeNumber during initial load.
• Advisor not yet onboarded to Salesforce. Webhooks referencing ADVISOR_ID for a Contact that does not exist in EDA must be held in a pending-lookup state or logged to a custom Pending_Advisor_Relationship__c object for manual resolution.
• Program Account missing. A webhook arrives for PROGRAM_CD = 'UNDECLARED' before the corresponding Program Account is created. Route to dead-letter queue; create the Account manually or via a separate provisioning webhook, then reprocess the held event.
• Stale Status Transition. A UPDATED webhook arrives out-of-order (e.g., Withdrawn sent before Active). Use CHANGE_TS as the system-of-record timestamp; discard events older than the current LastModifiedDate on the existing Affiliation.
• Bulk Term Rollover. At term end, the ERP may emit thousands of enrollment-close webhooks simultaneously. Throttle the middleware to 10 concurrent updates and leverage Salesforce bulk API for Affiliation updates rather than REST.
• Null BIRTH_DT. EDA requires a Contact birthdate for some reporting features. If null, set Birthdate = 1900-01-01 as a sentinel and flag Birthdate_Verified__c = false.

Cutover

The cutover is reversible because the nightly batch job is retained in disabled state, not deleted, and the legacy ERP's webhook emission can be toggled off at the source if a rollback is required. Reconciliation is achieved by running a post-cutover report that aggregates Affiliation counts by Program Account and compares them against a COUNT(*) of active enrollment rows in the legacy ERP, grouped by PROGRAM_CD and ENROLL_STATUS. Any discrepancy exceeding a 0.1% threshold triggers an alert for investigation before the batch job is decommissioned after the 30-day hold period. During cutover, schedule a 4-hour freeze window where no new ERP enrollments are processed via webhooks; backfill that window immediately after go-live using the bootstrap export.