I Built a Chat Interface for a 1990s Student Database

Aeries SIS is a student information system used by over 1,000 school districts in California. It holds everything -- attendance, grades, contacts, enrollment history. It's also the kind of software where looking up a student's phone number requires navigating through four dropdown menus and a popup window that blocks the rest of the screen.

Our office staff look up students dozens of times a day. A parent calls about absences. A teacher needs a contact number. An admin needs to verify enrollment. Every time, it's the same painful UI dance. I built a chat interface where they can just type "look up Maria Garcia" and get the answer.

Two-Tier Routing: The Interesting Problem

The obvious approach is: send every query to Claude, let it figure out what API calls to make, return the answer. That works, but it's slow (9+ seconds per query) and it means every student lookup gets sent to a third-party AI service. Under FERPA, that's a third-party disclosure you have to document.

Most queries from office staff are simple: "look up student 12345", "attendance for Maria Garcia", "contacts for school 994." These don't need AI reasoning. They need a regex match and an API call.

So I built a two-tier router:

def route_query(query: str) -> RouteResult | None:
    """Try to handle locally without sending to Claude."""
    normalized = query.strip().lower()

    # Direct student ID lookup: "look up 12345", "student 12345"
    id_match = re.search(r"\b(\d{4,7})\b", normalized)
    if id_match and any(kw in normalized for kw in ["look", "student", "find"]):
        return RouteResult(tool="get_student", params={"student_id": int(id_match.group(1))})

    # Name search: "look up Maria Garcia"
    name_match = re.search(r"(?:look up|find|search)\s+([A-Za-z]+(?:\s+[A-Za-z]+)?)", query)
    if name_match:
        return RouteResult(tool="search_students", params={"name": name_match.group(1)})

    # Bail to Claude for complex queries ("who has the most absences?")
    if any(kw in normalized for kw in ["who has", "compare", "failing", "most"]):
        return None  # AI path

    # ... 8 patterns total covering all 10 tools

When the router matches, the query never leaves the server. No API call to Anthropic, no token cost, no FERPA disclosure. The audit log marks it processing_mode: "local" instead of "ai-assisted".

The benchmark results surprised me:

The AI path still exists for complex questions ("which 3rd graders have more than 5 unexcused absences this month?") where Claude needs to reason about which API calls to chain together. But the common case -- the parent calling about their kid -- resolves instantly without touching an external service.

The PII Stripping Layer

Aeries API responses include everything. Social security numbers, medical flags, parent employment info, IEP status. None of that should surface in a chat interface for general office staff.

SAFE_STUDENT_FIELDS = [
    "StudentID", "FirstName", "LastName", "Grade",
    "SchoolCode", "Gender", "Birthdate",
    "HomePhone", "CellPhone",  # Contact info only
]

SAFE_CONTACT_FIELDS = [
    "ContactName", "Relationship",
    "HomePhone", "WorkPhone", "CellPhone",
]

def filter_student(raw: dict) -> dict:
    """Strip everything except allowlisted fields. Fail closed."""
    return {k: v for k, v in raw.items() if k in SAFE_STUDENT_FIELDS}

This is a fail-closed approach. If Aeries adds a new field to their API response tomorrow (they've done it before -- no changelog, no warning), it gets silently dropped. The only way data surfaces in the chat is if it's explicitly in the allowlist.

The Audit Log That Can't Be Deleted

Every query -- local or AI-routed -- writes a row to a SQLite audit database:

CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp TEXT NOT NULL,
    session_id TEXT NOT NULL,
    query TEXT NOT NULL,
    processing_mode TEXT NOT NULL,  -- "local" or "ai-assisted"
    tools_called TEXT,
    third_party_disclosure TEXT NOT NULL,  -- "none" or "anthropic"
    student_ids_accessed TEXT
);

The app only inserts. No UPDATE, no DELETE statements exist in the codebase. If a parent or auditor asks "who accessed my child's record and when," the answer is in the database. If they ask "was my child's data sent to an AI service," the third_party_disclosure column tells you exactly which queries hit Claude and which stayed local.

What It Looks Like

The frontend is vanilla HTML/CSS/JS. No React, no build step, no node_modules. A chat interface with typing indicators, markdown table rendering for results, and a badge under each response showing whether it was processed locally (green) or AI-assisted (orange).

Staff type natural language. The system returns formatted tables or conversational answers depending on the query. Quick-action buttons parsed from Claude's responses let staff drill into attendance or contacts without typing follow-ups.

Current State: Demo-Ready, Not Production

What's ready:

• Core chat loop with Claude tool use -- working
• All 10 tools defined and callable -- working
• Two-tier local/AI routing with 8 regex patterns -- working
• FERPA audit logging -- working
• Session management with TTL and eviction -- working
• Rate limiting -- working
• 30 passing tests -- working

What's not:

• Google SSO (code written, needs client ID/secret)
• TLS (cert generation script ready, not yet generated)
• Real Aeries connection (needs district API cert)

The same Aeries API certificate that blocks the SARB Tracker blocks this. Once district IT hands over credentials, both projects go live against real data the same day.