feat(email): track bounces

[dargmuesli]

We need to track email bounces so we don't continue sending emails to addresses which do not exist.

The AWS Simple Email Service (SES) can send notifications, maybe to some API endpoint at maevsi (Nuxt server) which can then add bounce information to the database. Relevant email sending functionality could then e.g. block sending emails to addresses that led to a bounce. There could be a cooldown or – if too complex – a support ticket necessary for users who wish to unblock an address.

This ticket is about creating a database table to track such bounces.

Here is exemplary JSON that AWS SES sent through AWS SNS:

{
  "eventType": "Bounce",
  "bounce": {
    "feedbackId": "01070193cf31534a-2ce1ba85-****-****-****-9c0668bb62a0-000000",
    "bounceType": "Permanent",
    "bounceSubType": "General",
    "bouncedRecipients": [
      {
        "emailAddress": "*******@yahoo.com",
        "action": "failed",
        "status": "5.3.0",
        "diagnosticCode": "smtp; 552 1 Requested mail action aborted, mailbox not found"
      }
    ],
    "timestamp": "2024-12-16T11:18:56.664Z",
    "remoteMtaIp": "**.***.***.**",
    "reportingMTA": "dns; b224-2.smtp-out.eu-central-1.amazonses.com"
  },
  "mail": {
    "timestamp": "2024-12-16T11:18:54.839Z",
    "source": "[email protected]",
    "sourceArn": "arn:aws:ses:eu-central-1:967007******:identity/*******",
    "sendingAccountId": "967007******",
    "messageId": "01070193cf314c77-030ab73a-****-****-****-1a53a0b7e396-000000",
    "destination": ["*******@yahoo.com"],
    "headersTruncated": false,
    "headers": [
      {
        "name": "List-Unsubscribe",
        "value": "mailto:[email protected]?subject=Unsubscribe%20*******@yahoo.com"
      },
      { "name": "From", "value": "maevsi <[email protected]>" },
      { "name": "To", "value": "*******@yahoo.com" },
      { "name": "Subject", "value": "Complete registration" },
      {
        "name": "Message-ID",
        "value": "<99025d77-****-****-****[email protected]>"
      },
      { "name": "Date", "value": "Mon, 16 Dec 2024 11:18:52 +0000" },
      { "name": "MIME-Version", "value": "1.0" },
      {
        "name": "Content-Type",
        "value": "multipart/alternative; boundary=\"--_NmP-a0960bdd866868c0-Part_1\""
      }
    ],
    "commonHeaders": {
      "from": ["maevsi <[email protected]>"],
      "date": "Mon, 16 Dec 2024 11:18:52 +0000",
      "to": ["*******@yahoo.com"],
      "messageId": "01070193cf314c77-030ab73a-****-****-****-1a53a0b7e396-000000",
      "subject": "Complete registration"
    },
    "tags": {
      "ses:source-tls-version": ["TLSv1.3"],
      "ses:operation": ["SendRawEmail"],
      "ses:configuration-set": ["default"],
      "ses:source-ip": ["**.**.***.***"],
      "ses:from-domain": ["maev.si"],
      "ses:caller-identity": ["************"]
    }
  }
}

{
  "notificationType": "Bounce",
  "bounce": {
    "feedbackId": "01070193cf31534a-2ce1ba85-****-****-****-9c0668bb62a0-000000",
    "bounceType": "Permanent",
    "bounceSubType": "General",
    "bouncedRecipients": [
      {
        "emailAddress": [*******@yahoo.com](mailto:*******@yahoo.com),
        "action": "failed",
        "status": "5.3.0",
        "diagnosticCode": "smtp; 552 1 Requested mail action aborted, mailbox not found"
      }
    ],
    "timestamp": "2024-12-16T11:18:56.000Z",
    "remoteMtaIp": "**.***.***.**",
    "reportingMTA": "dns; b224-2.smtp-out.eu-central-1.amazonses.com"
  },
  "mail": {
    "timestamp": "2024-12-16T11:18:54.839Z",
    "source": [[email protected]](mailto:[email protected]),
    "sourceArn": "arn:aws:ses:eu-central-1:967007******:identity/*******",
    "sourceIp": "**.**.***.***",
    "callerIdentity": "*************",
    "sendingAccountId": "967007******",
    "messageId": "01070193cf314c77-030ab73a-****-****-****-1a53a0b7e396-000000",
    "destination": [[*******@yahoo.com](mailto:*******@yahoo.com)]
  }
}