← all topics Delayed payments board · say it in this order 1 Require 4m2 Entities 1m 3 API 3m4 Design 10m 5 Dives ~20mreset
SAY THIS SENTENCE FIRST

"The database is the schedule. The payments table, indexed by due time, is what the schedulers read; pollers claim due rows with SKIP LOCKED and leases, and the gateway idempotency key, the paymentId, collapses every retry into at most one charge."

STEP 1 OF 5

Requirements

4 min
ASK, THEN COMMIT

Four questions. State your assumption for each so the interview moves even without answers.

  1. What is being paid: an external card/bank via a gateway, or in-game currency?  Assume an external gateway (the harder case); the in-game variant swaps the gateway for the wallet service and gets simpler.
  2. Due-time precision: is "within a minute of due" acceptable?  Assume yes; sub-second scheduling is a different problem.
  3. Can a payment be edited before due, or only cancelled?  Assume cancel plus re-create.
  4. What happens on terminal failure (card declined after retries): notify and drop, or park for manual review?  Assume notify + park.
Numbers to say

100M scheduled payments/day, about 1.2K executions/sec average, with sharp due-time spikes (midnight, 1st of month) reaching 50 to 100x average. Trigger lateness p99 under 60s. All amounts in integer minor units, never floats or DECIMAL.

Hard rules

At most once against the gateway no matter what crashes (the money side); at least once attempted (the product side); reconciled to exactly-once effect via gateway idempotency. Full audit of every attempt. Out of scope: gateway internals, FX, subscription logic (this is the engine such logic schedules onto).

DONE WHEN: interviewer nods at the split: average load is a correctness problem, and the only throughput story is the due-time spike.
STEP 2 OF 5

Core entities

1 min
  • Scheduled payment: who pays, how much, when, with a lifecycle (pending → processing → completed / failed / cancelled).
  • Execution attempt: one try against the gateway, with its outcome; a payment has many, the audit trail is their history.
  • Due partition: the unit of "what is due now", a time bucket the schedulers sweep.
  • Gateway: the external processor, reachable only through idempotency keys.
DONE WHEN: you have planted the sentence: the database IS the schedule; there is no separate queue holding truth, and everything else is an optimization over reading the table.
STEP 3 OF 5

API

3 min
// payer = authenticated actor; keys scoped (actor, key)
POST /v1/scheduled-payments
     Header: Idempotency-Key: key
     Body: { "payee": P, "amount": 2500, "dueAt": "2026-09-01T00:00:00Z" }
     → 201 { paymentId, status: PENDING }
     → 409 same key, different body
     // validated: amount > 0 and ≤ cap, dueAt in future and ≤ horizon (1y)

GET    /v1/scheduled-payments/{id}     // { status, dueAt, attempts: [...] }
GET    /v1/scheduled-payments?status=&cursor=
DELETE /v1/scheduled-payments/{id}
       → 200 CANCELLED           // won the race
       → 409 ALREADY_PROCESSING  // lost it: too late
  • Cancel is a compare-and-swap (CAS, an atomic compare-then-update) on status PENDING → CANCELLED; if execution already claimed the row, cancel honestly loses with 409 rather than pretending. The race is decided by one row's state, nowhere else (dive E).
  • All times accepted and stored in UTC; presentation converts.
STEP 4 OF 5

High-level design

10 min, end to end, no dives yet
Whiteboard version: draw order
client → API → PAYMENTS TABLE (truth = schedule), partitioned by due day, keyed (actor,key)  -- row 1: scheduling
pollers claim due rows (SKIP LOCKED + lease + attempt) → executors → gateway (key = paymentId)  -- row 2: execution
reaper: expired leases → PENDING; exhausted → DLQ; CDC → Kafka → audit + notify; daily reconciliation  -- row 3: the safety net
dp-arch
THE WALKTHROUGH, ONE BREATH PER CLAUSE

Creating a payment is one insert with a scoped idempotency key; the table, partitioned by due day and indexed by (status, due_time), is itself the schedule → dive A (why not a queue). Scheduler pollers sweep the current partition every few seconds and claim batches of due rows with SKIP LOCKED, stamping a lease and an attempt number → dive B. Executor workers call the gateway with the payment's id as the idempotency key, so any number of retries collapses into at most one charge → dive C, then record the outcome. A reaper reclaims expired leases and parks exhausted payments in a DLQ (dead letter queue, a holding pen for work that needs a human). Every status change flows via CDC to Kafka for audit and notifications, and a daily reconciliation diffs the gateway's settlement report against our attempts → dive F.

DONE WHEN: the interviewer picks a box to open. Let them steer from here.
  • If they just nod and wait: "the riskiest part is the claim query, shall I open it?"
  • Running behind: protect dives B and C. Cut G first; A can shrink to its one-sentence argument.
STEP 5 OF 5

Deep dives: the core four

~20 min, interviewer steers
TRIGGER: "how do you know exactly one worker fires a payment" / "two pollers at once"
B · The claim: exactly-once triggering
SKIP LOCKED + lease + attempt number
-- each poller, every ~5s, on the live partition:
UPDATE payments p SET
    status = 'PROCESSING',
    lease_until = now() + interval '2 min',
    attempt = attempt + 1
FROM (
  SELECT id FROM payments
  WHERE status = 'PENDING' AND due_time <= now()
  ORDER BY due_time
  LIMIT 100
  FOR UPDATE SKIP LOCKED
) due WHERE p.id = due.id
RETURNING p.*;
SKIP LOCKED is the whole trick
  • Concurrent pollers each grab a disjoint batch of due rows without blocking each other, because rows another poller has locked are skipped, not waited on.
  • N pollers scale the sweep with zero coordination service, no leader election, no distributed lock.
The lease bounds crash damage
  • A worker that dies mid-execution leaves its rows PROCESSING with an expiring lease.
  • The reaper CASes expired leases back to PENDING (attempt count intact), and the next sweep retries them.
  • A payment is never lost and never concurrently executed: at any instant exactly one holder has an unexpired lease, or the row is PENDING.
The attempt number is the fencing token
  • Recorded on every attempt row and passed through logging, so a zombie worker whose lease expired mid-call is distinguishable in the audit trail.
  • Its late outcome write CASes against the attempt it owns, failing harmlessly if the world moved on.
TRIGGER: "could the card get charged twice" / "gateway call times out"
C · The gateway call: where at-most-once lives
ONE KEY · gateway idempotency key = paymentId, not attempt id
The load-bearing choice
  • Every retry of a payment, across crashes, lease expiries, and backoff, presents the same key, so the gateway collapses them into at most one charge.
  • Keying by attempt would make every retry a fresh charge, which is the classic double-billing bug. Name it before they do.
Ambiguous outcomes are the hard case
  • A timeout after sending means the charge may or may not have happened. Never guess.
  • Keep the payment PROCESSING, and retry with the same key (safe by the above) or query the gateway's status endpoint for that key.
  • Only a definitive gateway answer moves the row to COMPLETED or a retryable FAILED.
Retry policy
  • Exponential backoff with jitter (1m, 5m, 30m, 2h, 12h), budget ~5 for retryable failures (network, 5xx, rate limit).
  • Terminal failures (card declined, account closed) go straight to FAILED + notification, no retries.
  • Exhausted budget parks in the DLQ for review.
Every attempt is a row
  • (payment_id, attempt, started_at, gateway_key, outcome, gateway_ref).
  • The audit requirement is satisfied by construction, and the daily reconciliation (dive F) diffs gateway settlement against exactly this table.
TRIGGER: "what does the schema look like" / "what breaks at midnight on the 1st"
D · Data model and the due-time spike
SCHEMA, PARTITIONING, THE MIDNIGHT PROBLEM
payments (
  id uuid pk, payer, payee
  amount_minor bigint       -- integer cents, never DECIMAL
  due_time timestamptz, due_day partition key
  status PENDING | PROCESSING | COMPLETED | FAILED | CANCELLED
  attempt int, lease_until
  idem_actor, idem_key unique (actor, key)
) PARTITION BY due_day
  INDEX (status, due_time)      -- the sweep index
  INDEX (payer, created_at)     -- user's list view

attempts ( payment_id, attempt pk, gateway_key, outcome, gateway_ref, at )
Day partitions keep the sweep index tiny
  • Pollers only touch today's partition, so the hot index covers ~100M rows, not all history; old partitions age out to cold storage after settlement.
The midnight spike
  • Humans schedule for round times, so 00:00 on the 1st can hold 100x an average second.
  • Lever 1, smear: execute due payments over a window; the contract says "at due time", delivery is due + jitter within the lateness SLO, stated honestly.
  • Lever 2, pace to the gateway's rate limit: the true bottleneck is the gateway's ceiling, not our sweep; the claim batches naturally form the pacing queue.
Why Postgres
  • The claim needs multi-row transactional SKIP LOCKED semantics, and the schedule benefits from one strongly consistent table with real indexes.
  • At 1.2K/s average this is one primary with partitioning, no exotics.
TRIGGER: "user cancels right at the due time" / "a stale worker wakes up"
E · The races: cancel, edit, zombie
ONE ROW'S STATUS DECIDES EVERYTHING
Cancel vs execute
  • Cancel is UPDATE ... SET status='CANCELLED' WHERE id=? AND status='PENDING'.
  • If the claim got there first, the row is PROCESSING, cancel matches zero rows and returns 409 ALREADY_PROCESSING.
  • If cancel got there first, the claim's inner SELECT no longer sees the row.
  • Both orders are correct because both mutations are CAS on the same status column of the same row; there is no window where both win.
Zombie worker
  • Lease expires at T, reaper flips the row to PENDING, a new claim executes with attempt N+1 and the same gateway key; the zombie finishes its stale call late.
  • The gateway key collapses the charge to one regardless, and the zombie's outcome write is fenced: UPDATE attempts SET outcome=? WHERE payment_id=? AND attempt=N touches only its own attempt row, never the payment's status, which only the current attempt's owner CASes.
Edit before due
  • Not supported by design; cancel (CAS-guarded) and re-create with a new key.
  • One less mutable path through the money state machine, said as a choice, not a gap.
STEP 5, CONT.

Three more dives

rehearse after the core four
TRIGGER: "why not a delay queue / Redis / timers"
A · Scheduling approaches, compared honestly
FOUR OPTIONS, ONE WINNER AT THIS SCALE
The table to say aloud
  • Poll the DB (chosen): indexed sweep of due rows + claim. Truth and schedule are one store; cancel is a row update; recovery is free, the row is still there.
  • Delay queues: broker holds a message until the delay expires. Caps on delay horizon, cancel means tombstone-chasing a queued message, and the queue becomes a second truth store to reconcile.
  • Redis ZSET: sorted set, score = due_time, pop ≤ now. Fine as an accelerator; wrong as truth (persistence and atomic-claim story weaker than the DB it would shadow).
  • Timing wheel: in-memory ring of buckets. The right tool inside a process (OS timers, Kafka brokers use one); wrong across processes and restarts at day-plus horizons.
The argument
  • Every alternative to polling duplicates the payments table's state into a second system, and then cancel, edit, crash recovery, and audit all become synchronization problems between the two.
  • Polling a partitioned index at a few-second interval costs almost nothing at 100M rows/day and keeps one truth.
  • The moment polling actually strains, the load is due-time spikes, which dive D solves in the key, not by changing the architecture.
TRIGGER: "gateway is down for 6 hours" / "their report disagrees with you"
F · Failure handling and reconciliation
EACH FAILURE, ITS MECHANISM
Poller or executor crash
  • Nothing is lost, the schedule is rows; leases expire, the reaper returns work, attempt counts survive.
Gateway outage
  • Circuit-break, stop claiming (leave rows PENDING rather than churning attempts).
  • On recovery drain the backlog oldest-first, paced to the gateway's rate limit.
  • Backlog size and oldest-due age are the outage dashboards.
Database failover
  • The schedule is on the primary with synchronous replication; a failover pauses execution for seconds.
  • Claims in flight either committed (lease holds) or did not (rows still PENDING); the state machine has no torn middle.
Reconciliation, both directions
  • Daily, diff the gateway's settlement report against the attempts table.
  • Charge with no COMPLETED row → our miss, repair status from gateway truth.
  • COMPLETED row with no charge → gateway's miss or our bug, page.
  • This closes the loop that per-request idempotency alone cannot: it catches the failures neither side saw.
DLQ discipline
  • Exhausted or ambiguous-terminal payments park with full attempt history; a human tool re-drives or refunds; nothing silently disappears, which is the audit requirement doing its job.
TRIGGER: "how do you operate this" / "what would you simplify"
G · Ops, SLOs, and the simpler design
OPERATE IT, AND KNOW WHAT IT BEATS
SLOs and alerts
  • Trigger lateness p99 < 60s (measured due_time → first attempt); at-most-once violation count: zero, audited by reconciliation; API availability 99.95%.
  • Alerts: oldest-due PENDING age (the single best health number: if the sweep stalls, this grows), claim throughput vs due inflow, lease-expiry rate (worker health), DLQ arrival rate, gateway error and latency, reconciliation mismatches (any nonzero pages).
Deploys
  • Pollers and executors are stateless; roll freely, leases cover the gap.
  • The table's partitions pre-create a week ahead by a boring cron that is itself monitored.
The simpler design this must beat
  • One cron job, once a minute: SELECT due rows; execute serially. Correct at small scale, and where to start.
  • It breaks on (a) execution time exceeding the interval (backlog compounds), (b) needing more than one worker (no claim semantics), (c) crash mid-batch (no leases).
  • This design is that cron job grown exactly three mechanisms: SKIP LOCKED claims, leases, and gateway idempotency keys. Nothing else was added, which is the argument that the component count is earned.
Open questions and what I would cut
  • Poll interval (5s) and lease length (2m) are guesses; derive from measured execution latency distribution and the lateness SLO.
  • The smear window for spikes is a product conversation: how late is "at midnight" allowed to be? The lever exists; the number needs an owner.
  • Would cut first: the Kafka audit fan-out. At this scale the attempts table plus the DB's own CDC to a warehouse may serve audit and notifications without a streaming tier; keep Kafka only if notification latency or consumer count demands it.
  • Recurring payments (subscriptions) are deliberately out: the clean layering is a subscription service that creates rows in this system, keeping this engine single-shot.
  • Multi-region: single region of record; scheduled work tolerates a brief regional failover better than most systems since due times are minutes-grained.
FLASHCARDS

The five hardest probes

show all (interview mode)
Two pollers sweep at the same instant. Walk the rows.
Both run the claim; the inner SELECTs lock disjoint sets because SKIP LOCKED skips rows the other holds; each poller's UPDATE stamps only its own batch. No row appears in two batches, no poller waits. The serialization point is row locks, which existed anyway.
Worker charges the card, then dies before writing COMPLETED.
Row stays PROCESSING, lease expires, reaper → PENDING, next claim retries with the same gateway key; the gateway replays its recorded success without charging again; the new worker writes COMPLETED. The charge happened once; our record caught up. If the gateway's answer were ever lost too, the daily reconciliation repairs it from settlement.
User cancels at 11:59:59.9 for a midnight payment.
Two CAS mutations race on one status column. Cancel wins → row CANCELLED, sweep never sees it. Claim wins → cancel returns 409 ALREADY_PROCESSING, truthfully. There is no interleaving where both proceed, because neither mutation is a read-then-write.
What breaks at 100x on the 1st of the month?
Not the sweep (an index range read) and not the claim (batched row locks); the gateway's rate ceiling breaks first. The claim batches become the pacing queue, lateness grows within the smear window, and the honest statement is that a due-time spike converts throughput shortage into bounded, monitored lateness, never into drops or duplicates.
Why is the queue-based design worse, really?
Because cancel, edit, and audit are row operations, and a delay queue moves the trigger into a second store that cannot CAS against the row's status: cancelling means chasing an in-flight message, and crash recovery means reconciling queue state with table state. Polling keeps every race on one row, which is why every race on this board has a two-line answer.