Introduction

In this post, I will describe what a Webhook is and show examples of how Oracle REST Data Services (ORDS) can be used to handle Webhook events. I will dive deep into an example of a SendGrid Webhook, summarize three other examples, and finish up with a general discussion on Webhook best practices.

What is a Webhook?

A Webhook is a way for an application to provide real-time information to other applications. It allows one application to send a notification to another application when a specific event occurs.

When the event occurs, the first application sends an HTTP request (often a POST request) to a specific URL configured for the Webhook (also called a "callback" or "endpoint") in the second application. The second application then receives and processes the request and can take appropriate action based on the information it receives.

Webhooks are often used for integrating two different systems or providing real-time notifications about events that happen within an application.

How can ORDS help? You can build REST services in ORDS that act as the "callback" to receive Webhook events from other systems.

🧰 Webhooks are another example of the versatility of ORDS and the RAD Stack!

SendGrid Example Webhook

Twilio SendGrid is a cloud-based email marketing tool that assists marketers and developers with campaign management and audience engagement. One feature of SendGrid is capturing events like email delivered, email bounced, email opened, email link clicked, etc.

Overview

Let's take an example of an APEX Application that handles employee satisfaction surveys. Every year, we need to send an email out to all employees asking them to complete that year's survey.

1. An APEX page calls a PL/SQL procedure which calls the SendGrid REST API to send emails to all employees. The email contains a link to an APEX App where employees can complete the employee survey.

2. SendGrid delivers emails to each employee's email inbox.

3. When an email is successfully delivered, SendGrid calls the ORDS Webhook with a JSON payload, including details of the delivered event.

4. An employee opens the email and clicks the link for the Employee Survey App. SendGrid sends an open and a click event to the ORDS Webhook.

5. An ORDS Handler receives events from SendGrid, parses the JSON, and creates records in a table.

6. As events come in from the Webhook, we can show a dashboard in APEX showing which employees have received the email and which ones have yet to open it. This allows us to tailor reminder emails based on where the employee got to in the process.

🧰 As APEX developers, we should never forget that we have the versatility and power of ORDS at our disposal!

ORDS Setup

I am assuming we already have an ORDS Module called sendgrid and an ORDS Template called email_update .

We need to create an ORDS POST Handler to receive events from SendGrid. We will also need to secure this handler with OAuth2 Client Credentials.

ORDS POST Handler

We will keep the POST Handler simple; it just passes on the JSON payload to a PL/SQL procedure called sendgrid_webhook.

DECLARE
  l_body_text CLOB := :BODY_TEXT;
BEGIN
  email_utl_pk.sendgrid_webhook 
   (p_json   => l_body_text,
    x_status => :status);
EXCEPTION WHEN OTHERS THEN
  :status := 400;
END;

The procedure called by the ORDS Handler looks like this:

Notes

• SendGrid sends the date and time of the event in Unix Epoch Time, so we have to convert it to an Oracle TIMESTAMP WITH TIMEZONE in the UTC timezone.

• SendGrid can send multiple events simultaneously; we can insert them all into a table using JSON_TABLE.

• The 6th line in the SELECT statement (sg_orig_msg_id) extracts the message ID that SendGrid gave us when we originally sent the email. This allows us to tie the original email to the event received in the Webhook.

ORDS OAuth2 Client

We need an ORDS OAuth2 client to get credentials to provide SendGrid when they call the ORDS service.

BEGIN  
  oauth.create_client
   (p_name            => 'SENDGRID_WEBHOOK_CLIENT',
    p_description     => 'Used by SendGrid for event callbacks',
    p_grant_type      => 'client_credentials',
    p_support_email   => 'jon@example.com',
    p_privilege_names => NULL);
  COMMIT;
END;

We can get the Client Id and Client Secret by running the following SQL:

SELECT client_id, client_secret 
FROM   user_ords_clients
WHERE  name = 'SENDGRID_WEBHOOK_CLIENT'

ORDS Role and Privilege

We need an ORDS Role and Privilege to secure the ORDS module, and then we need to grant the new role to the OAuth2 Client we created above.

DECLARE
  l_roles       sys.owa.vc_arr;
  l_modules     sys.owa.vc_arr;
  l_patterns    sys.owa.vc_arr;
BEGIN
  -- Create a new Role.
  ords.create_role(p_role_name => 'sendgrid.role');

  -- Define a Privelege and associate it with the module and role.
  l_roles(1)   := 'sendgrid.role';
  l_modules(1) := 'sendgrid';
  ords.define_privilege
   (p_privilege_name => c_ords_privilege_name,
    p_roles          => l_roles,
    p_patterns       => l_patterns,
    p_modules        => l_modules,
    p_label          => 'Privilege for the module sendgrid',
    p_description    => 'Protects the sendgrid module',
    p_comments       => null );  

  -- Grant the role 'sendgrid.role' to the OAuth Client called 'SENDGRID_WEBHOOK_CLIENT'
  oauth.grant_client_role('SENDGRID_WEBHOOK_CLIENT', 'sendgrid.role');
  COMMIT;
END;

SendGrid Setup

Now that we have a secured ORDS REST Service setup, we can set up the Event Webhook in SendGrid. This is what the setup looks like:

• We have selected OAuth2 as the Authorization Method. The Client ID and Client Secret come from the ORDS OAuth2 Client created above.

• The Token URL is the Token URL for the ORDS instance.

• The HTTP Post URL is the URL of the ORDS POST Handler created above.

After these setups are complete, any email events in SendGrid should get posted to our ORDS REST Service.

What if the Webhook Fails

There are situations where our ORDS service could fail. Maybe ORDS is down, or we have a bug in our ORDS Handler. When SendGrid sends events, it expects a 2xx response; in our case, we are returning a 200 response for success and a 400 response if there is an unexpected exception.

If SendGrid does not get a 2xx response, it will retry the POST request to the ORDS REST Service until it receives a 2xx response or the maximum time has expired. Events are retried at increasing intervals for up to 24 hours after the event occurs. This time limit is a rolling 24 hours, which means new failing events will receive their own 24-hour retry period. This makes it very important that your ORDS server is never unavailable for more than 24 hours.

Other Examples

This section includes examples of other Webhooks that I have worked with. Hopefully, these give you ideas of where to use ORDS to handle your Webhook needs.

Jira Service Desk

Jira Service Desk has a comprehensive set of Webhooks that can call REST Services with events that occur during the lifecycle of a Service Desk ticket. You can build an ORDS REST Service to receive these events and perform actions in your database when they occur.

Microsoft Teams

Microsoft Teams has Webhooks, which allow a user to post a message to a Teams Channel, have Teams call a REST service to perform an action, and return a response to the Teams user. You can create an ORDS REST Service to perform this action and run any code you like. This is an excellent way of implementing a simple chatbot. You can read more about this in my blog post: Use MS Teams to Chat with the World via Oracle REST Data Services

Oracle OCI Events

The Oracle OCI Events system enables you to create automation based on the state changes of resources throughout your OCI tenancy. For example, you can set up an Event to call an ORDS REST API whenever a file is uploaded to OCI Object Store. In my blog post, you can read about this example Webhook: Event-Driven Integration with OCI Events, ORDS, & APEX.

Discussion

Now that we have reviewed several examples let's talk about what you need to look out for when creating ORDS REST Services to handle Webhook events.

Webhook Errors

As discussed in the SendGrid section, you need to handle the fact that your ORDS REST Service may fail (or ORDS could be down). Each provider has different rules for retrying events, so you need to be aware of these and ensure that your ORDS SLA fits in with the re-try schedule from the provider.

In the SendGrid example, SendGrid keeps trying to send the event for 24 hours. If you are not confident any issues with your ORDS service will not be cleared up within 24 hours, then Webhooks may not be for you.

Security

Webhooks are a convenient way to receive real-time updates from other systems. This does not mean, however, that you should create unsecured REST APIs for consumption by Webhooks. An unsecured REST service means anyone with the URL can call it, which is not good.

In the SendGrid example, SendGrid allowed us to use OAuth2 Client Credentials to make a secure call to our REST Service. Not only does this secure our REST service, but it also puts us in control. At any time, we can delete the ORDS OAuth2 credentials, and SendGrid will no longer be able to call the REST Service.

OAuth2 is not the only approach. Other providers may send a token you can verify in your ORDS service. MS Teams sends an HMAC encrypted token in the Authorization HTTP header, which you can validate in the ORDS service.

Ensure your ORDS service is secured, or consider not using the Webhook at all.

Scaling

Webhooks can generate a high volume of requests, so it's essential to design your ORDS REST Services with performance in mind. In extreme circumstances, this may include using a queueing system to handle the requests and or rate limiting to prevent overloading the system.

You can read more about building performant ORDS REST Services in my post: Building Performant REST Services with Oracle REST Data Services.

Dependencies

Webhooks rely on external systems to function properly, so it's essential to have a plan in place for handling situations where those systems are down or unavailable.

Conclusion

Webhooks are almost always faster than polling and require less work on your end. They offer a convenient method for receiving real-time updates from other systems. Best of all, you can build REST Services to receive Webhook events from hundreds of systems using ORDS, which you already have!

🔗 Read More

• #️⃣ More ORDS Posts