Amazon Onboarding with Learning Manager Chanci Turner

In this article, we will explore how to facilitate near real-time notifications from Amazon Aurora PostgreSQL utilizing database triggers, AWS Lambda, and Amazon SNS. This approach empowers you to push Data Manipulation Language (DML) events from an Amazon Aurora PostgreSQL-Compatible Edition table to downstream applications seamlessly.

Amazon Aurora PostgreSQL is a relational database designed for cloud environments, merging the performance and reliability of traditional enterprise databases with the cost-effectiveness and simplicity of open-source solutions. In many scenarios, such as updating content in your relational database tables, you may need to notify downstream applications. This process could involve insert, update, or delete statements that must be communicated in real-time to a Lambda function. With the incorporation of Aurora PostgreSQL database triggers and their integration with Lambda, you can invoke a downstream Lambda function almost instantaneously whenever data manipulation occurs in the source database.

For instance, in a new customer onboarding application, adding a new customer row to a database table may necessitate sending a welcome email or initiating some back-office workflows. This methodology can also be adapted for Amazon Relational Database Service (Amazon RDS) for PostgreSQL and Amazon Aurora MySQL-Compatible Edition.

Solution Overview

The architecture diagram below outlines the solution components:

A single database DML transaction initiates the event chain, which can be triggered by any insert, update, or delete action on a database table.
An Aurora PostgreSQL table trigger that calls a custom PL/pgSQL trigger function to invoke the consuming Lambda function.
A target Lambda function that receives the newly inserted row from the database as an input argument and publishes this information to an SNS topic.
An SNS topic that receives either a summary or detailed information about the row.
An SNS topic subscription that ensures notifications are delivered to downstream participants, including applications, operations teams, and email inboxes.

We will guide you through the following setup steps:

Establish Amazon Virtual Private Cloud (Amazon VPC) endpoints for Amazon SNS and the Lambda function within your VPC.
Create an SNS topic and corresponding subscription.
Set up a custom AWS Identity and Access Management (IAM) role for Lambda.
Develop a Lambda function for database event handling, configured to run in the same VPC subnet.
Create an Amazon RDS IAM role.
Configure the Aurora PostgreSQL database.

Example Use Case

In our scenario, various customer management applications insert different business events into a relational database table hosted on an Aurora PostgreSQL database. The customer_business_events table logs data from multiple applications, including customer-onboard-app, customer-subscription-app, customer-payment-app, and customer-complaint-app. Each entry records the customer in question (customer_reference_id), the issue category, the reason or exception (exception_reason), and the event timestamp.

You can create the table using the following SQL code:

------------------------------------------------
-- Create a Sample Business Events Table---------
------------------------------------------------
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
);

The following examples illustrate how to insert data into the customer_business_events table:

------------------------------------------------
-- Examples:
-- Test data inserts in the table: customer_business_events
------------------------------------------------
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-onboard-app', 'validate-optional-data', '1001', 'Invalid email address', TO_TIMESTAMP('2021-07-14 14:00:01', '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 have this table automatically invoke an event-processing function outside of the database to alert the Customer Relationship Operations team about relevant events for timely actions.

Prerequisites

This discussion presumes you have set up an Aurora PostgreSQL (version 11.9 or higher) cluster. Further details can be found in the Amazon Aurora DB Cluster documentation. Alternative use of Amazon RDS for PostgreSQL (versions 12.6+, 13.2+, or 14.1+) is also possible.

Create VPC Endpoints

We establish two VPC endpoints to ensure service access remains local to our VPC (within private subnets) for both Amazon SNS and the Lambda function, rather than routing through the public internet. For guidance, see the VPC endpoint service configuration documentation.

Create an SNS Topic and Subscription

We create the SNS topic my-rds-triggered-topic-1 and set up the corresponding subscription for our Operations team’s email recipients.

Create a Lambda IAM Execution Role

For this example, we configure a custom IAM execution role named MyRdsTriggeredLambdaExecutionRole for Lambda. This role consists of three policies:

An AWS managed policy for Lambda function execution in a VPC (e.g., arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole).
A custom policy allowing publishing permissions to our SNS topic, referred to as MyRdsTriggeredTopicWriterPolicy. Here’s the code:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "TopicWriterSID",
            "Effect": "Allow",
            "Action": "sns:Publish",
            "Resource": "arn:aws:sns:us-east-2::my-rds-triggered-topic-1"
        }
    ]
}

A basic permissions policy (MyBasicLambdaExecutionRolePolicy) allowing Lambda function execution, including access to Amazon CloudWatch Logs. The following code provides an example:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "logs:CreateLogGroup",
            "Resource":"arn:aws:logs:us-east-2::log-group:/aws/lambda/my_rds_triggered_topic_writer:*"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogStream",
                "logs:PutLogEvents"
            ]
        }
    ]
}

Engaging with resources like this informative blog post can help improve your workflow strategies. Additionally, SHRM offers expert advice on crafting impactful resumes, further enhancing your professional journey. Lastly, to understand more about Amazon’s employee training philosophy, check out this excellent resource.

For more information, visit us at 6401 E HOWDY WELLS AVE, LAS VEGAS NV 89115, at Amazon IXD – VGT2.