shithub Public

Watch 1 Fork 0 Star 0
MySQL · 6199 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2 --
3 -- S33 — Webhooks. Two tables:
4 --
5 -- * webhooks — operator-configured subscriptions. Owner is a
6 -- repo OR an org (org-owned repos can carry
7 -- their own org-level webhooks too, fanned out
8 -- on every repo event in the org).
9 -- * webhook_deliveries — one row per delivery attempt. Pending /
10 -- retry rows are picked up by the deliver
11 -- worker. Successful and terminally-failed rows
12 -- are kept for the redelivery UI; the purge
13 -- cron prunes them after retention.
14 --
15 -- Secrets at rest:
16 -- - `secret_ciphertext` + `secret_nonce` are wrapped with
17 -- `internal/auth/secretbox` (chacha20poly1305 AEAD; 32-byte key
18 -- from config). The key is the same TOTP key for now — see
19 -- `webhooks.md` for rotation procedure.
20 --
21 -- Auto-disable:
22 -- - `consecutive_failures` increments on terminal failure, resets on
23 -- success. When it crosses `auto_disable_threshold` the deliverer
24 -- sets `disabled_at` + `disabled_reason` and surfaces a warning to
25 -- repo/org admins.
26
27 -- +goose Up
28
29 CREATE TYPE webhook_owner_kind AS ENUM ('repo', 'org');
30 CREATE TYPE webhook_content_type AS ENUM ('json', 'form');
31 CREATE TYPE webhook_delivery_status AS ENUM (
32 'pending', 'succeeded', 'failed_retry', 'failed_permanent'
33 );
34
35 CREATE TABLE webhooks (
36 id bigserial PRIMARY KEY,
37 owner_kind webhook_owner_kind NOT NULL,
38 owner_id bigint NOT NULL,
39 url text NOT NULL,
40 content_type webhook_content_type NOT NULL DEFAULT 'json',
41 -- Subscribed event kinds. Empty set means "all"; explicit subset
42 -- restricts the fan-out match. Rows that change shape (e.g. push
43 -- with no commits) still fire — filter expressions are post-MVP.
44 events text[] NOT NULL DEFAULT ARRAY[]::text[],
45 secret_ciphertext bytea NOT NULL,
46 secret_nonce bytea NOT NULL,
47 active boolean NOT NULL DEFAULT true,
48 -- Per-webhook SSL verification override. Default keeps verification
49 -- on; self-hosters with internal CAs can flip it (UI surfaces a
50 -- loud warning). Enforcement ships with the deliverer; the column
51 -- is wired up day-1 so config doesn't churn later.
52 ssl_verification boolean NOT NULL DEFAULT true,
53 consecutive_failures int NOT NULL DEFAULT 0,
54 auto_disable_threshold int NOT NULL DEFAULT 50,
55 disabled_at timestamptz,
56 disabled_reason text,
57 last_success_at timestamptz,
58 last_failure_at timestamptz,
59 created_by_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
60 created_at timestamptz NOT NULL DEFAULT now(),
61 updated_at timestamptz NOT NULL DEFAULT now(),
62
63 CONSTRAINT webhooks_url_length CHECK (char_length(url) BETWEEN 1 AND 2048),
64 CONSTRAINT webhooks_threshold_positive CHECK (auto_disable_threshold > 0)
65 );
66
67 CREATE INDEX webhooks_owner_idx ON webhooks (owner_kind, owner_id);
68 CREATE INDEX webhooks_active_idx ON webhooks (owner_kind, owner_id) WHERE active = true AND disabled_at IS NULL;
69
70 CREATE TRIGGER set_updated_at BEFORE UPDATE ON webhooks
71 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
72
73 CREATE TABLE webhook_deliveries (
74 id bigserial PRIMARY KEY,
75 webhook_id bigint NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
76 -- event_kind mirrors `domain_events.kind`. event_id points at the
77 -- triggering domain_events row; NULL for synthetic deliveries
78 -- (ping, redelivery from a manual UI click).
79 event_kind text NOT NULL,
80 event_id bigint,
81 delivery_uuid uuid NOT NULL DEFAULT gen_random_uuid(),
82 payload jsonb NOT NULL,
83 request_headers jsonb NOT NULL DEFAULT '{}'::jsonb,
84 request_body bytea NOT NULL DEFAULT ''::bytea,
85 response_status int,
86 response_headers jsonb,
87 response_body bytea,
88 response_truncated boolean NOT NULL DEFAULT false,
89 started_at timestamptz NOT NULL DEFAULT now(),
90 completed_at timestamptz,
91 attempt int NOT NULL DEFAULT 1,
92 max_attempts int NOT NULL DEFAULT 8,
93 next_retry_at timestamptz,
94 status webhook_delivery_status NOT NULL DEFAULT 'pending',
95 -- idempotency_key is sha256(payload + webhook_id + event_id);
96 -- subscribers can dedupe across retries with it.
97 idempotency_key text NOT NULL,
98 error_summary text,
99 -- Set when the delivery was created via UI redelivery; points at
100 -- the original delivery for audit. Nullable for first attempts.
101 redeliver_of bigint REFERENCES webhook_deliveries(id) ON DELETE SET NULL,
102
103 CONSTRAINT webhook_deliveries_event_kind_length CHECK (char_length(event_kind) BETWEEN 1 AND 64),
104 CONSTRAINT webhook_deliveries_attempt_positive CHECK (attempt >= 1),
105 CONSTRAINT webhook_deliveries_max_attempts_positive CHECK (max_attempts >= 1)
106 );
107
108 -- Deliver-loop hot path: pending or retry-ready, due now.
109 CREATE INDEX webhook_deliveries_pending_due_idx
110 ON webhook_deliveries (next_retry_at)
111 WHERE status IN ('pending', 'failed_retry');
112
113 -- Per-webhook delivery list (settings UI).
114 CREATE INDEX webhook_deliveries_webhook_started_idx
115 ON webhook_deliveries (webhook_id, started_at DESC);
116
117 -- +goose Down
118 DROP TABLE IF EXISTS webhook_deliveries;
119 DROP TABLE IF EXISTS webhooks;
120 DROP TYPE IF EXISTS webhook_delivery_status;
121 DROP TYPE IF EXISTS webhook_content_type;
122 DROP TYPE IF EXISTS webhook_owner_kind;
123