feat: course sequencing insights and DFWI analysis (Option B — load course enrollments)

[William-Hill]

Overview

Implement true per-course DFWI (Dropped/Failed/Withdrew/Incomplete) analysis and course sequencing insights by loading the raw course-enrollment data into the database.

The existing course_predictions table is student-level aggregates only. The real per-enrollment data lives in data/bishop_state_courses.csv (~99,500 rows) and is not yet in the DB.

---

Phase 1 — Data Layer

Migration: course_enrollments table

CREATE TABLE public.course_enrollments (
  id                    BIGSERIAL PRIMARY KEY,
  student_guid          TEXT NOT NULL,
  cohort                TEXT,
  cohort_term           TEXT,
  academic_year         TEXT,
  academic_term         TEXT,
  course_prefix         TEXT,     -- e.g. MAT, ENG, NUR
  course_number         TEXT,     -- e.g. 100, 101
  course_name           TEXT,
  course_cip            TEXT,
  course_type           TEXT,     -- CU = credit, CC = co-req
  gateway_type          TEXT,     -- M = math, E = English, N = neither
  is_core_course        BOOLEAN,
  core_course_type      TEXT,
  delivery_method       TEXT,     -- F = face-to-face, O = online, H = hybrid
  grade                 TEXT,     -- A, B, C, D, F, W, I, etc.
  credits_attempted     NUMERIC,
  credits_earned        NUMERIC,
  instructor_status     TEXT      -- FT / PT
);

Ingestion script: scripts/load-course-enrollments.ts

• Reads data/bishop_state_courses.csv
• Batch-inserts into course_enrollments with upsert (idempotent)
• Maps CSV column names to table columns
• Logs progress and row count on completion

---

Phase 2 — API Routes

GET /api/courses/dfwi

Returns DFWI rate per course (prefix + number), sortable by rate or volume.

DFWI = grade IN ('D', 'F', 'W', 'I')

Response shape:

[
  {
    "course_prefix": "MAT",
    "course_number": "100",
    "course_name": "College Algebra",
    "gateway_type": "M",
    "enrollments": 312,
    "dfwi_count": 134,
    "dfwi_rate": 42.9,
    "pass_rate": 57.1,
    "avg_grade_numeric": 1.8
  }
]

Query params: gatewayOnly=true, minEnrollments=N, cohort, term

GET /api/courses/sequences

Identifies common course pairings taken in the same term or consecutive terms. Useful for seeing which courses co-occur with high-DFWI courses.

GET /api/courses/gateway-funnel

Aggregated view:

• % students who attempted gateway math/English in Year 1
• % who passed
• % who withdrew/failed
• Breakdown by cohort, enrollment intensity, delivery method

---

Phase 3 — UI: /courses page

New page with three sections:

1. High-Risk Course Table — sortable by DFWI rate, filterable by gateway/non-gateway, prefix, delivery method. Color-coded DFWI rate cells.

2. Gateway Funnel Charts — bar or funnel chart: Attempted → Passed, Attempted → DFWI, by cohort year. Powered by Recharts.

3. Course Sequencing Panel — shows which courses students typically take together or in sequence, with pass/fail overlay. Helps identify prerequisite gaps.

---

Acceptance Criteria

• course_enrollments table created and populated (~99,500 rows)
• DFWI rate queryable per course with filters
• /courses page accessible to admin, advisor, ir, faculty roles
• Gateway funnel chart renders for all 5 cohorts
• High-risk course table sortable and filterable
• Ingestion script is idempotent (safe to re-run)
• NLP query interface can reference course-level data

[William-Hill]

Implemented in PR #87 — course_enrollments table loaded with 99,559 rows from bishop_state_courses.csv, DFWI/gateway-funnel/sequences API routes, tabbed /courses page with AI-powered co-enrollment explainability.