System Design: Banking Payment System Enhancements

Context

You are building a minimal bank-transfer payment orchestration service (e.g., ACH/SEPA) that schedules and executes outgoing payments. The system persists all state and exposes a simple HTTP API. Payments move through a state machine (e.g., SCHEDULED → QUEUED → PROCESSING → SENT → SETTLED/FAILED), and are executed by background workers.

Requirements

Design and implement the following features:

1. cancelPayment
   • Add an operation to cancel a previously scheduled payment.
   • Only payments that haven't been irreversibly sent downstream can be canceled.
   • Define idempotent behavior, allowed states for cancellation, and race-condition handling.
2. Extend schedulePayment
   • Ensure schedulePayment reports and persists both success and failure results (for both the scheduling action itself and the downstream execution attempts).
   • Include idempotency for client retries and durable audit logs for all results.
3. Remove obsolete payment cases
   • Identify and remove any legacy/obsolete payment states or code paths.
   • Propose a safe data and code migration plan so production data remains consistent.
4. Reporting
   • Produce a report of outgoing transactions sorted by total loss amount.
   • Define "loss" precisely, including returns/chargebacks and fees, and how recoveries/refunds affect it.
   • Provide a query or computation plan that aggregates and sorts by loss.

Deliverables

• API design for schedulePayment and cancelPayment (request/response, status codes, idempotency semantics).
• Payment state machine and concurrency rules.
• Data model (tables/entities) for payments, attempts, events/audit, fees/returns.
• Reporting definition with a query that produces the loss-sorted report.
• Edge cases and validation strategy.