flaxo/ghost

public

forked from ghost/ghost

Source code of Ghost - On Ghost

Code Issues0 Pull requests0

ghostpackagesdbdrizzle0035_parched_bushwacker.sql

add webhooks schemac63a19714 days ago53 lines · 3.2 KB

1	CREATE TYPE "public"."delivery_job_kind" AS ENUM('email', 'webhook');--> statement-breakpoint
2	CREATE TYPE "public"."delivery_job_status
3	CREATE TABLE "delivery_attempt" (
4	"id" uuid PRIMARY KEY DEFAULT
5	"job_id" uuid NOT NULL,
6	"started_at" timestamp with time zone DEFAULT
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
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
21	"attempts" integer DEFAULT
22	"next_attempt_at" timestamp with time zone DEFAULT
23	"locked_until" timestamp with time zone DEFAULT
24	"claim_token" uuid DEFAULT gen_random_uuid()
25	"last_error" text,
26	"created_at" timestamp with time zone DEFAULT
27	"updated_at" timestamp with time zone DEFAULT
28	CONSTRAINT "delivery_job_idempotencyKey_unique" UNIQUE
29	);
30	--> statement-breakpoint
31	CREATE TABLE "webhook_endpoint" (
32	"id" text PRIMARY KEY
33	"repository_id" text,
34	"organization_id" text,
35	"url" text NOT NULL
36	"secret" text NOT NULL
37	"events" text[]
38	"active" boolean DEFAULT
39	"disabled_reason" text,
40	"created_at" timestamp with time zone DEFAULT
41	"updated_at" timestamp with time zone DEFAULT
42	CONSTRAINT "webhook_endpoint_secret_unique" UNIQUE
43	CONSTRAINT "webhook_endpoint_owner" CHECK
44	);
45	--> statement-breakpoint
46	ALTER TABLE "delivery_attempt" ADD
47	ALTER TABLE "delivery_job" ADD
48	ALTER TABLE "webhook_endpoint" ADD
49	ALTER TABLE "webhook_endpoint" ADD
50	CREATE INDEX "delivery_attempt_job_idx" ON
51	CREATE INDEX "delivery_job_pending_idx" ON
52	CREATE INDEX "webhook_endpoint_repository_idx" ON
53	CREATE INDEX "webhook_endpoint_organization_idx" ON

"

AS

ENUM(

'

pending

'

,

'

succeeded

'

,

'

dead

'

);

--

> statement-breakpoint

gen_random_uuid

()

NOT NULL

,

now

()

NOT NULL

,

,

gen_random_uuid

()

NOT NULL

,

,

,

'

pending

'

NOT NULL

,

0

NOT NULL

,

now

()

NOT NULL

,

now

()

NOT NULL

,

NOT NULL

,

now

()

NOT NULL

,

now

()

NOT NULL

,

(

"

idempotency_key

"

)

NOT NULL

,

,

,

NOT NULL

,

true

NOT NULL

,

now

()

NOT NULL

,

now

()

NOT NULL

,

(

"

secret

"

),

(num_nonnulls(repository_id, organization_id)

=

1

)

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

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

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

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

"

delivery_attempt

"

USING

btree (

"

job_id

"

,

"

started_at

"

);

--

> statement-breakpoint

"

delivery_job

"

USING

btree (

"

next_attempt_at

"

)

WHERE

"

delivery_job

"

.

"

status

"

=

'

pending

'

;

--

> statement-breakpoint

"

webhook_endpoint

"

USING

btree (

"

repository_id

"

);

--

> statement-breakpoint

"

webhook_endpoint

"

USING

btree (

"

organization_id

"

);