mirror of
https://github.com/knadh/listmonk.git
synced 2024-09-20 07:16:33 +08:00
95f63a13af
This commit splits roles into two, user roles and list roles, both of which are attached separately to a user. List roles are collection of lists each with read|write permissions, while user roles now have all permissions except for per-list ones. This allows for easier management of roles, eliminating the need to clone and create new roles just to adjust specific list permissions.
429 lines
21 KiB
SQL
429 lines
21 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');
|
|
DROP TYPE IF EXISTS user_type CASCADE; CREATE TYPE user_type AS ENUM ('user', 'api');
|
|
DROP TYPE IF EXISTS user_status CASCADE; CREATE TYPE user_status AS ENUM ('enabled', 'disabled');
|
|
DROP TYPE IF EXISTS role_type CASCADE; CREATE TYPE role_type AS ENUM ('user', 'list');
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
-- 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);
|
|
DROP INDEX IF EXISTS idx_subs_created_at; CREATE INDEX idx_subs_created_at ON subscribers(created_at);
|
|
DROP INDEX IF EXISTS idx_subs_updated_at; CREATE INDEX idx_subs_updated_at ON subscribers(updated_at);
|
|
|
|
-- 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 INDEX IF EXISTS idx_lists_type; CREATE INDEX idx_lists_type ON lists(type);
|
|
DROP INDEX IF EXISTS idx_lists_optin; CREATE INDEX idx_lists_optin ON lists(optin);
|
|
DROP INDEX IF EXISTS idx_lists_name; CREATE INDEX idx_lists_name ON lists(name);
|
|
DROP INDEX IF EXISTS idx_lists_created_at; CREATE INDEX idx_lists_created_at ON lists(created_at);
|
|
DROP INDEX IF EXISTS idx_lists_updated_at; CREATE INDEX idx_lists_updated_at ON lists(updated_at);
|
|
|
|
|
|
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_slug TEXT NULL UNIQUE,
|
|
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 INDEX IF EXISTS idx_camps_status; CREATE INDEX idx_camps_status ON campaigns(status);
|
|
DROP INDEX IF EXISTS idx_camps_name; CREATE INDEX idx_camps_name ON campaigns(name);
|
|
DROP INDEX IF EXISTS idx_camps_created_at; CREATE INDEX idx_camps_created_at ON campaigns(created_at);
|
|
DROP INDEX IF EXISTS idx_camps_updated_at; CREATE INDEX idx_camps_updated_at ON campaigns(updated_at);
|
|
|
|
|
|
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.cache_slow_queries', 'false'),
|
|
('app.cache_slow_queries_interval', '"0 3 * * *"'),
|
|
('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', '""'),
|
|
('security.oidc', '{"enabled": false, "provider_url": "", "client_id": "", "client_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": 1, "action": "blocklist"}, "complaint" : {"count": 1, "action": "blocklist"}}'),
|
|
('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));
|
|
|
|
-- roles
|
|
DROP TABLE IF EXISTS roles CASCADE;
|
|
CREATE TABLE roles (
|
|
id SERIAL PRIMARY KEY,
|
|
type role_type NOT NULL DEFAULT 'user',
|
|
parent_id INTEGER NULL REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
list_id INTEGER NULL REFERENCES lists(id) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
permissions TEXT[] NOT NULL DEFAULT '{}',
|
|
name TEXT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
CREATE UNIQUE INDEX roles_idx ON roles (parent_id, list_id);
|
|
CREATE UNIQUE INDEX roles_name_idx ON roles (type, name) WHERE name IS NOT NULL;
|
|
|
|
-- users
|
|
DROP TABLE IF EXISTS users CASCADE;
|
|
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_login BOOLEAN NOT NULL DEFAULT false,
|
|
password TEXT NULL,
|
|
email TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
avatar TEXT NULL,
|
|
type user_type NOT NULL DEFAULT 'user',
|
|
user_role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
|
|
list_role_id INTEGER NULL REFERENCES roles(id) ON DELETE CASCADE,
|
|
status user_status NOT NULL DEFAULT 'disabled',
|
|
loggedin_at TIMESTAMP WITH TIME ZONE NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- user sessions
|
|
DROP TABLE IF EXISTS sessions CASCADE;
|
|
CREATE TABLE sessions (
|
|
id TEXT NOT NULL PRIMARY KEY,
|
|
data JSONB DEFAULT '{}'::jsonb NOT NULL,
|
|
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL
|
|
);
|
|
DROP INDEX IF EXISTS idx_sessions; CREATE INDEX idx_sessions ON sessions (id, created_at);
|
|
|
|
-- materialized views
|
|
|
|
-- dashboard stats
|
|
DROP MATERIALIZED VIEW IF EXISTS mat_dashboard_counts;
|
|
CREATE MATERIALIZED VIEW mat_dashboard_counts AS
|
|
WITH subs AS (
|
|
SELECT COUNT(*) AS num, status FROM subscribers GROUP BY status
|
|
)
|
|
SELECT NOW() AS updated_at,
|
|
JSON_BUILD_OBJECT(
|
|
'subscribers', JSON_BUILD_OBJECT(
|
|
'total', (SELECT SUM(num) FROM subs),
|
|
'blocklisted', (SELECT num FROM subs WHERE status='blocklisted'),
|
|
'orphans', (
|
|
SELECT COUNT(id) FROM subscribers
|
|
LEFT JOIN subscriber_lists ON (subscribers.id = subscriber_lists.subscriber_id)
|
|
WHERE subscriber_lists.subscriber_id IS NULL
|
|
)
|
|
),
|
|
'lists', JSON_BUILD_OBJECT(
|
|
'total', (SELECT COUNT(*) FROM lists),
|
|
'private', (SELECT COUNT(*) FROM lists WHERE type='private'),
|
|
'public', (SELECT COUNT(*) FROM lists WHERE type='public'),
|
|
'optin_single', (SELECT COUNT(*) FROM lists WHERE optin='single'),
|
|
'optin_double', (SELECT COUNT(*) FROM lists WHERE optin='double')
|
|
),
|
|
'campaigns', JSON_BUILD_OBJECT(
|
|
'total', (SELECT COUNT(*) FROM campaigns),
|
|
'by_status', (
|
|
SELECT JSON_OBJECT_AGG (status, num) FROM
|
|
(SELECT status, COUNT(*) AS num FROM campaigns GROUP BY status) r
|
|
)
|
|
),
|
|
'messages', (SELECT SUM(sent) AS messages FROM campaigns)
|
|
) AS data;
|
|
DROP INDEX IF EXISTS mat_dashboard_stats_idx; CREATE UNIQUE INDEX mat_dashboard_stats_idx ON mat_dashboard_counts (updated_at);
|
|
|
|
|
|
DROP MATERIALIZED VIEW IF EXISTS mat_dashboard_charts;
|
|
CREATE MATERIALIZED VIEW mat_dashboard_charts AS
|
|
WITH clicks AS (
|
|
SELECT JSON_AGG(ROW_TO_JSON(row))
|
|
FROM (
|
|
WITH viewDates AS (
|
|
SELECT TIMEZONE('UTC', created_at)::DATE AS to_date,
|
|
TIMEZONE('UTC', created_at)::DATE - INTERVAL '30 DAY' AS from_date
|
|
FROM link_clicks ORDER BY id DESC LIMIT 1
|
|
)
|
|
SELECT COUNT(*) AS count, created_at::DATE as date FROM link_clicks
|
|
-- use > between < to force the use of the date index.
|
|
WHERE TIMEZONE('UTC', created_at)::DATE BETWEEN (SELECT from_date FROM viewDates) AND (SELECT to_date FROM viewDates)
|
|
GROUP by date ORDER BY date
|
|
) row
|
|
),
|
|
views AS (
|
|
SELECT JSON_AGG(ROW_TO_JSON(row))
|
|
FROM (
|
|
WITH viewDates AS (
|
|
SELECT TIMEZONE('UTC', created_at)::DATE AS to_date,
|
|
TIMEZONE('UTC', created_at)::DATE - INTERVAL '30 DAY' AS from_date
|
|
FROM campaign_views ORDER BY id DESC LIMIT 1
|
|
)
|
|
SELECT COUNT(*) AS count, created_at::DATE as date FROM campaign_views
|
|
-- use > between < to force the use of the date index.
|
|
WHERE TIMEZONE('UTC', created_at)::DATE BETWEEN (SELECT from_date FROM viewDates) AND (SELECT to_date FROM viewDates)
|
|
GROUP by date ORDER BY date
|
|
) row
|
|
)
|
|
SELECT NOW() AS updated_at, JSON_BUILD_OBJECT('link_clicks', COALESCE((SELECT * FROM clicks), '[]'),
|
|
'campaign_views', COALESCE((SELECT * FROM views), '[]')
|
|
) AS data;
|
|
DROP INDEX IF EXISTS mat_dashboard_charts_idx; CREATE UNIQUE INDEX mat_dashboard_charts_idx ON mat_dashboard_charts (updated_at);
|
|
|
|
-- subscriber counts stats for lists
|
|
DROP MATERIALIZED VIEW IF EXISTS mat_list_subscriber_stats;
|
|
CREATE MATERIALIZED VIEW mat_list_subscriber_stats AS
|
|
SELECT NOW() AS updated_at, lists.id AS list_id, subscriber_lists.status, COUNT(*) AS subscriber_count FROM lists
|
|
LEFT JOIN subscriber_lists ON (subscriber_lists.list_id = lists.id)
|
|
GROUP BY lists.id, subscriber_lists.status
|
|
UNION ALL
|
|
SELECT NOW() AS updated_at, 0 AS list_id, NULL AS status, COUNT(*) AS subscriber_count FROM subscribers;
|
|
DROP INDEX IF EXISTS mat_list_subscriber_stats_idx; CREATE UNIQUE INDEX mat_list_subscriber_stats_idx ON mat_list_subscriber_stats (list_id, status);
|