`scripts/ingest-la-newspapers-metadata.ts`

184 lines
/**
 * ingest-la-newspapers-metadata.ts
 *
 * Phase 1 of the LA-area historical newspaper ingest. Pulls *metadata only*
 * (no OCR text yet) for every issue in 4 confirmed Internet Archive
 * collections covering Greater LA pre-1951. Run companion script
 * `pull-la-newspapers-ocr.ts` to backfill the OCR text afterward.
 *
 * Sources (all free, public, IA-hosted, OCR-searchable):
 *   - pub_los-angeles-times                            ~17,298 issues 1881–1951
 *   - newspaperarchive-los-angeles-daily-herald         ~2,408 issues 1873–~1900
 *   - newspaperarchive-pomona-progress-bulletin         ~3,065 issues 1967+
 *   - newspaperarchive-santa-ana-orange-county-register   ~339 issues 1929+
 *
 * IA scrape API: https://archive.org/services/search/v1/scrape
 *   - cursor-paginated; max 10K rows per cursor; preferred for >1K results
 *
 * Idempotent — safe to re-run. Uses ON CONFLICT (source_identifier) DO UPDATE.
 *
 * Tier: A (Internet Archive scan of canonical historical paper).
 *
 * Usage: bun run scripts/ingest-la-newspapers-metadata.ts
 */
import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.PGHOST ?? '/tmp',
  database: process.env.PGDATABASE ?? 'stayclaim',
  user: process.env.PGUSER ?? process.env.USER,
  password: process.env.PGPASSWORD,
  port: parseInt(process.env.PGPORT ?? '5432', 10),
  max: 4,
});

type Paper = {
  collection: string;
  paperName: string;
  paperShort: string;
};

const PAPERS: Paper[] = [
  { collection: 'pub_los-angeles-times',                                paperName: 'Los Angeles Times',              paperShort: 'LATimes' },
  { collection: 'newspaperarchive-los-angeles-daily-herald',            paperName: 'Los Angeles Daily Herald',       paperShort: 'LADailyHerald' },
  { collection: 'newspaperarchive-pomona-progress-bulletin',            paperName: 'Pomona Progress Bulletin',       paperShort: 'PomonaProgress' },
  { collection: 'newspaperarchive-santa-ana-orange-county-register',    paperName: 'Santa Ana Orange County Register', paperShort: 'SantaAnaRegister' },
];

const SCRAPE = 'https://archive.org/services/search/v1/scrape';

type ScrapeRow = {
  identifier: string;
  date?: string;
  title?: string;
};

async function scrapeCollection(collection: string, onBatch: (rows: ScrapeRow[]) => Promise<void>) {
  let cursor: string | undefined;
  let total = 0;
  while (true) {
    const params = new URLSearchParams({
      q: `collection:${collection}`,
      fields: 'identifier,date,title',
      count: '1000',
    });
    if (cursor) params.set('cursor', cursor);
    const res = await fetch(`${SCRAPE}?${params}`);
    if (!res.ok) throw new Error(`IA scrape ${collection} → ${res.status}`);
    const json = (await res.json()) as { items: ScrapeRow[]; cursor?: string; total: number };
    if (!json.items?.length) break;
    await onBatch(json.items);
    total += json.items.length;
    if (!json.cursor) break;
    cursor = json.cursor;
  }
  return total;
}

async function ensureSchema() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS news_issue (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      source_collection TEXT NOT NULL,
      source_identifier TEXT UNIQUE NOT NULL,
      paper_name TEXT NOT NULL,
      paper_short TEXT NOT NULL,
      issue_date DATE,
      ia_url TEXT NOT NULL,
      ocr_text_url TEXT NOT NULL,
      ocr_text TEXT,
      ocr_text_tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', coalesce(ocr_text, ''))) STORED,
      ocr_fetched_at TIMESTAMPTZ,
      page_count INT,
      source_tier CHAR(1) NOT NULL DEFAULT 'A',
      source_label TEXT NOT NULL DEFAULT 'Internet Archive',
      title TEXT,
      metadata_at TIMESTAMPTZ DEFAULT now()
    );
    CREATE INDEX IF NOT EXISTS idx_news_issue_date ON news_issue(issue_date);
    CREATE INDEX IF NOT EXISTS idx_news_issue_paper_date ON news_issue(paper_short, issue_date);
    CREATE INDEX IF NOT EXISTS idx_news_issue_tsv ON news_issue USING GIN(ocr_text_tsv);
    CREATE INDEX IF NOT EXISTS idx_news_issue_ocr_pending ON news_issue(paper_short) WHERE ocr_text IS NULL;
  `);
  console.log('schema OK');
}

async function upsertBatch(paper: Paper, rows: ScrapeRow[]): Promise<number> {
  if (!rows.length) return 0;
  const values: any[] = [];
  const tuples: string[] = [];
  rows.forEach((r, i) => {
    const base = i * 8;
    tuples.push(`($${base+1},$${base+2},$${base+3},$${base+4},$${base+5},$${base+6},$${base+7},$${base+8})`);
    const iaUrl     = `https://archive.org/details/${r.identifier}`;
    const ocrUrl    = `https://archive.org/download/${r.identifier}/${r.identifier}_djvu.txt`;
    const issueDate = r.date && /^\d{4}-\d{2}-\d{2}/.test(r.date) ? r.date.slice(0,10) : null;
    values.push(
      paper.collection,
      r.identifier,
      paper.paperName,
      paper.paperShort,
      issueDate,
      iaUrl,
      ocrUrl,
      r.title ?? null,
    );
  });
  const sql = `
    INSERT INTO news_issue
      (source_collection, source_identifier, paper_name, paper_short, issue_date, ia_url, ocr_text_url, title)
    VALUES ${tuples.join(',')}
    ON CONFLICT (source_identifier) DO UPDATE SET
      issue_date = COALESCE(EXCLUDED.issue_date, news_issue.issue_date),
      title      = COALESCE(EXCLUDED.title, news_issue.title)
  `;
  const r = await pool.query(sql, values);
  return r.rowCount ?? 0;
}

async function main() {
  await ensureSchema();
  let grandTotal = 0;
  for (const paper of PAPERS) {
    console.log(`\n→ ${paper.paperName} (${paper.collection})`);
    let pulled = 0;
    let inserted = 0;
    await scrapeCollection(paper.collection, async (rows) => {
      const n = await upsertBatch(paper, rows);
      pulled += rows.length;
      inserted += n;
      process.stdout.write(`  scraped ${pulled} … upserted ${inserted}\r`);
    });
    console.log(`  ✓ ${paper.paperName}: ${pulled} scraped, ${inserted} upserted`);
    grandTotal += pulled;
  }

  const { rows: stats } = await pool.query<{
    paper_short: string;
    issues: string;
    earliest: string | null;
    latest: string | null;
    ocr_pending: string;
  }>(`
    SELECT paper_short,
           count(*)::text as issues,
           min(issue_date)::text as earliest,
           max(issue_date)::text as latest,
           count(*) FILTER (WHERE ocr_text IS NULL)::text as ocr_pending
    FROM news_issue
    GROUP BY paper_short
    ORDER BY paper_short
  `);
  console.log('\n--- summary ---');
  for (const s of stats) {
    console.log(`  ${s.paper_short.padEnd(20)} issues=${s.issues.padStart(6)}  ${s.earliest ?? '?'} → ${s.latest ?? '?'}  pending OCR=${s.ocr_pending}`);
  }
  console.log(`\ngrand total scraped: ${grandTotal}`);
  await pool.end();
}

main().catch(e => {
  console.error('FATAL', e);
  process.exit(1);
});