CREATE FUNCTION as_immutable_array_to_string(text[]) RETURNS text IMMUTABLE PARALLEL SAFE LANGUAGE sql AS $$ SELECT coalesce(array_to_string($1, ' '), '') $$;
--> statement-breakpoint
CREATE EXTENSION IF NOT EXISTS pg_trgm;
--> statement-breakpoint
CREATE TABLE "admin_users" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"username" text NOT NULL,
	"password_hash" text NOT NULL,
	"must_change_password" boolean DEFAULT true NOT NULL,
	"bootstrapped" boolean DEFAULT false NOT NULL,
	"totp_secret" text,
	"failed_attempts" integer DEFAULT 0 NOT NULL,
	"locked_until" timestamp with time zone,
	"last_login_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "admin_users_username_unique" UNIQUE("username")
);
--> statement-breakpoint
CREATE TABLE "audit_events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"actor_type" text NOT NULL,
	"actor_id" text,
	"action" text NOT NULL,
	"target_type" text,
	"target_id" text,
	"detail" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"ip" "inet",
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "login_attempts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"username" text NOT NULL,
	"ip" "inet",
	"success" boolean NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "password_changes" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"previous_hash" text NOT NULL,
	"reason" text NOT NULL,
	"ip" "inet",
	"changed_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "security_events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid,
	"kind" text NOT NULL,
	"ip" "inet",
	"detail" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "sessions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"token_hash" text NOT NULL,
	"user_id" uuid NOT NULL,
	"csrf_secret" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"last_seen_at" timestamp with time zone DEFAULT now() NOT NULL,
	"rotated_from" uuid,
	"ip" "inet",
	"user_agent" text,
	"revoked_at" timestamp with time zone,
	CONSTRAINT "sessions_token_hash_unique" UNIQUE("token_hash")
);
--> statement-breakpoint
CREATE TABLE "project_instruction_versions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"project_id" uuid NOT NULL,
	"version" integer NOT NULL,
	"filename" text NOT NULL,
	"sha256" text NOT NULL,
	"byte_size" integer NOT NULL,
	"content_text" text NOT NULL,
	"original_bytes" "bytea" NOT NULL,
	"frontmatter" jsonb,
	"validation" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"note" text,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_projects" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"title" text NOT NULL,
	"discipline" text NOT NULL,
	"description" text DEFAULT '' NOT NULL,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	"archived_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "research_templates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"name" text NOT NULL,
	"mode" text NOT NULL,
	"description" text DEFAULT '' NOT NULL,
	"content" text NOT NULL,
	"version" integer DEFAULT 1 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "research_templates_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "checkpoints" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"attempt_id" uuid NOT NULL,
	"stage" text NOT NULL,
	"data" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "job_leases" (
	"run_id" uuid PRIMARY KEY NOT NULL,
	"attempt_id" uuid NOT NULL,
	"worker_id" uuid NOT NULL,
	"fencing_token" bigint NOT NULL,
	"acquired_at" timestamp with time zone DEFAULT now() NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"last_heartbeat_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_runs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"project_id" uuid NOT NULL,
	"instruction_version_id" uuid NOT NULL,
	"idempotency_key" text NOT NULL,
	"title" text NOT NULL,
	"status" text DEFAULT 'draft' NOT NULL,
	"research_mode" text NOT NULL,
	"secondary_modes" text[] DEFAULT '{}' NOT NULL,
	"discipline" text NOT NULL,
	"compute_profile" text DEFAULT 'standard' NOT NULL,
	"provider" text DEFAULT 'anthropic' NOT NULL,
	"model" text NOT NULL,
	"reviewer_model" text NOT NULL,
	"max_budget_usd" numeric(10, 2) NOT NULL,
	"max_runtime_minutes" integer NOT NULL,
	"max_attempts" integer DEFAULT 2 NOT NULL,
	"auto_publish" boolean DEFAULT false NOT NULL,
	"quality_policy_version" text NOT NULL,
	"manifest" jsonb NOT NULL,
	"manifest_sha256" text NOT NULL,
	"current_attempt_no" integer DEFAULT 0 NOT NULL,
	"current_stage" text,
	"fencing_token" bigint DEFAULT 0 NOT NULL,
	"last_event_seq" bigint DEFAULT 0 NOT NULL,
	"cancel_requested" boolean DEFAULT false NOT NULL,
	"failure_reason" text,
	"total_cost_usd" numeric(10, 4) DEFAULT '0' NOT NULL,
	"paper_id" uuid,
	"queued_at" timestamp with time zone,
	"started_at" timestamp with time zone,
	"finished_at" timestamp with time zone,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "run_attempts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"attempt_no" integer NOT NULL,
	"worker_id" uuid,
	"status" text DEFAULT 'leased' NOT NULL,
	"fencing_token" bigint NOT NULL,
	"resume_from_stage" text,
	"started_at" timestamp with time zone DEFAULT now() NOT NULL,
	"ended_at" timestamp with time zone,
	"error" text,
	"cost_usd" numeric(10, 4) DEFAULT '0' NOT NULL,
	"summary" jsonb
);
--> statement-breakpoint
CREATE TABLE "stage_events" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"run_id" uuid NOT NULL,
	"attempt_id" uuid,
	"worker_id" uuid,
	"seq" bigint NOT NULL,
	"stage" text,
	"level" text DEFAULT 'info' NOT NULL,
	"kind" text NOT NULL,
	"message" text NOT NULL,
	"data" jsonb,
	"at" timestamp with time zone NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "stage_executions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"attempt_id" uuid NOT NULL,
	"stage" text NOT NULL,
	"status" text DEFAULT 'pending' NOT NULL,
	"started_at" timestamp with time zone DEFAULT now() NOT NULL,
	"ended_at" timestamp with time zone,
	"cost_usd" numeric(10, 4) DEFAULT '0' NOT NULL,
	"turns" integer DEFAULT 0 NOT NULL,
	"usage" jsonb,
	"error" text
);
--> statement-breakpoint
CREATE TABLE "compute_workers" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" text NOT NULL,
	"token_hash" text NOT NULL,
	"status" text DEFAULT 'offline' NOT NULL,
	"version" text,
	"protocol_version" integer DEFAULT 1 NOT NULL,
	"lan_ip" text,
	"node_meta" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"profiles" text[] DEFAULT '{"standard"}' NOT NULL,
	"capacity" integer DEFAULT 1 NOT NULL,
	"running" integer DEFAULT 0 NOT NULL,
	"enrolled_at" timestamp with time zone DEFAULT now() NOT NULL,
	"last_heartbeat_at" timestamp with time zone,
	"drain_requested_at" timestamp with time zone,
	"quarantine_reason" text,
	"disabled_at" timestamp with time zone,
	"token_rotated_at" timestamp with time zone,
	CONSTRAINT "compute_workers_name_unique" UNIQUE("name")
);
--> statement-breakpoint
CREATE TABLE "resource_profiles" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"name" text NOT NULL,
	"description" text DEFAULT '' NOT NULL,
	"cpu_cores" integer NOT NULL,
	"ram_gb" integer NOT NULL,
	"max_runtime_minutes" integer NOT NULL,
	"default_budget_usd" integer NOT NULL,
	"requires" text[] DEFAULT '{}' NOT NULL,
	CONSTRAINT "resource_profiles_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "worker_assignments" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"attempt_id" uuid NOT NULL,
	"worker_id" uuid NOT NULL,
	"assigned_at" timestamp with time zone DEFAULT now() NOT NULL,
	"released_at" timestamp with time zone,
	"release_reason" text
);
--> statement-breakpoint
CREATE TABLE "worker_heartbeats" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"worker_id" uuid NOT NULL,
	"load" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"running" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "artifacts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"attempt_id" uuid,
	"stage" text NOT NULL,
	"path" text NOT NULL,
	"version" integer DEFAULT 1 NOT NULL,
	"kind" text NOT NULL,
	"sha256" text NOT NULL,
	"bytes" bigint NOT NULL,
	"content_type" text DEFAULT 'application/octet-stream' NOT NULL,
	"storage_key" text NOT NULL,
	"visibility" text DEFAULT 'private' NOT NULL,
	"synthetic" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "citations" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"claim" text NOT NULL,
	"location" text,
	"kind" text DEFAULT 'literature' NOT NULL,
	"source_keys" text[] DEFAULT '{}' NOT NULL,
	"artifact_paths" text[] DEFAULT '{}' NOT NULL
);
--> statement-breakpoint
CREATE TABLE "datasets" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"name" text NOT NULL,
	"source" text NOT NULL,
	"url" text,
	"license" text NOT NULL,
	"acquired_at" text NOT NULL,
	"sha256" text,
	"bytes" bigint,
	"synthetic" boolean DEFAULT false NOT NULL,
	"transformations" text[] DEFAULT '{}' NOT NULL,
	"notes" text
);
--> statement-breakpoint
CREATE TABLE "experiment_records" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"name" text NOT NULL,
	"command" text NOT NULL,
	"started_at" text NOT NULL,
	"ended_at" text NOT NULL,
	"exit_code" integer NOT NULL,
	"seed" text,
	"environment" jsonb,
	"outputs" text[] DEFAULT '{}' NOT NULL,
	"synthetic" boolean DEFAULT false NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_sources" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"key" text NOT NULL,
	"kind" text NOT NULL,
	"identifier" text NOT NULL,
	"title" text NOT NULL,
	"authors" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"year" integer,
	"venue" text,
	"url" text,
	"verified" boolean DEFAULT false NOT NULL,
	"verified_at" timestamp with time zone,
	"verification" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"access_level" text DEFAULT 'metadata' NOT NULL
);
--> statement-breakpoint
CREATE TABLE "review_findings" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"round" integer NOT NULL,
	"finding_key" text NOT NULL,
	"severity" text NOT NULL,
	"category" text NOT NULL,
	"title" text NOT NULL,
	"detail" text NOT NULL,
	"proposed_test" text,
	"resolution" text DEFAULT 'open' NOT NULL,
	"resolution_note" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "publication_decisions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"evaluation_id" uuid,
	"decision" text NOT NULL,
	"decided_by" text NOT NULL,
	"rationale" text NOT NULL,
	"override" boolean DEFAULT false NOT NULL,
	"override_rationale" text,
	"gate_summary" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "quality_gate_results" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"evaluation_id" uuid NOT NULL,
	"policy_version" text NOT NULL,
	"mode" text NOT NULL,
	"gate" text NOT NULL,
	"severity" text NOT NULL,
	"status" text NOT NULL,
	"message" text NOT NULL,
	"evidence" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "quality_policies" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"version" text NOT NULL,
	"active" boolean DEFAULT false NOT NULL,
	"profiles" jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "quality_policies_version_unique" UNIQUE("version")
);
--> statement-breakpoint
CREATE TABLE "corrections" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"paper_id" uuid NOT NULL,
	"from_version_no" integer NOT NULL,
	"to_version_no" integer NOT NULL,
	"notice" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "paper_sequences" (
	"year" integer PRIMARY KEY NOT NULL,
	"last" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "paper_series" (
	"paper_id" uuid NOT NULL,
	"series_id" uuid NOT NULL,
	CONSTRAINT "paper_series_paper_id_series_id_pk" PRIMARY KEY("paper_id","series_id")
);
--> statement-breakpoint
CREATE TABLE "paper_topics" (
	"paper_id" uuid NOT NULL,
	"topic_id" uuid NOT NULL,
	CONSTRAINT "paper_topics_paper_id_topic_id_pk" PRIMARY KEY("paper_id","topic_id")
);
--> statement-breakpoint
CREATE TABLE "paper_versions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"paper_id" uuid NOT NULL,
	"version_no" integer NOT NULL,
	"run_id" uuid NOT NULL,
	"title" text NOT NULL,
	"abstract" text NOT NULL,
	"keywords" text[] DEFAULT '{}' NOT NULL,
	"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"artifact_set" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"pdf_public_path" text NOT NULL,
	"qa_summary" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"provenance" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"changelog" text DEFAULT '' NOT NULL,
	"published_at" timestamp with time zone DEFAULT now() NOT NULL,
	"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 NULL
);
--> statement-breakpoint
CREATE TABLE "papers" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"public_id" text NOT NULL,
	"slug" text NOT NULL,
	"title" text NOT NULL,
	"status" text DEFAULT 'published' NOT NULL,
	"discipline" text NOT NULL,
	"research_mode" text NOT NULL,
	"current_version_id" uuid,
	"series_id" uuid,
	"first_published_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	"retracted_at" timestamp with time zone,
	"synthetic" boolean DEFAULT false NOT NULL,
	CONSTRAINT "papers_public_id_unique" UNIQUE("public_id"),
	CONSTRAINT "papers_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "publication_events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"paper_id" uuid NOT NULL,
	"version_id" uuid,
	"kind" text NOT NULL,
	"actor" text NOT NULL,
	"detail" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "retractions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"paper_id" uuid NOT NULL,
	"reason" text NOT NULL,
	"notice" text NOT NULL,
	"retracted_by" text NOT NULL,
	"retracted_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "series" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"name" text NOT NULL,
	"description" text DEFAULT '' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "series_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "topics" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"name" text NOT NULL,
	"description" text DEFAULT '' NOT NULL,
	"parent_id" uuid,
	"sort_order" integer DEFAULT 0 NOT NULL,
	CONSTRAINT "topics_slug_unique" UNIQUE("slug")
);
--> statement-breakpoint
CREATE TABLE "cost_records" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid,
	"kind" text NOT NULL,
	"amount_usd" numeric(10, 4) NOT NULL,
	"detail" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "provider_usage" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"attempt_id" uuid,
	"stage" text,
	"provider" text DEFAULT 'anthropic' NOT NULL,
	"model" text NOT NULL,
	"input_tokens" bigint DEFAULT 0 NOT NULL,
	"output_tokens" bigint DEFAULT 0 NOT NULL,
	"cache_read_tokens" bigint DEFAULT 0 NOT NULL,
	"cache_create_tokens" bigint DEFAULT 0 NOT NULL,
	"cost_usd" numeric(10, 4) DEFAULT '0' NOT NULL,
	"turns" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "resource_usage" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"attempt_id" uuid,
	"worker_id" uuid,
	"cpu_seconds" numeric(12, 2),
	"wall_seconds" numeric(12, 2),
	"disk_bytes" bigint,
	"peak_rss_bytes" bigint,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "platform_settings" (
	"key" text PRIMARY KEY NOT NULL,
	"value" jsonb NOT NULL,
	"updated_by" text,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
ALTER 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-breakpoint
CREATE INDEX "audit_events_target_idx" ON "audit_events" USING btree ("target_type","target_id");--> statement-breakpoint
CREATE INDEX "audit_events_created_idx" ON "audit_events" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "login_attempts_ip_idx" ON "login_attempts" USING btree ("ip","created_at");--> statement-breakpoint
CREATE INDEX "login_attempts_user_idx" ON "login_attempts" USING btree ("username","created_at");--> statement-breakpoint
CREATE INDEX "security_events_kind_idx" ON "security_events" USING btree ("kind","created_at");--> statement-breakpoint
CREATE INDEX "sessions_user_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "sessions_expires_idx" ON "sessions" USING btree ("expires_at");--> statement-breakpoint
CREATE UNIQUE INDEX "piv_project_version_uq" ON "project_instruction_versions" USING btree ("project_id","version");--> statement-breakpoint
CREATE UNIQUE INDEX "piv_project_sha_uq" ON "project_instruction_versions" USING btree ("project_id","sha256");--> statement-breakpoint
CREATE INDEX "piv_project_idx" ON "project_instruction_versions" USING btree ("project_id");--> statement-breakpoint
CREATE INDEX "checkpoints_run_idx" ON "checkpoints" USING btree ("run_id","created_at");--> statement-breakpoint
CREATE UNIQUE INDEX "runs_idempotency_uq" ON "research_runs" USING btree ("idempotency_key");--> statement-breakpoint
CREATE INDEX "runs_status_idx" ON "research_runs" USING btree ("status","queued_at");--> statement-breakpoint
CREATE INDEX "runs_project_idx" ON "research_runs" USING btree ("project_id");--> statement-breakpoint
CREATE UNIQUE INDEX "attempts_run_no_uq" ON "run_attempts" USING btree ("run_id","attempt_no");--> statement-breakpoint
CREATE INDEX "attempts_run_idx" ON "run_attempts" USING btree ("run_id");--> statement-breakpoint
CREATE UNIQUE INDEX "stage_events_run_seq_uq" ON "stage_events" USING btree ("run_id","seq");--> statement-breakpoint
CREATE UNIQUE INDEX "stage_exec_attempt_stage_uq" ON "stage_executions" USING btree ("attempt_id","stage");--> statement-breakpoint
CREATE INDEX "stage_exec_run_idx" ON "stage_executions" USING btree ("run_id");--> statement-breakpoint
CREATE INDEX "worker_assignments_run_idx" ON "worker_assignments" USING btree ("run_id");--> statement-breakpoint
CREATE INDEX "worker_heartbeats_worker_idx" ON "worker_heartbeats" USING btree ("worker_id","created_at");--> statement-breakpoint
CREATE UNIQUE INDEX "artifacts_run_path_version_uq" ON "artifacts" USING btree ("run_id","path","version");--> statement-breakpoint
CREATE INDEX "artifacts_run_idx" ON "artifacts" USING btree ("run_id");--> statement-breakpoint
CREATE INDEX "citations_run_idx" ON "citations" USING btree ("run_id");--> statement-breakpoint
CREATE INDEX "datasets_run_idx" ON "datasets" USING btree ("run_id");--> statement-breakpoint
CREATE INDEX "experiments_run_idx" ON "experiment_records" USING btree ("run_id");--> statement-breakpoint
CREATE UNIQUE INDEX "sources_run_key_uq" ON "research_sources" USING btree ("run_id","key");--> statement-breakpoint
CREATE UNIQUE INDEX "review_findings_uq" ON "review_findings" USING btree ("run_id","round","finding_key");--> statement-breakpoint
CREATE INDEX "pub_decisions_run_idx" ON "publication_decisions" USING btree ("run_id","created_at");--> statement-breakpoint
CREATE INDEX "qgr_run_idx" ON "quality_gate_results" USING btree ("run_id","evaluation_id");--> statement-breakpoint
CREATE UNIQUE INDEX "paper_versions_uq" ON "paper_versions" USING btree ("paper_id","version_no");--> statement-breakpoint
CREATE UNIQUE INDEX "paper_versions_run_uq" ON "paper_versions" USING btree ("run_id");--> statement-breakpoint
CREATE INDEX "paper_versions_search_idx" ON "paper_versions" USING gin ("search");--> statement-breakpoint
CREATE INDEX "papers_status_idx" ON "papers" USING btree ("status","first_published_at");--> statement-breakpoint
CREATE INDEX "papers_discipline_idx" ON "papers" USING btree ("discipline");--> statement-breakpoint
CREATE INDEX "publication_events_paper_idx" ON "publication_events" USING btree ("paper_id","created_at");--> statement-breakpoint
CREATE INDEX "provider_usage_run_idx" ON "provider_usage" USING btree ("run_id");
--> statement-breakpoint
CREATE INDEX papers_title_trgm_idx ON papers USING gin (title gin_trgm_ops);