Store less data in post meta

remcotolsma commented 3 years ago

@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`	:x:	:white_check_mark:
`_pronamic_payment_address`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_adyen_details_result`	:x:	:white_check_mark:
`_pronamic_payment_adyen_payment_response`	:x:	:white_check_mark:
`_pronamic_payment_adyen_sdk_version`	:x:	:white_check_mark:
`_pronamic_payment_amount`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_city`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_config_id`	:x:	:white_check_mark:
`_pronamic_payment_consumer_account_number`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_consumer_bic`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_consumer_iban`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_consumer_name`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_country`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_currency`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_customer_name`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_description`	:x:	:white_check_mark:
`_pronamic_payment_email`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_end_date`	:white_check_mark: (also through periods)	:white_check_mark:
`_pronamic_payment_entrance_code`	:x:	:white_check_mark:
`_pronamic_payment_first_name`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_issuer`	:x:	:white_check_mark:
`_pronamic_payment_key`	:x:	:white_check_mark:
`_pronamic_payment_language`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_last_name`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_locale`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_method`	:x:	:white_check_mark:
`_pronamic_payment_mollie_change_payment_state_url`	:x:	:white_check_mark:
`_pronamic_payment_mollie_customer_id`	:x:	:white_check_mark:
`_pronamic_payment_omnikassa_2_merchant_order_id`	:x:	:white_check_mark:
`_pronamic_payment_order_id`	:x:	:white_check_mark:
`_pronamic_payment_recurring`	:x:	:white_check_mark:
`_pronamic_payment_recurring_type`	:x:	:white_check_mark:
`_pronamic_payment_source`	:x:	:white_check_mark:
`_pronamic_payment_source_id`	:x:	:white_check_mark:
`_pronamic_payment_start_date`	:white_check_mark: (also through periods)	:white_check_mark:
`_pronamic_payment_status`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_subscription_id`	:white_check_mark: through periods	:white_check_mark:
`_pronamic_payment_telephone_number`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_transaction_id`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_user_agent`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_user_ip`	:white_check_mark:	:white_check_mark:
`_pronamic_payment_version`	:x:	:white_check_mark:
`_pronamic_payment_woocommerce_payment_method`	:x:	:white_check_mark:
`_pronamic_payment_woocommerce_payment_method_title`	:x:	:white_check_mark:
`_pronamic_payment_zip`	:white_check_mark:	:white_check_mark:

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`	:white_check_mark: through fases	:white_check_mark:
`_pronamic_subscription_config_id`	:x:	:white_check_mark:
`_pronamic_subscription_currency`	:white_check_mark: through fases	:white_check_mark:
`_pronamic_subscription_customer_name`	:white_check_mark:	:white_check_mark:
`_pronamic_subscription_description`	:x:	:white_check_mark:
`_pronamic_subscription_email`	:white_check_mark:	:white_check_mark:
`_pronamic_subscription_end_date`	:white_check_mark:	:white_check_mark:
`_pronamic_subscription_expiry_date`	:white_check_mark:	:white_check_mark:
`_pronamic_subscription_key`	:x:	:white_check_mark:
`_pronamic_subscription_mollie_customer_id`	:x:	:white_check_mark:
`_pronamic_subscription_mollie_mandate_id`	:x:	:white_check_mark:
`_pronamic_subscription_next_payment`	:white_check_mark:	:white_check_mark:
`_pronamic_subscription_next_payment_delivery_date`	:white_check_mark:	:white_check_mark:
`_pronamic_subscription_payment_method`	:x:	:white_check_mark:
`_pronamic_subscription_source`	:x:	:white_check_mark:
`_pronamic_subscription_source_id`	:x:	:white_check_mark:
`_pronamic_subscription_start_date`	:white_check_mark: through fases	:white_check_mark:
`_pronamic_subscription_status`	:white_check_mark:	:white_check_mark:

https://github.com/pronamic/wp-pronamic-pay/issues/212#issuecomment-931258268

rvdsteege commented 3 years ago

Should we also remove some meta?

_pronamic_payment_entrance_code → only in use by iDEAL Advanced and Sisow gateways;
_pronamic_payment_key and _pronamic_subscription_key → could maybe be stored as post_password (until custom tables arrive), as WooCommerce also seems to be doing;
do we still need _pronamic_payment_start_date and _pronamic_payment_end_date, or retrieve these from associated period?

I assume we'll keep gateway and extension specific meta in post meta? Or introduce integrations or gateway and extension properties, which can be used for these types of meta?

Note: if we remove meta, some data might not end up in privacy data export, even though it is stored in the JSON. Should add some kind of export based on the payment/subscription object, as is the case with anonymisation.

remcotolsma commented 3 years ago

I assume we'll keep gateway and extension specific meta in post meta? Or introduce integrations or gateway and extension properties, which can be used for these types of meta?

Yes, just a simple private $meta; property in Payment and Subscription with type array<string, mixed> via MetaTrait?

{
  "id":1,
  "status":"Open",
  "meta": {
    "entrance_code": "",
    "adyen_details_result": "",
    "adyen_payment_response": "",
    "adyen_sdk_version": "",
    "woocommerce_payment_method": "",
    "woocommerce_payment_method_title": "",
    "omnikassa_2_merchant_order_id": "",
    "mollie_customer_id": "",
    "mollie_change_payment_state_url": ""
}

remcotolsma commented 3 years ago

I noticed we already have a $payment->get_meta( $key ) and $payment->set_meta( $key, $value ) talking directly to get_post_meta( ... ) and update_post_meta( ... ). We could use these existing functions to read/write the meta values from the object property private $meta with a fallback from post meta.

remcotolsma commented 2 years ago

I think we've come a long way with this, I only noticed some unnecessary null meta values in a production environment:

remcotolsma commented 2 years ago

think we've come a long way with this, I only noticed some unnecessary null meta values in a production environment:

Fixed in:

pronamic / wp-pronamic-pay