← back to Stayclaim

scripts/enrich-entities-wikidata.ts

147 lines

#!/usr/bin/env -S npx tsx
/**
 * Wikidata enrichment for entity rows.
 *
 * For each entity missing wiki_url / birth_year / death_year, queries the
 * Wikidata SPARQL endpoint to find the Q-ID by exact label match (English),
 * then pulls birth date (P569), death date (P570), occupation (P106), and
 * the entity's English Wikipedia URL via sitelinks.
 *
 * Wikidata is a Tier-C source. Updates carry confidence 0.7 and only
 * fill empty fields — never overwrite existing data.
 *
 * Usage:
 *   npx tsx scripts/enrich-entities-wikidata.ts
 *   npx tsx scripts/enrich-entities-wikidata.ts --slug paul-r-williams
 *   npx tsx scripts/enrich-entities-wikidata.ts --kind architect
 *   npx tsx scripts/enrich-entities-wikidata.ts --dry
 */
import { Pool } from 'pg';

type WdRow = {
  item: { value: string };
  itemLabel: { value: string };
  birth?: { value: string };
  death?: { value: string };
  wiki?: { value: string };
};

const SPARQL = `
SELECT ?item ?itemLabel ?birth ?death ?wiki WHERE {
  ?item rdfs:label "%LABEL%"@en.
  OPTIONAL { ?item wdt:P569 ?birth. }
  OPTIONAL { ?item wdt:P570 ?death. }
  OPTIONAL {
    ?wiki schema:about ?item;
          schema:isPartOf <https://en.wikipedia.org/>.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
LIMIT 5
`;

async function queryWikidata(name: string): Promise<WdRow | null> {
  const sparql = SPARQL.replace('%LABEL%', name.replace(/"/g, '\\"'));
  const url = 'https://query.wikidata.org/sparql?format=json&query=' + encodeURIComponent(sparql);
  const res = await fetch(url, {
    headers: {
      Accept: 'application/sparql-results+json',
      'User-Agent': 'pastdoor/0.1 (https://pastdoor.com; data-enrichment)',
    },
  });
  if (!res.ok) throw new Error(`Wikidata ${res.status}`);
  const j = (await res.json()) as { results: { bindings: WdRow[] } };
  return j.results.bindings[0] ?? null;
}

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

async function main() {
  const args = process.argv.slice(2);
  const slugArg = args.find((a, i) => args[i - 1] === '--slug');
  const kindArg = args.find((a, i) => args[i - 1] === '--kind');
  const dry = args.includes('--dry');

  const conds: string[] = [];
  const params: unknown[] = [];
  if (slugArg) {
    params.push(slugArg);
    conds.push(`slug = $${params.length}`);
  }
  if (kindArg) {
    params.push(kindArg);
    conds.push(`kind = $${params.length}`);
  }
  const sql = `SELECT id, slug, display_name, kind, birth_year, death_year, wiki_url
               FROM entity
               ${conds.length ? 'WHERE ' + conds.join(' AND ') : ''}
               ORDER BY display_name`;
  const { rows } = await pool.query(sql, params);

  let queried = 0;
  let updated = 0;
  let nohit = 0;
  for (const e of rows) {
    queried++;
    let hit: WdRow | null = null;
    try {
      hit = await queryWikidata(e.display_name);
    } catch (err) {
      console.warn(`[${e.slug}] wikidata error: ${(err as Error).message}`);
    }
    if (!hit) {
      nohit++;
      console.log(`[${e.slug}] no wikidata hit`);
      await new Promise(r => setTimeout(r, 600));
      continue;
    }
    const updates: Record<string, unknown> = {};
    if (!e.birth_year && hit.birth?.value) {
      const y = Number(hit.birth.value.slice(0, 4));
      if (Number.isFinite(y)) updates.birth_year = y;
    }
    if (!e.death_year && hit.death?.value) {
      const y = Number(hit.death.value.slice(0, 4));
      if (Number.isFinite(y)) updates.death_year = y;
    }
    if (!e.wiki_url && hit.wiki?.value) updates.wiki_url = hit.wiki.value;

    if (Object.keys(updates).length === 0) {
      console.log(`[${e.slug}] up to date`);
      await new Promise(r => setTimeout(r, 400));
      continue;
    }
    if (dry) {
      console.log(`[${e.slug}] DRY would set`, updates);
    } else {
      const setParts: string[] = [];
      const vals: unknown[] = [];
      for (const [k, v] of Object.entries(updates)) {
        vals.push(v);
        setParts.push(`${k} = $${vals.length}`);
      }
      vals.push(e.id);
      await pool.query(
        `UPDATE entity SET ${setParts.join(', ')} WHERE id = $${vals.length}`,
        vals
      );
      console.log(`[${e.slug}] updated`, updates);
      updated++;
    }
    await new Promise(r => setTimeout(r, 600)); // be a polite Wikidata client
  }

  console.log(JSON.stringify({ queried, updated, no_match: nohit, dry }, null, 2));
  await pool.end();
}

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