konnosDashboard
0021_webhook_subscribers.sql68 lines · main
1-- 0021_webhook_subscribers — outbound (platform → customer) webhooks.
2-- briven emits events (abuse.report.opened, deploy.succeeded, ...) and
3-- fans them out to every matching subscriber. Deliveries retry on
4-- failure with exponential backoff up to 5 attempts; the dispatcher
5-- claims rows via the partial index on status='pending'.
6
7CREATE TABLE IF NOT EXISTS "webhook_subscribers" (
8 "id" text PRIMARY KEY NOT NULL,
9 "project_id" text NOT NULL,
10 "name" text NOT NULL,
11 "target_url" text NOT NULL,
12 "event_types" text NOT NULL DEFAULT '*',
13 "signing_secret_encrypted" text NOT NULL,
14 "enabled" boolean DEFAULT true NOT NULL,
15 "last_delivery_at" timestamp with time zone,
16 "last_delivery_status" text,
17 "created_by" text,
18 "created_at" timestamp with time zone DEFAULT now() NOT NULL,
19 "updated_at" timestamp with time zone DEFAULT now() NOT NULL,
20 "deleted_at" timestamp with time zone,
21 CONSTRAINT "webhook_subscribers_project_id_fk"
22 FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE CASCADE,
23 CONSTRAINT "webhook_subscribers_created_by_fk"
24 FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE SET NULL
25);
26
27CREATE UNIQUE INDEX IF NOT EXISTS "webhook_subscribers_project_name_idx"
28 ON "webhook_subscribers" USING btree ("project_id", "name")
29 WHERE "deleted_at" IS NULL;
30
31CREATE INDEX IF NOT EXISTS "webhook_subscribers_project_idx"
32 ON "webhook_subscribers" USING btree ("project_id")
33 WHERE "deleted_at" IS NULL;
34
35CREATE TABLE IF NOT EXISTS "webhook_outbound_deliveries" (
36 "id" text PRIMARY KEY NOT NULL,
37 "subscriber_id" text NOT NULL,
38 "project_id" text NOT NULL,
39 "event_id" text NOT NULL,
40 "event_type" text NOT NULL,
41 "payload" jsonb NOT NULL,
42 "status" text DEFAULT 'pending' NOT NULL,
43 "attempt_count" text DEFAULT '0' NOT NULL,
44 "next_attempt_at" timestamp with time zone NOT NULL,
45 "last_attempt_at" timestamp with time zone,
46 "status_code" text,
47 "duration_ms" text,
48 "error_message" text,
49 "created_at" timestamp with time zone DEFAULT now() NOT NULL,
50 CONSTRAINT "webhook_outbound_deliveries_subscriber_id_fk"
51 FOREIGN KEY ("subscriber_id") REFERENCES "webhook_subscribers"("id") ON DELETE CASCADE,
52 CONSTRAINT "webhook_outbound_deliveries_project_id_fk"
53 FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE CASCADE
54);
55
56-- Dispatcher hot path: pending rows only.
57CREATE INDEX IF NOT EXISTS "webhook_outbound_deliveries_due_idx"
58 ON "webhook_outbound_deliveries" USING btree ("next_attempt_at")
59 WHERE "status" = 'pending';
60
61CREATE INDEX IF NOT EXISTS "webhook_outbound_deliveries_subscriber_idx"
62 ON "webhook_outbound_deliveries" USING btree ("subscriber_id", "created_at");
63
64CREATE INDEX IF NOT EXISTS "webhook_outbound_deliveries_project_idx"
65 ON "webhook_outbound_deliveries" USING btree ("project_id", "created_at");
66
67CREATE INDEX IF NOT EXISTS "webhook_outbound_deliveries_event_id_idx"
68 ON "webhook_outbound_deliveries" USING btree ("event_id");