Kōwhai College nightly student-data sync

Duplicates. 2,380 records at 100 per page means at least 24 page requests. At 60 requests/minute, a 24-page crawl should take under 30 seconds, yet the trial took 75 minutes. That gap suggests the script either slept between calls or retried on timeouts without checking whether the previous write had succeeded. A timeout is ambiguous: the server may have committed the write before the client gave up. Retrying blindly therefore creates duplicates. The fix is idempotency: each record needs a stable key (e.g. SMS student ID) and the analytics platform must upsert on that key, not insert. The client should also use a deterministic request ID per record and treat a timeout as “unknown”, then re-read the record's state before retrying. Exponential backoff with jitter should respect 429 headers rather than adding fixed sleeps.

Corruption. Macrons are a strong clue to an encoding mismatch. If the script read the API response as Latin-1 or used a default platform encoding when writing to CSV, “ā” and “ō” would be mangled. The design should enforce UTF-8 end to end, validate round-tripping of a known macron-bearing name, and reject a page rather than partially write it.

Ordering and deletion. Pagination without a stable sort can miss or repeat records if data changes mid-crawl. Sorting by student ID and syncing a high-water mark makes the crawl resumable. Soft-deleted students need an explicit tombstone, otherwise the analytics platform keeps stale rows.

Operational fit. A single part-time IT lead with a four-week deadline should not build custom retry logic. Using the vendor's supported bulk endpoint, or a managed integration tool with idempotent upserts, reduces the maintenance burden and the chance of an unnoticed 429.

The request is disproportionate to the stated purpose. “Improving matching” can be met with a pseudonymous key and a limited set of attributes. Names, dates of birth, and home addresses together form a high-risk identity cluster: they enable re-identification, and address data creates physical-safety risks if leaked. Under the Privacy Act 2020, the college must collect only what is necessary for a lawful purpose and must not use identifying information for a new purpose without a basis. Sending the full set to a third party is a disclosure that needs a clear purpose, safeguards, and transparency to students and caregivers.

The trade-off is real: matching quality may drop if the vendor only receives a key. A middle path is to send a salted hash of the student ID plus first name and year level for fuzzy matching, keeping date of birth and address at the college. If the vendor genuinely needs date of birth to disambiguate common names, the college should test whether a partial date (month and year) or a derived “age band” achieves the same match rate. Address should be excluded unless there is a documented operational need; analytics does not obviously require it.

Conditions: a data-processing agreement with breach notification, retention limits, and no onward disclosure; encryption in transit and at rest; audit logs of who queries the data; a documented retention period; and a review after the first term comparing match rates with and without the sensitive fields. If the vendor cannot meet those conditions, the college should keep the identity data on-premises and send only pseudonymised aggregates.

The two largest risks are data-integrity failure at scale and unmonitored rate-limit behaviour.

Data integrity: the trial already produced 3% duplicates and 41 corrupted names. Four weeks is enough to fix the design only if testing is targeted. I would build a reconciliation report that compares record counts, a checksum of names after normalisation, and a sample of 50 known tricky records (macrons, hyphens, double-barrelled surnames, students with no address). The test should run against a full copy of the SMS, not a 100-record sample, because pagination and timing bugs appear only at scale. A “dry-run diff” that lists inserts, updates, and tombstones before applying them lets a non-specialist verify the outcome.

Rate limiting: the trial ignored 429 responses and took 75 minutes. I would instrument the sync to log every request, response code, and retry, then run a deliberate rate-limit test by lowering the client's concurrency until the API returns 429. The acceptance criterion is that the sync backs off, completes within the 90-minute window, and produces zero duplicates. I would also test a mid-sync network drop and confirm the sync resumes without duplicating records.

Given one part-time lead, the plan should automate the reconciliation report and run it nightly, rather than relying on manual checks. If those two tests cannot pass in three weeks, the correct recommendation is to delay go-live rather than accept silent duplication in student records.