Ticket: HEL-680
Date: 2026-04-07
Create ~35 app.* canonical registry entries for student-level application strategy data. Create ~111 tracker_* catalog entries documenting every field in the app tracker tables. Add a college-select field type to TaskFormRenderer. Wire canonical_key on ~50 fields across 20+ existing task types. Introduce a portal_tracker source type so tracker-backed fields appear correctly in Source Catalog.
The 67 existing task types have fully populated field schemas but only 5 have canonical_key wiring. Every meaningful student-level outcome captured during counseling sessions (personal statement status, FAFSA status, ED school, LOR status, etc.) silently discards its data instead of writing to the canonical store. The per-college tracker tables (next_student_app_details, next_student_essays, etc.) hold 100+ fields that are invisible to the Source Catalog because they have no registry entries. This ticket catalogs everything and wires the student-level fields to the canonical store.
Two categories:
app.* — student-level, single-value-per-student fields. Written to next_student_profile_data via canonical_key. ~35 fields.tracker_*.* — multi-row-per-student fields. Catalog-only entries with source_mappings → portal_tracker. Data stays in tracker tables. ~111 fields across 7 sub-categories.Aggregate fields (app.college_count, app.accepted_count, etc.) live in the app.* category. Source_mappings describe the aggregate query. They are computed inline in the tracker UI, not materialized in next_student_profile_data.
New source type: portal_tracker — source_mappings entry: {"source": "portal_tracker", "table": "next_student_app_details", "column": "submitted"}. Source Catalog renders these with an "App Tracker" badge.
New field type: college-select — dropdown populated from the student's selected_colleges. Writes the college name as a string to canonical store (consistent with academic.high_school, contact.city). The tracker tables maintain proper FK relationships.
Confirm column names match analysis before writing any SQL:
-- Verify next_student_app_details columns
SELECT column_name FROM information_schema.columns
WHERE table_name = 'next_student_app_details'
ORDER BY ordinal_position;
-- Verify next_student_essays columns
SELECT column_name FROM information_schema.columns
WHERE table_name = 'next_student_essays'
ORDER BY ordinal_position;
-- Count existing task types with field_schema
SELECT COUNT(*) FROM next_task_types WHERE field_schema != '[]'::jsonb;
-- Expect 67
app.* Canonical Registry EntriesAdd to prisma/seed-field-registry.js and INSERT into next_field_registry.
| field_key | display_name | data_type | options |
|---|---|---|---|
app.personal_statement_status |
Personal Statement Status | select | Draft complete - ready for review / In progress / Topic selected - writing in progress / Not started |
app.personal_statement_topic |
Personal Statement Topic | text | — |
app.personal_statement_theme |
Personal Statement Theme | select | Adapting to Change / Overcoming Challenges / Identity & Cultural Background / Academic & Intellectual Growth / Leadership & Service / Creative Expression / Family & Community Impact / Other |
app.personal_statement_word_count |
Personal Statement Word Count | number | — |
app.activities_list_status |
Activities List Status | select | Finalized and approved / Final polish in progress / Feedback incorporated / Initial draft complete / Not started |
app.lor_status |
Recommendation Letter Status | select | All requested and confirmed / Requested - awaiting confirmation / Teachers identified - need to request / Need to identify teachers |
app.fafsa_status |
FAFSA Status | select | Submitted / Completed / In Progress / Not Started |
app.css_profile_status |
CSS Profile Status | select | Not Required / Submitted / Completed / In Progress / Not Started |
app.ed_school |
Early Decision School | text | — (college-select field type in tasks) |
app.family_ed_consensus |
Family ED Consensus Confirmed | boolean | — |
app.common_app_status |
Common App Status | select | Complete / Needs Minor Fixes / Needs Major Revisions / Not Started |
app.supplemental_essays_status |
Supplemental Essays Status | select | All Complete / In Progress / Not Started |
app.narrative_strength |
Application Narrative Strength | select | Strong / Good - needs refinement / Developing - needs focus / Needs Work |
app.writing_confidence |
Student Writing Confidence | select | High / Moderate / Low / Perfectionist |
app.target_decision_date |
Target College Decision Date | date | — |
app.financial_aid_appeal_recommended |
Financial Aid Appeal Recommended | boolean | — |
app.target_scholarship_applications |
Target # Scholarship Applications | number | — |
app.portfolio_quality |
Portfolio Quality Rating | select | Excellent / Good / Needs Improvement |
app.portfolio_platform |
Portfolio Submission Platform | select | School's online portal / SlideRoom / Common App / Direct email / Physical submission / Other |
app.interview_confidence |
Interview Confidence Level | select | 1 - Very nervous / 2 - Somewhat nervous / 3 - Neutral / 4 - Confident / 5 - Very confident |
| field_key | display_name | source table | aggregate |
|---|---|---|---|
app.college_count |
Total Colleges on List | selected_colleges | count |
app.applied_count |
Applied Count | selected_colleges | count WHERE applied=true |
app.accepted_count |
Accepted Count | selected_colleges | count WHERE application_status='accepted' |
app.safety_count |
Safety / Likely Count | selected_colleges + priority_types | count by tier |
app.target_count |
Target Count | selected_colleges + priority_types | count by tier |
app.reach_count |
Reach Count | selected_colleges + priority_types | count by tier |
app.total_essays_assigned |
Total Essays Assigned | next_student_essays | count |
app.essays_submitted |
Essays Submitted | next_student_essays | count WHERE status='submitted' |
app.total_recommendations |
Total Recommenders | next_student_recommenders | count |
app.recommendations_confirmed |
Recommendations Confirmed | next_student_recommenders | count WHERE status='confirmed' |
app.total_scholarships_tracked |
Scholarships Tracked | next_student_scholarships | count |
app.scholarships_awarded |
Scholarships Awarded | next_student_scholarships | count WHERE status='awarded' |
app.total_activities_listed |
Activities Listed | next_student_activities | count |
app.milestones_complete |
Milestones Complete | next_student_cap_progress | count WHERE completed=true |
app.milestones_total |
Total Milestones | next_cap_milestones | count |
Source mapping format for aggregates:
[{"source": "portal_tracker", "table": "selected_colleges", "aggregate": "count"}]
[{"source": "portal_tracker", "table": "selected_colleges", "aggregate": "count_where", "column": "applied", "value": true}]
tracker_* Catalog Registry EntriesAll entries: wired_to_ui: true, source_mappings: [{"source": "portal_tracker", "table": "...", "column": "..."}], data type matches DB column.
tracker_app — from next_student_app_details (~60 fields)| field_key | display_name | data_type |
|---|---|---|
tracker_app.application_type |
Application Type | text |
tracker_app.submitted |
Submitted | boolean |
tracker_app.submit_date_target |
Target Submission Date | date |
tracker_app.submit_date_actual |
Actual Submission Date | date |
tracker_app.school_decision |
School Decision | text |
tracker_app.school_decision_date |
Decision Date | date |
tracker_app.app_deadline |
Application Deadline | date |
tracker_app.ed_deadline |
ED Deadline | date |
tracker_app.ed_notification_date |
ED Notification Date | date |
tracker_app.ea_deadline |
EA Deadline | date |
tracker_app.ea_notification_date |
EA Notification Date | date |
tracker_app.rd_deadline |
RD Deadline | date |
tracker_app.rolling_deadline |
Rolling Deadline | date |
tracker_app.merit_deadline |
Merit Aid Deadline | date |
tracker_app.financial_deadline |
Financial Aid Deadline | date |
tracker_app.test_optional |
Test Optional | boolean |
tracker_app.superscores |
Superscores Accepted | boolean |
tracker_app.should_report_scores |
Should Report Scores | boolean |
tracker_app.score_reporting_method |
Score Reporting Method | text |
tracker_app.scores_sent_date |
Scores Sent Date | date |
tracker_app.test_blind |
Test Blind | boolean |
tracker_app.css_profile_required |
CSS Profile Required | boolean |
tracker_app.fafsa_css_needed_for_merit |
FAFSA/CSS Needed for Merit | boolean |
tracker_app.no_merit_aid |
No Merit Aid Available | boolean |
tracker_app.application_fee |
Application Fee | number |
tracker_app.coa_amount |
Cost of Attendance | number |
tracker_app.net_coa |
Net Cost of Attendance | number |
tracker_app.tuition |
Tuition | number |
tracker_app.fees |
Fees | number |
tracker_app.housing |
Housing | number |
tracker_app.books_supplies |
Books & Supplies | number |
tracker_app.transportation_other |
Transportation & Other | number |
tracker_app.oos_surcharge |
Out-of-State Surcharge | number |
tracker_app.scholarships_aid_amount |
Scholarships & Aid Amount | number |
tracker_app.loan_amount |
Loan Amount | number |
tracker_app.direct_admit |
Direct Admit Program | boolean |
tracker_app.honors_college |
Honors College | boolean |
tracker_app.school_tracks_interest |
School Tracks Interest | boolean |
tracker_app.probability_counselor |
Counselor Probability | text |
tracker_app.counselor_review_notes |
Counselor Review Notes | textarea |
tracker_app.decision_notes |
Decision Notes | textarea |
tracker_app.application_requirements |
Application Requirements | textarea |
tracker_app.scholarship_requirements |
Scholarship Requirements | textarea |
tracker_app.appeal_requirements |
Appeal Requirements | textarea |
tracker_app.portal_link |
Application Portal Link | text |
tracker_app.coa_link |
COA Link | text |
tracker_app.npc_link |
Net Price Calculator Link | text |
tracker_app.npc_result |
NPC Result | text |
tracker_app.financial_aid_link |
Financial Aid Page | text |
tracker_app.scholarship_link |
Scholarship Page | text |
tracker_app.special_circumstances_link |
Special Circumstances Link | text |
tracker_app.noncustodial_waiver_link |
Non-Custodial Waiver Link | text |
tracker_app.intl_financial_aid_link |
International Financial Aid Link | text |
tracker_app.ed_policy_link |
ED Policy Link | text |
tracker_app.college_info_link |
College Info Link | text |
tracker_app.student_rank |
Student Rank | number |
tracker_app.major1 |
Primary Major | text |
tracker_app.major2 |
Secondary Major | text |
tracker_app.major3 |
Third Major | text |
tracker_app.safe_rankings |
SAFE Rankings | text |
tracker_essay — from next_student_essays (12 fields)| field_key | display_name | data_type |
|---|---|---|
tracker_essay.essay_name |
Essay Name | text |
tracker_essay.essay_type |
Essay Type | text |
tracker_essay.prompt |
Prompt | textarea |
tracker_essay.theme |
Theme | text |
tracker_essay.word_count_limit |
Word Count Limit | number |
tracker_essay.current_word_count |
Current Word Count | number |
tracker_essay.status |
Essay Status | select |
tracker_essay.writing_order |
Writing Order | number |
tracker_essay.my_target_date |
Student Target Date | date |
tracker_essay.first_draft_date |
First Draft Date | date |
tracker_essay.final_draft_date |
Final Draft Date | date |
tracker_essay.notes |
Notes | textarea |
tracker_rec — from next_student_recommenders (11 fields)| field_key | display_name | data_type |
|---|---|---|
tracker_rec.recommender_name |
Recommender Name | text |
tracker_rec.rec_type |
Recommendation Type | text |
tracker_rec.relationship |
Relationship | text |
tracker_rec.email |
text | |
tracker_rec.date_requested |
Date Requested | date |
tracker_rec.resume_sent |
Resume Sent | date |
tracker_rec.invite_sent |
Invite Sent | date |
tracker_rec.follow_up_sent |
Follow-up Sent | date |
tracker_rec.status |
Status | select |
tracker_rec.due_date |
Due Date | date |
tracker_rec.notes |
Notes | textarea |
tracker_scholarship — from next_student_scholarships (8 fields)| field_key | display_name | data_type |
|---|---|---|
tracker_scholarship.scholarship_name |
Scholarship Name | text |
tracker_scholarship.organization |
Organization | text |
tracker_scholarship.amount |
Amount | number |
tracker_scholarship.due_date |
Due Date | date |
tracker_scholarship.sent_date |
Date Sent | date |
tracker_scholarship.reply_date |
Reply Date | date |
tracker_scholarship.status |
Status | select |
tracker_scholarship.notes |
Notes | textarea |
tracker_activity — from next_student_activities (10 fields)| field_key | display_name | data_type |
|---|---|---|
tracker_activity.activity_type |
Activity Type | text |
tracker_activity.position_title |
Position / Title | text |
tracker_activity.organization |
Organization | text |
tracker_activity.description |
Description | textarea |
tracker_activity.grades_participated |
Grades Participated | text |
tracker_activity.timing |
Timing | text |
tracker_activity.hours_per_week |
Hours per Week | number |
tracker_activity.weeks_per_year |
Weeks per Year | number |
tracker_activity.format |
Format (Common App / UC) | text |
tracker_activity.theme |
Theme | text |
tracker_milestone — from next_cap_milestones + next_student_cap_progress (6 fields)| field_key | display_name | data_type |
|---|---|---|
tracker_milestone.title |
Milestone Title | text |
tracker_milestone.grade_level |
Grade Level | text |
tracker_milestone.season |
Season | text |
tracker_milestone.category |
Category | text |
tracker_milestone.completed |
Completed | boolean |
tracker_milestone.completed_date |
Completed Date | date |
tracker_interest — from next_student_demonstrated_interest (4 fields)| field_key | display_name | data_type |
|---|---|---|
tracker_interest.interest_type |
Interest Type | text |
tracker_interest.completed |
Completed | boolean |
tracker_interest.completed_date |
Completed Date | date |
tracker_interest.notes |
Notes | textarea |
Total new registry entries: 35 (app.) + 111 (tracker_.*) = 146
college-select Field Typeapp/api/students/[id]/college-list-options/route.ts (new)GET — returns simplified list for dropdown: [{id, name, priority_type}]SELECT id, college_id, priority, (SELECT name FROM colleges WHERE id = sc.college_id) AS name FROM selected_colleges sc WHERE student_id = $1 AND removed_at IS NULL ORDER BY prioritycomponents/ui/TaskFormRenderer.tsxstudentId?: number prop (used by college-select to fetch options)college-select to field type handling:
GET /api/students/{studentId}/college-list-optionsonCanonicalChange if canonical_key is set, else onChangecomponents/settings/TaskTypeEditor.tsx"college-select" to the FIELD_TYPES array in the form buildercomponents/dashboard/TaskMilestoneModal.tsxstudentId prop down to TaskFormRendererportal_tracker Source in Source Catalogcomponents/settings/FieldRegistryManager.tsxportal_tracker source type in SourceBadges:portal_tracker: { bg: "#e0f2fe", text: "#0369a1", label: "App Tracker" }
tracker_* fields, completeness display shows row count from tracker table rather than a percentage (these are multi-row, not single-value). Display as "N rows" instead of "N%".canonical_key Wiring Across Task TypesUpdate scripts/seed-task-types.ts to add canonical_key to the following fields. Then run the update migration to apply to DB.
Set Application Strategy
| field id | canonical_key |
|---|---|
personalStatementStatus |
app.personal_statement_status |
activitiesListStatus |
app.activities_list_status |
lorStatus |
app.lor_status |
edSchool |
app.ed_school |
familyEdConsensus |
app.family_ed_consensus |
Develop Preliminary College List
| field id | canonical_key |
|---|---|
geographicPreferences |
college_prefs.location |
schoolSizePreference |
college_prefs.size |
campusSettingPreference |
college_prefs.setting |
primaryIntendedMajor |
academic.potential_majors |
Refine College List
| field id | canonical_key |
|---|---|
primaryMajorInterest |
academic.potential_majors |
geographicPreferences |
college_prefs.location |
schoolSizePreference |
college_prefs.size |
Review S.A.F.E. College Preferences (already has 8 — add remaining)
| field id | canonical_key |
|---|---|
majorInterests |
academic.potential_majors |
geographicPreferences |
college_prefs.location |
settingPreference |
college_prefs.setting |
FAFSA/CSS Profile Workshop
| field id | canonical_key |
|---|---|
fafsaStatus |
app.fafsa_status |
cssProfileStatus |
app.css_profile_status |
Senior Scholarship Bootcamp
| field id | canonical_key |
|---|---|
fafsaStatus |
app.fafsa_status |
cssProfileStatus |
app.css_profile_status |
First Essay Coach Meeting (counselor)
| field id | canonical_key |
|---|---|
personalStatementTopic |
app.personal_statement_topic |
essayTheme |
app.personal_statement_theme |
First Essay Coach Meeting (essay coach)
| field id | canonical_key |
|---|---|
writingConfidence |
app.writing_confidence |
selectedTheme |
app.personal_statement_theme |
Personal Statement Complete (counselor + essay coach)
| field id | canonical_key |
|---|---|
personalStatementStatus |
app.personal_statement_status |
wordCount |
app.personal_statement_word_count |
CA Activities List Finalized
| field id | canonical_key |
|---|---|
activitiesListStatus |
app.activities_list_status |
UC Activities List Finalized
| field id | canonical_key |
|---|---|
activitiesReadySubmission |
app.activities_list_status |
EA/ED1 Common App Review 1 + 2
| field id | canonical_key |
|---|---|
overallAppStatus |
app.common_app_status |
commonAppCompletion |
app.common_app_status |
supplementalEssaysStatus |
app.supplemental_essays_status |
Final EA/ED Common App Review
| field id | canonical_key |
|---|---|
applicationStatus |
app.common_app_status |
personalStatementStatus |
app.personal_statement_status |
activitiesReview |
app.activities_list_status |
supplementalEssaysStatus |
app.supplemental_essays_status |
Financial Aid Review Meeting
| field id | canonical_key |
|---|---|
financialAidAppealRecommended |
app.financial_aid_appeal_recommended |
Award Assessment & Decision Meeting
| field id | canonical_key |
|---|---|
targetDecisionDate |
app.target_decision_date |
Senior Scholarship Search
| field id | canonical_key |
|---|---|
targetNumberOfScholarshipApplications |
app.target_scholarship_applications |
Scholarship Interview Prep
| field id | canonical_key |
|---|---|
interviewConfidence |
app.interview_confidence |
Stand Out Factor Completed
| field id | canonical_key |
|---|---|
primaryStandoutFactor |
goals.stand_out_factor |
narrativeStrengthAssessment |
app.narrative_strength |
Portfolio Created
| field id | canonical_key |
|---|---|
portfolioQualityRating |
app.portfolio_quality |
portfolioPlatform |
app.portfolio_platform |
Private Budget Meeting (already has 2 — add remaining)
| field id | canonical_key |
|---|---|
efcEstimate |
financial.efc |
aidEligibility |
financial.need_based_fa_qualify |
meritAidPotential |
financial.merit_aid_potential |
keyConcerns |
financial.college_budget_notes |
Major by College Identified
| field id | canonical_key |
|---|---|
firstChoiceMajor |
academic.potential_majors |
General Major Identified
| field id | canonical_key |
|---|---|
primaryMajor |
academic.potential_majors |
secondaryMajor |
academic.potential_majors |
type on college-picker fields → college-selectIn scripts/seed-task-types.ts, update all fields with type: "college-picker" to type: "college-select". Affects:
edSchool, earlyActionSchoolsschoolsRemoved, schoolsAdded, reachSchools, targetSchools, safetySchoolscurrentCollegeList, reachSchools, targetSchools, likelySchools, schoolsToRemoveprioritySchoolsacceptedColleges, studentTopChoicesschoolsRequiringPortfoliocollegeNamefinancialAidPackagesReviewedTotal canonical_key additions: ~55 fields across 23 task types
One migration file:
-- 1. INSERT all 146 new registry entries (app.* + tracker_*.*)
INSERT INTO next_field_registry (field_key, display_name, category, data_type, ...)
VALUES ...; -- generated from seed-field-registry.js additions
-- 2. UPDATE task type field_schemas to add canonical_key
-- Pattern (same as HEL-678):
UPDATE next_task_types
SET field_schema = (
SELECT jsonb_agg(
CASE WHEN f->>'id' = '<field_id>'
THEN f || '{"canonical_key": "<registry_key>", "type": "<updated_type>"}'
ELSE f
END
)
FROM jsonb_array_elements(field_schema) f
)
WHERE name = '<task_name>';
Run scripts/seed-task-types.ts to apply updates to DB after SQL migration confirms clean.
e2e/app-tracker-canonical.spec.ts:
app.personal_statement_status field saves to canonical store on submitapp.ed_school (college-select) shows student's colleges, saves name string to canonicalgoals.stand_out_factor (Stand Out Factor Completed) saves to canonicale2e/college-select-field.spec.ts:
e2e/source-catalog-tracker.spec.ts:
app category → shows 35 entriestracker_app category → shows 60 entriesprisma/seed-field-registry.js — add 146 new registry entriesscripts/seed-task-types.ts — add canonical_key + update college-picker → college-select across 23 task typescomponents/ui/TaskFormRenderer.tsx — add college-select field type + studentId propcomponents/settings/TaskTypeEditor.tsx — add college-select to FIELD_TYPES listcomponents/settings/FieldRegistryManager.tsx — portal_tracker source badge + row-count display for tracker_* fieldscomponents/dashboard/TaskMilestoneModal.tsx — pass studentId to TaskFormRendererlib/canonical-profile.ts — handle portal_tracker source type in completeness queries (for Source Catalog stats)prisma/migrations/[ts]_app_tracker_registry.sql — INSERT 146 entries + UPDATE 23 task type field_schemasapp/api/students/[id]/college-list-options/route.ts — simplified college list for college-select dropdowne2e/app-tracker-canonical.spec.tse2e/college-select-field.spec.tse2e/source-catalog-tracker.spec.tscanonical-profile.ts aggregate query execution (aggregate fields are documented in registry, computed inline in tracker UI, not read via canonical profile reader)app.* entries exist in next_field_registrytracker_* catalog entries exist in next_field_registryportal_tracker source shows "App Tracker" badge in Source Catalogtracker_* entries display row count (not %) in completeness columncollege-select field type renders student's college list in task modalapp.ed_school field writes college name to next_student_profile_dataapp.personal_statement_status written to canonical store when Set Application Strategy task is savedgoals.stand_out_factor written to canonical store when Stand Out Factor Completed task is savedapp.fafsa_status written from both FAFSA Workshop and Senior Scholarship Bootcamp taskscollege-select type selectable in task type editor form builderscripts/seed-task-types.ts reflects all canonical_key additions and type changes