Subscriptions follow-up payments problem due to slow database / lots of metadata

[remcotolsma]

We have a customer with many payments and subscriptions where the start-up of the follow-up payments is not going well. At first, the slow server/database seemed to be causing problems, but adding a temporary index on the wp_postmeta.meta_value may have played a role as well. However, this issue may be a signal that not all WordPress installations are stable enough to initiate subscription follow-up payments. I don't think it's the first time that this doesn't run as smoothly as we'd like. The WordPress cron doesn't always seem to work well for this. The big question is how are we going to fix/improve this? Is it better to use the Mollie Subscriptions API?

Internal HelpScout ticket: https://secure.helpscout.net/conversation/1637278281/22756?folderId=1425710

[remcotolsma]

This issue is not completely new, we have the following internal Basecamp to-do open for this: "Abonnementen » Dubbele betalingen mogelijk bij dubbele cron". A possible solution is also to make more use of custom tables and to lock rows while starting up a follow-up payment.

[remcotolsma]

We wrote 2 Mollie CLI commands to help cancel payments.

wp pronamic-pay mollie payments list --api_key=live_●●●●●●●●●●●●●●●●●●●●●●●●●●●●●● --is_cancelable --format=ids`

wp pronamic-pay mollie payments cancel $( wp pronamic-pay mollie payments list --api_key=live_●●●●●●●●●●●●●●●●●●●●●●●●●●●●●● --is_cancelable --format=ids ) --api_key=live_●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

[remcotolsma]

Option 1 - Switch to Mollie Subscriptions API

Advantages Mollie Subscriptions API

• Follow-up payments no longer depend on WordPress (cron).
• Test / staging environments will no longer initiate follow-up payments.
• Subscriptions could be displayed in the Mollie dashboard or other tools.
• Other payment gateways like PayPal and Stripe work in a similar way.

Disadvantages Mollie Subscriptions API

• More difficult to pay for a period in advance?
• More difficult to manually collect a payment / period?
• ?

Are there any real drawbacks?

Other options?

• Reduce meta data?
• Improve meta query?
• Lock mechanism?

[rvdsteege]

Disadvantages Mollie Subscriptions API

• Payment method changes not possible?
  1. allow any mandate to be used instead or
  2. cancel and create new subscription.
• Pause/resume subscriptions not possible?
  1. cancel and create new subscription instead.
• Intervals can only be specified in days/weeks/months, not as advanced PHP date interval.

[remcotolsma]

Option 2 - One global daily schedule event that schedules single events for each follow-up payment

Note that scheduling an event to occur within 10 minutes of an existing event with the same action hook will be ignored unless you pass unique $args values for each scheduled event.

https://developer.wordpress.org/reference/functions/wp_schedule_single_event/

The advantage of this may also be that if a follow-up payment cannot be started at the gateway, we can reschedule the single event. I'm not exactly sure what WordPress does with events that throw an exception. It might not be watertight yet, but this option might solve some issues.

@rvdsteege You mentioned that WooCommerce also schedules events for orders, i couldn't find this: https://github.com/woocommerce/woocommerce/search?q=wp_schedule_single_event.

[rvdsteege]

You mentioned that WooCommerce also schedules events for orders, i couldn't find this.

It's here:

• https://github.com/woocommerce/woocommerce-admin/blob/6865aac06f970c5642d03be1476637e5cca26300/src/Schedulers/SchedulerTraits.php#L289-L316

[remcotolsma]

@rvdsteege made a overview of meta usage:
https://docs.google.com/spreadsheets/d/1CJHnHcr9s8lkh4Fq9HQmlAThCK69eXiTC3UktyeQKYw/edit?usp=sharing

Pronamic Pay meta

Payments

select p.ID, p.post_title, m.meta_key, m.meta_value from wp_posts as `p` left join wp_postmeta as `m` ON m.post_id = p.ID where p.post_type=‘pronamic_payment’ and m.meta_key LIKE ‘_pronamic%’ group by m.meta_key order by m.meta_key ASC

Key	In JSON	In post meta
_pronamic_payment_action_url	❌	✅
_pronamic_payment_address	✅	✅
_pronamic_payment_adyen_details_result	❌	✅
_pronamic_payment_adyen_payment_response	❌	✅
_pronamic_payment_adyen_sdk_version	❌	✅
_pronamic_payment_amount	✅	✅
_pronamic_payment_city	✅	✅
_pronamic_payment_config_id	❌	✅
_pronamic_payment_consumer_account_number	✅	✅
_pronamic_payment_consumer_bic	✅	✅
_pronamic_payment_consumer_iban	✅	✅
_pronamic_payment_consumer_name	✅	✅
_pronamic_payment_country	✅	✅
_pronamic_payment_currency	✅	✅
_pronamic_payment_customer_name	✅	✅
_pronamic_payment_description	❌	✅
_pronamic_payment_email	✅	✅
_pronamic_payment_end_date	✅ (also through periods)	✅
_pronamic_payment_entrance_code	❌	✅
_pronamic_payment_first_name	✅	✅
_pronamic_payment_issuer	❌	✅
_pronamic_payment_key	❌	✅
_pronamic_payment_language	✅	✅
_pronamic_payment_last_name	✅	✅
_pronamic_payment_locale	✅	✅
_pronamic_payment_method	❌	✅
_pronamic_payment_mollie_change_payment_state_url	❌	✅
_pronamic_payment_mollie_customer_id	❌	✅
_pronamic_payment_omnikassa_2_merchant_order_id	❌	✅
_pronamic_payment_order_id	❌	✅
_pronamic_payment_recurring	❌	✅
_pronamic_payment_recurring_type	❌	✅
_pronamic_payment_source	❌	✅
_pronamic_payment_source_id	❌	✅
_pronamic_payment_start_date	✅ (also through periods)	✅
_pronamic_payment_status	✅	✅
_pronamic_payment_subscription_id	✅ through periods	✅
_pronamic_payment_telephone_number	✅	✅
_pronamic_payment_transaction_id	✅	✅
_pronamic_payment_user_agent	✅	✅
_pronamic_payment_user_ip	✅	✅
_pronamic_payment_version	❌	✅
_pronamic_payment_woocommerce_payment_method	❌	✅
_pronamic_payment_woocommerce_payment_method_title	❌	✅
_pronamic_payment_zip	✅	✅

Subscriptions

select p.ID, p.post_title, m.meta_key, m.meta_value from wp_posts as `p` left join wp_postmeta as `m` ON m.post_id = p.ID where p.post_type=‘pronamic_pay_subscr’ and m.meta_key LIKE ‘_pronamic%’ group by m.meta_key order by m.meta_key ASC

Key	In JSON	In post meta
_pronamic_subscription_amount	✅ through fases	✅
_pronamic_subscription_config_id	❌	✅
_pronamic_subscription_currency	✅ through fases	✅
_pronamic_subscription_customer_name	✅	✅
_pronamic_subscription_description	❌	✅
_pronamic_subscription_email	✅	✅
_pronamic_subscription_end_date	✅	✅
_pronamic_subscription_expiry_date	✅	✅
_pronamic_subscription_key	❌	✅
_pronamic_subscription_mollie_customer_id	❌	✅
_pronamic_subscription_mollie_mandate_id	❌	✅
_pronamic_subscription_next_payment	✅	✅
_pronamic_subscription_next_payment_delivery_date	✅	✅
_pronamic_subscription_payment_method	❌	✅
_pronamic_subscription_source	❌	✅
_pronamic_subscription_source_id	❌	✅
_pronamic_subscription_start_date	✅ through fases	✅
_pronamic_subscription_status	✅	✅

[remcotolsma]

I think we can remove the following issues from the Subscriptions follow-up payments improvement project:

• Customer specific: delete subscriptions that are not subscriptions? #222
• Proposal database structure custom tables #234
• Introduce a $gateway->can_retry_payment( Payment $payment ) function? #243
• Make Mollie payments that don't require interaction (follow-up payments) asynchronous? #255
• More self-explaining WordPress admin subscription page #265
• Re-add CLI command to easily test subscription follow-up payments #266

[remcotolsma]

We still have a few open issues in the Subscriptions follow-up payments improvement project, but we have already implemented the solution for this issue, so I'm closing this ticket.