Building a roster sync that survives the school year

A roster sync looks like a nightly ETL job. It's closer to a replication protocol against a source you can't lock, can't query historically and don't control, whose correctness is judged by a teacher standing in front of thirty children at 08:15.

Here's how to build one that holds.

The rule everything else follows from

Never replace the world.

The tempting nightly job is: truncate the roster tables, load the feed, done. Simple, obviously correct in the naive sense, and it destroys your product.

Every internal id you issued is gone, so every foreign key from your own domain (submissions, progress, notes, groups a teacher made by hand) points at nothing. You can't answer "who was in this class in March" because you overwrote March. And you can't distinguish "this student left" from "the export failed".

The alternative is a diff, in five stages, each owning a specific failure.

Stage 1: Fetch

Pull the payload into raw storage before you interpret it. Two properties matter.

Idempotent and resumable. A run that dies on page 400 of 900 should resume, not restart. Large districts take long enough that "just run it again" costs you the window.

Alert on absence, not only on error. The most common production failure here is a file that never arrives, or an endpoint returning 200 with an empty page because somebody rotated a credential. Neither raises an exception. Set an expected-by deadline per district and alert when it passes.

Stage 2: Stage

Land the raw rows in staging tables that mirror the source shape, not your domain shape. Keep a run id.

This is where schema drift surfaces. A district updates their SIS in October, the export gains a column, and either your parser is strict and the run fails, or it's lenient and silently drops a field you needed. Log unknown columns as a warning instead of picking either extreme.

Keep the staged payload for a bounded window, days rather than forever, written into your retention schedule. When a teacher reports a wrong class, the first question is always what the district actually sent. Answering it in two minutes instead of two days is the difference between a support ticket and an escalation.

Stage 3: Validate, the stage that saves you

Before anything is applied, run sanity checks against the previous successful run. This one stage prevents most catastrophic roster incidents.

Check	What it catches
Row count collapse	A truncated export, which looks identical to a mass withdrawal
Org disappearance	A sharing change or partial export, almost never a closed school
Enrolment ratio outside a plausible band	A join that half-completed
100% of rows changed	A provider stamping dateLastModified rather than tracking changes
Nothing changed for days	An upstream job failing quietly

The response to a failed check is quarantine and alert. Not abort silently, and not apply anyway. The run stops before the apply stage, yesterday's data stays live, and somebody gets told.

A district would far rather be a day stale than wrong.

Stage 4: Diff

For each resource, compare the staged row to what you hold:

sync_records (source, resource_type, external_id,
              last_hash, last_seen_at, status)

Hash the meaningful fields of the source row, deliberately excluding dateLastModified. Compare to last_hash. That gives you creates, updates and no-ops without trusting the provider's change tracking.

Deletes are the subtle part, and there are two cases that must not be conflated.

Explicit deletion. The row arrived with status: tobedeleted. Act on it: end the enrolment, hide the class. Don't destroy history.

Absence. The row simply isn't in this payload. That is not a deletion. It may be a delta payload containing only changes. It may be a partial share. It may be a failed export.

Only treat absence as deletion when you received a full payload that you validated, and even then prefer marking last_seen_at and sweeping after several consecutive full runs.

Teams that treat absence as deletion produce the classic incident: a partial export at 02:00, and by 08:15 every teacher in the district has an empty class list.

Stage 5: Apply

Apply in dependency order (orgs, sessions, courses, classes, users, enrolments) so references resolve. Transaction per resource type rather than one enormous transaction, so a failure in enrolments doesn't roll back six hours of user updates.

And separate visibility from retention. This is the most important semantic distinction in the whole pipeline.

A section marked tobedeleted in June means the school year ended. The right response is to end the enrolments and stop surfacing the class. The wrong one is deleting the student work attached to it. Districts have lost a year of evidence to a vendor that treated a soft-delete flag as an instruction, and the vendor was technically following the feed.

Retention is governed by your contract and by state law. The feed has no opinion on it.

The day the district migrates SIS

All of the above assumes you resolve external identifiers to internal ones through the identities table from the data model of a school.

Here's the scenario that justifies it. A district migrates from one SIS to another over the summer. Every sourcedId changes. If that was your primary key, migration day orphans every piece of student work for every child in the district, and no query fixes it because the mapping never existed.

With an identities table, migration day is a mapping exercise. You receive a crosswalk, or you match on a stable secondary attribute (the SIS-local student number is usually preserved, since it's what district staff use), and you insert new identity rows pointing at the same internal users.

Build the re-key path before you need it: accept a crosswalk file, run it in dry-run mode producing a report of matched, ambiguous and unmatched, and require a human to approve before it commits. You'll use it more than once.

The August problem

Everything above is steady state. August isn't steady state.

In July, next year's classes exist with no teachers assigned. In early August, schedules change nightly at volume. In late August, students are still being enrolled. On day one everything has to work, and for three weeks after it, add/drop churn continues at a rate you won't see again all year.

Four things this demands:

Tolerate an incomplete roster without erroring. A class with no teacher is normal in July. Represent it, don't reject it.

Give teachers a manual re-sync. Per class, self-serve, no ticket, backed by an NRPS call or a targeted fetch. This single feature removes more September support load than anything else, because the honest answer to "my roster is wrong" is often "the district fixed it twenty minutes ago and we sync at 02:00", and a button turns that into a non-event.

Increase sync frequency for the rollover window. If you run nightly for ten months, run every few hours in the last two weeks of August. The cost is a fortnight of compute.

Rehearse it. Run last year's rollover against a copy in June, with the real timings. Rollover is the highest-risk operation your pipeline performs and it happens once a year, which is exactly the frequency at which nobody remembers how it went.

Observability the district can use

Standard job metrics don't answer the only question that matters operationally: is this district okay right now?

Per district, per run, track and expose the last successful run, rows created, updated and ended, validation results, classes with zero students, classes with no teacher, and users with no enrolment. Those last three are what support will use every day, because they correspond to what a person actually notices.

Then make it visible to the district's own administrator. A technology director who can see their sync status without emailing you generates a fraction of the tickets, and when something is genuinely wrong on their side, they find it before you do.