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}2728const isMain = process.argv[1] && path.resolve(process.argv[1]) === fileURLToPath(import.meta.url);29if (isMain) {30  runMigrations()31    .then(async () => {32      console.log('[migrate] done');33      await closeDb();34    })35    .catch(async (err) => {36      console.error('[migrate] failed', err);37      await closeDb();38      process.exit(1);39    });40}41