Gestione degli eventi di notifica AWS SES in SQL come email in Outlook

Riepilogo

Il post descrive una sfida riscontrata durante l’invio di grandi lotti di email annuali utilizzando AWS Simple Email Service (SES). Poiché molti indirizzi diventano non validi nel tempo, AWS SES genera notifiche di evento (inclusi dettagli in formato JSON come tipo di evento, metadati della mail e motivi del bounce) per i messaggi non recapitabili. Tradizionalmente queste notifiche venivano gestite manualmente da una casella di posta dedicata, ma questo diventa impraticabile con grandi volumi.

Per affrontare questo problema, l’autore delinea un approccio automatizzato utilizzando Invantive UniversalSQL insieme a Outlook. Innanzitutto, tutte le notifiche di evento SES vengono raccolte da una cartella di Outlook. Poi, utilizzando Invantive Query Tool connesso a Outlook tramite un driver COM, le query SQL possono leggere le email ed estrarre il corpo completo. Il contenuto JSON viene analizzato direttamente all’interno di SQL utilizzando jsontable. Questo consente l’estrazione strutturata dei dettagli chiave dell’evento (come il tipo di bounce e gli indirizzi dei destinatari rimbalzati) in una tabella in memoria per ulteriori analisi ed elaborazioni, evitando la gestione manuale.

Gestione delle notifiche di evento di bounce AWS SES

Ogni anno, in concomitanza con il cambio più comune dell’esercizio fiscale, Invantive invia un grande lotto di email con suggerimenti per chiudere l’esercizio fiscale in corso e iniziare il nuovo. Non è raro che alcuni indirizzi email non siano più disponibili, fatto che viene registrato tramite una notifica di evento AWS SES.

A questo scopo esiste una casella di posta dedicata, con in generale al massimo poche email al giorno. Queste vengono controllate manualmente e viene intrapresa un’azione correttiva.

Ma durante l’invio annuale in blocco, il numero di email non recapitabili è superiore a quanto sia ragionevole gestire manualmente, generando molte email come:

con - oltre al testo - anche JSON:

{
  "eventType": "DeliveryDelay",
  "mail": {
    "timestamp": "2025-12-23T18:13:02.762Z",
    "source": "sales@invantive.eu",
    "sourceArn": "arn:aws:ses:eu-west-1:1234567:identity/invantive.eu",
    "sendingAccountId": "1234567",
    "messageId": "0102019b4c6a22aa-5a2ea763-b123-4235-a9d2-8412b8d0fee8-000000",
    "destination": [
      "john.doe@acme.eu"
    ],
    "headersTruncated": false,
    "headers": [
      {
        "name": "Date",
        "value": "Tue, 23 Dec 2025 18:13:02 +0000"
      },
      {
        "name": "From",
        "value": "sales@invantive.eu"
      },
      {
        "name": "To",
        "value": "john.doe@acme.eu"
      },
      {
        "name": "Subject",
        "value": "Tip on fiscal year"
      },
      {
        "name": "MIME-Version",
        "value": "1.0"
      },
      {
        "name": "Content-Type",
        "value": "multipart/alternative;  boundary=\"----=_Part_1300819_1123446008.1766513582814\""
      }
    ],
    "commonHeaders": {
      "from": [
        "sales@invantive.eu"
      ],
      "date": "Tue, 23 Dec 2025 18:13:02 +0000",
      "to": [
        "john.doe@acme.eu"
      ],
      "messageId": "0102019b4c6a22aa-5a2ea763-b123-4235-a9d2-8412b8d0fee8-000000",
      "subject": "Tip on fiscal year"
    },
    "tags": {
      "ses:source-tls-version": [
        "TLSv1.3"
      ],
      "ses:operation": [
        "SendTemplatedEmail"
      ],
      "ses:configuration-set": [
        "default"
      ],
      "ses:recipient-isp": [
        "UNKNOWN_ISP"
      ],
      "ses:source-ip": [
        "80.61.37.124"
      ],
      "ses:from-domain": [
        "invantive.eu"
      ],
      "ses:sender-identity": [
        "invantive.eu"
      ],
      "ses:caller-identity": [
        "s-stripo"
      ]
    }
  },
  "deliveryDelay": {
    "timestamp": "2025-12-24T08:13:03.107Z",
    "delayType": "TransientCommunicationFailure",
    "expirationTime": "2025-12-24T08:13:03.463Z",
    "delayedRecipients": [
      {
        "emailAddress": "john.doe@acme.eu",
        "status": "4.4.0",
        "diagnosticCode": "smtp; 421 4.4.0 Unable to lookup DNS for acme.eu"
      }
    ]
  }
}

Ovviamente è anche possibile indirizzare tali notifiche di evento verso un database o uno storage di file. In questo caso, con l’utilizzo di AWS SES in rapido calo all’interno di Invantive, è stata colta l’occasione per elaborare queste notifiche di evento utilizzando Invantive UniversalSQL.

Elaborazione automatizzata della posta

Il primo passo è stato scaricare tutti gli eventi in una casella di posta Outlook. Con Outlook (classic) ancora in esecuzione, è stato avviato Invantive Query Tool ed è stata stabilita una connessione utilizzando un qualsiasi driver gratuito o premium (come Dummy).

Invantive Query Tool si è connesso anche all’istanza COM di Outlook ed è stato possibile determinare la cartella di posta utilizzando:

select *
from   Folders@outlook fdr

Successivamente, sono state accedute tutte le mail includendo l’idratazione del corpo completo della mail tramite includeFullBody e filtrando sull’oggetto “Amazon SES Email Event Notification”:

select mal.Body
from   Folders@outlook fdr
join   Mails@outlook
       ( folderEntryId => fdr.EntryID
       , includeFullBody => true
       ) mal
where  mal.ConversationTopic = 'Amazon SES Email Event Notification'

Il testo del corpo della mail conteneva anche un testo di firma oltre al JSON, quindi sono state necessarie alcune correzioni per mantenere solo il JSON. L’istruzione SQL completa per recuperare i motivi di bounce e gli indirizzi email coinvolti era:

create or replace table BounceEvents@InMemoryStorage
as
select distinct jte.*
from   Folders@outlook fdr
join   Mails@outlook
       ( folderEntryId => fdr.EntryID
       , includefullbody => true
       ) mal
join   jsontable
       ( ''
         passing trim(replace(substr(body, 1, regexp_instr(body, '--\r.*', 1, 1, 1, 'm') - 4), chr(13), '', chr(10), ''))
         columns eventType  varchar2 path 'eventType'
         ,       bounceType varchar2 path 'bounce.bounceType'
         ,       bouncedRecipient1 varchar2 path 'bounce.bouncedRecipients[0].emailAddress'
       ) jte
on     jte.EventType not in ('DeliveryDelay')
where  mal.ConversationTopic = 'Amazon SES Email Event Notification'

Le righe in BounceEvents@InMemoryStorage potevano poi essere ulteriormente analizzate ed elaborate.