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:
- Create an appointment into database
-
Create a record into
appointment_sms_communications
these values:- Status=processing
- appointment_id = the appointment id created in step 1
- phone = the customer's phone from the table
customers
. We will search by id using the appointment.customer_id - datetime_attempted: current timestamp usually many rdbms can auto-assighn this value.
Keep the appointment_sms_communications.id
Trigger sms sending
If an error occurs, mark the appointment_sms_communications.Status = failed and record both
exception
andexception_msg
. Otherwise, markappointment_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:
- If
sendSMSForAppointment
times out we can keep track whether we send an sms or not. - We can keep track the sms sending performance via looking the difference of
datetime_attempted
anddatetime_sent
. - 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
- 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 fromAppointment.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:
- We keep a table that saves the process status for each time we attempt to process it.
- 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.
- 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.
- Keeping track of exceptions is useful, especially when free telemetry options do not work (eg. sentry free plan)
Top comments (0)