512 lines
31 KiB
PL/PgSQL
512 lines
31 KiB
PL/PgSQL
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();
|