CREATE EXTENSION IF NOT EXISTS pgcrypto;
--> statement-breakpoint
CREATE TABLE "audit_events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"occurred_at" timestamp with time zone DEFAULT now() NOT NULL,
	"actor_user_id" uuid,
	"workspace_id" uuid,
	"action" text NOT NULL,
	"resource_type" text NOT NULL,
	"resource_id" text,
	"request_id" text,
	"outcome" text NOT NULL,
	"metadata_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	CONSTRAINT "audit_events_outcome_check" CHECK ("audit_events"."outcome" in ('success', 'denied', 'failed'))
);
--> statement-breakpoint
CREATE TABLE "auth_sessions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"token_hash" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"last_seen_at" timestamp with time zone DEFAULT now() NOT NULL,
	"idle_expires_at" timestamp with time zone NOT NULL,
	"absolute_expires_at" timestamp with time zone NOT NULL,
	"revoked_at" timestamp with time zone,
	"source_ip_hash" text,
	"user_agent_summary" text,
	CONSTRAINT "auth_sessions_token_hash_uq" UNIQUE("token_hash")
);
--> statement-breakpoint
CREATE TABLE "collection_items" (
	"collection_id" uuid NOT NULL,
	"playbook_id" uuid NOT NULL,
	"position" integer DEFAULT 0 NOT NULL,
	"added_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "collection_items_pkey" PRIMARY KEY("collection_id","playbook_id")
);
--> statement-breakpoint
CREATE TABLE "collections" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"workspace_id" uuid NOT NULL,
	"name" text NOT NULL,
	"description" text DEFAULT '' NOT NULL,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "collections_workspace_name_uq" UNIQUE("workspace_id","name")
);
--> statement-breakpoint
CREATE TABLE "composition_drafts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"workspace_id" uuid NOT NULL,
	"playbook_version_id" uuid NOT NULL,
	"repository_profile_revision_id" uuid,
	"input_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"scope_override_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"autonomy_level" text NOT NULL,
	"work_mode" text NOT NULL,
	"last_render_digest" text,
	"created_by" uuid 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 "evaluation_cases" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"playbook_version_id" uuid NOT NULL,
	"logical_case_id" text NOT NULL,
	"fixture_version" text NOT NULL,
	"case_json" jsonb NOT NULL,
	"case_digest" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "evaluation_cases_identity_uq" UNIQUE("playbook_version_id","logical_case_id","fixture_version")
);
--> statement-breakpoint
CREATE TABLE "evaluation_results" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"evaluation_case_id" uuid NOT NULL,
	"environment_json" jsonb NOT NULL,
	"status" text NOT NULL,
	"dimension_scores_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"evidence_artifact_id" uuid,
	"executed_by" uuid,
	"executed_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "evaluation_results_status_check" CHECK ("evaluation_results"."status" in ('passed', 'failed', 'error', 'skipped', 'stale'))
);
--> statement-breakpoint
CREATE TABLE "favorites" (
	"workspace_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"playbook_id" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "favorites_pkey" PRIMARY KEY("workspace_id","user_id","playbook_id")
);
--> statement-breakpoint
CREATE TABLE "generated_artifacts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"artifact_type" text NOT NULL,
	"storage_key" text NOT NULL,
	"filename" text NOT NULL,
	"media_type" text NOT NULL,
	"size_bytes" bigint NOT NULL,
	"sha256" text NOT NULL,
	"expires_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "generated_artifacts_storage_key_uq" UNIQUE("storage_key"),
	CONSTRAINT "generated_artifacts_type_check" CHECK ("generated_artifacts"."artifact_type" in ('prompt_text', 'markdown', 'run_pack_zip', 'agents_suggestion', 'support_bundle')),
	CONSTRAINT "generated_artifacts_size_check" CHECK ("generated_artifacts"."size_bytes" >= 0)
);
--> statement-breakpoint
CREATE TABLE "generated_runs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"workspace_id" uuid NOT NULL,
	"source_draft_id" uuid,
	"playbook_version_id" uuid NOT NULL,
	"playbook_snapshot_json" jsonb NOT NULL,
	"repository_profile_snapshot_json" jsonb,
	"normalized_input_json" jsonb NOT NULL,
	"policy_snapshot_json" jsonb NOT NULL,
	"provenance_json" jsonb NOT NULL,
	"lint_result_json" jsonb NOT NULL,
	"rendered_prompt" text NOT NULL,
	"render_digest" text NOT NULL,
	"idempotency_key" text,
	"generated_by" uuid NOT NULL,
	"generated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "generated_runs_workspace_idempotency_uq" UNIQUE("workspace_id","idempotency_key")
);
--> statement-breakpoint
CREATE TABLE "instance_settings" (
	"singleton" boolean PRIMARY KEY DEFAULT true NOT NULL,
	"setup_completed_at" timestamp with time zone,
	"owner_user_id" uuid,
	"config_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"config_digest" text,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "instance_settings_singleton_check" CHECK ("instance_settings"."singleton")
);
--> statement-breakpoint
CREATE TABLE "integration_secrets" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"integration_id" uuid NOT NULL,
	"secret_kind" text NOT NULL,
	"envelope_version" integer NOT NULL,
	"key_version" text NOT NULL,
	"nonce" bytea NOT NULL,
	"ciphertext" bytea NOT NULL,
	"auth_tag" bytea NOT NULL,
	"last_four" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"rotated_at" timestamp with time zone,
	CONSTRAINT "integration_secrets_integration_kind_uq" UNIQUE("integration_id","secret_kind")
);
--> statement-breakpoint
CREATE TABLE "integrations" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"workspace_id" uuid NOT NULL,
	"type" text NOT NULL,
	"display_name" text NOT NULL,
	"base_url" text NOT NULL,
	"status" text DEFAULT 'configured' NOT NULL,
	"capabilities_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"last_checked_at" timestamp with time zone,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "integrations_workspace_type_base_url_uq" UNIQUE("workspace_id","type","base_url"),
	CONSTRAINT "integrations_type_check" CHECK ("integrations"."type" in ('gitea')),
	CONSTRAINT "integrations_status_check" CHECK ("integrations"."status" in ('configured', 'healthy', 'degraded', 'disabled'))
);
--> statement-breakpoint
CREATE TABLE "invitations" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"email" text NOT NULL,
	"token_hash" text NOT NULL,
	"instance_role" text NOT NULL,
	"workspace_id" uuid,
	"workspace_role" text,
	"expires_at" timestamp with time zone NOT NULL,
	"accepted_at" timestamp with time zone,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "invitations_token_hash_uq" UNIQUE("token_hash"),
	CONSTRAINT "invitations_instance_role_check" CHECK ("invitations"."instance_role" in ('instance_admin', 'user')),
	CONSTRAINT "invitations_workspace_role_check" CHECK ("invitations"."workspace_role" is null or "invitations"."workspace_role" in ('owner', 'editor', 'viewer'))
);
--> statement-breakpoint
CREATE TABLE "jobs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"workspace_id" uuid,
	"type" text NOT NULL,
	"state" text NOT NULL,
	"idempotency_key" text,
	"payload_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"progress_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"attempt_count" integer DEFAULT 0 NOT NULL,
	"max_attempts" integer DEFAULT 3 NOT NULL,
	"lease_owner" text,
	"lease_expires_at" timestamp with time zone,
	"available_at" timestamp with time zone DEFAULT now() NOT NULL,
	"started_at" timestamp with time zone,
	"finished_at" timestamp with time zone,
	"error_code" text,
	"error_detail_redacted" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "jobs_state_check" CHECK ("jobs"."state" in ('queued', 'running', 'succeeded', 'failed', 'cancelled')),
	CONSTRAINT "jobs_attempt_count_check" CHECK ("jobs"."attempt_count" >= 0),
	CONSTRAINT "jobs_max_attempts_check" CHECK ("jobs"."max_attempts" > 0)
);
--> statement-breakpoint
CREATE TABLE "password_reset_tokens" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"user_id" uuid NOT NULL,
	"token_hash" text NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"used_at" timestamp with time zone,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "password_reset_tokens_token_hash_uq" UNIQUE("token_hash")
);
--> statement-breakpoint
CREATE TABLE "playbook_versions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"playbook_id" uuid NOT NULL,
	"semantic_version" text NOT NULL,
	"lifecycle" text NOT NULL,
	"package_api_version" text NOT NULL,
	"title" text NOT NULL,
	"summary" text NOT NULL,
	"category" text NOT NULL,
	"risk_tier" text NOT NULL,
	"package_json" jsonb NOT NULL,
	"template_text" text NOT NULL,
	"content_digest" text NOT NULL,
	"search_document" tsvector,
	"published_at" timestamp with time zone,
	"supersedes_version_id" uuid,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "playbook_versions_playbook_semver_uq" UNIQUE("playbook_id","semantic_version"),
	CONSTRAINT "playbook_versions_lifecycle_check" CHECK ("playbook_versions"."lifecycle" in ('draft', 'reviewed', 'validated', 'battle-tested', 'deprecated')),
	CONSTRAINT "playbook_versions_risk_tier_check" CHECK ("playbook_versions"."risk_tier" in ('low', 'moderate', 'high', 'critical'))
);
--> statement-breakpoint
CREATE TABLE "playbooks" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"workspace_id" uuid,
	"logical_id" text NOT NULL,
	"slug" text NOT NULL,
	"namespace" text NOT NULL,
	"source_type" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "playbooks_namespace_logical_id_uq" UNIQUE("namespace","logical_id"),
	CONSTRAINT "playbooks_namespace_slug_uq" UNIQUE("namespace","slug"),
	CONSTRAINT "playbooks_source_type_check" CHECK ("playbooks"."source_type" in ('built_in', 'private', 'imported', 'remote_registry'))
);
--> statement-breakpoint
CREATE TABLE "repositories" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"workspace_id" uuid NOT NULL,
	"display_name" text NOT NULL,
	"source_type" text NOT NULL,
	"external_owner" text,
	"external_name" text,
	"external_id" text,
	"integration_id" uuid,
	"default_branch" text,
	"archived" 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,
	CONSTRAINT "repositories_source_type_check" CHECK ("repositories"."source_type" in ('manual', 'gitea'))
);
--> statement-breakpoint
CREATE TABLE "repository_findings" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"snapshot_id" uuid NOT NULL,
	"rule_id" text NOT NULL,
	"severity" text NOT NULL,
	"title" text NOT NULL,
	"rationale" text NOT NULL,
	"evidence_pointer" text NOT NULL,
	"recommended_playbook_slug" text,
	"status" text DEFAULT 'open' NOT NULL,
	"resolution_note" text,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "repository_findings_evidence_uq" UNIQUE("snapshot_id","rule_id","evidence_pointer"),
	CONSTRAINT "repository_findings_severity_check" CHECK ("repository_findings"."severity" in ('info', 'low', 'medium', 'high', 'critical')),
	CONSTRAINT "repository_findings_status_check" CHECK ("repository_findings"."status" in ('open', 'dismissed', 'resolved'))
);
--> statement-breakpoint
CREATE TABLE "repository_profile_revisions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"repository_id" uuid NOT NULL,
	"revision_number" integer NOT NULL,
	"profile_json" jsonb NOT NULL,
	"source_snapshot_id" uuid,
	"content_digest" text NOT NULL,
	"created_by" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "repository_profile_revisions_repository_number_uq" UNIQUE("repository_id","revision_number"),
	CONSTRAINT "repository_profile_revisions_repository_digest_uq" UNIQUE("repository_id","content_digest")
);
--> statement-breakpoint
CREATE TABLE "repository_snapshots" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"repository_id" uuid NOT NULL,
	"integration_id" uuid,
	"state" text NOT NULL,
	"captured_at" timestamp with time zone,
	"capability_snapshot_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"evidence_json" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"evidence_digest" text,
	"sync_job_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "repository_snapshots_state_check" CHECK ("repository_snapshots"."state" in ('collecting', 'complete', 'failed', 'cancelled'))
);
--> statement-breakpoint
CREATE TABLE "run_feedback" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"run_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"rating" text,
	"notes" text DEFAULT '' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "run_feedback_run_user_uq" UNIQUE("run_id","user_id"),
	CONSTRAINT "run_feedback_rating_check" CHECK ("run_feedback"."rating" is null or "run_feedback"."rating" in ('helpful', 'mixed', 'unhelpful'))
);
--> statement-breakpoint
CREATE TABLE "users" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"email" text NOT NULL,
	"display_name" text NOT NULL,
	"password_hash" text NOT NULL,
	"instance_role" text NOT NULL,
	"status" text DEFAULT 'active' NOT NULL,
	"password_changed_at" timestamp with time zone DEFAULT now() NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	"deleted_at" timestamp with time zone,
	CONSTRAINT "users_instance_role_check" CHECK ("users"."instance_role" in ('instance_owner', 'instance_admin', 'user')),
	CONSTRAINT "users_status_check" CHECK ("users"."status" in ('active', 'disabled', 'pending_deletion'))
);
--> statement-breakpoint
CREATE TABLE "workspace_memberships" (
	"workspace_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"role" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "workspace_memberships_pkey" PRIMARY KEY("workspace_id","user_id"),
	CONSTRAINT "workspace_memberships_role_check" CHECK ("workspace_memberships"."role" in ('owner', 'editor', 'viewer'))
);
--> statement-breakpoint
CREATE TABLE "workspaces" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" text NOT NULL,
	"type" text DEFAULT 'personal' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	"deleted_at" timestamp with time zone,
	CONSTRAINT "workspaces_type_check" CHECK ("workspaces"."type" in ('personal', 'team'))
);
--> statement-breakpoint
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_actor_user_fk" FOREIGN KEY ("actor_user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "auth_sessions" ADD CONSTRAINT "auth_sessions_user_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "collection_items" ADD CONSTRAINT "collection_items_collection_fk" FOREIGN KEY ("collection_id") REFERENCES "public"."collections"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "collection_items" ADD CONSTRAINT "collection_items_playbook_fk" FOREIGN KEY ("playbook_id") REFERENCES "public"."playbooks"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "collections" ADD CONSTRAINT "collections_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "collections" ADD CONSTRAINT "collections_created_by_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "composition_drafts" ADD CONSTRAINT "composition_drafts_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "composition_drafts" ADD CONSTRAINT "composition_drafts_playbook_version_fk" FOREIGN KEY ("playbook_version_id") REFERENCES "public"."playbook_versions"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "composition_drafts" ADD CONSTRAINT "composition_drafts_profile_revision_fk" FOREIGN KEY ("repository_profile_revision_id") REFERENCES "public"."repository_profile_revisions"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "composition_drafts" ADD CONSTRAINT "composition_drafts_created_by_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "evaluation_cases" ADD CONSTRAINT "evaluation_cases_playbook_version_fk" FOREIGN KEY ("playbook_version_id") REFERENCES "public"."playbook_versions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "evaluation_results" ADD CONSTRAINT "evaluation_results_case_fk" FOREIGN KEY ("evaluation_case_id") REFERENCES "public"."evaluation_cases"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "evaluation_results" ADD CONSTRAINT "evaluation_results_artifact_fk" FOREIGN KEY ("evidence_artifact_id") REFERENCES "public"."generated_artifacts"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "evaluation_results" ADD CONSTRAINT "evaluation_results_executed_by_fk" FOREIGN KEY ("executed_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "favorites" ADD CONSTRAINT "favorites_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "favorites" ADD CONSTRAINT "favorites_user_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "favorites" ADD CONSTRAINT "favorites_playbook_fk" FOREIGN KEY ("playbook_id") REFERENCES "public"."playbooks"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "generated_artifacts" ADD CONSTRAINT "generated_artifacts_run_fk" FOREIGN KEY ("run_id") REFERENCES "public"."generated_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "generated_runs" ADD CONSTRAINT "generated_runs_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "generated_runs" ADD CONSTRAINT "generated_runs_source_draft_fk" FOREIGN KEY ("source_draft_id") REFERENCES "public"."composition_drafts"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "generated_runs" ADD CONSTRAINT "generated_runs_playbook_version_fk" FOREIGN KEY ("playbook_version_id") REFERENCES "public"."playbook_versions"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "generated_runs" ADD CONSTRAINT "generated_runs_generated_by_fk" FOREIGN KEY ("generated_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "instance_settings" ADD CONSTRAINT "instance_settings_owner_user_fk" FOREIGN KEY ("owner_user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "integration_secrets" ADD CONSTRAINT "integration_secrets_integration_fk" FOREIGN KEY ("integration_id") REFERENCES "public"."integrations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "integrations" ADD CONSTRAINT "integrations_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "integrations" ADD CONSTRAINT "integrations_created_by_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invitations" ADD CONSTRAINT "invitations_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "invitations" ADD CONSTRAINT "invitations_created_by_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "jobs" ADD CONSTRAINT "jobs_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "password_reset_tokens" ADD CONSTRAINT "password_reset_tokens_user_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "password_reset_tokens" ADD CONSTRAINT "password_reset_tokens_created_by_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "playbook_versions" ADD CONSTRAINT "playbook_versions_playbook_fk" FOREIGN KEY ("playbook_id") REFERENCES "public"."playbooks"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "playbook_versions" ADD CONSTRAINT "playbook_versions_supersedes_fk" FOREIGN KEY ("supersedes_version_id") REFERENCES "public"."playbook_versions"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "playbook_versions" ADD CONSTRAINT "playbook_versions_created_by_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "playbooks" ADD CONSTRAINT "playbooks_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "repositories" ADD CONSTRAINT "repositories_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "repositories" ADD CONSTRAINT "repositories_integration_fk" FOREIGN KEY ("integration_id") REFERENCES "public"."integrations"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "repository_findings" ADD CONSTRAINT "repository_findings_snapshot_fk" FOREIGN KEY ("snapshot_id") REFERENCES "public"."repository_snapshots"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "repository_profile_revisions" ADD CONSTRAINT "repository_profile_revisions_repository_fk" FOREIGN KEY ("repository_id") REFERENCES "public"."repositories"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "repository_profile_revisions" ADD CONSTRAINT "repository_profile_revisions_snapshot_fk" FOREIGN KEY ("source_snapshot_id") REFERENCES "public"."repository_snapshots"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "repository_profile_revisions" ADD CONSTRAINT "repository_profile_revisions_created_by_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "repository_snapshots" ADD CONSTRAINT "repository_snapshots_repository_fk" FOREIGN KEY ("repository_id") REFERENCES "public"."repositories"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "repository_snapshots" ADD CONSTRAINT "repository_snapshots_integration_fk" FOREIGN KEY ("integration_id") REFERENCES "public"."integrations"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "repository_snapshots" ADD CONSTRAINT "repository_snapshots_job_fk" FOREIGN KEY ("sync_job_id") REFERENCES "public"."jobs"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "run_feedback" ADD CONSTRAINT "run_feedback_run_fk" FOREIGN KEY ("run_id") REFERENCES "public"."generated_runs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "run_feedback" ADD CONSTRAINT "run_feedback_user_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "workspace_memberships" ADD CONSTRAINT "workspace_memberships_workspace_fk" FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "workspace_memberships" ADD CONSTRAINT "workspace_memberships_user_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "audit_events_workspace_time_idx" ON "audit_events" USING btree ("workspace_id","occurred_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "audit_events_action_time_idx" ON "audit_events" USING btree ("action","occurred_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "auth_sessions_user_active_idx" ON "auth_sessions" USING btree ("user_id","absolute_expires_at") WHERE "auth_sessions"."revoked_at" is null;--> statement-breakpoint
CREATE INDEX "composition_drafts_workspace_updated_idx" ON "composition_drafts" USING btree ("workspace_id","updated_at" DESC NULLS LAST);--> statement-breakpoint
CREATE UNIQUE INDEX "generated_runs_digest_actor_uq" ON "generated_runs" USING btree ("workspace_id","generated_by","render_digest","generated_at");--> statement-breakpoint
CREATE INDEX "generated_runs_workspace_time_idx" ON "generated_runs" USING btree ("workspace_id","generated_at" DESC NULLS LAST);--> statement-breakpoint
CREATE UNIQUE INDEX "jobs_workspace_type_idempotency_uq" ON "jobs" USING btree ("workspace_id","type","idempotency_key") WHERE "jobs"."workspace_id" is not null and "jobs"."idempotency_key" is not null;--> statement-breakpoint
CREATE UNIQUE INDEX "jobs_global_type_idempotency_uq" ON "jobs" USING btree ("type","idempotency_key") WHERE "jobs"."workspace_id" is null and "jobs"."idempotency_key" is not null;--> statement-breakpoint
CREATE INDEX "jobs_claim_idx" ON "jobs" USING btree ("state","available_at","created_at") WHERE "jobs"."state" = 'queued';--> statement-breakpoint
CREATE INDEX "jobs_lease_idx" ON "jobs" USING btree ("state","lease_expires_at") WHERE "jobs"."state" = 'running';--> statement-breakpoint
CREATE INDEX "playbook_versions_search_idx" ON "playbook_versions" USING gin ("search_document");--> statement-breakpoint
CREATE INDEX "playbook_versions_filters_idx" ON "playbook_versions" USING btree ("category","risk_tier","lifecycle","published_at" DESC NULLS LAST);--> statement-breakpoint
CREATE INDEX "playbooks_workspace_idx" ON "playbooks" USING btree ("workspace_id");--> statement-breakpoint
CREATE UNIQUE INDEX "repositories_external_uq" ON "repositories" USING btree ("workspace_id","integration_id","external_id") WHERE "repositories"."external_id" is not null;--> statement-breakpoint
CREATE INDEX "repository_snapshots_repo_time_idx" ON "repository_snapshots" USING btree ("repository_id","captured_at" DESC NULLS LAST);--> statement-breakpoint
CREATE UNIQUE INDEX "users_email_ci_uq" ON "users" USING btree (lower("email")) WHERE "users"."deleted_at" is null;--> statement-breakpoint
CREATE INDEX "workspace_memberships_user_idx" ON "workspace_memberships" USING btree ("user_id");
--> statement-breakpoint
-- The singleton exists before setup so concurrent callers have a stable row to
-- inspect after taking the transaction-scoped advisory lock.
INSERT INTO "instance_settings" ("singleton") VALUES (true)
ON CONFLICT ("singleton") DO NOTHING;
--> statement-breakpoint
CREATE FUNCTION devrunbook_try_setup_advisory_lock()
RETURNS boolean
LANGUAGE sql
VOLATILE
PARALLEL UNSAFE
AS $$
  SELECT pg_try_advisory_xact_lock(hashtextextended('devrunbook:first-run-setup', 0));
$$;
--> statement-breakpoint
COMMENT ON FUNCTION devrunbook_try_setup_advisory_lock() IS
  'Acquire the transaction-scoped first-run setup lock; call inside the setup transaction.';
--> statement-breakpoint
CREATE FUNCTION devrunbook_reject_immutable_update()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- Allow PostgreSQL referential actions (for example ON DELETE SET NULL) to
  -- preserve the deletion contract while rejecting direct application writes.
  IF pg_trigger_depth() > 1 THEN
    RETURN NEW;
  END IF;
  RAISE EXCEPTION USING
    ERRCODE = '55000',
    MESSAGE = format('%I is immutable', TG_TABLE_NAME);
END;
$$;
--> statement-breakpoint
CREATE FUNCTION devrunbook_reject_append_only_mutation()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF pg_trigger_depth() > 1 THEN
    RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
  END IF;
  RAISE EXCEPTION USING
    ERRCODE = '55000',
    MESSAGE = format('%I is append-only', TG_TABLE_NAME);
END;
$$;
--> statement-breakpoint
CREATE TRIGGER playbook_versions_published_immutable_trg
BEFORE UPDATE ON "playbook_versions"
FOR EACH ROW
WHEN (OLD."published_at" IS NOT NULL)
EXECUTE FUNCTION devrunbook_reject_immutable_update();
--> statement-breakpoint
CREATE TRIGGER repository_profile_revisions_immutable_trg
BEFORE UPDATE ON "repository_profile_revisions"
FOR EACH ROW
EXECUTE FUNCTION devrunbook_reject_immutable_update();
--> statement-breakpoint
CREATE TRIGGER repository_snapshots_complete_immutable_trg
BEFORE UPDATE ON "repository_snapshots"
FOR EACH ROW
WHEN (OLD."state" = 'complete')
EXECUTE FUNCTION devrunbook_reject_immutable_update();
--> statement-breakpoint
CREATE TRIGGER generated_runs_immutable_trg
BEFORE UPDATE ON "generated_runs"
FOR EACH ROW
EXECUTE FUNCTION devrunbook_reject_immutable_update();
--> statement-breakpoint
CREATE TRIGGER evaluation_results_immutable_trg
BEFORE UPDATE ON "evaluation_results"
FOR EACH ROW
EXECUTE FUNCTION devrunbook_reject_immutable_update();
--> statement-breakpoint
CREATE TRIGGER audit_events_append_only_trg
BEFORE UPDATE OR DELETE ON "audit_events"
FOR EACH ROW
EXECUTE FUNCTION devrunbook_reject_append_only_mutation();