How I can monitor that a scripts that performs a bulk process actually did its job? (with example)

In my work we usually we use mass mailing and sms sending scripts that need to be sent realtime. Any missing sms may cause annoyance, therefore monitoring of communications at least on our site is crucial.

Frequently what we have is a pool of phones. For better understanding, let us make an example:

Example

We have an appointment system that sends an sms to the customer's for their appointments:

Tables

Let suppose we have this table:

Customers
id: Serial
name: String
phone: String

And we need to send an sms for example for notifying for an appointment:

Appointment:
id: Serial
phone_callback: String
name: string
date: Datetime without timezone
customer_id: foreign key customers.id
datetime_created: Timestamp that record has been created

And we need once a user inserts an appointment to send an SMS, but we need to know whether the sms has been sent or not. We may attempt to sent multiple SMS.

For that, we have another table:

appointment_sms_communications:
id: serial
datetime_attempted: datetime without timezone
datetime_sent: datetime without timezone NULLABLE
appointment_id: foreign key appointment
phone: phone that will receive the sms
exception: Any exception class NULLABLE
exception_msg: Any exception message NULLABLE
Status: enum [Sent,failed,processing]

What we keep in the table above is any attempt to communicate via SMS. With status we keep whether the communication was successful or not or whether is in progress of communicating.

Algorithm

What algorithm we will follow is:

1. Create an appointment into database

2. Create a record into appointment_sms_communications these values:

   1. Status=processing
   2. appointment_id = the appointment id created in step 1
   3. phone = the customer's phone from the table customers. We will search by id using the appointment.customer_id
   4. datetime_attempted: current timestamp usually many rdbms can auto-assighn this value.

3. Keep the appointment_sms_communications.id

4. Trigger sms sending

5. If an error occurs, mark the appointment_sms_communications.Status = failed and record both exception and exception_msg. Otherwise, mark appointment_sms_communications.Status = sent. Set datetime_sent with the current timestamp

What we do is to record when we attempted the sms sending and whether the sms has been successfully has been sent or not. So we can keep track when we send a sms and whether we successfully contacted the sms gateway or not.

Code

In our example, we will use Laravel to show a demo implementation of the algorithm.
In our example, we assume that App\Services\SMSService class sends the SMS, also for sms sending we will use a Queue dispatch with a callback function.

Furthermore, each table will have the following Eloquent ORM models:

• Customers : App\Model\Customers
• Appointment: App\Model\Appointment
• appointment_sms_communications: App\Model\AppointmentSMSCommunication

Also, for simplicity, we will use a controller and not any fancy service.

namespace App\Controllers;
use App\Model\Customers;
use App\Model\Appointment;
use App\Model\AppointmentSMSCommunication;
use App\Services\SMSService;
// Any other laravel use for controllers

class AppointmentController extends Controller
{

   public function make_appointment(Request $request)
   {
      // Input check ommuited for simplicity
      $appointment = new Appointment();
      $appointment.customer_id = $request.get('customer_id');
      $appointment.customer_id = $request.get('datetime');
      $appointment.name = $request.get('name');
      $appointment.phone_callback = $request.get('phone_callback');
      $appointment.created = Carbon::now()->format("Y-m-d H:i:s');

       $appointment.save();

       dispatch(function () use ($appointment) {
           $smsAttempt = new SMSAttempt();
           // $appointment->customer() returns ther Customer that has $appointment.appointment_id
           $smsAttempt.phone = $appointment->customer()->phone;
           $smsAttempt.status = 'pending';
           $smsAttempt.datetime_attempted = Carbon::now()->format('Y-m-d H:i:s');
            $smsAttempt.appointment_id = $appointment.id;
            $smsAttempt->save();
            try {
             SMSService::sendSMSForAppointment($appointment);
             $smsAttempt.status = 'sent';
            } catch(Exception $e) {
               $smsAttempt.status = 'failed';
               $smsAttempt.exception = get_class($e);
               $smsAttempt.exception_msg = $e->getMessage();
            } finally {
               $smsAttempt.datetime_attempted = Carbon::now()->format("Y-m-d H:i:s");
                $smsAttempt->save();
            }
       });
     }
}

Reasons why we do this

As you further notice, there is a double save, one before sending the sms and one after sending it. The reason why we do this is for various reasons:

1. If sendSMSForAppointment times out we can keep track whether we send an sms or not.
2. We can keep track the sms sending performance via looking the difference of datetime_attempted and datetime_sent.
3. If an sms times out or has never been sent, we can re-try it via a watchdog process that runs via a cron or as a infinite loop. For example, if X miliseconds has ellapsed from datetime_attempted
4. Also, no record in appointment_sms_communications for an appointment means that no sms has been sent. Therefore, we can also implement a watchdog where looks if X time has elapsed from Appointment.created and no sms has been sent.

As a result, we can ensure that a sms has been sent. Also, we can easily monitor with a few select queries when a sms is send or failed to be sent due to an exception.

Generalization - Conclution

The algorithm above is used and can be adapted to monitor critical bulk processes of table records or critical jobs that run in the background, for example automated sms communications.

In our case, instead of an SMS it could be a function that bills some orders, and we need to ensure that an order has been billed correctly.

The most important part to keep from above is:

1. We keep a table that saves the process status for each time we attempt to process it.
2. The table that records this attempt to process a database record eg ford billing or communication must have a status field indicating the status of the processing.
3. We need to initially create a record of processing JUST BEFORE doing the process, and we need to update it after the process is done.
4. Keeping track of exceptions is useful, especially when free telemetry options do not work (eg. sentry free plan)