Ticket: HEL-681
Date: 2026-04-07
Three prior tickets (HEL-678, HEL-679, HEL-680) combined into one overnight execute run:
canonical_key on 22 Cat 2 task form fields, run HubSpot backfill scriptscreens/is_required/is_skippable to task types, create next_assigned_tasks, migrate onboarding steps into task types, add Supabase file upload, refactor onboarding route, remove OnboardingTabapp.* + tracker_*.*), add college-select field type, wire canonical_key on 55 more task fields, build full staff-facing App Tracker UI at /students/[id]/trackergit checkout main && git pull
git checkout -b feat/HEL-681-portal-data-layer
.claude/plans/HEL-678-task-field-canonical-wiring.md — exact field-by-field wiring table for Cat 2 fields.claude/plans/HEL-679-task-type-screens-unification.md — exact migration SQL, onboarding step definitions, component rewrites.claude/plans/HEL-680-app-tracker-canonical-wiring.md — exact tracker_* field table, app.* field table, task-type canonical_key mapping table.claude/plans/HEL-681-app-tracker-staff-ui.md — component architecture for the tracker UI.tmp/app-tracker-mockup.html — approved HTML prototype for the App Tracker staff UI. Use it as the pixel reference for CollegeTrackerGrid layout, color coding, and summary bar.
Commit after: feat(schema): add screens/assigned-tasks, drop onboarding tables [HEL-681]
next_task_types in prisma/schema.prismaAdd three fields:
screens String[] @default([])
is_required Boolean @default(false)
is_skippable Boolean @default(false)
next_assigned_tasks modelmodel next_assigned_tasks {
id Int @id @default(autoincrement())
user_type String @default("student") @db.VarChar(20)
user_id Int
task_type_id Int
status String @default("pending") @db.VarChar(20)
assigned_by_id Int?
completed_at DateTime?
skipped_at DateTime?
created_at DateTime @default(now()) @db.Timestamptz(6)
updated_at DateTime @updatedAt @db.Timestamptz(6)
task_type next_task_types @relation(fields: [task_type_id], references: [id])
@@unique([user_type, user_id, task_type_id])
@@index([user_type, user_id])
}
Also add the inverse relation to next_task_types:
assigned_tasks next_assigned_tasks[]
Delete the model blocks for next_onboarding_steps and next_onboarding_completions.
prisma generatenpx prisma generate
Verify no TypeScript errors before continuing.
Commit after: feat(migration): task-type screens, assigned-tasks, onboarding steps as task-types [HEL-681]
Create prisma/migrations/[timestamp]_portal_data_layer/migration.sql
-- ============================================================
-- PART A: next_task_types new columns
-- ============================================================
ALTER TABLE next_task_types
ADD COLUMN IF NOT EXISTS screens TEXT[] NOT NULL DEFAULT '{}',
ADD COLUMN IF NOT EXISTS is_required BOOLEAN NOT NULL DEFAULT false,
ADD COLUMN IF NOT EXISTS is_skippable BOOLEAN NOT NULL DEFAULT false;
-- ============================================================
-- PART B: next_assigned_tasks
-- ============================================================
CREATE TABLE IF NOT EXISTS next_assigned_tasks (
id SERIAL PRIMARY KEY,
user_type VARCHAR(20) NOT NULL DEFAULT 'student',
user_id INTEGER NOT NULL,
task_type_id INTEGER NOT NULL REFERENCES next_task_types(id),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
assigned_by_id INTEGER,
completed_at TIMESTAMPTZ,
skipped_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (user_type, user_id, task_type_id)
);
CREATE INDEX IF NOT EXISTS idx_assigned_tasks_user ON next_assigned_tasks (user_type, user_id);
-- ============================================================
-- PART C: Assign screens=['session'] to all current task types
-- ============================================================
UPDATE next_task_types
SET screens = ARRAY['session'],
category = CASE
WHEN 'counseling' = ANY(departments) THEN 'counseling'
WHEN 'tutoring' = ANY(departments) THEN 'tutoring'
WHEN 'essay' = ANY(departments) THEN 'essay'
ELSE category
END
WHERE is_active = true;
-- ============================================================
-- PART D: Insert onboarding steps as task types
-- ============================================================
-- Student: Contact Info (is_required)
INSERT INTO next_task_types (name, label, description, category, field_schema, screens, is_required, is_skippable, sort_order, is_active)
VALUES (
'profile_contact',
'Contact Info',
'Basic contact information',
'onboarding',
'[
{"id":"firstName","label":"First Name","type":"text","canonical_key":"contact.first_name","required":true},
{"id":"lastName","label":"Last Name","type":"text","canonical_key":"contact.last_name","required":true},
{"id":"email","label":"Email","type":"text","canonical_key":"contact.email"},
{"id":"phone","label":"Phone","type":"text","canonical_key":"contact.phone"},
{"id":"address","label":"Street Address","type":"text","canonical_key":"contact.address"},
{"id":"city","label":"City","type":"text","canonical_key":"contact.city"},
{"id":"state","label":"State","type":"text","canonical_key":"contact.state"},
{"id":"zip","label":"ZIP Code","type":"text","canonical_key":"contact.zip"}
]'::jsonb,
ARRAY['student_onboarding'],
true, false, 2, true
) ON CONFLICT (name) DO NOTHING;
-- Student: Academic Background
INSERT INTO next_task_types (name, label, description, category, field_schema, screens, is_required, is_skippable, sort_order, is_active)
VALUES (
'profile_academic',
'Academic Background',
'Your high school and academic history',
'onboarding',
'[
{"id":"highSchool","label":"High School","type":"text","canonical_key":"academic.high_school"},
{"id":"graduationYear","label":"Graduation Year","type":"text","canonical_key":"academic.graduation_year"},
{"id":"gpaWeighted","label":"Weighted GPA","type":"text","canonical_key":"academic.weighted_gpa"},
{"id":"gpaUnweighted","label":"Unweighted GPA","type":"text","canonical_key":"academic.unweighted_gpa"},
{"id":"classRank","label":"Class Rank","type":"text","canonical_key":"academic.class_rank"},
{"id":"currentMathClass","label":"Current Math Class","type":"text","canonical_key":"academic.survey_current_math_class"},
{"id":"apIbCourses","label":"AP / IB Courses Taken","type":"textarea","canonical_key":"academic.ap_ib_courses"}
]'::jsonb,
ARRAY['student_onboarding'],
false, true, 3, true
) ON CONFLICT (name) DO NOTHING;
-- Student: Major & Career
INSERT INTO next_task_types (name, label, description, category, field_schema, screens, is_required, is_skippable, sort_order, is_active)
VALUES (
'profile_major_career',
'Major & Career',
'Your academic and career interests',
'onboarding',
'[
{"id":"potentialMajors","label":"Potential Majors","type":"textarea","canonical_key":"academic.potential_majors"},
{"id":"careerInterests","label":"Career Interests","type":"textarea","canonical_key":"activities.career_interests"},
{"id":"personalBrand","label":"Personal Brand / Theme","type":"textarea","canonical_key":"goals.personal_brand"}
]'::jsonb,
ARRAY['student_onboarding'],
false, true, 4, true
) ON CONFLICT (name) DO NOTHING;
-- Student: Activities & Interests
INSERT INTO next_task_types (name, label, description, category, field_schema, screens, is_required, is_skippable, sort_order, is_active)
VALUES (
'profile_activities',
'Activities & Interests',
'Extracurriculars, sports, and hobbies',
'onboarding',
'[
{"id":"extracurriculars","label":"Extracurricular Activities","type":"textarea","canonical_key":"activities.extracurriculars"},
{"id":"sports","label":"Sports","type":"textarea","canonical_key":"activities.sports"},
{"id":"communityService","label":"Community Service","type":"textarea","canonical_key":"activities.community_service"},
{"id":"favoriteActivities","label":"Favorite Activities","type":"textarea","canonical_key":"activities.favorite_activities"}
]'::jsonb,
ARRAY['student_onboarding'],
false, true, 5, true
) ON CONFLICT (name) DO NOTHING;
-- Student: College Preferences
INSERT INTO next_task_types (name, label, description, category, field_schema, screens, is_required, is_skippable, sort_order, is_active)
VALUES (
'profile_college_prefs',
'College Preferences',
'What you''re looking for in a college',
'onboarding',
'[
{"id":"locationPreference","label":"Location Preference","type":"textarea","canonical_key":"college_prefs.location"},
{"id":"sizePreference","label":"School Size Preference","type":"select","canonical_key":"college_prefs.size","options":["Small (<5,000)","Medium (5,000–15,000)","Large (>15,000)","No preference"]},
{"id":"settingPreference","label":"Campus Setting","type":"select","canonical_key":"college_prefs.setting","options":["Urban","Suburban","Rural","No preference"]},
{"id":"studyAbroad","label":"Interest in Study Abroad","type":"select","canonical_key":"college_prefs.study_abroad","options":["Very interested","Somewhat interested","Not interested"]}
]'::jsonb,
ARRAY['student_onboarding'],
false, true, 6, true
) ON CONFLICT (name) DO NOTHING;
-- Student: About You
INSERT INTO next_task_types (name, label, description, category, field_schema, screens, is_required, is_skippable, sort_order, is_active)
VALUES (
'profile_about_you',
'About You',
'Background and personal information',
'onboarding',
'[
{"id":"birthDate","label":"Date of Birth","type":"date","canonical_key":"demographics.birth_date"},
{"id":"ethnicity","label":"Hispanic/Latino Ethnicity","type":"select","canonical_key":"demographics.ethnicity","options":["Yes, Hispanic/Latino","No, not Hispanic/Latino"]},
{"id":"race","label":"Race","type":"select","canonical_key":"demographics.race","options":["White","Asian","Black or African American","American Indian or Alaska Native","Native Hawaiian or Other Pacific Islander"]},
{"id":"citizenshipStatus","label":"Citizenship Status","type":"select","canonical_key":"demographics.citizenship_status","options":["U.S. Citizen or U.S. National","U.S. Dual Citizen","U.S. Permanent Resident","Other (non-U.S.)","U.S. Refugee or Asylee"]}
]'::jsonb,
ARRAY['student_onboarding'],
false, true, 7, true
) ON CONFLICT (name) DO NOTHING;
-- Student: Notification Preferences (5 boolean canonical fields)
INSERT INTO next_task_types (name, label, description, category, field_schema, screens, is_required, is_skippable, sort_order, is_active)
VALUES (
'profile_notifications',
'Notification Preferences',
'How you''d like to receive updates',
'onboarding',
'[
{"id":"smsMeetingReminders","label":"SMS Meeting Reminders","type":"boolean","canonical_key":"prefs.sms_meeting_reminders"},
{"id":"smsTaskReminders","label":"SMS Task Reminders","type":"boolean","canonical_key":"prefs.sms_task_reminders"},
{"id":"emailMeetingReminders","label":"Email Meeting Reminders","type":"boolean","canonical_key":"prefs.email_meeting_reminders"},
{"id":"emailWeeklySummary","label":"Email Weekly Summary","type":"boolean","canonical_key":"prefs.email_weekly_summary"},
{"id":"emailNewsletters","label":"Email Newsletters","type":"boolean","canonical_key":"prefs.email_newsletters"}
]'::jsonb,
ARRAY['student_onboarding'],
false, true, 8, true
) ON CONFLICT (name) DO NOTHING;
-- Student: Upload Transcript
INSERT INTO next_task_types (name, label, description, category, field_schema, screens, is_required, is_skippable, sort_order, is_active)
VALUES (
'profile_transcript',
'Upload Transcript',
'Upload your most recent transcript',
'onboarding',
'[
{"id":"transcriptUrl","label":"Transcript","type":"file-upload","canonical_key":"documents.transcript_url","accept":".pdf,.jpg,.png","maxFiles":1}
]'::jsonb,
ARRAY['student_onboarding'],
false, true, 9, true
) ON CONFLICT (name) DO NOTHING;
-- Parent: Contact Info (is_required)
INSERT INTO next_task_types (name, label, description, category, field_schema, screens, is_required, is_skippable, sort_order, is_active)
VALUES (
'parent_contact',
'Your Contact Info',
'Parent or guardian contact information',
'onboarding',
'[
{"id":"firstName","label":"First Name","type":"text","canonical_key":"contact.first_name","required":true},
{"id":"lastName","label":"Last Name","type":"text","canonical_key":"contact.last_name","required":true},
{"id":"email","label":"Email","type":"text","canonical_key":"contact.email"},
{"id":"phone","label":"Phone","type":"text","canonical_key":"contact.phone"},
{"id":"employer","label":"Employer","type":"text","canonical_key":"family.parent1_employer"},
{"id":"industry","label":"Industry / Occupation","type":"text","canonical_key":"family.parent1_industry"}
]'::jsonb,
ARRAY['parent_onboarding'],
true, false, 2, true
) ON CONFLICT (name) DO NOTHING;
-- Parent: Family Background
INSERT INTO next_task_types (name, label, description, category, field_schema, screens, is_required, is_skippable, sort_order, is_active)
VALUES (
'parent_background',
'Family Background',
'Family and financial background',
'onboarding',
'[
{"id":"custodialStatus","label":"Custodial Parent Status","type":"select","canonical_key":"family.custodial_parent_status","options":["Married/Together","Divorced/Separated","Single parent","Other"]},
{"id":"parentAlmaMater","label":"Parent Alma Mater","type":"text","canonical_key":"family.parent1_alma_mater"},
{"id":"totalChildrenCollege","label":"Children Going to College","type":"text","canonical_key":"family.total_children_in_college"},
{"id":"agiRange","label":"Adjusted Gross Income Range","type":"select","canonical_key":"financial.agi_range","options":["Under $50K","$50K–$75K","$75K–$100K","$100K–$150K","$150K–$200K","Over $200K"]}
]'::jsonb,
ARRAY['parent_onboarding'],
false, true, 3, true
) ON CONFLICT (name) DO NOTHING;
-- ============================================================
-- PART E: Drop old onboarding tables
-- ============================================================
DROP TABLE IF EXISTS next_onboarding_completions;
DROP TABLE IF EXISTS next_onboarding_steps;
-- ============================================================
-- PART F: Fix 11 broken HubSpot source_mappings
-- ============================================================
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"act_score"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='academic.act_score';
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"sat_score"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='academic.sat_score';
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"student_engagement"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='activities.student_engagement';
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"total_children_college"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='family.total_children_in_college';
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"adjusted_gross_income"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='financial.agi_range';
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"college_budget_notes"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='financial.college_budget_notes';
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"merit_aid_potential"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='financial.merit_aid_potential';
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"total_funds_saved"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='financial.total_funds_saved';
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"how_did_you_hear"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='goals.how_did_you_hear';
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"referral_type"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='goals.referral_type';
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(CASE WHEN m->>'source'='hubspot' THEN m || '{"property":"referred_by"}' ELSE m END) FROM jsonb_array_elements(source_mappings) m) WHERE field_key='goals.referred_by';
-- goals.special_circumstances: remove hubspot mapping (no matching column exists)
UPDATE next_field_registry SET source_mappings = (SELECT jsonb_agg(m) FROM jsonb_array_elements(source_mappings) m WHERE m->>'source' != 'hubspot') WHERE field_key='goals.special_circumstances';
-- ============================================================
-- PART G: ethnicity/race/citizenship source_mappings consolidation
-- ============================================================
UPDATE next_field_registry SET source_mappings = jsonb_build_array(jsonb_build_object('source','legacy_db','table','person_ethnic_infos','column','ethnicity')) || COALESCE(source_mappings, '[]'::jsonb) WHERE field_key='demographics.ethnicity';
UPDATE next_field_registry SET source_mappings = jsonb_build_array(jsonb_build_object('source','legacy_db','table','person_ethnic_infos','column','race')) || COALESCE(source_mappings, '[]'::jsonb) WHERE field_key='demographics.race';
UPDATE next_field_registry SET source_mappings = jsonb_build_array(jsonb_build_object('source','legacy_db','table','person_ethnic_infos','column','citizenship_status')) WHERE field_key='demographics.citizenship_status';
-- Deactivate _db duplicates
UPDATE next_field_registry SET is_active=false WHERE field_key IN ('ethnicity_db','race_db','citizenship_status_db','athletic_rec_db');
-- ============================================================
-- PART H: New registry entries — 6 prefs.* + 1 documents.*
-- ============================================================
INSERT INTO next_field_registry (field_key, display_name, category, data_type, visibility, is_active, source_mappings) VALUES
('prefs.sms_meeting_reminders','SMS Meeting Reminders','prefs','boolean','all',true,'[]'::jsonb),
('prefs.sms_task_reminders','SMS Task Reminders','prefs','boolean','all',true,'[]'::jsonb),
('prefs.email_meeting_reminders','Email Meeting Reminders','prefs','boolean','all',true,'[]'::jsonb),
('prefs.email_weekly_summary','Email Weekly Summary','prefs','boolean','all',true,'[]'::jsonb),
('prefs.email_newsletters','Email Newsletters','prefs','boolean','all',true,'[]'::jsonb),
('documents.transcript_url','Transcript File','documents','text','all',true,'[]'::jsonb)
ON CONFLICT (field_key) DO NOTHING;
-- ============================================================
-- PART I: New registry entries — 11 Cat 3A academic fields
-- ============================================================
INSERT INTO next_field_registry (field_key, display_name, category, data_type, visibility, is_active, source_mappings) VALUES
('academic.target_act_score','Target ACT Score','academic','number','all',false,'[]'::jsonb),
('academic.target_sat_score','Target SAT Score','academic','number','all',false,'[]'::jsonb),
('academic.target_gpa','Target GPA','academic','text','all',false,'[]'::jsonb),
('academic.class_rank','Class Rank','academic','text','all',false,'[]'::jsonb),
('academic.ap_ib_courses','AP / IB Courses Taken','academic','textarea','all',false,'[]'::jsonb),
('academic.ap_ib_course_count','AP / IB Course Count','academic','number','all',false,'[]'::jsonb),
('college_selection.committed_school','Committed School','college_selection','text','all',false,'[]'::jsonb),
('goals.personal_brand','Personal Brand / Theme','goals','textarea','all',false,'[]'::jsonb),
('goals.stand_out_factor','Stand Out Factor','goals','text','all',false,'[]'::jsonb),
('activities.summer_programs','Summer Programs / Camps','activities','textarea','all',false,'[]'::jsonb)
ON CONFLICT (field_key) DO NOTHING;
-- ============================================================
-- PART J: New registry entries — 35 app.* canonical fields
-- ============================================================
INSERT INTO next_field_registry (field_key, display_name, category, data_type, visibility, is_active, source_mappings) VALUES
('app.personal_statement_status','Personal Statement Status','app','select','all',true,'[]'::jsonb),
('app.personal_statement_topic','Personal Statement Topic','app','text','all',true,'[]'::jsonb),
('app.personal_statement_theme','Personal Statement Theme','app','select','all',true,'[]'::jsonb),
('app.personal_statement_word_count','Personal Statement Word Count','app','number','all',true,'[]'::jsonb),
('app.activities_list_status','Activities List Status','app','select','all',true,'[]'::jsonb),
('app.lor_status','Recommendation Letter Status','app','select','all',true,'[]'::jsonb),
('app.fafsa_status','FAFSA Status','app','select','all',true,'[]'::jsonb),
('app.css_profile_status','CSS Profile Status','app','select','all',true,'[]'::jsonb),
('app.ed_school','Early Decision School','app','text','all',true,'[]'::jsonb),
('app.family_ed_consensus','Family ED Consensus Confirmed','app','boolean','all',true,'[]'::jsonb),
('app.common_app_status','Common App Status','app','select','all',true,'[]'::jsonb),
('app.supplemental_essays_status','Supplemental Essays Status','app','select','all',true,'[]'::jsonb),
('app.narrative_strength','Application Narrative Strength','app','select','all',true,'[]'::jsonb),
('app.writing_confidence','Student Writing Confidence','app','select','all',true,'[]'::jsonb),
('app.target_decision_date','Target College Decision Date','app','date','all',true,'[]'::jsonb),
('app.financial_aid_appeal_recommended','Financial Aid Appeal Recommended','app','boolean','all',true,'[]'::jsonb),
('app.target_scholarship_applications','Target # Scholarship Applications','app','number','all',true,'[]'::jsonb),
('app.portfolio_quality','Portfolio Quality Rating','app','select','all',true,'[]'::jsonb),
('app.portfolio_platform','Portfolio Submission Platform','app','select','all',true,'[]'::jsonb),
('app.interview_confidence','Interview Confidence Level','app','select','all',true,'[]'::jsonb),
('app.college_count','Total Colleges on List','app','number','all',true,'[{"source":"portal_tracker","table":"selected_colleges","aggregate":"count"}]'::jsonb),
('app.applied_count','Applied Count','app','number','all',true,'[{"source":"portal_tracker","table":"selected_colleges","aggregate":"count_where","column":"applied","value":true}]'::jsonb),
('app.accepted_count','Accepted Count','app','number','all',true,'[{"source":"portal_tracker","table":"selected_colleges","aggregate":"count_where","column":"application_status","value":"Accepted"}]'::jsonb),
('app.safety_count','Safety / Likely Count','app','number','all',true,'[{"source":"portal_tracker","table":"selected_colleges","aggregate":"count_tier","value":"Safety"}]'::jsonb),
('app.target_count','Target Count','app','number','all',true,'[{"source":"portal_tracker","table":"selected_colleges","aggregate":"count_tier","value":"Target"}]'::jsonb),
('app.reach_count','Reach Count','app','number','all',true,'[{"source":"portal_tracker","table":"selected_colleges","aggregate":"count_tier","value":"Reach"}]'::jsonb),
('app.total_essays_assigned','Total Essays Assigned','app','number','all',true,'[{"source":"portal_tracker","table":"next_student_essays","aggregate":"count"}]'::jsonb),
('app.essays_submitted','Essays Submitted','app','number','all',true,'[{"source":"portal_tracker","table":"next_student_essays","aggregate":"count_where","column":"status","value":"submitted"}]'::jsonb),
('app.total_recommendations','Total Recommenders','app','number','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","aggregate":"count"}]'::jsonb),
('app.recommendations_confirmed','Recommendations Confirmed','app','number','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","aggregate":"count_where","column":"status","value":"confirmed"}]'::jsonb),
('app.total_scholarships_tracked','Scholarships Tracked','app','number','all',true,'[{"source":"portal_tracker","table":"next_student_scholarships","aggregate":"count"}]'::jsonb),
('app.scholarships_awarded','Scholarships Awarded','app','number','all',true,'[{"source":"portal_tracker","table":"next_student_scholarships","aggregate":"count_where","column":"status","value":"awarded"}]'::jsonb),
('app.total_activities_listed','Activities Listed','app','number','all',true,'[{"source":"portal_tracker","table":"next_student_activities","aggregate":"count"}]'::jsonb),
('app.milestones_complete','Milestones Complete','app','number','all',true,'[{"source":"portal_tracker","table":"next_student_cap_progress","aggregate":"count_where","column":"completed","value":true}]'::jsonb),
('app.milestones_total','Total Milestones','app','number','all',true,'[{"source":"portal_tracker","table":"next_cap_milestones","aggregate":"count"}]'::jsonb)
ON CONFLICT (field_key) DO NOTHING;
-- ============================================================
-- PART K: tracker_* catalog entries (111 fields)
-- See HEL-680 plan for full field list. Insert with source portal_tracker.
-- ============================================================
-- tracker_app (sample — executor generates all 60 from next_student_app_details column list)
INSERT INTO next_field_registry (field_key, display_name, category, data_type, visibility, is_active, source_mappings) VALUES
('tracker_app.application_type','Application Type','tracker_app','text','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"application_type"}]'::jsonb),
('tracker_app.submitted','Submitted','tracker_app','boolean','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"submitted"}]'::jsonb),
('tracker_app.submit_date_target','Target Submission Date','tracker_app','date','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"submit_date_target"}]'::jsonb),
('tracker_app.submit_date_actual','Actual Submission Date','tracker_app','date','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"submit_date_actual"}]'::jsonb),
('tracker_app.school_decision','School Decision','tracker_app','text','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"school_decision"}]'::jsonb),
('tracker_app.school_decision_date','Decision Date','tracker_app','date','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"school_decision_date"}]'::jsonb),
('tracker_app.app_deadline','Application Deadline','tracker_app','date','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"app_deadline"}]'::jsonb),
('tracker_app.ed_deadline','ED Deadline','tracker_app','date','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"ed_deadline"}]'::jsonb),
('tracker_app.ea_deadline','EA Deadline','tracker_app','date','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"ea_deadline"}]'::jsonb),
('tracker_app.rd_deadline','RD Deadline','tracker_app','date','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"rd_deadline"}]'::jsonb),
('tracker_app.financial_deadline','Financial Aid Deadline','tracker_app','date','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"financial_deadline"}]'::jsonb),
('tracker_app.merit_deadline','Merit Aid Deadline','tracker_app','date','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"merit_deadline"}]'::jsonb),
('tracker_app.test_optional','Test Optional','tracker_app','boolean','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"test_optional"}]'::jsonb),
('tracker_app.superscores','Superscores Accepted','tracker_app','boolean','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"superscores"}]'::jsonb),
('tracker_app.should_report_scores','Should Report Scores','tracker_app','boolean','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"should_report_scores"}]'::jsonb),
('tracker_app.css_profile_required','CSS Profile Required','tracker_app','boolean','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"css_profile_required"}]'::jsonb),
('tracker_app.coa_amount','Cost of Attendance','tracker_app','number','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"coa_amount"}]'::jsonb),
('tracker_app.tuition','Tuition','tracker_app','number','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"tuition"}]'::jsonb),
('tracker_app.oos_surcharge','Out-of-State Surcharge','tracker_app','number','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"oos_surcharge"}]'::jsonb),
('tracker_app.school_decision','School Decision','tracker_app','text','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"school_decision"}]'::jsonb),
('tracker_app.counselor_review_notes','Counselor Review Notes','tracker_app','textarea','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"counselor_review_notes"}]'::jsonb),
('tracker_app.portal_link','Application Portal Link','tracker_app','text','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"portal_link"}]'::jsonb),
('tracker_app.coa_link','COA Link','tracker_app','text','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"coa_link"}]'::jsonb),
('tracker_app.npc_link','Net Price Calculator Link','tracker_app','text','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"npc_link"}]'::jsonb),
('tracker_app.student_rank','Student Rank','tracker_app','number','all',true,'[{"source":"portal_tracker","table":"next_student_app_details","column":"student_rank"}]'::jsonb)
ON CONFLICT (field_key) DO NOTHING;
-- EXECUTOR: run this query to get remaining next_student_app_details columns not yet inserted,
-- then generate matching INSERT statements following the same pattern above:
-- SELECT column_name FROM information_schema.columns WHERE table_name='next_student_app_details' ORDER BY ordinal_position;
-- tracker_essay
INSERT INTO next_field_registry (field_key, display_name, category, data_type, visibility, is_active, source_mappings) VALUES
('tracker_essay.essay_name','Essay Name','tracker_essay','text','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"essay_name"}]'::jsonb),
('tracker_essay.essay_type','Essay Type','tracker_essay','text','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"essay_type"}]'::jsonb),
('tracker_essay.prompt','Prompt','tracker_essay','textarea','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"prompt"}]'::jsonb),
('tracker_essay.theme','Theme','tracker_essay','text','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"theme"}]'::jsonb),
('tracker_essay.word_count_limit','Word Count Limit','tracker_essay','number','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"word_count_limit"}]'::jsonb),
('tracker_essay.current_word_count','Current Word Count','tracker_essay','number','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"current_word_count"}]'::jsonb),
('tracker_essay.status','Essay Status','tracker_essay','select','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"status"}]'::jsonb),
('tracker_essay.writing_order','Writing Order','tracker_essay','number','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"writing_order"}]'::jsonb),
('tracker_essay.my_target_date','Student Target Date','tracker_essay','date','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"my_target_date"}]'::jsonb),
('tracker_essay.first_draft_date','First Draft Date','tracker_essay','date','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"first_draft_date"}]'::jsonb),
('tracker_essay.final_draft_date','Final Draft Date','tracker_essay','date','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"final_draft_date"}]'::jsonb),
('tracker_essay.notes','Notes','tracker_essay','textarea','all',true,'[{"source":"portal_tracker","table":"next_student_essays","column":"notes"}]'::jsonb)
ON CONFLICT (field_key) DO NOTHING;
-- tracker_rec
INSERT INTO next_field_registry (field_key, display_name, category, data_type, visibility, is_active, source_mappings) VALUES
('tracker_rec.recommender_name','Recommender Name','tracker_rec','text','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"recommender_name"}]'::jsonb),
('tracker_rec.rec_type','Recommendation Type','tracker_rec','text','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"rec_type"}]'::jsonb),
('tracker_rec.relationship','Relationship','tracker_rec','text','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"relationship"}]'::jsonb),
('tracker_rec.email','Email','tracker_rec','text','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"email"}]'::jsonb),
('tracker_rec.date_requested','Date Requested','tracker_rec','date','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"date_requested"}]'::jsonb),
('tracker_rec.resume_sent','Resume Sent','tracker_rec','date','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"resume_sent"}]'::jsonb),
('tracker_rec.invite_sent','Invite Sent','tracker_rec','date','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"invite_sent"}]'::jsonb),
('tracker_rec.follow_up_sent','Follow-up Sent','tracker_rec','date','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"follow_up_sent"}]'::jsonb),
('tracker_rec.status','Status','tracker_rec','select','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"status"}]'::jsonb),
('tracker_rec.due_date','Due Date','tracker_rec','date','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"due_date"}]'::jsonb),
('tracker_rec.notes','Notes','tracker_rec','textarea','all',true,'[{"source":"portal_tracker","table":"next_student_recommenders","column":"notes"}]'::jsonb)
ON CONFLICT (field_key) DO NOTHING;
-- tracker_scholarship, tracker_activity, tracker_milestone, tracker_interest:
-- Follow the same INSERT pattern. See HEL-680 plan for full field lists.
-- Run: SELECT column_name FROM information_schema.columns WHERE table_name IN ('next_student_scholarships','next_student_activities','next_cap_milestones','next_student_demonstrated_interest') ORDER BY table_name, ordinal_position;
-- ============================================================
-- PART L: Wire canonical_key on 22 Cat 2 task form fields (HEL-678)
-- ============================================================
-- Pattern: update field_schema JSONB in next_task_types
-- Full wiring table in HEL-678 plan. Key examples:
UPDATE next_task_types SET field_schema=(SELECT jsonb_agg(CASE WHEN f->>'id'='rigorAssessment' THEN f||'{"canonical_key":"academic.academic_rigor"}' ELSE f END) FROM jsonb_array_elements(field_schema) f) WHERE name='11th Grade Course Selection';
UPDATE next_task_types SET field_schema=(SELECT jsonb_agg(CASE WHEN f->>'id'='extracurriculars' THEN f||'{"canonical_key":"activities.extracurriculars"}' WHEN f->>'id'='honors' THEN f||'{"canonical_key":"activities.awards"}' WHEN f->>'id'='leadershipRoles' THEN f||'{"canonical_key":"activities.leadership"}' ELSE f END) FROM jsonb_array_elements(field_schema) f) WHERE name='Activities & Honors Workshop';
UPDATE next_task_types SET field_schema=(SELECT jsonb_agg(CASE WHEN f->>'id'='communityService' THEN f||'{"canonical_key":"activities.community_service"}' WHEN f->>'id'='workExperience' THEN f||'{"canonical_key":"activities.employment"}' WHEN f->>'id'='primarySport' THEN f||'{"canonical_key":"activities.sports"}' ELSE f END) FROM jsonb_array_elements(field_schema) f) WHERE name='Develop Resume';
-- EXECUTOR: apply all 22 field wirings from the HEL-678 plan wiring table.
-- ============================================================
-- PART M: Wire canonical_key on ~55 app-tracker task fields (HEL-680)
-- ============================================================
-- Full wiring table in HEL-680 plan. Key examples:
UPDATE next_task_types SET field_schema=(SELECT jsonb_agg(CASE WHEN f->>'id'='personalStatementStatus' THEN f||'{"canonical_key":"app.personal_statement_status"}' WHEN f->>'id'='activitiesListStatus' THEN f||'{"canonical_key":"app.activities_list_status"}' WHEN f->>'id'='lorStatus' THEN f||'{"canonical_key":"app.lor_status"}' WHEN f->>'id'='edSchool' THEN f||'{"canonical_key":"app.ed_school","type":"college-select"}' WHEN f->>'id'='familyEdConsensus' THEN f||'{"canonical_key":"app.family_ed_consensus"}' ELSE f END) FROM jsonb_array_elements(field_schema) f) WHERE name='Set Application Strategy';
UPDATE next_task_types SET field_schema=(SELECT jsonb_agg(CASE WHEN f->>'id'='fafsaStatus' THEN f||'{"canonical_key":"app.fafsa_status"}' WHEN f->>'id'='cssProfileStatus' THEN f||'{"canonical_key":"app.css_profile_status"}' ELSE f END) FROM jsonb_array_elements(field_schema) f) WHERE name IN ('FAFSA/CSS Profile Workshop','Senior Scholarship Bootcamp');
UPDATE next_task_types SET field_schema=(SELECT jsonb_agg(CASE WHEN f->>'id'='primaryStandoutFactor' THEN f||'{"canonical_key":"goals.stand_out_factor"}' WHEN f->>'id'='narrativeStrengthAssessment' THEN f||'{"canonical_key":"app.narrative_strength"}' ELSE f END) FROM jsonb_array_elements(field_schema) f) WHERE name='Stand Out Factor Completed';
UPDATE next_task_types SET field_schema=(SELECT jsonb_agg(CASE WHEN f->>'id'='portfolioQualityRating' THEN f||'{"canonical_key":"app.portfolio_quality"}' WHEN f->>'id'='portfolioPlatform' THEN f||'{"canonical_key":"app.portfolio_platform"}' ELSE f END) FROM jsonb_array_elements(field_schema) f) WHERE name='Portfolio Created';
-- EXECUTOR: apply all remaining wirings from the HEL-680 plan Step 5 wiring table.
-- Also update all college-picker → college-select type fields in same UPDATE statements.
-- ============================================================
-- PART N: Verify
-- ============================================================
-- Run these after migration to confirm:
SELECT COUNT(*) FROM next_field_registry WHERE field_key LIKE 'app.%'; -- expect 35
SELECT COUNT(*) FROM next_field_registry WHERE category LIKE 'tracker_%'; -- expect 111
SELECT COUNT(*) FROM next_field_registry WHERE field_key LIKE 'prefs.%'; -- expect at least 5
SELECT source_mappings FROM next_field_registry WHERE field_key='academic.act_score'; -- expect property="act_score"
SELECT COUNT(*) FROM next_task_types WHERE 'student_onboarding' = ANY(screens); -- expect 8
SELECT COUNT(*) FROM next_task_types WHERE 'session' = ANY(screens); -- expect all old task types
Run migration:
npx prisma migrate dev --name portal_data_layer
Commit after: feat(seeds): field-registry + task-types canonical wiring [HEL-681]
prisma/seed-field-registry.jsMirror ALL changes from Phase 2 Parts F–K into the seed file so npm run db:seed stays in sync. Fix all 11 HubSpot property names, add all new entries. Deactivate _db duplicates.
scripts/seed-task-types.tsFor every canonical_key addition and college-picker → college-select change applied in SQL, update the matching field object in the seed file. Run npx ts-node scripts/seed-task-types.ts --dry-run (or equivalent) to verify.
Commit after: feat(api): screens/assigned-tasks/upload/onboarding/tracker routes [HEL-681]
app/api/task-types/route.tsscreens, is_required, is_skippable in POST/PUT body?screen= query param filter: WHERE screens @> ARRAY[$screen]app/api/task-types/categories/route.ts (new)GET → SELECT DISTINCT category FROM next_task_types
WHERE category IS NOT NULL AND category != ''
ORDER BY category
Returns string[].
app/api/onboarding/complete-step/route.ts (rewrite)// Body: { assignedId: number }
// UPDATE next_assigned_tasks SET status='completed', completed_at=NOW()
// WHERE id=$assignedId AND user_id=$userId
// Return { allDone: boolean }
Auth: student or employee (impersonation via studentId query param).
app/api/onboarding/skip-step/route.ts (rewrite)// Body: { assignedId: number }
// Verify task_type.is_skippable = true (join on task_type_id)
// UPDATE next_assigned_tasks SET status='skipped', skipped_at=NOW()
// WHERE id=$assignedId AND user_id=$userId
app/api/upload/student-file/route.ts (new)// POST multipart: { file, studentId }
// Auth: student or employee
// Upload to student-files/students/{studentId}/{filename} via lib/supabase-storage.ts uploadFile()
// Return { path, signedUrl } (TTL 300s)
app/api/upload/student-file/signed-url/route.ts (new)// GET ?path=...
// Return { signedUrl } (TTL 300s)
// Auth: student or employee
app/api/students/[id]/college-list-options/route.ts (new)// GET → selected_colleges WHERE student_id=$id AND removed_at IS NULL
// JOIN colleges ON id=college_id
// Return [{ id, name, priorityType }] ordered by priority
// Auth: student (own) or employee
app/api/admin/portal-settings/route.ts (new)Move logic from app/api/admin/onboarding/settings/route.ts. Keep old route as a 301 redirect to new path.
app/api/student/selected-college/route.tsAdd PATCH handler:
// Body: { id, field, value }
// UPDATE selected_colleges SET {field}={value} WHERE id=$id
// Allowed fields: applied, major1, major2, priority, application_status
Commit after: feat(lib): onboarding-types, onboarding-check, canonical-profile [HEL-681]
lib/onboarding-types.tsReplace OnboardingStep interface with TaskTypeStep:
export interface TaskTypeStep {
id: number;
name: string;
label: string;
description: string | null;
field_schema: FieldDef[];
is_required: boolean;
is_skippable: boolean;
sort_order: number;
status: "pending" | "completed" | "skipped";
assigned_id: number; // next_assigned_tasks.id
}
lib/onboarding-check.ts (rewrite)async function checkOnboardingRequired(userId, userType, currentPath) {
if (currentPath.startsWith('/onboarding')) return { required: false };
const settings = await prisma.next_onboarding_settings.findFirst();
const enforceKey = userType === 'parent' ? 'enforce_parents' : 'enforce_students';
if (!settings?.[enforceKey]) return { required: false };
const taskTypes = await prisma.next_task_types.findMany({
where: { screens: { has: `${userType}_onboarding` }, is_active: true }
});
const assignments = await prisma.next_assigned_tasks.findMany({
where: { user_type: userType, user_id: userId }
});
const completedIds = new Set(assignments.filter(a => a.status === 'completed').map(a => a.task_type_id));
const pending = taskTypes.filter(tt => tt.is_required && !completedIds.has(tt.id));
return { required: pending.length > 0 };
}
lib/canonical-profile.tsAdd handling for portal_tracker source type in completeness computation. When a field's source_mappings contains source: "portal_tracker", return the row count from the referenced table rather than a percentage. The Source Catalog calls this function; it should return { count: number, display: "N rows" } for tracker fields instead of { pct: number }.
Commit after: feat(onboarding): task-type-backed steps + lazy-init assigned-tasks [HEL-681]
app/onboarding/page.tsx (full rewrite)1. Auth check → determine userType: 'student' | 'parent' | employee preview
2. screen = userType === 'parent' ? 'parent_onboarding' : 'student_onboarding'
3. Fetch: next_task_types WHERE screens @> [screen] AND is_active=true ORDER BY sort_order
4. Fetch: next_assigned_tasks WHERE user_type=userType AND user_id=userId
5. Lazy init: for any task type with no assignment row, INSERT (status='pending')
6. Filter: exclude status='completed' steps
7. Map to TaskTypeStep[] with embedded status and assigned_id
8. Pass to OnboardingFlow
All data fetching is server-side. Employee preview: ?studentId=N reads student's tasks.
components/onboarding/OnboardingFlow.tsx (update)steps: TaskTypeStep[] (was OnboardingStep[])TaskFormRenderer (remove all step_type branching — no more profile_complete, notifications, transcript_upload handlers)/api/onboarding/complete-step with { assignedId: step.assigned_id }/api/onboarding/skip-step with { assignedId: step.assigned_id } (only if step.is_skippable)!step.is_skippableCommit after: feat(settings): task-type-editor screens/category/toggles, file-upload, college-select [HEL-681]
components/settings/TaskTypeEditor.tsxScreens multi-select — replace any category-as-screen logic with checkboxes for:
student_onboarding | parent_onboarding | student_profile | student_detail | session | exit_survey
When session checked, show category field. Other screens: category is optional free-form.
Category → hashtag combobox — replace SelectField with a combobox:
GET /api/task-types/categories on mountis_required toggle — checkbox: "Required (blocks completion)"
is_skippable toggle — checkbox: "Skippable by user"
file-upload in FIELD_TYPES — add to the field type dropdown in the form builder
college-select in FIELD_TYPES — add to the field type dropdown
Screen filter chips — add a row of screen filter chips above the task type list (alongside existing dept/cat filters)
components/ui/TaskFormRenderer.tsxstudentId?: number prop — required for college-select fields
file-upload field type:
/api/upload/student-file with { file, studentId }path in form valuecollege-select field type:
GET /api/students/{studentId}/college-list-optionsonCanonicalChange if canonical_key setcomponents/settings/FieldRegistryManager.tsxAdd portal_tracker to source badge color map:
portal_tracker: { bg: "#e0f2fe", text: "#0369a1", label: "App Tracker" }
For tracker_* category fields: completeness column shows row count ("N rows") not percentage. Detect by checking if source_mappings[0].source === 'portal_tracker' and source_mappings[0].aggregate is absent.
components/dashboard/TaskMilestoneModal.tsxPass studentId (from student.id) down to TaskFormRenderer as prop.
components/settings/SettingsClient.tsxRemove OnboardingTab import and its tab entry. The enforcement toggles move to a General/Portal Settings tab — add them there using the next_onboarding_settings data already fetched.
app/(portal)/settings/page.tsxRemove next_onboarding_steps fetch (table is gone). Keep next_onboarding_settings fetch. Move its data to the General tab.
Commit after included in Phase 7 commit or separate: fix(session-modal): filter by screens not category [HEL-681]
components/dashboard/TaskMilestoneModal.tsxChange task type filter from tt.category === "session" to tt.screens?.includes("session"). Also update the fetch call to GET /api/task-types?screen=session.
Commit after: feat(tracker): staff-facing /students/[id]/tracker with 7 sub-tabs [HEL-681]
app/(portal)/students/[id]/tracker/page.tsx (new — server component)// 1. Auth + resolve studentId from params
// 2. Parallel fetch:
const [selectedColleges, essays, recommenders, 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 } },
college_priority_types: { select: { description: true } },
next_student_app_details: { take: 1 }
},
orderBy: { priority: 'asc' }
}),
prisma.next_student_essays.findMany({ where: { student_id: studentId } }),
prisma.next_student_recommenders.findMany({ where: { student_id: studentId } }),
prisma.next_student_activities.findMany({ where: { student_id: studentId }, orderBy: { position_number: 'asc' } }),
prisma.next_student_scholarships.findMany({ where: { student_id: studentId } }),
prisma.next_student_demonstrated_interest.findMany({ where: { student_id: studentId } })
]);
// 3. Also fetch student name + counselor name for mini-header
// 4. Render AppTrackerShell with all data as props
components/students/tracker/AppTrackerShell.tsx (new — client component)?tab= from URL (useSearchParams)/students/{id}) + SummaryBar + tab nav + active tab contentrouter.push(?tab=${tab}, { scroll: false })components/students/tracker/SummaryBar.tsx (new)Computed from props (no additional fetch):
selectedColleges.lengthfilter(sc => sc.applied).lengthfilter(sc => sc.next_student_app_details?.[0]?.school_decision === 'Accepted').lengthcollege_priority_types.descriptionessays.filter(e => e.status === 'submitted').length / essays.lengthrecommenders.filter(r => r.status === 'confirmed').length / recommenders.lengthDesign: horizontal stat strip matching .tmp/app-tracker-mockup.html summary bar.
components/students/tracker/CollegeTrackerGrid.tsx (new — client component)The main grid. Use .tmp/app-tracker-mockup.html as pixel reference.
Props: selectedColleges (with app_details embedded), studentId, counselorInitials
Column structure (match mockup exactly):
--brand-dark header bg = default (catalog/readonly)Cell types:
#f3f1fa)#fffdf0), click → inline <input> or <select>, blur/Enter → PATCH#fff0f3), editable by employees only<input type="date"> inlineFrozen columns: Rank (44px) + College Name (220px min) — use position: sticky; left: 0/44px; z-index: 1
Tier badge: Safety green / Likely blue / Target amber / Reach red — pill badge from mockup
Counselor avatar: 18px circle with initials, inline with tier badge
PATCH targets:
next_student_app_details fields → PATCH /api/student/tracker/app-detailsselected_colleges.applied, major1 → PATCH /api/student/selected-collegeselected_colleges.application_status (Outcome) → PATCH /api/student/selected-college"+ Add college to list" button at bottom → opens college name input (simple for now, search modal as Phase 2).
components/students/tracker/EssaysTrackerTab.tsx (new)Table columns: College (select from student's list) | Essay Name | Type | Word Limit | Current Words | Status | Writing Order | Target Date | First Draft | Final Draft | Notes
Status options: Not Started / Outline / Draft / Revising / Final / Submitted
All cells inline-editable. PATCH to /api/student/tracker/essays.
Sort by Writing Order default. "+ Add essay" → blank row at bottom.
components/students/tracker/RecsTrackerTab.tsx (new)Table columns: Type | Name | Relationship | Email | Requested | Resume Sent ✓ | Invite Sent ✓ | Follow-up ✓ | Status | Due Date | Notes
Pre-populate with 2 Teacher + 1 Counselor blank rows if no recommenders exist.
Checkbox columns (Resume Sent, Invite Sent, Follow-up): click → sets date to today or clears. PATCH to /api/student/tracker/recommenders.
components/students/tracker/ActivitiesTrackerTab.tsx (new)10-row table (Common App max):
Columns: # | Type | Position/Title | Organization | Description | Grades | Timing | Hours/Week | Weeks/Year
Character counters: Description (150 char), Organization (100 char), Position (50 char) — shown below cell on focus.
PATCH to /api/student/tracker/activities.
components/students/tracker/UCActivitiesTrackerTab.tsx (new)Same as Activities but:
format = 'uc' in /api/student/tracker/activitiescomponents/students/tracker/ScholarshipsTrackerTab.tsx (new)Columns: Scholarship Name | Organization | Amount | Due Date | Sent Date | Reply Date | Status | Notes
Status: Researching / Applied / Pending / Awarded / Declined
PATCH to /api/student/tracker/scholarships. "+ Add scholarship" row.
components/students/tracker/InterestTrackerTab.tsx (new)Columns: College | Interest Type | Completed ✓ | Date | Notes
Interest Type options: College Visit / Open House / Virtual Event / Social Follow / Email Registration / Interview / Other
PATCH to /api/student/tracker/interest. Group by college toggle.
components/students/StudentDetail.tsxAdd "App Tracker →" link button in the student header area (near existing action buttons):
<Link href={`/students/${student.id}/tracker`} className="btn-ghost text-sm">
App Tracker →
</Link>
components/students/detail-sections/CollegeListSection.tsxAdd "Open in App Tracker →" link at bottom of the collapsed college list.
Run after Phase 2 migration is confirmed. Do NOT commit the output.
node scripts/backfill-hubspot-canonical.js
Script at scripts/backfill-hubspot-canonical.js — see HEL-678 plan for full code. Reads next_hubspot_contacts for 63 matched students, maps via source_mappings property→column lookup, merges into next_student_profile_data (existing portal data wins, HubSpot fills gaps).
Expected output: ~62 inserted, 1 updated, ~0 skipped.
Verify:
SELECT COUNT(*) FROM next_student_profile_data; -- expect ~63
Commit after: chore(cleanup): remove onboarding tab + legacy step components [HEL-681]
Delete:
components/settings/OnboardingTab.tsxcomponents/onboarding/NotificationPrefsStep.tsxcomponents/onboarding/TranscriptUploadStep.tsxapp/api/admin/onboarding/steps/route.tsprisma/seed-onboarding.jsVerify no remaining imports of deleted files:
grep -r "OnboardingTab\|NotificationPrefsStep\|TranscriptUploadStep\|seed-onboarding" --include="*.ts" --include="*.tsx" .
Fix any remaining import errors before committing.
Commit after: test(e2e): app-tracker, onboarding, task-types, canonical [HEL-681]
e2e/task-type-editor.spec.tsscreens=["student_onboarding"], is_required=true → verify saves and loadsfile-upload field type available in field buildercollege-select field type available in field builderscreen=sessione2e/onboarding-flow.spec.ts/onboarding → sees task types in sort_ordernext_assigned_tasks (status=completed)/home?studentId=N shows student's pending stepsparent_onboarding steps onlye2e/app-tracker-staff.spec.ts/students/{id}/trackere2e/app-tracker-essays.spec.tse2e/app-tracker-canonical.spec.tsapp.personal_statement_status to canonical storegoals.stand_out_factorapp.fafsa_status written from FAFSA Workshope2e/source-catalog-tracker.spec.tsapp category → 35+ entriestracker_app → 60+ entriesportal_tracker source entries show "App Tracker" badgenpm run test:smoke
Fix any failures before pushing. Then:
npm run test:e2e
Manual spot checks:
/onboarding as a student → complete first step → verify next_assigned_tasks row status=completedgit push -u origin feat/HEL-681-portal-data-layer
| Phase | Commit message |
|---|---|
| 1 | feat(schema): add screens/assigned-tasks, drop onboarding tables [HEL-681] |
| 2 | feat(migration): portal data layer — all DDL + registry + wiring [HEL-681] |
| 3 | feat(seeds): field-registry + task-types canonical wiring [HEL-681] |
| 4 | feat(api): screens/assigned-tasks/upload/onboarding/tracker routes [HEL-681] |
| 5 | feat(lib): onboarding-types, onboarding-check, canonical-profile [HEL-681] |
| 6 | feat(onboarding): task-type-backed steps + lazy-init assigned-tasks [HEL-681] |
| 7+8 | feat(settings): task-type-editor screens/toggles + session-modal fix [HEL-681] |
| 9 | feat(tracker): staff-facing /students/[id]/tracker with 7 sub-tabs [HEL-681] |
| 11 | chore(cleanup): remove onboarding tab + legacy step components [HEL-681] |
| 12 | test(e2e): app-tracker, onboarding, task-types, canonical [HEL-681] |