spb/cancerindex

1CREATE TABLE "drug_codes" (2	"id" bigserial PRIMARY KEY NOT NULL,3	"drug_id" varchar(32) NOT NULL,4	"system" text NOT NULL,5	"code" text NOT NULL,6	"label" text,7	"match_type" text DEFAULT 'EXACT_IDENTIFIER' NOT NULL,8	"source_id" varchar(32)9);10--> statement-breakpoint11CREATE TABLE "drug_pipeline" (12	"id" bigserial PRIMARY KEY NOT NULL,13	"drug_id" varchar(32) NOT NULL,14	"cancer_id" varchar(32),15	"stage" text NOT NULL,16	"max_phase" text,17	"active_trials" integer DEFAULT 0 NOT NULL,18	"recruiting_trials" integer DEFAULT 0 NOT NULL,19	"phase3_trials" integer DEFAULT 0 NOT NULL,20	"total_trials" integer DEFAULT 0 NOT NULL,21	"approvals" integer DEFAULT 0 NOT NULL,22	"jurisdictions" text[] DEFAULT '{}' NOT NULL,23	"first_approval_date" text,24	"latest_approval_date" text,25	"first_trial_date" text,26	"formula_version" text NOT NULL,27	"inputs" jsonb DEFAULT '{}'::jsonb NOT NULL,28	"updated_at" timestamp with time zone DEFAULT now() NOT NULL29);30--> statement-breakpoint31CREATE TABLE "research_gap_components" (32	"id" bigserial PRIMARY KEY NOT NULL,33	"cancer_id" varchar(32) NOT NULL,34	"geography" text NOT NULL,35	"year" integer NOT NULL,36	"sex" text DEFAULT 'all' NOT NULL,37	"burden_source_id" varchar(32) NOT NULL,38	"deaths" double precision,39	"incidence" double precision,40	"active_trials" integer DEFAULT 0 NOT NULL,41	"phase3_trials" integer DEFAULT 0 NOT NULL,42	"publications_5y" integer DEFAULT 0 NOT NULL,43	"approved_drugs" integer DEFAULT 0 NOT NULL,44	"death_share" real,45	"trial_share" real,46	"publication_share" real,47	"trial_gap_ratio" real,48	"research_gap_ratio" real,49	"trials_per_1000_deaths" real,50	"publications_per_1000_deaths" real,51	"eligible" boolean DEFAULT true NOT NULL,52	"ineligible_reason" text,53	"formula_version" text NOT NULL,54	"inputs" jsonb DEFAULT '{}'::jsonb NOT NULL,55	"updated_at" timestamp with time zone DEFAULT now() NOT NULL56);57--> statement-breakpoint58CREATE TABLE "trial_intelligence" (59	"id" bigserial PRIMARY KEY NOT NULL,60	"cancer_id" varchar(32) NOT NULL,61	"entity_level" text DEFAULT 'all' NOT NULL,62	"total_trials" integer DEFAULT 0 NOT NULL,63	"active_trials" integer DEFAULT 0 NOT NULL,64	"recruiting_trials" integer DEFAULT 0 NOT NULL,65	"phase1_active" integer DEFAULT 0 NOT NULL,66	"phase2_active" integer DEFAULT 0 NOT NULL,67	"phase3_active" integer DEFAULT 0 NOT NULL,68	"phase3_recruiting" integer DEFAULT 0 NOT NULL,69	"phase4_active" integer DEFAULT 0 NOT NULL,70	"completed_trials" integer DEFAULT 0 NOT NULL,71	"terminated_trials" integer DEFAULT 0 NOT NULL,72	"withdrawn_trials" integer DEFAULT 0 NOT NULL,73	"suspended_trials" integer DEFAULT 0 NOT NULL,74	"with_results" integer DEFAULT 0 NOT NULL,75	"new_trials_12m" integer DEFAULT 0 NOT NULL,76	"new_trials_prior_12m" integer DEFAULT 0 NOT NULL,77	"trial_growth_yoy" real,78	"avg_enrollment" real,79	"median_enrollment" real,80	"total_enrollment_active" integer,81	"distinct_sponsors" integer DEFAULT 0 NOT NULL,82	"industry_share" real,83	"sponsor_hhi" real,84	"top_sponsor" text,85	"top_sponsor_share" real,86	"distinct_countries" integer DEFAULT 0 NOT NULL,87	"us_share" real,88	"top_country" text,89	"top_country_share" real,90	"country_hhi" real,91	"termination_share" real,92	"why_stopped_breakdown" jsonb DEFAULT '{}'::jsonb NOT NULL,93	"trials_per_1000_deaths" real,94	"trials_per_100k_cases" real,95	"burden_geography" text,96	"burden_year" integer,97	"burden_source_id" varchar(32),98	"formula_version" text NOT NULL,99	"inputs" jsonb DEFAULT '{}'::jsonb NOT NULL,100	"updated_at" timestamp with time zone DEFAULT now() NOT NULL101);102--> statement-breakpoint103CREATE TABLE "trial_site_country_counts" (104	"id" bigserial PRIMARY KEY NOT NULL,105	"cancer_id" varchar(32),106	"phase" text,107	"recruiting_only" boolean DEFAULT false NOT NULL,108	"country" text NOT NULL,109	"iso3" text,110	"sites" integer NOT NULL,111	"trials" integer NOT NULL,112	"formula_version" text NOT NULL,113	"updated_at" timestamp with time zone DEFAULT now() NOT NULL114);115--> statement-breakpoint116CREATE UNIQUE INDEX "drug_codes_uq" ON "drug_codes" USING btree ("drug_id","system","code");--> statement-breakpoint117CREATE INDEX "drug_codes_lookup_idx" ON "drug_codes" USING btree ("system","code");--> statement-breakpoint118CREATE UNIQUE INDEX "drug_pipeline_uq" ON "drug_pipeline" USING btree ("drug_id","cancer_id");--> statement-breakpoint119CREATE INDEX "drug_pipeline_stage_idx" ON "drug_pipeline" USING btree ("stage");--> statement-breakpoint120CREATE INDEX "drug_pipeline_cancer_idx" ON "drug_pipeline" USING btree ("cancer_id","stage");--> statement-breakpoint121CREATE UNIQUE INDEX "research_gap_components_uq" ON "research_gap_components" USING btree ("cancer_id","geography","year","sex","burden_source_id");--> statement-breakpoint122CREATE INDEX "research_gap_scope_idx" ON "research_gap_components" USING btree ("geography","year","sex");--> statement-breakpoint123CREATE UNIQUE INDEX "trial_intelligence_uq" ON "trial_intelligence" USING btree ("cancer_id","entity_level");--> statement-breakpoint124CREATE INDEX "trial_intelligence_active_idx" ON "trial_intelligence" USING btree ("entity_level","active_trials");--> statement-breakpoint125CREATE UNIQUE INDEX "trial_site_country_uq" ON "trial_site_country_counts" USING btree ("cancer_id","phase","recruiting_only","country");--> statement-breakpoint126CREATE INDEX "trial_site_country_lookup_idx" ON "trial_site_country_counts" USING btree ("cancer_id","phase","recruiting_only");