Building a FERPA-Compliant SARB Tracker From Scratch

Every year around March, the SARB coordinator at my district starts asking office staff for "the packet." That means: attendance printouts from Aeries, copies of truancy letters that may or may not have been scanned, handwritten contact logs from a binder, and SART meeting notes that live in someone's email. California Education Code §48260-48273 requires schools to document a specific intervention chain before referring chronically absent students to the Student Attendance Review Board. The documentation requirement is clear. The tooling is nonexistent.

I'm building the tool. This post is about where it is right now -- what's real, what almost went wrong, and what's still waiting on credentials I don't have yet.

The Hard Call: Dual-Engine Isolation

The first architectural decision was the most important one. Aeries (our student information system) is the source of truth for student identity and attendance. My app needs to read from it. But FERPA means I cannot write back to it, and I cannot let a bug in my sidecar logic accidentally execute against the district's production database.

The solution is two completely separate SQLAlchemy engines built from independent connection strings:

from sqlalchemy import Engine, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import StaticPool

AERIES_DB_URL = os.getenv("AERIES_DB_URL", "sqlite:///:memory:")
SIDECAR_DB_URL = os.getenv("SIDECAR_DB_URL", "sqlite:///:memory:")

def _build_engine(url: str) -> Engine:
    if "sqlite" in url:
        return create_engine(url, connect_args={"check_same_thread": False}, poolclass=StaticPool)
    return create_engine(url)

aeries_engine = _build_engine(AERIES_DB_URL)   # Read-only. Never INSERT/UPDATE/DELETE.
sidecar_engine = _build_engine(SIDECAR_DB_URL) # Read/write. Interventions + audit log.

AeriesSessionLocal = sessionmaker(bind=aeries_engine)
SidecarSessionLocal = sessionmaker(bind=sidecar_engine)

This looks simple but it caught a real bug early. The original code checked if "sqlite" in url against the Aeries URL to decide connection args for both engines. In the docker-compose setup, Aeries is MSSQL and the sidecar is SQLite. The sidecar engine was getting MSSQL connection settings. Tests passed because both were SQLite in-memory during testing. It would have failed silently in production -- the sidecar would connect but with wrong pool behavior.

The fix was obvious once caught: each engine checks its own URL independently. But it's the kind of bug that only manifests when your test environment doesn't match your deployment topology -- which is always.

The FERPA Boundary as Code

FERPA compliance isn't a checkbox you add at the end. It's a constraint that shapes every model. The approach: Pydantic models with extra="forbid" act as an allowlist. If a field isn't explicitly in the schema, it can't serialize out of the API.

from pydantic import BaseModel, ConfigDict

class FerpaModel(BaseModel):
    """Base model enforcing strict schema boundaries to prevent PII leakage."""
    model_config = ConfigDict(extra="forbid")

class StudentInfo(FerpaModel):
    student_id: int
    school_code: int
    first_name: str
    last_name: str
    grade: int
    # That's it. No SSN, no address, no parent info, no IEP flags.
    # Adding a field requires explicit justification in code review.

And the audit contract -- every read of student data must call write_audit() before returning:

def write_audit(
    actor: str,
    action: str,            # "read", "create", "export", "report"
    resource: str,          # "attendance/994/1001"
    processing_mode: str,   # "local"
    third_party_disclosure: str  # "None" or "LACOE SARB"
) -> None:
    with SidecarSessionLocal() as db:
        db.execute(text(
            "INSERT INTO audit_log "
            "(request_id, actor, action, resource, processing_mode, third_party_disclosure) "
            "VALUES (:request_id, :actor, :action, :resource, :mode, :disclosure)"
        ), {...})
        db.commit()

This is enforced by convention and testing -- every test that reads student data asserts an audit row was written. There's no way to skip it without a test failing. If I ever add a new route that reads from Aeries, the test pattern catches the missing audit call.

Classification Priority: Where I Almost Got It Wrong

The attendance classification engine maps student absence data to five tiers. The first version had a bug that wouldn't have shown up until a real SARB hearing:

def classify_attendance(student_id, present_count, excused_count, unexcused_count, tardy_count) -> str:
    total_days = present_count + excused_count + unexcused_count
    if total_days == 0:
        return "clean"

    absence_pct = ((excused_count + unexcused_count) / total_days) * 100
    unexcused_pct = (unexcused_count / total_days) * 100

    # Unexcused tiers MUST come first -- they trigger legal actions
    if unexcused_pct >= 10.0:
        return "chronic_truant"       # EC §48263.6 -- immediate SARB referral
    if unexcused_count >= 3:
        return "truant"              # EC §48260 -- legal notification trigger
    if absence_pct >= 10.0:
        return "chronic_absentee"    # EC §60901(c)(1) -- intervention required
    if absence_pct >= 5.0:
        return "at_risk"
    return "clean"

The original code checked chronic_absentee (10% total absences) before truant (3+ unexcused). A student with 14% total absences AND 4 unexcused days would have been classified as chronic_absentee -- which triggers intervention but not the legal notification required under EC §48260. Wrong classification, wrong workflow, potential compliance failure at a SARB hearing.

The fix was reordering the checks: unexcused-based tiers always evaluate first because they carry legal weight. I added a regression test specifically for the overlap case. This is the kind of bug that passes code review but fails at the courthouse.

What's Honestly Not Done

That's the honest state. The architecture is production-grade. The test coverage is real. The compliance patterns are correct. But until real attendance data flows through the classification engine and a real admin reviews the output, it's an untested prototype with good bones.

What's Next

Three things, in order:

• Set up the Google service account and run the reporter against a real Sheet with real evidence entries
• Get Aeries API credentials and validate classification against known students
• Pilot with 3-4 SARB admin staff at one school site

After validation, the pitch is straightforward: over 1,000 California districts use Aeries SIS, and every one of them has the same SARB compliance problem. The architecture already isolates by school code, supports configurable API endpoints, and separates the evidence sidecar from the district's source of truth. But that's a post for after the pilot proves the workflow actually holds up with real humans and real data.