← back to Norma
agents/price-agent/skills/crawl-scorecard.js
390 lines
/**
* crawl-scorecard.js — Discover skill
*
* Fetches expanded College Scorecard data for all CA schools.
* Paginates through the API, upserts into college_scorecard,
* and inserts tuition_history rows for each school.
*/
const fetch = require('node-fetch');
const { query } = require('../../shared/db');
const { logAction } = require('../../shared/audit-logger');
const {
SCORECARD_API_KEY,
SCORECARD_BASE_URL,
ALL_LATEST_FIELDS,
historicalFields,
mapScorecardToRow,
mapHistoricalToRow,
} = require('../lib/scorecard-fields');
const AGENT_NAME = 'price-agent';
const PER_PAGE = 100;
const RATE_LIMIT_MS = 1500;
function sleep(ms) {
return new Promise((resolve) => setTimeout(resolve, ms));
}
/**
* Determine the academic year from Scorecard data_year or fall back to current.
*/
function resolveAcademicYear(item) {
// Scorecard data_year is the fall term year (e.g. 2022 = 2022-2023)
const dataYear =
item['latest.academics.year'] ||
item['data_year'] ||
null;
if (dataYear && !isNaN(Number(dataYear))) return Number(dataYear);
// Fall back to current academic year: if month >= 7, current year, else previous
const now = new Date();
return now.getMonth() >= 6 ? now.getFullYear() : now.getFullYear() - 1;
}
module.exports = async function crawlScorecard(body = {}) {
const startTime = Date.now();
const state = (body && body.state) || 'CA';
const includeHistorical = !!(body && body.include_historical);
// 1. Log crawl start
const crawlLogRes = await query(
`INSERT INTO price_crawl_log (crawl_type, status, metadata)
VALUES ('scorecard', 'running', $1)
RETURNING id`,
[JSON.stringify({ state, include_historical: includeHistorical })]
);
const crawlLogId = crawlLogRes.rows[0].id;
let schoolsFound = 0;
let schoolsUpdated = 0;
const errors = [];
try {
// Build field list
let fields = ALL_LATEST_FIELDS.join(',');
const historicalYears = includeHistorical
? [2018, 2019, 2020, 2021, 2022, 2023, 2024]
: [];
if (historicalYears.length > 0) {
const histFields = historicalYears.flatMap((y) => historicalFields(y));
fields += ',' + histFields.join(',');
}
let page = 0;
let hasMore = true;
while (hasMore) {
const url =
`${SCORECARD_BASE_URL}?api_key=${SCORECARD_API_KEY}` +
`&school.state=${state}` +
`&per_page=${PER_PAGE}` +
`&page=${page}` +
`&fields=${fields}`;
console.log(`[crawl-scorecard] Fetching page ${page}...`);
let resp;
try {
resp = await fetch(url, {
headers: { 'User-Agent': 'NormaPriceAgent/1.0' },
timeout: 30000,
});
} catch (fetchErr) {
errors.push({ page, error: fetchErr.message });
console.error(`[crawl-scorecard] Fetch error page ${page}:`, fetchErr.message);
break;
}
if (!resp.ok) {
const errText = await resp.text().catch(() => 'unknown');
errors.push({ page, status: resp.status, error: errText.substring(0, 500) });
console.error(`[crawl-scorecard] API error page ${page}: ${resp.status}`);
break;
}
let data;
try {
data = await resp.json();
} catch (jsonErr) {
errors.push({ page, error: 'Invalid JSON response' });
break;
}
const results = data.results || [];
const metadata = data.metadata || {};
const totalResults = metadata.total || 0;
if (page === 0) {
schoolsFound = totalResults;
console.log(`[crawl-scorecard] Total schools in ${state}: ${totalResults}`);
}
if (results.length === 0) {
hasMore = false;
break;
}
// 3. Process each school
for (const item of results) {
try {
const row = mapScorecardToRow(item);
if (!row.unit_id) continue;
// 3a. Upsert into college_scorecard
await query(
`INSERT INTO college_scorecard (
unit_id, school_name, city, state, zip, school_url,
ownership, institution_type,
tuition_in_state, tuition_out_state,
room_board_on_campus, room_board_off_campus,
books_supplies, other_expenses_on, other_expenses_off,
coa_academic_year_on,
avg_net_price,
net_price_0_30k, net_price_30_48k, net_price_48_75k,
net_price_75_110k, net_price_110k_plus,
median_debt, pell_grant_rate, default_rate,
enrollment, completion_rate, retention_rate,
median_earnings_6yr, median_earnings_10yr,
raw_data, fetched_at, updated_at
) VALUES (
$1,$2,$3,$4,$5,$6,
$7,$8,
$9,$10,
$11,$12,
$13,$14,$15,
$16,
$17,
$18,$19,$20,
$21,$22,
$23,$24,$25,
$26,$27,$28,
$29,$30,
$31, NOW(), NOW()
)
ON CONFLICT (unit_id) DO UPDATE SET
school_name = EXCLUDED.school_name,
city = EXCLUDED.city,
zip = EXCLUDED.zip,
school_url = EXCLUDED.school_url,
ownership = EXCLUDED.ownership,
institution_type = EXCLUDED.institution_type,
tuition_in_state = EXCLUDED.tuition_in_state,
tuition_out_state = EXCLUDED.tuition_out_state,
room_board_on_campus = EXCLUDED.room_board_on_campus,
room_board_off_campus = EXCLUDED.room_board_off_campus,
books_supplies = EXCLUDED.books_supplies,
other_expenses_on = EXCLUDED.other_expenses_on,
other_expenses_off = EXCLUDED.other_expenses_off,
coa_academic_year_on = EXCLUDED.coa_academic_year_on,
avg_net_price = EXCLUDED.avg_net_price,
net_price_0_30k = EXCLUDED.net_price_0_30k,
net_price_30_48k = EXCLUDED.net_price_30_48k,
net_price_48_75k = EXCLUDED.net_price_48_75k,
net_price_75_110k = EXCLUDED.net_price_75_110k,
net_price_110k_plus = EXCLUDED.net_price_110k_plus,
median_debt = EXCLUDED.median_debt,
pell_grant_rate = EXCLUDED.pell_grant_rate,
default_rate = EXCLUDED.default_rate,
enrollment = EXCLUDED.enrollment,
completion_rate = EXCLUDED.completion_rate,
retention_rate = EXCLUDED.retention_rate,
median_earnings_6yr = EXCLUDED.median_earnings_6yr,
median_earnings_10yr = EXCLUDED.median_earnings_10yr,
raw_data = EXCLUDED.raw_data,
fetched_at = NOW(),
updated_at = NOW()`,
[
row.unit_id, row.school_name, row.city, row.state, row.zip, row.school_url,
row.ownership != null ? String(row.ownership) : null, row.institution_type,
row.tuition_in_state, row.tuition_out_state,
row.room_board_on_campus, row.room_board_off_campus,
row.books_supplies, row.other_expenses_on, row.other_expenses_off,
row.coa_on_campus,
row.net_price_overall,
row.net_price_0_30k, row.net_price_30_48k, row.net_price_48_75k,
row.net_price_75_110k, row.net_price_110k_plus,
row.median_debt, row.pell_grant_rate, row.default_rate,
row.enrollment, row.completion_rate, row.retention_rate,
row.median_earnings_6yr, row.median_earnings_10yr,
JSON.stringify(item),
]
);
// 3b. Insert latest year into tuition_history
const academicYear = resolveAcademicYear(item);
await query(
`INSERT INTO tuition_history (
unit_id, school_name, state, institution_type, academic_year, data_source,
tuition_in_state, tuition_out_state,
room_board_on_campus, room_board_off_campus,
books_supplies, other_expenses,
net_price_overall, net_price_0_30k, net_price_30_48k,
net_price_48_75k, net_price_75_110k, net_price_110k_plus,
median_debt, pell_grant_rate, raw_data
) VALUES (
$1,$2,$3,$4,$5,'scorecard',
$6,$7,
$8,$9,
$10,$11,
$12,$13,$14,
$15,$16,$17,
$18,$19,$20
)
ON CONFLICT (unit_id, academic_year, data_source) DO UPDATE SET
school_name = EXCLUDED.school_name,
institution_type = EXCLUDED.institution_type,
tuition_in_state = COALESCE(EXCLUDED.tuition_in_state, tuition_history.tuition_in_state),
tuition_out_state = COALESCE(EXCLUDED.tuition_out_state, tuition_history.tuition_out_state),
room_board_on_campus = COALESCE(EXCLUDED.room_board_on_campus, tuition_history.room_board_on_campus),
room_board_off_campus = COALESCE(EXCLUDED.room_board_off_campus, tuition_history.room_board_off_campus),
books_supplies = COALESCE(EXCLUDED.books_supplies, tuition_history.books_supplies),
other_expenses = COALESCE(EXCLUDED.other_expenses, tuition_history.other_expenses),
net_price_overall = COALESCE(EXCLUDED.net_price_overall, tuition_history.net_price_overall),
net_price_0_30k = COALESCE(EXCLUDED.net_price_0_30k, tuition_history.net_price_0_30k),
net_price_30_48k = COALESCE(EXCLUDED.net_price_30_48k, tuition_history.net_price_30_48k),
net_price_48_75k = COALESCE(EXCLUDED.net_price_48_75k, tuition_history.net_price_48_75k),
net_price_75_110k = COALESCE(EXCLUDED.net_price_75_110k, tuition_history.net_price_75_110k),
net_price_110k_plus = COALESCE(EXCLUDED.net_price_110k_plus, tuition_history.net_price_110k_plus),
median_debt = COALESCE(EXCLUDED.median_debt, tuition_history.median_debt),
pell_grant_rate = COALESCE(EXCLUDED.pell_grant_rate, tuition_history.pell_grant_rate),
raw_data = EXCLUDED.raw_data`,
[
row.unit_id, row.school_name, row.state || state, row.institution_type, academicYear,
row.tuition_in_state, row.tuition_out_state,
row.room_board_on_campus, row.room_board_off_campus,
row.books_supplies, row.other_expenses_on,
row.net_price_overall, row.net_price_0_30k, row.net_price_30_48k,
row.net_price_48_75k, row.net_price_75_110k, row.net_price_110k_plus,
row.median_debt, row.pell_grant_rate, JSON.stringify(item),
]
);
// 3c. If include_historical, insert historical years too
if (includeHistorical) {
for (const year of historicalYears) {
const histRow = mapHistoricalToRow(item, year);
// Only insert if there's at least one non-null value
const hasData = Object.values(histRow).some((v) => v != null);
if (!hasData) continue;
await query(
`INSERT INTO tuition_history (
unit_id, school_name, state, institution_type, academic_year, data_source,
tuition_in_state, tuition_out_state,
room_board_on_campus, room_board_off_campus,
books_supplies, other_expenses,
net_price_overall, net_price_0_30k, net_price_30_48k,
net_price_48_75k, net_price_75_110k, net_price_110k_plus,
median_debt, pell_grant_rate
) VALUES (
$1,$2,$3,$4,$5,'scorecard',
$6,$7,$8,$9,$10,$11,
$12,$13,$14,$15,$16,$17,
$18,$19
)
ON CONFLICT (unit_id, academic_year, data_source) DO UPDATE SET
tuition_in_state = COALESCE(EXCLUDED.tuition_in_state, tuition_history.tuition_in_state),
tuition_out_state = COALESCE(EXCLUDED.tuition_out_state, tuition_history.tuition_out_state),
room_board_on_campus = COALESCE(EXCLUDED.room_board_on_campus, tuition_history.room_board_on_campus),
room_board_off_campus = COALESCE(EXCLUDED.room_board_off_campus, tuition_history.room_board_off_campus),
books_supplies = COALESCE(EXCLUDED.books_supplies, tuition_history.books_supplies),
other_expenses = COALESCE(EXCLUDED.other_expenses, tuition_history.other_expenses),
net_price_overall = COALESCE(EXCLUDED.net_price_overall, tuition_history.net_price_overall),
median_debt = COALESCE(EXCLUDED.median_debt, tuition_history.median_debt),
pell_grant_rate = COALESCE(EXCLUDED.pell_grant_rate, tuition_history.pell_grant_rate)`,
[
row.unit_id, row.school_name, row.state || state, row.institution_type, year,
histRow.tuition_in_state, histRow.tuition_out_state,
histRow.room_board_on_campus, histRow.room_board_off_campus,
histRow.books_supplies, histRow.other_expenses,
histRow.net_price_overall, histRow.net_price_0_30k, histRow.net_price_30_48k,
histRow.net_price_48_75k, histRow.net_price_75_110k, histRow.net_price_110k_plus,
histRow.median_debt, histRow.pell_grant_rate,
]
);
}
}
schoolsUpdated++;
} catch (schoolErr) {
const uid = item.id || item['id'] || 'unknown';
errors.push({ unit_id: uid, error: schoolErr.message });
console.error(`[crawl-scorecard] Error processing school ${uid}:`, schoolErr.message);
}
}
// Check if there are more pages
const totalPages = Math.ceil(totalResults / PER_PAGE);
page++;
hasMore = page < totalPages;
if (hasMore) {
await sleep(RATE_LIMIT_MS);
}
}
// 5. Update crawl log with results
const durationMs = Date.now() - startTime;
await query(
`UPDATE price_crawl_log
SET status = 'completed',
schools_found = $1,
schools_updated = $2,
duration_ms = $3,
metadata = metadata || $4,
completed_at = NOW()
WHERE id = $5`,
[
schoolsFound,
schoolsUpdated,
durationMs,
JSON.stringify({ errors: errors.slice(0, 50), pages_fetched: page }),
crawlLogId,
]
);
// 6. Audit log
await logAction({
agent: AGENT_NAME,
actionType: 'crawl',
platform: 'scorecard_api',
content: `Crawled ${schoolsUpdated}/${schoolsFound} ${state} schools from College Scorecard`,
responseData: { schools_found: schoolsFound, schools_updated: schoolsUpdated, errors: errors.length },
status: errors.length > 0 ? 'partial' : 'success',
errorMessage: errors.length > 0 ? `${errors.length} schools had errors` : null,
});
console.log(`[crawl-scorecard] Complete: ${schoolsUpdated}/${schoolsFound} schools updated in ${durationMs}ms`);
return {
schools_found: schoolsFound,
schools_updated: schoolsUpdated,
crawl_log_id: crawlLogId,
errors_count: errors.length,
duration_ms: durationMs,
};
} catch (err) {
// Fatal error — update crawl log
const durationMs = Date.now() - startTime;
await query(
`UPDATE price_crawl_log
SET status = 'error', error_message = $1, duration_ms = $2, completed_at = NOW()
WHERE id = $3`,
[err.message, durationMs, crawlLogId]
).catch(() => {});
await logAction({
agent: AGENT_NAME,
actionType: 'crawl',
platform: 'scorecard_api',
content: 'Scorecard crawl failed',
status: 'error',
errorMessage: err.message,
}).catch(() => {});
console.error(`[crawl-scorecard] Fatal error:`, err);
throw err;
}
};