Search code examples
databasetransactionspaypalatomic

What are good practices to deal with PayPal API call and records in database?

I'm writing a website that let users submit credit card information and subscribe my web service. I'm dealing with database record and PayPal API, and I notice there would be some potential problem. That is, I can't rollback a PayPal API call if the following database operation fails. For example, let say, we create a recurring payment profile and write a record to database like this, in a RMDB transaction

Transaction begin
CreateRecurringPaymentsProfile (PayPal)
Insert a record to table 'subscription'
Transaction end

This works fine if nothing goes wrong, but what if our insertion of subscription record to database fails?

Transaction begin
CreateRecurringPaymentsProfile (PayPal)
Insert a record to table 'subscription' failed
Transaction rolled back

Of course we can rollback the transaction of database, but we cannot rollback the PayPal API call. Then we have an orphan recurring payment profile at PayPal. I have an idea, I can create the subscription record first, and update it later.

Transaction begin
Insert a record to table 'subscription'
Transaction end

Transaction begin
CreateRecurringPaymentsProfile (PayPal)
Update subscription record
Transaction end

In this way, if the database operation fails, at least we have the subscription record. This might be a possible solution, but I'm wondering what are good practices to deal with database operation cannot be rolled back like this? Especially when we are dealing with money.

Thanks.

Solution

  • I suggest you do this in two steps like you considered. The transaction is first create with status "Initialized" or similar. Then after the PayPal payment returns successfully you change status to "Paid".

    Even if the PayPal transaction fails, there is a benefit of having a transaction saved, so you'll know how many failed/abandoned payments you have.