Case study

A student information system for a multi-campus school under three regulators

Grades, attendance, and finances for four campuses, feeding reports to three regulators. I designed the data model and led the team that built and ran it.

The constraint

LSI ran four campuses, each teaching the same programs to students who could transfer between them. The school answered to three regulators with different definitions of the same facts: the Bureau for Private Postsecondary Education (BPPE), which licenses private schools in California; ACCET, its accreditor at the time (the school has since moved to CEA); and, because most students were on F-1 visas, the Department of Homeland Security through SEVIS reporting.

When I arrived, everything lived in a Microsoft Access database that only a few people could write to. Everyone else filled out paper forms and waited for them to be keyed in. Each campus kept its own version of attendance, grades, and payments, and reporting season meant reconciling them by hand.

What made it non-obvious:

• The same record meant different things to different regulators. Attendance is the usual one: the accreditor wants attendance as a share of scheduled hours for academic progress, the licensing bureau wants it for enrollment and refund calculations, and immigration wants to know the day a student drops below full-time so the school can report it within the deadline. One attendance table, three derived facts, three sets of deadlines.
• Corrections had to be auditable, not just possible. Instructors fix attendance after the fact. Finance adjusts charges. Every one of those changes is something a regulator can ask about later, so the system could not simply overwrite. It had to keep what was there, who changed it, and when.
• Retention outlasts the software. BPPE requires student records to be kept for years after a student leaves, and transcripts for far longer. In practice that meant keeping student files indefinitely in cold storage. The data model had to assume it would outlive the application.
• Every campus had its own workflow, and the regulators did not care. Consistency across campuses was a reporting requirement, but each site had staff, schedules, and habits built around how they already worked.

Options considered

1. Buy a commercial student information system. We looked at Moodle, which is a learning platform rather than a student records system, and at paid SIS products. Off-the-shelf systems assume a semester structure and US-domestic reporting. A multi-campus ESL school with rolling starts and visa reporting would have needed heavy customization, at license cost the school did not want to carry.
2. Keep the per-campus tools and build a reporting layer on top. Cheapest first step. But it leaves the source data inconsistent, so the reports are only as trustworthy as the reconciliation, and the reconciliation is exactly the manual work that was already failing.
3. Build a single system with one data model, applied identically at every campus. Highest upfront cost and the school takes on a development team. In exchange: one definition of every regulated fact, one audit trail, and reports generated instead of assembled.

What was chosen and why

Option 3. We built the SIS from scratch: one system serving all four campuses, with reports shaped to what the school and each regulator actually asked for.

The tradeoff accepted: the school became dependent on a small in-house team and on me for the design, instead of on a vendor. That is a real risk for a school and I would name it in an interview. It was worth it because the reporting obligations were specific enough that a generic product would have needed the same team anyway, just working around someone else’s data model.

The data model was designed around the regulators, not the screens. Each regulated fact, attendance, grades, charges and payments, enrollment status, was modeled once. Every change to one of those records was written to a history table with the author and the time, so the original entry was never lost. The screens for campus staff, the accreditor’s report, the state’s annual report, and the immigration reporting were all derived from the same records. A correction could always be traced back to what it replaced.

Design details:

• Audit trail: history tables plus an application log, visible only to administrators.
• Multi-campus consistency: one database, with each campus in its own set of tables on an identical schema. That isolated campus data by default and made campus-level access control simple. The cost was that cross-campus reports and student transfers had to span table sets instead of filtering one table. A transfer ran as a scripted job that moved the student’s records, history included, from one campus’s tables to another’s.
• Access control: Instructor, campus admin or coordinator, corporate, finance, agent, and student. Regular staff could see only their own campus; administrators could see all campuses.
• Regulatory reports: BPPE annual report, ACCET attendance and progress, SEVIS updates, and the status letters students request from the school. Generated from the same records the staff used day to day, so there was no separate “reporting database” to drift.
• Retention and export: records were kept in encrypted storage on AWS, separate from the application database.
• The e-book app: a separate web app that let students use phones and tablets instead of printed books. Students were paying for new books every session, and the school was printing books it could not always sell. Moving to e-books removed both costs.

What happened

• BPPE auditors were given their own login and audited the system directly.
• The SIS went live campus by campus, replacing the Access database at each site before moving to the next.
• The e-book app was rolled out campus by campus, starting with one small class.
• The system is still in use today by staff, students, and agents.
• BPPE and ACCET reports, attendance records, and transcripts were generated from one place instead of assembled by hand, and could be printed on request.
• Document signing moved online, which cut turnaround time and paper storage.

If I did it again

The first version was built in plain PHP because of constraints at the time; later modules moved to Laravel and Vue. I would have pushed harder for a framework from day one. The plain-PHP core did the job, but every later module paid for it in integration work.

---

Open to technical lead and solutions architect roles, remote. Get in touch or read the other case studies.