Protecting Sensitive Records

A transcript-analysis system holds exactly the data an education agency is most accountable for: student coursework, grades, identifiers, and determinations about a person's career. In the U.S. this lives in FERPA territory — education records with legal protection. You're a developer, not the agency's counsel, so the working posture is: treat student data as regulated, let policy staff say precisely how, and build so the strict interpretation is cheap. Four engineering habits do most of the work.

Least privilege, in the app and the database

Least privilege means every identity — human or service — gets the minimum access its job requires. In the app, that's the role and per-record ownership checks from lesson one: an EPP sees its own candidates, Commission staff see their review queue, nobody gets "all records" for convenience. Mirror it in SQL Server: the web app's login should be a low-privilege user with rights on the tables it needs — not db_owner, never sa. Reporting jobs get a read-only login. When (not if) a component is compromised, privilege boundaries decide whether that's an incident or a breach notification.

Encryption in transit and at rest

In transit is table stakes: TLS on every hop, including app-to-database (Encrypt=True in the connection string) — not just the browser leg. At rest, SQL Server's Transparent Data Encryption (TDE) encrypts the database files wholesale, which answers "what if someone steals a backup?" For the narrow set of high-value columns (SSNs, birthdates), Always Encrypted keeps values encrypted even from DBAs. The pragmatic layering: TDE plus TLS everywhere as baseline, column-level encryption only where policy demands it — it complicates querying, so scope it deliberately.

Audit trails: who saw what, who changed what

Public-sector systems must answer, months later, "who viewed this candidate's record and who changed this determination?" That requires an audit trail designed in, not grepped from web-server logs. Minimum viable: an append-only audit table recording user, timestamp, action, record id, and before/after values for every create/update/delete on sensitive entities — written from your service layer or EF's SaveChanges override so no code path skips it. For determinations, prefer soft history (a status-history table) over destructive updates: the row that says "requirement met" should never be silently overwritten.

Retention: keeping data is a liability too

Every record kept is a record that can leak. Agencies have retention schedules — how long each record class must be kept, and when it must be disposed of. Your job: make retention implementable. Timestamp everything, design deletes/anonymization as real supported operations (harder than it sounds once audit rows and foreign keys reference a candidate), and never squirrel production PII into logs, temp tables, or developer machines for debugging. Masked or synthetic data belongs in every environment below production.