1CREATE TABLE IF NOT EXISTS webhooks (
 2  id SERIAL PRIMARY KEY,
 3  repo_id INTEGER NOT NULL,
 4  url TEXT NOT NULL,
 5  secret TEXT NOT NULL,
 6  content_type INTEGER NOT NULL,
 7  active BOOLEAN NOT NULL,
 8  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 9  updated_at TIMESTAMP NOT NULL,
10  UNIQUE (repo_id, url),
11  CONSTRAINT repo_id_fk
12  FOREIGN KEY(repo_id) REFERENCES repos(id)
13  ON DELETE CASCADE
14  ON UPDATE CASCADE
15);
16
17CREATE TABLE IF NOT EXISTS webhook_events (
18  id SERIAL PRIMARY KEY,
19  webhook_id INTEGER NOT NULL,
20  event INTEGER NOT NULL,
21  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
22  UNIQUE (webhook_id, event),
23  CONSTRAINT webhook_id_fk
24  FOREIGN KEY(webhook_id) REFERENCES webhooks(id)
25  ON DELETE CASCADE
26  ON UPDATE CASCADE
27);
28
29CREATE TABLE IF NOT EXISTS webhook_deliveries (
30  id TEXT PRIMARY KEY,
31  webhook_id INTEGER NOT NULL,
32  event INTEGER NOT NULL,
33  request_url TEXT NOT NULL,
34  request_method TEXT NOT NULL,
35  request_error TEXT,
36  request_headers TEXT NOT NULL,
37  request_body TEXT NOT NULL,
38  response_status INTEGER NOT NULL,
39  response_headers TEXT NOT NULL,
40  response_body TEXT NOT NULL,
41  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
42  CONSTRAINT webhook_id_fk
43  FOREIGN KEY(webhook_id) REFERENCES webhooks(id)
44  ON DELETE CASCADE
45  ON UPDATE CASCADE
46);