Listening for Listmonk Subscriber Confirmations in Node-RED via Postgres LISTEN/NOTIFY

This tutorial sets up a real-time trigger that fires whenever a subscriber confirms their double opt-in on a Listmonk list. Listmonk itself has no outgoing webhook for this event (as of v6.2.0), so we tap into the underlying Postgres database directly using LISTEN/NOTIFY, and pick it up in Node-RED with node-red-contrib-digitaloak-postgresql.

Prerequisites #

• A running Listmonk instance backed by Postgres
• Node-RED with access to the same Postgres instance (same Docker network / Swarm stack)
• Database credentials that already work for regular queries against the listmonk database (no special grants are needed for LISTEN/NOTIFY — it’s a session-level pub/sub mechanism, not tied to table permissions)

1. Create the Postgres trigger #

Connect to the Listmonk database:

docker exec -it <listmonk-db-container> psql -U listmonk -d listmonk

Create a function that fires a notification whenever a subscriber’s status on a list changes to confirmed:

CREATE OR REPLACE FUNCTION notify_subscriber_confirmed() RETURNS trigger AS $$
BEGIN
  IF NEW.status = 'confirmed' AND (OLD.status IS DISTINCT FROM 'confirmed') THEN
    PERFORM pg_notify('subscriber_confirmed',
      json_build_object(
        'subscriber_id', NEW.subscriber_id,
        'list_id', NEW.list_id,
        'confirmed_at', now()
      )::text
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_subscriber_confirmed
AFTER UPDATE ON subscriber_lists
FOR EACH ROW
EXECUTE FUNCTION notify_subscriber_confirmed();

Listmonk’s actual confirmation query (confirm-subscription-optin) does a plain UPDATE subscriber_lists SET status='confirmed' ..., so this trigger fires correctly regardless of whether the confirmation comes from the double opt-in email link or a manual admin action.

Verify the trigger exists #

SELECT tgname FROM pg_trigger WHERE tgname = 'trg_subscriber_confirmed';
SELECT proname FROM pg_proc WHERE proname = 'notify_subscriber_confirmed';

Both should return one row. If pgAdmin doesn’t show the trigger/function in its object browser, refresh the tree (F5) or double check you’re looking at the right server/database — pgAdmin caches its object tree and won’t auto-refresh after objects are created outside its own Query Tool.

Test it manually #

In one psql/pgAdmin session:

LISTEN subscriber_confirmed;

In another session:

UPDATE subscriber_lists SET status = 'confirmed' WHERE subscriber_id = <test id>;

Back in the first session, run any command (even SELECT 1;) — psql only prints buffered async notifications when it processes the next command, so an idle terminal can look like nothing arrived even when it did.

2. Install the Node-RED nodes #

In Node-RED: Menu (☰) → Manage palette → Install tab, search for and install:

@digitaloak/node-red-contrib-digitaloak-postgresql

This package bundles three node types in one install: a connection manager (config node), a query node, and a listen node. The listen node uses its own dedicated client, separate from the query node’s connection pool.

3. Configure the connection manager #

Drag a listen node onto the canvas. Next to the config dropdown, click the pencil icon to create a new connection manager:

Host:     <your Postgres host, e.g. Swarm service name of the DB container>
Port:     5432
Database: listmonk
User:     listmonk
Password: <password>

These are the same credentials Listmonk itself uses in its docker-compose.yml / config.toml under [db].

⚠️ Known issue: “client for communication with database is disabled” #

Even with everything configured correctly and the client marked as enabled, the listen node may still throw:

cannot subscribe to the channel 'subscriber_confirmed', connectionManager '<id>' (listmonk-db):
client for communication with database is disabled (you can change its status via connection
configuration in connectionManager node).

This happened even when the “enabled” setting was already turned on. The fix that worked:

1. Open the connection manager node
2. Set it to disabled
3. Deploy
4. Open it again, set it back to enabled
5. Deploy again

The error went away after this forced toggle. This looks like a state-initialization bug in the node — the client apparently doesn’t correctly pick up its enabled status on a cold deploy, but does pick it up on a state transition (disabled → enabled). If you hit this error on first setup, don’t assume your configuration is wrong — try the toggle first.

4. Configure the listen node #

On the listen node itself:

Channel: subscriber_confirmed

This must match the channel name used in pg_notify() exactly, including case.

5. Parse the payload #

pg_notify sends its payload as plain text, so the listen node’s output needs one parsing step before the rest of your flow can use it. Add a function node after the listen node:

msg.payload = JSON.parse(msg.payload.payload);
return msg;

After this, msg.payload.subscriber_id and msg.payload.list_id are usable values.

6. Add a debug node #

To see notifications arrive live while building/testing the flow, wire a debug node right after the parsing function node:

[listen: subscriber_confirmed] → [function: parse payload] → [debug]

Open the Debug panel (bug icon, top right of the editor) and deploy. Every confirmation will show up here immediately with the parsed subscriber_id / list_id / confirmed_at.

If you want a persistent, at-a-glance view outside the editor (e.g. on a phone), swap the debug node for node-red-dashboard’s ui_table/ui_template, feeding it a rolling list kept in flow context:

let log = flow.get('confirm_log') || [];
log.unshift({
    time: new Date().toLocaleString(),
    subscriber_id: msg.payload.subscriber_id,
    list_id: msg.payload.list_id
});
flow.set('confirm_log', log.slice(0, 50));
msg.payload = log;
return msg;

Dashboard is then reachable at http://<node-red-host>:1880/ui.

7. Downstream flow (idempotency) #

Before acting on a notification, check whether this subscriber has already been processed — Postgres NOTIFY delivers to every active listener, and if you ever run more than one instance of a listener process (e.g. during a rolling deploy), the same event can legitimately arrive more than once:

[debug/function] → [postgresql query: SELECT 1 FROM automation.sequence_state WHERE subscriber_id = $1]
  → [switch: row exists?]
      NO  → insert into sequence_state, send transactional email via Listmonk /api/tx
      YES → discard (already handled)

This makes the flow safe against duplicate deliveries regardless of their source.

Caveats to keep in mind #

• This trigger lives outside Listmonk’s own schema management. It’s not documented or supported by the Listmonk project. After a listmonk --upgrade, re-check that the trigger still exists (see the verification query in step 1) — a future schema migration touching subscriber_lists could theoretically remove it.
• Keep a fallback poll (e.g. once daily) that catches any subscriber confirmed via subscriber_lists.status = 'confirmed' but missing from your own state table, in case the trigger or listener silently stops working between checks.
• Don’t call outbound HTTP directly from the Postgres trigger function. Keep it to pg_notify only — a blocking or slow HTTP call inside a trigger would also block Listmonk’s own write transaction that confirms the subscriber.