← back to Norma

agents/price-agent/skills/crawl-federal.js

378 lines

/**
 * crawl-federal.js — Custom route skill
 *
 * Fetches federal student loan interest rates and limits.
 * Since studentaid.gov is React-rendered and hard to scrape,
 * this uses known current rates with Gemini AI as a fallback
 * for extracting data from fetched HTML.
 */

const fetch = require('node-fetch');
const { query } = require('../../shared/db');
const { logAction } = require('../../shared/audit-logger');
const { parseFederalRatesPage } = require('../lib/html-parser');

const AGENT_NAME = 'price-agent';
const GEMINI_API_KEY = '${GOOGLE_API_KEY}';
const GEMINI_URL = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=${GEMINI_API_KEY}`;

const STUDENTAID_URL = 'https://studentaid.gov/understand-aid/types/loans/interest-rates';

/**
 * Known federal student loan rates for 2025-2026 academic year.
 * These are the authoritative fallback values.
 */
const KNOWN_RATES_2025 = {
  academic_year: 2025,
  effective_date: '2025-07-01',
  expiration_date: '2026-06-30',
  rates: [
    {
      rate_type: 'direct_subsidized_undergrad',
      interest_rate: 6.53,
      origination_fee: 1.057,
      annual_limit_dep_1: 5500,
      annual_limit_dep_2: 6500,
      annual_limit_dep_3: 7500,
      annual_limit_indep_1: 9500,
      annual_limit_indep_2: 10500,
      annual_limit_indep_3: 12500,
      aggregate_limit_dep: 31000,
      aggregate_limit_indep: 57500,
      sub_limit_dep_1: 3500,
      sub_limit_dep_2: 4500,
      sub_limit_dep_3: 5500,
    },
    {
      rate_type: 'direct_unsubsidized_undergrad',
      interest_rate: 6.53,
      origination_fee: 1.057,
      annual_limit_dep_1: 5500,
      annual_limit_dep_2: 6500,
      annual_limit_dep_3: 7500,
      annual_limit_indep_1: 9500,
      annual_limit_indep_2: 10500,
      annual_limit_indep_3: 12500,
      aggregate_limit_dep: 31000,
      aggregate_limit_indep: 57500,
    },
    {
      rate_type: 'direct_unsubsidized_graduate',
      interest_rate: 8.08,
      origination_fee: 1.057,
      grad_annual_limit: 20500,
      grad_aggregate_limit: 138500,
    },
    {
      rate_type: 'direct_plus',
      interest_rate: 9.08,
      origination_fee: 4.228,
    },
  ],
};

/**
 * Attempt to extract rates from HTML using Gemini AI.
 */
async function extractRatesWithGemini(html) {
  const truncated = html.substring(0, 30000);

  const prompt = `Extract federal student loan interest rates from this HTML page content.
Return ONLY a JSON object (no markdown, no explanation) with this structure:
{
  "academic_year": 2025,
  "rates": [
    {
      "rate_type": "direct_subsidized_undergrad",
      "interest_rate": 6.53,
      "origination_fee": 1.057
    },
    {
      "rate_type": "direct_unsubsidized_undergrad",
      "interest_rate": 6.53,
      "origination_fee": 1.057
    },
    {
      "rate_type": "direct_unsubsidized_graduate",
      "interest_rate": 8.08,
      "origination_fee": 1.057
    },
    {
      "rate_type": "direct_plus",
      "interest_rate": 9.08,
      "origination_fee": 4.228
    }
  ]
}

If you cannot find rate data in the HTML, return: {"error": "no_rates_found"}

HTML content:
${truncated}`;

  try {
    const resp = await fetch(GEMINI_URL, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        contents: [{ parts: [{ text: prompt }] }],
        generationConfig: {
          temperature: 0.1,
          maxOutputTokens: 2000,
        },
      }),
      timeout: 30000,
    });

    if (!resp.ok) {
      console.warn(`[crawl-federal] Gemini API returned ${resp.status}`);
      return null;
    }

    const data = await resp.json();
    const text =
      data.candidates &&
      data.candidates[0] &&
      data.candidates[0].content &&
      data.candidates[0].content.parts &&
      data.candidates[0].content.parts[0] &&
      data.candidates[0].content.parts[0].text;

    if (!text) return null;

    // Extract JSON from response (may be wrapped in markdown code block)
    const jsonMatch = text.match(/\{[\s\S]*\}/);
    if (!jsonMatch) return null;

    const parsed = JSON.parse(jsonMatch[0]);
    if (parsed.error) return null;

    return parsed;
  } catch (err) {
    console.warn('[crawl-federal] Gemini extraction failed:', err.message);
    return null;
  }
}

module.exports = async function crawlFederal(body = {}) {
  const startTime = Date.now();
  const targetYear = (body && body.academic_year) || KNOWN_RATES_2025.academic_year;

  // Log crawl start
  const crawlLogRes = await query(
    `INSERT INTO price_crawl_log (crawl_type, status, metadata)
     VALUES ('federal_rates', 'running', $1)
     RETURNING id`,
    [JSON.stringify({ academic_year: targetYear })]
  );
  const crawlLogId = crawlLogRes.rows[0].id;

  let ratesUpdated = 0;
  let dataSource = 'known_rates';
  const errors = [];

  try {
    let ratesData = null;

    // 1. Try fetching from studentaid.gov
    console.log('[crawl-federal] Fetching studentaid.gov...');
    try {
      const resp = await fetch(STUDENTAID_URL, {
        headers: {
          'User-Agent': 'Mozilla/5.0 (compatible; NormaPriceAgent/1.0; education-research)',
          'Accept': 'text/html,application/xhtml+xml',
        },
        timeout: 15000,
        redirect: 'follow',
      });

      if (resp.ok) {
        const html = await resp.text();

        // 2. Try Cheerio-based parser first
        try {
          const parsed = parseFederalRatesPage(html);
          if (parsed && parsed.rates && parsed.rates.length > 0) {
            ratesData = parsed;
            dataSource = 'studentaid_parse';
            console.log('[crawl-federal] Parsed rates from HTML successfully');
          }
        } catch (parseErr) {
          console.warn('[crawl-federal] HTML parser failed:', parseErr.message);
        }

        // 3. If parser didn't work (likely React-rendered), try Gemini
        if (!ratesData) {
          console.log('[crawl-federal] HTML parse incomplete, trying Gemini...');
          const geminiResult = await extractRatesWithGemini(html);
          if (geminiResult && geminiResult.rates && geminiResult.rates.length > 0) {
            ratesData = geminiResult;
            dataSource = 'studentaid_gemini';
            console.log('[crawl-federal] Gemini extracted rates successfully');
          }
        }
      } else {
        console.warn(`[crawl-federal] studentaid.gov returned ${resp.status}`);
      }
    } catch (fetchErr) {
      console.warn('[crawl-federal] Failed to fetch studentaid.gov:', fetchErr.message);
    }

    // 4. Fall back to known rates if nothing else worked
    if (!ratesData) {
      console.log('[crawl-federal] Using known current rates as fallback');
      ratesData = KNOWN_RATES_2025;
      dataSource = 'known_rates';
    }

    const academicYear = ratesData.academic_year || targetYear;
    const effectiveDate = ratesData.effective_date || KNOWN_RATES_2025.effective_date;
    const expirationDate = ratesData.expiration_date || KNOWN_RATES_2025.expiration_date;

    // 5. Upsert each rate type into federal_rates
    // Merge known limits into any scraped/AI-extracted rates
    const knownByType = {};
    for (const r of KNOWN_RATES_2025.rates) {
      knownByType[r.rate_type] = r;
    }

    for (const rate of ratesData.rates) {
      try {
        const rateType = rate.rate_type;
        // Merge: prefer scraped values, fall back to known for limits
        const known = knownByType[rateType] || {};

        const interestRate = rate.interest_rate || known.interest_rate;
        const originationFee = rate.origination_fee || known.origination_fee || null;

        if (!interestRate) {
          errors.push({ rate_type: rateType, error: 'No interest rate value' });
          continue;
        }

        await query(
          `INSERT INTO federal_rates (
            academic_year, rate_type, interest_rate, origination_fee,
            annual_limit_dep_1, annual_limit_dep_2, annual_limit_dep_3,
            annual_limit_indep_1, annual_limit_indep_2, annual_limit_indep_3,
            aggregate_limit_dep, aggregate_limit_indep,
            grad_annual_limit, grad_aggregate_limit,
            effective_date, expiration_date,
            data_source, raw_data
          ) VALUES (
            $1,$2,$3,$4,
            $5,$6,$7,
            $8,$9,$10,
            $11,$12,
            $13,$14,
            $15,$16,
            $17,$18
          )
          ON CONFLICT (academic_year, rate_type) DO UPDATE SET
            interest_rate = EXCLUDED.interest_rate,
            origination_fee = COALESCE(EXCLUDED.origination_fee, federal_rates.origination_fee),
            annual_limit_dep_1 = COALESCE(EXCLUDED.annual_limit_dep_1, federal_rates.annual_limit_dep_1),
            annual_limit_dep_2 = COALESCE(EXCLUDED.annual_limit_dep_2, federal_rates.annual_limit_dep_2),
            annual_limit_dep_3 = COALESCE(EXCLUDED.annual_limit_dep_3, federal_rates.annual_limit_dep_3),
            annual_limit_indep_1 = COALESCE(EXCLUDED.annual_limit_indep_1, federal_rates.annual_limit_indep_1),
            annual_limit_indep_2 = COALESCE(EXCLUDED.annual_limit_indep_2, federal_rates.annual_limit_indep_2),
            annual_limit_indep_3 = COALESCE(EXCLUDED.annual_limit_indep_3, federal_rates.annual_limit_indep_3),
            aggregate_limit_dep = COALESCE(EXCLUDED.aggregate_limit_dep, federal_rates.aggregate_limit_dep),
            aggregate_limit_indep = COALESCE(EXCLUDED.aggregate_limit_indep, federal_rates.aggregate_limit_indep),
            grad_annual_limit = COALESCE(EXCLUDED.grad_annual_limit, federal_rates.grad_annual_limit),
            grad_aggregate_limit = COALESCE(EXCLUDED.grad_aggregate_limit, federal_rates.grad_aggregate_limit),
            effective_date = COALESCE(EXCLUDED.effective_date, federal_rates.effective_date),
            expiration_date = COALESCE(EXCLUDED.expiration_date, federal_rates.expiration_date),
            data_source = EXCLUDED.data_source,
            raw_data = EXCLUDED.raw_data`,
          [
            academicYear,
            rateType,
            interestRate,
            originationFee,
            rate.annual_limit_dep_1 || known.annual_limit_dep_1 || null,
            rate.annual_limit_dep_2 || known.annual_limit_dep_2 || null,
            rate.annual_limit_dep_3 || known.annual_limit_dep_3 || null,
            rate.annual_limit_indep_1 || known.annual_limit_indep_1 || null,
            rate.annual_limit_indep_2 || known.annual_limit_indep_2 || null,
            rate.annual_limit_indep_3 || known.annual_limit_indep_3 || null,
            rate.aggregate_limit_dep || known.aggregate_limit_dep || null,
            rate.aggregate_limit_indep || known.aggregate_limit_indep || null,
            rate.grad_annual_limit || known.grad_annual_limit || null,
            rate.grad_aggregate_limit || known.grad_aggregate_limit || null,
            effectiveDate || null,
            expirationDate || null,
            dataSource,
            JSON.stringify(rate),
          ]
        );

        ratesUpdated++;
        console.log(`[crawl-federal] Upserted ${rateType}: ${interestRate}%`);
      } catch (rateErr) {
        errors.push({ rate_type: rate.rate_type, error: rateErr.message });
        console.error(`[crawl-federal] Error upserting ${rate.rate_type}:`, rateErr.message);
      }
    }

    // Update crawl log
    const durationMs = Date.now() - startTime;
    await query(
      `UPDATE price_crawl_log
       SET status = 'completed',
           schools_updated = $1,
           duration_ms = $2,
           metadata = metadata || $3,
           completed_at = NOW()
       WHERE id = $4`,
      [
        ratesUpdated,
        durationMs,
        JSON.stringify({ data_source: dataSource, errors: errors.slice(0, 10) }),
        crawlLogId,
      ]
    );

    await logAction({
      agent: AGENT_NAME,
      actionType: 'crawl',
      platform: 'federal_rates',
      content: `Updated ${ratesUpdated} federal rate types for ${academicYear} (source: ${dataSource})`,
      responseData: { rates_updated: ratesUpdated, academic_year: academicYear, data_source: dataSource },
      status: errors.length > 0 ? 'partial' : 'success',
      errorMessage: errors.length > 0 ? `${errors.length} rate errors` : null,
    });

    console.log(`[crawl-federal] Complete: ${ratesUpdated} rates updated (source: ${dataSource}) in ${durationMs}ms`);

    return {
      rates_updated: ratesUpdated,
      academic_year: academicYear,
      data_source: dataSource,
      crawl_log_id: crawlLogId,
      errors_count: errors.length,
      duration_ms: durationMs,
    };
  } catch (err) {
    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: 'federal_rates',
      content: 'Federal rates crawl failed',
      status: 'error',
      errorMessage: err.message,
    }).catch(() => {});

    console.error('[crawl-federal] Fatal error:', err);
    throw err;
  }
};