Development of a custom Telegram notification mechanism for new Isso blog comments

Author Christian Reading time 7 minutes
Development of a custom Telegram notification mechanism for new Isso blog comments

Photo by Ehtiram Mammadov: https://www.pexels.com/photo/wooden-door-between-drawers-on-walls-23322329/

After integrating Isso into my Bludit blog, I am slowly starting to receive comments from my dear readers. As Bludit and Isso are separate, there is no easily visible notification of new comments that need to be approved.

As this is a fairly low traffic blog and I'm not constantly checking Isso manually, this has resulted in a comment being stuck in the moderation queue for 7 days. Only approved after the person contacted me directly to point this out.

Problem identified and the solution that immediately came to mind was the following:

  1. Write a script that will be executed every n minutes by a Systemd timer
  2. This script retrieves the number of Isso comments, pending approval, from the sqlite3 database file
  3. If the number is not zero, send a message via Telegram

This should be a feasible solution as long as currently I receive a somewhat low-amount of comments.

Database internals

Isso stores the comments in a sqlite3 database so our first task is to identify the structure (tables, columns) which store the data we need. With .table we get the tables and with .schema comments we get the SQL-Statement which was used to create the corresponding table.

However using PRAGMA table_info(comments); we get a cleaner list of all columns and the associated datatype. https://www.sqlite.org/pragma.html#toc lists all Pragmas in SQLite.

root@admin /opt/isso/db # cp comments.db testcomments.db

root@admin /opt/isso/db # sqlite3 testcomments.db
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.

sqlite> .table
comments     preferences  threads

sqlite> .schema comments
CREATE TABLE comments (     tid REFERENCES threads(id), id INTEGER PRIMARY KEY, parent INTEGER,     created FLOAT NOT NULL, modified FLOAT, mode INTEGER, remote_addr VARCHAR,     text VARCHAR, author VARCHAR, email VARCHAR, website VARCHAR,     likes INTEGER DEFAULT 0, dislikes INTEGER DEFAULT 0, voters BLOB NOT NULL,     notification INTEGER DEFAULT 0);
CREATE TRIGGER remove_stale_threads AFTER DELETE ON comments BEGIN     DELETE FROM threads WHERE id NOT IN (SELECT tid FROM comments); END;

sqlite> PRAGMA table_info(comments);
0|tid||0||0
1|id|INTEGER|0||1
2|parent|INTEGER|0||0
3|created|FLOAT|1||0
4|modified|FLOAT|0||0
5|mode|INTEGER|0||0
6|remote_addr|VARCHAR|0||0
7|text|VARCHAR|0||0
8|author|VARCHAR|0||0
9|email|VARCHAR|0||0
10|website|VARCHAR|0||0
11|likes|INTEGER|0|0|0
12|dislikes|INTEGER|0|0|0
13|voters|BLOB|1||0
14|notification|INTEGER|0|0|0

From the first look the column mode looks like what we want. To test this I created a new comment which is pending approvement.

user@host /opt/isso/db # sqlite3 testcomments.db <<< 'select * from comments where mode == 2;'
5|7||1724288099.62894||2|ip.ip.ip.ip|etertert|test||https://admin.brennt.net|0|0||0

And we got confirmation as only the new comment is listed. After approving the comment's mode changes to 1. Therefore we found a way to identify comments pending approval.

The check-isso-comments.sh script

Adding a bit of fail-safe and output we hack together the following script.

If you want to use it you need to fill in the values for TELEGRAM_CHAT_ID & TELEGRAM_BOT_TOKEN. Where TELEGRAM_CHAT_ID is the ID of the person who shall receive the messages and TELEGRAM_BOT_TOKEN takes your Bot's Token for accessing Telegrams HTTP-API.

#!/bin/bash
# vim: set tabstop=2 smarttab shiftwidth=2 softtabstop=2 expandtab foldmethod=syntax :

# Bash strict mode
#  read: http://redsymbol.net/articles/unofficial-bash-strict-mode/
set -euo pipefail
IFS=$'\n\t'

# Generic
VERSION="1.0"
#SOURCE="https://github.com/ChrLau/scripts/blob/master/check-isso-comments.sh"
# Values
TELEGRAM_CHAT_ID=""
TELEGRAM_BOT_TOKEN=""
ISSO_COMMENTS_DB=""
# Needed binaries
SQLITE3="$(command -v sqlite3)"
CURL="$(command -v curl)"
# Colored output
RED="\e[31m"
#GREEN="\e[32m"
ENDCOLOR="\e[0m"

# Check that variables are defined
if [ -z "$TELEGRAM_CHAT_ID" ] || [ -z "$TELEGRAM_BOT_TOKEN" ] || [ -z "$ISSO_COMMENTS_DB" ]; then
  echo "${RED}This script requires the variables TELEGRAM_CHAT_ID, TELEGRAM_BOT_TOKEN and ISSO_COMMENTS_DB to be set. Define them at the top of this script.${ENDCOLOR}"
  exit 1;
fi

# Test if sqlite3 is present and executeable
if [ ! -x "${SQLITE3}" ]; then
  echo "${RED}This script requires sqlite3 to connect to the database. Exiting.${ENDCOLOR}"
  exit 2;
fi

# Test if ssh is present and executeable
if [ ! -x "${CURL}" ]; then
  echo "${RED}This script requires curl to send the message via Telegram API. Exiting.${ENDCOLOR}"
  exit 2;
fi

# Test if the Isso comments DB file is readable
if [ ! -r "${ISSO_COMMENTS_DB}" ]; then
  echo "${RED}The ISSO sqlite3 database ${ISSO_COMMENTS_DB} is not readable. Exiting.${ENDCOLOR}"
  exit 3;
fi

COMMENT_COUNT=$(echo "select count(*) from comments where mode == 2" | sqlite3 "${ISSO_COMMENTS_DB}")

TEMPLATE=$(cat <<TEMPLATE
<strong>ISSO Comment checker</strong>

<pre>${COMMENT_COUNT} comments need approval</pre>
TEMPLATE
)

if [ "${COMMENT_COUNT}" -gt 0 ]; then

  /usr/bin/curl --silent --output /dev/null \
    --data-urlencode "chat_id=${TELEGRAM_CHAT_ID}" \
    --data-urlencode "text=${TEMPLATE}" \
    --data-urlencode "parse_mode=HTML" \
    --data-urlencode "disable_web_page_preview=true" \
    "https://api.telegram.org/bot${TELEGRAM_BOT_TOKEN}/sendMessage"

fi

Executing this script on the command-line will already sent a notification to me via Telegram. However I do want this to be automated hence I make use of a Systemd timer.

Systemd configuration

I use the following timer to get notified between 10 to 22 o'clock as there is no need to spam me with message when I can't do anything. I create the files as /etc/systemd/system/isso-comments.timer and /etc/systemd/system/isso-comments.service.

user@host ~ # systemctl cat isso-comments.timer
# /etc/systemd/system/isso-comments.timer
[Unit]
Description=Checks for new, unapproved Isso comments

[Timer]
# Documentation: https://www.freedesktop.org/software/systemd/man/latest/systemd.time.html#Calendar%20Events
OnCalendar=*-*-* 10..22:00:00
Unit=isso-comments.service

[Install]
WantedBy=default.target

The actual script is started by the following service unit file.

user@host ~ # systemctl cat isso-comments.service
# /etc/systemd/system/isso-comments.service
[Unit]
Description=Check for new unapproved Isso comments
After=network-online.target
Wants=network-online.target

[Service]
# Allows the execution of multiple ExecStart parameters in sequential order
Type=oneshot
# Show status "dead" after commands are executed (this is just commands being run)
RemainAfterExit=no
ExecStart=/usr/local/bin/check-isso-comments.sh

[Install]
WantedBy=default.target

After that it's the usual way of activating & starting a new Systemd unit and timer:

user@host ~ # systemctl daemon-reload

user@host ~ # systemctl enable isso-comments.service
Created symlink /etc/systemd/system/default.target.wants/isso-comments.service → /etc/systemd/system/isso-comments.service.
user@host ~ # systemctl enable isso-comments.timer
Created symlink /etc/systemd/system/default.target.wants/isso-comments.timer → /etc/systemd/system/isso-comments.timer.

user@host ~ # systemctl start isso-comments.timer
user@host ~ # systemctl status isso-comments.timer
● isso-comments.timer - Checks for new, unapproved Isso comments
     Loaded: loaded (/etc/systemd/system/isso-comments.timer; enabled; vendor preset: enabled)
     Active: active (waiting) since Mon 2024-09-09 19:33:45 CEST; 2s ago
    Trigger: Mon 2024-09-09 20:00:00 CEST; 26min left
   Triggers: ● isso-comments.service

Sep 09 19:33:45 admin systemd[1]: Started Checks for new, unapproved Isso comments.
user@host ~ # systemctl start isso-comments.service
user@host ~ # systemctl status isso-comments.service
● isso-comments.service - Check for new unapproved Isso comments
     Loaded: loaded (/etc/systemd/system/isso-comments.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Mon 2024-09-09 19:33:58 CEST; 14s ago
TriggeredBy: ● isso-comments.timer
    Process: 421812 ExecStart=/usr/local/bin/check-isso-comments.sh (code=exited, status=0/SUCCESS)
   Main PID: 421812 (code=exited, status=0/SUCCESS)
        CPU: 23ms

Sep 09 19:33:58 admin systemd[1]: Starting Check for new unapproved Isso comments...
Sep 09 19:33:58 admin systemd[1]: isso-comments.service: Succeeded.
Sep 09 19:33:58 admin systemd[1]: Finished Check for new unapproved Isso comments.

user@host ~ # systemctl list-timers
NEXT                         LEFT          LAST                         PASSED        UNIT                         ACTIVATES
Mon 2024-09-09 20:00:00 CEST 22min left    n/a                          n/a           isso-comments.timer          isso-comments.service
Tue 2024-09-10 00:00:00 CEST 4h 22min left Mon 2024-09-09 00:00:00 CEST 19h ago       logrotate.timer              logrotate.service
Tue 2024-09-10 00:00:00 CEST 4h 22min left Mon 2024-09-09 00:00:00 CEST 19h ago       man-db.timer                 man-db.service
Tue 2024-09-10 06:10:34 CEST 10h left      Mon 2024-09-09 06:06:10 CEST 13h ago       apt-daily-upgrade.timer      apt-daily-upgrade.service
Tue 2024-09-10 11:42:59 CEST 16h left      Mon 2024-09-09 19:33:17 CEST 4min 28s ago  apt-daily.timer              apt-daily.service
Tue 2024-09-10 14:22:35 CEST 18h left      Mon 2024-09-09 14:22:35 CEST 5h 15min ago  systemd-tmpfiles-clean.timer systemd-tmpfiles-clean.service
Sun 2024-09-15 03:10:04 CEST 5 days left   Sun 2024-09-08 03:10:27 CEST 1 day 16h ago e2scrub_all.timer            e2scrub_all.service
Mon 2024-09-16 01:21:39 CEST 6 days left   Mon 2024-09-09 00:45:54 CEST 18h ago       fstrim.timer                 fstrim.service

9 timers listed.
Pass --all to see loaded but inactive timers, too.

And now I'm getting informed in time of new comments. Yai!