`scripts/seed-reviews-from-reddit.js`

79 lines
#!/usr/bin/env node
/**
 * Seed `reviews` from `org_signals` where source='reddit'.
 *
 * For each Reddit signal we don't already have a review for:
 *   1. Send title+body to local Ollama (gemma3:4b) for sentiment + 1-5 score.
 *   2. Insert a review with source='reddit', source_url=permalink,
 *      reviewer_display=reddit username, scores from LLM analysis.
 *   3. Skip if we've already seeded that URL.
 *
 * Run nightly via launchd (com.steve.pd-reddit-reviews-nightly).
 */
const { fetch } = require('undici');
const { pool, query } = require('../agents/shared/db');

const OLLAMA = process.env.OLLAMA_BASE || 'http://127.0.0.1:11434';
const MODEL  = process.env.SENTIMENT_MODEL || 'gemma3:4b';

async function classifySentiment(title, body) {
  const text = (title + '\n\n' + body).slice(0, 4000);
  const prompt = `Classify the sentiment of this Reddit post about a healthcare practice. Output STRICT JSON only:
{"score": 1-5, "summary": "one sentence summary of the patient's actual experience"}

Score 1=terrible, 3=neutral, 5=excellent. If the post is just a question without an experience, output {"score": null, "summary": "asking for recommendations"}.

Post:
"""
${text}
"""

JSON:`;
  try {
    const r = await fetch(`${OLLAMA}/api/generate`, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ model: MODEL, prompt, stream: false, format: 'json',
        options: { temperature: 0.2, num_ctx: 4096 } }),
      signal: AbortSignal.timeout(45_000),
    });
    if (!r.ok) return null;
    const j = await r.json();
    const out = JSON.parse(j.response || '{}');
    const score = out.score != null ? Math.max(1, Math.min(5, Math.round(Number(out.score)))) : null;
    return { score, summary: String(out.summary || '').slice(0, 1000) };
  } catch { return null; }
}

async function main() {
  const r = await query(`
    SELECT s.id AS signal_id, s.organization_id, s.title, s.body, s.author, s.posted_at, s.url
      FROM org_signals s
     WHERE s.source = 'reddit'
       AND NOT EXISTS (SELECT 1 FROM reviews rv WHERE rv.source_url = s.url)
     ORDER BY s.id
     LIMIT ${Number(process.env.LIMIT) || 1000}
  `);
  console.log(`[reddit→reviews] candidates=${r.rowCount} model=${MODEL}`);

  let ok = 0, skipped = 0, fail = 0;
  for (const s of r.rows) {
    const sent = await classifySentiment(s.title || '', s.body || '');
    if (!sent || sent.score == null) { skipped++; continue; }
    try {
      await query(`
        INSERT INTO reviews (target_organization_id, reviewer_display,
                             overall_score, body, source, source_url, source_posted_at)
        VALUES ($1, $2, $3, $4, 'reddit', $5, $6)
        ON CONFLICT (source_url) DO NOTHING
      `, [s.organization_id, s.author || 'reddit user', sent.score, (sent.summary || s.title || s.body || '').slice(0, 5000), s.url, s.posted_at || null]);
      ok++;
      if (ok % 10 === 0) console.log(`[reddit→reviews] ok=${ok} skipped=${skipped} fail=${fail}`);
    } catch (e) { fail++; }
  }
  console.log(`[reddit→reviews] done. ok=${ok} skipped=${skipped} fail=${fail}`);
  await pool.end();
}

main().catch(async e => { console.error(e); try { await pool.end(); } catch (_) {}; process.exit(1); });