Process inbound mail on exe.dev VMs into an SQLite database

fetch-mail.sh

#!/bin/bash
# Ingest new Maildir messages into ~/mail.db, then delete the files.
#
# schedule in cron with something like:
# * * * * * /home/exedev/bin/fetch-mail.sh >> /home/exedev/.fetch-mail.log 2>&1
set -euo pipefail

DB="$HOME/mail.db"
NEW="$HOME/Maildir/new"
LOCK="$HOME/.fetch-mail.lock"

exec 9>"$LOCK"
flock -n 9 || exit 0

sqlite3 "$DB" <<'SQL'
CREATE TABLE IF NOT EXISTS mail (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  received_at TEXT NOT NULL DEFAULT (datetime('now')),
  filename TEXT,
  delivered_to TEXT,
  from_addr TEXT,
  subject TEXT,
  raw BLOB NOT NULL
);
SQL

python3 - "$NEW" "$DB" <<'PY'
import os, sys, sqlite3, email
from email import policy

newdir, db = sys.argv[1], sys.argv[2]
conn = sqlite3.connect(db)
for name in sorted(os.listdir(newdir)):
    path = os.path.join(newdir, name)
    if not os.path.isfile(path):
        continue
    try:
        with open(path, 'rb') as fh:
            raw = fh.read()
        msg = email.message_from_bytes(raw, policy=policy.default)
        dt = msg.get('Delivered-To')
        fr = msg.get('From')
        sub = msg.get('Subject')
        conn.execute(
            "INSERT INTO mail(filename, delivered_to, from_addr, subject, raw) VALUES(?,?,?,?,?)",
            (name, str(dt) if dt else None, str(fr) if fr else None, str(sub) if sub else None, raw),
        )
        conn.commit()
        os.remove(path)
    except Exception as e:
        sys.stderr.write(f"failed {name}: {e}\n")
conn.close()
PY