feat: SIS deep-link from student detail view (FERPA-compliant identity resolution)

[William-Hill]

Overview

The student detail page (/students/[guid]) currently displays de-identified academic indicators only, using an anonymised Student_GUID. Advisors who want to act on a prediction need a way to locate the real student record in the institution's SIS (Colleague, Banner, Ellucian, etc.) without this system ever storing or transmitting PII.

Approach: SIS Deep-Link (Option 2)

The detail page shows an "Open in SIS" button that constructs a URL to the institution's SIS using the real internal student ID. Identity resolution happens entirely within the institution's own network — this application never receives, stores, or transmits a student's name, SSN, or any other FERPA-protected identifier.

/students/[guid]
  → advisor clicks "Open in SIS"
  → browser navigates to https://sis.bscc.edu/students?id=<real_id>

The GUID-to-SIS-ID mapping lives only in the SIS. This app holds only the GUID.

FERPA stance: Compliant. This is the same pattern used by Civitas Learning, EAB Navigate, and other FERPA-compliant analytics platforms. The analytics system acts as a "school official" display layer; the SIS owns identity resolution.

Implementation Plan

1 — Environment configuration

Add two optional env vars to .env.local:

# Base URL for SIS deep-links (leave blank to hide the button)
SIS_BASE_URL=https://sis.bscc.edu/students
# Query param name the SIS expects for the student ID (default: id)
SIS_ID_PARAM=id

2 — GUID-to-SIS-ID mapping table

The institution's IT team maintains a guid_sis_map table (or equivalent) in their secured SIS or a separate Supabase schema with row-level security:

CREATE TABLE guid_sis_map (
  student_guid TEXT PRIMARY KEY,
  sis_id       TEXT NOT NULL
);

This table is never exposed through any public API route in this application.

3 — Server-side lookup API (role-gated)

Add GET /api/students/[guid]/sis-link that:

• Requires Advisor or IR role (enforced by middleware — see issue feat: role-based access control for advisors, faculty, and leadership (FR6) #75)
• Looks up sis_id from guid_sis_map for the given GUID
• Returns { url: "https://sis.bscc.edu/students?id=<sis_id>" }
• Returns 404 if no mapping exists
• Never logs the SIS ID to audit trail or console

4 — UI: "Open in SIS" button

On the student detail page, when SIS_BASE_URL is configured and the user has Advisor/IR role:

• Show an "Open in SIS" button (external link icon) in the student header
• Button is hidden entirely for Faculty and Leadership roles
• On click, fetches /api/students/[guid]/sis-link and opens the URL in a new tab
• If no mapping exists, show a tooltip: "No SIS record linked for this student"

5 — Audit logging

Every SIS deep-link click is logged to the server-side audit log (logs/query-history.jsonl) as:

{ "event": "sis_link_accessed", "guid": "<guid>", "role": "advisor", "timestamp": "..." }

The sis_id itself is not logged.

Acceptance Criteria

• SIS_BASE_URL env var controls whether the button appears (hidden when blank)
• Button only visible to Advisor + IR + Admin roles
• SIS ID is never stored in student_level_with_predictions or llm_recommendations
• SIS ID is never returned by any public API endpoint
• Every deep-link access is logged (GUID + role, not SIS ID)
• Button shows a graceful fallback if no mapping exists for a GUID
• Works with any SIS that accepts a query-param student ID in a URL

Dependencies

• Requires issue feat: role-based access control for advisors, faculty, and leadership (FR6) #75 (role-based access) to enforce Advisor/IR-only visibility
• Requires institution IT to populate and maintain the guid_sis_map table

Notes

• This is the same identity-resolution pattern used by Civitas Learning, EAB Navigate, and Hobsons Starfish
• Alternative approaches considered (lookup table only, hash-based reversible GUID, institution proxy endpoint) are documented in the PR discussion for issue feat: student detail view with personalized recommendations (FERPA-compliant) #77