A per-user reminder backend: schema, delay budget, delivery

Reminders due inside seven days can live in the broker; anything further has to wait in your table. The rule, the schema, and both delivery paths on Infrai's queue.

A reminder backend is three decisions, not three thousand lines. Where does a pending reminder physically wait, what wakes your code up when it’s due, and what happens when the send fails. Infrai’s queue answers the second and third directly — messages can be published with a delay of up to 604800 seconds and retried automatically — and the first has a rule attached to that number.

Anything due inside a week can go straight into the broker at the moment the user sets it. Anything further out waits in your own table until a sweep brings it inside the window.

Why the seven-day figure decides your schema

delay_seconds on publish is documented as 0..604800, and a request over that maximum is refused. So the design question isn’t “can the queue hold my reminder” but “for how much of its life should it”. A message inside the broker is fast and needs no polling; a row in your table is listable, cancellable and editable. Split the lifetime between the two and you get both.

BullMQ’s repeatable jobs handle that third row natively with a cron expression, and if Redis is already in your stack that’s less machinery than a generator you write. Celery Beat plays the same role in Python. Neither removes the need for the first two rows; they just move the recurrence bookkeeping into the broker.

The rows

Per-user reminders need the user’s timezone stored next to the time, because “09:00” means fourteen different instants across a customer base and you’ll be asked to render the schedule back to them.

CREATE TABLE user_reminder (
  id            bigserial PRIMARY KEY,
  user_id       text        NOT NULL,
  channel       text        NOT NULL CHECK (channel IN ('email','sms','push')),
  template      text        NOT NULL,
  fire_at       timestamptz NOT NULL,
  tz            text        NOT NULL DEFAULT 'UTC',
  state         text        NOT NULL DEFAULT 'pending'
                CHECK (state IN ('pending','handed_off','sent','cancelled','failed')),
  message_id    text,
  UNIQUE (user_id, template, fire_at)
);

CREATE INDEX user_reminder_handoff
  ON user_reminder (fire_at)
  WHERE state = 'pending';

handed_off is the state that matters. It means the reminder now lives in the broker and the sweep must stop looking at it, which is what keeps a slow sweep from publishing the same nudge twice.

Hand off at creation when you can

import process from "node:process";
import pg from "pg";

const API = "https://api.infrai.cc";
const MAX_DELAY = 604800;                        // 7 days, the documented ceiling
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const key = process.env.INFRAI_API_KEY;
if (!key) throw new Error("INFRAI_API_KEY is required");

/** Publish a reminder into the broker, delayed until it is due. */
export async function handOff(row) {
  const seconds = Math.floor((new Date(row.fire_at).getTime() - Date.now()) / 1000);
  if (seconds > MAX_DELAY) return null;          // too far out; leave it in the table
  const message = {
    queue: "user-reminders",
    body: { reminder_id: row.id, user_id: row.user_id, channel: row.channel, template: row.template },
    delay_seconds: Math.max(0, seconds),
  };
  const res = await fetch(`${API}/v1/queue/publish`, {
    method: "POST",
    headers: { Authorization: `Bearer ${key}`, "Content-Type": "application/json" },
    body: JSON.stringify(message),
  });
  const out = await res.json();
  if (!out.ok) throw new Error(`${out.error.code}: ${out.error.message}`);
  await pool.query(
    "UPDATE user_reminder SET state = 'handed_off', message_id = $1 WHERE id = $2 AND state = 'pending'",
    [out.data.message_id, row.id],
  );
  return out.data.message_id;
}

/** Runs hourly: hand off everything that has come inside the window. */
export async function sweep() {
  const { rows } = await pool.query(
    `SELECT id, user_id, channel, template, fire_at FROM user_reminder
      WHERE state = 'pending' AND fire_at <= now() + interval '6 days'
      ORDER BY fire_at LIMIT 2000`,
  );
  let handed = 0;
  for (const row of rows) {
    try { if (await handOff(row)) handed += 1; }
    catch (err) { console.error(`reminder ${row.id} not handed off: ${err.message}`); }
  }
  console.log(`sweep handed off ${handed}/${rows.length}`);
  return handed;
}

Six days, not seven, is deliberate — sweeping at the exact boundary means a slow run computes a delay of 604801 and gets a 400 back.

The same publish from the command line, if you’d rather see it once before wiring it in:

export INFRAI_API_KEY="your_infrai_api_key"

curl -sS -X POST "https://api.infrai.cc/v1/queue/publish" \
  -H "Authorization: Bearer ${INFRAI_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"queue":"user-reminders","body":{"reminder_id":8801,"user_id":"u_4412","channel":"email","template":"trial_ending"}}'

Publishing also accepts an idempotency_key. Send the same key twice and you get the same message_id back rather than two reminders, which covers the double-submit and the retried API call — worth setting to something like reminder:8801.

Being woken up: push or poll

Two delivery paths exist and they aren’t equivalent. A push subscription needs a public HTTPS endpoint; a polling consumer needs nothing but outbound network, which is why it’s the fallback for anything running behind a VPN or on a laptop.

curl -sS -X POST "https://api.infrai.cc/v1/queue/push_subscribe/user-reminders" \
  -H "Authorization: Bearer ${INFRAI_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"queue":"user-reminders","url":"https://hooks.example.com/q/8f2c1d4a/reminders","secret":"a-long-random-string"}'

Two limitations to register before you rely on this. The URL isn’t proved reachable at subscribe time — registration succeeds against an endpoint that doesn’t exist yet, so a typo shows up later as silence rather than an error. And there’s no unsubscribe route in the queue namespace, so treat every subscription as permanent and put a version segment in the path (as above) when you need to move traffic to a new handler.

The receiver itself should do almost nothing:

import express from "express";
import { timingSafeEqual } from "node:crypto";

const app = express();
const token = process.env.PUSH_PATH_TOKEN ?? "";

app.post("/q/:token/reminders", express.json({ limit: "512kb" }), (req, res) => {
  const given = Buffer.from(req.params.token);
  const want = Buffer.from(token);
  if (given.length !== want.length || !timingSafeEqual(given, want)) {
    return res.status(404).end();
  }
  res.status(200).end();                          // acknowledge first
  queueMicrotask(() => {
    send(req.body).catch((err) => console.error(`send failed: ${err.message}`));
  });
});

async function send(payload) {
  const res = await fetch(process.env.NOTIFY_URL, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(payload),
    signal: AbortSignal.timeout(15_000),
  });
  if (!res.ok) throw new Error(`notifier returned ${res.status}`);
}

app.listen(3000, () => console.log("reminder receiver on :3000"));

The polling version is the same handler wrapped in a loop:

import process from "node:process";

const API = "https://api.infrai.cc";
const headers = {
  Authorization: `Bearer ${process.env.INFRAI_API_KEY}`,
  "Content-Type": "application/json",
};

async function call(path, payload) {
  const res = await fetch(`${API}${path}`, { method: "POST", headers, body: JSON.stringify(payload) });
  const out = await res.json();
  if (!out.ok) throw new Error(`${path}: ${out.error.code} ${out.error.message}`);
  return out.data;
}

export async function poll() {
  const { items } = await call("/v1/queue/consume", { queue: "user-reminders", max_messages: 10 });
  for (const msg of items) {
    try {
      await deliver(msg.payload);
      await call("/v1/queue/ack", { queue: "user-reminders", receipt_handle: msg.message_id });
    } catch (err) {
      console.error(`delivery ${msg.delivery_count} failed: ${err.message}`);
      await call("/v1/queue/nack", { queue: "user-reminders", message_id: msg.message_id });
    }
  }
  return items.length;
}

async function deliver(payload) {
  const res = await fetch(process.env.NOTIFY_URL, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify(payload),
    signal: AbortSignal.timeout(15_000),
  });
  if (!res.ok) throw new Error(`notifier returned ${res.status}`);
}

Acking a message whose 300-second lease already expired returns QUEUE_MESSAGE_NOT_IN_FLIGHT — a sign your handler is slower than the visibility timeout, and that the reminder has probably gone out twice.

Checking the backend is alive

curl -sS "https://api.infrai.cc/v1/queue/stats/user-reminders" \
  -H "Authorization: Bearer ${INFRAI_API_KEY}"

curl -sS "https://api.infrai.cc/v1/account/usage" \
  -H "Authorization: Bearer ${INFRAI_API_KEY}"

delayed_count should track the number of rows in handed_off; a gap between them is the alert worth having. On cost, only the publish is billable — $0.00002 per reminder, verified 2026-07-26 — while consume, ack, nack, stats and push registration are free within rate limits. The $2 of starting credit covers roughly 100,000 reminders, and these rates have trended down rather than up, so read the usage call rather than this sentence when you plan a year.

The part that isn’t priced is the part that usually costs most: the reminder still has to be delivered. Email and SMS sit behind the same key and the same invoice as this queue, which is the argument for keeping them together — not the per-message rate.

References

• Infrai queue API reference
• QUEUE_MESSAGE_NOT_IN_FLIGHT error reference
• Upstash QStash schedules
• BullMQ repeatable jobs