spb/cancerindex

1# Schema changes — ops layer (2026-09-08)23Developed with `drizzle-kit push` on `cancerindex_a` (CLAUDE.md conventions: parallel agents do not4commit migration files). **The integrator generates the migration** with `pnpm db:generate` after5merging; the expected DDL is listed here so the generated file can be reviewed against it.67## 1. New table `system_alerts` (CLAUDE.md §170)89Schema: `packages/database/src/schema/ext-ops.ts` (exported from `schema/index.ts`). Helpers:10`packages/database/src/alerts.ts` (`raiseAlert`, `raiseAlertSafe`, `resolveAlerts`, `listAlerts`).1112```sql13CREATE TABLE "system_alerts" (14  "id" bigserial PRIMARY KEY NOT NULL,15  "kind" text NOT NULL,                       -- connector_failure | connector_aborted | anomaly | schema_drift | source_failing | source_stale | …16  "severity" text DEFAULT 'warn' NOT NULL,    -- info | warn | critical17  "connector_id" text,18  "message" text NOT NULL,19  "detail" jsonb DEFAULT '{}'::jsonb NOT NULL,20  "first_seen_at" timestamp with time zone DEFAULT now() NOT NULL,21  "last_seen_at" timestamp with time zone DEFAULT now() NOT NULL,22  "count" integer DEFAULT 1 NOT NULL,23  "status" text DEFAULT 'open' NOT NULL,      -- open | acknowledged | resolved24  "resolved_at" timestamp with time zone25);26CREATE INDEX "system_alerts_status_idx" ON "system_alerts" USING btree ("status","kind","connector_id");27CREATE INDEX "system_alerts_seen_idx" ON "system_alerts" USING btree ("last_seen_at");28```2930Deduplication (kind + connector_id + message among open/acknowledged rows) is done in `raiseAlert`,31not by a unique index, so historical resolved rows with the same message can coexist.3233Writers: `runConnector` (failed / anomaly / aborted / schema drift, resolves on success), the worker34health probe (`source_failing`, `source_stale`). Readers: `pnpm cix alerts`, `pnpm cix doctor`,35`GET /v1/admin/alerts`. Manual triage: `pnpm cix alerts ack <id>` / `resolve <id>`.3637## 2. Manifest field `checkpointEvery`3839`ConnectorManifest.checkpointEvery` (zod, default 2000) — stored inside `sources.manifest` jsonb;40no DDL. Drives the automatic mid-run cursor checkpoint (docs/connectors/README.md).4142## 3. Performance indexes (raw SQL, `packages/database/src/migrate.ts` → `createPerformanceIndexes`)4344Idempotent `CREATE INDEX IF NOT EXISTS`, executed after the SQL migrations by `pnpm db:migrate`45(already the pattern for the trigram indexes). They are *not* part of the drizzle schema on purpose46(GIN / opclass indexes are not expressible there), so `drizzle-kit generate` will not emit them; the47post-migration block recreates them idempotently after every `pnpm db:migrate`, which also runs in the48mld `post_sync` hook on deploy.4950```sql51CREATE INDEX IF NOT EXISTS civic_evidence_gene_ids_gin      ON civic_evidence_items USING gin (gene_ids);52CREATE INDEX IF NOT EXISTS civic_evidence_variant_ids_gin   ON civic_evidence_items USING gin (variant_ids);53CREATE INDEX IF NOT EXISTS civic_evidence_therapy_ids_gin   ON civic_evidence_items USING gin (therapy_ids);54CREATE INDEX IF NOT EXISTS trial_conditions_cancer_trial_idx ON trial_conditions (cancer_id, trial_id);55CREATE INDEX IF NOT EXISTS clinical_trials_status_type_idx  ON clinical_trials (overall_status, study_type);56CREATE INDEX IF NOT EXISTS cancer_aliases_norm_pattern_idx  ON cancer_aliases (normalized text_pattern_ops);57CREATE INDEX IF NOT EXISTS ingest_runs_connector_status_idx ON ingest_runs (connector_id, status, started_at DESC);58DROP INDEX IF EXISTS clinical_trials_status_updated_idx;   -- tried and rejected, see below59```6061### Measurements (cancerindex_a, PostgreSQL 17.9, Apple Silicon; 9 629 cancers, 17 218 hierarchy edges,6233 687 aliases, 45 045 genes, 126 107 trials, 278 132 trial_conditions, 11 968 CIViC items; EXPLAIN ANALYZE, warm cache)6364| Query shape | Before | After | Note |65|---|---|---|---|66| Gene-level counters: per-gene evidence counts over all genes (`counters.ts`), containment form + GIN | 42 557 ms (`@>` seq scan) / 25 963 ms (`= ANY`) | **55 ms** | `x = ANY(array_col)` cannot use GIN → counters rewritten to `gene_ids @> ARRAY[g.id]::text[]` (equivalent) |67| `refreshCounters()` end to end (`pnpm cix counters`, 9 510 cancers + 725 genes, two runs each) | 96.0 s / 96.0 s | **5.3 s / 6.6 s** | three `= ANY` full scans over 45 045 genes removed; the remaining time is the recursive descendants + trial_map ≈ 2.6 s |68| CIViC items for one gene (`gene_ids @> ARRAY[id]`) | 1.65 ms | 0.19 ms | API `genes.ts` still uses `= ANY` (1.9 ms, unchanged) — switch to `@>` when touching that route |69| Global active-interventional count (`overall_status = ANY(...) AND study_type`) | 18.3 ms | 1.1 ms | index-only scan on `clinical_trials_status_type_idx` |70| Alias lookup `cancers?q=` (exact OR prefix OR word LIKE) | 3.5 ms | 0.5 ms | BitmapOr over `_norm_idx` + `_norm_pattern_idx` + trigram |71| Alias search (exact OR prefix OR `%` similarity) | 48.2 ms | 3.7 ms | prefix branch now indexable under en_US collation |72| Recursive descendants of a top-level cancer (`descendantIds`) | 1.9 ms | 0.8 ms | unchanged plan (`cancer_hierarchy_uq` index-only); noise |73| Counters `trial_map` (all cancers × descendants → trials) | 2 561 ms | 2 644 ms | dominated by the recursive CTE + DISTINCT; `trial_conditions_cancer_trial_idx` gives an index-only probe per descendant but no net change at this size |74| CIViC evidence for a scope by status | 1.1 ms | 1.1 ms | `civic_evidence_cancer_idx` already sufficient |75| Trials for a scope, `EXISTS` semi-join + status + `ORDER BY … LIMIT 20` | 2.1 ms | **116 ms with** `(overall_status, last_update_posted_date)` → index rejected; 2 ms without | ordered-index-walk trap for small scopes |7677Trial scope queries re-measured with a representative scope (Malignant Central Nervous System78Neoplasm — the top-level cancer with the most mapped trial conditions in this database), without79(`DROP INDEX` inside a rolled-back transaction) and with `trial_conditions_cancer_trial_idx`:8081| Query shape (scope = CNS, 192 descendants) | Without | With |82|---|---|---|83| Trials for the scope, `EXISTS` semi-join + `overall_status = 'RECRUITING'` + `ORDER BY last_update_posted_date LIMIT 20` (API `trials.ts`) | 35.2 ms | 21.5 ms |84| Same, JOIN form with active-status list and `count(*) OVER()` (API `cancers.ts`) | 18.3 ms | 19.0 ms (noise) |85| Status facet for the scope (web trials page) | 19.3 ms | 16.9 ms |8687The gain is modest at 278 k conditions (index-only probe instead of heap fetches per descendant);88it grows with `trial_conditions` size and it removes the heap I/O on a cold cache.8990### Recommendations outside this change set (routes not touched here)9192- `apps/api/src/routes/genes.ts` / `drugs.ts`: replace `X = ANY(e.gene_ids|variant_ids|therapy_ids)`93  with `e.gene_ids @> ARRAY[X]::text[]` to use the new GIN indexes.94- Keep `ORDER BY last_update_posted_date` lists without a status-ordered index; if large scopes get95  slow, paginate with a keyset on `(last_update_posted_date, id)` inside the semi-join instead.96