Orphaned Records when Payment Declined

[dw-lc]

Prerequisites

• [X] Put an X between the brackets on this line if you have done all of the following:
  • Did you perform a search at https://github.com/issues?q=is%3Aissue+user%3ASparkDevNetwork+-repo%3ARock to see if your bug or enhancement is already reported?
  • Can you reproduce the problem on a fresh install or the demo site?
  • Did you include your Rock version number and client culture setting?

Description

When a scheduled transaction has a saved payment method that gets declined at the gateway, an extra [FinancialPaymentDetail] record gets added to the database. No other records link to this extra record, so it’s not needed. Since it doesn’t even have all of the same data as the correct records, it’s bad data getting added to the database.

Steps to Reproduce

1. Go to a person’s profile > Contributions Tab.
2. Add a new recurring scheduled transaction with a start date in the future, and provide a credit card number.
3. Once you see the Transaction Id and the option to save the payment method, you can pause and see some of the records we note below.
4. Once you have the confirmation of the records that you need, check the box to save the payment method and give it a name, then click “Save Account”.
5. You can now query the database to see additional records shown below.
6. If you can test with a credit card that will get rejected, then the bug will occur when the transaction attempts to process. Otherwise you may need to use SQL to update the saved card to have a past expiration date. Then when the transaction attempts to process (in testing it’s easiest to use the API) the issue will occur. In a production instance, examples of this issue will naturally happen over time as cards naturally expire and have their transactions declined.

Expected Behavior:

I don’t expect any new records in the FinancialPaymentDetail table when a transaction is declined.

Actual Behavior:

When a transaction is declined, a new FinancialPaymentDetail record is created. However, no records in the FinancialTransaction, FinancialScheduledTransaction, or FinancialPersonSavedAccount tables are linked to this new record, so it’s apparently orphaned/unused. Additionally, it doesn’t have all of the same property values as the original records which are still being used properly.

Extra SQL Record documentation

When a giver sets up a scheduled transaction using a credit card, the Scheduled Transaction record references a FinancialPaymentDetail record by Id:

During this process, the giver has the opportunity to save the payment method:

If they do so, two additional records are created:

1. An additional [FinancialPaymentDetail] record is created for the Saved Account.

2. The [FinancialPersonSavedAccount] record is added and references the above FinancialPaymentDetail record.

(Additionally, the first FinancalPaymentDetail record is updated to store this record’s Id in [FinancialPersonSavedAccountId])

The bug occurs when the transaction is declined, so unless you can test with a one-time use card or something, we need to facilitate a rejection. Use SQL to update the expiration dates to a date in the past.

Now when the transaction is attempted to be processed and gets rejected, you’ll see that a new [FinancialPaymentDetail] record is created. (we used https://rockrmslatest.com/api/FinancialScheduledTransactions/Process/2?enableDuplicateChecking=false&enableScheduleAdherenceProtection=false&idempotencyKey=test1 )

Versions

• Rock Version: v9.2 through PreAlpha 1.15.0.1 at least.
• Client Culture Setting: EN-US