ansible-role-listmonk/templates/configure.sql.j2

{#- Converges listmonk's database-held configuration. Every statement prints
    the number of rows it changed, so the play can report "changed" and only
    restart listmonk when something actually moved. -#}
{%- macro q(s) -%}$lmv${{ s }}$lmv${%- endmacro -%}
{%- set settings = {
  'app.site_name': listmonk_site_name,
  'app.root_url': listmonk_root_url,
  'app.from_email': listmonk_from_email,
  'app.logo_url': listmonk_logo_url,
  'app.favicon_url': listmonk_favicon_url,
  'app.notify_emails': listmonk_notify_emails,
  'app.enable_public_subscription_page': true,
  'app.enable_public_archive': true,
  'app.send_optin_confirmation': true,
  'privacy.individual_tracking': false,
  'privacy.unsubscribe_header': true,
  'privacy.record_optin_ip': false,
  'security.captcha': {
    'altcha': {'enabled': true, 'complexity': 300000},
    'hcaptcha': {'enabled': false, 'key': '', 'secret': ''},
  },
  'security.oidc': {
    'enabled': listmonk_oidc_enabled | bool,
    'provider_url': listmonk_oidc_provider_url,
    'provider_name': listmonk_oidc_provider_name,
    'client_id': listmonk_oidc_client_id,
    'client_secret': listmonk_oidc_client_secret,
    'auto_create_users': false,
    'default_user_role_id': none,
    'default_list_role_id': none,
  },
  'upload.provider': 'filesystem',
  'upload.filesystem.upload_path': '/listmonk/uploads',
  'upload.filesystem.upload_uri': '/uploads',
  'smtp': [{
    'enabled': true,
    'host': listmonk_smtp_host,
    'port': listmonk_smtp_port | int,
    'auth_protocol': 'login',
    'username': listmonk_smtp_username,
    'password': listmonk_smtp_password,
    'hello_hostname': listmonk_host,
    'max_conns': listmonk_smtp_max_conns | int,
    'idle_timeout': '15s',
    'wait_timeout': '5s',
    'max_msg_retries': 2,
    'msg_retry_delay': '10ms',
    'tls_type': listmonk_smtp_tls_type,
    'tls_skip_verify': false,
    'email_headers': [],
    'from_addresses': [],
  }],
  'appearance.public.custom_css': lookup('ansible.builtin.file', role_path ~ '/files/public.css'),
  'appearance.admin.custom_css': lookup('ansible.builtin.file', role_path ~ '/files/admin.css'),
  'appearance.public.custom_js': lookup('ansible.builtin.file', role_path ~ '/files/public.js'),
} | combine(listmonk_settings_extra) -%}
\set ON_ERROR_STOP on
BEGIN;

WITH desired(key, value) AS (VALUES
{% for k, v in settings.items() %}
  ({{ q(k) }}, {{ q(v | to_json) }}::jsonb){{ ',' if not loop.last }}
{% endfor %}
), changed AS (
  UPDATE settings s SET value = d.value, updated_at = NOW()
  FROM desired d
  WHERE s.key = d.key AND s.value IS DISTINCT FROM d.value
  RETURNING s.key
)
SELECT count(*) FROM changed;

{% for u in listmonk_oidc_admins %}
WITH ins AS (
  INSERT INTO users (username, password_login, email, name, type, user_role_id, status)
  SELECT {{ q(u.username) }}, false, {{ q(u.email | lower) }}, {{ q(u.name) }}, 'user', r.id, 'enabled'
  FROM roles r WHERE r.name = 'Super Admin' AND r.type = 'user'
  ON CONFLICT DO NOTHING
  RETURNING id
)
SELECT count(*) FROM ins;
{% endfor %}

{% for l in listmonk_lists %}
WITH ins AS (
  INSERT INTO lists (uuid, name, type, optin, tags, description)
  SELECT gen_random_uuid(), {{ q(l.name) }}, {{ q(l.type | default('public')) }}::list_type,
         {{ q(l.optin | default('double')) }}::list_optin,
         ARRAY[{% for t in l.tags | default([]) %}{{ q(t) }}{{ ',' if not loop.last }}{% endfor %}]::varchar(100)[],
         {{ q(l.description | default('') | trim) }}
  WHERE NOT EXISTS (SELECT 1 FROM lists WHERE name = {{ q(l.name) }})
  RETURNING id
)
SELECT count(*) FROM ins;
WITH upd AS (
  UPDATE lists SET type = {{ q(l.type | default('public')) }}::list_type,
                   optin = {{ q(l.optin | default('double')) }}::list_optin,
                   description = {{ q(l.description | default('') | trim) }},
                   updated_at = NOW()
  WHERE name = {{ q(l.name) }}
    AND (type, optin, description) IS DISTINCT FROM
        ({{ q(l.type | default('public')) }}::list_type, {{ q(l.optin | default('double')) }}::list_optin, {{ q(l.description | default('') | trim) }})
  RETURNING id
)
SELECT count(*) FROM upd;
{% endfor %}

{#- Scoped API users: the membership app (subscribers) and the newsletter
    automation (campaigns). Each gets a user role and a list role limited to
    its lists. listmonk caches API users at startup; the play restarts it. #}
{%- set api_users = [] %}
{%- if listmonk_api_token | length > 0 %}
{%-   set _ = api_users.append({'username': listmonk_api_user, 'token': listmonk_api_token, 'role': listmonk_api_role,
        'permissions': ['subscribers:get', 'subscribers:manage'], 'lists': listmonk_api_lists}) %}
{%- endif %}
{%- if listmonk_newsletter_api_token | default('') | length > 0 %}
{%-   set _ = api_users.append({'username': listmonk_newsletter_api_user, 'token': listmonk_newsletter_api_token, 'role': listmonk_newsletter_api_role,
        'permissions': ['campaigns:get', 'campaigns:manage', 'campaigns:send', 'templates:get'], 'lists': listmonk_newsletter_api_lists}) %}
{%- endif %}
{% for api in api_users %}
WITH ins AS (
  INSERT INTO roles (type, name, permissions)
  SELECT 'user', {{ q(api.role) }}, ARRAY[{% for p in api.permissions %}{{ q(p) }}{{ ',' if not loop.last }}{% endfor %}]
  WHERE NOT EXISTS (SELECT 1 FROM roles WHERE type = 'user' AND name = {{ q(api.role) }})
  RETURNING id
)
SELECT count(*) FROM ins;
WITH upd AS (
  UPDATE roles SET permissions = ARRAY[{% for p in api.permissions %}{{ q(p) }}{{ ',' if not loop.last }}{% endfor %}], updated_at = NOW()
  WHERE type = 'user' AND name = {{ q(api.role) }}
    AND permissions IS DISTINCT FROM ARRAY[{% for p in api.permissions %}{{ q(p) }}{{ ',' if not loop.last }}{% endfor %}]
  RETURNING id
)
SELECT count(*) FROM upd;
WITH ins AS (
  INSERT INTO roles (type, name)
  SELECT 'list', {{ q(api.role) }}
  WHERE NOT EXISTS (SELECT 1 FROM roles WHERE type = 'list' AND name = {{ q(api.role) }} AND parent_id IS NULL)
  RETURNING id
)
SELECT count(*) FROM ins;
WITH parent AS (
  SELECT id FROM roles WHERE type = 'list' AND name = {{ q(api.role) }} AND parent_id IS NULL
), ins AS (
  INSERT INTO roles (type, parent_id, list_id, permissions)
  SELECT 'list', parent.id, l.id, ARRAY['list:get', 'list:manage']
  FROM parent, lists l
  WHERE l.name IN ({% for n in api.lists %}{{ q(n) }}{{ ',' if not loop.last }}{% endfor %})
  ON CONFLICT (parent_id, list_id) DO NOTHING
  RETURNING id
)
SELECT count(*) FROM ins;
WITH ur AS (
  SELECT id FROM roles WHERE type = 'user' AND name = {{ q(api.role) }}
), lr AS (
  SELECT id FROM roles WHERE type = 'list' AND name = {{ q(api.role) }} AND parent_id IS NULL
), up AS (
  INSERT INTO users (username, password_login, password, email, name, type, user_role_id, list_role_id, status)
  SELECT {{ q(api.username) }}, false, {{ q(api.token | hash('sha256')) }},
         {{ q(api.username ~ '@api') }}, {{ q(api.username) }}, 'api', ur.id, lr.id, 'enabled'
  FROM ur, lr
  ON CONFLICT (username) DO UPDATE
    SET password = EXCLUDED.password, user_role_id = EXCLUDED.user_role_id,
        list_role_id = EXCLUDED.list_role_id, status = 'enabled', updated_at = NOW()
    WHERE (users.password, users.user_role_id, users.list_role_id, users.status)
          IS DISTINCT FROM (EXCLUDED.password, EXCLUDED.user_role_id, EXCLUDED.list_role_id, 'enabled'::user_status)
  RETURNING id
)
SELECT count(*) FROM up;
{% endfor %}

{#- Campaign templates (templates/email/campaign.html.j2): created by name,
    body kept in sync, and the main one is the default for new campaigns. #}
{%- set email_templates = [
  (listmonk_email_template_name, lookup('ansible.builtin.template', 'email/campaign.html.j2', template_vars={'archive': false})),
  (listmonk_email_archive_template_name, lookup('ansible.builtin.template', 'email/campaign.html.j2', template_vars={'archive': true})),
] %}
{% for name, body in email_templates %}
WITH ins AS (
  INSERT INTO templates (name, type, subject, body)
  SELECT {{ q(name) }}, 'campaign', '{{ '{{' }} .Campaign.Subject {{ '}}' }}', {{ q(body) }}
  WHERE NOT EXISTS (SELECT 1 FROM templates WHERE name = {{ q(name) }} AND type = 'campaign')
  RETURNING id
)
SELECT count(*) FROM ins;
WITH upd AS (
  UPDATE templates SET body = {{ q(body) }}, updated_at = NOW()
  WHERE name = {{ q(name) }} AND type = 'campaign' AND body IS DISTINCT FROM {{ q(body) }}
  RETURNING id
)
SELECT count(*) FROM upd;
{% endfor %}
WITH unset AS (
  UPDATE templates SET is_default = false
  WHERE is_default AND NOT (name = {{ q(listmonk_email_template_name) }} AND type = 'campaign')
  RETURNING id
)
SELECT count(*) FROM unset;
WITH setd AS (
  UPDATE templates SET is_default = true
  WHERE id = (SELECT min(id) FROM templates WHERE name = {{ q(listmonk_email_template_name) }} AND type = 'campaign') AND NOT is_default
  RETURNING id
)
SELECT count(*) FROM setd;

{% if listmonk_remove_sample_data | bool %}
WITH del AS (
  DELETE FROM campaigns
  WHERE archive_slug = 'welcome-to-listmonk' AND status = 'draft' AND 'test-campaign' = ANY(tags)
  RETURNING id
)
SELECT count(*) FROM del;
WITH del AS (
  DELETE FROM subscribers WHERE email IN ('john@example.com', 'anon@example.com') RETURNING id
)
SELECT count(*) FROM del;
WITH del AS (
  DELETE FROM lists l
  WHERE l.name IN ('Default list', 'Opt-in list') AND l.tags = ARRAY['test']::varchar(100)[]
    AND NOT EXISTS (SELECT 1 FROM subscriber_lists sl WHERE sl.list_id = l.id)
  RETURNING id
)
SELECT count(*) FROM del;
{% endif %}

COMMIT;