CREATE TABLE "audit_log" ( "id" bigserial PRIMARY KEY NOT NULL, "actor" text NOT NULL, "action" text NOT NULL, "entity_type" text, "entity_id" text, "before" jsonb, "after" jsonb, "reason" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "change_events" ( "id" bigserial PRIMARY KEY NOT NULL, "entity_type" text NOT NULL, "entity_id" text NOT NULL, "kind" text NOT NULL, "summary" text NOT NULL, "before" jsonb, "after" jsonb, "ingest_run_id" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "connector_cursors" ( "connector_id" text PRIMARY KEY NOT NULL, "cursor" jsonb DEFAULT '{}'::jsonb NOT NULL, "last_success_at" timestamp with time zone, "last_attempt_at" timestamp with time zone, "paused" boolean DEFAULT false NOT NULL, "health" text DEFAULT 'unknown' NOT NULL, "health_detail" text, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "connector_field_stats" ( "id" bigserial PRIMARY KEY NOT NULL, "connector_id" text NOT NULL, "entity" text NOT NULL, "field" text NOT NULL, "types" text[] DEFAULT '{}' NOT NULL, "seen_count" integer DEFAULT 0 NOT NULL, "null_count" integer DEFAULT 0 NOT NULL, "first_seen_run" text, "last_seen_run" text, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "entity_merges" ( "id" bigserial PRIMARY KEY NOT NULL, "entity_type" text NOT NULL, "keep_id" text NOT NULL, "merge_id" text NOT NULL, "evidence" jsonb DEFAULT '{}'::jsonb NOT NULL, "status" text DEFAULT 'proposed' NOT NULL, "decided_by" text, "decided_at" timestamp with time zone, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "id_sequences" ( "namespace" varchar(16) PRIMARY KEY NOT NULL, "next" bigint DEFAULT 1 NOT NULL ); --> statement-breakpoint CREATE TABLE "ingest_runs" ( "id" text PRIMARY KEY NOT NULL, "connector_id" text NOT NULL, "source_id" varchar(32) NOT NULL, "mode" text DEFAULT 'incremental' NOT NULL, "status" text DEFAULT 'running' NOT NULL, "started_at" timestamp with time zone DEFAULT now() NOT NULL, "finished_at" timestamp with time zone, "duration_ms" integer, "records_fetched" integer DEFAULT 0 NOT NULL, "records_created" integer DEFAULT 0 NOT NULL, "records_updated" integer DEFAULT 0 NOT NULL, "records_unchanged" integer DEFAULT 0 NOT NULL, "records_rejected" integer DEFAULT 0 NOT NULL, "http_requests" integer DEFAULT 0 NOT NULL, "http_failures" integer DEFAULT 0 NOT NULL, "rate_limit_events" integer DEFAULT 0 NOT NULL, "validation_failures" integer DEFAULT 0 NOT NULL, "schema_drift" jsonb DEFAULT '[]'::jsonb NOT NULL, "cursor_before" jsonb, "cursor_after" jsonb, "error" text, "log" jsonb DEFAULT '[]'::jsonb NOT NULL, "dataset_version" text, "anomaly" text ); --> statement-breakpoint CREATE TABLE "provenance" ( "id" bigserial PRIMARY KEY NOT NULL, "public_id" varchar(32), "source_id" varchar(32) NOT NULL, "source_record_id" text, "source_url" text, "dataset" text, "dataset_version" text, "publication_id" varchar(32), "pmid" text, "doi" text, "retrieved_at" timestamp with time zone NOT NULL, "published_at" text, "updated_at_source" text, "geography" text, "population" text, "cohort_size" integer, "methodology" text, "evidence_type" text NOT NULL, "access_level" text DEFAULT 'open' NOT NULL, "confidence" real, "license" text, "ingest_run_id" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "source_records" ( "id" bigserial PRIMARY KEY NOT NULL, "source_id" varchar(32) NOT NULL, "entity_kind" text NOT NULL, "source_record_id" text NOT NULL, "payload_hash" text NOT NULL, "raw_path" text, "status" text DEFAULT 'active' NOT NULL, "first_seen_run" text, "last_seen_run" text, "retrieved_at" timestamp with time zone DEFAULT now() NOT NULL, "source_updated_at" timestamp with time zone, "canonical_type" text, "canonical_id" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "sources" ( "id" varchar(32) PRIMARY KEY NOT NULL, "slug" text NOT NULL, "name" text NOT NULL, "organization" text, "category" text NOT NULL, "description" text, "homepage" text, "docs_url" text, "terms_url" text, "access_type" text NOT NULL, "access_auth" text NOT NULL, "license" text, "license_status" text DEFAULT 'review' NOT NULL, "commercial_use" text DEFAULT 'unknown' NOT NULL, "redistribution" text DEFAULT 'unknown' NOT NULL, "attribution" text, "license_reviewed_at" timestamp with time zone, "approved_for_production" boolean DEFAULT false NOT NULL, "update_frequency" text, "supports_incremental" boolean DEFAULT false NOT NULL, "entities" text[] DEFAULT '{}' NOT NULL, "metrics" text[] DEFAULT '{}' NOT NULL, "rate_limit" text, "status" text DEFAULT 'planned' NOT NULL, "tier" integer DEFAULT 0 NOT NULL, "manifest" jsonb DEFAULT '{}'::jsonb NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "unresolved_labels" ( "id" bigserial PRIMARY KEY NOT NULL, "source_id" varchar(32) NOT NULL, "entity_kind" text NOT NULL, "source_text" text NOT NULL, "normalized" text NOT NULL, "context" jsonb DEFAULT '{}'::jsonb NOT NULL, "count" integer DEFAULT 1 NOT NULL, "status" text DEFAULT 'open' NOT NULL, "suggested_id" text, "suggested_match_type" text, "suggested_score" real, "resolved_id" text, "resolved_by" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "anatomical_sites" ( "id" varchar(32) PRIMARY KEY NOT NULL, "name" text NOT NULL, "slug" text NOT NULL, "ncit_code" text, "uberon_id" text, "parent_id" varchar(32), "system" text ); --> statement-breakpoint CREATE TABLE "cancer_aliases" ( "id" bigserial PRIMARY KEY NOT NULL, "cancer_id" varchar(32) NOT NULL, "alias" text NOT NULL, "normalized" text NOT NULL, "alias_type" text DEFAULT 'synonym' NOT NULL, "source_id" varchar(32), "source_terminology" text, "language" text DEFAULT 'en' NOT NULL ); --> statement-breakpoint CREATE TABLE "cancer_anatomy" ( "id" bigserial PRIMARY KEY NOT NULL, "cancer_id" varchar(32) NOT NULL, "site_id" varchar(32) NOT NULL, "relation" text DEFAULT 'primary' NOT NULL, "source_id" varchar(32) ); --> statement-breakpoint CREATE TABLE "cancer_codes" ( "id" bigserial PRIMARY KEY NOT NULL, "cancer_id" varchar(32) NOT NULL, "system" text NOT NULL, "code" text NOT NULL, "match_type" text DEFAULT 'EXACT_IDENTIFIER' NOT NULL, "source_id" varchar(32), "valid_from" text, "valid_to" text ); --> statement-breakpoint CREATE TABLE "cancer_hierarchy" ( "id" bigserial PRIMARY KEY NOT NULL, "parent_id" varchar(32) NOT NULL, "child_id" varchar(32) NOT NULL, "hierarchy_type" text NOT NULL, "source_id" varchar(32) ); --> statement-breakpoint CREATE TABLE "cancers" ( "id" varchar(32) PRIMARY KEY NOT NULL, "slug" text NOT NULL, "canonical_name" text NOT NULL, "short_name" text, "entity_type" text DEFAULT 'cancer' NOT NULL, "malignant" boolean DEFAULT true NOT NULL, "solid_tumor" boolean DEFAULT true NOT NULL, "hematologic" boolean DEFAULT false NOT NULL, "pediatric_relevant" boolean DEFAULT false NOT NULL, "rare_cancer" boolean, "top_level" boolean DEFAULT false NOT NULL, "description" text, "description_provenance_id" integer, "primary_ncit_code" text, "primary_oncotree_code" text, "depth" integer DEFAULT 0 NOT NULL, "status" text DEFAULT 'active' NOT NULL, "merged_into" varchar(32), "deprecated_reason" text, "classification_version" text, "semantic_types" text[] DEFAULT '{}' NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "cohort_definitions" ( "id" bigserial PRIMARY KEY NOT NULL, "name" text NOT NULL, "cancer_id" varchar(32) NOT NULL, "biomarker_ids" text[] DEFAULT '{}' NOT NULL, "variant_ids" text[] DEFAULT '{}' NOT NULL, "stage" text, "attributes" jsonb DEFAULT '{}'::jsonb NOT NULL, "confidence" real, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "geographies" ( "id" varchar(32) PRIMARY KEY NOT NULL, "slug" text NOT NULL, "name" text NOT NULL, "kind" text NOT NULL, "iso2" text, "iso3" text, "parent_id" varchar(32), "who_region" text, "population" integer, "population_year" integer ); --> statement-breakpoint CREATE TABLE "biomarkers" ( "id" varchar(32) PRIMARY KEY NOT NULL, "slug" text NOT NULL, "name" text NOT NULL, "kind" text NOT NULL, "gene_id" varchar(32), "ncit_code" text, "description" text, "measurement" jsonb DEFAULT '{}'::jsonb NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "cancer_gene_frequencies" ( "id" bigserial PRIMARY KEY NOT NULL, "cohort_id" varchar(32) NOT NULL, "cancer_id" varchar(32), "gene_id" varchar(32), "gene_symbol" text NOT NULL, "alteration_type" text DEFAULT 'ssm' NOT NULL, "cases_affected" integer NOT NULL, "cases_profiled" integer NOT NULL, "frequency" real NOT NULL, "rank" integer, "data_release" text, "provenance_id" integer NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "entity_embeddings" ( "id" bigserial PRIMARY KEY NOT NULL, "entity_type" text NOT NULL, "entity_id" text NOT NULL, "model" text NOT NULL, "dimensions" integer NOT NULL, "text_hash" text NOT NULL, "embedding" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "gene_aliases" ( "id" bigserial PRIMARY KEY NOT NULL, "gene_id" varchar(32) NOT NULL, "alias" text NOT NULL, "alias_type" text NOT NULL, "source_id" varchar(32) ); --> statement-breakpoint CREATE TABLE "genes" ( "id" varchar(32) PRIMARY KEY NOT NULL, "hgnc_id" text, "symbol" text NOT NULL, "name" text, "locus_type" text, "locus_group" text, "location" text, "chromosome" text, "ensembl_gene_id" text, "ncbi_gene_id" text, "omim_ids" text[] DEFAULT '{}' NOT NULL, "uniprot_ids" text[] DEFAULT '{}' NOT NULL, "refseq_accession" text, "prev_symbols" text[] DEFAULT '{}' NOT NULL, "alias_symbols" text[] DEFAULT '{}' NOT NULL, "gene_families" text[] DEFAULT '{}' NOT NULL, "status" text DEFAULT 'Approved' NOT NULL, "is_cancer_gene" boolean DEFAULT false NOT NULL, "civic_gene_id" integer, "description" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "genomic_cohorts" ( "id" varchar(32) PRIMARY KEY NOT NULL, "source_id" varchar(32) NOT NULL, "study_id" text NOT NULL, "name" text NOT NULL, "program" text, "primary_sites" text[] DEFAULT '{}' NOT NULL, "disease_types" text[] DEFAULT '{}' NOT NULL, "cancer_id" varchar(32), "cancer_match_type" text, "case_count" integer, "cases_with_ssm" integer, "data_release" text, "access_level" text DEFAULT 'open' NOT NULL, "url" text, "provenance_id" integer, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "variant_aliases" ( "id" bigserial PRIMARY KEY NOT NULL, "variant_id" varchar(32) NOT NULL, "alias" text NOT NULL, "source_id" varchar(32) ); --> statement-breakpoint CREATE TABLE "variant_clinical_significance" ( "id" bigserial PRIMARY KEY NOT NULL, "variant_id" varchar(32) NOT NULL, "clinvar_variation_id" text NOT NULL, "clinical_significance" text NOT NULL, "review_status" text, "star_rating" integer, "last_evaluated" text, "conditions" text[] DEFAULT '{}' NOT NULL, "condition_cancer_ids" text[] DEFAULT '{}' NOT NULL, "origin_simple" text, "number_submitters" integer, "provenance_id" integer NOT NULL, "ingest_run_id" text, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "variants" ( "id" varchar(32) PRIMARY KEY NOT NULL, "slug" text NOT NULL, "gene_id" varchar(32), "gene_symbol" text, "name" text NOT NULL, "variant_type" text, "hgvs_g" text, "hgvs_c" text, "hgvs_p" text, "assembly" text, "chromosome" text, "start" integer, "end" integer, "reference_bases" text, "alternate_bases" text, "coordinates" jsonb DEFAULT '[]'::jsonb NOT NULL, "clinvar_variation_id" text, "civic_variant_id" integer, "dbsnp_ids" text[] DEFAULT '{}' NOT NULL, "fusion_partners" text[] DEFAULT '{}' NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "drug_aliases" ( "id" bigserial PRIMARY KEY NOT NULL, "drug_id" varchar(32) NOT NULL, "alias" text NOT NULL, "normalized" text NOT NULL, "alias_type" text DEFAULT 'synonym' NOT NULL, "source_id" varchar(32) ); --> statement-breakpoint CREATE TABLE "drug_approvals" ( "id" bigserial PRIMARY KEY NOT NULL, "drug_id" varchar(32) NOT NULL, "cancer_id" varchar(32), "biomarker_ids" text[] DEFAULT '{}' NOT NULL, "tumor_agnostic" boolean DEFAULT false NOT NULL, "jurisdiction" text NOT NULL, "authority" text NOT NULL, "indication" text NOT NULL, "line_of_therapy" text, "disease_stage" text, "approval_type" text, "accelerated" boolean, "conditional" boolean, "approval_date" text, "withdrawal_date" text, "status" text NOT NULL, "application_number" text, "source_id" varchar(32) NOT NULL, "provenance_id" integer NOT NULL, "raw" jsonb, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "drugs" ( "id" varchar(32) PRIMARY KEY NOT NULL, "slug" text NOT NULL, "name" text NOT NULL, "kind" text, "ncit_code" text, "chembl_id" text, "civic_therapy_id" integer, "drugbank_id" text, "pubchem_cid" text, "unii" text, "mechanism" text, "target_gene_ids" text[] DEFAULT '{}' NOT NULL, "development_status" text, "description" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "treatment_regimens" ( "id" varchar(32) PRIMARY KEY NOT NULL, "slug" text NOT NULL, "name" text NOT NULL, "component_drug_ids" text[] DEFAULT '{}' NOT NULL, "modality" text DEFAULT 'drug_combination' NOT NULL, "description" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "clinical_trials" ( "id" varchar(32) PRIMARY KEY NOT NULL, "nct_id" text NOT NULL, "brief_title" text NOT NULL, "official_title" text, "acronym" text, "study_type" text, "phases" text[] DEFAULT '{}' NOT NULL, "overall_status" text, "why_stopped" text, "start_date" text, "primary_completion_date" text, "completion_date" text, "first_posted_date" text, "last_update_posted_date" text, "results_first_posted_date" text, "has_results" boolean DEFAULT false NOT NULL, "enrollment_count" integer, "enrollment_type" text, "lead_sponsor" text, "lead_sponsor_class" text, "collaborators" text[] DEFAULT '{}' NOT NULL, "conditions" text[] DEFAULT '{}' NOT NULL, "keywords" text[] DEFAULT '{}' NOT NULL, "interventions" jsonb DEFAULT '[]'::jsonb NOT NULL, "arms" jsonb DEFAULT '[]'::jsonb NOT NULL, "primary_outcomes" jsonb DEFAULT '[]'::jsonb NOT NULL, "secondary_outcomes" jsonb DEFAULT '[]'::jsonb NOT NULL, "eligibility" jsonb DEFAULT '{}'::jsonb NOT NULL, "sex" text, "minimum_age" text, "maximum_age" text, "countries" text[] DEFAULT '{}' NOT NULL, "locations_count" integer DEFAULT 0 NOT NULL, "references" jsonb DEFAULT '[]'::jsonb NOT NULL, "brief_summary" text, "is_oncology" boolean DEFAULT true NOT NULL, "source_record_id" integer, "ingest_run_id" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "trial_conditions" ( "id" bigserial PRIMARY KEY NOT NULL, "trial_id" varchar(32) NOT NULL, "condition_text" text NOT NULL, "normalized" text NOT NULL, "cancer_id" varchar(32), "match_type" text DEFAULT 'UNRESOLVED' NOT NULL, "confidence" real ); --> statement-breakpoint CREATE TABLE "trial_interventions" ( "id" bigserial PRIMARY KEY NOT NULL, "trial_id" varchar(32) NOT NULL, "name" text NOT NULL, "normalized" text NOT NULL, "intervention_type" text, "drug_id" varchar(32), "match_type" text DEFAULT 'UNRESOLVED' NOT NULL ); --> statement-breakpoint CREATE TABLE "trial_locations" ( "id" bigserial PRIMARY KEY NOT NULL, "trial_id" varchar(32) NOT NULL, "facility" text, "city" text, "state" text, "zip" text, "country" text, "status" text, "lat" real, "lng" real ); --> statement-breakpoint CREATE TABLE "trial_pulse" ( "id" bigserial PRIMARY KEY NOT NULL, "day" date NOT NULL, "cancer_id" varchar(32), "phase" text, "new_trials" integer NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "literature_counts" ( "id" bigserial PRIMARY KEY NOT NULL, "cancer_id" varchar(32) NOT NULL, "window_key" text NOT NULL, "window_start" text, "window_end" text, "query" text NOT NULL, "count" integer NOT NULL, "provenance_id" integer NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "publication_entity_edges" ( "id" bigserial PRIMARY KEY NOT NULL, "publication_id" varchar(32) NOT NULL, "entity_type" text NOT NULL, "entity_id" text NOT NULL, "method" text NOT NULL, "confidence" real, "status" text DEFAULT 'candidate' NOT NULL, "source_id" varchar(32), "ingest_run_id" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "publications" ( "id" varchar(32) PRIMARY KEY NOT NULL, "pmid" text, "doi" text, "pmcid" text, "title" text NOT NULL, "abstract" text, "journal" text, "journal_iso" text, "pub_date" text, "pub_year" integer, "publication_types" text[] DEFAULT '{}' NOT NULL, "mesh_terms" jsonb DEFAULT '[]'::jsonb NOT NULL, "authors" jsonb DEFAULT '[]'::jsonb NOT NULL, "language" text, "is_preprint" boolean DEFAULT false NOT NULL, "retracted" boolean DEFAULT false NOT NULL, "retraction_notice" text, "nct_ids" text[] DEFAULT '{}' NOT NULL, "cited_by_count" integer, "source_record_id" integer, "ingest_run_id" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "civic_evidence_items" ( "id" bigserial PRIMARY KEY NOT NULL, "civic_id" integer NOT NULL, "name" text, "molecular_profile_id" integer, "molecular_profile_name" text, "gene_symbols" text[] DEFAULT '{}' NOT NULL, "gene_ids" text[] DEFAULT '{}' NOT NULL, "variant_ids" text[] DEFAULT '{}' NOT NULL, "civic_variant_ids" integer[] DEFAULT '{}' NOT NULL, "disease_name" text, "doid" text, "cancer_id" varchar(32), "cancer_match_type" text, "therapy_names" text[] DEFAULT '{}' NOT NULL, "therapy_ids" text[] DEFAULT '{}' NOT NULL, "therapy_interaction_type" text, "evidence_type" text, "evidence_level" text, "evidence_direction" text, "significance" text, "evidence_rating" integer, "status" text, "description" text, "pmid" text, "source_citation" text, "phenotypes" text[] DEFAULT '{}' NOT NULL, "provenance_id" integer NOT NULL, "ingest_run_id" text, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "knowledge_edges" ( "id" bigserial PRIMARY KEY NOT NULL, "source_entity_type" text NOT NULL, "source_entity_id" text NOT NULL, "target_entity_type" text NOT NULL, "target_entity_id" text NOT NULL, "relationship_type" text NOT NULL, "cancer_context_ids" text[] DEFAULT '{}' NOT NULL, "predictive" boolean, "prognostic" boolean, "diagnostic" boolean, "predisposing" boolean, "direction" text, "evidence_level" text, "evidence_score" real, "evidence_category" text DEFAULT 'curated_evidence' NOT NULL, "status" text DEFAULT 'active' NOT NULL, "source_id" varchar(32) NOT NULL, "source_record_id" text, "provenance_ids" integer[] DEFAULT '{}' NOT NULL, "support_count" integer DEFAULT 1 NOT NULL, "first_seen_at" timestamp with time zone DEFAULT now() NOT NULL, "last_seen_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "risk_factors" ( "id" bigserial PRIMARY KEY NOT NULL, "slug" text NOT NULL, "name" text NOT NULL, "kind" text NOT NULL, "classification_authority" text, "classification" text, "description" text, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "epidemiology_observations" ( "id" bigserial PRIMARY KEY NOT NULL, "cancer_id" varchar(32) NOT NULL, "geography_id" varchar(32) NOT NULL, "year" integer NOT NULL, "year_end" integer, "sex" text DEFAULT 'all' NOT NULL, "age_group" text DEFAULT 'all' NOT NULL, "metric" text NOT NULL, "value" double precision NOT NULL, "unit" text NOT NULL, "lower_ci" double precision, "upper_ci" double precision, "standard_population" text, "estimate_type" text DEFAULT 'observed' NOT NULL, "site_definition" text, "source_id" varchar(32) NOT NULL, "provenance_id" integer NOT NULL, "ingest_run_id" text, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "survival_observations" ( "id" bigserial PRIMARY KEY NOT NULL, "cancer_id" varchar(32) NOT NULL, "geography_id" varchar(32), "stage" text, "staging_system" text, "sex" text DEFAULT 'all' NOT NULL, "age_group" text DEFAULT 'all' NOT NULL, "diagnosis_period" text, "survival_type" text NOT NULL, "duration_months" integer NOT NULL, "probability" real, "median_months" real, "cohort_size" integer, "lower_ci" real, "upper_ci" real, "method" text, "source_id" varchar(32) NOT NULL, "provenance_id" integer NOT NULL, "ingest_run_id" text, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "ai_answers" ( "id" bigserial PRIMARY KEY NOT NULL, "kind" text NOT NULL, "subject_id" text, "question_hash" text NOT NULL, "question" text, "answer" jsonb NOT NULL, "model" text NOT NULL, "prompt_version" text NOT NULL, "source_snapshot" jsonb DEFAULT '{}'::jsonb NOT NULL, "data_as_of" timestamp with time zone NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "api_keys" ( "id" bigserial PRIMARY KEY NOT NULL, "key_hash" text NOT NULL, "prefix" text NOT NULL, "label" text, "owner_email" text, "tier" text DEFAULT 'free' NOT NULL, "rate_limit_per_minute" integer DEFAULT 60 NOT NULL, "active" boolean DEFAULT true NOT NULL, "last_used_at" timestamp with time zone, "created_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "entity_counters" ( "id" bigserial PRIMARY KEY NOT NULL, "entity_type" text NOT NULL, "entity_id" text NOT NULL, "trial_count" integer DEFAULT 0 NOT NULL, "active_trial_count" integer DEFAULT 0 NOT NULL, "recruiting_trial_count" integer DEFAULT 0 NOT NULL, "phase3_trial_count" integer DEFAULT 0 NOT NULL, "publication_count" integer DEFAULT 0 NOT NULL, "publication_count_5y" integer DEFAULT 0 NOT NULL, "publication_count_12m" integer DEFAULT 0 NOT NULL, "gene_count" integer DEFAULT 0 NOT NULL, "variant_count" integer DEFAULT 0 NOT NULL, "drug_count" integer DEFAULT 0 NOT NULL, "approved_drug_count" integer DEFAULT 0 NOT NULL, "evidence_count" integer DEFAULT 0 NOT NULL, "cohort_count" integer DEFAULT 0 NOT NULL, "subtype_count" integer DEFAULT 0 NOT NULL, "descendant_count" integer DEFAULT 0 NOT NULL, "epidemiology_obs_count" integer DEFAULT 0 NOT NULL, "survival_obs_count" integer DEFAULT 0 NOT NULL, "completeness" jsonb DEFAULT '{}'::jsonb NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "metric_definitions" ( "id" varchar(32) PRIMARY KEY NOT NULL, "slug" text NOT NULL, "name" text NOT NULL, "description" text NOT NULL, "formula" text NOT NULL, "formula_version" text NOT NULL, "unit" text NOT NULL, "higher_is_worse" boolean, "aggregation" text, "valid_dimensions" text[] DEFAULT '{}' NOT NULL, "source_slugs" text[] DEFAULT '{}' NOT NULL, "category" text NOT NULL, "eligibility" jsonb DEFAULT '{}'::jsonb NOT NULL, "experimental" boolean DEFAULT false NOT NULL, "created_at" timestamp with time zone DEFAULT now() NOT NULL, "updated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "ranking_snapshots" ( "id" bigserial PRIMARY KEY NOT NULL, "metric_id" varchar(32) NOT NULL, "metric_slug" text NOT NULL, "scope_key" text NOT NULL, "geography" text DEFAULT 'WORLD' NOT NULL, "sex" text DEFAULT 'all' NOT NULL, "age_group" text DEFAULT 'all' NOT NULL, "year" integer, "entity_level" text DEFAULT 'top' NOT NULL, "formula_version" text NOT NULL, "eligible_entities" integer NOT NULL, "inputs_hash" text NOT NULL, "source_ids" text[] DEFAULT '{}' NOT NULL, "is_current" boolean DEFAULT true NOT NULL, "generated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "rankings" ( "id" bigserial PRIMARY KEY NOT NULL, "snapshot_id" integer NOT NULL, "metric_slug" text NOT NULL, "scope_key" text NOT NULL, "cancer_id" varchar(32) NOT NULL, "rank" integer NOT NULL, "eligible_entities" integer NOT NULL, "percentile" real NOT NULL, "value" double precision NOT NULL, "unit" text NOT NULL, "confidence" text DEFAULT 'MEDIUM' NOT NULL, "inputs" jsonb DEFAULT '{}'::jsonb NOT NULL, "breakdown" jsonb, "previous_rank" integer, "generated_at" timestamp with time zone DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE INDEX "change_events_entity_idx" ON "change_events" USING btree ("entity_type","entity_id","created_at");--> statement-breakpoint CREATE UNIQUE INDEX "connector_field_stats_uq" ON "connector_field_stats" USING btree ("connector_id","entity","field");--> statement-breakpoint CREATE INDEX "ingest_runs_connector_idx" ON "ingest_runs" USING btree ("connector_id","started_at");--> statement-breakpoint CREATE INDEX "provenance_source_idx" ON "provenance" USING btree ("source_id","source_record_id");--> statement-breakpoint CREATE INDEX "provenance_pmid_idx" ON "provenance" USING btree ("pmid");--> statement-breakpoint CREATE UNIQUE INDEX "source_records_uq" ON "source_records" USING btree ("source_id","entity_kind","source_record_id");--> statement-breakpoint CREATE INDEX "source_records_canonical_idx" ON "source_records" USING btree ("canonical_type","canonical_id");--> statement-breakpoint CREATE UNIQUE INDEX "sources_slug_uq" ON "sources" USING btree ("slug");--> statement-breakpoint CREATE UNIQUE INDEX "unresolved_labels_uq" ON "unresolved_labels" USING btree ("source_id","entity_kind","normalized");--> statement-breakpoint CREATE INDEX "unresolved_labels_count_idx" ON "unresolved_labels" USING btree ("status","count");--> statement-breakpoint CREATE UNIQUE INDEX "anatomical_sites_slug_uq" ON "anatomical_sites" USING btree ("slug");--> statement-breakpoint CREATE UNIQUE INDEX "cancer_aliases_uq" ON "cancer_aliases" USING btree ("cancer_id","normalized","alias_type");--> statement-breakpoint CREATE INDEX "cancer_aliases_norm_idx" ON "cancer_aliases" USING btree ("normalized");--> statement-breakpoint CREATE UNIQUE INDEX "cancer_anatomy_uq" ON "cancer_anatomy" USING btree ("cancer_id","site_id","relation");--> statement-breakpoint CREATE UNIQUE INDEX "cancer_codes_uq" ON "cancer_codes" USING btree ("cancer_id","system","code");--> statement-breakpoint CREATE INDEX "cancer_codes_lookup_idx" ON "cancer_codes" USING btree ("system","code");--> statement-breakpoint CREATE UNIQUE INDEX "cancer_hierarchy_uq" ON "cancer_hierarchy" USING btree ("parent_id","child_id","hierarchy_type");--> statement-breakpoint CREATE INDEX "cancer_hierarchy_child_idx" ON "cancer_hierarchy" USING btree ("child_id");--> statement-breakpoint CREATE UNIQUE INDEX "cancers_slug_uq" ON "cancers" USING btree ("slug");--> statement-breakpoint CREATE UNIQUE INDEX "cancers_ncit_uq" ON "cancers" USING btree ("primary_ncit_code");--> statement-breakpoint CREATE INDEX "cancers_name_idx" ON "cancers" USING btree ("canonical_name");--> statement-breakpoint CREATE INDEX "cancers_type_idx" ON "cancers" USING btree ("entity_type","malignant","top_level");--> statement-breakpoint CREATE UNIQUE INDEX "geographies_slug_uq" ON "geographies" USING btree ("slug");--> statement-breakpoint CREATE INDEX "geographies_iso3_idx" ON "geographies" USING btree ("iso3");--> statement-breakpoint CREATE UNIQUE INDEX "biomarkers_slug_uq" ON "biomarkers" USING btree ("slug");--> statement-breakpoint CREATE UNIQUE INDEX "cancer_gene_freq_uq" ON "cancer_gene_frequencies" USING btree ("cohort_id","gene_symbol","alteration_type");--> statement-breakpoint CREATE INDEX "cancer_gene_freq_cancer_idx" ON "cancer_gene_frequencies" USING btree ("cancer_id","frequency");--> statement-breakpoint CREATE INDEX "cancer_gene_freq_gene_idx" ON "cancer_gene_frequencies" USING btree ("gene_id");--> statement-breakpoint CREATE UNIQUE INDEX "entity_embeddings_uq" ON "entity_embeddings" USING btree ("entity_type","entity_id","model");--> statement-breakpoint CREATE UNIQUE INDEX "gene_aliases_uq" ON "gene_aliases" USING btree ("gene_id","alias","alias_type");--> statement-breakpoint CREATE INDEX "gene_aliases_alias_idx" ON "gene_aliases" USING btree ("alias");--> statement-breakpoint CREATE UNIQUE INDEX "genes_symbol_uq" ON "genes" USING btree ("symbol");--> statement-breakpoint CREATE UNIQUE INDEX "genes_hgnc_uq" ON "genes" USING btree ("hgnc_id");--> statement-breakpoint CREATE INDEX "genes_ensembl_idx" ON "genes" USING btree ("ensembl_gene_id");--> statement-breakpoint CREATE INDEX "genes_ncbi_idx" ON "genes" USING btree ("ncbi_gene_id");--> statement-breakpoint CREATE UNIQUE INDEX "genomic_cohorts_uq" ON "genomic_cohorts" USING btree ("source_id","study_id");--> statement-breakpoint CREATE INDEX "genomic_cohorts_cancer_idx" ON "genomic_cohorts" USING btree ("cancer_id");--> statement-breakpoint CREATE UNIQUE INDEX "variant_aliases_uq" ON "variant_aliases" USING btree ("variant_id","alias");--> statement-breakpoint CREATE UNIQUE INDEX "variant_clinsig_uq" ON "variant_clinical_significance" USING btree ("clinvar_variation_id");--> statement-breakpoint CREATE UNIQUE INDEX "variants_slug_uq" ON "variants" USING btree ("slug");--> statement-breakpoint CREATE INDEX "variants_gene_idx" ON "variants" USING btree ("gene_id");--> statement-breakpoint CREATE INDEX "variants_clinvar_idx" ON "variants" USING btree ("clinvar_variation_id");--> statement-breakpoint CREATE INDEX "variants_civic_idx" ON "variants" USING btree ("civic_variant_id");--> statement-breakpoint CREATE UNIQUE INDEX "drug_aliases_uq" ON "drug_aliases" USING btree ("drug_id","normalized","alias_type");--> statement-breakpoint CREATE INDEX "drug_aliases_norm_idx" ON "drug_aliases" USING btree ("normalized");--> statement-breakpoint CREATE INDEX "drug_approvals_drug_idx" ON "drug_approvals" USING btree ("drug_id");--> statement-breakpoint CREATE INDEX "drug_approvals_cancer_idx" ON "drug_approvals" USING btree ("cancer_id");--> statement-breakpoint CREATE UNIQUE INDEX "drugs_slug_uq" ON "drugs" USING btree ("slug");--> statement-breakpoint CREATE INDEX "drugs_ncit_idx" ON "drugs" USING btree ("ncit_code");--> statement-breakpoint CREATE INDEX "drugs_civic_idx" ON "drugs" USING btree ("civic_therapy_id");--> statement-breakpoint CREATE INDEX "drugs_chembl_idx" ON "drugs" USING btree ("chembl_id");--> statement-breakpoint CREATE UNIQUE INDEX "treatment_regimens_slug_uq" ON "treatment_regimens" USING btree ("slug");--> statement-breakpoint CREATE UNIQUE INDEX "clinical_trials_nct_uq" ON "clinical_trials" USING btree ("nct_id");--> statement-breakpoint CREATE INDEX "clinical_trials_status_idx" ON "clinical_trials" USING btree ("overall_status");--> statement-breakpoint CREATE INDEX "clinical_trials_updated_idx" ON "clinical_trials" USING btree ("last_update_posted_date");--> statement-breakpoint CREATE INDEX "clinical_trials_sponsor_idx" ON "clinical_trials" USING btree ("lead_sponsor");--> statement-breakpoint CREATE UNIQUE INDEX "trial_conditions_uq" ON "trial_conditions" USING btree ("trial_id","normalized");--> statement-breakpoint CREATE INDEX "trial_conditions_cancer_idx" ON "trial_conditions" USING btree ("cancer_id");--> statement-breakpoint CREATE INDEX "trial_conditions_norm_idx" ON "trial_conditions" USING btree ("normalized");--> statement-breakpoint CREATE UNIQUE INDEX "trial_interventions_uq" ON "trial_interventions" USING btree ("trial_id","normalized");--> statement-breakpoint CREATE INDEX "trial_interventions_drug_idx" ON "trial_interventions" USING btree ("drug_id");--> statement-breakpoint CREATE INDEX "trial_locations_trial_idx" ON "trial_locations" USING btree ("trial_id");--> statement-breakpoint CREATE INDEX "trial_locations_country_idx" ON "trial_locations" USING btree ("country");--> statement-breakpoint CREATE UNIQUE INDEX "trial_pulse_uq" ON "trial_pulse" USING btree ("day","cancer_id","phase");--> statement-breakpoint CREATE UNIQUE INDEX "literature_counts_uq" ON "literature_counts" USING btree ("cancer_id","window_key");--> statement-breakpoint CREATE INDEX "literature_counts_window_idx" ON "literature_counts" USING btree ("window_key","count");--> statement-breakpoint CREATE UNIQUE INDEX "pub_entity_edges_uq" ON "publication_entity_edges" USING btree ("publication_id","entity_type","entity_id","method");--> statement-breakpoint CREATE INDEX "pub_entity_edges_entity_idx" ON "publication_entity_edges" USING btree ("entity_type","entity_id");--> statement-breakpoint CREATE UNIQUE INDEX "publications_pmid_uq" ON "publications" USING btree ("pmid");--> statement-breakpoint CREATE INDEX "publications_doi_idx" ON "publications" USING btree ("doi");--> statement-breakpoint CREATE INDEX "publications_year_idx" ON "publications" USING btree ("pub_year");--> statement-breakpoint CREATE INDEX "publications_retracted_idx" ON "publications" USING btree ("retracted");--> statement-breakpoint CREATE UNIQUE INDEX "civic_evidence_uq" ON "civic_evidence_items" USING btree ("civic_id");--> statement-breakpoint CREATE INDEX "civic_evidence_cancer_idx" ON "civic_evidence_items" USING btree ("cancer_id");--> statement-breakpoint CREATE INDEX "civic_evidence_gene_idx" ON "civic_evidence_items" USING btree ("gene_symbols");--> statement-breakpoint CREATE 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-breakpoint CREATE INDEX "knowledge_edges_source_idx" ON "knowledge_edges" USING btree ("source_entity_type","source_entity_id","relationship_type");--> statement-breakpoint CREATE INDEX "knowledge_edges_target_idx" ON "knowledge_edges" USING btree ("target_entity_type","target_entity_id","relationship_type");--> statement-breakpoint CREATE UNIQUE INDEX "risk_factors_slug_uq" ON "risk_factors" USING btree ("slug");--> statement-breakpoint CREATE UNIQUE INDEX "epi_obs_uq" ON "epidemiology_observations" USING btree ("cancer_id","geography_id","year","sex","age_group","metric","source_id","site_definition");--> statement-breakpoint CREATE INDEX "epi_obs_lookup_idx" ON "epidemiology_observations" USING btree ("metric","geography_id","year","sex");--> statement-breakpoint CREATE INDEX "epi_obs_cancer_idx" ON "epidemiology_observations" USING btree ("cancer_id","metric");--> statement-breakpoint CREATE INDEX "survival_obs_cancer_idx" ON "survival_observations" USING btree ("cancer_id","survival_type","duration_months");--> statement-breakpoint CREATE UNIQUE INDEX "ai_answers_uq" ON "ai_answers" USING btree ("kind","question_hash","prompt_version");--> statement-breakpoint CREATE UNIQUE INDEX "api_keys_hash_uq" ON "api_keys" USING btree ("key_hash");--> statement-breakpoint CREATE UNIQUE INDEX "entity_counters_uq" ON "entity_counters" USING btree ("entity_type","entity_id");--> statement-breakpoint CREATE INDEX "entity_counters_trials_idx" ON "entity_counters" USING btree ("entity_type","active_trial_count");--> statement-breakpoint CREATE UNIQUE INDEX "metric_definitions_slug_uq" ON "metric_definitions" USING btree ("slug");--> statement-breakpoint CREATE INDEX "ranking_snapshots_lookup_idx" ON "ranking_snapshots" USING btree ("metric_slug","scope_key","is_current");--> statement-breakpoint CREATE UNIQUE INDEX "rankings_uq" ON "rankings" USING btree ("snapshot_id","cancer_id");--> statement-breakpoint CREATE INDEX "rankings_cancer_idx" ON "rankings" USING btree ("cancer_id","metric_slug");--> statement-breakpoint CREATE INDEX "rankings_lookup_idx" ON "rankings" USING btree ("metric_slug","scope_key","rank");