| 1 | CREATE TYPE "public"."delivery_job_kind" AS ENUM('email', 'webhook');--> statement-breakpoint |
| 2 | CREATE TYPE "public"."delivery_job_status" AS ENUM('pending', 'succeeded', 'dead');--> statement-breakpoint |
| 3 | CREATE TABLE "delivery_attempt" ( |
| 4 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, |
| 5 | "job_id" uuid NOT NULL, |
| 6 | "started_at" timestamp with time zone DEFAULT now() NOT NULL, |
| 7 | "duration_ms" integer NOT NULL, |
| 8 | "status_code" integer, |
| 9 | "error" text, |
| 10 | "request_headers" jsonb NOT NULL, |
| 11 | "response_body" text |
| 12 | ); |
| 13 | --> statement-breakpoint |
| 14 | CREATE TABLE "delivery_job" ( |
| 15 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, |
| 16 | "kind" "delivery_job_kind" NOT NULL, |
| 17 | "idempotency_key" text NOT NULL, |
| 18 | "payload" jsonb NOT NULL, |
| 19 | "endpoint_id" text, |
| 20 | "status" "delivery_job_status" DEFAULT 'pending' NOT NULL, |
| 21 | "attempts" integer DEFAULT 0 NOT NULL, |
| 22 | "next_attempt_at" timestamp with time zone DEFAULT now() NOT NULL, |
| 23 | "locked_until" timestamp with time zone DEFAULT now() NOT NULL, |
| 24 | "claim_token" uuid DEFAULT gen_random_uuid() NOT NULL, |
| 25 | "last_error" text, |
| 26 | "created_at" timestamp with time zone DEFAULT now() NOT NULL, |
| 27 | "updated_at" timestamp with time zone DEFAULT now() NOT NULL, |
| 28 | CONSTRAINT "delivery_job_idempotencyKey_unique" UNIQUE("idempotency_key") |
| 29 | ); |
| 30 | --> statement-breakpoint |
| 31 | CREATE TABLE "webhook_endpoint" ( |
| 32 | "id" text PRIMARY KEY NOT NULL, |
| 33 | "repository_id" text, |
| 34 | "organization_id" text, |
| 35 | "url" text NOT NULL, |
| 36 | "secret" text NOT NULL, |
| 37 | "events" text[] NOT NULL, |
| 38 | "active" boolean DEFAULT true NOT NULL, |
| 39 | "disabled_reason" text, |
| 40 | "created_at" timestamp with time zone DEFAULT now() NOT NULL, |
| 41 | "updated_at" timestamp with time zone DEFAULT now() NOT NULL, |
| 42 | CONSTRAINT "webhook_endpoint_secret_unique" UNIQUE("secret"), |
| 43 | CONSTRAINT "webhook_endpoint_owner" CHECK (num_nonnulls(repository_id, organization_id) = 1) |
| 44 | ); |
| 45 | --> statement-breakpoint |
| 46 | ALTER TABLE "delivery_attempt" ADD CONSTRAINT "delivery_attempt_job_id_delivery_job_id_fk" FOREIGN KEY ("job_id") REFERENCES "public"."delivery_job"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint |
| 47 | ALTER TABLE "delivery_job" ADD CONSTRAINT "delivery_job_endpoint_id_webhook_endpoint_id_fk" FOREIGN KEY ("endpoint_id") REFERENCES "public"."webhook_endpoint"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint |
| 48 | ALTER TABLE "webhook_endpoint" ADD CONSTRAINT "webhook_endpoint_repository_id_repository_id_fk" FOREIGN KEY ("repository_id") REFERENCES "public"."repository"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint |
| 49 | ALTER TABLE "webhook_endpoint" ADD CONSTRAINT "webhook_endpoint_organization_id_organization_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organization"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint |
| 50 | CREATE INDEX "delivery_attempt_job_idx" ON "delivery_attempt" USING btree ("job_id","started_at");--> statement-breakpoint |
| 51 | CREATE INDEX "delivery_job_pending_idx" ON "delivery_job" USING btree ("next_attempt_at") WHERE "delivery_job"."status" = 'pending';--> statement-breakpoint |
| 52 | CREATE INDEX "webhook_endpoint_repository_idx" ON "webhook_endpoint" USING btree ("repository_id");--> statement-breakpoint |
| 53 | CREATE INDEX "webhook_endpoint_organization_idx" ON "webhook_endpoint" USING btree ("organization_id"); |