spb/cancerindex

1import 'server-only';2import { run, sql, safe } from '@/lib/db';34export interface AdminOverview {5  tables: Array<{ table: string; n: number }>;6  runs24h: number;7  failedRuns7d: number;8  openUnresolved: number;9  currentSnapshots: number;10  auditRecent: Array<{ id: number; actor: string; action: string; entity_type: string | null; entity_id: string | null; reason: string | null; created_at: Date }>;11}1213const COUNTED_TABLES = ['cancers', 'cancer_aliases', 'cancer_hierarchy', 'cancer_codes', 'anatomical_sites', 'geographies', 'sources', 'ingest_runs', 'source_records', 'provenance', 'genes', 'variants', 'drugs', 'drug_approvals', 'clinical_trials', 'trial_conditions', 'publications', 'literature_counts', 'civic_evidence_items', 'genomic_cohorts', 'cancer_gene_frequencies', 'epidemiology_observations', 'survival_observations', 'ranking_snapshots', 'rankings', 'entity_counters', 'unresolved_labels', 'change_events', 'audit_log'];1415export async function adminOverview(): Promise<AdminOverview> {16  const tables = await safe(17    () => run<{ table: string; n: string }>(sql.join(COUNTED_TABLES.map((t) => sql`SELECT ${t}::text AS "table", (SELECT count(*) FROM ${sql.identifier(t)}) AS n`), sql` UNION ALL `)),18    [] as Array<{ table: string; n: string }>,19  );20  const [misc] = await safe(21    () =>22      run<{ runs24h: string; failed7d: string; open_unresolved: string; snaps: string }>(sql`23        SELECT (SELECT count(*) FROM ingest_runs WHERE started_at > now() - interval '24 hours') AS runs24h,24               (SELECT count(*) FROM ingest_runs WHERE status IN ('failed','aborted') AND started_at > now() - interval '7 days') AS failed7d,25               (SELECT count(*) FROM unresolved_labels WHERE status = 'open') AS open_unresolved,26               (SELECT count(*) FROM ranking_snapshots WHERE is_current) AS snaps`),27    [{ runs24h: '0', failed7d: '0', open_unresolved: '0', snaps: '0' }],28  );29  const auditRecent = await safe(() => run<AdminOverview['auditRecent'][number]>(sql`SELECT id, actor, action, entity_type, entity_id, reason, created_at FROM audit_log ORDER BY created_at DESC LIMIT 15`), []);30  return { tables: tables.map((t) => ({ table: t.table, n: Number(t.n) })), runs24h: Number(misc?.runs24h ?? 0), failedRuns7d: Number(misc?.failed7d ?? 0), openUnresolved: Number(misc?.open_unresolved ?? 0), currentSnapshots: Number(misc?.snaps ?? 0), auditRecent };31}3233export interface UnresolvedRow {34  id: number;35  source_id: string;36  source_slug: string;37  entity_kind: string;38  source_text: string;39  normalized: string;40  context: Record<string, unknown>;41  count: number;42  status: string;43  suggested_id: string | null;44  suggested_name: string | null;45  suggested_match_type: string | null;46  suggested_score: number | null;47  resolved_id: string | null;48  resolved_by: string | null;49  created_at: Date;50  updated_at: Date;51}5253export async function listUnresolved(opts: { entityKind: string; status: string; page: number; pageSize: number }): Promise<{ rows: UnresolvedRow[]; total: number; kinds: Array<{ k: string; n: number }> }> {54  const where = sql`${opts.entityKind ? sql`u.entity_kind = ${opts.entityKind}` : sql`true`} AND ${opts.status ? sql`u.status = ${opts.status}` : sql`true`}`;55  const total = await safe(() => run<{ n: string }>(sql`SELECT count(*) AS n FROM unresolved_labels u WHERE ${where}`), [{ n: '0' }]);56  const rows = await safe(57    () =>58      run<UnresolvedRow>(sql`59        SELECT u.*, s.slug AS source_slug,60          CASE u.entity_kind WHEN 'cancer' THEN (SELECT canonical_name FROM cancers WHERE id = u.suggested_id) WHEN 'drug' THEN (SELECT name FROM drugs WHERE id = u.suggested_id) WHEN 'gene' THEN (SELECT symbol FROM genes WHERE id = u.suggested_id) END AS suggested_name61        FROM unresolved_labels u JOIN sources s ON s.id = u.source_id WHERE ${where} ORDER BY u.count DESC, u.updated_at DESC LIMIT ${opts.pageSize} OFFSET ${(opts.page - 1) * opts.pageSize}`),62    [] as UnresolvedRow[],63  );64  const kinds = await safe(() => run<{ k: string; n: string }>(sql`SELECT entity_kind AS k, count(*) AS n FROM unresolved_labels WHERE status = 'open' GROUP BY 1 ORDER BY n DESC`), [] as Array<{ k: string; n: string }>);65  return { rows, total: Number(total[0]?.n ?? 0), kinds: kinds.map((k) => ({ k: k.k, n: Number(k.n) })) };66}6768export interface ConnectorHealthRow {69  slug: string;70  name: string;71  category: string;72  status: string;73  license_status: string;74  health: string | null;75  health_detail: string | null;76  paused: boolean | null;77  last_success_at: Date | null;78  last_attempt_at: Date | null;79  last_run_id: string | null;80  last_run_status: string | null;81  records_fetched: number | null;82  records_created: number | null;83  records_updated: number | null;84  records_rejected: number | null;85  schema_drift: unknown[] | null;86  runs_total: number;87  drift_fields: number;88}89export async function connectorHealth(): Promise<ConnectorHealthRow[]> {90  return safe(91    () =>92      run<ConnectorHealthRow>(sql`93        SELECT s.slug, s.name, s.category, s.status, s.license_status, cc.health, cc.health_detail, cc.paused, cc.last_success_at, cc.last_attempt_at,94               lr.id AS last_run_id, lr.status AS last_run_status, lr.records_fetched, lr.records_created, lr.records_updated, lr.records_rejected, lr.schema_drift,95               (SELECT count(*) FROM ingest_runs r WHERE r.source_id = s.id)::int AS runs_total,96               (SELECT count(*) FROM connector_field_stats f WHERE f.connector_id = s.slug)::int AS drift_fields97        FROM sources s LEFT JOIN connector_cursors cc ON cc.connector_id = s.slug98        LEFT JOIN LATERAL (SELECT * FROM ingest_runs r WHERE r.source_id = s.id ORDER BY r.started_at DESC LIMIT 1) lr ON true99        ORDER BY s.tier, s.name`),100    [] as ConnectorHealthRow[],101  );102}103