Sign in

pulls/pr_01m47d15m3e54sn21z27rpy5n9

public

pr_01m47d15m3e54sn21z27rpy5n9/services/webhooks/migrations/0001_init.sql

58 lines1,866 bytesCodeBlame
1-- Webhooks, and every delivery made to them. Every timestamp is RFC 3339 UTC.
2
3CREATE TABLE hooks (
4 id TEXT PRIMARY KEY,
5 -- repo or workspace.
6 scope TEXT NOT NULL,
7 -- The workspace's slug.
8 workspace TEXT NOT NULL,
9 -- For a repository's webhook: its id, and owner/name.
10 repo_id TEXT,
11 repo TEXT,
12 url TEXT NOT NULL,
13 -- The event types it is sent, as a JSON array; ["*"] for all.
14 events TEXT NOT NULL,
15 active INTEGER NOT NULL DEFAULT 1,
16 -- The signing secret, sealed with AES-256-GCM under the service's key
17 -- and bound to the hook's id.
18 secret TEXT NOT NULL,
19 secret_hint TEXT NOT NULL,
20 created_by TEXT NOT NULL,
21 created_at TEXT NOT NULL,
22 last_status TEXT,
23 last_delivered_at TEXT
24);
25CREATE INDEX hooks_by_repo ON hooks (repo_id);
26CREATE INDEX hooks_by_workspace ON hooks (workspace, scope);
27
28CREATE TABLE deliveries (
29 id TEXT PRIMARY KEY,
30 hook_id TEXT NOT NULL,
31 -- The event delivered, or empty for a ping or a redelivery.
32 event_id TEXT NOT NULL,
33 event TEXT NOT NULL,
34 -- The JSON sent.
35 payload TEXT NOT NULL,
36 -- pending, delivered or failed.
37 status TEXT NOT NULL,
38 attempts INTEGER NOT NULL DEFAULT 0,
39 response_status INTEGER,
40 response_body TEXT,
41 error TEXT,
42 duration_ms INTEGER,
43 created_at TEXT NOT NULL,
44 delivered_at TEXT,
45 next_attempt_at TEXT
46);
47CREATE INDEX deliveries_by_hook ON deliveries (hook_id, id);
48CREATE INDEX deliveries_due ON deliveries (status, next_attempt_at);
49-- An event is delivered to a webhook once, however often the bus repeats it.
50CREATE UNIQUE INDEX deliveries_once ON deliveries (hook_id, event_id) WHERE event_id <> '';
51
52-- Which workspace a repository is in, so a workspace's webhooks find its
53-- repositories' events. Filled from repo.created, and on demand.
54CREATE TABLE repo_names (
55 repo_id TEXT PRIMARY KEY,
56 namespace TEXT NOT NULL,
57 name TEXT NOT NULL
58);