Tenant-Local Cutoffs for Daily SaaS Report Emails

Short answer: for a daily report email, use a thin cron-triggered webhook to create one durable report run, then let a queue worker do the slow work. Treat delivery as at-least-once and make the report key idempotent. The important design choice for EU and US tenants is the tenant's local reporting date, not the clock time of the scheduler.

This is a small system, but it has a large blast radius. A missed report becomes a support ticket; a duplicate report looks like bad data. I've been paged for both, and the alert usually points at the wrong layer first.

What should a SaaS cron webhook guarantee for EU and US report emails?

It should guarantee that an eligible time window creates one logical run. It should not promise that an email arrives at an exact second, or that a scheduler retry cannot happen. Those are different promises.

Persist a key such as tenant_id + report_date + report_type before doing any email work. Compute report_date from the tenant's declared time zone. A UTC timestamp is useful for ordering and audit logs, but it is not a substitute for the business date: a midnight boundary in Berlin and one in New York are different events.

The webhook should authenticate its caller, validate the schedule event, insert-or-find that key, and enqueue a compact reference. The worker can then load the report inputs, render the attachment or body, call the mail provider, and record the outcome. If the same queue message is delivered again, the worker finds the existing run and follows its recorded state instead of sending a second email.

Keep the trigger boring. That is a feature.

The failure modes and implementation boundary

The first failure is a missed trigger. A schedule can be paused, deployed incorrectly, or delayed by the platform. A successful trigger invocation proves only that the trigger ran; it does not prove that the webhook accepted a run or that a mail provider accepted a message. Keep those as separate timestamps and metrics.

The second failure is redelivery. Queue visibility is a lease, not a permanent claim. If a worker takes longer than the visibility timeout, the message can become visible again while the first worker is still running. The durable report-run key and a provider-side idempotency mechanism, where available, must protect the side effect. A FIFO deduplication window is not a daily business identity.

The third failure is a partial result. The report may be stored successfully while email submission times out, or email submission may succeed before the worker loses its connection. Do not infer “not sent” from a client timeout. Record an ambiguous state and reconcile it using the mail provider's delivery identifier or an operator-approved replay policy.

That last distinction matters during incidents. A retry is not automatically a new send.

Here is the incident path I want in the runbook. First, find the tenant-local report key and its state in the ledger. Next, compare the worker attempt, queue receipt, and mail-provider response timestamps; a missing response is evidence of uncertainty, not evidence of failure. Then check whether the provider returned a delivery identifier, whether the worker recorded it, and whether the provider's delivery status can be queried. If the answer is still ambiguous, hold the key for operator review instead of issuing a blind resend. If the ledger says the run was never created, replay the trigger through the normal insertion path. If the run exists but has no queue record, enqueue the reference rather than recreating the run. If the run has a confirmed delivery identifier, close the incident without sending again. This sequence is slower than a one-line retry, but it preserves the distinction between recovering work and repeating a side effect.

Write it down.

A small implementation keeps the state in the application. The scheduler does not need to know report contents. Its job is to wake the application. The application owns the business key, queue payload, and audit trail.

package main

import (
    "context"
    "crypto/sha256"
    "encoding/hex"
    "fmt"
    "net/http"
    "time"
)

type ReportRun struct {
    TenantID   string
    ReportDate string
    ReportType string
}

type RunStore interface {
    CreateOrGet(ctx context.Context, run ReportRun, key string) (created bool, err error)
}

type Queue interface {
    Enqueue(ctx context.Context, payload []byte) error
}

func stableKey(run ReportRun) string {
    h := sha256.Sum256([]byte(run.TenantID + "|" + run.ReportDate + "|" + run.ReportType))
    return hex.EncodeToString(h[:])
}

func triggerHandler(store RunStore, queue Queue, run ReportRun) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        if r.Method != http.MethodPost {
            http.Error(w, "method not allowed", http.StatusMethodNotAllowed)
            return
        }
        if r.Header.Get("Authorization") == "" {
            http.Error(w, "unauthorized", http.StatusUnauthorized)
            return
        }

        key := stableKey(run)
        created, err := store.CreateOrGet(r.Context(), run, key)
        if err != nil {
            http.Error(w, "run could not be recorded", http.StatusInternalServerError)
            return
        }
        if created {
            payload := []byte(fmt.Sprintf(`{"run_key":%q}`, key))
            if err := queue.Enqueue(r.Context(), payload); err != nil {
                http.Error(w, "run could not be queued", http.StatusInternalServerError)
                return
            }
        }
        w.WriteHeader(http.StatusAccepted)
    })
}

func main() {
    _ = time.UTC
    _ = triggerHandler
}

The example leaves the tenant-date calculation outside the HTTP handler on purpose. It needs tenant configuration, a daylight-saving policy, and a business rule for a missing local day. Put that logic in a tested application service, then pass the resulting ReportRun into this boundary. Do not derive a tenant's date from the machine's local clock. Your mileage may vary on the right daylight-saving policy; write it down before an operator has to make the choice during a missed run.

The queue payload contains a reference, not a report. That keeps retries cheap and lets the worker use the current report query and retention policy. The database uniqueness constraint behind CreateOrGet is part of the design, not an implementation detail; an in-memory mutex disappears when the process restarts.

How do you verify and roll back the delivery path?

Test the complete chain with a non-customer tenant and a fixed report date. Submit the same trigger twice, then inspect the run ledger, queue claims, mail-provider response, and final delivery record. The expected result is one logical run and one send decision. Also test a worker timeout followed by redelivery, and test the case where the mail request result is ambiguous.

For production, alert on missing logical runs after an agreed grace period rather than on scheduler latency alone. Break the dashboard into four signals: trigger received, run recorded, work completed, and email accepted. Include tenant time zone and report date in structured logs. Never put sensitive report contents in logs or queue messages.

Rollback is deliberately plain: pause the schedule, stop creating new runs, decide whether queued work should drain, and keep the run ledger intact. If a code release changed report calculation, replay only keys whose state and business policy allow it. A catch-up job should go through the same insert-or-find path; an emergency script that sends directly is how duplicate delivery gets manufactured.

When is a cron webhook the wrong boundary?

This pattern is not suitable when a report is one step in a long-lived workflow with explicit dependencies, branches, joins, human approval, or compensation. In that case, an orchestration system may justify its extra control plane because it owns more of the state transitions.

It is also not suitable when the endpoint cannot be exposed through an authenticated public HTTPS boundary, or when the team needs a strong exact-time guarantee that the scheduler cannot provide. Choose an existing internal scheduler or workflow platform when its operational conventions already cover those requirements. The cheapest bill is not the same thing as the lowest incident risk.

The catch is operational ownership: the small pattern leaves the run ledger, replay policy, and reconciliation logic with your team. Stick with the workflow platform when that ownership is already part of the team's operating model, or when the report has crossed from a job into a business process.

For a single daily report, though, adding a workflow engine before the failure modes demand one creates more state to operate. Start with the durable run key, queue semantics, observability, and replay policy. Revisit the boundary when the job gains real coordination requirements.

References

• AWS SQS visibility timeout documentation
• GitHub Actions workflow triggers documentation