Aug 1, 2023

Level 9

Sending sms stored in database table

Hello,

I have a database table named "sms" that we store sms we want to send in this table. this table receives data from multiple sources including my Laravel application, another application, mobile application, oracle application. this table could have like 2000 records with status 'pending' means ready to be sent.

What is the best way to do this job considering Cron Jobs with Command and Queued Jobs.

and how to prevent same record of sms from being processed twice and avoid any possible locks

LaryAI

2 years ago

Level 58

To send SMS stored in a database table, you can use Laravel's queue system along with a cron job and a command. Here's a step-by-step solution:

Create a new command in Laravel using the make:command Artisan command. Run the following command in your terminal:

php artisan make:command SendPendingSms

This will create a new command file named SendPendingSms.php in the app/Console/Commands directory.

Open the SendPendingSms.php file and update the handle method with the logic to send the pending SMS. You can retrieve the pending SMS records from the database table and send them using your preferred SMS service provider. Here's an example implementation:

use App\Models\Sms;
use Illuminate\Console\Command;

class SendPendingSms extends Command
{
    protected $signature = 'sms:send-pending';
    protected $description = 'Send pending SMS';

    public function handle()
    {
        $pendingSms = Sms::where('status', 'pending')->get();

        foreach ($pendingSms as $sms) {
            // Send the SMS using your preferred SMS service provider
            // Update the status of the SMS record to 'sent' or 'failed'
        }
    }
}

Register the command in the app/Console/Kernel.php file. Open the file and add the following line to the commands property:

protected $commands = [
    Commands\SendPendingSms::class,
];

Set up a cron job to run the command at regular intervals. Open your server's crontab file by running the following command:

crontab -e

Add the following line to the file to run the command every minute:

* * * * * php /path/to/your/laravel/project/artisan sms:send-pending >> /dev/null 2>&1

Make sure to replace /path/to/your/laravel/project with the actual path to your Laravel project.

To prevent the same SMS record from being processed twice, you can update the status of the SMS record to 'processing' before sending it. This can be done within the foreach loop in the SendPendingSms command. Once the SMS is sent, update the status to 'sent' or 'failed' accordingly.
To avoid any possible locks, make sure your SMS sending logic is efficient and doesn't cause any long-running processes. If you're using an external SMS service provider, ensure that your code can handle any potential timeouts or errors gracefully.

With this setup, the cron job will run the SendPendingSms command every minute, which will send the pending SMS stored in the database table.

vincent15000

2 years ago

Level 63

You can prevent sending twice the same SMS by changing the status of the line in the sms table.

If you want to queue the jobs, I don't know queues very well, but you can read the documentation.

https://laravel.com/docs/10.x/queues

1 like

JussiMannisto

2 years ago

Level 50

Scheduled commands and queued jobs can both work. Queued jobs may be easier to scale, because you can just add more queue workers to increase your throughput.

An easy way to prevent duplicate processing is to use a database transaction to get a batch of models in the pending state and mark them as processing. You can then handle the messages and mark them as sent at the end.

The important thing here is to run the pending selection and processing update within the same transaction. This way two threads can't accidentally pick the same models for processing.

2 likes

uniqueginun

2 years ago

Level 9

@JussiMannisto thank you for your replay: can you give demo code without details on how to achieve that

1 like

Snapey

2 years ago

Level 122

Do you need multiple senders to get the required throughput?

Are there rate limits by the sending service?

I would probably have a scheduled task that gets all messages with 'pending' state, creates a job for each and then sets the state for all the rows to 'queued'

Now your jobs can run according to the number of workers you have servicing the queue. The scheduled task might queue all SMS that appeared in the last minute. Make sure your table of SMS has a key that you can mark all messages as queued in one single sql update.

If you need to send messages with minimum latency, you can use the sub-minute processing in Laravel 10 or Spaties package for the same.

This way you don't need to worry about transactions and record locks, messages will be sent reliably with a built-in retry mechanism and failed jobs will go to the failed jobs table for investigation or resend.

2 likes

uniqueginun

2 years ago

Level 9

@Snapey thanks for the reply, if I understand you correctly I guess I would have to add another column "batch" in the database table and each bulk inserts will have the same batch id to use it later to mark all messages in single batch as queued?!

the scheduled command would look like

class CheckForNewSms extends Command
{
    public function handle(): int
    {
        SendSms::query()
            ->where('status', 'pending')
			->take(600) // the rate limit is 10 per second							
            ->orderBy('create_date')
            ->get()
            ->each(fn ($sms) => dispatch(new SendSmsJob($sms->markAsQueued())));

        return 0;
    }
}

$schedule->command('sms:send')->everyMinute();

class SendSmsJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected ?SendSms $sms = null;

    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct(SendSms $sms)
    {
        $this->sms = $sms;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        $sms = $this->sms;

		if(app(SmsContract::class)->send($sms->mobile, $sms->text)) {
			$sms->status = 'sent';
			$sms->save();
		}
	}
}

what you think?

1 like

Snapey

2 years ago

Level 122

no, i would use the unique ids on each sms record to update them as queued with a whereIn, using an array of keys, and doing this once all the messages are queued (to cut down on db transactions )

And I probably would not set the sent state. 'queued' could be the final state in that table

Failure of the job could go back and mark the sms as failed if you want

2 likes

JussiMannisto

2 years ago

Level 50

@Snapey I'd definitely mark them as sent because it's the real status. This way you can distinguish actually queued messages from the sent ones. And it touches updated_at.

2 likes

Snapey

2 years ago

Level 122

@JussiMannisto I suppose if the limit is just 10 messages per second through the API then there is plenty of bandwidth to update the table.

2 likes

JussiMannisto

2 years ago

Level 50

@Snapey Even if the rate was higher, I don't think it'll be a performance bottle neck as you're just updating a record by it's primary key. There's much more overhead in the things the framework has to do in this approach: create a job record for each model, retrieve the job & mark it as reserved, retrieve the associated model etc.

If I needed to really improve performance and reduce DB calls, I'd process a batch of models on each job instead of a a single model. I'd just implement my own retry pattern for each API call instead of relying on automated job retries.

1 like

uniqueginun

2 years ago

Level 9

@jussimannisto @snapey

when is the good time to update their status? when I fetch them by the command or when they being processed by the job?

     public function handle(): int
    {
        $smsList = SendSms::query()
            ->where('status', 'pending')
			->take(600) // the rate limit is 10 per second							
            ->orderBy('create_date')
            ->get();
     	
            dispatch(new SendSmsJob($smsList);

        return 0;
    }


class SendSmsJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected Collection $pendingSms;

    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct(Collection $pendingSms)
    {
        $this->pendingSms= $pendingSms;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
         SendSms::query()->whereIn('id',  $this->pendingSms->pluck('id')->toArray())->maskAsQueued()->each(
				fn($sms) => app(SmsContract::class)->send($sms->mobile, $sms->text)
         );
    }
}

now I know how to do it but the key things I can't figure out are:

what is the main role of the command? I am assuming reading the pending sms as they are and then sending them to the job
the job will update their status to queued and then send them and mark as sent
failed ones will updated back to pending

this is the flow I am imagining what you think?

1 like

Snapey

2 years ago

Best Answer

Level 122

mark them as soon as you pick them up. Imagine the worker is stopped, or delayed more than a minute. The way you have it they are not marked until the job is handled.

You need to prevent the next occurrence of the command queuing the same messages

1 like

Please or to participate in this conversation.