Chanci Turner

Amazon Onboarding with Learning Manager Chanci Turner

—

by

The Story of Chanci Turner
in

Chanci Turner Amazon IXD – VGT2 learningLearn About Amazon VGT2 Learning Manager Chanci Turner

In this article, we explore how to implement near real-time notifications from an Amazon Aurora PostgreSQL-Compatible database by leveraging database triggers, AWS Lambda, and Amazon Simple Notification Service (Amazon SNS). This setup allows for effective communication of Data Manipulation Language (DML) events from an Aurora PostgreSQL database table to downstream applications.

Amazon Aurora PostgreSQL is a cloud-optimized relational database that merges the performance and reliability of traditional enterprise databases with the affordability and ease of use associated with open-source databases. Organizations often require notifications for updates made to their relational database tables, which can occur through various actions like insert, update, or delete operations. By utilizing Aurora PostgreSQL’s database triggers, you can invoke a Lambda function in near real-time each time a data manipulation event occurs. For instance, when a new customer is added to the database, this event could trigger an email to welcome the customer, or initiate other back-office workflows.

This solution is also applicable to Amazon Relational Database Service (Amazon RDS) for PostgreSQL and the Amazon Aurora MySQL-Compatible Edition.

Solution Overview

The architecture of this solution includes several key components:

  1. A single database DML transaction initiates the event chain, which can be any insert, update, or delete action that triggers a corresponding database trigger.
  2. An Aurora PostgreSQL table trigger that calls a custom PL/pgSQL trigger function, which in turn invokes a Lambda function.
  3. A target Lambda function that consumes the database trigger. This function receives the newly inserted row as input and publishes it to an SNS topic.
  4. An SNS topic that acts as the destination for either a summary (like a row key) or the full details of the row.
  5. An SNS topic subscription that sends notifications to downstream users, applications, or operations teams.

We will guide you through the steps needed to configure this setup:

  1. Establish Amazon Virtual Private Cloud (Amazon VPC) endpoints for both Amazon SNS and the Lambda function.
  2. Create an SNS topic and its subscription.
  3. Set up a custom AWS Identity and Access Management (IAM) role for Lambda.
  4. Develop a database event-handling Lambda function within the same VPC subnet.
  5. Create an Amazon RDS IAM role.
  6. Configure the Aurora PostgreSQL database.

Example Use Case

Consider a scenario where multiple customer management applications are inserting various business events into a relational table within an Aurora PostgreSQL database. The customer_business_events table captures data from several applications, including customer onboarding, subscriptions, payments, and complaints. It logs essential details such as customer reference ID, category of the event, and reason for any issues, alongside a timestamp for when the event occurred.

You can create this table using the following SQL code:

CREATE TABLE public.customer_business_events (
    application varchar(64) NULL,
    category varchar(64) NULL,
    customer_reference_id varchar(8) NULL,
    exception_reason varchar(128) NULL,
    event_timestamp timestamp NULL
);

Here are some sample insert statements for populating the customer_business_events table:

INSERT INTO customer_business_events VALUES ('customer-onboard-app', 'validate-optional-data', '1001', 'Missing secondary phone number', TO_TIMESTAMP('2021-07-14 14:00:00', 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO customer_business_events VALUES ('customer-complaint-app', 'log-customer-complaint', '1001', 'Customer logged a complaint', TO_TIMESTAMP('2021-07-14 14:14:06', 'YYYY-MM-DD HH24:MI:SS' ));

Our objective is to ensure that any insert, update, or delete action on this table triggers an event-processing function that notifies the Customer Relationship Operations team to take appropriate actions.

Prerequisites

This guide assumes that you have an Aurora PostgreSQL cluster (version 11.9 or later) established; further details can be found in the article on Creating an Amazon Aurora DB Cluster. Alternatively, you may also deploy Amazon RDS for PostgreSQL (versions 12.6+, 13.2+, or 14.1+).

VPC Endpoints

To enhance security, we create two VPC endpoints, one for Amazon SNS and another for the Lambda function, ensuring that our service access remains local to the VPC rather than routing through the public internet. For guidance on this process, refer to the instructions on creating a VPC endpoint service configuration for interface endpoints.

SNS Topic and Subscription

We will create the SNS topic my-rds-triggered-topic-1 along with a corresponding subscription for our Operations team’s email notifications.

Lambda IAM Execution Role

Next, we will configure a custom IAM execution role for Lambda, which we will name MyRdsTriggeredLambdaExecutionRole. This role will include three specific policies:

  1. An AWS managed policy for running the Lambda function in a VPC, arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole.
  2. A custom policy (MyRdsTriggeredTopicWriterPolicy) allowing the function to publish messages to our SNS topic.
  3. A basic permissions policy (MyBasicLambdaExecutionRolePolicy) to enable the Lambda function to log actions to Amazon CloudWatch.

For more resources on effective email networking templates, check out this helpful blog post at Career Contessa. Additionally, for information regarding employee classifications, you can refer to SHRM. Lastly, for onboarding tips, Amazon Business has excellent resources.

Related Topics:

Chanci Turner