# 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).

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 <id> / resolve <id>.

# 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.

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)

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:

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.