Ticket: HEL-681
Date: 2026-04-07
Build a full staff-facing App Tracker inside the employee portal, accessible from Student Detail. A new /students/[id]/tracker page with 7 sub-tracker tabs: College List (inline-editable grid), Essays, Recommendations, Activities, UC Activities, Scholarships, Demonstrated Interest. The College List tab is the primary view — a horizontal-scroll grid with frozen columns, column groups, color-coded editability, and inline cell editing. Replaces the spreadsheet counselors use today.
Counselors maintain a separate spreadsheet per student with ~60 columns and 5+ sub-sheets. All tracker DB tables and API routes already exist. The student-facing tracker UI (/tracker) is already built. This ticket builds the staff-facing counterpart inside the employee portal so counselors can retire the spreadsheet and work directly in the portal.
.tmp/app-tracker-mockup.html — reviewed and approved by Austin. Key UX decisions captured there:
HEL-680 (App Tracker Canonical Wiring) — provides portal_tracker source type and app.* aggregate fields. This ticket does NOT depend on HEL-680 being complete; aggregate counts in the summary bar can be computed directly from fetched data. HEL-680 wires those counts to the canonical store for Source Catalog visibility.
| Item | Location |
|---|---|
| All tracker API routes | app/api/student/tracker/{app-details,essays,recommenders,activities,scholarships,interest,milestones}/route.ts |
| All tracker DB tables | Migrated 20260328_* |
| Student-facing tracker | app/(student-portal)/tracker/ (6 tabs, overview + sub-trackers) |
selected_colleges query |
Student detail page — fetches with priority types, college name, major1/2, ranking, applied, status |
| College List section (SD) | components/students/detail-sections/CollegeListSection.tsx — collapsed view, NOT the new tracker |
New dedicated page: app/(portal)/students/[id]/tracker/page.tsx
Why a separate route (not a tab inside StudentDetail):
/tracker/essays, /tracker/recs, etc.)Add an "App Tracker" button/link in the student header card (alongside existing action buttons). Navigates to /students/{id}/tracker. No structural change to StudentDetail.tsx — just one link.
/students/[id]/tracker
├── AppTrackerPage (server component)
│ ├── Fetches: selected_colleges (with college data), all tracker sub-data
│ └── AppTrackerShell (client component)
│ ├── Student mini-header (name, grade, counselor, back link to SD)
│ ├── SummaryBar (aggregate counts)
│ ├── TrackerTabs (College List | Essays | Recs | Activities | UC Activities | Scholarships | Interest)
│ └── [active tab content]
Use a ?tab= query param (not nested routes) to keep it simple. Default tab = colleges.
/students/[id]/tracker?tab=colleges ← default
/students/[id]/tracker?tab=essays
/students/[id]/tracker?tab=recs
/students/[id]/tracker?tab=activities
/students/[id]/tracker?tab=uc-activities
/students/[id]/tracker?tab=scholarships
/students/[id]/tracker?tab=interest
app/(portal)/students/[id]/tracker/page.tsx — server component:
// Parallel fetches
const [collegesRaw, essays, recommenders, recColleges, activities, scholarships, interest] = await Promise.all([
prisma.selected_colleges.findMany({
where: { student_id: studentId, removed_at: null },
include: {
colleges: {
select: {
name: true, city: true, state: true, undergrad_enrollment: true,
acceptance_rate: true, act_25: true, act_75: true, sat_25: true, sat_75: true
}
},
college_priority_types: { select: { description: true } },
next_student_app_details: { take: 1 }
},
orderBy: { priority: "asc" }
}),
fetch(`/api/student/tracker/essays?studentId=${studentId}`),
fetch(`/api/student/tracker/recommenders?studentId=${studentId}`),
// etc.
]);
Note on college catalog fields: Check colleges table schema — if acceptance_rate, enrollment, test score ranges are stored there, pull them and mark read-only in the grid. If not, these columns come from next_student_app_details and are counselor-editable. Executor verifies before building.
components/students/tracker/SummaryBar.tsx
Computed from fetched data (no separate query needed):
| Stat | Source |
|---|---|
| Colleges | selectedColleges.length |
| Applied | filter(sc => sc.applied) |
| Accepted | filter(sc => sc.application_status === 'Accepted') |
| Safety / Likely / Target / Reach | filter by college_priority_types.description |
| Essays done | essays.filter(e => e.status === 'submitted').length / essays.length |
| Recs confirmed | recommenders.filter(r => r.status === 'confirmed').length / recommenders.length |
Renders as the horizontal stat strip from the mockup.
components/students/tracker/CollegeTrackerGrid.tsx — client component
| # | Column | Frozen | Background | Editable | Source |
|---|---|---|---|---|---|
| 1 | Rank | ✓ | gray | No | selected_colleges.priority |
| 2 | College + Location | ✓ | gray | No | colleges.name/city/state |
| 3 | Tier + Counselor | — | gray | No | priority_types.description + team |
| 4 | Major | — | yellow | Yes | selected_colleges.major1 |
| 5 | Enrollment | — | gray | (gray) | colleges.undergrad_enrollment |
| 6 | Acceptance Rate | — | gray | (gray) | colleges.acceptance_rate or app_details |
| 7 | App Type | — | yellow | Yes | app_details.application_type |
| 8 | Target Submit Date | — | yellow | Yes | app_details.submit_date_target |
| 9 | Applied? | — | yellow | Yes (toggle) | selected_colleges.applied |
| 10 | EA / ED Deadline | — | gray | (gray) | app_details.ea_deadline / ed_deadline |
| 11 | RD Deadline | — | gray | (gray) | app_details.rd_deadline |
| 12 | Financial Aid Deadline | — | gray | (gray) | app_details.financial_deadline |
| 13 | Test Optional | — | yellow | Yes (toggle) | app_details.test_optional |
| 14 | Superscore? | — | yellow | Yes (toggle) | app_details.superscores |
| 15 | Report Scores? | — | pink | Yes (counselor) | app_details.should_report_scores |
| 16 | Cost (In/Out) | — | gray | (gray) | app_details.coa_amount / oos_surcharge |
| 17 | CSS Profile? | — | gray | (gray) | app_details.css_profile_required |
| 18 | Financial Deadlines | — | gray | (gray) | app_details.financial_deadline |
| 19 | Outcome | — | pink | Yes (counselor) | app_details.school_decision |
| 20 | # Essays | — | gray | No | computed from essays for this college |
<input> or <select> appears inline, fills cell, styled to match. On blur or Enter → PATCH to API. On Escape → revert.<input type="date"> inline. On blur → PATCH.For next_student_app_details fields:
PATCH /api/student/tracker/app-details
Body: { selected_college_id, field_name, value }
For selected_colleges fields (applied, major1):
PATCH /api/student/selected-college ← may need new route or extend existing
Body: { id, applied?, major1?, priority? }
"+ Add college to list" button at bottom → opens college search modal (existing college search component if available, or name input). Creates selected_colleges row + empty next_student_app_details row.
components/students/tracker/EssaysTrackerTab.tsx
Table: one row per essay. Columns:
| Column | Editable | Source |
|---|---|---|
| College | Yes (select from student's list) | next_student_essays.selected_college_id |
| Essay Name | Yes | essay_name |
| Type | Yes | essay_type (Common App / Supplemental / UC / Other) |
| Word Limit | Yes | word_count_limit |
| Current Words | Yes | current_word_count |
| Status | Yes (select) | status — Not Started / Outline / Draft / Revising / Final / Submitted |
| Writing Order | Yes | writing_order |
| Target Date | Yes | my_target_date |
| First Draft | Yes | first_draft_date |
| Final Draft | Yes | final_draft_date |
| Notes | Yes | notes |
Prompt column: show truncated (click to expand full prompt in tooltip or expand row).
Sort by: Writing Order (default) or College. Group by College option (toggle).
"+ Add essay" → inline blank row at bottom.
components/students/tracker/RecsTrackerTab.tsx
Two-part view:
Top: Recommender list — one row per recommender
| Column | Source |
|---|---|
| Type | rec_type — Teacher / Counselor / Other |
| Name | recommender_name |
| Relationship | relationship |
email |
|
| Requested | date_requested |
| Resume Sent | resume_sent (date, checkmark if set) |
| Invite Sent | invite_sent (date) |
| Follow-up Sent | follow_up_sent (date) |
| Status | status — Pending / Confirmed / Submitted |
| Due Date | due_date |
Pre-populate with 2 Teacher rows + 1 Counselor row (blank) when no recommenders exist yet.
Bottom: College assignment matrix (optional for v1, can be Phase 2)
Which recommenders are writing for which colleges — checkbox grid. Uses next_student_recommender_colleges.
components/students/tracker/ActivitiesTrackerTab.tsx
10-row table (Common App max). Each row:
| Column | Source | Notes |
|---|---|---|
| # | position_number |
1–10, drag to reorder |
| Activity Type | activity_type |
dropdown |
| Position / Title | position_title |
50 char limit |
| Organization | organization |
100 char limit |
| Description | description |
150 char limit — show char counter |
| Grades | grades_participated |
multi-select: 9/10/11/12 |
| Timing | timing |
All Year / School Year / School Break |
| Hours/Week | hours_per_week |
number |
| Weeks/Year | weeks_per_year |
number |
Click any cell to edit inline. Character counters on Description and Organization (Common App limits).
components/students/tracker/UCActivitiesTrackerTab.tsx
Same structure as Activities but different char limits:
format column: Common App / UCReuse the same next_student_activities table — differentiate via format field.
components/students/tracker/ScholarshipsTrackerTab.tsx
Simple table:
| Column | Source |
|---|---|
| Scholarship Name | scholarship_name |
| Organization | organization |
| Amount | amount (formatted as currency) |
| Due Date | due_date |
| Date Sent | sent_date |
| Reply Date | reply_date |
| Status | status — Researching / Applied / Pending / Awarded / Declined |
| Notes | notes |
"+ Add scholarship" inline row.
components/students/tracker/InterestTrackerTab.tsx
One row per interest activity per college.
| Column | Source |
|---|---|
| College | selected_college_id (name from join) |
| Interest Type | interest_type — College Visit / Open House / Virtual Event / Social Follow / Email Registration / Interview / Other |
| Completed | completed (toggle) |
| Date | completed_date |
| Notes | notes |
Group by College (toggle between flat list and grouped view).
components/students/StudentDetail.tsx — minimal change:
Add "App Tracker →" button/link in the student header section (near existing action buttons). Links to /students/{id}/tracker.
Also update CollegeListSection to show an "Open in App Tracker" link at the bottom of the collapsed view.
If no existing PATCH route for selected_colleges, add:
app/api/student/selected-college/route.ts (or extend existing route):
PATCH — update applied, major1, major2, priority, application_status on a selected_colleges row{ id: number, field: string, value: unknown }app/(portal)/students/[id]/tracker/page.tsx — server component, data fetchcomponents/students/tracker/AppTrackerShell.tsx — client shell: tabs, layoutcomponents/students/tracker/SummaryBar.tsx — aggregate stat stripcomponents/students/tracker/CollegeTrackerGrid.tsx — the main gridcomponents/students/tracker/EssaysTrackerTab.tsxcomponents/students/tracker/RecsTrackerTab.tsxcomponents/students/tracker/ActivitiesTrackerTab.tsxcomponents/students/tracker/UCActivitiesTrackerTab.tsxcomponents/students/tracker/ScholarshipsTrackerTab.tsxcomponents/students/tracker/InterestTrackerTab.tsxcomponents/students/StudentDetail.tsx — add "App Tracker" link in headercomponents/students/detail-sections/CollegeListSection.tsx — add "Open in App Tracker" linkapp/api/student/selected-college/route.ts — add PATCH support (or create)e2e/app-tracker-staff.spec.tse2e/app-tracker-essays.spec.tse2e/app-tracker-staff.spec.ts:
/students/{id}/trackere2e/app-tracker-essays.spec.ts:
/tracker route) — already built, untouched/students/[id]/tracker page accessible from Student Detail header link