281 lines
14 KiB
SQL
281 lines
14 KiB
SQL
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,
|
|
meta JSONB NOT NULL DEFAULT '{}',
|
|
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,
|
|
content_type TEXT NOT NULL DEFAULT 'application/octet-stream',
|
|
thumb TEXT NOT NULL,
|
|
meta JSONB NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- campaign_media
|
|
DROP TABLE IF EXISTS campaign_media CASCADE;
|
|
CREATE TABLE campaign_media (
|
|
campaign_id INTEGER REFERENCES campaigns(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
|
|
-- Media items may be deleted, so media_id is nullable
|
|
-- and a copy of the original name is maintained here.
|
|
media_id INTEGER NULL REFERENCES media(id) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
|
|
filename TEXT NOT NULL DEFAULT ''
|
|
);
|
|
DROP INDEX IF EXISTS idx_camp_media_id; CREATE UNIQUE INDEX idx_camp_media_id ON campaign_media (campaign_id, media_id);
|
|
DROP INDEX IF EXISTS idx_camp_media_camp_id; CREATE INDEX idx_camp_media_camp_id ON campaign_media(campaign_id);
|
|
|
|
|
|
-- 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 <noreply@listmonk.yoursite.com>"'),
|
|
('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.enable_public_archive_rss_content', 'true'),
|
|
('app.send_optin_confirmation', 'true'),
|
|
('app.check_updates', 'true'),
|
|
('app.notify_emails', '["admin1@mysite.com", "admin2@mysite.com"]'),
|
|
('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', '[]'),
|
|
('privacy.record_optin_ip', 'false'),
|
|
('security.enable_captcha', 'false'),
|
|
('security.captcha_key', '""'),
|
|
('security.captcha_secret', '""'),
|
|
('upload.provider', '"filesystem"'),
|
|
('upload.max_file_size', '5000'),
|
|
('upload.extensions', '["jpg","jpeg","png","gif","svg","*"]'),
|
|
('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', '"167h"'),
|
|
('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":"username@gmail.com","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.actions', '{"soft": {"count": 2, "action": "none"}, "hard": {"count": 2, "action": "blocklist"}, "complaint" : {"count": 2, "action": "delete"}}'),
|
|
('bounce.ses_enabled', 'false'),
|
|
('bounce.sendgrid_enabled', 'false'),
|
|
('bounce.sendgrid_key', '""'),
|
|
('bounce.postmark', '{"enabled": false, "username": "", "password": ""}'),
|
|
('bounce.mailboxes',
|
|
'[{"enabled":false, "type": "pop", "host":"pop.yoursite.com","port":995,"auth_protocol":"userpass","username":"username","password":"password","return_path": "bounce@listmonk.yoursite.com","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));
|