Send a Daily Summary Email

Introduction

Scheduled Triggers allows you to schedule business, or other logic to occur at specific times or intervals. In this guide, we'll explore how to use Scheduled Triggers to send each user a daily summary of recent notifications which they have received. We'll do this by executing this trigger every morning and seeing what new notifications have come through in the last twenty-four hours. If a user has new notifications, they'll get an email listing them all.

DOCS E-COMMERCE SAMPLE APP

This quickstart/recipe is dependent upon the docs e-commerce sample app. If you haven't already deployed the sample app, you can do so with one click below. If you've already deployed the sample app, simply use your existing project.

Prerequisites

Before getting started, ensure that you have the following in place:

• The docs e-commerce sample app deployed to Hasura Cloud.
• A working SMTP server or email-sending service that you can integrate with to send emails.

Tunneling your webhook endpoint from your local machine

If you plan on using a webhook endpoint hosted on your own machine, ensure that you have a tunneling service such as ngrok set up so that your Cloud Project can communicate with your local machine.

Our model

When sending transactional emails such as this, there are three fundamental components to consider:

• Your data source: In your database, which table contains the value that you want to use to determine whether or not to send the email?
• Your querying logic: In your webhook, how will you query your database to determine whether or not to send the email? How will you return information so that you have the correct data to include in the email?
• Your email templating: How will you generate and send the email containing the information you want to send?

Our sample app's database contains, among others, two tables: users and notifications. The users table contains the details of all users, including the user's email address. The notifications table contains all notifications sent to users. Each notification has a user_id property that references the user to whom the notification was sent.

Step 1: Create the Scheduled Event

Head to the Hasura Console of your project and navigate to the "Events" tab. From there, click on the Cron Triggers item in the sidebar. Then, click Create:

Step 2: Configure the Scheduled Event

First, provide a name for your trigger, e.g., daily_recap_email. Then, enter a webhook URL that will be called when the event is triggered. This webhook will be responsible for sending the summary email and can be hosted anywhere, and written in any language, you like.

The route on our webhook we'll use is /daily-summary. Below, we'll see what this looks like with a service like ngrok, but the format will follow this template:

https://<your-webhook-url>/daily-summary

Tunneling your webhook endpoint to your local machine

You'll need to use a tunneling service such as ngrok to expose a webhook endpoint running on your local machine to the internet and Hasura Cloud. This will give you a public URL that will forward requests to your local machine and the server which we'll configure below.

You'll need to modify your webhook URL to use the public URL provided by ngrok.

After installing ngrok and authenticating, you can do this by running:

ngrok http 4000

Then, copy the Forwarding value for use in our webhook 🎉

Next, we'll configure the cron expression that will trigger the event. In this example, we want to send requests at 9:00 AM every morning. We can do that with the following cron expression:

0 9 * * *

Our trigger must also have a payload. This payload will be sent to the webhook endpoint when the event is triggered. We don't have to include any data in the payload, but we can if we want to. In this example, we'll simply send a trigger_type property categorizing the event as a daily_recap_email. In the Payload section, enter the following:

{
  "trigger_type": "daily_recap_email"
}

Under Advanced Settings, we can configure the headers that will be sent with the request. We'll add an authentication header to prevent abuse of the endpoint and ensure that only Hasura can trigger the event. Set the Key as secret-authorization-string and the Value as super_secret_string_123.

Also, change the Request Transform Options to POST so that the payload is sent in the request body.

Finally, click the Add Cron Trigger button to create the Scheduled Event.

Step 3: Create a webhook to handle the request

Whenever a cron job is triggered, Hasura will send a request to the webhook URL you provided. In this example, we're simply going to send a POST request. Our webhook will parse the request, ensure the header is correct, and then query the database to determine which customers should receive a summary of their new notifications.

Below, we've written an example of webhook. As we established earlier, this runs on port 4000. If you're attempting to run this locally, follow the instructions below. If you're running this in a hosted environment, use this code as a guide to write your own webhook.

JavaScript

Init a new project with npm init and install the following dependencies:

npm install express nodemailer

Then, create a new file called index.js and add the following code:

const express = require('express');
const nodemailer = require('nodemailer');

const app = express();

// Create a Nodemailer transporter using Ethereal email service
// Ideally, this configuration would be stored somewhere else
nodemailer.createTestAccount((err, account) => {
  if (err) {
    console.error('Failed to create a testing account. ' + err.message);
    return process.exit(1);
  }

  // If all goes as planned, here's the console telling us we're 👍
  console.log('Credentials obtained, listening on the webhook...');

  // Create a transporter object for nodemailer
  const transporter = nodemailer.createTransport({
    host: 'smtp.ethereal.email',
    port: 587,
    secure: false,
    auth: {
      user: account.user,
      pass: account.pass,
    },
  });

  // Our route for the webhook
  app.post('/daily-summary', async (req, res) => {
    // confirm the auth header is correct — ideally, you'd keep the secret in an environment variable
    const authHeader = req.headers['secret-authorization-string'];
    if (authHeader !== 'super_secret_string_123') {
      return res.status(401).json({
        message: 'Unauthorized',
      });
    }

    // get our date ready for the query
    const today = new Date();
    const twentyFourHoursPrior = new Date(today.setDate(today.getDate() - 1));
    const twentyFourHoursPriorAsTimestamp = twentyFourHoursPrior.toISOString();

    // Fetch the data from our Hasura instance
    async function getRecentNotifications() {
      const response = await fetch('<YOUR_CLOUD_PROJECT_ENDPOINT>', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'x-hasura-admin-secret': '<YOUR_ADMIN_SECRET>',
        },
        body: JSON.stringify({
          query: `
          query DailyNotificationsQuery($start_time: timestamptz!) {
            notifications(where: {created_at: {_gte: $start_time}}) {
              id
              message
              user {
                id
                email
                name
              }
            }
          }          
          `,
          variables: {
            start_time: twentyFourHoursPriorAsTimestamp,
          },
        }),
      });
      const { data } = await response.json();
      return data.notifications;
    }

    // get our users and filter out the ones with no notifications
    let notifications = await getRecentNotifications();

    // map over the data and send an email to each user
    async function sendNotificationSummary(notifications) {
      let outcomes = [];
      notifications.map(async notification => {
        // Create a message object
        const message = {
          from: 'SuperStore.com <[email protected]>',
          to: `${notification.user.name} <${notification.user.email}>`,
          subject: `You've got new notifications, ${notification.user.name.split(' ')[0]}!`,
          text: `Hi ${notification.user.name.split(' ')[0]},\n\nCheck out your recent notifications:\n\n${
            notification.message
          }\n\nThanks,\nSuperStore.com`,
        };

        // Send the message using the Nodemailer transporter
        const info = await transporter.sendMail(message);

        // Log the message info
        console.log(`\nMessage sent to ${notification.user.name}: ${nodemailer.getTestMessageUrl(info)}`);

        // add the info to the outcomes array
        outcomes.push({
          messageId: info.messageId,
          previewUrl: nodemailer.getTestMessageUrl(info),
        });
        return outcomes;
      });
    }

    await sendNotificationSummary(notifications);

    // Return a JSON response to the client
    res.json({
      message: 'Notifications sent!',
    });
  });

  // Start the server
  app.listen(4000, () => {
    console.log('Server started on port 4000');
  });
});

You can run the server by running node index.js in your terminal.

Python

Make sure you have the necessary dependencies installed. You can use pip to install them:

pip install Flask secure-smtplib requests

Then, create a new file called index.py and add the following code:

from flask import Flask, request, jsonify
import smtplib
from smtplib import SMTPException
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from datetime import datetime, timedelta
import requests

app = Flask(__name__)

# Create an SMTP server connection
def create_smtp_server():
    try:
        server = smtplib.SMTP(host='smtp.ethereal.email', port=587)
        server.starttls()
        server.login('[email protected]', 'samplePassword123')
        return server
    except SMTPException as e:
        print(f"An error occurred while creating the SMTP server: {e}\n\n🔔We'll print the message to the terminal:\n\n")
        return None

@app.route('/daily-summary', methods=['POST'])
def send_daily_summary():
    # Confirm the authentication header is correct
    auth_header = request.headers.get('secret-authorization-string')
    if auth_header != 'super_secret_string_123':
        return jsonify({'message': 'Unauthorized'}), 401

    # Get the date for the query
    today = datetime.now()
    twenty_four_hours_prior = today - timedelta(days=1)

    # Fetch data from your Hasura instance (replace with actual endpoint and admin secret)
    endpoint = '<YOUR_CLOUD_PROJECT_ENDPOINT>'
    admin_secret = '<YOUR_ADMIN_SECRET>'
    query = {
        "query": """
        query DailyNotificationsQuery($start_time: timestamptz!) {
          notifications(where: {created_at: {_gte: $start_time}}) {
            id
            message
            user {
              id
              email
              name
            }
          }
        }
        """,
        "variables": {"start_time": twenty_four_hours_prior.isoformat()}
    }
    headers = {
        'Content-Type': 'application/json',
        'x-hasura-admin-secret': admin_secret
    }
    response = requests.post(endpoint, json=query, headers=headers)
    data = response.json()
    notifications = data.get('data', {}).get('notifications', [])

    # Create an email and send it for each notification
    recipients = []
    for notification in notifications:
        # Create a message
        msg = MIMEMultipart()
        msg['From'] = '[email protected]'
        msg['To'] = f"{notification['user']['name']} <{notification['user']['email']}>"
        msg['Subject'] = f"You've got new notifications, {notification['user']['name'].split(' ')[0]}!"
        message_body = f"Hi {notification['user']['name'].split(' ')[0]},\n\nCheck out your recent notifications:\n\n{notification['message']}\n\nThanks,\nSuperStore.com"
        msg.attach(MIMEText(message_body, 'plain'))

        server = create_smtp_server()
        if server is not None:
            # Send the email if server is running
            server.sendmail('[email protected]', notification['user']['email'], msg.as_string())
            server.quit()
        else:
            # or just print the message to the terminal
            print(f"From: {msg['From']}\nTo: {msg['To']}\nSubject: {msg['Subject']}\n{message_body}")

        recipients.append(notification['user']['email'])

    return jsonify({'message': 'Notifications sent!', 'recipients': recipients})

if __name__ == '__main__':
    app.run(port=4000)

You can run the server by running python index.py in your terminal.

If you see the message Webhook server is running on port 4000, you're good to go!

Step 4: Test the setup

With your server running, Hasura should start hitting our endpoint. As we set our cron expression to 0 9 * * *, the webhook will be triggered at 9:00 AM every day. We don't want to wait that long to test it. For now, update the expression to * * * * * to trigger the webhook every minute. Then, check out your invocation logs in the Hasura Console to verify that the webhook was triggered successfully and your terminal to see the outputted information and link to a handy email 🎉

The screenshot above illustrates the output of the Node.js version of the webhook.

Feel free to customize the webhook implementation based on your specific requirements and chosen email sending service. Remember to handle error scenarios, implement necessary validations, and add appropriate security measures to your webhook endpoint.