spb/cancerindex

1import { migrate } from 'drizzle-orm/postgres-js/migrator';2import { fileURLToPath } from 'node:url';3import path from 'node:path';4import { getDb, getSql, closeDb } from './client.js';56/**7 * Runs SQL migrations from ./migrations (CLAUDE.md §284). Extensions are created first because8 * drizzle-kit does not manage them; pgvector is optional and only enabled when available.9 */10export async function runMigrations(): Promise<void> {11  const sql = getSql({ max: 1 });12  await sql`CREATE EXTENSION IF NOT EXISTS pg_trgm`;13  await sql`CREATE EXTENSION IF NOT EXISTS unaccent`;14  const [vec] = await sql`SELECT 1 AS ok FROM pg_available_extensions WHERE name = 'vector'`;15  if (vec) await sql`CREATE EXTENSION IF NOT EXISTS vector`;16  else console.warn('[migrate] pgvector not available — semantic search disabled until installed');17  const here = path.dirname(fileURLToPath(import.meta.url));18  await migrate(getDb({ max: 1 }), { migrationsFolder: path.resolve(here, '../migrations') });19  // Trigram indexes for fuzzy search (not expressible in drizzle schema).20  await sql`CREATE INDEX IF NOT EXISTS cancer_aliases_trgm_idx ON cancer_aliases USING gin (normalized gin_trgm_ops)`;21  await sql`CREATE INDEX IF NOT EXISTS cancers_name_trgm_idx ON cancers USING gin (lower(canonical_name) gin_trgm_ops)`;22  await sql`CREATE INDEX IF NOT EXISTS genes_symbol_trgm_idx ON genes USING gin (lower(symbol) gin_trgm_ops)`;23  await sql`CREATE INDEX IF NOT EXISTS drugs_name_trgm_idx ON drugs USING gin (lower(name) gin_trgm_ops)`;24  await sql`CREATE INDEX IF NOT EXISTS trials_title_trgm_idx ON clinical_trials USING gin (lower(brief_title) gin_trgm_ops)`;25  await sql`CREATE INDEX IF NOT EXISTS publications_title_trgm_idx ON publications USING gin (lower(title) gin_trgm_ops)`;26  await createPerformanceIndexes(sql);27}2829/**30 * Performance indexes reviewed 2026-09-08 (docs/schema-changes-ops.md) — idempotent, applied after31 * every migration. GIN array indexes serve the containment form (`gene_ids @> ARRAY[$1]::text[]`,32 * used by the ranking counters); `x = ANY(array_column)` cannot use them. text_pattern_ops makes33 * `normalized LIKE 'prefix%'` indexable under the en_US collation the databases are created with.34 */35export async function createPerformanceIndexes(sql: ReturnType<typeof getSql>): Promise<void> {36  // CIViC evidence: per-gene / per-variant / per-therapy roll-ups (counters, gene & drug pages).37  await sql`CREATE INDEX IF NOT EXISTS civic_evidence_gene_ids_gin ON civic_evidence_items USING gin (gene_ids)`;38  await sql`CREATE INDEX IF NOT EXISTS civic_evidence_variant_ids_gin ON civic_evidence_items USING gin (variant_ids)`;39  await sql`CREATE INDEX IF NOT EXISTS civic_evidence_therapy_ids_gin ON civic_evidence_items USING gin (therapy_ids)`;40  // Trials: descendant scope → trials semi-join (index-only) and status/type facets.41  // Deliberately NO (overall_status, last_update_posted_date) index: with `ORDER BY … LIMIT 20` the42  // planner walked that ordered index for small scopes (2 ms → 116 ms measured) instead of the43  // trial_conditions semi-join.44  await sql`CREATE INDEX IF NOT EXISTS trial_conditions_cancer_trial_idx ON trial_conditions (cancer_id, trial_id)`;45  await sql`CREATE INDEX IF NOT EXISTS clinical_trials_status_type_idx ON clinical_trials (overall_status, study_type)`;46  await sql`DROP INDEX IF EXISTS clinical_trials_status_updated_idx`;47  // Aliases: prefix lookups (`normalized LIKE 'glioblastoma%'`) in search / cancers?q=.48  await sql`CREATE INDEX IF NOT EXISTS cancer_aliases_norm_pattern_idx ON cancer_aliases (normalized text_pattern_ops)`;49  // Ops: latest run per connector (doctor, anomaly guard baseline).50  await sql`CREATE INDEX IF NOT EXISTS ingest_runs_connector_status_idx ON ingest_runs (connector_id, status, started_at DESC)`;51}5253const isMain = process.argv[1] && path.resolve(process.argv[1]) === fileURLToPath(import.meta.url);54if (isMain) {55  runMigrations()56    .then(async () => {57      console.log('[migrate] done');58      await closeDb();59    })60    .catch(async (err) => {61      console.error('[migrate] failed', err);62      await closeDb();63      process.exit(1);64    });65}66