spb/cancerindex

1CREATE TABLE "audit_log" (2	"id" bigserial PRIMARY KEY NOT NULL,3	"actor" text NOT NULL,4	"action" text NOT NULL,5	"entity_type" text,6	"entity_id" text,7	"before" jsonb,8	"after" jsonb,9	"reason" text,10	"created_at" timestamp with time zone DEFAULT now() NOT NULL11);12--> statement-breakpoint13CREATE TABLE "change_events" (14	"id" bigserial PRIMARY KEY NOT NULL,15	"entity_type" text NOT NULL,16	"entity_id" text NOT NULL,17	"kind" text NOT NULL,18	"summary" text NOT NULL,19	"before" jsonb,20	"after" jsonb,21	"ingest_run_id" text,22	"created_at" timestamp with time zone DEFAULT now() NOT NULL23);24--> statement-breakpoint25CREATE TABLE "connector_cursors" (26	"connector_id" text PRIMARY KEY NOT NULL,27	"cursor" jsonb DEFAULT '{}'::jsonb NOT NULL,28	"last_success_at" timestamp with time zone,29	"last_attempt_at" timestamp with time zone,30	"paused" boolean DEFAULT false NOT NULL,31	"health" text DEFAULT 'unknown' NOT NULL,32	"health_detail" text,33	"updated_at" timestamp with time zone DEFAULT now() NOT NULL34);35--> statement-breakpoint36CREATE TABLE "connector_field_stats" (37	"id" bigserial PRIMARY KEY NOT NULL,38	"connector_id" text NOT NULL,39	"entity" text NOT NULL,40	"field" text NOT NULL,41	"types" text[] DEFAULT '{}' NOT NULL,42	"seen_count" integer DEFAULT 0 NOT NULL,43	"null_count" integer DEFAULT 0 NOT NULL,44	"first_seen_run" text,45	"last_seen_run" text,46	"updated_at" timestamp with time zone DEFAULT now() NOT NULL47);48--> statement-breakpoint49CREATE TABLE "entity_merges" (50	"id" bigserial PRIMARY KEY NOT NULL,51	"entity_type" text NOT NULL,52	"keep_id" text NOT NULL,53	"merge_id" text NOT NULL,54	"evidence" jsonb DEFAULT '{}'::jsonb NOT NULL,55	"status" text DEFAULT 'proposed' NOT NULL,56	"decided_by" text,57	"decided_at" timestamp with time zone,58	"created_at" timestamp with time zone DEFAULT now() NOT NULL59);60--> statement-breakpoint61CREATE TABLE "id_sequences" (62	"namespace" varchar(16) PRIMARY KEY NOT NULL,63	"next" bigint DEFAULT 1 NOT NULL64);65--> statement-breakpoint66CREATE TABLE "ingest_runs" (67	"id" text PRIMARY KEY NOT NULL,68	"connector_id" text NOT NULL,69	"source_id" varchar(32) NOT NULL,70	"mode" text DEFAULT 'incremental' NOT NULL,71	"status" text DEFAULT 'running' NOT NULL,72	"started_at" timestamp with time zone DEFAULT now() NOT NULL,73	"finished_at" timestamp with time zone,74	"duration_ms" integer,75	"records_fetched" integer DEFAULT 0 NOT NULL,76	"records_created" integer DEFAULT 0 NOT NULL,77	"records_updated" integer DEFAULT 0 NOT NULL,78	"records_unchanged" integer DEFAULT 0 NOT NULL,79	"records_rejected" integer DEFAULT 0 NOT NULL,80	"http_requests" integer DEFAULT 0 NOT NULL,81	"http_failures" integer DEFAULT 0 NOT NULL,82	"rate_limit_events" integer DEFAULT 0 NOT NULL,83	"validation_failures" integer DEFAULT 0 NOT NULL,84	"schema_drift" jsonb DEFAULT '[]'::jsonb NOT NULL,85	"cursor_before" jsonb,86	"cursor_after" jsonb,87	"error" text,88	"log" jsonb DEFAULT '[]'::jsonb NOT NULL,89	"dataset_version" text,90	"anomaly" text91);92--> statement-breakpoint93CREATE TABLE "provenance" (94	"id" bigserial PRIMARY KEY NOT NULL,95	"public_id" varchar(32),96	"source_id" varchar(32) NOT NULL,97	"source_record_id" text,98	"source_url" text,99	"dataset" text,100	"dataset_version" text,101	"publication_id" varchar(32),102	"pmid" text,103	"doi" text,104	"retrieved_at" timestamp with time zone NOT NULL,105	"published_at" text,106	"updated_at_source" text,107	"geography" text,108	"population" text,109	"cohort_size" integer,110	"methodology" text,111	"evidence_type" text NOT NULL,112	"access_level" text DEFAULT 'open' NOT NULL,113	"confidence" real,114	"license" text,115	"ingest_run_id" text,116	"created_at" timestamp with time zone DEFAULT now() NOT NULL117);118--> statement-breakpoint119CREATE TABLE "source_records" (120	"id" bigserial PRIMARY KEY NOT NULL,121	"source_id" varchar(32) NOT NULL,122	"entity_kind" text NOT NULL,123	"source_record_id" text NOT NULL,124	"payload_hash" text NOT NULL,125	"raw_path" text,126	"status" text DEFAULT 'active' NOT NULL,127	"first_seen_run" text,128	"last_seen_run" text,129	"retrieved_at" timestamp with time zone DEFAULT now() NOT NULL,130	"source_updated_at" timestamp with time zone,131	"canonical_type" text,132	"canonical_id" text,133	"created_at" timestamp with time zone DEFAULT now() NOT NULL,134	"updated_at" timestamp with time zone DEFAULT now() NOT NULL135);136--> statement-breakpoint137CREATE TABLE "sources" (138	"id" varchar(32) PRIMARY KEY NOT NULL,139	"slug" text NOT NULL,140	"name" text NOT NULL,141	"organization" text,142	"category" text NOT NULL,143	"description" text,144	"homepage" text,145	"docs_url" text,146	"terms_url" text,147	"access_type" text NOT NULL,148	"access_auth" text NOT NULL,149	"license" text,150	"license_status" text DEFAULT 'review' NOT NULL,151	"commercial_use" text DEFAULT 'unknown' NOT NULL,152	"redistribution" text DEFAULT 'unknown' NOT NULL,153	"attribution" text,154	"license_reviewed_at" timestamp with time zone,155	"approved_for_production" boolean DEFAULT false NOT NULL,156	"update_frequency" text,157	"supports_incremental" boolean DEFAULT false NOT NULL,158	"entities" text[] DEFAULT '{}' NOT NULL,159	"metrics" text[] DEFAULT '{}' NOT NULL,160	"rate_limit" text,161	"status" text DEFAULT 'planned' NOT NULL,162	"tier" integer DEFAULT 0 NOT NULL,163	"manifest" jsonb DEFAULT '{}'::jsonb NOT NULL,164	"created_at" timestamp with time zone DEFAULT now() NOT NULL,165	"updated_at" timestamp with time zone DEFAULT now() NOT NULL166);167--> statement-breakpoint168CREATE TABLE "unresolved_labels" (169	"id" bigserial PRIMARY KEY NOT NULL,170	"source_id" varchar(32) NOT NULL,171	"entity_kind" text NOT NULL,172	"source_text" text NOT NULL,173	"normalized" text NOT NULL,174	"context" jsonb DEFAULT '{}'::jsonb NOT NULL,175	"count" integer DEFAULT 1 NOT NULL,176	"status" text DEFAULT 'open' NOT NULL,177	"suggested_id" text,178	"suggested_match_type" text,179	"suggested_score" real,180	"resolved_id" text,181	"resolved_by" text,182	"created_at" timestamp with time zone DEFAULT now() NOT NULL,183	"updated_at" timestamp with time zone DEFAULT now() NOT NULL184);185--> statement-breakpoint186CREATE TABLE "anatomical_sites" (187	"id" varchar(32) PRIMARY KEY NOT NULL,188	"name" text NOT NULL,189	"slug" text NOT NULL,190	"ncit_code" text,191	"uberon_id" text,192	"parent_id" varchar(32),193	"system" text194);195--> statement-breakpoint196CREATE TABLE "cancer_aliases" (197	"id" bigserial PRIMARY KEY NOT NULL,198	"cancer_id" varchar(32) NOT NULL,199	"alias" text NOT NULL,200	"normalized" text NOT NULL,201	"alias_type" text DEFAULT 'synonym' NOT NULL,202	"source_id" varchar(32),203	"source_terminology" text,204	"language" text DEFAULT 'en' NOT NULL205);206--> statement-breakpoint207CREATE TABLE "cancer_anatomy" (208	"id" bigserial PRIMARY KEY NOT NULL,209	"cancer_id" varchar(32) NOT NULL,210	"site_id" varchar(32) NOT NULL,211	"relation" text DEFAULT 'primary' NOT NULL,212	"source_id" varchar(32)213);214--> statement-breakpoint215CREATE TABLE "cancer_codes" (216	"id" bigserial PRIMARY KEY NOT NULL,217	"cancer_id" varchar(32) NOT NULL,218	"system" text NOT NULL,219	"code" text NOT NULL,220	"match_type" text DEFAULT 'EXACT_IDENTIFIER' NOT NULL,221	"source_id" varchar(32),222	"valid_from" text,223	"valid_to" text224);225--> statement-breakpoint226CREATE TABLE "cancer_hierarchy" (227	"id" bigserial PRIMARY KEY NOT NULL,228	"parent_id" varchar(32) NOT NULL,229	"child_id" varchar(32) NOT NULL,230	"hierarchy_type" text NOT NULL,231	"source_id" varchar(32)232);233--> statement-breakpoint234CREATE TABLE "cancers" (235	"id" varchar(32) PRIMARY KEY NOT NULL,236	"slug" text NOT NULL,237	"canonical_name" text NOT NULL,238	"short_name" text,239	"entity_type" text DEFAULT 'cancer' NOT NULL,240	"malignant" boolean DEFAULT true NOT NULL,241	"solid_tumor" boolean DEFAULT true NOT NULL,242	"hematologic" boolean DEFAULT false NOT NULL,243	"pediatric_relevant" boolean DEFAULT false NOT NULL,244	"rare_cancer" boolean,245	"top_level" boolean DEFAULT false NOT NULL,246	"description" text,247	"description_provenance_id" integer,248	"primary_ncit_code" text,249	"primary_oncotree_code" text,250	"depth" integer DEFAULT 0 NOT NULL,251	"status" text DEFAULT 'active' NOT NULL,252	"merged_into" varchar(32),253	"deprecated_reason" text,254	"classification_version" text,255	"semantic_types" text[] DEFAULT '{}' NOT NULL,256	"created_at" timestamp with time zone DEFAULT now() NOT NULL,257	"updated_at" timestamp with time zone DEFAULT now() NOT NULL258);259--> statement-breakpoint260CREATE TABLE "cohort_definitions" (261	"id" bigserial PRIMARY KEY NOT NULL,262	"name" text NOT NULL,263	"cancer_id" varchar(32) NOT NULL,264	"biomarker_ids" text[] DEFAULT '{}' NOT NULL,265	"variant_ids" text[] DEFAULT '{}' NOT NULL,266	"stage" text,267	"attributes" jsonb DEFAULT '{}'::jsonb NOT NULL,268	"confidence" real,269	"created_at" timestamp with time zone DEFAULT now() NOT NULL270);271--> statement-breakpoint272CREATE TABLE "geographies" (273	"id" varchar(32) PRIMARY KEY NOT NULL,274	"slug" text NOT NULL,275	"name" text NOT NULL,276	"kind" text NOT NULL,277	"iso2" text,278	"iso3" text,279	"parent_id" varchar(32),280	"who_region" text,281	"population" integer,282	"population_year" integer283);284--> statement-breakpoint285CREATE TABLE "biomarkers" (286	"id" varchar(32) PRIMARY KEY NOT NULL,287	"slug" text NOT NULL,288	"name" text NOT NULL,289	"kind" text NOT NULL,290	"gene_id" varchar(32),291	"ncit_code" text,292	"description" text,293	"measurement" jsonb DEFAULT '{}'::jsonb NOT NULL,294	"created_at" timestamp with time zone DEFAULT now() NOT NULL,295	"updated_at" timestamp with time zone DEFAULT now() NOT NULL296);297--> statement-breakpoint298CREATE TABLE "cancer_gene_frequencies" (299	"id" bigserial PRIMARY KEY NOT NULL,300	"cohort_id" varchar(32) NOT NULL,301	"cancer_id" varchar(32),302	"gene_id" varchar(32),303	"gene_symbol" text NOT NULL,304	"alteration_type" text DEFAULT 'ssm' NOT NULL,305	"cases_affected" integer NOT NULL,306	"cases_profiled" integer NOT NULL,307	"frequency" real NOT NULL,308	"rank" integer,309	"data_release" text,310	"provenance_id" integer NOT NULL,311	"updated_at" timestamp with time zone DEFAULT now() NOT NULL312);313--> statement-breakpoint314CREATE TABLE "entity_embeddings" (315	"id" bigserial PRIMARY KEY NOT NULL,316	"entity_type" text NOT NULL,317	"entity_id" text NOT NULL,318	"model" text NOT NULL,319	"dimensions" integer NOT NULL,320	"text_hash" text NOT NULL,321	"embedding" text,322	"created_at" timestamp with time zone DEFAULT now() NOT NULL323);324--> statement-breakpoint325CREATE TABLE "gene_aliases" (326	"id" bigserial PRIMARY KEY NOT NULL,327	"gene_id" varchar(32) NOT NULL,328	"alias" text NOT NULL,329	"alias_type" text NOT NULL,330	"source_id" varchar(32)331);332--> statement-breakpoint333CREATE TABLE "genes" (334	"id" varchar(32) PRIMARY KEY NOT NULL,335	"hgnc_id" text,336	"symbol" text NOT NULL,337	"name" text,338	"locus_type" text,339	"locus_group" text,340	"location" text,341	"chromosome" text,342	"ensembl_gene_id" text,343	"ncbi_gene_id" text,344	"omim_ids" text[] DEFAULT '{}' NOT NULL,345	"uniprot_ids" text[] DEFAULT '{}' NOT NULL,346	"refseq_accession" text,347	"prev_symbols" text[] DEFAULT '{}' NOT NULL,348	"alias_symbols" text[] DEFAULT '{}' NOT NULL,349	"gene_families" text[] DEFAULT '{}' NOT NULL,350	"status" text DEFAULT 'Approved' NOT NULL,351	"is_cancer_gene" boolean DEFAULT false NOT NULL,352	"civic_gene_id" integer,353	"description" text,354	"created_at" timestamp with time zone DEFAULT now() NOT NULL,355	"updated_at" timestamp with time zone DEFAULT now() NOT NULL356);357--> statement-breakpoint358CREATE TABLE "genomic_cohorts" (359	"id" varchar(32) PRIMARY KEY NOT NULL,360	"source_id" varchar(32) NOT NULL,361	"study_id" text NOT NULL,362	"name" text NOT NULL,363	"program" text,364	"primary_sites" text[] DEFAULT '{}' NOT NULL,365	"disease_types" text[] DEFAULT '{}' NOT NULL,366	"cancer_id" varchar(32),367	"cancer_match_type" text,368	"case_count" integer,369	"cases_with_ssm" integer,370	"data_release" text,371	"access_level" text DEFAULT 'open' NOT NULL,372	"url" text,373	"provenance_id" integer,374	"updated_at" timestamp with time zone DEFAULT now() NOT NULL375);376--> statement-breakpoint377CREATE TABLE "variant_aliases" (378	"id" bigserial PRIMARY KEY NOT NULL,379	"variant_id" varchar(32) NOT NULL,380	"alias" text NOT NULL,381	"source_id" varchar(32)382);383--> statement-breakpoint384CREATE TABLE "variant_clinical_significance" (385	"id" bigserial PRIMARY KEY NOT NULL,386	"variant_id" varchar(32) NOT NULL,387	"clinvar_variation_id" text NOT NULL,388	"clinical_significance" text NOT NULL,389	"review_status" text,390	"star_rating" integer,391	"last_evaluated" text,392	"conditions" text[] DEFAULT '{}' NOT NULL,393	"condition_cancer_ids" text[] DEFAULT '{}' NOT NULL,394	"origin_simple" text,395	"number_submitters" integer,396	"provenance_id" integer NOT NULL,397	"ingest_run_id" text,398	"updated_at" timestamp with time zone DEFAULT now() NOT NULL399);400--> statement-breakpoint401CREATE TABLE "variants" (402	"id" varchar(32) PRIMARY KEY NOT NULL,403	"slug" text NOT NULL,404	"gene_id" varchar(32),405	"gene_symbol" text,406	"name" text NOT NULL,407	"variant_type" text,408	"hgvs_g" text,409	"hgvs_c" text,410	"hgvs_p" text,411	"assembly" text,412	"chromosome" text,413	"start" integer,414	"end" integer,415	"reference_bases" text,416	"alternate_bases" text,417	"coordinates" jsonb DEFAULT '[]'::jsonb NOT NULL,418	"clinvar_variation_id" text,419	"civic_variant_id" integer,420	"dbsnp_ids" text[] DEFAULT '{}' NOT NULL,421	"fusion_partners" text[] DEFAULT '{}' NOT NULL,422	"created_at" timestamp with time zone DEFAULT now() NOT NULL,423	"updated_at" timestamp with time zone DEFAULT now() NOT NULL424);425--> statement-breakpoint426CREATE TABLE "drug_aliases" (427	"id" bigserial PRIMARY KEY NOT NULL,428	"drug_id" varchar(32) NOT NULL,429	"alias" text NOT NULL,430	"normalized" text NOT NULL,431	"alias_type" text DEFAULT 'synonym' NOT NULL,432	"source_id" varchar(32)433);434--> statement-breakpoint435CREATE TABLE "drug_approvals" (436	"id" bigserial PRIMARY KEY NOT NULL,437	"drug_id" varchar(32) NOT NULL,438	"cancer_id" varchar(32),439	"biomarker_ids" text[] DEFAULT '{}' NOT NULL,440	"tumor_agnostic" boolean DEFAULT false NOT NULL,441	"jurisdiction" text NOT NULL,442	"authority" text NOT NULL,443	"indication" text NOT NULL,444	"line_of_therapy" text,445	"disease_stage" text,446	"approval_type" text,447	"accelerated" boolean,448	"conditional" boolean,449	"approval_date" text,450	"withdrawal_date" text,451	"status" text NOT NULL,452	"application_number" text,453	"source_id" varchar(32) NOT NULL,454	"provenance_id" integer NOT NULL,455	"raw" jsonb,456	"created_at" timestamp with time zone DEFAULT now() NOT NULL,457	"updated_at" timestamp with time zone DEFAULT now() NOT NULL458);459--> statement-breakpoint460CREATE TABLE "drugs" (461	"id" varchar(32) PRIMARY KEY NOT NULL,462	"slug" text NOT NULL,463	"name" text NOT NULL,464	"kind" text,465	"ncit_code" text,466	"chembl_id" text,467	"civic_therapy_id" integer,468	"drugbank_id" text,469	"pubchem_cid" text,470	"unii" text,471	"mechanism" text,472	"target_gene_ids" text[] DEFAULT '{}' NOT NULL,473	"development_status" text,474	"description" text,475	"created_at" timestamp with time zone DEFAULT now() NOT NULL,476	"updated_at" timestamp with time zone DEFAULT now() NOT NULL477);478--> statement-breakpoint479CREATE TABLE "treatment_regimens" (480	"id" varchar(32) PRIMARY KEY NOT NULL,481	"slug" text NOT NULL,482	"name" text NOT NULL,483	"component_drug_ids" text[] DEFAULT '{}' NOT NULL,484	"modality" text DEFAULT 'drug_combination' NOT NULL,485	"description" text,486	"created_at" timestamp with time zone DEFAULT now() NOT NULL487);488--> statement-breakpoint489CREATE TABLE "clinical_trials" (490	"id" varchar(32) PRIMARY KEY NOT NULL,491	"nct_id" text NOT NULL,492	"brief_title" text NOT NULL,493	"official_title" text,494	"acronym" text,495	"study_type" text,496	"phases" text[] DEFAULT '{}' NOT NULL,497	"overall_status" text,498	"why_stopped" text,499	"start_date" text,500	"primary_completion_date" text,501	"completion_date" text,502	"first_posted_date" text,503	"last_update_posted_date" text,504	"results_first_posted_date" text,505	"has_results" boolean DEFAULT false NOT NULL,506	"enrollment_count" integer,507	"enrollment_type" text,508	"lead_sponsor" text,509	"lead_sponsor_class" text,510	"collaborators" text[] DEFAULT '{}' NOT NULL,511	"conditions" text[] DEFAULT '{}' NOT NULL,512	"keywords" text[] DEFAULT '{}' NOT NULL,513	"interventions" jsonb DEFAULT '[]'::jsonb NOT NULL,514	"arms" jsonb DEFAULT '[]'::jsonb NOT NULL,515	"primary_outcomes" jsonb DEFAULT '[]'::jsonb NOT NULL,516	"secondary_outcomes" jsonb DEFAULT '[]'::jsonb NOT NULL,517	"eligibility" jsonb DEFAULT '{}'::jsonb NOT NULL,518	"sex" text,519	"minimum_age" text,520	"maximum_age" text,521	"countries" text[] DEFAULT '{}' NOT NULL,522	"locations_count" integer DEFAULT 0 NOT NULL,523	"references" jsonb DEFAULT '[]'::jsonb NOT NULL,524	"brief_summary" text,525	"is_oncology" boolean DEFAULT true NOT NULL,526	"source_record_id" integer,527	"ingest_run_id" text,528	"created_at" timestamp with time zone DEFAULT now() NOT NULL,529	"updated_at" timestamp with time zone DEFAULT now() NOT NULL530);531--> statement-breakpoint532CREATE TABLE "trial_conditions" (533	"id" bigserial PRIMARY KEY NOT NULL,534	"trial_id" varchar(32) NOT NULL,535	"condition_text" text NOT NULL,536	"normalized" text NOT NULL,537	"cancer_id" varchar(32),538	"match_type" text DEFAULT 'UNRESOLVED' NOT NULL,539	"confidence" real540);541--> statement-breakpoint542CREATE TABLE "trial_interventions" (543	"id" bigserial PRIMARY KEY NOT NULL,544	"trial_id" varchar(32) NOT NULL,545	"name" text NOT NULL,546	"normalized" text NOT NULL,547	"intervention_type" text,548	"drug_id" varchar(32),549	"match_type" text DEFAULT 'UNRESOLVED' NOT NULL550);551--> statement-breakpoint552CREATE TABLE "trial_locations" (553	"id" bigserial PRIMARY KEY NOT NULL,554	"trial_id" varchar(32) NOT NULL,555	"facility" text,556	"city" text,557	"state" text,558	"zip" text,559	"country" text,560	"status" text,561	"lat" real,562	"lng" real563);564--> statement-breakpoint565CREATE TABLE "trial_pulse" (566	"id" bigserial PRIMARY KEY NOT NULL,567	"day" date NOT NULL,568	"cancer_id" varchar(32),569	"phase" text,570	"new_trials" integer NOT NULL,571	"updated_at" timestamp with time zone DEFAULT now() NOT NULL572);573--> statement-breakpoint574CREATE TABLE "literature_counts" (575	"id" bigserial PRIMARY KEY NOT NULL,576	"cancer_id" varchar(32) NOT NULL,577	"window_key" text NOT NULL,578	"window_start" text,579	"window_end" text,580	"query" text NOT NULL,581	"count" integer NOT NULL,582	"provenance_id" integer NOT NULL,583	"updated_at" timestamp with time zone DEFAULT now() NOT NULL584);585--> statement-breakpoint586CREATE TABLE "publication_entity_edges" (587	"id" bigserial PRIMARY KEY NOT NULL,588	"publication_id" varchar(32) NOT NULL,589	"entity_type" text NOT NULL,590	"entity_id" text NOT NULL,591	"method" text NOT NULL,592	"confidence" real,593	"status" text DEFAULT 'candidate' NOT NULL,594	"source_id" varchar(32),595	"ingest_run_id" text,596	"created_at" timestamp with time zone DEFAULT now() NOT NULL597);598--> statement-breakpoint599CREATE TABLE "publications" (600	"id" varchar(32) PRIMARY KEY NOT NULL,601	"pmid" text,602	"doi" text,603	"pmcid" text,604	"title" text NOT NULL,605	"abstract" text,606	"journal" text,607	"journal_iso" text,608	"pub_date" text,609	"pub_year" integer,610	"publication_types" text[] DEFAULT '{}' NOT NULL,611	"mesh_terms" jsonb DEFAULT '[]'::jsonb NOT NULL,612	"authors" jsonb DEFAULT '[]'::jsonb NOT NULL,613	"language" text,614	"is_preprint" boolean DEFAULT false NOT NULL,615	"retracted" boolean DEFAULT false NOT NULL,616	"retraction_notice" text,617	"nct_ids" text[] DEFAULT '{}' NOT NULL,618	"cited_by_count" integer,619	"source_record_id" integer,620	"ingest_run_id" text,621	"created_at" timestamp with time zone DEFAULT now() NOT NULL,622	"updated_at" timestamp with time zone DEFAULT now() NOT NULL623);624--> statement-breakpoint625CREATE TABLE "civic_evidence_items" (626	"id" bigserial PRIMARY KEY NOT NULL,627	"civic_id" integer NOT NULL,628	"name" text,629	"molecular_profile_id" integer,630	"molecular_profile_name" text,631	"gene_symbols" text[] DEFAULT '{}' NOT NULL,632	"gene_ids" text[] DEFAULT '{}' NOT NULL,633	"variant_ids" text[] DEFAULT '{}' NOT NULL,634	"civic_variant_ids" integer[] DEFAULT '{}' NOT NULL,635	"disease_name" text,636	"doid" text,637	"cancer_id" varchar(32),638	"cancer_match_type" text,639	"therapy_names" text[] DEFAULT '{}' NOT NULL,640	"therapy_ids" text[] DEFAULT '{}' NOT NULL,641	"therapy_interaction_type" text,642	"evidence_type" text,643	"evidence_level" text,644	"evidence_direction" text,645	"significance" text,646	"evidence_rating" integer,647	"status" text,648	"description" text,649	"pmid" text,650	"source_citation" text,651	"phenotypes" text[] DEFAULT '{}' NOT NULL,652	"provenance_id" integer NOT NULL,653	"ingest_run_id" text,654	"updated_at" timestamp with time zone DEFAULT now() NOT NULL655);656--> statement-breakpoint657CREATE TABLE "knowledge_edges" (658	"id" bigserial PRIMARY KEY NOT NULL,659	"source_entity_type" text NOT NULL,660	"source_entity_id" text NOT NULL,661	"target_entity_type" text NOT NULL,662	"target_entity_id" text NOT NULL,663	"relationship_type" text NOT NULL,664	"cancer_context_ids" text[] DEFAULT '{}' NOT NULL,665	"predictive" boolean,666	"prognostic" boolean,667	"diagnostic" boolean,668	"predisposing" boolean,669	"direction" text,670	"evidence_level" text,671	"evidence_score" real,672	"evidence_category" text DEFAULT 'curated_evidence' NOT NULL,673	"status" text DEFAULT 'active' NOT NULL,674	"source_id" varchar(32) NOT NULL,675	"source_record_id" text,676	"provenance_ids" integer[] DEFAULT '{}' NOT NULL,677	"support_count" integer DEFAULT 1 NOT NULL,678	"first_seen_at" timestamp with time zone DEFAULT now() NOT NULL,679	"last_seen_at" timestamp with time zone DEFAULT now() NOT NULL680);681--> statement-breakpoint682CREATE TABLE "risk_factors" (683	"id" bigserial PRIMARY KEY NOT NULL,684	"slug" text NOT NULL,685	"name" text NOT NULL,686	"kind" text NOT NULL,687	"classification_authority" text,688	"classification" text,689	"description" text,690	"created_at" timestamp with time zone DEFAULT now() NOT NULL691);692--> statement-breakpoint693CREATE TABLE "epidemiology_observations" (694	"id" bigserial PRIMARY KEY NOT NULL,695	"cancer_id" varchar(32) NOT NULL,696	"geography_id" varchar(32) NOT NULL,697	"year" integer NOT NULL,698	"year_end" integer,699	"sex" text DEFAULT 'all' NOT NULL,700	"age_group" text DEFAULT 'all' NOT NULL,701	"metric" text NOT NULL,702	"value" double precision NOT NULL,703	"unit" text NOT NULL,704	"lower_ci" double precision,705	"upper_ci" double precision,706	"standard_population" text,707	"estimate_type" text DEFAULT 'observed' NOT NULL,708	"site_definition" text,709	"source_id" varchar(32) NOT NULL,710	"provenance_id" integer NOT NULL,711	"ingest_run_id" text,712	"updated_at" timestamp with time zone DEFAULT now() NOT NULL713);714--> statement-breakpoint715CREATE TABLE "survival_observations" (716	"id" bigserial PRIMARY KEY NOT NULL,717	"cancer_id" varchar(32) NOT NULL,718	"geography_id" varchar(32),719	"stage" text,720	"staging_system" text,721	"sex" text DEFAULT 'all' NOT NULL,722	"age_group" text DEFAULT 'all' NOT NULL,723	"diagnosis_period" text,724	"survival_type" text NOT NULL,725	"duration_months" integer NOT NULL,726	"probability" real,727	"median_months" real,728	"cohort_size" integer,729	"lower_ci" real,730	"upper_ci" real,731	"method" text,732	"source_id" varchar(32) NOT NULL,733	"provenance_id" integer NOT NULL,734	"ingest_run_id" text,735	"updated_at" timestamp with time zone DEFAULT now() NOT NULL736);737--> statement-breakpoint738CREATE TABLE "ai_answers" (739	"id" bigserial PRIMARY KEY NOT NULL,740	"kind" text NOT NULL,741	"subject_id" text,742	"question_hash" text NOT NULL,743	"question" text,744	"answer" jsonb NOT NULL,745	"model" text NOT NULL,746	"prompt_version" text NOT NULL,747	"source_snapshot" jsonb DEFAULT '{}'::jsonb NOT NULL,748	"data_as_of" timestamp with time zone NOT NULL,749	"created_at" timestamp with time zone DEFAULT now() NOT NULL750);751--> statement-breakpoint752CREATE TABLE "api_keys" (753	"id" bigserial PRIMARY KEY NOT NULL,754	"key_hash" text NOT NULL,755	"prefix" text NOT NULL,756	"label" text,757	"owner_email" text,758	"tier" text DEFAULT 'free' NOT NULL,759	"rate_limit_per_minute" integer DEFAULT 60 NOT NULL,760	"active" boolean DEFAULT true NOT NULL,761	"last_used_at" timestamp with time zone,762	"created_at" timestamp with time zone DEFAULT now() NOT NULL763);764--> statement-breakpoint765CREATE TABLE "entity_counters" (766	"id" bigserial PRIMARY KEY NOT NULL,767	"entity_type" text NOT NULL,768	"entity_id" text NOT NULL,769	"trial_count" integer DEFAULT 0 NOT NULL,770	"active_trial_count" integer DEFAULT 0 NOT NULL,771	"recruiting_trial_count" integer DEFAULT 0 NOT NULL,772	"phase3_trial_count" integer DEFAULT 0 NOT NULL,773	"publication_count" integer DEFAULT 0 NOT NULL,774	"publication_count_5y" integer DEFAULT 0 NOT NULL,775	"publication_count_12m" integer DEFAULT 0 NOT NULL,776	"gene_count" integer DEFAULT 0 NOT NULL,777	"variant_count" integer DEFAULT 0 NOT NULL,778	"drug_count" integer DEFAULT 0 NOT NULL,779	"approved_drug_count" integer DEFAULT 0 NOT NULL,780	"evidence_count" integer DEFAULT 0 NOT NULL,781	"cohort_count" integer DEFAULT 0 NOT NULL,782	"subtype_count" integer DEFAULT 0 NOT NULL,783	"descendant_count" integer DEFAULT 0 NOT NULL,784	"epidemiology_obs_count" integer DEFAULT 0 NOT NULL,785	"survival_obs_count" integer DEFAULT 0 NOT NULL,786	"completeness" jsonb DEFAULT '{}'::jsonb NOT NULL,787	"updated_at" timestamp with time zone DEFAULT now() NOT NULL788);789--> statement-breakpoint790CREATE TABLE "metric_definitions" (791	"id" varchar(32) PRIMARY KEY NOT NULL,792	"slug" text NOT NULL,793	"name" text NOT NULL,794	"description" text NOT NULL,795	"formula" text NOT NULL,796	"formula_version" text NOT NULL,797	"unit" text NOT NULL,798	"higher_is_worse" boolean,799	"aggregation" text,800	"valid_dimensions" text[] DEFAULT '{}' NOT NULL,801	"source_slugs" text[] DEFAULT '{}' NOT NULL,802	"category" text NOT NULL,803	"eligibility" jsonb DEFAULT '{}'::jsonb NOT NULL,804	"experimental" boolean DEFAULT false NOT NULL,805	"created_at" timestamp with time zone DEFAULT now() NOT NULL,806	"updated_at" timestamp with time zone DEFAULT now() NOT NULL807);808--> statement-breakpoint809CREATE TABLE "ranking_snapshots" (810	"id" bigserial PRIMARY KEY NOT NULL,811	"metric_id" varchar(32) NOT NULL,812	"metric_slug" text NOT NULL,813	"scope_key" text NOT NULL,814	"geography" text DEFAULT 'WORLD' NOT NULL,815	"sex" text DEFAULT 'all' NOT NULL,816	"age_group" text DEFAULT 'all' NOT NULL,817	"year" integer,818	"entity_level" text DEFAULT 'top' NOT NULL,819	"formula_version" text NOT NULL,820	"eligible_entities" integer NOT NULL,821	"inputs_hash" text NOT NULL,822	"source_ids" text[] DEFAULT '{}' NOT NULL,823	"is_current" boolean DEFAULT true NOT NULL,824	"generated_at" timestamp with time zone DEFAULT now() NOT NULL825);826--> statement-breakpoint827CREATE TABLE "rankings" (828	"id" bigserial PRIMARY KEY NOT NULL,829	"snapshot_id" integer NOT NULL,830	"metric_slug" text NOT NULL,831	"scope_key" text NOT NULL,832	"cancer_id" varchar(32) NOT NULL,833	"rank" integer NOT NULL,834	"eligible_entities" integer NOT NULL,835	"percentile" real NOT NULL,836	"value" double precision NOT NULL,837	"unit" text NOT NULL,838	"confidence" text DEFAULT 'MEDIUM' NOT NULL,839	"inputs" jsonb DEFAULT '{}'::jsonb NOT NULL,840	"breakdown" jsonb,841	"previous_rank" integer,842	"generated_at" timestamp with time zone DEFAULT now() NOT NULL843);844--> statement-breakpoint845CREATE INDEX "change_events_entity_idx" ON "change_events" USING btree ("entity_type","entity_id","created_at");--> statement-breakpoint846CREATE UNIQUE INDEX "connector_field_stats_uq" ON "connector_field_stats" USING btree ("connector_id","entity","field");--> statement-breakpoint847CREATE INDEX "ingest_runs_connector_idx" ON "ingest_runs" USING btree ("connector_id","started_at");--> statement-breakpoint848CREATE INDEX "provenance_source_idx" ON "provenance" USING btree ("source_id","source_record_id");--> statement-breakpoint849CREATE INDEX "provenance_pmid_idx" ON "provenance" USING btree ("pmid");--> statement-breakpoint850CREATE UNIQUE INDEX "source_records_uq" ON "source_records" USING btree ("source_id","entity_kind","source_record_id");--> statement-breakpoint851CREATE INDEX "source_records_canonical_idx" ON "source_records" USING btree ("canonical_type","canonical_id");--> statement-breakpoint852CREATE UNIQUE INDEX "sources_slug_uq" ON "sources" USING btree ("slug");--> statement-breakpoint853CREATE UNIQUE INDEX "unresolved_labels_uq" ON "unresolved_labels" USING btree ("source_id","entity_kind","normalized");--> statement-breakpoint854CREATE INDEX "unresolved_labels_count_idx" ON "unresolved_labels" USING btree ("status","count");--> statement-breakpoint855CREATE UNIQUE INDEX "anatomical_sites_slug_uq" ON "anatomical_sites" USING btree ("slug");--> statement-breakpoint856CREATE UNIQUE INDEX "cancer_aliases_uq" ON "cancer_aliases" USING btree ("cancer_id","normalized","alias_type");--> statement-breakpoint857CREATE INDEX "cancer_aliases_norm_idx" ON "cancer_aliases" USING btree ("normalized");--> statement-breakpoint858CREATE UNIQUE INDEX "cancer_anatomy_uq" ON "cancer_anatomy" USING btree ("cancer_id","site_id","relation");--> statement-breakpoint859CREATE UNIQUE INDEX "cancer_codes_uq" ON "cancer_codes" USING btree ("cancer_id","system","code");--> statement-breakpoint860CREATE INDEX "cancer_codes_lookup_idx" ON "cancer_codes" USING btree ("system","code");--> statement-breakpoint861CREATE UNIQUE INDEX "cancer_hierarchy_uq" ON "cancer_hierarchy" USING btree ("parent_id","child_id","hierarchy_type");--> statement-breakpoint862CREATE INDEX "cancer_hierarchy_child_idx" ON "cancer_hierarchy" USING btree ("child_id");--> statement-breakpoint863CREATE UNIQUE INDEX "cancers_slug_uq" ON "cancers" USING btree ("slug");--> statement-breakpoint864CREATE UNIQUE INDEX "cancers_ncit_uq" ON "cancers" USING btree ("primary_ncit_code");--> statement-breakpoint865CREATE INDEX "cancers_name_idx" ON "cancers" USING btree ("canonical_name");--> statement-breakpoint866CREATE INDEX "cancers_type_idx" ON "cancers" USING btree ("entity_type","malignant","top_level");--> statement-breakpoint867CREATE UNIQUE INDEX "geographies_slug_uq" ON "geographies" USING btree ("slug");--> statement-breakpoint868CREATE INDEX "geographies_iso3_idx" ON "geographies" USING btree ("iso3");--> statement-breakpoint869CREATE UNIQUE INDEX "biomarkers_slug_uq" ON "biomarkers" USING btree ("slug");--> statement-breakpoint870CREATE UNIQUE INDEX "cancer_gene_freq_uq" ON "cancer_gene_frequencies" USING btree ("cohort_id","gene_symbol","alteration_type");--> statement-breakpoint871CREATE INDEX "cancer_gene_freq_cancer_idx" ON "cancer_gene_frequencies" USING btree ("cancer_id","frequency");--> statement-breakpoint872CREATE INDEX "cancer_gene_freq_gene_idx" ON "cancer_gene_frequencies" USING btree ("gene_id");--> statement-breakpoint873CREATE UNIQUE INDEX "entity_embeddings_uq" ON "entity_embeddings" USING btree ("entity_type","entity_id","model");--> statement-breakpoint874CREATE UNIQUE INDEX "gene_aliases_uq" ON "gene_aliases" USING btree ("gene_id","alias","alias_type");--> statement-breakpoint875CREATE INDEX "gene_aliases_alias_idx" ON "gene_aliases" USING btree ("alias");--> statement-breakpoint876CREATE UNIQUE INDEX "genes_symbol_uq" ON "genes" USING btree ("symbol");--> statement-breakpoint877CREATE UNIQUE INDEX "genes_hgnc_uq" ON "genes" USING btree ("hgnc_id");--> statement-breakpoint878CREATE INDEX "genes_ensembl_idx" ON "genes" USING btree ("ensembl_gene_id");--> statement-breakpoint879CREATE INDEX "genes_ncbi_idx" ON "genes" USING btree ("ncbi_gene_id");--> statement-breakpoint880CREATE UNIQUE INDEX "genomic_cohorts_uq" ON "genomic_cohorts" USING btree ("source_id","study_id");--> statement-breakpoint881CREATE INDEX "genomic_cohorts_cancer_idx" ON "genomic_cohorts" USING btree ("cancer_id");--> statement-breakpoint882CREATE UNIQUE INDEX "variant_aliases_uq" ON "variant_aliases" USING btree ("variant_id","alias");--> statement-breakpoint883CREATE UNIQUE INDEX "variant_clinsig_uq" ON "variant_clinical_significance" USING btree ("clinvar_variation_id");--> statement-breakpoint884CREATE UNIQUE INDEX "variants_slug_uq" ON "variants" USING btree ("slug");--> statement-breakpoint885CREATE INDEX "variants_gene_idx" ON "variants" USING btree ("gene_id");--> statement-breakpoint886CREATE INDEX "variants_clinvar_idx" ON "variants" USING btree ("clinvar_variation_id");--> statement-breakpoint887CREATE INDEX "variants_civic_idx" ON "variants" USING btree ("civic_variant_id");--> statement-breakpoint888CREATE UNIQUE INDEX "drug_aliases_uq" ON "drug_aliases" USING btree ("drug_id","normalized","alias_type");--> statement-breakpoint889CREATE INDEX "drug_aliases_norm_idx" ON "drug_aliases" USING btree ("normalized");--> statement-breakpoint890CREATE INDEX "drug_approvals_drug_idx" ON "drug_approvals" USING btree ("drug_id");--> statement-breakpoint891CREATE INDEX "drug_approvals_cancer_idx" ON "drug_approvals" USING btree ("cancer_id");--> statement-breakpoint892CREATE UNIQUE INDEX "drugs_slug_uq" ON "drugs" USING btree ("slug");--> statement-breakpoint893CREATE INDEX "drugs_ncit_idx" ON "drugs" USING btree ("ncit_code");--> statement-breakpoint894CREATE INDEX "drugs_civic_idx" ON "drugs" USING btree ("civic_therapy_id");--> statement-breakpoint895CREATE INDEX "drugs_chembl_idx" ON "drugs" USING btree ("chembl_id");--> statement-breakpoint896CREATE UNIQUE INDEX "treatment_regimens_slug_uq" ON "treatment_regimens" USING btree ("slug");--> statement-breakpoint897CREATE UNIQUE INDEX "clinical_trials_nct_uq" ON "clinical_trials" USING btree ("nct_id");--> statement-breakpoint898CREATE INDEX "clinical_trials_status_idx" ON "clinical_trials" USING btree ("overall_status");--> statement-breakpoint899CREATE INDEX "clinical_trials_updated_idx" ON "clinical_trials" USING btree ("last_update_posted_date");--> statement-breakpoint900CREATE INDEX "clinical_trials_sponsor_idx" ON "clinical_trials" USING btree ("lead_sponsor");--> statement-breakpoint901CREATE UNIQUE INDEX "trial_conditions_uq" ON "trial_conditions" USING btree ("trial_id","normalized");--> statement-breakpoint902CREATE INDEX "trial_conditions_cancer_idx" ON "trial_conditions" USING btree ("cancer_id");--> statement-breakpoint903CREATE INDEX "trial_conditions_norm_idx" ON "trial_conditions" USING btree ("normalized");--> statement-breakpoint904CREATE UNIQUE INDEX "trial_interventions_uq" ON "trial_interventions" USING btree ("trial_id","normalized");--> statement-breakpoint905CREATE INDEX "trial_interventions_drug_idx" ON "trial_interventions" USING btree ("drug_id");--> statement-breakpoint906CREATE INDEX "trial_locations_trial_idx" ON "trial_locations" USING btree ("trial_id");--> statement-breakpoint907CREATE INDEX "trial_locations_country_idx" ON "trial_locations" USING btree ("country");--> statement-breakpoint908CREATE UNIQUE INDEX "trial_pulse_uq" ON "trial_pulse" USING btree ("day","cancer_id","phase");--> statement-breakpoint909CREATE UNIQUE INDEX "literature_counts_uq" ON "literature_counts" USING btree ("cancer_id","window_key");--> statement-breakpoint910CREATE INDEX "literature_counts_window_idx" ON "literature_counts" USING btree ("window_key","count");--> statement-breakpoint911CREATE UNIQUE INDEX "pub_entity_edges_uq" ON "publication_entity_edges" USING btree ("publication_id","entity_type","entity_id","method");--> statement-breakpoint912CREATE INDEX "pub_entity_edges_entity_idx" ON "publication_entity_edges" USING btree ("entity_type","entity_id");--> statement-breakpoint913CREATE UNIQUE INDEX "publications_pmid_uq" ON "publications" USING btree ("pmid");--> statement-breakpoint914CREATE INDEX "publications_doi_idx" ON "publications" USING btree ("doi");--> statement-breakpoint915CREATE INDEX "publications_year_idx" ON "publications" USING btree ("pub_year");--> statement-breakpoint916CREATE INDEX "publications_retracted_idx" ON "publications" USING btree ("retracted");--> statement-breakpoint917CREATE UNIQUE INDEX "civic_evidence_uq" ON "civic_evidence_items" USING btree ("civic_id");--> statement-breakpoint918CREATE INDEX "civic_evidence_cancer_idx" ON "civic_evidence_items" USING btree ("cancer_id");--> statement-breakpoint919CREATE INDEX "civic_evidence_gene_idx" ON "civic_evidence_items" USING btree ("gene_symbols");--> statement-breakpoint920CREATE UNIQUE INDEX "knowledge_edges_uq" ON "knowledge_edges" USING btree ("source_entity_type","source_entity_id","target_entity_type","target_entity_id","relationship_type","source_id","source_record_id");--> statement-breakpoint921CREATE INDEX "knowledge_edges_source_idx" ON "knowledge_edges" USING btree ("source_entity_type","source_entity_id","relationship_type");--> statement-breakpoint922CREATE INDEX "knowledge_edges_target_idx" ON "knowledge_edges" USING btree ("target_entity_type","target_entity_id","relationship_type");--> statement-breakpoint923CREATE UNIQUE INDEX "risk_factors_slug_uq" ON "risk_factors" USING btree ("slug");--> statement-breakpoint924CREATE UNIQUE INDEX "epi_obs_uq" ON "epidemiology_observations" USING btree ("cancer_id","geography_id","year","sex","age_group","metric","source_id","site_definition");--> statement-breakpoint925CREATE INDEX "epi_obs_lookup_idx" ON "epidemiology_observations" USING btree ("metric","geography_id","year","sex");--> statement-breakpoint926CREATE INDEX "epi_obs_cancer_idx" ON "epidemiology_observations" USING btree ("cancer_id","metric");--> statement-breakpoint927CREATE INDEX "survival_obs_cancer_idx" ON "survival_observations" USING btree ("cancer_id","survival_type","duration_months");--> statement-breakpoint928CREATE UNIQUE INDEX "ai_answers_uq" ON "ai_answers" USING btree ("kind","question_hash","prompt_version");--> statement-breakpoint929CREATE UNIQUE INDEX "api_keys_hash_uq" ON "api_keys" USING btree ("key_hash");--> statement-breakpoint930CREATE UNIQUE INDEX "entity_counters_uq" ON "entity_counters" USING btree ("entity_type","entity_id");--> statement-breakpoint931CREATE INDEX "entity_counters_trials_idx" ON "entity_counters" USING btree ("entity_type","active_trial_count");--> statement-breakpoint932CREATE UNIQUE INDEX "metric_definitions_slug_uq" ON "metric_definitions" USING btree ("slug");--> statement-breakpoint933CREATE INDEX "ranking_snapshots_lookup_idx" ON "ranking_snapshots" USING btree ("metric_slug","scope_key","is_current");--> statement-breakpoint934CREATE UNIQUE INDEX "rankings_uq" ON "rankings" USING btree ("snapshot_id","cancer_id");--> statement-breakpoint935CREATE INDEX "rankings_cancer_idx" ON "rankings" USING btree ("cancer_id","metric_slug");--> statement-breakpoint936CREATE INDEX "rankings_lookup_idx" ON "rankings" USING btree ("metric_slug","scope_key","rank");