Query Unpaid Orders

In e‑commerce, food‑delivery, ticketing, and similar transaction systems, the need to identify and act on orders that remain unpaid is a frequent and critical requirement.
At first glance it looks like a simple status‑filter query, but the underlying challenges involve real‑time detection, high throughput, and strong consistency.
Below we dissect the core business concerns, outline the most common technical solutions, compare them, and distill best‑practice design guidelines.

Core Business Challenges

1. Order Timeout Closure
   Users typically have a limited window (e.g., 15–30 minutes) to complete payment. If the payment is not received within that window, the order must be automatically closed to free inventory, coupons, and other resources.

2. Payment‑Callback Compensation
   Payment gateways may lose callbacks due to network jitter or transient failures. Orders that linger in a “payment in‑progress” state need an explicit check against the payment provider to confirm the final outcome.

Both scenarios demand a solution that satisfies three non‑negotiable criteria:

Common Implementation Patterns

1. Database Polling

The most straightforward approach: a cron‑like job runs every minute (or configurable interval) and pulls orders that are still “pending” and older than the timeout threshold.

SELECT id, user_id, created_at
FROM orders
WHERE status = 'PENDING'
  AND created_at < NOW() - INTERVAL 30 MINUTE
ORDER BY id
LIMIT 200;

The job then attempts an idempotent update:

int rows = orderDao.closeIfUnpaid(orderId);
if (rows > 0) {
    inventoryService.release(orderId);
}

Why idempotent?
The WHERE status = 'PENDING' guard ensures that only the first updater succeeds; subsequent attempts affect zero rows, naturally preventing duplicate cancellations.

2. Delayed Messaging

When an order is created, a delayed message is sent to a queue with a delay equal to the payment window. When the message finally arrives, the consumer checks the order status:

// Producer
messageQueue.sendDelayed(orderId, delay = 30 * 60 * 1000); // 30 minutes

// Consumer
Order order = orderDao.findById(orderId);
if (order.status == PENDING) {
    orderDao.close(orderId);
    inventoryService.release(orderId);
}

This pattern delivers near‑real‑time closure and scales horizontally, but you must guard against message loss (e.g., broker crash) by running a low‑frequency reconciliation job.

3. Redis Key Expiry Listener

Store the order ID in a Redis key with a TTL equal to the payment window. Subscribe to the __keyevent@<db>__:expired channel to receive a notification when the key expires.

# Producer
redis.setex(f"order:{orderId}", 1800, "pending")  # 30 minutes

# Subscriber
def on_expire(message):
    order_id = message['data'].split(':')[1]
    order = orderDao.findById(order_id)
    if order.status == PENDING:
        orderDao.close(order_id)
        inventoryService.release(order_id)

Caveat: Redis key‑event notifications are not guaranteed to be delivered in every scenario (e.g., Redis restart). Use this only as a supplemental trigger.

4. Redis ZSet Time Wheel

Insert the order into a sorted set with the expiry timestamp as the score. A background job scans the set for members whose score is less than the current time.

ZADD orders:timeout 1633024800 order123

# Periodic scan
now = time.time()
expired = redis.zrangebyscore('orders:timeout', 0, now)
for order_id in expired:
    order = orderDao.findById(order_id)
    if order.status == PENDING:
        orderDao.close(order_id)
        inventoryService.release(order_id)
    redis.zrem('orders:timeout', order_id)

This approach is efficient when you already rely heavily on Redis and want to avoid an external message broker.

5. Lazy (On‑Demand) Processing

When a user queries an order or initiates payment, the service first checks if the order has timed out. If so, it closes the order before proceeding.

Order order = orderDao.findById(orderId);
if (order.status == PENDING && order.createdAt < now - timeout) {
    orderDao.close(orderId);
    inventoryService.release(orderId);
}

While this incurs no extra infrastructure, it cannot guarantee timely closure in the absence of user interaction.

Cross‑Pattern Design Principles

Practical Example: Hybrid Approach

A robust system often combines multiple patterns:

1. Primary Path – Delayed messaging for near‑real‑time closure.
2. Secondary Path – Database polling every 5 minutes to catch any missed messages.
3. Tertiary Path – Lazy check during user‑initiated actions to provide instant feedback.

# Configuration
payment_timeout_minutes: 30
polling_interval_seconds: 300
max_batch_size: 500

Workflow

1. Order Creation

   • Persist order with status PENDING.
   • Send a delayed message to the payment‑timeout queue.
   • Optionally set a Redis key with the same TTL for quick fallback.

2. Delayed Message Consumer

   • On message arrival, load the order.
   • If still PENDING, close it and release inventory.
   • Record the action in a “closed_by_timeout” flag.

3. Periodic Polling Job

   • Query orders older than the timeout that remain PENDING.
   • Batch‑update them idempotently.
   • Trigger inventory release for each.

4. User‑Facing API

   • On order query or payment attempt, check status.
   • If the order has just timed out, close it immediately before proceeding.

5. Reconciliation Service

   • Run nightly to cross‑check orders in PENDING or PAYING states against the payment provider’s API.
   • Resolve any discrepancies.

Choosing the Right Mix

Conclusion

“Querying unpaid orders” is more than a simple status check; it is a multi‑faceted problem that touches real‑time processing, scalability, and consistency.
By understanding the trade‑offs of each pattern and adhering to the core design principles—proper indexing, idempotent updates, batching, and reconciliation—you can build a resilient system that gracefully handles timeouts, compensates for lost callbacks, and scales with your order volume.

Remember: no single solution fits all. The best practice is to start with the simplest viable approach, monitor its performance, and layer additional mechanisms (delayed messages, polling, reconciliation) as the system grows.