Best approach for handling payment timeouts in an online store

Ecom
1

I’m building an online shop and need advice on managing payment timeouts. Here’s my current setup:

CREATE TABLE transactions (
  transaction_id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  amount DECIMAL(6,2),
  payment_method ENUM('cash', 'credit_card'),
  transaction_status ENUM('awaiting', 'confirmed', 'failed'),
  created_at TIMESTAMP
);

When a customer places an order, the status is set to ‘awaiting’. They’re then sent to the payment gateway. If the payment goes through, the status changes to ‘confirmed’. If something goes wrong, it becomes ‘failed’.

I want to give customers 30 minutes to complete their payment. After that, the order should be canceled and the item made available again.

What’s the best way to handle this? Should I use a script that runs every few minutes to check for expired transactions? Or is there a better method? I’m not sure about the most efficient approach here. Any suggestions would be really helpful!

2

Hey there Ethan_Cosmos! Your question got me thinking… :thinking: Have you considered using a combination of database indexing and a background job for this? Here’s what I’m wondering:

Could you add an index on the ‘created_at’ column and ‘transaction_status’? That way, you could quickly query for awaiting transactions older than 30 mins.

Then maybe set up a background job (like with Sidekiq if you’re using Ruby) to run every few minutes? It could grab those expired transactions and update them in batches.

What do you think about that approach? Would it work with your current setup?

Also, I’m curious - how many transactions do you expect to handle per day? That might influence the best solution here.

Let me know what you think! Always fun to brainstorm these kinds of challenges. :blush:

3

For handling payment timeouts efficiently, I’d recommend implementing a message queue system like RabbitMQ or Apache Kafka. This approach allows you to decouple the timeout logic from your main application.

When an order is placed, publish a message to the queue with a delay of 30 minutes. If the payment is confirmed before the delay expires, remove the message from the queue. If not, when the message is consumed after 30 minutes, update the transaction status to ‘failed’ and release the inventory.

This method is scalable, reduces database load, and doesn’t require constant polling. It’s particularly effective for high-traffic scenarios and provides better fault tolerance compared to cron jobs or database triggers.

4

hey ethan, u could try using a task scheduler like cron to run a script regularly. it’d check for transactions older than 30 mins and mark em as failed. Another option is using database triggers to automatically update status after the timeout. both work, pick wats easiest for ur setup!