Sis Integration Patterns

SIS Integration Patterns

Use this skill when a practitioner needs to design or implement a bidirectional data integration between a Student Information System (SIS) and Salesforce Education Cloud. This skill covers the canonical Education Cloud data model objects, SIS-to-Salesforce upsert keying strategies, batch and near-real-time sync patterns, and enrollment status writeback flows.

Before Starting

Gather this context before working on anything in this domain:

• Confirm the Education Cloud API version deployed in the org. AcademicTermEnrollment and CourseOfferingParticipant are available from API v57.0; LearnerProfile from v63.0; CourseOfferingParticipant.DroppedDateTime from v64.0. Integration code targeting older API versions will silently omit fields or fail.
• Identify the SIS-native student identifier. Banner uses PIDM (a numeric surrogate key), PeopleSoft uses EMPLID (a string), Colleague uses student ID. This identifier must be mapped to a custom External ID field on Contact (and optionally on LearnerProfile) before any upsert pattern can work reliably.
• Determine whether the org uses a Person Account model or a Contact-only model. Education Cloud's AcademicTermEnrollment.LearnerContactId and LearnerProfile.ContactId are Contact lookups. Person Account implementations populate Contact records via the Person Account trigger infrastructure — upserts targeting Contact directly are affected by Person Account sharing rules and record type constraints.
• Confirm integration middleware and its error-handling capabilities. Bulk API 2.0 is the correct API surface for nightly batch loads; REST Composite API is appropriate for transactional writes of 25 records or fewer; MuleSoft Salesforce Connector handles Bulk API natively and provides Object Store for watermark persistence.

Core Concepts

Education Cloud Data Model Hierarchy for SIS Integration

The core SIS-relevant Education Cloud objects form a strict parent-child hierarchy:

Contact (student)
 └── AcademicTermEnrollment  (one per student per term; API v57.0+)
      └── CourseOfferingParticipant  (one per student per course section; API v57.0+)
           └── CourseOfferingPtcpResult  (grade result; API v57.0+)
LearnerProfile  (master-detail to Contact; one per student; API v63.0+)
PersonAcademicCredential  (earned degree or credential; API v59.0+)

Key field relationships from the Education Cloud Developer Guide (v66.0):

• AcademicTermEnrollment.LearnerContactId — required lookup to Contact.
• AcademicTermEnrollment.EnrollmentStatus — restricted picklist: Active, Dropout, Expelled, Graduated, No show, Other, Transferred, Withdrawn.
• CourseOfferingParticipant.AcademicTermEnrollmentId — lookup to AcademicTermEnrollment.
• CourseOfferingParticipant.CourseOfferingId — required lookup to CourseOffering.
• CourseOfferingPtcpResult.CourseOfferingParticipantId — required lookup to CourseOfferingParticipant.
• CourseOfferingPtcpResult.LetterGrade — string; NumericGrade — double; ParticipantResultStatus — restricted picklist: Fail, Incomplete, Pass, Withdraw.
• LearnerProfile.ContactId — master-detail to Contact; this is a create-only field.
• LearnerProfile.StudentIdNumber — string field for the institution-issued student identifier.
• PersonAcademicCredential.LearnerContactId — lookup to Contact; AchievedDate — date.

Parent records must exist before child records are upserted. Attempting to upsert a CourseOfferingParticipant before its parent AcademicTermEnrollment is committed produces a REQUIRED_FIELD_MISSING or INVALID_CROSS_REFERENCE_KEY error in Bulk API 2.0.

External ID Keying Strategy

Upsert operations require an External ID field to identify whether to insert or update a record. The SIS native identifier (PIDM, EMPLID, or equivalent) must be surfaced as a custom External ID field in Salesforce.

Recommended pattern:

• On Contact: create a custom field SIS_Student_ID__c (Text, External ID, Unique). Populate this field during the initial student load. All downstream upserts reference Contact via this field, not by Salesforce record ID.
• On LearnerProfile: the StudentIdNumber standard field is a plain string and is not an External ID — it cannot be used as an upsert key. Create a separate custom field SIS_LearnerProfile_ID__c (Text, External ID, Unique) if you need to upsert LearnerProfiles without first querying for the Salesforce record ID.
• On AcademicTermEnrollment, CourseOfferingParticipant, and CourseOfferingPtcpResult: create composite external IDs that encode SIS identifiers plus term/section codes (e.g., SIS_Enrollment_Key__c = PIDM|TermCode).

Without External ID fields, every batch job must first query Salesforce IDs, dramatically increasing API call volume and introducing race conditions.

Change Data Capture for Enrollment Status Writeback

Many SIS platforms require Salesforce to push enrollment status changes back (e.g., a student self-service drop in Salesforce must update the SIS). The supported patterns are:

1. Change Data Capture (CDC): Enable CDC on AcademicTermEnrollment. Subscribing middleware receives a change event payload including old and new EnrollmentStatus values. This is the lowest-latency, lowest-overhead pattern for writeback. Available for custom and standard objects; Education Cloud objects support CDC from API v47.0+.
2. Platform Events: If the SIS cannot consume CDC directly, use an Apex trigger or Flow on AcademicTermEnrollment to publish a custom Platform Event. The middleware subscribes to the Platform Event topic.
3. Outbound Messaging (legacy): Workflow-driven SOAP callback. Suitable only if the SIS already exposes a SOAP endpoint and the latency requirement is loose (up to 24 hours with retries).

CDC is preferred because it requires no custom Apex, supports replay from a durable event bus (72-hour retention), and includes field-level change detection.

Common Patterns

Pattern 1: Nightly Batch Upsert from SIS to Education Cloud

When to use: The SIS runs a nightly export of changed enrollment, grade, and student demographic data. Latency of up to 12–24 hours is acceptable.

How it works:

1. SIS exports a set of CSV or JSON files partitioned by object type (students, term enrollments, course participants, grades).
2. Middleware (MuleSoft, Boomi, or custom) reads each file and invokes Salesforce Bulk API 2.0 upsert jobs in dependency order:
   • Job 1: Upsert Contact on SIS_Student_ID__c
   • Job 2: Upsert LearnerProfile on SIS_LearnerProfile_ID__c (after Job 1 completes)
   • Job 3: Upsert AcademicTermEnrollment on SIS_Enrollment_Key__c
   • Job 4: Upsert CourseOfferingParticipant on SIS_CourseParticipant_Key__c
   • Job 5: Upsert CourseOfferingPtcpResult on SIS_GradeResult_Key__c
3. Failed records are written to an error log; a retry job processes them after a configurable delay.
4. A watermark timestamp (e.g., LastModifiedDate from the SIS extract) is stored in middleware persistent state (MuleSoft Object Store, database table) to support incremental loads on the next run.

Why not a simple insert: Without upsert on External ID, re-running the job on duplicate data creates duplicate records. Without the ordered job sequence, child upserts fail because parent IDs do not yet exist.

// Apex example — bulk upsert of AcademicTermEnrollment records using External ID
List<AcademicTermEnrollment__c> enrollments = buildEnrollmentList(sisExportRows);
Database.UpsertResult[] results = Database.upsert(enrollments,
    AcademicTermEnrollment__c.SIS_Enrollment_Key__c, false);
for (Database.UpsertResult r : results) {
    if (!r.isSuccess()) {
        logError(r.getErrors());
    }
}

Pattern 2: Near-Real-Time Enrollment Status Writeback via CDC

When to use: The institution requires the SIS to reflect enrollment status changes (adds, drops, withdrawals) made in Salesforce within minutes, not overnight.

How it works:

1. Enable Change Data Capture for AcademicTermEnrollment in Setup > Integrations > Change Data Capture.
2. MuleSoft (or another EDA-compatible middleware) subscribes to the /data/AcademicTermEnrollmentChangeEvent topic using the CometD protocol.
3. On each change event, check the changedFields payload for EnrollmentStatus.
4. If EnrollmentStatus changed, extract SIS_Enrollment_Key__c from the event payload and call the SIS update API to write the new status.
5. Acknowledge the event. Failed SIS calls are retried with exponential backoff; the CDC bus retains events for 72 hours, allowing catch-up after middleware downtime.

Why not polling: Polling SOQL queries consume API call quota and introduce latency proportional to poll interval. CDC delivers changes within seconds and has no per-event API cost against the REST/SOAP call limits.

Decision Guidance

Recommended Workflow

Step-by-step instructions for an AI agent or practitioner working on this task:

1. Identify SIS identifier and API version. Confirm which SIS platform is in use, the format of its student identifier (PIDM, EMPLID, etc.), and the Education Cloud API version in the target Salesforce org. API version determines field availability — verify LearnerProfile (v63.0+) and CourseOfferingParticipant.DroppedDateTime (v64.0+) are present if needed.

2. Design and deploy External ID fields. Create custom External ID fields on Contact (SIS_Student_ID__c), and composite-key External ID fields on AcademicTermEnrollment, CourseOfferingParticipant, and CourseOfferingPtcpResult. Confirm each field is marked Unique and External ID in the field definition. Do not use LearnerProfile.StudentIdNumber as an upsert key — it is not indexed as an External ID.

3. Scaffold the integration flow in dependency order. Implement upsert jobs that execute in the mandatory sequence: Contact → LearnerProfile → AcademicTermEnrollment → CourseOfferingParticipant → CourseOfferingPtcpResult → PersonAcademicCredential. Each layer must fully complete (and errors resolved) before the next layer runs.

4. Implement watermark-based incremental loads. Store the LastModifiedDate (or SIS-equivalent timestamp) of the last successfully processed batch run in durable middleware state (MuleSoft Object Store, a custom Salesforce object, or an external database). On subsequent runs, filter the SIS export to records modified after the watermark.

5. Enable CDC and build the writeback subscriber. If near-real-time writeback from Salesforce to SIS is required, enable Change Data Capture on AcademicTermEnrollment via Setup. Implement a middleware subscriber to the /data/AcademicTermEnrollmentChangeEvent topic. Filter on EnrollmentStatus field changes and invoke the SIS update API, with retry and dead-letter handling.

6. Test with production-scale data volumes. Run bulk upsert jobs against a full scratch org dataset. Verify that Bulk API 2.0 job completion rates and failed-record counts are within acceptable thresholds. Confirm CDC event throughput matches expected enrollment change rates using the Event Monitoring logs.

7. Review checklist before go-live. Run through every item in the Review Checklist below; do not mark the integration complete until all items pass.

Review Checklist

Run through these before marking work in this area complete:

• Custom External ID fields exist on Contact, AcademicTermEnrollment, CourseOfferingParticipant, CourseOfferingPtcpResult, and LearnerProfile (where upsert is needed); all are marked Unique and External ID
• Upsert job sequence enforces parent-before-child ordering: Contact → AcademicTermEnrollment → CourseOfferingParticipant → CourseOfferingPtcpResult
• Watermark timestamp is persisted in durable state after each successful batch run; incremental loads use the watermark
• Bulk API 2.0 is used for loads of more than 500 records per object per run (not REST single-record operations)
• Failed-record error logs are captured and surfaced to the integration operations team; a retry mechanism exists
• CDC is enabled on AcademicTermEnrollment (if writeback is required) and the middleware subscriber handles the 72-hour replay window
• LearnerProfile.ContactId is set only on insert (master-detail); update operations do not attempt to reassign this field
• Integration user has the minimum required object and field permissions; FLS blocks are tested, not assumed
• FERPA-sensitive fields (e.g., grades in CourseOfferingPtcpResult) are not written to non-Education-Cloud objects or exposed in integration logs

Salesforce-Specific Gotchas

Non-obvious platform behaviors that cause real production problems:

1. LearnerProfile.ContactId is a create-only master-detail field. Once a LearnerProfile is created, its ContactId cannot be updated via API. Attempting to upsert a LearnerProfile and change ContactId throws FIELD_INTEGRITY_EXCEPTION. If a student's Contact is merged or the SIS identifier changes, the old LearnerProfile must be deleted and re-created, not updated.

2. LearnerProfile.StudentIdNumber is not an External ID. Despite being the obvious field for storing a SIS identifier, StudentIdNumber is a plain Text field without External ID or Unique attributes. Upserting LearnerProfile by this field throws INVALID_FIELD in the Bulk API. A custom External ID field is required.

3. AcademicTermEnrollment.EnrollmentStatus uses a restricted picklist. Values not in the allowed set (Active, Dropout, Expelled, Graduated, No show, Other, Transferred, Withdrawn) cause INVALID_OR_NULL_FOR_RESTRICTED_PICKLIST errors in bulk loads. SIS enrollment status codes must be mapped to these canonical values before the upsert — a pass-through mapping of the SIS raw status string will fail.

4. Bulk API 2.0 processes records within a job in arbitrary order. If a single Bulk API 2.0 job contains both a parent AcademicTermEnrollment record and its child CourseOfferingParticipant records, the parent is not guaranteed to be processed before the child. Split parents and children into separate sequential jobs; never mix parent and child records in the same upsert job.

5. CDC events do not include field values for unchanged fields. When consuming a AcademicTermEnrollmentChangeEvent, only fields present in changedFields have updated values. Reading EnrollmentStatus from the payload when it is not in changedFields returns null, not the current value. Middleware must either store prior state or make a follow-up SOQL query to get the full current record state.

Output Artifacts

Related Skills

• ferpa-compliance-in-salesforce — when the SIS integration involves student academic records, FERPA compliance controls on LearnerProfile and ContactPointTypeConsent must be reviewed alongside the integration design
• data/sharing-recalculation-performance — large-volume SIS loads often trigger sharing recalculation; review performance implications when record ownership or sharing rules are modified as part of the sync