Short answer: use cron only to wake a coordinator; record each due renewal in Postgres with a stable idempotency key, let queue workers deliver reminders, and clean completed records in bounded batches after the business deadline. This is the least complex design that can survive duplicate triggers, worker restarts, and a large dataset without pretending that cron itself provides delivery.
For a property manager, the deadline is the product rule. A lease renewal reminder may become eligible at 09:00 in the property's business timezone, but the scheduler, database, and queue all have different ideas about time and completion. The useful guarantee is not "the cron expression ran." It is "every eligible lease produced at most one logical reminder, retries remained possible, and cleanup never erased evidence that delivery was still pending."
Keep that distinction sharp.
The 09:00 deadline sets the work budget
The data flow is short: a cron trigger wakes a Node.js coordinator, the coordinator finds due lease deadlines in Postgres and inserts durable work records, workers claim those records and send reminders, and a separate cleanup pass deletes old terminal records in small batches. The trigger can run twice. The worker can stop halfway through. Neither event should change the logical outcome because Postgres, rather than process memory, holds the transition state.
A stable key ties the whole path together. For example, property_id + lease_id + renewal_deadline identifies one logical reminder. Put a unique constraint on that key. A repeated trigger then becomes a harmless insert conflict rather than a second tenant message. Don't derive the key from the run time: two cron invocations at 09:00 and 09:01 would look different even though they represent the same business event.
The coordinator also needs a watermark or an overlap window. If it scans only now() to now() + interval, a delayed invocation can leave a permanent gap. Instead, scan eligible rows through the current cutoff and rely on the unique key to absorb overlap. I'm not sure how wide that overlap should be for every deployment; scheduler delay observations and the maximum acceptable reminder latency are what settle it. The correctness rule, however, stays fixed: overlap is safe, gaps aren't.
Why keep the coordinator inside one Postgres transaction?
The following TypeScript keeps scheduling and claiming explicit. It assumes renewal_work has a unique constraint on (property_id, lease_id, renewal_deadline), plus status, attempts, available_at, and claimed_at columns. The cron handler does no delivery work.
import { Pool, PoolClient } from "pg";
const db = new Pool({ connectionString: process.env.DATABASE_URL });
const BATCH_SIZE = 200;
export async function onCronTick(cutoff: Date): Promise<number> {
const result = await db.query(
`INSERT INTO renewal_work (property_id, lease_id, renewal_deadline, status, available_at)
SELECT property_id, lease_id, renewal_deadline, 'ready', NOW()
FROM leases
WHERE renewal_deadline <= $1
AND renewal_reminder_required = TRUE
ON CONFLICT (property_id, lease_id, renewal_deadline) DO NOTHING`,
[cutoff],
);
return result.rowCount ?? 0;
}
type RenewalJob = {
id: string;
propertyId: string;
leaseId: string;
renewalDeadline: Date;
attempts: number;
};
async function claimBatch(client: PoolClient): Promise<RenewalJob[]> {
const result = await client.query<RenewalJob>(
`WITH claimed AS (
SELECT id
FROM renewal_work
WHERE status = 'ready' AND available_at <= NOW()
ORDER BY renewal_deadline, id
FOR UPDATE SKIP LOCKED
LIMIT $1
)
UPDATE renewal_work AS work
SET status = 'processing', claimed_at = NOW(), attempts = attempts + 1
FROM claimed
WHERE work.id = claimed.id
RETURNING work.id,
work.property_id AS "propertyId",
work.lease_id AS "leaseId",
work.renewal_deadline AS "renewalDeadline",
work.attempts`,
[BATCH_SIZE],
);
return result.rows;
}
export async function takeWork(): Promise<RenewalJob[]> {
const client = await db.connect();
try {
await client.query("BEGIN");
const jobs = await claimBatch(client);
await client.query("COMMIT");
return jobs;
} catch (error) {
await client.query("ROLLBACK");
throw error;
} finally {
client.release();
}
}
There is an intentional limit here: claiming and sending are separate operations. A worker can send a reminder and stop before marking it complete, so a retry can repeat the external side effect. Exactly-once delivery doesn't fall out of a queue. The receiving notification boundary needs the same idempotency key, or the system must accept at-least-once attempts and reconcile duplicates. For tenant communication, I would require idempotency at the sender because a duplicate renewal notice is visible and confusing.
The long scan deserves equal attention. Index the eligibility predicate used by the coordinator, inspect its query plan with production-shaped data, and keep the enqueue transaction bounded. One giant transaction over millions of leases holds state for too long and makes rollback expensive. Page on a stable tuple such as (renewal_deadline, id), commit each page, then continue. Offset pagination is a poor fit because rows can move relative to the offset while the scan is active.
Failure injection is the acceptance test
A useful test matrix forces the clock across a timezone boundary, invokes the trigger twice with the same cutoff, stops a worker after the external send, holds one claimed row past its lease, and starts cleanup while retryable work remains. Assert business outcomes: one logical work record per lease deadline, no missing eligible lease, retry visibility after a stopped worker, and zero deletion of nonterminal rows. A unit test for the cron expression proves almost none of this.
Deployment should preserve the same boundaries. Run schema migration before workers understand the new state, cap worker concurrency independently of trigger frequency, and expose lag as now - oldest ready deadline. Counts alone can look healthy while one old property remains stuck behind newer work. Alert on age, repeated attempts, dead-letter volume when a queue supports it, and cleanup progress. Keep correlation fields such as lease ID out of unstructured log prose so they can be queried consistently, and apply the application's privacy rules because these identifiers point back to tenant records.
For an operational checklist, read the pipeline left to right before release: verify the business timezone and cutoff calculation; confirm the unique key survives repeat triggers; prove that a worker claim expires or is returned to ready state; exercise idempotency at the notification boundary; pause cleanup and demonstrate that reminders continue; resume it with a deliberately small batch; and inspect the oldest-ready and oldest-retained ages. Stick with a single-process scheduler only when missed work can be reconstructed cheaply and duplicate delivery has no meaningful impact. It is not suitable when a renewal deadline must remain auditable across deploys or process loss.
What should a Node.js cron trigger queue guarantee for Postgres cleanup?
| Guarantee | Mechanism | Failure it contains | Evidence to retain |
|---|---|---|---|
| No lost eligibility | Overlapping scans plus a unique business key | Late or repeated cron invocation | Last successful cutoff and inserted count |
| Exclusive claiming | Short transaction with FOR UPDATE SKIP LOCKED
|
Concurrent workers selecting the same row | Claim age and worker attempt count |
| Safe retry | End-to-end idempotency key and delayed requeue | Stop after an external send | Logical key and delivery receipt |
| Safe cleanup | Terminal-state filter, age threshold, and batch limit | Deleting work that can still be retried | Deleted count and oldest retained row |
These are delivery guarantees, not vendor features. Cloudflare Workers Cron Triggers can supply the scheduled wake-up. AWS SQS can supply a managed queue and a dead-letter queue for messages that repeatedly fail processing. PostgreSQL can hold the lease truth, work ledger, and claim transitions. Node.js can run the coordinator and workers. Those products occupy different boundaries; comparing them as substitutes hides the real decision.
BullMQ is another possible Node.js queue boundary, while a Postgres-backed work table keeps the state model in one database. The trade-off isn't a universal ranking. A managed queue separates worker pressure from the primary database and offers queue-specific operations, but it introduces a second durable system whose publish boundary must be coordinated with Postgres. A database-backed queue avoids that handoff, yet worker polling and queue retention consume database capacity shared with the application. Measure the contention that matters to your workload. Your mileage may vary.
Retention spends database capacity too
Cleanup should target old terminal work, never merely old work. Use a retention cutoff that leaves enough history for support investigation and replay policy, then delete by primary key in fixed-size transactions. If the table is partitioned by a time field that matches the retention rule, dropping an eligible partition can make cleanup predictable; if the rule depends on mutable delivery status, row batches are easier to reason about. The catch is that either approach adds operational work. For a modest table, keep ordinary indexed deletes and accept the slower path until measurements justify partition lifecycle management.
Do not schedule cleanup in the same transaction that discovers reminders. Discovery is allowed to repeat and should finish quickly. Retention has a different risk profile: a bad cutoff removes evidence. Separate jobs, credentials, metrics, and stop conditions make an incorrect cleanup run easier to halt without delaying reminders.
Tiny batches win.
For the large dataset in this scenario, start with a conservative batch size, record transaction duration and rows deleted, and adjust from observed lock time and replication pressure rather than from a copied magic number. Cost matters here too: more frequent tiny scans spend compute and database I/O, while infrequent huge scans create bursts. The sensible point depends on lease volume, index selectivity, worker concurrency, and the business deadline. No single cadence is a best practice in isolation.
References
These primary references document the external scheduling and failed-message boundaries. Read them alongside the database query plans and delivery contract from your own system; those local artifacts determine the safe batch size, retry horizon, and deadline tolerance.
- AWS, "Amazon SQS dead-letter queues": https://docs.aws.amazon.com/AWSSimpleQueueService/latest/SQSDeveloperGuide/sqs-dead-letter-queues.html
- Cloudflare, "Cron Triggers": https://developers.cloudflare.com/workers/configuration/cron-triggers/
Top comments (0)