How do you build reliable subscription renewal reminders?

Failure One: Sending Twice

The naive job selects subscriptions renewing in seven days and emails their owners. It works until the cron platform retries a timed-out invocation, at which point some users get two identical emails and a few get three.

Retries are not an edge case — they are the documented behaviour of most schedulers, and a job that sends email must assume it will be run again with the same input.

The fix is a stored fact, not a lock:

create table reminder_sends (
  subscription_id uuid not null references subscriptions(id) on delete cascade,
  due_date        date not null,
  days_before     smallint not null,
  sent_at         timestamptz not null default now(),
  primary key (subscription_id, due_date, days_before)
);

The primary key does the work. Insert before sending; if the insert conflicts, another run already handled it and this one skips. `due_date` in the key rather than a plain timestamp is what makes the same subscription eligible again next month without any cleanup job.

insert into reminder_sends (subscription_id, due_date, days_before)
values ($1, $2, $3)
on conflict do nothing
returning subscription_id;

No row returned means someone else got there first. Send only when a row comes back.

Failure Two: Timezones

"Seven days before renewal" is not a single instant. For a user in Auckland and one in Lisbon it is twelve hours apart, and a job running at 09:00 UTC delivers a reminder at 22:00 to one of them.

Two decisions.

Store the user's timezone as an IANA identifier — `Europe/Zurich`, not an offset. Offsets are wrong twice a year.

Evaluate the reminder window in the user's local date, not in UTC:

where (s.next_billing_date - (r.days_before || ' days')::interval)::date
      = (now() at time zone u.timezone)::date

Then run the job hourly rather than daily and send only to users whose local time is in a sensible window — 08:00 to 10:00 is conventional. An hourly job with a local-hour filter delivers everyone a morning email; a daily job delivers everyone the same instant and half of them get it at night.

The subtlety that catches people: `next_billing_date` should be a `date`, not a `timestamptz`. A renewal happens on a day, not at an instant, and storing it with a time zone means it shifts under conversion.

Failure Three: The Run That Stops Halfway

Serverless functions have execution limits. A job iterating ten thousand subscriptions will hit one, and the platform will kill it mid-list. Without a cursor, the second half of the list is never processed — and because the job "ran", nothing alerts.

This one is nastier than it looks, because it degrades gradually. The job completes fine at a thousand users, completes at three thousand, and starts silently truncating at six thousand. Nobody notices, because the users who stop receiving reminders are always at the end of the ordering.

Two mechanisms together:

Order deterministically and record a cursor. Order by `id`, store the last processed one, resume from it on the next invocation.

Budget the time explicitly and report exhaustion. Check elapsed time each iteration, stop at 80 % of the limit, and write a run record that says how many were processed and whether the run finished:

const budget = Date.now() + 0.8 * LIMIT_MS
for (const row of rows) {
  if (Date.now() > budget) { partial = true; break }
  await send(row)
  cursor = row.id
}
await recordRun({ checked, sent, partial, cursor })

`partial` is the important field. A job that finishes early and says so is operable; one that finishes early silently is a slow leak. Surface the run records somewhere you look — a health panel that shows last-run time and outcome per job answers "is this still working" without waiting for a user to report it.

Failure Four: Delivery Reported as Success

The one that cost us the most. The application called the email provider, received an HTTP 200, and reported success to the user. The message was not delivered. A 200 from a transactional email API means the request was accepted for processing, which is not the same claim.

Three things close this.

Record the provider's response, not just the status code. Store the message id and any error body against the send. Without it, the only debugging tool is the provider's own dashboard, and correlating it back to a user is guesswork.

Surface failures to the interface. If a send fails, the user should see that it failed. Reporting success for a message that did not arrive is worse than an honest error, because the user acts on the false belief that they are covered.

Have a fallback path and know which one was used. A second provider is only useful if you can tell afterwards which one delivered. Store it.

Rate limits deserve their own note: providers throttle, and a throttled send may be dropped with no log entry at all on the sender's side. If your job sends a burst once a day, it is exactly the shape that triggers throttling. Spread the sends, and treat a missing outcome record as a failure rather than as an absence of evidence.

Lead Times Are Per Subscription

A single global "remind me N days before" setting is the wrong shape, and it is worth getting right in the schema rather than retrofitting.

Seven days suits a monthly plan. Thirty or more is required for an annual one, because annual contracts commonly carry a thirty-day notice period and a reminder inside that window arrives after the last moment action would have helped. Three days is right for a trial conversion.

create table reminder_rules (
  id              uuid primary key default gen_random_uuid(),
  subscription_id uuid not null references subscriptions(id) on delete cascade,
  days_before     smallint not null check (days_before between 1 and 90),
  channel         text not null default 'email'
);

Multiple rows per subscription lets a user have both a thirty-day and a three-day warning on an expensive annual plan, which is what people actually want and what a single integer column cannot express.

Sensible defaults by cycle at creation time — 7 for month, 30 for year, 3 for trial — mean most users never touch the setting, which is the point.

Checking Entitlement Inside the Job

A small operational note that is easy to get wrong and embarrassing when you do.

If reminders are a paid feature, the check belongs in the job's query, not only in the UI. We shipped a reminders cron with no plan filter, so free users received renewal emails while the interface showed them a paywall for the same feature. Nobody complained — it was a better product than we were selling — but the pricing page was making a false statement.

Every scheduled job that produces user-visible output needs the entitlement check in its own query. The UI is not a gate; it is a display.

The Honest Build-Versus-Buy Line

The schema is the easy part. It is also the part that feels like progress, which is why people stop enjoying the project immediately after finishing it.

What actually takes the time, in the order it bites:

Scheduled reminders. Not the sending — a cron job and an SMTP call is an afternoon. The problems are idempotency (a retried run must not email twice), timezone handling (a reminder "7 days before" for a user in Auckland is not the same instant as for one in Lisbon), and the failure mode where the job dies halfway through the list and the second half silently never runs. Every one of these has bitten us in production.

Recurrence maths. "Monthly" is not a fixed interval. The 31st of January plus one month is a decision, not a calculation, and every library makes a different one. Add quarterly, annual, every-two-months and every-18-months and you have a small state machine.

Currency. Storing an amount without its currency works until the first user has two. Converting at entry time is the intuitive fix and it is wrong — the stored figure drifts as rates move, and the user's original agreement is lost.

Nothing to import from. Manual entry of twenty subscriptions is genuinely tedious. Building CSV import means writing a column mapper, and a column mapper that silently mishandles a column is worse than no import at all.

None of that is hard in the sense of being intellectually difficult. It is hard in the sense of being four weekends, and the fourth weekend is the one where the project dies.

If the goal is to learn, build it — the schema below is a real one and it works. If the goal is to stop losing money to forgotten subscriptions, the build is a detour.

What SubTracker Does With This

For context, since the decisions above come from a running system rather than from a design exercise.

SubTracker is a Next.js and PostgreSQL application (Supabase) that tracks subscriptions manually — there is no bank connection, so every row originates from a form or a CSV import. That constraint shapes the schema: there is no "detected" state, no confidence score, no merchant-matching table. Every row is an assertion the user made.

The free plan tracks unlimited subscriptions and includes CSV import and a live calendar feed. Reminders and price-hike alerts are Plus, at $5.90/month. Family is $9.90/month and adds a shared workspace for up to ten people, which is where the ownership columns below stop being theoretical.