← back to Norma
agents/price-agent/lib/change-detector.js
301 lines
/**
* Change Detection Engine
*
* Compares consecutive tuition/cost snapshots for a school and generates
* alerts when year-over-year changes exceed defined thresholds.
*
* Alerts are written to the `cost_alerts` table in PostgreSQL.
*
* Usage:
* const { detectChangesForSchool } = require('./change-detector');
* const alerts = await detectChangesForSchool(110635); // UC Berkeley
*/
const { query } = require('../../shared/db');
// ---------------------------------------------------------------------------
// Threshold configuration
// ---------------------------------------------------------------------------
/**
* Percentage-change thresholds per metric.
* Absolute value of change_pct is compared against these.
* - info: abs(change) > 0 but < warning
* - warning: abs(change) >= warning but < critical
* - critical: abs(change) >= critical
*/
const THRESHOLDS = {
tuition_in_state: { warning: 3, critical: 8 },
tuition_out_state: { warning: 3, critical: 8 },
room_board_on_campus: { warning: 5, critical: 12 },
net_price_overall: { warning: 5, critical: 10 },
books_supplies: { warning: 8, critical: 20 },
median_debt: { warning: 3, critical: 8 },
coa_on_campus: { warning: 3, critical: 8 },
};
// ---------------------------------------------------------------------------
// Alert severity classifier
// ---------------------------------------------------------------------------
/**
* Determine severity level based on the absolute percentage change
* and the metric's threshold config.
*
* @param {number} absChangePct - Absolute value of percentage change
* @param {{ warning: number, critical: number }} threshold
* @returns {'info'|'warning'|'critical'}
*/
function classifySeverity(absChangePct, threshold) {
if (absChangePct >= threshold.critical) return 'critical';
if (absChangePct >= threshold.warning) return 'warning';
return 'info';
}
/**
* Determine the alert_type string based on the direction of change.
*
* @param {string} metric - The metric name (e.g. 'tuition_in_state')
* @param {number} changePct - Signed percentage change
* @returns {string}
*/
function alertType(metric, changePct) {
const direction = changePct > 0 ? 'increase' : 'decrease';
return `${metric}_${direction}`;
}
// ---------------------------------------------------------------------------
// Core detection
// ---------------------------------------------------------------------------
/**
* Compare two data snapshots and generate alerts for metrics that exceed
* their configured thresholds.
*
* @param {number} unitId - IPEDS unit ID of the school
* @param {string} schoolName - Human-readable school name
* @param {object} newData - Most recent cost snapshot (from tuition_history)
* @param {object} prevData - Previous cost snapshot
* @returns {Promise<object[]>} Array of alert objects that were inserted
*/
async function detectChanges(unitId, schoolName, newData, prevData) {
if (!newData || !prevData) {
console.warn(
`[change-detector] Missing data for unit_id=${unitId} — skipping comparison.`
);
return [];
}
const alerts = [];
for (const [metric, threshold] of Object.entries(THRESHOLDS)) {
const newVal = newData[metric];
const prevVal = prevData[metric];
// Skip if either value is null/undefined/zero (can't compute meaningful %)
if (newVal == null || prevVal == null || prevVal === 0) {
continue;
}
const changePct = ((newVal - prevVal) / prevVal) * 100;
const absChangePct = Math.abs(changePct);
// Only create alerts when the change exceeds the info threshold (> 0%)
if (absChangePct < 0.01) {
continue;
}
const severity = classifySeverity(absChangePct, threshold);
const type = alertType(metric, changePct);
const alert = {
unit_id: unitId,
school_name: schoolName,
alert_type: type,
metric,
previous_value: prevVal,
new_value: newVal,
change_pct: Math.round(changePct * 100) / 100, // 2 decimal places
academic_year: newData.academic_year || null,
severity,
};
try {
const result = await query(
`INSERT INTO cost_alerts
(unit_id, school_name, alert_type, metric,
previous_value, new_value, change_pct,
academic_year, severity)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
RETURNING id, created_at`,
[
alert.unit_id,
alert.school_name,
alert.alert_type,
alert.metric,
alert.previous_value,
alert.new_value,
alert.change_pct,
alert.academic_year,
alert.severity,
]
);
if (result.rows && result.rows.length > 0) {
alert.id = result.rows[0].id;
alert.created_at = result.rows[0].created_at;
}
} catch (err) {
console.error(
`[change-detector] Failed to insert alert for ${schoolName} / ${metric}:`,
err.message
);
// Attach the error but don't throw — continue processing other metrics
alert.insertError = err.message;
}
alerts.push(alert);
// Log significant alerts to stdout
if (severity === 'warning' || severity === 'critical') {
console.log(
`[change-detector] ${severity.toUpperCase()}: ${schoolName} — ` +
`${metric} changed ${changePct > 0 ? '+' : ''}${alert.change_pct}% ` +
`($${prevVal.toLocaleString()} -> $${newVal.toLocaleString()})`
);
}
}
return alerts;
}
/**
* Auto-detect changes for a school by querying the two most recent
* tuition_history snapshots and running detectChanges().
*
* @param {number} unitId - IPEDS unit ID
* @returns {Promise<object[]>} Array of alert objects
*/
async function detectChangesForSchool(unitId) {
let rows;
try {
const result = await query(
`SELECT *
FROM tuition_history
WHERE unit_id = $1
ORDER BY academic_year DESC
LIMIT 2`,
[unitId]
);
rows = result.rows;
} catch (err) {
console.error(
`[change-detector] Failed to query tuition_history for unit_id=${unitId}:`,
err.message
);
return [];
}
if (!rows || rows.length < 2) {
console.log(
`[change-detector] Not enough history for unit_id=${unitId} ` +
`(found ${rows ? rows.length : 0} records, need 2). Skipping.`
);
return [];
}
// rows[0] = most recent, rows[1] = previous
const newData = rows[0];
const prevData = rows[1];
const schoolName = newData.school_name || `Unit ${unitId}`;
return detectChanges(unitId, schoolName, newData, prevData);
}
/**
* Run change detection across all schools that have tuition_history records.
* Returns a summary of all generated alerts grouped by severity.
*
* @returns {Promise<{ total: number, critical: object[], warning: object[], info: object[] }>}
*/
async function detectChangesForAllSchools() {
let unitIds;
try {
const result = await query(
`SELECT DISTINCT unit_id
FROM tuition_history
ORDER BY unit_id`
);
unitIds = result.rows.map((r) => r.unit_id);
} catch (err) {
console.error(
'[change-detector] Failed to query distinct unit_ids:',
err.message
);
return { total: 0, critical: [], warning: [], info: [] };
}
console.log(
`[change-detector] Running change detection for ${unitIds.length} schools...`
);
const allAlerts = [];
for (const unitId of unitIds) {
const alerts = await detectChangesForSchool(unitId);
allAlerts.push(...alerts);
}
const summary = {
total: allAlerts.length,
critical: allAlerts.filter((a) => a.severity === 'critical'),
warning: allAlerts.filter((a) => a.severity === 'warning'),
info: allAlerts.filter((a) => a.severity === 'info'),
};
console.log(
`[change-detector] Complete. ` +
`${summary.critical.length} critical, ` +
`${summary.warning.length} warning, ` +
`${summary.info.length} info alerts generated.`
);
return summary;
}
/**
* Check if an alert already exists for this school/metric/academic_year
* to avoid duplicate alerts on repeated runs.
*
* @param {number} unitId
* @param {string} metric
* @param {string} academicYear
* @returns {Promise<boolean>}
*/
async function alertExists(unitId, metric, academicYear) {
try {
const result = await query(
`SELECT 1 FROM cost_alerts
WHERE unit_id = $1 AND metric = $2 AND academic_year = $3
LIMIT 1`,
[unitId, metric, academicYear]
);
return result.rows.length > 0;
} catch (err) {
console.error(
`[change-detector] alertExists check failed for unit_id=${unitId}:`,
err.message
);
return false;
}
}
module.exports = {
THRESHOLDS,
classifySeverity,
detectChanges,
detectChangesForSchool,
detectChangesForAllSchools,
alertExists,
};