Journal Entry, July 14th to July 21st 2023

July 21, 2023 · 2 min read


  1. 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:

 JSON_EXTRACT(webhook_calls.payload, '$') AS `type_id`,
 JSON_EXTRACT(webhook_calls.payload, '$.data.object.object') AS `type`,
 JSON_EXTRACT(webhook_calls.payload, '$[0].metadata.user_id') AS `user_id`,
 JSON_EXTRACT(webhook_calls.payload, '$[0].plan.nickname') AS `nickname`,
 JSON_EXTRACT(webhook_calls.payload, '$[0].plan.metadata.base_sku') AS `base_sku`,
 invoices ON invoices.invoice_id = TRIM('"' FROM JSON_EXTRACT(webhook_calls.payload, '$'))
 MONTH(webhook_calls.created_at) = 7
 AND YEAR(webhook_calls.created_at) = 2023
 AND JSON_EXTRACT(webhook_calls.payload, '$[0].plan.metadata.base_sku') IS NOT NULL
 AND invoices.invoice_id IS NULL
 JSON_EXTRACT(webhook_calls.payload, '$[0].metadata.user_id')
 payload_type ASC

This 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.