bigtech-sucks/packages/core/db/ingest/run.mjs

#!/usr/bin/env node
/**
 * Self-authoring ingest.
 *
 * The roster writes itself. The scores do not.
 *
 *   DISCOVERY   automatic. A source names a piece of software and a usage number.
 *   ASSESSMENT  never automatic. A score requires a written rationale and tier-1
 *               or tier-2 evidence, and the build gate enforces that.
 *
 * That split is the whole design. If the ingest wrote scores, the oath on the
 * front page would be a lie the same afternoon.
 *
 * Run: npm run db:ingest
 * Dry run: npm run db:ingest -- --dry
 */
import { DatabaseSync } from 'node:sqlite';
import { readFileSync } from 'node:fs';
import { join, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';
import { ADAPTERS, looksLikeUserSoftware } from './sources.mjs';

const HERE = dirname(fileURLToPath(import.meta.url));
const DB_PATH = join(HERE, '..', 'bigtech.db');
const DRY = process.argv.includes('--dry');

/** What to go looking for, and what to rank it by. */
const TARGETS = [
  { category: 'messaging', source: 'flathub', query: 'messaging' },
  { category: 'messaging', source: 'github',  query: 'messaging' },
  { category: 'email',     source: 'flathub', query: 'email' },
  { category: 'email',     source: 'github',  query: 'mail-client' },
  { category: 'passwords', source: 'flathub', query: 'password' },
  { category: 'browser',   source: 'flathub', query: 'web browser' },
  { category: 'notes',     source: 'flathub', query: 'notes' },
  { category: 'files',     source: 'github',  query: 'file-sync' },
  { category: 'photos',    source: 'flathub', query: 'photo' },
  // Flathub is a curated store of end-user Linux apps, so relevance is free.
  // GitHub topics are uncurated, so relevance has to be earned by a classifier.
  // Prefer the store wherever it has the category. Verified 2026-10-29: the
  // GitHub topic 'local-llm' returned Langroid and Pangu.js, neither of which
  // is a product a person installs.
  { category: 'ai',        source: 'flathub', query: 'llm' },
];

const slug = (s) =>
  s.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '').slice(0, 48);

const now = new Date().toISOString();

async function main() {
  const db = new DatabaseSync(DB_PATH);
  db.exec('PRAGMA foreign_keys = ON');
  db.exec(readFileSync(join(HERE, '..', 'schema.sql'), 'utf8'));

  const existing = new Map(
    db.prepare('SELECT id, LOWER(name) AS n FROM product').all().map((r) => [r.n, r.id]),
  );
  const taken = new Set(db.prepare('SELECT id FROM product').all().map((r) => r.id));

  const insProduct = db.prepare(`
    INSERT OR IGNORE INTO product
      (id, vendor_id, name, category_id, platform, source_url, license, sovereign, boss, art_slot, monthly_cost, notes)
    VALUES (?, 'unknown', ?, ?, ?, ?, ?, 0, 0, NULL, NULL, ?)`);

  const insDiscovery = db.prepare(`
    INSERT OR IGNORE INTO discovery
      (product_id, source, source_url, usage_signal, usage_metric, observed_at, raw)
    VALUES (?, ?, ?, ?, ?, ?, ?)`);

  const stats = { targets: 0, rowsSeen: 0, newProducts: 0, newReceipts: 0, failed: [] };

  for (const target of TARGETS) {
    const adapter = ADAPTERS[target.source];
    if (!adapter) { console.warn(`  ! unknown source ${target.source}`); continue; }
    stats.targets++;

    let rows;
    try {
      rows = await adapter(target.query);
    } catch (err) {
      stats.failed.push(`${target.source}/${target.query}: ${err.message}`);
      console.warn(`  FAIL  ${target.source}/${target.query}  ${err.message}`);
      continue;
    }

    let added = 0;
    let dropped = 0;
    for (const row of rows) {
      // Relevance gate first. A highly starred message broker is not a product
      // a person chooses, and a roster full of them is a roster nobody trusts.
      if (row.platform === 'source' && !looksLikeUserSoftware(row)) { dropped++; continue; }
      stats.rowsSeen++;
      const key = row.name.toLowerCase();
      let id = existing.get(key);

      if (!id) {
        let candidate = slug(row.name);
        if (taken.has(candidate)) candidate = `${candidate}-${slug(target.category)}`;
        if (taken.has(candidate)) continue;

        if (!DRY) {
          insProduct.run(
            candidate, row.name, target.category, row.platform,
            row.sourceUrl, row.raw?.license ?? null,
            `Discovered by ${target.source} ingest on ${now.slice(0, 10)}. Unassessed.`,
          );
        }
        taken.add(candidate);
        existing.set(key, candidate);
        id = candidate;
        stats.newProducts++;
        added++;
      }

      if (!DRY) {
        const r = insDiscovery.run(
          id, target.source, row.sourceUrl, row.usage, row.usageMetric,
          now, JSON.stringify(row.raw),
        );
        if (r.changes) stats.newReceipts++;
      }
    }
    console.log(
      `  ${target.source.padEnd(8)} ${String(target.query).padEnd(16)} ${target.category.padEnd(11)} ${String(rows.length).padStart(3)} seen, ${added} new, ${dropped} not-an-app`,
    );
  }

  const backlog = db.prepare('SELECT * FROM v_backlog LIMIT 12').all();
  const cov = db.prepare('SELECT * FROM v_coverage').all();
  const assessed = cov.filter((c) => c.status !== 'unassessed').length;
  db.close();

  console.log('');
  console.log(DRY ? 'DRY RUN, nothing written' : 'INGEST COMPLETE');
  console.log(`  targets attempted : ${stats.targets}`);
  console.log(`  rows seen         : ${stats.rowsSeen}`);
  console.log(`  new products      : ${stats.newProducts}`);
  console.log(`  new receipts      : ${stats.newReceipts}`);
  console.log(`  failed sources    : ${stats.failed.length}`);
  console.log('');
  console.log(`COVERAGE  ${assessed} assessed / ${cov.length} products`);
  if (backlog.length) {
    console.log('');
    console.log('BACKLOG, ordered by usage');
    for (const b of backlog) {
      console.log(`  ${String(b.usage).padStart(8)}  ${b.name.padEnd(28)} ${String(b.category_id).padEnd(11)} via ${b.found_via}`);
    }
  }
}

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