0020_webhook_endpoints.sql56 lines · main

1	-- 0020_webhook_endpoints — inbound webhook receivers.
2	-- The public POST /webhooks/:projectId/:endpointId endpoint authenticates
3	-- callers via HMAC-SHA256(`${timestamp}.${rawBody}`, signingSecret). The
4	-- signing secret is stored AES-256-GCM-encrypted, same KEK + format as
5	-- project_env_vars. Every inbound request (accepted OR rejected) inserts
6	-- one row into webhook_deliveries — the audit log surface is the only way
7	-- an operator can tell "did the signature fail" vs "did my function 500".
8
9	CREATE TABLE IF NOT EXISTS "webhook_endpoints" (
10	"id" text PRIMARY KEY NOT NULL,
11	"project_id" text NOT NULL,
12	"name" text NOT NULL,
13	"function_name" text NOT NULL,
14	"signing_secret_encrypted" text NOT NULL,
15	"enabled" boolean DEFAULT true NOT NULL,
16	"last_delivery_at" timestamp with time zone,
17	"last_delivery_status" text,
18	"created_by" text,
19	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
20	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
21	"deleted_at" timestamp with time zone,
22	CONSTRAINT "webhook_endpoints_project_id_fk"
23	FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE CASCADE,
24	CONSTRAINT "webhook_endpoints_created_by_fk"
25	FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE SET NULL
26	);
27
28	CREATE UNIQUE INDEX IF NOT EXISTS "webhook_endpoints_project_name_idx"
29	ON "webhook_endpoints" USING btree ("project_id", "name")
30	WHERE "deleted_at" IS NULL;
31
32	CREATE INDEX IF NOT EXISTS "webhook_endpoints_project_idx"
33	ON "webhook_endpoints" USING btree ("project_id")
34	WHERE "deleted_at" IS NULL;
35
36	CREATE TABLE IF NOT EXISTS "webhook_deliveries" (
37	"id" text PRIMARY KEY NOT NULL,
38	"endpoint_id" text NOT NULL,
39	"project_id" text NOT NULL,
40	"status" text NOT NULL,
41	"source_ip_hash" text,
42	"function_name" text,
43	"duration_ms" text,
44	"error_message" text,
45	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
46	CONSTRAINT "webhook_deliveries_endpoint_id_fk"
47	FOREIGN KEY ("endpoint_id") REFERENCES "webhook_endpoints"("id") ON DELETE CASCADE,
48	CONSTRAINT "webhook_deliveries_project_id_fk"
49	FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE CASCADE
50	);
51
52	CREATE INDEX IF NOT EXISTS "webhook_deliveries_endpoint_idx"
53	ON "webhook_deliveries" USING btree ("endpoint_id", "created_at");
54
55	CREATE INDEX IF NOT EXISTS "webhook_deliveries_project_idx"
56	ON "webhook_deliveries" USING btree ("project_id", "created_at");