Scheduled Shipment Notifications After the 7-Day Queue Delay Ceiling

Bottom line: a reminder due further out than your broker's max delay limit doesn't belong in the queue yet. Keep the due date in a table you own, then let a small cron scan enqueue each delayed message once it enters the delivery window. A queue message is transport with a deadline attached, not a calendar, and once you split those two jobs the whole class of "user reminders not arriving after seven days" incidents turns into an ordinary capacity question.

The system I'll use throughout is a B2B SaaS shipment tracker. One shipment update fans out to every subscriber watching that shipment — the shipper, the consignee's ops inbox, two internal watchers, sometimes a customer's own webhook receiver — and a subset of those subscribers asked to be reminded a few days before the delivery window opens. Fan-out is what makes the scheduling question sharp, because a single skipped tick doesn't cost you one notification, it costs you one per subscriber, and the on-call engineer finds out from a support ticket rather than from a dashboard.

The bug reports are identical every time. The reminder row is there. The notification isn't.

What happens to scheduled reminders past the queue's max delay limit?

Every broker caps how far ahead it will hold a message before making it visible, and the cap is a property of the transport rather than of your product. Amazon SQS allows a per-message delay of at most 900 seconds — fifteen minutes — and separately caps message retention at fourteen days, so a queue there cannot physically remember anything about next month. RabbitMQ has no native "publish this in N days" verb at all; the common construction is a per-message TTL on a holding queue whose dead-letter exchange routes expired messages to the queue your consumer actually reads, which means the delay you think you configured is really an expiry plus a routing hop. Other schedulers stretch to days or weeks. The number differs; the ceiling always exists.

What brokers do with an out-of-range delay differs too, and that's where the silent failures come from. Rejecting the publish is the friendly behavior, because your service sees an error at write time and you find the bug in staging. Clamping the value to the maximum is the dangerous one: the reminder fires immediately, the delivery looks successful in every log you have, and the subscriber gets a notification about a shipment that's still six days out. Chaining hops — publishing a delayed message whose only job is to publish the next delayed message until the due date arrives — is the workaround people reach for first, and it multiplies the number of independent chances to lose the reminder while spreading the schedule across a system with no query interface. You can't ask a queue "what reminders do we owe next Tuesday?" You can ask a table that.

So the invariant is boring and worth writing on the wall: the queue moves work that is ready now, and the database remembers what will be ready later. Retention rules make the same point from the other side. A message sitting in a queue is subject to a retention window measured in days, while a shipment reminder is a business record that has to survive redeploys, a broker migration, and an auditor asking why a customer wasn't told.

Which delivery guarantee are you actually buying?

Most brokers give you at-least-once delivery, and the deduplication features that exist are scoped to short windows measured in minutes, which is nowhere near the recovery window of a real incident. That's not a defect, it's the contract. Design against it.

Two service levels are in play here and conflating them is how teams end up debugging the wrong component. The scheduling SLO says a reminder becomes eligible within some bound of its due time — a minute is generous for most reminder products. The delivery SLO says an eligible reminder reaches the subscriber within some bound of becoming eligible. The database owns the first, the queue and workers own the second, and separating them tells you immediately whether a paged incident is a scan problem or a consumer problem.

Fan-out forces one more modeling decision. The unit of delivery is the pair of shipment update and subscriber, not the shipment update alone, because a partially delivered fan-out has to be resumable without re-notifying the subscribers who already got it. One row per pair, each carrying its own state and its own stable delivery key, gives the worker something to be idempotent against. Derive that key from data that doesn't change on retry — reminder id, subscriber id, due timestamp — and the second copy of a message becomes a cheap no-op rather than a duplicate email to a customer's ops inbox.

A due-date table and one bounded scan

The claim query is the load-bearing part. FOR UPDATE SKIP LOCKED lets several scanner replicas run the same statement concurrently, each skipping rows another transaction has already locked instead of queueing behind them, which is what keeps a horizontally scaled scanner from serializing itself.

Note the predicate below is due_at < now() + horizon, not due_at = now(). Cron ticks get skipped during a deploy, a node eviction, or a long GC pause, and a scheduler that doesn't backfill missed runs will leave you with rows whose due time is in the past forever. An open-ended lower bound plus a small forward horizon means a resumed scanner sweeps up everything it missed on the next tick.

package reminders

import (
    "context"
    "crypto/sha256"
    "database/sql"
    "encoding/hex"
    "fmt"
    "time"
)

// Claim and return in one statement. SKIP LOCKED lets several scanner replicas
// run this concurrently: each skips rows another transaction already holds
// instead of queueing behind them.
const claimDue = `
UPDATE shipment_reminder
   SET state = 'claimed', claimed_at = now()
 WHERE id IN (
       SELECT id
         FROM shipment_reminder
        WHERE state = 'pending'
          AND due_at < $1
        ORDER BY due_at
          FOR UPDATE SKIP LOCKED
        LIMIT $2)
RETURNING id, subscriber_id, shipment_id, due_at`

type Due struct {
    ID           string
    SubscriberID string
    ShipmentID   string
    DueAt        time.Time
}

// deliveryKey is stable across retries and across scanner replicas: same
// reminder, same subscriber, same due time, same key. The notification worker
// uses it to make a second copy of the message a no-op.
func deliveryKey(d Due) string {
    sum := sha256.Sum256([]byte(d.ID + "|" + d.SubscriberID + "|" + d.DueAt.UTC().Format(time.RFC3339Nano)))
    return hex.EncodeToString(sum[:])
}

type Publisher interface {
    Publish(ctx context.Context, key string, payload []byte) error
}

// Scan claims one bounded batch and hands it to the broker. horizon must be
// larger than one scan interval plus the worst tick jitter you tolerate;
// batch caps the work a single tick can create.
func Scan(ctx context.Context, db *sql.DB, pub Publisher, horizon time.Duration, batch int) (int, error) {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return 0, err
    }
    defer tx.Rollback()

    rows, err := tx.QueryContext(ctx, claimDue, time.Now().Add(horizon), batch)
    if err != nil {
        return 0, err
    }
    var due []Due
    for rows.Next() {
        var d Due
        if err := rows.Scan(&d.ID, &d.SubscriberID, &d.ShipmentID, &d.DueAt); err != nil {
            rows.Close()
            return 0, err
        }
        due = append(due, d)
    }
    rows.Close()
    if err := rows.Err(); err != nil {
        return 0, err
    }

    for _, d := range due {
        payload := []byte(fmt.Sprintf(`{"reminder":%q,"subscriber":%q,"shipment":%q}`,
            d.ID, d.SubscriberID, d.ShipmentID))
        if err := pub.Publish(ctx, deliveryKey(d), payload); err != nil {
            // Roll back the claim; the rows stay pending and the next tick retries.
            return 0, err
        }
    }
    return len(due), tx.Commit()
}

Publishing inside the claim transaction is a deliberate trade-off, and I'd rather name it than pretend it isn't there: a publish that succeeds followed by a commit that fails produces a duplicate on the next tick. That's survivable precisely because the delivery key makes duplicates cheap. If your notification side effect can't be made idempotent — a payment, a physical dispatch, anything with a real-world cost — then the honest answer is the transactional outbox instead, where the scan writes an outbox row in the same transaction and a separate relay publishes it, trading a second moving part for a much stronger story about what happened when the network ate the response.

Capacity planning falls out of the same two numbers. Suppose 40,000 shipment updates a day at an average of three subscribers each: 120,000 deliveries, roughly 1.4 per second averaged, call it 14 per second at a morning peak. A batch cap of 1,000 rows every 30 seconds gives about 33 claims per second of drain capacity, which is a bit over 2x peak — enough headroom that a scan doesn't become the bottleneck, and small enough that a single tick can't create a backlog the workers can't chew through. Run the catch-up arithmetic too, because that's the case nobody sizes: a scanner paused for 20 minutes accumulates about 1,700 rows, which clears in two ticks. A scanner paused for six hours accumulates 30,000, which needs 30 ticks and fifteen minutes of drain — and if your delivery SLO is tighter than that, you need either a bigger batch cap or an alert that fires long before six hours.

Buy versus build at the delay ceiling

The decision here is mostly about which failure you'd rather be paged for, and secondarily about how much of the schedule is business data you can't afford to have living in someone else's opaque store.

Lock-in shows up in a specific place, and it isn't the API surface. Migrating the publish call is an afternoon. Migrating the schedule itself — millions of pending timers held inside a managed product with no bulk export — is a project, so the more of your future the scheduler holds, the more the horizon should live in a table you can dump. Cost follows the same shape: per-timer pricing looks trivial at 120,000 deliveries a day and stops looking trivial when a product decision multiplies subscribers per shipment by four. Self-hosting isn't free either; it's just billed in on-call hours instead of invoices.

Where this design stops earning its keep

The scan-and-claim pattern is a poor fit for sub-second timing. A 30-second cadence means up to 30 seconds of scheduling error plus tick jitter, which is fine for a shipment reminder and not fine for a trading deadline; stick with a broker-native timer or an in-process scheduler when the tolerance is smaller than your scan interval. It also doesn't suit a single table at extreme volume — once pending timers run into the tens of millions, an ordered scan over one table becomes the hot spot, and a bucket-per-minute layout or a purpose-built timer service is the better shape. And it can't give you exactly-once notification, because no transport can; if a duplicate email is unacceptable rather than merely annoying, the suppression has to live at the subscriber boundary with a durable key, not in the queue's configuration.

Reminders that branch — wait for an approval, escalate after a day, compensate when a shipment is cancelled — have outgrown a due-date table and belong in a workflow engine, where those joins are first-class rather than columns you keep adding.

Two operational habits make the difference between a pattern that works on the whiteboard and one that survives contact with a real fleet. Instrument the age of the oldest pending row, not just the error rate: scheduling lag rises minutes before anything starts throwing errors, and it's the only signal that catches a scanner that's alive but starving. Then rehearse the catch-up path — pause the scanner in staging for an hour, resume it, and watch whether the batch cap, the worker concurrency and the downstream provider's rate limit actually let the backlog drain inside your delivery SLO.

How often ticks get skipped in practice, I'm not going to guess at; it depends on your scheduler, your deploy frequency and how aggressive your node autoscaler is. Measure it in your own environment. The design above just makes sure a skipped tick costs you a few minutes of lag instead of a shipment notification that quietly never arrives.

References

• Amazon SQS delay queues
• Amazon SQS message retention and quotas
• RabbitMQ dead letter exchanges
• PostgreSQL SELECT documentation (FOR UPDATE SKIP LOCKED)
• Transactional outbox pattern