forked from knadh/listmonk
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
256 lines (230 loc) · 12.4 KB
/
schema.sql
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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
DROP TYPE IF EXISTS list_type CASCADE; CREATE TYPE list_type AS ENUM ('public', 'private', 'temporary');
DROP TYPE IF EXISTS list_optin CASCADE; CREATE TYPE list_optin AS ENUM ('single', 'double');
DROP TYPE IF EXISTS subscriber_status CASCADE; CREATE TYPE subscriber_status AS ENUM ('enabled', 'disabled', 'blocklisted');
DROP TYPE IF EXISTS subscription_status CASCADE; CREATE TYPE subscription_status AS ENUM ('unconfirmed', 'confirmed', 'unsubscribed');
DROP TYPE IF EXISTS campaign_status CASCADE; CREATE TYPE campaign_status AS ENUM ('draft', 'running', 'scheduled', 'paused', 'cancelled', 'finished');
DROP TYPE IF EXISTS campaign_type CASCADE; CREATE TYPE campaign_type AS ENUM ('regular', 'optin');
DROP TYPE IF EXISTS content_type CASCADE; CREATE TYPE content_type AS ENUM ('richtext', 'html', 'plain', 'markdown');
DROP TYPE IF EXISTS bounce_type CASCADE; CREATE TYPE bounce_type AS ENUM ('soft', 'hard', 'complaint');
DROP TYPE IF EXISTS template_type CASCADE; CREATE TYPE template_type AS ENUM ('campaign', 'tx');
-- subscribers
DROP TABLE IF EXISTS subscribers CASCADE;
CREATE TABLE subscribers (
id SERIAL PRIMARY KEY,
uuid uuid NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
attribs JSONB NOT NULL DEFAULT '{}',
status subscriber_status NOT NULL DEFAULT 'enabled',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
DROP INDEX IF EXISTS idx_subs_email; CREATE UNIQUE INDEX idx_subs_email ON subscribers(LOWER(email));
DROP INDEX IF EXISTS idx_subs_status; CREATE INDEX idx_subs_status ON subscribers(status);
-- lists
DROP TABLE IF EXISTS lists CASCADE;
CREATE TABLE lists (
id SERIAL PRIMARY KEY,
uuid uuid NOT NULL UNIQUE,
name TEXT NOT NULL,
type list_type NOT NULL,
optin list_optin NOT NULL DEFAULT 'single',
tags VARCHAR(100)[],
description TEXT NOT NULL DEFAULT '',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
DROP TABLE IF EXISTS subscriber_lists CASCADE;
CREATE TABLE subscriber_lists (
subscriber_id INTEGER REFERENCES subscribers(id) ON DELETE CASCADE ON UPDATE CASCADE,
list_id INTEGER NULL REFERENCES lists(id) ON DELETE CASCADE ON UPDATE CASCADE,
status subscription_status NOT NULL DEFAULT 'unconfirmed',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
PRIMARY KEY(subscriber_id, list_id)
);
DROP INDEX IF EXISTS idx_sub_lists_sub_id; CREATE INDEX idx_sub_lists_sub_id ON subscriber_lists(subscriber_id);
DROP INDEX IF EXISTS idx_sub_lists_list_id; CREATE INDEX idx_sub_lists_list_id ON subscriber_lists(list_id);
DROP INDEX IF EXISTS idx_sub_lists_status; CREATE INDEX idx_sub_lists_status ON subscriber_lists(status);
-- templates
DROP TABLE IF EXISTS templates CASCADE;
CREATE TABLE templates (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
type template_type NOT NULL DEFAULT 'campaign',
subject TEXT NOT NULL,
body TEXT NOT NULL,
is_default BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE UNIQUE INDEX ON templates (is_default) WHERE is_default = true;
-- campaigns
DROP TABLE IF EXISTS campaigns CASCADE;
CREATE TABLE campaigns (
id SERIAL PRIMARY KEY,
uuid uuid NOT NULL UNIQUE,
name TEXT NOT NULL,
subject TEXT NOT NULL,
from_email TEXT NOT NULL,
body TEXT NOT NULL,
altbody TEXT NULL,
content_type content_type NOT NULL DEFAULT 'richtext',
send_at TIMESTAMP WITH TIME ZONE,
headers JSONB NOT NULL DEFAULT '[]',
status campaign_status NOT NULL DEFAULT 'draft',
tags VARCHAR(100)[],
-- The subscription statuses of subscribers to which a campaign will be sent.
-- For opt-in campaigns, this will be 'unsubscribed'.
type campaign_type DEFAULT 'regular',
-- The ID of the messenger backend used to send this campaign.
messenger TEXT NOT NULL,
template_id INTEGER REFERENCES templates(id) ON DELETE SET DEFAULT DEFAULT 1,
-- Progress and stats.
to_send INT NOT NULL DEFAULT 0,
sent INT NOT NULL DEFAULT 0,
max_subscriber_id INT NOT NULL DEFAULT 0,
last_subscriber_id INT NOT NULL DEFAULT 0,
-- Publishing.
archive BOOLEAN NOT NULL DEFAULT false,
archive_template_id INTEGER REFERENCES templates(id) ON DELETE SET DEFAULT DEFAULT 1,
archive_meta JSONB NOT NULL DEFAULT '{}',
started_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
DROP TABLE IF EXISTS campaign_lists CASCADE;
CREATE TABLE campaign_lists (
id BIGSERIAL PRIMARY KEY,
campaign_id INTEGER NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE ON UPDATE CASCADE,
-- Lists may be deleted, so list_id is nullable
-- and a copy of the original list name is maintained here.
list_id INTEGER NULL REFERENCES lists(id) ON DELETE SET NULL ON UPDATE CASCADE,
list_name TEXT NOT NULL DEFAULT ''
);
CREATE UNIQUE INDEX ON campaign_lists (campaign_id, list_id);
DROP INDEX IF EXISTS idx_camp_lists_camp_id; CREATE INDEX idx_camp_lists_camp_id ON campaign_lists(campaign_id);
DROP INDEX IF EXISTS idx_camp_lists_list_id; CREATE INDEX idx_camp_lists_list_id ON campaign_lists(list_id);
DROP TABLE IF EXISTS campaign_views CASCADE;
CREATE TABLE campaign_views (
id BIGSERIAL PRIMARY KEY,
campaign_id INTEGER NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE ON UPDATE CASCADE,
-- Subscribers may be deleted, but the view counts should remain.
subscriber_id INTEGER NULL REFERENCES subscribers(id) ON DELETE SET NULL ON UPDATE CASCADE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
DROP INDEX IF EXISTS idx_views_camp_id; CREATE INDEX idx_views_camp_id ON campaign_views(campaign_id);
DROP INDEX IF EXISTS idx_views_subscriber_id; CREATE INDEX idx_views_subscriber_id ON campaign_views(subscriber_id);
DROP INDEX IF EXISTS idx_views_date; CREATE INDEX idx_views_date ON campaign_views((TIMEZONE('UTC', created_at)::DATE));
-- media
DROP TABLE IF EXISTS media CASCADE;
CREATE TABLE media (
id SERIAL PRIMARY KEY,
uuid uuid NOT NULL UNIQUE,
provider TEXT NOT NULL DEFAULT '',
filename TEXT NOT NULL,
thumb TEXT NOT NULL,
meta JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- links
DROP TABLE IF EXISTS links CASCADE;
CREATE TABLE links (
id SERIAL PRIMARY KEY,
uuid uuid NOT NULL UNIQUE,
url TEXT NOT NULL UNIQUE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
DROP TABLE IF EXISTS link_clicks CASCADE;
CREATE TABLE link_clicks (
id BIGSERIAL PRIMARY KEY,
campaign_id INTEGER NULL REFERENCES campaigns(id) ON DELETE CASCADE ON UPDATE CASCADE,
link_id INTEGER NOT NULL REFERENCES links(id) ON DELETE CASCADE ON UPDATE CASCADE,
-- Subscribers may be deleted, but the link counts should remain.
subscriber_id INTEGER NULL REFERENCES subscribers(id) ON DELETE SET NULL ON UPDATE CASCADE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
DROP INDEX IF EXISTS idx_clicks_camp_id; CREATE INDEX idx_clicks_camp_id ON link_clicks(campaign_id);
DROP INDEX IF EXISTS idx_clicks_link_id; CREATE INDEX idx_clicks_link_id ON link_clicks(link_id);
DROP INDEX IF EXISTS idx_clicks_sub_id; CREATE INDEX idx_clicks_sub_id ON link_clicks(subscriber_id);
DROP INDEX IF EXISTS idx_clicks_date; CREATE INDEX idx_clicks_date ON link_clicks((TIMEZONE('UTC', created_at)::DATE));
-- settings
DROP TABLE IF EXISTS settings CASCADE;
CREATE TABLE settings (
key TEXT NOT NULL UNIQUE,
value JSONB NOT NULL DEFAULT '{}',
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
DROP INDEX IF EXISTS idx_settings_key; CREATE INDEX idx_settings_key ON settings(key);
INSERT INTO settings (key, value) VALUES
('app.site_name', '"Mailing list"'),
('app.root_url', '"http://localhost:9000"'),
('app.favicon_url', '""'),
('app.from_email', '"listmonk <[email protected]>"'),
('app.logo_url', '""'),
('app.concurrency', '10'),
('app.message_rate', '10'),
('app.batch_size', '1000'),
('app.max_send_errors', '1000'),
('app.message_sliding_window', 'false'),
('app.message_sliding_window_duration', '"1h"'),
('app.message_sliding_window_rate', '10000'),
('app.enable_public_archive', 'true'),
('app.enable_public_subscription_page', 'true'),
('app.send_optin_confirmation', 'true'),
('app.check_updates', 'true'),
('app.notify_emails', '["[email protected]", "[email protected]"]'),
('app.lang', '"en"'),
('privacy.individual_tracking', 'false'),
('privacy.unsubscribe_header', 'true'),
('privacy.allow_blocklist', 'true'),
('privacy.allow_export', 'true'),
('privacy.allow_wipe', 'true'),
('privacy.allow_preferences', 'true'),
('privacy.exportable', '["profile", "subscriptions", "campaign_views", "link_clicks"]'),
('privacy.domain_blocklist', '[]'),
('upload.provider', '"filesystem"'),
('upload.filesystem.upload_path', '"uploads"'),
('upload.filesystem.upload_uri', '"/uploads"'),
('upload.s3.url', '"https://ap-south-1.s3.amazonaws.com"'),
('upload.s3.public_url', '""'),
('upload.s3.aws_access_key_id', '""'),
('upload.s3.aws_secret_access_key', '""'),
('upload.s3.aws_default_region', '"ap-south-1"'),
('upload.s3.bucket', '""'),
('upload.s3.bucket_domain', '""'),
('upload.s3.bucket_path', '"/"'),
('upload.s3.bucket_type', '"public"'),
('upload.s3.expiry', '"14d"'),
('smtp',
'[{"enabled":true, "host":"smtp.yoursite.com","port":25,"auth_protocol":"cram","username":"username","password":"password","hello_hostname":"","max_conns":10,"idle_timeout":"15s","wait_timeout":"5s","max_msg_retries":2,"tls_type":"STARTTLS","tls_skip_verify":false,"email_headers":[]},
{"enabled":false, "host":"smtp.gmail.com","port":465,"auth_protocol":"login","username":"[email protected]","password":"password","hello_hostname":"","max_conns":10,"idle_timeout":"15s","wait_timeout":"5s","max_msg_retries":2,"tls_type":"TLS","tls_skip_verify":false,"email_headers":[]}]'),
('messengers', '[]'),
('bounce.enabled', 'false'),
('bounce.webhooks_enabled', 'false'),
('bounce.count', '2'),
('bounce.action', '"blocklist"'),
('bounce.ses_enabled', 'false'),
('bounce.sendgrid_enabled', 'false'),
('bounce.sendgrid_key', '""'),
('bounce.mailboxes',
'[{"enabled":false, "type": "pop", "host":"pop.yoursite.com","port":995,"auth_protocol":"userpass","username":"username","password":"password","return_path": "[email protected]","scan_interval":"15m","tls_enabled":true,"tls_skip_verify":false}]'),
('appearance.admin.custom_css', '""'),
('appearance.admin.custom_js', '""'),
('appearance.public.custom_css', '""'),
('appearance.public.custom_js', '""');
-- bounces
DROP TABLE IF EXISTS bounces CASCADE;
CREATE TABLE bounces (
id SERIAL PRIMARY KEY,
subscriber_id INTEGER NOT NULL REFERENCES subscribers(id) ON DELETE CASCADE ON UPDATE CASCADE,
campaign_id INTEGER NULL REFERENCES campaigns(id) ON DELETE SET NULL ON UPDATE CASCADE,
type bounce_type NOT NULL DEFAULT 'hard',
source TEXT NOT NULL DEFAULT '',
meta JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
DROP INDEX IF EXISTS idx_bounces_sub_id; CREATE INDEX idx_bounces_sub_id ON bounces(subscriber_id);
DROP INDEX IF EXISTS idx_bounces_camp_id; CREATE INDEX idx_bounces_camp_id ON bounces(campaign_id);
DROP INDEX IF EXISTS idx_bounces_source; CREATE INDEX idx_bounces_source ON bounces(source);
DROP INDEX IF EXISTS idx_bounces_date; CREATE INDEX idx_bounces_date ON bounces((TIMEZONE('UTC', created_at)::DATE));