← back to Norma
agents/price-agent/lib/scorecard-fields.js
252 lines
/**
* Expanded College Scorecard API field list for comprehensive cost tracking.
*
* API docs: https://collegescorecard.ed.gov/data/documentation/
* Fields prefixed with "latest." return most recent year.
* Historical fields use "{year}." prefix (e.g., "2020.cost.tuition.in_state").
*/
// Registered key expired — using DEMO_KEY (1000 req/hr limit, sufficient for CA-only crawls)
const SCORECARD_API_KEY = process.env.SCORECARD_API_KEY || 'DEMO_KEY';
const SCORECARD_BASE_URL = 'https://api.data.gov/ed/collegescorecard/v1/schools';
// Core identification fields
const ID_FIELDS = [
'id',
'ope8_id',
'school.name',
'school.city',
'school.state',
'school.zip',
'school.school_url',
'school.ownership',
'school.locale',
'school.degrees_awarded.predominant',
'school.carnegie_basic',
];
// Tuition & fees
const COST_FIELDS = [
'latest.cost.tuition.in_state',
'latest.cost.tuition.out_of_state',
'latest.cost.tuition.program_year',
'latest.cost.roomboard.oncampus',
'latest.cost.roomboard.offcampus',
'latest.cost.booksupply',
'latest.cost.otherexpense.oncampus',
'latest.cost.otherexpense.offcampus',
'latest.cost.attendance.academic_year',
'latest.cost.attendance.program_year',
];
// Average net price (overall + by income bracket)
const NET_PRICE_FIELDS = [
'latest.cost.avg_net_price.overall',
'latest.cost.net_price.consumer.by_income_level.0-30000',
'latest.cost.net_price.consumer.by_income_level.30001-48000',
'latest.cost.net_price.consumer.by_income_level.48001-75000',
'latest.cost.net_price.consumer.by_income_level.75001-110000',
'latest.cost.net_price.consumer.by_income_level.110001-plus',
// Public institution variants
'latest.cost.avg_net_price.public',
'latest.cost.avg_net_price.private',
];
// Student aid & debt
const AID_FIELDS = [
'latest.aid.pell_grant_rate',
'latest.aid.federal_loan_rate',
'latest.aid.median_debt_suppressed.overall',
'latest.aid.median_debt.completers.overall',
'latest.aid.median_debt.noncompleters',
'latest.aid.median_debt.completers.monthly_payments',
];
// Outcomes
const OUTCOME_FIELDS = [
'latest.completion.rate_suppressed.overall',
'latest.student.retention_rate.overall',
'latest.earnings.6_yrs_after_entry.median',
'latest.earnings.10_yrs_after_entry.median',
'latest.repayment.3_yr_repayment.overall',
];
// Default rates
const DEFAULT_RATE_FIELDS = [
'latest.repayment.repayment_cohort.3_year_declining_balance',
'latest.aid.loan_principal',
];
// Enrollment
const ENROLLMENT_FIELDS = [
'latest.student.enrollment.all',
'latest.student.enrollment.undergrad_12_month',
'latest.student.size',
'latest.student.demographics.race_ethnicity.white',
'latest.student.demographics.race_ethnicity.black',
'latest.student.demographics.race_ethnicity.hispanic',
'latest.student.demographics.race_ethnicity.asian',
'latest.student.part_time_share',
];
// All latest fields combined
const ALL_LATEST_FIELDS = [
...ID_FIELDS,
...COST_FIELDS,
...NET_PRICE_FIELDS,
...AID_FIELDS,
...OUTCOME_FIELDS,
...DEFAULT_RATE_FIELDS,
...ENROLLMENT_FIELDS,
];
/**
* Build historical field list for a specific year.
* Replaces "latest." prefix with "{year}." prefix.
*/
function historicalFields(year) {
const costFields = [
`${year}.cost.tuition.in_state`,
`${year}.cost.tuition.out_of_state`,
`${year}.cost.roomboard.oncampus`,
`${year}.cost.roomboard.offcampus`,
`${year}.cost.booksupply`,
`${year}.cost.otherexpense.oncampus`,
`${year}.cost.otherexpense.offcampus`,
`${year}.cost.avg_net_price.overall`,
`${year}.cost.net_price.consumer.by_income_level.0-30000`,
`${year}.cost.net_price.consumer.by_income_level.30001-48000`,
`${year}.cost.net_price.consumer.by_income_level.48001-75000`,
`${year}.cost.net_price.consumer.by_income_level.75001-110000`,
`${year}.cost.net_price.consumer.by_income_level.110001-plus`,
`${year}.aid.median_debt.completers.overall`,
`${year}.aid.pell_grant_rate`,
`${year}.completion.rate_suppressed.overall`,
`${year}.student.size`,
];
return costFields;
}
/**
* Map a raw Scorecard API result to our DB columns.
*/
function mapScorecardToRow(item) {
const g = (path) => {
if (path in item) return item[path];
const parts = path.split('.');
let val = item;
for (const p of parts) {
if (val && typeof val === 'object') val = val[p];
else return null;
}
return val;
};
const num = (v) => {
if (v == null) return null;
const n = Number(v);
return isNaN(n) ? null : n;
};
return {
unit_id: num(g('id')),
school_name: g('school.name'),
city: g('school.city'),
state: g('school.state'),
zip: g('school.zip'),
school_url: g('school.school_url'),
ownership: num(g('school.ownership')),
institution_type: classifyInstitution(num(g('school.ownership')), num(g('school.degrees_awarded.predominant'))),
// Tuition
tuition_in_state: num(g('latest.cost.tuition.in_state')),
tuition_out_state: num(g('latest.cost.tuition.out_of_state')),
room_board_on_campus: num(g('latest.cost.roomboard.oncampus')),
room_board_off_campus: num(g('latest.cost.roomboard.offcampus')),
books_supplies: num(g('latest.cost.booksupply')),
other_expenses_on: num(g('latest.cost.otherexpense.oncampus')),
other_expenses_off: num(g('latest.cost.otherexpense.offcampus')),
coa_on_campus: num(g('latest.cost.attendance.academic_year')),
// Net price
net_price_overall: num(g('latest.cost.avg_net_price.overall')),
net_price_0_30k: num(g('latest.cost.net_price.consumer.by_income_level.0-30000')),
net_price_30_48k: num(g('latest.cost.net_price.consumer.by_income_level.30001-48000')),
net_price_48_75k: num(g('latest.cost.net_price.consumer.by_income_level.48001-75000')),
net_price_75_110k: num(g('latest.cost.net_price.consumer.by_income_level.75001-110000')),
net_price_110k_plus: num(g('latest.cost.net_price.consumer.by_income_level.110001-plus')),
// Aid/debt
median_debt: num(g('latest.aid.median_debt.completers.overall')),
pell_grant_rate: num(g('latest.aid.pell_grant_rate')),
default_rate: num(g('latest.repayment.repayment_cohort.3_year_declining_balance')),
// Enrollment
enrollment: num(g('latest.student.size')),
// Outcomes
completion_rate: num(g('latest.completion.rate_suppressed.overall')),
retention_rate: num(g('latest.student.retention_rate.overall')),
median_earnings_6yr: num(g('latest.earnings.6_yrs_after_entry.median')),
median_earnings_10yr: num(g('latest.earnings.10_yrs_after_entry.median')),
};
}
/**
* Map a historical year record to tuition_history row.
*/
function mapHistoricalToRow(item, year) {
const g = (path) => {
if (path in item) return item[path];
const parts = path.split('.');
let val = item;
for (const p of parts) {
if (val && typeof val === 'object') val = val[p];
else return null;
}
return val;
};
const num = (v) => {
if (v == null) return null;
const n = Number(v);
return isNaN(n) ? null : n;
};
return {
tuition_in_state: num(g(`${year}.cost.tuition.in_state`)),
tuition_out_state: num(g(`${year}.cost.tuition.out_of_state`)),
room_board_on_campus: num(g(`${year}.cost.roomboard.oncampus`)),
room_board_off_campus: num(g(`${year}.cost.roomboard.offcampus`)),
books_supplies: num(g(`${year}.cost.booksupply`)),
other_expenses: num(g(`${year}.cost.otherexpense.oncampus`)),
net_price_overall: num(g(`${year}.cost.avg_net_price.overall`)),
net_price_0_30k: num(g(`${year}.cost.net_price.consumer.by_income_level.0-30000`)),
net_price_30_48k: num(g(`${year}.cost.net_price.consumer.by_income_level.30001-48000`)),
net_price_48_75k: num(g(`${year}.cost.net_price.consumer.by_income_level.48001-75000`)),
net_price_75_110k: num(g(`${year}.cost.net_price.consumer.by_income_level.75001-110000`)),
net_price_110k_plus: num(g(`${year}.cost.net_price.consumer.by_income_level.110001-plus`)),
median_debt: num(g(`${year}.aid.median_debt.completers.overall`)),
pell_grant_rate: num(g(`${year}.aid.pell_grant_rate`)),
};
}
function classifyInstitution(ownership, degreesPredominant) {
if (ownership === 1) {
if (degreesPredominant <= 2) return 'community_college';
return '4year_public';
}
if (ownership === 2) return '4year_private';
if (ownership === 3) return 'for_profit';
return 'unknown';
}
module.exports = {
SCORECARD_API_KEY,
SCORECARD_BASE_URL,
ALL_LATEST_FIELDS,
historicalFields,
mapScorecardToRow,
mapHistoricalToRow,
classifyInstitution,
};