Enrollment Automation: Idempotent Sync Between a Student System and an LMS

Enrollment looks like a simple mapping: this student belongs in this course. In practice there are two systems that each believe they own the record, a term that starts on Monday, and a registrar who changed a section number at 4:55 on Friday. The integration fails not because the mapping is hard, but because nobody defined which side is authoritative and what happens on a retry.

This is a reference design for enrollment synchronization between a student information system (SIS) and a learning platform. It is not a report of a BuildVault deployment.

Write down the contract before writing code

Three questions decide most of the design:

1. Who is the source of truth for each field? Usually the SIS owns enrollment state and the LMS owns activity. Say so explicitly, per field.
2. Which direction does data flow? A one-way pull from the SIS is far simpler than bidirectional sync. If the LMS can also create enrollments, you now need conflict rules, and those rules need an owner.
3. How fresh must it be? Batch overnight is often sufficient. Real-time per-section changes cost more and need a queue with its own monitoring.

Write the answers into the repository as documentation. The next person to debug a missing student needs to know whether the record was rejected by a rule or never sent.

Idempotent upserts, keyed by stable identifiers

Retries are normal: a scheduled job overlaps, a webhook delivers twice, an operator re-runs a batch. Make applying an enrollment change a no-op when it has already been applied.

-- One row per (student, section), with history kept in an audit table.
INSERT INTO enrollment (sis_section_id, sis_person_id, status, source_updated_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (sis_section_id, sis_person_id)
DO UPDATE SET
  status = EXCLUDED.status,
  source_updated_at = EXCLUDED.source_updated_at
WHERE enrollment.source_updated_at < EXCLUDED.source_updated_at;

The guard on source_updated_at matters: without it, a delayed retry of an older message can reactivate a student who has since been dropped. That is the failure mode that ends up in a support queue, because the LMS insists an enrollment exists while the registrar insists it does not.

Treat identifiers as immutable. If your key is an email address or a display name, a correction in the SIS becomes a delete plus a create, and the activity history is orphaned.

Reconcile instead of trusting

Event-driven sync handles the common case; reconciliation handles the drift. Run a periodic diff:

• pull the SIS's current enrollment set for the active term
• compare it with the LMS's applied state
• report additions, removals, and fields that disagree
• apply the differences through the same idempotent path as live events

Send the report somewhere a human reads it. A reconciler that silently fixes drift hides the bug that caused it, and the next drift will be larger.

Separate access from the record

Two related but distinct questions:

• Access: can this person open the course right now? This can be derived and recomputed freely.
• Record: was this person enrolled in this section during this term, and who changed it? This needs to be durable and auditable.

Conflating them makes term rollover dangerous, because clearing access for the new term also erases the evidence of the previous one. Keep the audit log append-only and include who or what made each change, the source timestamp, and the payload as received.

Failures that show up in every deployment

• Partial batches. A batch that fails at record 400 of 900 leaves a half-applied term. Make batches resumable, or apply each record in its own transaction so a single bad row cannot block the rest.
• Timezone drift. Term start dates compared in local time create a window where enrollment is open in one system and closed in the other. Store and compare in UTC.
• Duplicate sections. Two SIS sections that map to one LMS course need an explicit merge decision, not a race to see which row wins.
• Dropped students with activity. The removal rule needs a retention policy: revoke access, keep the grade and submission history, and record why.

A validation checklist

1. Re-sending the same batch twice changes nothing except the audit log.
2. An out-of-order event does not overwrite newer state.
3. A reconciliation run against a deliberately drifted dataset restores both sides and produces a readable report.
4. A batch that fails mid-way can be resumed without duplicating enrollments.
5. Term rollover revokes access in the new term while preserving the previous term's records.
6. Every enrollment change can be traced to a source event or to a named operator.

The same failure-first approach applies to session and cache layers, where a stale copy can grant access the source of truth has already revoked — see scaling authentication. For standards-based interoperability, the OneRoster specification defines a common CSV and REST model for exactly this SIS-to-platform exchange; adopting it is usually cheaper than inventing a bespoke payload.