OTP Cooldowns With Postgres Partial Indexes

I keep seeing the same bug in verification systems: the API says "resend OTP" as if it were a harmless helper, but under load it becomes a small abuse surface, a support headache, and sometimes a delivery cost leak too. The fix is usually not a fancier controller. It is a better data model.

When I am working on Authentication flows, I want three properties at the same time:

• a user can request a fresh code without waiting forever
• the service can enforce cooldown rules consistently across instances
• support and ops can explain exactly why a request was accepted or rejected

That is where PostgreSQL partial indexes and an attempt ledger fit really well. It is not the only way, but it stays simple enough to maintain six months later, which matters more than people admit.

Why resend cooldowns get messy fast

Most teams start with a timestamp on the user row, then add an if now - last_sent_at < 60 seconds check in the API. That works for a week. Then somebody adds retries, background workers, passwordless login, or region failover and the rule starts drifting between code paths.

The bigger issue is that cooldown logic is usually attached to the request handler, not to the delivery event itself. If the first transaction commits and the mail job is retried, you can get weird states: cooldown set but no email sent, or email sent twice with one accepted request. It sounds edge-casey, but it shows up pretty often in real systems.

I also try to separate user abuse from product behavior. Someone testing signup with temp mail com or a disposable inbox is not automatically malicious. But your API still needs boundaries. The rule should protect the system without assuming every unusual inbox is bad.

Model cooldowns as data, not controller logic

The pattern I reuse is straightforward:

1. Store every OTP delivery attempt in a dedicated table.
2. Mark whether the attempt is still inside the active cooldown window.
3. Let Postgres enforce "only one active cooldown per recipient and purpose".
4. Expire the active flag when the window passes or the code is consumed.

That gives you an append-only history and a single source of truth. The API becomes thinner, and background workers can reason about the same state as the web nodes.

Here is the core table:

create table otp_delivery_attempts (
  id bigserial primary key,
  recipient_email text not null,
  purpose text not null,
  otp_hash text not null,
  cooldown_until timestamptz not null,
  active_cooldown boolean not null default true,
  delivery_status text not null default 'pending',
  created_at timestamptz not null default now(),
  consumed_at timestamptz
);

create unique index otp_active_cooldown_idx
  on otp_delivery_attempts (recipient_email, purpose)
  where active_cooldown = true;

That partial unique index is the useful bit. At any moment, one email address and one purpose can have only one active cooldown row. Every app instance gets the same answer because the database is doing the hard part.

Then the create flow becomes more boring, in a good way:

insert into otp_delivery_attempts (
  recipient_email,
  purpose,
  otp_hash,
  cooldown_until
)
values ($1, $2, $3, now() + interval '60 seconds');

If the insert succeeds, you enqueue delivery. If it conflicts, you return the remaining wait time. This is one of those places where a boring constraint beats a clever mutex.

The Postgres pattern I keep reusing

There are two implementation details that matter more than the table name.

First, I do not delete old rows. Historical rows are useful for support review, abuse heuristics, and debugging vendor incidents. If email latency spikes, you want to know whether the problem is request volume, worker lag, or upstream delivery.

Second, I clear active_cooldown explicitly when the code is consumed or when a sweeper job sees cooldown_until < now(). That may feel redundant because the timestamp already exists, but keeping an indexed boolean makes the uniqueness rule cheap and predictable.

For the API layer, I like returning a response shape like this:

{
  "accepted": false,
  "retry_after_seconds": 23,
  "reason": "cooldown_active"
}

The important thing is consistency. Your mobile app, SPA, and CLI should all see the same retry semantics. When teams skip that, the frontend invents its own timers and things get janky real fast.

If your delivery workers share infrastructure with other notification jobs, keep an eye on queue contention too. This write-up on queue isolation for email work is about a different domain, but the operational lesson carries over almost directly.

Testing the flow without burning real inboxes

Verification systems are awkward to test because the happy path crosses API, database, and inbox state. For local and CI checks, I usually want one inbox per scenario so I can assert:

• first send succeeds
• immediate resend is blocked
• resend after cooldown succeeds
• consuming the code clears the active lock

That is also where passwordless OTP inbox boundaries are worth reading. If test cases share inboxes, it gets hard to tell whether a failed assertion came from your auth logic or from polluted test state.

For manual verification, a generate throwaway email flow can be handy because it keeps personal inboxes out of low-value tests. When I only need to inspect formatting or timing, a service like temporary disposable mail is enough to validate the end-to-end path without wiring a full fixture harness. That should support testing, not replace proper integration coverage, but it saves time.

I also sometimes drop odd strings like temp gamil com or dummy e mail into test notes and seeded cases. Not as anchors, just as ugly input. It catches sanitization bugs and brittle analytics rules more often than it should, honestly.

One more thing: if your provider can delay or duplicate callbacks, store provider message IDs on the attempt row. Otherwise your system may look correct from the API side while the delivery ledger is lying a little bit.

Q&A

Why not store cooldown on the user record?

Because resend behavior belongs to an attempt stream, not to the identity row. A single user may trigger signup verification, email change verification, and passwordless login in the same hour. Those are different purposes with different rules.

Do I need Redis for this?

Not always. If PostgreSQL already owns the auth state, using a partial index for this rule is perfectly reasonable. Redis can still help for high-volume counters, but I would not add another moving part before proving the database is the bottleneck.

What is the most common mistake?

Treating resend as a UI concern. It is a backend contract first. The button text matters, sure, but the durable rule has to live where concurrent requests cannot dodge it.

The nice part of this pattern is that it scales down as well as up. A small service can ship it in one migration, and a larger system can extend it with outbox records, provider receipts, and risk scoring later. It is not flashy, maybe a little plain even, but plain systems are often the ones that keep working.