The nudge we could already send twice

The rest of this post is the deep dive: why a materialised queue beat a scan, why claimed_at and sent_at are two columns, and why an unscoped write is a decision rather than an oversight.

The feature

A short message before the thing happens. Your 3pm has a nudge at 2:50; a follow-up you gave a time to has one ten minutes before it. If your calendar already carries "remind me 30 minutes before" for that specific event, that is the lead time we use — asking you to state the same preference twice in two apps is an integration failing at its job.

Simple to describe. The interesting part is what "send it at the right minute" costs.

The query that had nothing to stand on

The obvious implementation is a sweep: every minute, for every tenant, ask what is due in the next few minutes and send it. Three facts in the existing code killed that before it was written.

• follow_ups had no index on due_date.
• The helper that finds due reminders loaded every task in the account and filtered them in JavaScript.
• Connected-calendar events were persisted nowhere at all. Answering "what meetings does this user have in ten minutes" means an OAuth round trip to Google or Microsoft, per tenant, per run.

That last one is the arithmetic that settles it. A per-tenant calendar call, at every minute, at today's account count, is on the order of 92,000 provider calls a day — to answer, almost always, "nothing". The cost scales with how many customers we have, not with how many reminders are actually due, which is exactly backwards.

So the work moves earlier

A scheduled_sends table is materialised per tenant over a three-hour look-ahead: a slower sweep resolves each user's calendar and timed follow-ups once and writes rows saying this nudge, for this subject, at this instant.

Delivery then stops being a scan. It is one indexed query over one table, costing O(rows due) rather than O(tenants), and returning nothing at all on most minutes. That is what makes a per-minute cron affordable — so the two run on separate schedules, at separate routes, at the frequencies their costs justify: materialising every fifteen minutes, delivering every minute.

Materialising more than once over the same window has to be free, because it happens constantly in normal operation: two runs overlap, a deploy restarts one mid-flight, a retry replays a batch. The queue is therefore idempotent on a unique index over (user_id, kind, ref_id, fire_at) — refilling the same window twice queues one nudge, not two.

The bug the speed-up exposed

Delivery was three separate statements:

1. SELECT … WHERE sent_at IS NULL AND fire_at <= now()
2. send the message
3. UPDATE … SET sent_at = now() WHERE id = ? AND sent_at IS NULL

Step 3's guard looks like the thing that stops a double send. Its own comment said as much: it guards against "a concurrent second tick".

It does not. It runs after the send. Two runs both pass the guard-free select in step 1, both send in step 2, and only the second loses the write in step 3. The guard prevented an overwritten timestamp. It never prevented a duplicate message.

This was true before we changed anything. At a quarter-hourly cadence the window where two runs overlap is small and the bug is rare enough to look like it doesn't exist. Moving to every minute doesn't create the race — it schedules it. And of all the ways a proactive feature can fail, a repeated "your meeting starts in 10 minutes" is the worst one: a late nudge is a mild annoyance, a duplicate one is the product looking broken.

Claim it, in one statement, in the database

The select became a claim:

UPDATE scheduled_sends
   SET claimed_at = now()
 WHERE id IN (
         SELECT id FROM scheduled_sends
          WHERE sent_at IS NULL
            AND fire_at <= now              -- due
            AND fire_at >  now - grace      -- but not so late it should be dropped
            AND (claimed_at IS NULL OR claimed_at < now - lease)
          ORDER BY fire_at
          LIMIT 200
          FOR UPDATE SKIP LOCKED
       )
RETURNING …

The inner select is the old predicate — which is also, exactly, the partial index behind it — plus the claim test. Four things in there are load-bearing.

It is in the database. Two delivery runs are two serverless invocations, possibly on two machines, sharing no memory. An in-process mutex is not a weaker version of this fix; it is not a version of it at all.

SKIP LOCKED makes a second runner step over rows the first has locked rather than block behind them. Blocking would serialise the runs and, worse, make a slow send hold up everyone else's.

The subquery exists because Postgres has no LIMIT on UPDATE. The bound itself is not about the database — the indexed read would happily return ten thousand rows. It is about what happens next: every row becomes an outbound WhatsApp or Telegram message, against a provider with rate limits, from a function with a wall-clock timeout. An unbounded run that meets a backlog gets killed halfway, having sent an arbitrary prefix and marked an arbitrary subset. A fixed slice, oldest first, drains a backlog at a known rate and starves nothing.

claimed_at is not sent_at. Collapsing them into one column would be tidier and would swallow every failure. A send that fails has to stay retryable, so sent_at is stamped on success — and on "we looked and there was nothing worth saying" — but never on failure. A row stamped sent before the attempt is a nudge the user never receives that the system believes it delivered. Losing a nudge is worse than sending one late.

The claim therefore expires. A run that never comes back — a function timeout, a redeploy mid-flight — releases nothing, so a claim older than its lease is claimable again. An ordinary failed send doesn't wait for that: the sender clears the column itself and the next minute retries. And the grace clause is the other half of the same thought — a row too old to be worth sending is never sent at all, because "your meeting starts in 10 minutes" delivered an hour later is not a late message, it's a wrong one.

The zero that wasn't a quiet minute

Turning the cron on was not enough. On hosted, the delivery sweep read zero rows on every run.

scheduled_sends is a tenant table, so our enterprise package puts FORCE ROW LEVEL SECURITY on it. Its policy wants either a matching app.current_user_id or an explicit bypass flag on the session. And the claim is unscoped by necessity: it has to find the due rows before it can know whose they are. That is the one query in the module that cannot carry a tenant, and nothing in the app outside the schema code had ever set the bypass.

So the policy's USING clause matched nothing, and the claim — an UPDATE … RETURNING — stamped no rows and returned none.

That failure shape is the point of writing this down. A read that returns nothing looks like an empty result. A write that matches nothing looks like an empty result and succeeds. Nothing threw, nothing logged, the run reported "0 due", and a quiet minute and a total outage produced byte-identical output. Feature built, tested, cron scheduled, and no nudge was ever going to arrive.

The bypass is now explicit, transaction-local, and covers exactly two statements: the claim, and the bookkeeping that settles a row afterwards. Everything else in the module stays tenant-scoped, and the rows the claim returns carry their own owner so the delivery step re-enters that tenant's scope before it resolves anything about the user — because under a bypass, a careless read across tenants does not fail. It succeeds.

A test now proves the whole thing rather than arguing it. Our isolation harness stands up a non-superuser Postgres — FORCE ROW LEVEL SECURITY binds a table's owner but never a superuser, so an isolation suite run as postgres passes vacuously, which is worse than having no suite — replays the shipped policy verbatim, and asserts both directions: unscoped reads and claims see nothing, the same statements under the bypass see every tenant's due rows, and a forged cross-tenant insert is still refused.

Three things worth taking away

Making something faster doesn't create a race. It schedules one. The double send existed at fifteen-minute cadence too. What the change did was move it from "has probably never happened" to "will happen this week", which is a difference in observation, not in correctness.

A guard's position in the sequence is part of the guard. WHERE sent_at IS NULL was real, tested and honestly named, and it protected a column instead of a user, because it ran after the side effect.

A no-op write is the worst failure shape available. If the claim had thrown, we would have found it in the first minute. Because it succeeded and did nothing, it took a deliberate look at row-level security to find at all — and the only durable fix for that class of bug is a test that reproduces the production security posture, rather than one that runs as a superuser and passes because nothing is enforced.