spb/worthdoing Public

1CREATE TABLE "agent_events" (2	"id" bigserial PRIMARY KEY NOT NULL,3	"investigation_id" uuid NOT NULL,4	"seq" integer NOT NULL,5	"type" text NOT NULL,6	"payload" jsonb NOT NULL,7	"created_at" timestamp with time zone DEFAULT now() NOT NULL8);9--> statement-breakpoint10CREATE TABLE "claims" (11	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,12	"investigation_id" uuid NOT NULL,13	"text" text NOT NULL,14	"status" text DEFAULT 'open' NOT NULL,15	"confidence" real DEFAULT 0.5 NOT NULL,16	"created_at" timestamp with time zone DEFAULT now() NOT NULL17);18--> statement-breakpoint19CREATE TABLE "competitors" (20	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,21	"name" text NOT NULL,22	"url" text,23	"description" text,24	"created_at" timestamp with time zone DEFAULT now() NOT NULL25);26--> statement-breakpoint27CREATE TABLE "evidence" (28	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,29	"investigation_id" uuid NOT NULL,30	"source_id" uuid NOT NULL,31	"claim_id" uuid,32	"kind" text NOT NULL,33	"quote" text NOT NULL,34	"summary" text NOT NULL,35	"strength" real DEFAULT 0.5 NOT NULL,36	"fingerprint" text NOT NULL,37	"created_at" timestamp with time zone DEFAULT now() NOT NULL38);39--> statement-breakpoint40CREATE TABLE "hypotheses" (41	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,42	"investigation_id" uuid NOT NULL,43	"parent_hypothesis_id" uuid,44	"title" text NOT NULL,45	"statement" text NOT NULL,46	"status" text DEFAULT 'proposed' NOT NULL,47	"confidence" real DEFAULT 0.5 NOT NULL,48	"rationale" text,49	"adversarial_checked" boolean DEFAULT false NOT NULL,50	"created_at" timestamp with time zone DEFAULT now() NOT NULL,51	"updated_at" timestamp with time zone DEFAULT now() NOT NULL52);53--> statement-breakpoint54CREATE TABLE "hypothesis_evidence" (55	"hypothesis_id" uuid NOT NULL,56	"evidence_id" uuid NOT NULL,57	"relation" text NOT NULL,58	"weight" real DEFAULT 0.5 NOT NULL,59	CONSTRAINT "hypothesis_evidence_hypothesis_id_evidence_id_pk" PRIMARY KEY("hypothesis_id","evidence_id")60);61--> statement-breakpoint62CREATE TABLE "investigation_steps" (63	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,64	"investigation_id" uuid NOT NULL,65	"step_number" integer NOT NULL,66	"model" text NOT NULL,67	"input_tokens" integer DEFAULT 0 NOT NULL,68	"output_tokens" integer DEFAULT 0 NOT NULL,69	"cost_usd" real DEFAULT 0 NOT NULL,70	"latency_ms" integer DEFAULT 0 NOT NULL,71	"tool_name" text,72	"tool_args" jsonb,73	"tool_result_summary" text,74	"decision_summary" text,75	"error" text,76	"created_at" timestamp with time zone DEFAULT now() NOT NULL77);78--> statement-breakpoint79CREATE TABLE "investigations" (80	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,81	"user_id" uuid,82	"objective" text NOT NULL,83	"status" text DEFAULT 'pending' NOT NULL,84	"phase" text DEFAULT 'scouting' NOT NULL,85	"stop_reason" text,86	"budget" jsonb NOT NULL,87	"budget_used" jsonb NOT NULL,88	"prompt_version" text NOT NULL,89	"tool_schema_version" text NOT NULL,90	"model" text NOT NULL,91	"error" text,92	"created_at" timestamp with time zone DEFAULT now() NOT NULL,93	"started_at" timestamp with time zone,94	"completed_at" timestamp with time zone95);96--> statement-breakpoint97CREATE TABLE "opportunities" (98	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,99	"investigation_id" uuid NOT NULL,100	"hypothesis_id" uuid,101	"title" text NOT NULL,102	"summary" text NOT NULL,103	"problem" text NOT NULL,104	"why_now" text NOT NULL,105	"risks" text NOT NULL,106	"skeptic_case" text NOT NULL,107	"report_md" text,108	"status" text DEFAULT 'candidate' NOT NULL,109	"worth_score" real,110	"evidence_confidence" real,111	"created_at" timestamp with time zone DEFAULT now() NOT NULL,112	"updated_at" timestamp with time zone DEFAULT now() NOT NULL113);114--> statement-breakpoint115CREATE TABLE "opportunity_competitors" (116	"opportunity_id" uuid NOT NULL,117	"competitor_id" uuid NOT NULL,118	"note" text,119	CONSTRAINT "opportunity_competitors_opportunity_id_competitor_id_pk" PRIMARY KEY("opportunity_id","competitor_id")120);121--> statement-breakpoint122CREATE TABLE "opportunity_evidence" (123	"opportunity_id" uuid NOT NULL,124	"evidence_id" uuid NOT NULL,125	"role" text DEFAULT 'context' NOT NULL,126	CONSTRAINT "opportunity_evidence_opportunity_id_evidence_id_pk" PRIMARY KEY("opportunity_id","evidence_id")127);128--> statement-breakpoint129CREATE TABLE "opportunity_scores" (130	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,131	"opportunity_id" uuid NOT NULL,132	"dimension" text NOT NULL,133	"score" real NOT NULL,134	"confidence" real NOT NULL,135	"reasoning" text NOT NULL,136	"evidence_ids" jsonb NOT NULL,137	"created_at" timestamp with time zone DEFAULT now() NOT NULL138);139--> statement-breakpoint140CREATE TABLE "saved_opportunities" (141	"user_id" uuid NOT NULL,142	"opportunity_id" uuid NOT NULL,143	"created_at" timestamp with time zone DEFAULT now() NOT NULL,144	CONSTRAINT "saved_opportunities_user_id_opportunity_id_pk" PRIMARY KEY("user_id","opportunity_id")145);146--> statement-breakpoint147CREATE TABLE "searches" (148	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,149	"investigation_id" uuid NOT NULL,150	"query" text NOT NULL,151	"intent" text,152	"provider" text DEFAULT 'firecrawl' NOT NULL,153	"result_count" integer DEFAULT 0 NOT NULL,154	"results" jsonb NOT NULL,155	"created_at" timestamp with time zone DEFAULT now() NOT NULL156);157--> statement-breakpoint158CREATE TABLE "source_contents" (159	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,160	"source_id" uuid NOT NULL,161	"content_hash" text NOT NULL,162	"markdown" text NOT NULL,163	"http_status" integer,164	"word_count" integer DEFAULT 0 NOT NULL,165	"retrieved_at" timestamp with time zone DEFAULT now() NOT NULL166);167--> statement-breakpoint168CREATE TABLE "sources" (169	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,170	"url" text NOT NULL,171	"canonical_url" text NOT NULL,172	"domain" text NOT NULL,173	"title" text,174	"description" text,175	"first_seen_investigation_id" uuid,176	"created_at" timestamp with time zone DEFAULT now() NOT NULL177);178--> statement-breakpoint179CREATE TABLE "users" (180	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,181	"email" text,182	"name" text,183	"created_at" timestamp with time zone DEFAULT now() NOT NULL,184	CONSTRAINT "users_email_unique" UNIQUE("email")185);186--> statement-breakpoint187ALTER TABLE "agent_events" ADD CONSTRAINT "agent_events_investigation_id_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint188ALTER TABLE "claims" ADD CONSTRAINT "claims_investigation_id_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint189ALTER TABLE "evidence" ADD CONSTRAINT "evidence_investigation_id_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint190ALTER TABLE "evidence" ADD CONSTRAINT "evidence_source_id_sources_id_fk" FOREIGN KEY ("source_id") REFERENCES "public"."sources"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint191ALTER TABLE "evidence" ADD CONSTRAINT "evidence_claim_id_claims_id_fk" FOREIGN KEY ("claim_id") REFERENCES "public"."claims"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint192ALTER TABLE "hypotheses" ADD CONSTRAINT "hypotheses_investigation_id_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint193ALTER TABLE "hypothesis_evidence" ADD CONSTRAINT "hypothesis_evidence_hypothesis_id_hypotheses_id_fk" FOREIGN KEY ("hypothesis_id") REFERENCES "public"."hypotheses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint194ALTER TABLE "hypothesis_evidence" ADD CONSTRAINT "hypothesis_evidence_evidence_id_evidence_id_fk" FOREIGN KEY ("evidence_id") REFERENCES "public"."evidence"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint195ALTER TABLE "investigation_steps" ADD CONSTRAINT "investigation_steps_investigation_id_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint196ALTER TABLE "investigations" ADD CONSTRAINT "investigations_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint197ALTER TABLE "opportunities" ADD CONSTRAINT "opportunities_investigation_id_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint198ALTER TABLE "opportunities" ADD CONSTRAINT "opportunities_hypothesis_id_hypotheses_id_fk" FOREIGN KEY ("hypothesis_id") REFERENCES "public"."hypotheses"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint199ALTER TABLE "opportunity_competitors" ADD CONSTRAINT "opportunity_competitors_opportunity_id_opportunities_id_fk" FOREIGN KEY ("opportunity_id") REFERENCES "public"."opportunities"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint200ALTER TABLE "opportunity_competitors" ADD CONSTRAINT "opportunity_competitors_competitor_id_competitors_id_fk" FOREIGN KEY ("competitor_id") REFERENCES "public"."competitors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint201ALTER TABLE "opportunity_evidence" ADD CONSTRAINT "opportunity_evidence_opportunity_id_opportunities_id_fk" FOREIGN KEY ("opportunity_id") REFERENCES "public"."opportunities"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint202ALTER TABLE "opportunity_evidence" ADD CONSTRAINT "opportunity_evidence_evidence_id_evidence_id_fk" FOREIGN KEY ("evidence_id") REFERENCES "public"."evidence"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint203ALTER TABLE "opportunity_scores" ADD CONSTRAINT "opportunity_scores_opportunity_id_opportunities_id_fk" FOREIGN KEY ("opportunity_id") REFERENCES "public"."opportunities"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint204ALTER TABLE "saved_opportunities" ADD CONSTRAINT "saved_opportunities_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint205ALTER TABLE "saved_opportunities" ADD CONSTRAINT "saved_opportunities_opportunity_id_opportunities_id_fk" FOREIGN KEY ("opportunity_id") REFERENCES "public"."opportunities"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint206ALTER TABLE "searches" ADD CONSTRAINT "searches_investigation_id_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint207ALTER TABLE "source_contents" ADD CONSTRAINT "source_contents_source_id_sources_id_fk" FOREIGN KEY ("source_id") REFERENCES "public"."sources"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint208ALTER TABLE "sources" ADD CONSTRAINT "sources_first_seen_investigation_id_investigations_id_fk" FOREIGN KEY ("first_seen_investigation_id") REFERENCES "public"."investigations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint209CREATE UNIQUE INDEX "agent_events_seq_idx" ON "agent_events" USING btree ("investigation_id","seq");--> statement-breakpoint210CREATE INDEX "claims_investigation_idx" ON "claims" USING btree ("investigation_id");--> statement-breakpoint211CREATE UNIQUE INDEX "competitors_name_idx" ON "competitors" USING btree ("name");--> statement-breakpoint212CREATE INDEX "evidence_investigation_idx" ON "evidence" USING btree ("investigation_id");--> statement-breakpoint213CREATE UNIQUE INDEX "evidence_fingerprint_idx" ON "evidence" USING btree ("investigation_id","fingerprint");--> statement-breakpoint214CREATE INDEX "hypotheses_investigation_idx" ON "hypotheses" USING btree ("investigation_id");--> statement-breakpoint215CREATE INDEX "steps_investigation_idx" ON "investigation_steps" USING btree ("investigation_id","step_number");--> statement-breakpoint216CREATE INDEX "investigations_status_idx" ON "investigations" USING btree ("status");--> statement-breakpoint217CREATE INDEX "investigations_created_idx" ON "investigations" USING btree ("created_at");--> statement-breakpoint218CREATE INDEX "opportunities_investigation_idx" ON "opportunities" USING btree ("investigation_id");--> statement-breakpoint219CREATE INDEX "opportunities_score_idx" ON "opportunities" USING btree ("worth_score");--> statement-breakpoint220CREATE UNIQUE INDEX "opportunity_scores_dim_idx" ON "opportunity_scores" USING btree ("opportunity_id","dimension");--> statement-breakpoint221CREATE INDEX "searches_investigation_idx" ON "searches" USING btree ("investigation_id");--> statement-breakpoint222CREATE INDEX "source_contents_source_idx" ON "source_contents" USING btree ("source_id","retrieved_at");--> statement-breakpoint223CREATE INDEX "source_contents_hash_idx" ON "source_contents" USING btree ("content_hash");--> statement-breakpoint224CREATE UNIQUE INDEX "sources_canonical_idx" ON "sources" USING btree ("canonical_url");--> statement-breakpoint225CREATE INDEX "sources_domain_idx" ON "sources" USING btree ("domain");