spb/autoscholar

1CREATE FUNCTION as_immutable_array_to_string(text[]) RETURNS text IMMUTABLE PARALLEL SAFE LANGUAGE sql AS $$ SELECT coalesce(array_to_string($1, ' '), '') $$;2--> statement-breakpoint3CREATE EXTENSION IF NOT EXISTS pg_trgm;4--> statement-breakpoint5CREATE TABLE "admin_users" (6	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,7	"username" text NOT NULL,8	"password_hash" text NOT NULL,9	"must_change_password" boolean DEFAULT true NOT NULL,10	"bootstrapped" boolean DEFAULT false NOT NULL,11	"totp_secret" text,12	"failed_attempts" integer DEFAULT 0 NOT NULL,13	"locked_until" timestamp with time zone,14	"last_login_at" timestamp with time zone,15	"created_at" timestamp with time zone DEFAULT now() NOT NULL,16	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,17	CONSTRAINT "admin_users_username_unique" UNIQUE("username")18);19--> statement-breakpoint20CREATE TABLE "audit_events" (21	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,22	"actor_type" text NOT NULL,23	"actor_id" text,24	"action" text NOT NULL,25	"target_type" text,26	"target_id" text,27	"detail" jsonb DEFAULT '{}'::jsonb NOT NULL,28	"ip" "inet",29	"created_at" timestamp with time zone DEFAULT now() NOT NULL30);31--> statement-breakpoint32CREATE TABLE "login_attempts" (33	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,34	"username" text NOT NULL,35	"ip" "inet",36	"success" boolean NOT NULL,37	"created_at" timestamp with time zone DEFAULT now() NOT NULL38);39--> statement-breakpoint40CREATE TABLE "password_changes" (41	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,42	"user_id" uuid NOT NULL,43	"previous_hash" text NOT NULL,44	"reason" text NOT NULL,45	"ip" "inet",46	"changed_at" timestamp with time zone DEFAULT now() NOT NULL47);48--> statement-breakpoint49CREATE TABLE "security_events" (50	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,51	"user_id" uuid,52	"kind" text NOT NULL,53	"ip" "inet",54	"detail" jsonb DEFAULT '{}'::jsonb NOT NULL,55	"created_at" timestamp with time zone DEFAULT now() NOT NULL56);57--> statement-breakpoint58CREATE TABLE "sessions" (59	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,60	"token_hash" text NOT NULL,61	"user_id" uuid NOT NULL,62	"csrf_secret" text NOT NULL,63	"created_at" timestamp with time zone DEFAULT now() NOT NULL,64	"expires_at" timestamp with time zone NOT NULL,65	"last_seen_at" timestamp with time zone DEFAULT now() NOT NULL,66	"rotated_from" uuid,67	"ip" "inet",68	"user_agent" text,69	"revoked_at" timestamp with time zone,70	CONSTRAINT "sessions_token_hash_unique" UNIQUE("token_hash")71);72--> statement-breakpoint73CREATE TABLE "project_instruction_versions" (74	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,75	"project_id" uuid NOT NULL,76	"version" integer NOT NULL,77	"filename" text NOT NULL,78	"sha256" text NOT NULL,79	"byte_size" integer NOT NULL,80	"content_text" text NOT NULL,81	"original_bytes" "bytea" NOT NULL,82	"frontmatter" jsonb,83	"validation" jsonb DEFAULT '{}'::jsonb NOT NULL,84	"note" text,85	"created_by" uuid,86	"created_at" timestamp with time zone DEFAULT now() NOT NULL87);88--> statement-breakpoint89CREATE TABLE "research_projects" (90	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,91	"title" text NOT NULL,92	"discipline" text NOT NULL,93	"description" text DEFAULT '' NOT NULL,94	"created_by" uuid,95	"created_at" timestamp with time zone DEFAULT now() NOT NULL,96	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,97	"archived_at" timestamp with time zone98);99--> statement-breakpoint100CREATE TABLE "research_templates" (101	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,102	"slug" text NOT NULL,103	"name" text NOT NULL,104	"mode" text NOT NULL,105	"description" text DEFAULT '' NOT NULL,106	"content" text NOT NULL,107	"version" integer DEFAULT 1 NOT NULL,108	"created_at" timestamp with time zone DEFAULT now() NOT NULL,109	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,110	CONSTRAINT "research_templates_slug_unique" UNIQUE("slug")111);112--> statement-breakpoint113CREATE TABLE "checkpoints" (114	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,115	"run_id" uuid NOT NULL,116	"attempt_id" uuid NOT NULL,117	"stage" text NOT NULL,118	"data" jsonb DEFAULT '{}'::jsonb NOT NULL,119	"created_at" timestamp with time zone DEFAULT now() NOT NULL120);121--> statement-breakpoint122CREATE TABLE "job_leases" (123	"run_id" uuid PRIMARY KEY NOT NULL,124	"attempt_id" uuid NOT NULL,125	"worker_id" uuid NOT NULL,126	"fencing_token" bigint NOT NULL,127	"acquired_at" timestamp with time zone DEFAULT now() NOT NULL,128	"expires_at" timestamp with time zone NOT NULL,129	"last_heartbeat_at" timestamp with time zone DEFAULT now() NOT NULL130);131--> statement-breakpoint132CREATE TABLE "research_runs" (133	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,134	"project_id" uuid NOT NULL,135	"instruction_version_id" uuid NOT NULL,136	"idempotency_key" text NOT NULL,137	"title" text NOT NULL,138	"status" text DEFAULT 'draft' NOT NULL,139	"research_mode" text NOT NULL,140	"secondary_modes" text[] DEFAULT '{}' NOT NULL,141	"discipline" text NOT NULL,142	"compute_profile" text DEFAULT 'standard' NOT NULL,143	"provider" text DEFAULT 'anthropic' NOT NULL,144	"model" text NOT NULL,145	"reviewer_model" text NOT NULL,146	"max_budget_usd" numeric(10, 2) NOT NULL,147	"max_runtime_minutes" integer NOT NULL,148	"max_attempts" integer DEFAULT 2 NOT NULL,149	"auto_publish" boolean DEFAULT false NOT NULL,150	"quality_policy_version" text NOT NULL,151	"manifest" jsonb NOT NULL,152	"manifest_sha256" text NOT NULL,153	"current_attempt_no" integer DEFAULT 0 NOT NULL,154	"current_stage" text,155	"fencing_token" bigint DEFAULT 0 NOT NULL,156	"last_event_seq" bigint DEFAULT 0 NOT NULL,157	"cancel_requested" boolean DEFAULT false NOT NULL,158	"failure_reason" text,159	"total_cost_usd" numeric(10, 4) DEFAULT '0' NOT NULL,160	"paper_id" uuid,161	"queued_at" timestamp with time zone,162	"started_at" timestamp with time zone,163	"finished_at" timestamp with time zone,164	"created_by" uuid,165	"created_at" timestamp with time zone DEFAULT now() NOT NULL,166	"updated_at" timestamp with time zone DEFAULT now() NOT NULL167);168--> statement-breakpoint169CREATE TABLE "run_attempts" (170	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,171	"run_id" uuid NOT NULL,172	"attempt_no" integer NOT NULL,173	"worker_id" uuid,174	"status" text DEFAULT 'leased' NOT NULL,175	"fencing_token" bigint NOT NULL,176	"resume_from_stage" text,177	"started_at" timestamp with time zone DEFAULT now() NOT NULL,178	"ended_at" timestamp with time zone,179	"error" text,180	"cost_usd" numeric(10, 4) DEFAULT '0' NOT NULL,181	"summary" jsonb182);183--> statement-breakpoint184CREATE TABLE "stage_events" (185	"id" bigserial PRIMARY KEY NOT NULL,186	"run_id" uuid NOT NULL,187	"attempt_id" uuid,188	"worker_id" uuid,189	"seq" bigint NOT NULL,190	"stage" text,191	"level" text DEFAULT 'info' NOT NULL,192	"kind" text NOT NULL,193	"message" text NOT NULL,194	"data" jsonb,195	"at" timestamp with time zone NOT NULL,196	"created_at" timestamp with time zone DEFAULT now() NOT NULL197);198--> statement-breakpoint199CREATE TABLE "stage_executions" (200	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,201	"run_id" uuid NOT NULL,202	"attempt_id" uuid NOT NULL,203	"stage" text NOT NULL,204	"status" text DEFAULT 'pending' NOT NULL,205	"started_at" timestamp with time zone DEFAULT now() NOT NULL,206	"ended_at" timestamp with time zone,207	"cost_usd" numeric(10, 4) DEFAULT '0' NOT NULL,208	"turns" integer DEFAULT 0 NOT NULL,209	"usage" jsonb,210	"error" text211);212--> statement-breakpoint213CREATE TABLE "compute_workers" (214	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,215	"name" text NOT NULL,216	"token_hash" text NOT NULL,217	"status" text DEFAULT 'offline' NOT NULL,218	"version" text,219	"protocol_version" integer DEFAULT 1 NOT NULL,220	"lan_ip" text,221	"node_meta" jsonb DEFAULT '{}'::jsonb NOT NULL,222	"profiles" text[] DEFAULT '{"standard"}' NOT NULL,223	"capacity" integer DEFAULT 1 NOT NULL,224	"running" integer DEFAULT 0 NOT NULL,225	"enrolled_at" timestamp with time zone DEFAULT now() NOT NULL,226	"last_heartbeat_at" timestamp with time zone,227	"drain_requested_at" timestamp with time zone,228	"quarantine_reason" text,229	"disabled_at" timestamp with time zone,230	"token_rotated_at" timestamp with time zone,231	CONSTRAINT "compute_workers_name_unique" UNIQUE("name")232);233--> statement-breakpoint234CREATE TABLE "resource_profiles" (235	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,236	"slug" text NOT NULL,237	"name" text NOT NULL,238	"description" text DEFAULT '' NOT NULL,239	"cpu_cores" integer NOT NULL,240	"ram_gb" integer NOT NULL,241	"max_runtime_minutes" integer NOT NULL,242	"default_budget_usd" integer NOT NULL,243	"requires" text[] DEFAULT '{}' NOT NULL,244	CONSTRAINT "resource_profiles_slug_unique" UNIQUE("slug")245);246--> statement-breakpoint247CREATE TABLE "worker_assignments" (248	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,249	"run_id" uuid NOT NULL,250	"attempt_id" uuid NOT NULL,251	"worker_id" uuid NOT NULL,252	"assigned_at" timestamp with time zone DEFAULT now() NOT NULL,253	"released_at" timestamp with time zone,254	"release_reason" text255);256--> statement-breakpoint257CREATE TABLE "worker_heartbeats" (258	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,259	"worker_id" uuid NOT NULL,260	"load" jsonb DEFAULT '{}'::jsonb NOT NULL,261	"running" integer DEFAULT 0 NOT NULL,262	"created_at" timestamp with time zone DEFAULT now() NOT NULL263);264--> statement-breakpoint265CREATE TABLE "artifacts" (266	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,267	"run_id" uuid NOT NULL,268	"attempt_id" uuid,269	"stage" text NOT NULL,270	"path" text NOT NULL,271	"version" integer DEFAULT 1 NOT NULL,272	"kind" text NOT NULL,273	"sha256" text NOT NULL,274	"bytes" bigint NOT NULL,275	"content_type" text DEFAULT 'application/octet-stream' NOT NULL,276	"storage_key" text NOT NULL,277	"visibility" text DEFAULT 'private' NOT NULL,278	"synthetic" boolean DEFAULT false NOT NULL,279	"created_at" timestamp with time zone DEFAULT now() NOT NULL280);281--> statement-breakpoint282CREATE TABLE "citations" (283	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,284	"run_id" uuid NOT NULL,285	"claim" text NOT NULL,286	"location" text,287	"kind" text DEFAULT 'literature' NOT NULL,288	"source_keys" text[] DEFAULT '{}' NOT NULL,289	"artifact_paths" text[] DEFAULT '{}' NOT NULL290);291--> statement-breakpoint292CREATE TABLE "datasets" (293	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,294	"run_id" uuid NOT NULL,295	"name" text NOT NULL,296	"source" text NOT NULL,297	"url" text,298	"license" text NOT NULL,299	"acquired_at" text NOT NULL,300	"sha256" text,301	"bytes" bigint,302	"synthetic" boolean DEFAULT false NOT NULL,303	"transformations" text[] DEFAULT '{}' NOT NULL,304	"notes" text305);306--> statement-breakpoint307CREATE TABLE "experiment_records" (308	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,309	"run_id" uuid NOT NULL,310	"name" text NOT NULL,311	"command" text NOT NULL,312	"started_at" text NOT NULL,313	"ended_at" text NOT NULL,314	"exit_code" integer NOT NULL,315	"seed" text,316	"environment" jsonb,317	"outputs" text[] DEFAULT '{}' NOT NULL,318	"synthetic" boolean DEFAULT false NOT NULL319);320--> statement-breakpoint321CREATE TABLE "research_sources" (322	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,323	"run_id" uuid NOT NULL,324	"key" text NOT NULL,325	"kind" text NOT NULL,326	"identifier" text NOT NULL,327	"title" text NOT NULL,328	"authors" jsonb DEFAULT '[]'::jsonb NOT NULL,329	"year" integer,330	"venue" text,331	"url" text,332	"verified" boolean DEFAULT false NOT NULL,333	"verified_at" timestamp with time zone,334	"verification" jsonb DEFAULT '{}'::jsonb NOT NULL,335	"access_level" text DEFAULT 'metadata' NOT NULL336);337--> statement-breakpoint338CREATE TABLE "review_findings" (339	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,340	"run_id" uuid NOT NULL,341	"round" integer NOT NULL,342	"finding_key" text NOT NULL,343	"severity" text NOT NULL,344	"category" text NOT NULL,345	"title" text NOT NULL,346	"detail" text NOT NULL,347	"proposed_test" text,348	"resolution" text DEFAULT 'open' NOT NULL,349	"resolution_note" text,350	"created_at" timestamp with time zone DEFAULT now() NOT NULL351);352--> statement-breakpoint353CREATE TABLE "publication_decisions" (354	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,355	"run_id" uuid NOT NULL,356	"evaluation_id" uuid,357	"decision" text NOT NULL,358	"decided_by" text NOT NULL,359	"rationale" text NOT NULL,360	"override" boolean DEFAULT false NOT NULL,361	"override_rationale" text,362	"gate_summary" jsonb DEFAULT '{}'::jsonb NOT NULL,363	"created_at" timestamp with time zone DEFAULT now() NOT NULL364);365--> statement-breakpoint366CREATE TABLE "quality_gate_results" (367	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,368	"run_id" uuid NOT NULL,369	"evaluation_id" uuid NOT NULL,370	"policy_version" text NOT NULL,371	"mode" text NOT NULL,372	"gate" text NOT NULL,373	"severity" text NOT NULL,374	"status" text NOT NULL,375	"message" text NOT NULL,376	"evidence" jsonb DEFAULT '{}'::jsonb NOT NULL,377	"created_at" timestamp with time zone DEFAULT now() NOT NULL378);379--> statement-breakpoint380CREATE TABLE "quality_policies" (381	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,382	"version" text NOT NULL,383	"active" boolean DEFAULT false NOT NULL,384	"profiles" jsonb NOT NULL,385	"created_at" timestamp with time zone DEFAULT now() NOT NULL,386	CONSTRAINT "quality_policies_version_unique" UNIQUE("version")387);388--> statement-breakpoint389CREATE TABLE "corrections" (390	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,391	"paper_id" uuid NOT NULL,392	"from_version_no" integer NOT NULL,393	"to_version_no" integer NOT NULL,394	"notice" text NOT NULL,395	"created_at" timestamp with time zone DEFAULT now() NOT NULL396);397--> statement-breakpoint398CREATE TABLE "paper_sequences" (399	"year" integer PRIMARY KEY NOT NULL,400	"last" integer DEFAULT 0 NOT NULL401);402--> statement-breakpoint403CREATE TABLE "paper_series" (404	"paper_id" uuid NOT NULL,405	"series_id" uuid NOT NULL,406	CONSTRAINT "paper_series_paper_id_series_id_pk" PRIMARY KEY("paper_id","series_id")407);408--> statement-breakpoint409CREATE TABLE "paper_topics" (410	"paper_id" uuid NOT NULL,411	"topic_id" uuid NOT NULL,412	CONSTRAINT "paper_topics_paper_id_topic_id_pk" PRIMARY KEY("paper_id","topic_id")413);414--> statement-breakpoint415CREATE TABLE "paper_versions" (416	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,417	"paper_id" uuid NOT NULL,418	"version_no" integer NOT NULL,419	"run_id" uuid NOT NULL,420	"title" text NOT NULL,421	"abstract" text NOT NULL,422	"keywords" text[] DEFAULT '{}' NOT NULL,423	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,424	"artifact_set" jsonb DEFAULT '[]'::jsonb NOT NULL,425	"pdf_public_path" text NOT NULL,426	"qa_summary" jsonb DEFAULT '{}'::jsonb NOT NULL,427	"provenance" jsonb DEFAULT '{}'::jsonb NOT NULL,428	"changelog" text DEFAULT '' NOT NULL,429	"published_at" timestamp with time zone DEFAULT now() NOT NULL,430	"search" "tsvector" GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(abstract, '')), 'B') || setweight(to_tsvector('english', as_immutable_array_to_string(keywords)), 'A')) STORED NOT NULL431);432--> statement-breakpoint433CREATE TABLE "papers" (434	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,435	"public_id" text NOT NULL,436	"slug" text NOT NULL,437	"title" text NOT NULL,438	"status" text DEFAULT 'published' NOT NULL,439	"discipline" text NOT NULL,440	"research_mode" text NOT NULL,441	"current_version_id" uuid,442	"series_id" uuid,443	"first_published_at" timestamp with time zone DEFAULT now() NOT NULL,444	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,445	"retracted_at" timestamp with time zone,446	"synthetic" boolean DEFAULT false NOT NULL,447	CONSTRAINT "papers_public_id_unique" UNIQUE("public_id"),448	CONSTRAINT "papers_slug_unique" UNIQUE("slug")449);450--> statement-breakpoint451CREATE TABLE "publication_events" (452	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,453	"paper_id" uuid NOT NULL,454	"version_id" uuid,455	"kind" text NOT NULL,456	"actor" text NOT NULL,457	"detail" jsonb DEFAULT '{}'::jsonb NOT NULL,458	"created_at" timestamp with time zone DEFAULT now() NOT NULL459);460--> statement-breakpoint461CREATE TABLE "retractions" (462	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,463	"paper_id" uuid NOT NULL,464	"reason" text NOT NULL,465	"notice" text NOT NULL,466	"retracted_by" text NOT NULL,467	"retracted_at" timestamp with time zone DEFAULT now() NOT NULL468);469--> statement-breakpoint470CREATE TABLE "series" (471	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,472	"slug" text NOT NULL,473	"name" text NOT NULL,474	"description" text DEFAULT '' NOT NULL,475	"created_at" timestamp with time zone DEFAULT now() NOT NULL,476	CONSTRAINT "series_slug_unique" UNIQUE("slug")477);478--> statement-breakpoint479CREATE TABLE "topics" (480	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,481	"slug" text NOT NULL,482	"name" text NOT NULL,483	"description" text DEFAULT '' NOT NULL,484	"parent_id" uuid,485	"sort_order" integer DEFAULT 0 NOT NULL,486	CONSTRAINT "topics_slug_unique" UNIQUE("slug")487);488--> statement-breakpoint489CREATE TABLE "cost_records" (490	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,491	"run_id" uuid,492	"kind" text NOT NULL,493	"amount_usd" numeric(10, 4) NOT NULL,494	"detail" jsonb DEFAULT '{}'::jsonb NOT NULL,495	"created_at" timestamp with time zone DEFAULT now() NOT NULL496);497--> statement-breakpoint498CREATE TABLE "provider_usage" (499	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,500	"run_id" uuid NOT NULL,501	"attempt_id" uuid,502	"stage" text,503	"provider" text DEFAULT 'anthropic' NOT NULL,504	"model" text NOT NULL,505	"input_tokens" bigint DEFAULT 0 NOT NULL,506	"output_tokens" bigint DEFAULT 0 NOT NULL,507	"cache_read_tokens" bigint DEFAULT 0 NOT NULL,508	"cache_create_tokens" bigint DEFAULT 0 NOT NULL,509	"cost_usd" numeric(10, 4) DEFAULT '0' NOT NULL,510	"turns" integer DEFAULT 0 NOT NULL,511	"created_at" timestamp with time zone DEFAULT now() NOT NULL512);513--> statement-breakpoint514CREATE TABLE "resource_usage" (515	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,516	"run_id" uuid NOT NULL,517	"attempt_id" uuid,518	"worker_id" uuid,519	"cpu_seconds" numeric(12, 2),520	"wall_seconds" numeric(12, 2),521	"disk_bytes" bigint,522	"peak_rss_bytes" bigint,523	"created_at" timestamp with time zone DEFAULT now() NOT NULL524);525--> statement-breakpoint526CREATE TABLE "platform_settings" (527	"key" text PRIMARY KEY NOT NULL,528	"value" jsonb NOT NULL,529	"updated_by" text,530	"updated_at" timestamp with time zone DEFAULT now() NOT NULL531);532--> statement-breakpoint533ALTER TABLE "password_changes" ADD CONSTRAINT "password_changes_user_id_admin_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."admin_users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint534ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_admin_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."admin_users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint535ALTER TABLE "project_instruction_versions" ADD CONSTRAINT "project_instruction_versions_project_id_research_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."research_projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint536ALTER TABLE "project_instruction_versions" ADD CONSTRAINT "project_instruction_versions_created_by_admin_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."admin_users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint537ALTER TABLE "research_projects" ADD CONSTRAINT "research_projects_created_by_admin_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."admin_users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint538ALTER TABLE "checkpoints" ADD CONSTRAINT "checkpoints_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint539ALTER TABLE "job_leases" ADD CONSTRAINT "job_leases_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint540ALTER TABLE "job_leases" ADD CONSTRAINT "job_leases_worker_id_compute_workers_id_fk" FOREIGN KEY ("worker_id") REFERENCES "public"."compute_workers"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint541ALTER TABLE "research_runs" ADD CONSTRAINT "research_runs_project_id_research_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."research_projects"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint542ALTER TABLE "research_runs" ADD CONSTRAINT "research_runs_instruction_version_id_project_instruction_versions_id_fk" FOREIGN KEY ("instruction_version_id") REFERENCES "public"."project_instruction_versions"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint543ALTER TABLE "research_runs" ADD CONSTRAINT "research_runs_created_by_admin_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."admin_users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint544ALTER TABLE "run_attempts" ADD CONSTRAINT "run_attempts_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint545ALTER TABLE "run_attempts" ADD CONSTRAINT "run_attempts_worker_id_compute_workers_id_fk" FOREIGN KEY ("worker_id") REFERENCES "public"."compute_workers"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint546ALTER TABLE "stage_events" ADD CONSTRAINT "stage_events_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint547ALTER TABLE "stage_executions" ADD CONSTRAINT "stage_executions_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint548ALTER TABLE "stage_executions" ADD CONSTRAINT "stage_executions_attempt_id_run_attempts_id_fk" FOREIGN KEY ("attempt_id") REFERENCES "public"."run_attempts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint549ALTER TABLE "worker_assignments" ADD CONSTRAINT "worker_assignments_worker_id_compute_workers_id_fk" FOREIGN KEY ("worker_id") REFERENCES "public"."compute_workers"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint550ALTER TABLE "worker_heartbeats" ADD CONSTRAINT "worker_heartbeats_worker_id_compute_workers_id_fk" FOREIGN KEY ("worker_id") REFERENCES "public"."compute_workers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint551ALTER TABLE "artifacts" ADD CONSTRAINT "artifacts_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint552ALTER TABLE "citations" ADD CONSTRAINT "citations_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint553ALTER TABLE "datasets" ADD CONSTRAINT "datasets_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint554ALTER TABLE "experiment_records" ADD CONSTRAINT "experiment_records_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint555ALTER TABLE "research_sources" ADD CONSTRAINT "research_sources_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint556ALTER TABLE "review_findings" ADD CONSTRAINT "review_findings_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint557ALTER TABLE "publication_decisions" ADD CONSTRAINT "publication_decisions_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint558ALTER TABLE "quality_gate_results" ADD CONSTRAINT "quality_gate_results_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint559ALTER TABLE "corrections" ADD CONSTRAINT "corrections_paper_id_papers_id_fk" FOREIGN KEY ("paper_id") REFERENCES "public"."papers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint560ALTER TABLE "paper_series" ADD CONSTRAINT "paper_series_paper_id_papers_id_fk" FOREIGN KEY ("paper_id") REFERENCES "public"."papers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint561ALTER TABLE "paper_series" ADD CONSTRAINT "paper_series_series_id_series_id_fk" FOREIGN KEY ("series_id") REFERENCES "public"."series"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint562ALTER TABLE "paper_topics" ADD CONSTRAINT "paper_topics_paper_id_papers_id_fk" FOREIGN KEY ("paper_id") REFERENCES "public"."papers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint563ALTER TABLE "paper_topics" ADD CONSTRAINT "paper_topics_topic_id_topics_id_fk" FOREIGN KEY ("topic_id") REFERENCES "public"."topics"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint564ALTER TABLE "paper_versions" ADD CONSTRAINT "paper_versions_paper_id_papers_id_fk" FOREIGN KEY ("paper_id") REFERENCES "public"."papers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint565ALTER TABLE "papers" ADD CONSTRAINT "papers_series_id_series_id_fk" FOREIGN KEY ("series_id") REFERENCES "public"."series"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint566ALTER TABLE "publication_events" ADD CONSTRAINT "publication_events_paper_id_papers_id_fk" FOREIGN KEY ("paper_id") REFERENCES "public"."papers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint567ALTER TABLE "retractions" ADD CONSTRAINT "retractions_paper_id_papers_id_fk" FOREIGN KEY ("paper_id") REFERENCES "public"."papers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint568ALTER TABLE "cost_records" ADD CONSTRAINT "cost_records_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint569ALTER TABLE "provider_usage" ADD CONSTRAINT "provider_usage_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint570ALTER TABLE "resource_usage" ADD CONSTRAINT "resource_usage_run_id_research_runs_id_fk" FOREIGN KEY ("run_id") REFERENCES "public"."research_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint571CREATE INDEX "audit_events_target_idx" ON "audit_events" USING btree ("target_type","target_id");--> statement-breakpoint572CREATE INDEX "audit_events_created_idx" ON "audit_events" USING btree ("created_at");--> statement-breakpoint573CREATE INDEX "login_attempts_ip_idx" ON "login_attempts" USING btree ("ip","created_at");--> statement-breakpoint574CREATE INDEX "login_attempts_user_idx" ON "login_attempts" USING btree ("username","created_at");--> statement-breakpoint575CREATE INDEX "security_events_kind_idx" ON "security_events" USING btree ("kind","created_at");--> statement-breakpoint576CREATE INDEX "sessions_user_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint577CREATE INDEX "sessions_expires_idx" ON "sessions" USING btree ("expires_at");--> statement-breakpoint578CREATE UNIQUE INDEX "piv_project_version_uq" ON "project_instruction_versions" USING btree ("project_id","version");--> statement-breakpoint579CREATE UNIQUE INDEX "piv_project_sha_uq" ON "project_instruction_versions" USING btree ("project_id","sha256");--> statement-breakpoint580CREATE INDEX "piv_project_idx" ON "project_instruction_versions" USING btree ("project_id");--> statement-breakpoint581CREATE INDEX "checkpoints_run_idx" ON "checkpoints" USING btree ("run_id","created_at");--> statement-breakpoint582CREATE UNIQUE INDEX "runs_idempotency_uq" ON "research_runs" USING btree ("idempotency_key");--> statement-breakpoint583CREATE INDEX "runs_status_idx" ON "research_runs" USING btree ("status","queued_at");--> statement-breakpoint584CREATE INDEX "runs_project_idx" ON "research_runs" USING btree ("project_id");--> statement-breakpoint585CREATE UNIQUE INDEX "attempts_run_no_uq" ON "run_attempts" USING btree ("run_id","attempt_no");--> statement-breakpoint586CREATE INDEX "attempts_run_idx" ON "run_attempts" USING btree ("run_id");--> statement-breakpoint587CREATE UNIQUE INDEX "stage_events_run_seq_uq" ON "stage_events" USING btree ("run_id","seq");--> statement-breakpoint588CREATE UNIQUE INDEX "stage_exec_attempt_stage_uq" ON "stage_executions" USING btree ("attempt_id","stage");--> statement-breakpoint589CREATE INDEX "stage_exec_run_idx" ON "stage_executions" USING btree ("run_id");--> statement-breakpoint590CREATE INDEX "worker_assignments_run_idx" ON "worker_assignments" USING btree ("run_id");--> statement-breakpoint591CREATE INDEX "worker_heartbeats_worker_idx" ON "worker_heartbeats" USING btree ("worker_id","created_at");--> statement-breakpoint592CREATE UNIQUE INDEX "artifacts_run_path_version_uq" ON "artifacts" USING btree ("run_id","path","version");--> statement-breakpoint593CREATE INDEX "artifacts_run_idx" ON "artifacts" USING btree ("run_id");--> statement-breakpoint594CREATE INDEX "citations_run_idx" ON "citations" USING btree ("run_id");--> statement-breakpoint595CREATE INDEX "datasets_run_idx" ON "datasets" USING btree ("run_id");--> statement-breakpoint596CREATE INDEX "experiments_run_idx" ON "experiment_records" USING btree ("run_id");--> statement-breakpoint597CREATE UNIQUE INDEX "sources_run_key_uq" ON "research_sources" USING btree ("run_id","key");--> statement-breakpoint598CREATE UNIQUE INDEX "review_findings_uq" ON "review_findings" USING btree ("run_id","round","finding_key");--> statement-breakpoint599CREATE INDEX "pub_decisions_run_idx" ON "publication_decisions" USING btree ("run_id","created_at");--> statement-breakpoint600CREATE INDEX "qgr_run_idx" ON "quality_gate_results" USING btree ("run_id","evaluation_id");--> statement-breakpoint601CREATE UNIQUE INDEX "paper_versions_uq" ON "paper_versions" USING btree ("paper_id","version_no");--> statement-breakpoint602CREATE UNIQUE INDEX "paper_versions_run_uq" ON "paper_versions" USING btree ("run_id");--> statement-breakpoint603CREATE INDEX "paper_versions_search_idx" ON "paper_versions" USING gin ("search");--> statement-breakpoint604CREATE INDEX "papers_status_idx" ON "papers" USING btree ("status","first_published_at");--> statement-breakpoint605CREATE INDEX "papers_discipline_idx" ON "papers" USING btree ("discipline");--> statement-breakpoint606CREATE INDEX "publication_events_paper_idx" ON "publication_events" USING btree ("paper_id","created_at");--> statement-breakpoint607CREATE INDEX "provider_usage_run_idx" ON "provider_usage" USING btree ("run_id");608--> statement-breakpoint609CREATE INDEX papers_title_trgm_idx ON papers USING gin (title gin_trgm_ops);610