← all topics

Design a Delayed Payment Scheduling System

Hello Interview structure · plain notes, one idea per line · schedule now, execute later, exactly once

Understanding the Problem

💸 What are we building?

Functional Requirements

  1. Users should be able to create, query, and cancel scheduled payments.
  2. The system should execute each payment at its due time, with retries and backoff on failure.
  3. A payment must be charged at most once against the gateway, no matter what crashes.
  4. Every attempt is audited in full; nothing terminal disappears silently.

Below the line (out of scope):

Non-Functional Requirements

Do the scale math first, because it decides the architecture
  1. A payment executes at most once against the gateway no matter what crashes (the money-side requirement) and at least once gets attempted (the product-side requirement); the two are reconciled to exactly-once effect via gateway idempotency keys.
  2. Amounts are integers in minor units (cents), never floats and never DECIMAL.
  3. Trigger lateness p99 under 60 seconds, measured due_time to first attempt.
  4. API availability 99.95%.

The Set Up

Defining the Core Entities

API or System Interface

POST   /v1/scheduled-payments               // payer = authenticated actor
  Header: Idempotency-Key: key              // keys scoped (actor, key)
  { "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 the 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

High-Level Design

1) Users should be able to create, query, and cancel scheduled payments

Create and cancel a scheduled payment: one insert, one compare-and-swap The Client posts a scheduled payment with a scoped Idempotency-Key to PaymentAPI, which inserts one PENDING row into PaymentsDB; a unique index on (payer, key) dedupes retries. PaymentAPI answers 201. A cancel is a compare-and-swap from PENDING to CANCELLED on the same row; if execution already claimed it, the swap matches zero rows and the Client gets 409. Client PaymentAPI validates amount + horizonkey scoped to (payer, key) PaymentsDB (Postgres) partitioned by due daythe table IS the schedule 1. POST /v1/scheduled-paymentsIdempotency-Key, scoped 2. INSERT PENDING unique (payer, key) dedupes 3. 201; cancel too late? 409 ALREADY_PROCESSING 4. DELETE: CAS PENDING -> CANCELLED too late? CAS matches zero rows the not-so-happy paths: same key + different body -> 409; retry with same key is safe: 201 again;the cancel-vs-execute race is decided by one row's status column - both mutations are CAS, neither can half-win

2) The system should execute each payment at its due time

Execute at due time: pollers claim due rows, executors charge and record Today's partition of PaymentsDB holds PENDING rows indexed by (status, due_time); rows whose due_time is at or before now are claimable. SchedulerPollers claim due rows with FOR UPDATE SKIP LOCKED, stamping a lease and attempt+1, and hand the batch to ExecutorWorkers. ExecutorWorkers charge PaymentGateway with paymentId as the key and write the outcome back. PaymentsDB - today's partition PENDING rows, index (status, due_time)         23:50 23:55 00:00 00:05 00:10 now <- due_time <= now: claimable SchedulerPollers sweep every ~5s ExecutorWorkers call gateway, record outcome PaymentGateway external processor 1. claim due rows FOR UPDATE SKIP LOCKED, batch 100stamp lease 2min, attempt+1 4. write outcome attempt row: outcome, gateway_ref 2. hand off batch claimed rows + lease 3. charge key = paymentId the not-so-happy paths: pollers never contend - locked rows are skipped, not waited on; a dead workerleaves an expiring lease (see the retry diagram); the 00:00 pile-up is real - smear + pace (deep dive)

3) Failures retry with backoff, and exhausted payments park for a human

Crash recovery and retries: an expired lease returns the row, the same key prevents a double charge An ExecutorWorker that dies mid-execution leaves its PaymentsDB row PROCESSING with an expiring lease. The Reaper compare-and-swaps expired rows back to PENDING with the attempt count intact, and on the next sweep SchedulerPollers claim the row and hand it to a new ExecutorWorker, which charges PaymentGateway with the same key, paymentId. After about five retryable failures the Reaper parks the payment in the DLQ for a human. ExecutorWorker died mid-execution PaymentsDB stuck row: PROCESSINGlease_until expired, attempt=N Reaper sweeps expired leases ExecutorWorker (new) same payment, attempt N+1 PaymentGateway replays recorded success DLQ - parked payments payment payment payment 1. lease left to expire crash needs no cleanup 2. CAS PROCESSING -> PENDING attempt count intact, next sweep retries SchedulerPollers the next sweep 3. claim the row attempt intact; backoff isfor gateway failures only 4. hand off 5. SAME key = paymentId all retries collapseto at most one charge 6. exhausted: park after about 5retryable failures front full attempt history attached; a human re-drives or refunds terminal failures (card declined, account closed) skip retries: straight to FAILED + notification;ambiguous timeouts stay PROCESSING - never guess, re-ask the gateway with the same key

4) Every attempt is audited, and payers hear about outcomes

Audit, notifications and daily reconciliation Every status change and attempt in PaymentsDB flows out by CDC to the Kafka topic payment-events, which feeds the derived AuditLog and the NotificationService. Once a day ReconciliationJob diffs PaymentGateway's settlement report against the attempts table in both directions, and any mismatch pages. PaymentsDB every status change + attempt Kafka: payment-events event event event AuditLog (derived) every attempt, queryable NotificationService executed / failed / parked PaymentGateway daily settlement report ReconciliationJob diff report vs attempts, daily Mismatches any nonzero pages 1. CDC commit-log stream 2a. append 2b. feeds NotificationServicenotifies the payer front 4. attempts table what we think happened 3. settlement report dashed = daily batch 5. diff, both ways charge w/o COMPLETED row -> repair from gateway truthCOMPLETED row w/o charge -> page reconciliation closes the loop idempotency alone cannot: it catches the failures neither side saw;the Kafka tier is the first thing to cut: the attempts table plus the database's own CDC to a warehousemay serve audit and notifications without a streaming tier

Potential Deep Dives

1) Why poll a database instead of using a delay queue?

Bad Solution: broker delay queues as the schedule
  • The broker holds each message until its delay expires, then delivers it to a worker.
  • Brokers cap the delay horizon, and a year-out payment needs a year-out timer.
  • Cancel means tombstone-chasing a message already inside the queue, and crash recovery means reconciling queue state with table state: the queue has become a second truth store.
Good Solution: Redis ZSET or a timing wheel as an accelerator
  • Redis sorted set with score = due_time, pop everything ≤ now: fine as an accelerator; wrong as truth, because its persistence and atomic-claim story is weaker than the database it would shadow.
  • Timing wheel (an in-memory ring of time buckets): the right tool inside a process; OS timers and Kafka brokers use one. Wrong across processes and restarts at day-plus horizons.
Great Solution: the database is the schedule; poll a partitioned index
  • The argument to say out loud: every alternative to polling ends up duplicating 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: cancel is a row update, recovery is free because the row is still there.
  • If pushed on "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. Polling keeps every race on one row, which is why every race in this design has a two-line answer.
  • The moment polling actually strains, the load is due-time spikes, which deep dive 4 solves in the key, not by changing the architecture.

2) How is a due payment triggered exactly once, across concurrent pollers and crashing workers?

Bad Solution: one cron job, once a minute, execute due rows serially
  • It is correct at small scale, and it is where to start; say that.
  • It breaks on three things: execution time exceeding the interval (the backlog compounds), needing more than one worker (no claim semantics), and a crash mid-batch (no leases).
Good Solution: claim batches with FOR UPDATE SKIP LOCKED
-- 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 lock disjoint sets, because rows another poller holds are skipped, not waited on. No coordination service, no leader election.
  • If pushed on "two pollers sweep at the same instant, walk the rows": both run the claim; the inner SELECTs lock disjoint sets; 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.
Great Solution: the claim plus leases plus an attempt-number fencing token
  • The lease bounds crash damage: expired leases are CASed back to PENDING by the reaper, attempt count intact, and the next sweep retries. 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, and its late outcome write CASes against the attempt it owns, failing harmlessly if the world moved on.
  • Be precise about the claim: this machinery makes triggering effectively exactly-once. The gateway call itself is where at-most-once lives, which is the next dive.

3) How do we make sure the gateway never charges twice?

Bad Solution: gateway idempotency key per attempt
  • Keying by attempt id makes every retry a fresh charge, which is the classic double-billing bug. Name it before the interviewer does.
Good Solution: gateway idempotency key = paymentId
  • This is 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.
  • Retries follow the backoff schedule (1m, 5m, 30m, 2h, 12h with jitter, budget ~5); terminal failures go straight to FAILED plus notification, no retries.
Great Solution: the key, plus honest handling of ambiguity, plus reconciliation
  • 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.
  • If pushed on "the worker charges the card, then dies before writing COMPLETED": the row stays PROCESSING, the lease expires, the reaper flips it to PENDING, the next claim retries with the same gateway key, the gateway replays its recorded success without charging again, and 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.
  • Every attempt is a row: (payment_id, attempt, started_at, gateway_key, outcome, gateway_ref). The daily reconciliation diffs gateway settlement against exactly this table.

4) What breaks at midnight on the 1st of the month?

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 )

5) The races: cancel vs execute, zombie workers, edits

6) What happens when things die, and how do we operate it?

Final Design

Delayed payments: final architecture The Client creates or cancels through PaymentAPI, which writes one PENDING row to PaymentsDB, the table that is the schedule. SchedulerPollers claim due rows with SKIP LOCKED and a lease, hand the batch to ExecutorWorkers, which charge PaymentGateway keyed by paymentId and write the outcome back. The Reaper returns expired leases to PENDING and parks exhausted payments in the DLQ. CDC streams every change through Kafka to AuditLog and NotificationService, and a daily ReconciliationJob diffs the gateway's settlement report against the attempts table. Client PaymentAPI validate,scope (payer, key) PaymentsDB (Postgres) partitioned by due dayindex (status, due_time)the table IS the schedule - TRUTH event event AuditLog (derived) NotificationService Reaper expired leases SchedulerPollers sweep every ~5s ExecutorWorkers call, then record PaymentGateway external processor DLQ - parked payments payment payment payment ReconciliationJob daily: settlement vs attempts 1. create/cancel + Idempotency-Key 2. one insert PENDING row 9. CDC every status + attempt Kafka: payment-events front 10a. audit 10b. notify 3. claim due rows SKIP LOCKED, lease 2min,attempt+1 6. write outcome attempt row+ status CAS 7. PROCESSING -> PENDING attempt intact;dashed arrows = periodic jobs 4. batch claimed rows 5. charge key = paymentId 8. exhausted: park after about 5 retryable failures front human re-drives or refunds; nothing silently disappears 11b. attempts table what we think happened 11a. settlement report daily diff, both directions the not-so-happy paths: worker dies mid-charge -> lease expires, reaper returns the row, the retry presents the SAME gateway key, at most one charge;gateway outage -> circuit-break, rows stay PENDING, drain oldest-first paced to the gateway ceiling; DB failover -> claims committed or didn't, no torn middle;reconciliation: charge w/o COMPLETED row -> repair from gateway truth; COMPLETED row w/o charge -> page

What is Expected at Each Level