spb/cancerindex

1import { fileURLToPath } from 'node:url';2import path from 'node:path';3import { eq, sql } from 'drizzle-orm';4import { getDb, closeDb } from './client.js';5import { biomarkers, geographies, metricDefinitions } from './schema/index.js';6import { mintId } from './ids.js';7import { METRIC_CATALOG } from './seed-data/metrics.js';8import { GEOGRAPHY_SEED } from './seed-data/geographies.js';9import { BIOMARKER_SEED, buildMeasurement, seedGeneSymbols } from './seed-data/biomarkers.js';10import type { Database } from './client.js';1112/**13 * Canonical biomarkers (SPEC §17, §52, §121): curated metadata only — identity, verified NCIt code,14 * anchor gene, aliases, assay conventions. Every scientific link is derived at query time.15 * Idempotent by slug; `CI-BIO` ids are minted once and never reassigned. Anchor genes are resolved16 * against `genes` by HGNC symbol; an unresolved symbol is reported and left null, never invented.17 */18export async function seedBiomarkers(db: Database): Promise<{ inserted: number; updated: number; total: number; unresolvedGenes: string[] }> {19  const symbols = seedGeneSymbols();20  const rows = symbols.length ? await db.execute<{ id: string; symbol: string }>(sql`SELECT id, symbol FROM genes WHERE symbol = ANY(${sql.param(symbols)}::text[])`) : [];21  const geneId = new Map<string, string>();22  for (const r of rows) geneId.set(r.symbol, r.id);23  const unresolvedGenes = symbols.filter((s) => !geneId.has(s));24  let inserted = 0;25  let updated = 0;26  for (const b of BIOMARKER_SEED) {27    const values = {28      name: b.name,29      kind: b.kind,30      geneId: b.geneSymbol ? geneId.get(b.geneSymbol) ?? null : null,31      ncitCode: b.ncitCode,32      description: b.description,33      measurement: buildMeasurement(b) as unknown as Record<string, unknown>,34    };35    const [existing] = await db.select({ id: biomarkers.id }).from(biomarkers).where(eq(biomarkers.slug, b.slug)).limit(1);36    if (existing) {37      await db.update(biomarkers).set({ ...values, updatedAt: new Date() }).where(eq(biomarkers.id, existing.id));38      updated++;39    } else {40      await db.insert(biomarkers).values({ id: await mintId(db, 'BIO'), slug: b.slug, ...values });41      inserted++;42    }43  }44  const [{ n }] = (await db.execute<{ n: string }>(sql`SELECT count(*)::text AS n FROM biomarkers`)) as unknown as [{ n: string }];45  return { inserted, updated, total: Number(n), unresolvedGenes };46}4748/**49 * Seeds only system data (CLAUDE.md §358): metric definitions, canonical geographies and the50 * curated biomarker catalogue (metadata, not scientific values). Scientific data is never seeded —51 * it comes from connectors. Sources are synced from connector manifests by `pnpm cix sources:sync`52 * (lives in the root CLI to avoid a package cycle).53 */54export async function seed(): Promise<void> {55  const db = getDb({ max: 2 });56  for (const m of METRIC_CATALOG) {57    const [existing] = await db.select({ id: metricDefinitions.id }).from(metricDefinitions).where(eq(metricDefinitions.slug, m.slug)).limit(1);58    const values = { ...m, updatedAt: new Date() };59    if (existing) await db.update(metricDefinitions).set(values).where(eq(metricDefinitions.id, existing.id));60    else await db.insert(metricDefinitions).values({ id: await mintId(db, 'METRIC'), ...m });61  }62  const bySlug = new Map<string, string>();63  for (const g of GEOGRAPHY_SEED) {64    const [existing] = await db.select({ id: geographies.id }).from(geographies).where(eq(geographies.slug, g.slug)).limit(1);65    const parentId = g.parentSlug ? bySlug.get(g.parentSlug) ?? null : null;66    if (existing) {67      await db.update(geographies).set({ name: g.name, kind: g.kind, iso2: g.iso2 ?? null, iso3: g.iso3 ?? null, parentId, whoRegion: g.whoRegion ?? null }).where(eq(geographies.id, existing.id));68      bySlug.set(g.slug, existing.id);69    } else {70      const id = await mintId(db, 'GEO');71      await db.insert(geographies).values({ id, slug: g.slug, name: g.name, kind: g.kind, iso2: g.iso2 ?? null, iso3: g.iso3 ?? null, parentId, whoRegion: g.whoRegion ?? null });72      bySlug.set(g.slug, id);73    }74  }75  const [{ n }] = (await db.execute<{ n: string }>(sql`SELECT count(*)::text AS n FROM metric_definitions`)) as unknown as [{ n: string }];76  const bio = await seedBiomarkers(db);77  console.log(`[seed] metrics=${n} geographies=${GEOGRAPHY_SEED.length} biomarkers=${bio.total} (inserted=${bio.inserted} updated=${bio.updated})`);78  if (bio.unresolvedGenes.length) console.warn(`[seed] biomarkers: unresolved HGNC symbols (gene_id left null): ${bio.unresolvedGenes.join(', ')}`);79}8081const isMain = process.argv[1] && path.resolve(process.argv[1]) === fileURLToPath(import.meta.url);82if (isMain) {83  seed()84    .then(() => closeDb())85    .catch(async (e) => {86      console.error('[seed] failed', e);87      await closeDb();88      process.exit(1);89    });90}91