WebhookManager/initialise-database.sql at main · josiebrohm/WebhookManager · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
-- Active: 1755869732761@@127.0.0.1@5432@webhook_manager_db@core
-- Database: webhook_manager_db

-- DROP DATABASE IF EXISTS webhook_manager_db;

CREATE DATABASE webhook_manager_db
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'C'
    LC_CTYPE = 'C'
    LOCALE_PROVIDER = 'libc'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

-- SCHEMA: core

-- DROP SCHEMA IF EXISTS core ;

CREATE SCHEMA IF NOT EXISTS core
    AUTHORIZATION postgres;

-- Table: core.endpoints

-- DROP TABLE IF EXISTS core.endpoints;

CREATE TABLE IF NOT EXISTS core.endpoints
(
    id uuid NOT NULL,
    url character varying(255) COLLATE pg_catalog."default" NOT NULL,
    secret character varying(255) COLLATE pg_catalog."default" NOT NULL,
    enabled boolean NOT NULL,
    last_success_at timestamp without time zone,
    last_failure_at timestamp without time zone,
    CONSTRAINT endpoints_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS core.endpoints
    OWNER to postgres;

-- Table: core.publisher_accounts

-- DROP TABLE IF EXISTS core.publisher_accounts;

CREATE TABLE IF NOT EXISTS core.publisher_accounts
(
    id uuid NOT NULL,
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    username character varying(255) COLLATE pg_catalog."default" NOT NULL,
	password character varying(255) COLLATE pg_catalog."default" NOT NULL,
    rate_limit integer,
    CONSTRAINT publisher_accounts_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS core.publisher_accounts
    OWNER to postgres;

-- Table: core.send_attempts

-- DROP TABLE IF EXISTS core.send_attempts;

CREATE TABLE IF NOT EXISTS core.send_attempts
(
    id uuid NOT NULL,
    webhook_message_id uuid NOT NULL,
    status character varying COLLATE pg_catalog."default" NOT NULL,
    retry_count integer NOT NULL DEFAULT 0,
    max_retries integer NOT NULL,
    scheduled_for timestamp without time zone,
    created_at timestamp without time zone,
    delivered_at timestamp without time zone,
    failed_at timestamp without time zone,
    CONSTRAINT send_attempts_pkey PRIMARY KEY (id),
    CONSTRAINT webhook_message_id FOREIGN KEY (webhook_message_id)
        REFERENCES core.webhook_messages (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS core.send_attempts
    OWNER to postgres;

-- Table: core.webhook_messages

-- DROP TABLE IF EXISTS core.webhook_messages;

CREATE TABLE IF NOT EXISTS core.webhook_messages
(
    id uuid NOT NULL,
    endpoint_id uuid NOT NULL,
    publisher_account_id uuid NOT NULL,
    headers json NOT NULL,
    payload json NOT NULL,
    event_type character varying COLLATE pg_catalog."default" NOT NULL,
    created_at timestamp without time zone NOT NULL,
    CONSTRAINT webhook_messages_pkey PRIMARY KEY (id),
    CONSTRAINT enpoint_id FOREIGN KEY (endpoint_id)
        REFERENCES core.endpoints (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT publisher_account_id FOREIGN KEY (publisher_account_id)
        REFERENCES core.publisher_accounts (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS core.webhook_messages
    OWNER to postgres;