# Schema changes — ops layer (2026-09-08) Developed with `drizzle-kit push` on `cancerindex_a` (CLAUDE.md conventions: parallel agents do not commit migration files). **The integrator generates the migration** with `pnpm db:generate` after merging; the expected DDL is listed here so the generated file can be reviewed against it. ## 1. New table `system_alerts` (CLAUDE.md §170) Schema: `packages/database/src/schema/ext-ops.ts` (exported from `schema/index.ts`). Helpers: `packages/database/src/alerts.ts` (`raiseAlert`, `raiseAlertSafe`, `resolveAlerts`, `listAlerts`). ```sql CREATE TABLE "system_alerts" ( "id" bigserial PRIMARY KEY NOT NULL, "kind" text NOT NULL, -- connector_failure | connector_aborted | anomaly | schema_drift | source_failing | source_stale | … "severity" text DEFAULT 'warn' NOT NULL, -- info | warn | critical "connector_id" text, "message" text NOT NULL, "detail" jsonb DEFAULT '{}'::jsonb NOT NULL, "first_seen_at" timestamp with time zone DEFAULT now() NOT NULL, "last_seen_at" timestamp with time zone DEFAULT now() NOT NULL, "count" integer DEFAULT 1 NOT NULL, "status" text DEFAULT 'open' NOT NULL, -- open | acknowledged | resolved "resolved_at" timestamp with time zone ); CREATE INDEX "system_alerts_status_idx" ON "system_alerts" USING btree ("status","kind","connector_id"); CREATE INDEX "system_alerts_seen_idx" ON "system_alerts" USING btree ("last_seen_at"); ``` Deduplication (kind + connector_id + message among open/acknowledged rows) is done in `raiseAlert`, not by a unique index, so historical resolved rows with the same message can coexist. Writers: `runConnector` (failed / anomaly / aborted / schema drift, resolves on success), the worker health probe (`source_failing`, `source_stale`). Readers: `pnpm cix alerts`, `pnpm cix doctor`, `GET /v1/admin/alerts`. Manual triage: `pnpm cix alerts ack ` / `resolve `. ## 2. Manifest field `checkpointEvery` `ConnectorManifest.checkpointEvery` (zod, default 2000) — stored inside `sources.manifest` jsonb; no DDL. Drives the automatic mid-run cursor checkpoint (docs/connectors/README.md). ## 3. Performance indexes (raw SQL, `packages/database/src/migrate.ts` → `createPerformanceIndexes`) Idempotent `CREATE INDEX IF NOT EXISTS`, executed after the SQL migrations by `pnpm db:migrate` (already the pattern for the trigram indexes). They are *not* part of the drizzle schema on purpose (GIN / opclass indexes are not expressible there), so `drizzle-kit generate` will not emit them; the post-migration block recreates them idempotently after every `pnpm db:migrate`, which also runs in the mld `post_sync` hook on deploy. ```sql CREATE INDEX IF NOT EXISTS civic_evidence_gene_ids_gin ON civic_evidence_items USING gin (gene_ids); CREATE INDEX IF NOT EXISTS civic_evidence_variant_ids_gin ON civic_evidence_items USING gin (variant_ids); CREATE INDEX IF NOT EXISTS civic_evidence_therapy_ids_gin ON civic_evidence_items USING gin (therapy_ids); CREATE INDEX IF NOT EXISTS trial_conditions_cancer_trial_idx ON trial_conditions (cancer_id, trial_id); CREATE INDEX IF NOT EXISTS clinical_trials_status_type_idx ON clinical_trials (overall_status, study_type); CREATE INDEX IF NOT EXISTS cancer_aliases_norm_pattern_idx ON cancer_aliases (normalized text_pattern_ops); CREATE INDEX IF NOT EXISTS ingest_runs_connector_status_idx ON ingest_runs (connector_id, status, started_at DESC); DROP INDEX IF EXISTS clinical_trials_status_updated_idx; -- tried and rejected, see below ``` ### Measurements (cancerindex_a, PostgreSQL 17.9, Apple Silicon; 9 629 cancers, 17 218 hierarchy edges, 33 687 aliases, 45 045 genes, 126 107 trials, 278 132 trial_conditions, 11 968 CIViC items; EXPLAIN ANALYZE, warm cache) | Query shape | Before | After | Note | |---|---|---|---| | 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) | | `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 | | 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 | | 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` | | Alias lookup `cancers?q=` (exact OR prefix OR word LIKE) | 3.5 ms | 0.5 ms | BitmapOr over `_norm_idx` + `_norm_pattern_idx` + trigram | | Alias search (exact OR prefix OR `%` similarity) | 48.2 ms | 3.7 ms | prefix branch now indexable under en_US collation | | Recursive descendants of a top-level cancer (`descendantIds`) | 1.9 ms | 0.8 ms | unchanged plan (`cancer_hierarchy_uq` index-only); noise | | 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 | | CIViC evidence for a scope by status | 1.1 ms | 1.1 ms | `civic_evidence_cancer_idx` already sufficient | | 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 | Trial scope queries re-measured with a representative scope (Malignant Central Nervous System Neoplasm — the top-level cancer with the most mapped trial conditions in this database), without (`DROP INDEX` inside a rolled-back transaction) and with `trial_conditions_cancer_trial_idx`: | Query shape (scope = CNS, 192 descendants) | Without | With | |---|---|---| | 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 | | Same, JOIN form with active-status list and `count(*) OVER()` (API `cancers.ts`) | 18.3 ms | 19.0 ms (noise) | | Status facet for the scope (web trials page) | 19.3 ms | 16.9 ms | The gain is modest at 278 k conditions (index-only probe instead of heap fetches per descendant); it grows with `trial_conditions` size and it removes the heap I/O on a cold cache. ### Recommendations outside this change set (routes not touched here) - `apps/api/src/routes/genes.ts` / `drugs.ts`: replace `X = ANY(e.gene_ids|variant_ids|therapy_ids)` with `e.gene_ids @> ARRAY[X]::text[]` to use the new GIN indexes. - Keep `ORDER BY last_update_posted_date` lists without a status-ordered index; if large scopes get slow, paginate with a keyset on `(last_update_posted_date, id)` inside the semi-join instead.