Requirements

Functional Requirements:

1. Users should be able to schedule transactions - for example an automated payment every month around the 15th for a utility bill or monthly balance. This could be both single transactions and also recurring transactions or conditional transactions based on specific triggers
2. The transactions should happen on the scheduled calendar day and be posted on the account statement.
3. Users should get notifications if the transaction failed to go through for whatever particular reason - low balance, exceeding credit limit etc.
4. Users should get success notifications with a receipt number and potentially an invoice if possible.
5. The system should support transactions by card - credit/debit and by bank accounts too.
6. Users can add and store payment methods (cards, bank accounts) via tokenization; scheduling references a saved payment method.

Out of scope: international and multi currency transactions. The system operates within a single region with single currency.

Non-Functional Requirements:

1. Prioritize consistency over availability - the system potentially being unavailable for 5-10 minutes would be less of an impact than reaching out to eventual consistency leading to potential duplicate transactions.
2. PII data should be secure both at rest and in transit. - Since transactions could be scheduled/recurring - the system would likely have card/account information stored in our databases. These should be secure - through a combination of encryption/hashing etc.
3. The system should be able to scale up to a million daily active users and million daily such transactions.

Capacity Estimation

Estimate the scale of the system. Consider daily active users, read/write ratio, storage requirements, bandwidth, and any relevant QPS calculations...

API Design

1. POST - /scheduleTransaction/ -> return: boolean schedule status + transactionName
   1. request: {authorizationHeader:"", authenticationToken:"Oauth 2.0 authentication Token","scheduledTimeStamp":"","paymentOption":,"amount":,"merchantName":""}
2. PUT - /updateTransaction/ -> return boolean updateStatus
   1. request:{authorizationHeader:"", authenticationToken:"Oauth 2.0 authentication Token","scheduledTimeStamp":"","paymentOption":,"amount":,"merchantName":"",'transactionName:':""}
3. Delete - /deleteTransaction/ -> return boolean deletionStatus
   1. request:{authorizationHeader:"", authenticationToken:"Oauth 2.0 authentication Token",'transactionName:':""}
4. GET - /listTransactions/query?user_id=""&date="" (date being optional) -> return transactions
5. POST - /paymentMethods/ -> return boolean status
   1. request{authorizationHeader:"", authenticationToken:"Oauth 2.0 authentication Token",paymentAccountNumber:"", paymentAccountType:"",paymentRoutingNumber (optional):"", paymentCVVNumber:"", paymentCardType (if card payment):"", paymentCardExpDate:"",methodAddedTimeStamp:""}
6. GET - /paymentMethods/query?user_id="" -> return paymentMethods
7. DELETE -/paymentMethods/query?user_id=""&method_id=""
   1. return boolean status
8. POST - /makePayment/ return - boolean (payment status)
   1. request: {gateway_token:"",last4:"",expiry:"",paymentAmount:"",merchantName:""}
9. POST - /notifyPayment/ return: boolean - deliveryStatus
   1. request: {paymentDate:"",paymentAmount:"",paymentStatus:"","last4":,"merchantName":}

High-Level Design

1. the schedule transaction service would be used to schedule a transaction and this resides behind an API gateway that takes care of the authentication and rate limiting. Whenever a transaction is scheduled, this service takes the payment method, the payment amount and the date and updates it to the database. This would be an insert operation in to the table - the transactionSchedule table
2. The /updateTransaction service which is similar to scheduleTransaction service - takes the parameters similar to scheduleTransaction (could be optional - based on that needs to be updated) along with the transactionName that needs to be updated and runs an Update query on the database table.
3. The /deleteTransaction service deletes a transaction schedule from the transactionSchedule table changing the status to closed.
4. The /listTransaction service lists all the transactions for a given user.
5. The /paymentMethod service can be used to add a payment method for a particular user type. This payment method is what is then used in scheduling a transaction

All the above are apis which are on the user facing side of the platform. But how do the actual payments work? This potentially falls under the infrastructure side of the system.

1. To keep things simple to start off we can have a worker that uses frequent polling and polls our database for any upcoming scheduled transactions. Then it uses the makePayment service with the gateway token and the last 4 digits and calls the external vendor with the amount to make the payment.

However as you rightly point out a single worker if it crashes every scheduled transaction misses its window. So we can read the most nearest upcoming schedule from the database and feed it to a kafka stream. The makePaymentService will act as a consumer . That way the worker frequent polling is avoided. The scheduleProducer just looks at the database and picks up the next item in the schedule and feeds it to the kafka schedule stream. We can add a transactionScheduleId+current timestamp for an idempotency key and feed it downstream to the kafka schedule stream. The makePayment service just keeps reading of the kafka schedule stream. Now if for some reason the makePaymentService crashes for some reason, then it would use the offsets from kafka to pick up from where it last was processing. This also ensures exactly once processing for any scheduled transaction. The scheduleProducer at any point picks up transactions whose status are pending or notProcessed, and scheduled_timestamp < now should be picked up by the scheduleProcessor. For month end or mid month spikes for subscriptions or rents or utilities, we can scale up the scheduleProducer and the kafka schedule stream horizontally. Additionally to optimize for speed, the schedule stream could be partitioned by user_id or even types of payment - a separate partition for subscription and one separate for rent payments etc.

Database Design

user:

1. - userId
2. - userEmail
3. - userPhone
4. - user address

transactionSchedule

1. transactionScheduleId
2. transactionScheduleName
3. transactionSchedule
4. nextScheduledDate
5. merchantName
6. paymentMethodId
7. status
8. userId

transactions:

1. transactionId
2. transactionScheduleId
3. transactionDate
4. transactionAmount
5. merchantName
6. paymentMethodId
7. status
8. userId

paymentMethod:

1. - paymentMethodId
2. - gateway_token
3. - last 4
4. - expiry date
5. - paymentMethodName

Detailed Component Design

1. We do not have separate databases. Having a single database which is horizontally scaled ensures that this database acts as a source of truth. Thus we address our first non functional requirement of consistency.
2. The idempotency key of scheduleId + timestamp is enforced from the scheduleProducer through the kafka schedule stream to the makePayment service. Now any potential duplication transactions are likely to happen here - let us check here - Once a transaction happens (external vendor is called) - the status is updated back to the database as InProgress. The scheduleProducer when it picks up new scheduled transactions, checks only for pending or notProcessed and picks them up. If for some reason the makePayment service crashes, the status will still be pending in the database for a given schedule and the producer will pick it up. Now if the external vendor call goes through but a response is not received (it could be lost in return or transit) - the makePayment service updates the status as "InProgress" in the database. Now the scheduleProducer again does not pick this schedule. We can potentially use a reconciliation job between the external vendor for anything that stays InProgress.
3. A third functional requirement was keeping the card PII information secure at rest and in transit. We use the oauth2.0 framework for authentication. For the paymentMethod when we add the bank details - we do not store the entire account details in our database. This data is shared with the external vendor to generate the gateway token which resides in the database. That way the card information resides reasonably secure. However the card information in the paymentMethod needs to be hashed to ensure safety during transit. We can append the card information to the authentication token and generate hash and validate it on the service side.