Batch retrying Stripe webhooks

Question

Sometimes you have webhooks from Stripe, or other processors that use the civicrm_paymentprocessor_webhook table, that were not successfully processed and need to be re-tried. This is most commonly observed with recurring contributions and them showing in pending status in CiviCRM.

I know I can replay individual contributions from the https://example.org/wp-admin/admin.php?page=CiviCRM&q=civicrm%2Fa#/paymentprocessorWebhook but what if I want to audit and check a batch of transactions at once?

Answer 1

The webhook UI resets the processed_date and status columns in civicrm_paymentprocessor_webhook table when you click "Retry".

Here is how you can target failed transactions and replay them in batch webhook table.

Get a list of failed transactions

Get the transaction ID's from a contribution report like this and select the transaction ID column:

Download the transactions from civicrm_paymentprocessor_webhook table

You can specify the date range in question.

SELECT * FROM `civicrm_paymentprocessor_webhook` WHERE `payment_processor_id` = 1 AND `created_date` LIKE '%2023-02%' AND `trigger` = 'invoice.payment_succeeded' 

Compare to get event ID's to retry

Within the webhook table there is an identifier column. Use text to columns to split the transaction ID's on ":" into their own columns. It may have Invoice ID, Charge ID, and subscription ID.

You will want to compare on Invoice ID (what Civi currently has in transaction field). Use VLOOKUP or similar method to do this and put the event_id column to the right of the invoice column you are using as your key.

example formula: =VLOOKUP(E2,civicrm_paymentprocessor_webhoo!I:J,2,FALSE)

Now that you've returned the event id's, combine them and format to be put added to a MySQL statement. Your result will looks like: (using dummy event ID's)

'evt_1','evt_2','evt_3','evt_4','evt_5'

Reset and Retry event ID's in MySQL

Using dummy data below, event ID's are normally like evt_3MXs5MGUXvR5yNII0RwNHBiM.

Now drop the event id's into a MySQL statement like below:

UPDATE `civicrm_paymentprocessor_webhook` SET
`processed_date` = NULL,
`status` = 'new'
WHERE `payment_processor_id` = '1' AND `created_date` LIKE '%2023-02%' AND `trigger` = 'invoice.payment_succeeded' AND `event_id` IN ('evt_1','evt_2','evt_3','evt_4','evt_5');

The Process PaymentProcessor Webhooks scheduled job will now look thru these entries that were reset and retry them. You can let the job run or manually kick off by "executing now."

While the root cause of why transactions do not process on the first run should be investigated, sometimes you just need to target and retry transaction quickly. This process takes about 20 minutes to do and is much faster and if you have a lot of transactions that need re-processing (I've had cases where I needed to do over 100).