How to avoid spamming users from your applications

Does your application send out emails? Lots of emails?

Did you ever get that feeling like someone punched you in the stomach when you realize that you mistakenly sent out hundreds or thousands of emails to your users when you didn't mean to?

I have. It's a terrible feeling. And these days, in the age of GDPR, there can be real consequences for invading the privacy of your users. This post explores how to make sure that, at least when you are developing and testing your code, you do not inadvertently spam your users.

The Oracle Dev Gym sends out lots of different kinds of emails to those players who have opted-in for them, such as:

• Results of the quiz you just completed
• Confirmation of sign-up in a class
• Reminder to take our weekly tournament quizzes
• Hourly reports to site admins with any new errors in our log
• Weekly activity summaries to quizmasters

The Dev Gym is an Oracle Application Express app, so we are able to happily and easily take advantage of the APEX_MAIL package, and its SEND procedure. Here's a diagram of what our email workflow could  look like:

In other words, wherever and whenever I need to send an email, whether it is from the quiz manager package or the class manager package or the site admin utilities package, I simply call apex_mail.send directly.

Like I say, I could take this approach. But that would be a really bad idea. Why?

With multiple accesses to the "core" send email procedure, it is difficult to put any kind of controls in place regarding email delivery, and also more challenging to debug and enhance email-related code.

Consider the situation at the Dev Gym. We currently have 72,000 users registered at the Dev Gym, and of those 30,000 have opted-in for emails. We have development, test/stage and production environments for the Dev Gym. Here's the rule we need to follow regarding emails:

When running in development or test, our users should never get an email. Specifically, unless the email "send to" is one of our developers, redirect all emails to the admin email address.

In other words, we still need to see the emails to verify the format and other behaviors, but those emails should never "escape" from the development team.

If APEX_MAIL.SEND is called from dozens of locations in my code, then I need to go to each of those locations and apply my rule. Therein lies madness and the inevitable mistake that results in emails spamming users.

So instead, we follow this rule when writing email-related code:

Never call APEX_MAIL.SEND directly. Instead call qdb_communication_mgr.send_email. 

Our own send_email procedure, in turn, calls APEX_MAIL.SEND. With this approach, the flow of email requests look like this:

By following this rule, we ensure that APEX_MAIL.SEND is called in just one place in our entire code base: inside our own wrapper, the qdb_communication_mgr.send_email procedure (the "qdb" prefix translates to "quiz database").

Here's a greatly simplified version of this procedure :

PROCEDURE send_mail (
   to_user_id_in      IN INTEGER,
   subject_in         IN VARCHAR2,
   html_in            IN CLOB,
   push_to_queue_in   IN BOOLEAN DEFAULT TRUE)
IS
   l_email_id   INTEGER;
   l_text       CLOB;

   /* Just a placeholder for this demonstration */ 
   FUNCTION is_developer (user_id_in IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN TRUE;
   END;

   FUNCTION user_email_address (user_id_in IN INTEGER)
      RETURN VARCHAR2
   IS
      l_email_address   qdb_users.email_address%TYPE;
   BEGIN
      SELECT email_address
        INTO l_email_address
        FROM qdb_users
       WHERE user_id = user_id_in;

      RETURN l_email_address;
   END;

   FUNCTION send_to_email_address (user_id_in IN INTEGER)
      RETURN VARCHAR2
   IS
      c_email_address   qdb_users.email_address%TYPE
                           := user_email_address (user_id_in);
   BEGIN
      RETURN CASE
                WHEN qdb_utilities.in_production THEN c_email_address
                WHEN is_developer (user_id_in) THEN c_email_address
                ELSE qdb_config.admin_email_address ()
             END;
   END;
BEGIN
   l_email_id :=
      apex_mail.send (p_to          => send_to_email_address (to_user_id_in),
                      p_from        => 'noreply@oracle.com',
                      p_subj        => subject_in,
                      p_body        => l_text,
                      p_body_html   => html_in,
                      p_cc          => NULL,
                      p_bcc         => NULL);

   IF push_to_queue_in
   THEN
      apex_mail.push_queue;
   END IF;
END send_mail;

Here are the highlights:

• I create a nested function to return the email address to which the email will be sent. Inside that function, I have all the logic to implement my requirement.
• The qdb_utilities package contains a function that returns TRUE if I am currently running this code in production.
• Since the only call to apex_mail.send occurs within my send_mail procedure, I am now 100% protected from accidentally sending out an email to users when it was not intended.
• I don't always push the email out of the queue. For example, when I doing a batch email, I want to wait till everything is put on the queue, and then push it. If I am notifying a user of a specific event or accomplishment, I might want to push immediately.

So: nothing terribly fancy, no rocket science. Just another demonstration of how encapsulating low-level functionality to control access to that functionality gives you flexibility and control that is otherwise very difficult to achieve.

This particular encapsulation is just another example of the fundamental rule of the SmartDB paradigm: create a "hard shell"  PL/SQL API around your data structures (and, in this case, infrastructure or plumbing). More info on SmartDB

Once you've got your encapsulation layer in place, it becomes really easy to add more functionality to your email management features. For example, we recently added our own email queue, so we can re-send an email if there was an error, and also verify that emails were produced and sent as expected.

Since we knew for a fact that APEX_MAIL.SEND is only called in one place, we could easily ensure that all email attempts were place in the queue as follows:

PROCEDURE send_mail (
   to_user_id_in      IN INTEGER,
   subject_in         IN VARCHAR2,
   html_in            IN CLOB,
   push_to_queue_in   IN BOOLEAN DEFAULT TRUE)
IS
...
BEGIN
   write_to_queue (
     email_sent     => 'N',
     to_address     => send_to_email_address (to_user_id_in),
     from_address   => 'noreply@oracle.com',
     subject        => subject_in,
     body           => l_text,
     body_html      => html_in,
     cc             => NULL,
     bcc            => NULL,
     email_id_out   => l_email_queue_id);

   l_email_id :=
      apex_mail.send (p_to          => send_to_email_address (to_user_id_in),
                      p_from        => 'noreply@oracle.com',
                      p_subj        => subject_in,
                      p_body        => l_text,
                      p_body_html   => html_in,
                      p_cc          => NULL,
                      p_bcc         => NULL);

   IF push_to_queue_in
   THEN
      apex_mail.push_queue;
   END IF;

   mark_email_sent (l_email_queue_id);
END send_mail;

If you have not already protected your users and yourself from the trauma of an accidental spamming,  I hope that you can learn from and use the approach we've taken.

Have you taken care of this problem in another way? Do you have suggestions for improving upon what we've done? Please let me know in the comments section.