spb/cancerindex

1import { sql } from 'drizzle-orm';2import type { Database } from '@cancerindex/database';34/**5 * Lineage trace (CLAUDE.md §252-253): ranked value → inputs → observation → provenance → raw record.6 * Supported: rankings/<id>, epidemiology_observations/<id>, cancer_gene_frequencies/<id>, literature_counts/<id>.7 */8export async function traceValue(db: Database, table: string, id: string): Promise<Record<string, unknown>> {9  const out: Record<string, unknown> = { table, id };10  const prov = async (pid: number | null | undefined) => {11    if (!pid) return null;12    const [p] = await db.execute<Record<string, unknown>>(sql`SELECT p.*, s.name AS source_name, s.slug AS source_slug, s.license FROM provenance p JOIN sources s ON s.id = p.source_id WHERE p.id = ${pid}`);13    if (!p) return null;14    const [rec] = await db.execute<Record<string, unknown>>(sql`SELECT id, entity_kind, source_record_id, raw_path, payload_hash, retrieved_at, status FROM source_records WHERE source_id = ${p.source_id as string} AND source_record_id = ${(p.source_record_id as string) ?? ''} LIMIT 1`);15    return { provenance: p, sourceRecord: rec ?? null };16  };17  switch (table) {18    case 'rankings': {19      const [r] = await db.execute<Record<string, unknown>>(sql`SELECT r.*, s.formula_version, s.inputs_hash, s.generated_at AS snapshot_generated_at, m.formula, m.name AS metric_name FROM rankings r JOIN ranking_snapshots s ON s.id = r.snapshot_id JOIN metric_definitions m ON m.slug = r.metric_slug WHERE r.id = ${Number(id)}`);20      if (!r) return { ...out, error: 'not found' };21      out.ranking = r;22      const inputs = (r.inputs ?? {}) as Record<string, unknown>;23      for (const k of ['observationId', 'incidenceObservationId', 'mortalityObservationId']) {24        if (inputs[k]) out[k] = await traceValue(db, 'epidemiology_observations', String(inputs[k]));25      }26      return out;27    }28    case 'epidemiology_observations': {29      const [o] = await db.execute<Record<string, unknown>>(sql`SELECT * FROM epidemiology_observations WHERE id = ${Number(id)}`);30      if (!o) return { ...out, error: 'not found' };31      return { ...out, observation: o, lineage: await prov(o.provenance_id as number) };32    }33    case 'cancer_gene_frequencies': {34      const [o] = await db.execute<Record<string, unknown>>(sql`SELECT * FROM cancer_gene_frequencies WHERE id = ${Number(id)}`);35      if (!o) return { ...out, error: 'not found' };36      return { ...out, frequency: o, lineage: await prov(o.provenance_id as number) };37    }38    case 'literature_counts': {39      const [o] = await db.execute<Record<string, unknown>>(sql`SELECT * FROM literature_counts WHERE id = ${Number(id)}`);40      if (!o) return { ...out, error: 'not found' };41      return { ...out, literatureCount: o, lineage: await prov(o.provenance_id as number) };42    }43    case 'survival_observations': {44      const [o] = await db.execute<Record<string, unknown>>(sql`SELECT * FROM survival_observations WHERE id = ${Number(id)}`);45      if (!o) return { ...out, error: 'not found' };46      return { ...out, observation: o, lineage: await prov(o.provenance_id as number) };47    }48    default:49      return { ...out, error: 'unsupported table' };50  }51}52