Wed Jul 19
I ran into an issue where I needed to process a number of Stripe webhooks stored in a webhook_calls table.
To mark progress, I thought it would be useful to join the invoices table but I ran into a snag.
In the webhook payload, the JSON has double quotes so an invoice shows as something like "in_" and in the invoices table it isn't quoted as the varchar covers it.
I looked into the QUOTE() function first but it turns out TRIM('"' FROM) is what I was after. This trims the leading and ending quote characters to match the invoices datatype.
An example of the SQL statement used:
SELECT
webhook_calls.id,
payload_type,
JSON_EXTRACT(webhook_calls.payload, '$.data.object.id') AS `type_id`,
JSON_EXTRACT(webhook_calls.payload, '$.data.object.object') AS `type`,
JSON_EXTRACT(webhook_calls.payload, '$.data.object.lines.data[0].metadata.user_id') AS `user_id`,
JSON_EXTRACT(webhook_calls.payload, '$.data.object.lines.data[0].plan.nickname') AS `nickname`,
JSON_EXTRACT(webhook_calls.payload, '$.data.object.lines.data[0].plan.metadata.base_sku') AS `base_sku`,
webhook_calls.created_at
FROM
webhook_calls
LEFT JOIN
invoices ON invoices.invoice_id = TRIM('"' FROM JSON_EXTRACT(webhook_calls.payload, '$.data.object.id'))
WHERE
MONTH(webhook_calls.created_at) = 7
AND YEAR(webhook_calls.created_at) = 2023
AND JSON_EXTRACT(webhook_calls.payload, '$.data.object.lines.data[0].plan.metadata.base_sku') IS NOT NULL
AND invoices.invoice_id IS NULL
ORDER BY
JSON_EXTRACT(webhook_calls.payload, '$.data.object.lines.data[0].metadata.user_id')
DESC,
payload_type ASCThis gets the webhook_calls records that have been created July 2023 with a base_sku not null as this processor ignores any products that aren't explicitly configured. This paired down thousands of events into a manageable number.
We process only invoice events so this was enough to create a list where I could manually match up the invoice.created to its pair invoice.payment_succeeded event.
I thought about automating this but it wasn't that many records to deal with and I wasn't sure what would happen if this was outside of artisan tinker.
I needed the jobs to be synchronous to make sure the invoices record was created first, followed by the subscriptions that gets created from the second event.
If I were to automate this I would sort by created_at, find the first event, dispatch, then find the second and sleep for a second before dispatching again. As a sanity check I could check that the invoices was created and subscriptions after that.
I'm not the biggest fan of MariaDB as I really wish it had MySQL's -> and ->> shorthand for the JSON functions but this SQL is compatible with both systems.