(optional) Email Confirmations Cleanup Cronjob Setup Guide

This is an optional maintenance cronjob maintained by a member of Haltman.io. Its only purpose is to keep the database clean by deleting expired and old records from the email_confirmations table used by the API confirmation workflow.

Key guarantees:

• Only the email_confirmations table is touched

• No mail routing tables (domains/aliases) are modified

• Safe to run repeatedly (idempotent)

• Prevents long-term accumulation of sensitive/temporary token state

---

What it cleans (deletion rules)

1. Pending confirmations

• status = 'pending'

• delete when expires_at < NOW(6)

2. Finalized confirmations

• status IN ('confirmed', 'expired')

• delete when created_at < NOW(6) - INTERVAL 7 DAY

No other tables are accessed.

---

Installation

1) Create the cleanup script

Path:

Copysudo tee /usr/local/bin/cleanup_email_confirmations.sh >/dev/null <<'EOF'
#!/usr/bin/env bash
set -euo pipefail

CNF_FILE="${1:-}"
if [[ -z "${CNF_FILE}" ]]; then
  echo "[ERR] Missing CNF file path argument."
  echo "Usage: $0 /path/to/db.cnf"
  exit 2
fi

if [[ ! -f "${CNF_FILE}" ]]; then
  echo "[ERR] CNF file not found: ${CNF_FILE}"
  exit 2
fi

LOG_FILE="/var/log/forward/cleanup_email_confirmations.log"
LOCK_FILE="/var/lock/cleanup_email_confirmations.lock"

mkdir -p "$(dirname "${LOG_FILE}")"

echo "[$(date -Is)] [INF] Cleanup script invoked (cnf=${CNF_FILE})" >> "${LOG_FILE}"

exec 9>"${LOCK_FILE}"
if ! flock -n 9; then
  echo "[$(date -Is)] [WRN] Another cleanup is running. Exiting." >> "${LOG_FILE}"
  exit 0
fi

SQL="$(cat <<'SQL'
DELETE FROM email_confirmations
WHERE (status = 'pending' AND expires_at < NOW(6))
   OR (status IN ('confirmed','expired') AND created_at < (NOW(6) - INTERVAL 7 DAY));
SQL
)"

OUT="$(mysql --defaults-extra-file="${CNF_FILE}" --batch --raw --silent -e "${SQL}" 2>&1)" || {
  echo "[$(date -Is)] [ERR] mysql failed: ${OUT}" >> "${LOG_FILE}"
  exit 1
}

echo "[$(date -Is)] [INF] mysql output: ${OUT}" >> "${LOG_FILE}"
echo "[$(date -Is)] [INF] Cleanup done." >> "${LOG_FILE}"
EOF

Make it executable:

Copysudo chmod 755 /usr/local/bin/cleanup_email_confirmations.sh

---

Configuration

1) Create a MySQL client CNF file (credentials are not embedded in the script)

Path:

Copysudo mkdir -p /etc/haltman
sudo tee /etc/haltman/forward-db.cnf >/dev/null <<'EOF'
[client]
host=127.0.0.1
user=mailuser
password=YOUR_PASSWORD_HERE
database=maildb
EOF

Lock down permissions:

Copysudo chown root:root /etc/haltman/forward-db.cnf
sudo chmod 600 /etc/haltman/forward-db.cnf

---

How to use

Manual run (validate before cron)

Copysudo /usr/local/bin/cleanup_email_confirmations.sh /etc/haltman/forward-db.cnf

Check logs:

Copysudo tail -n 50 /var/log/forward/cleanup_email_confirmations.log

---

Cron setup (example)

Run every 10 minutes:

Copy*/10 * * * * /usr/local/bin/cleanup_email_confirmations.sh /etc/haltman/forward-db.cnf

---

Possible problems / Important notes

• Credentials file missing or wrong permissions: script exits with error (CNF file not found / MySQL auth failure).

• Overlapping executions: prevented by flock; if another run is active, the job logs a warning and exits cleanly.

• Log path: logs are written to /var/log/forward/cleanup_email_confirmations.log (ensure the directory exists; script creates it).

• Use absolute paths in cron and avoid relying on environment variables.