WooCommerce B2B VAT incident: the midnight SQL fix

It was 22:47 on a Tuesday. The phone buzzed with a Slack alert from a client's bookkeeper: "VAT on order 18432 is wrong. And 18433. And the next eight." Their store is a small Dutch B2B parts supplier, around €4M in annual sales, almost entirely to other VAT-registered EU companies. Net 21% VAT on a reverse-charge invoice means the buyer pays the supplier, then has to chase a refund through the wrong channels for months. Not a bug you let sit until morning.

We had pushed nothing that day. The client had pushed nothing. The only event in the WordPress activity log was a 17:30 automatic update of an EU VAT plugin. Four hours later we were on the phone, in a staging clone, and on a MySQL prompt. This is what happened, what we ran, and what we wish we'd had in place beforehand.

The setup the incident hit

The store runs WooCommerce 8.x on PHP 8.2 with MariaDB 10.6. Order storage is still legacy posts and postmeta, not HPOS, because three of their plugins haven't shipped HPOS compatibility yet. B2B handling is split across two extensions: one that adds a VAT number field to checkout and validates it against the EU VIES service, and one that flips _is_vat_exempt = 'yes' on the order when a valid number is present. WooCommerce's own tax engine sees the exemption flag and skips tax calculation. Standard reverse-charge setup.

That second plugin had auto-updated at 17:30. The release notes mentioned "improved validation timing." What had actually changed: validation now ran on woocommerce_checkout_create_order instead of woocommerce_checkout_update_order_meta. The new hook fires before the tax totals are calculated, so when the plugin set the exemption flag, the cart had already finalised tax at 21%. The flag was set on the order, but the line totals already had VAT baked in.

An auto-updating tax-touching plugin is a production change you didn't authorise. WooCommerce has a setting to disable plugin auto-updates store-wide. Use it.

What the orders looked like

From 17:30 to 22:47 we had eleven B2B orders for a combined €38,210. Every one of them had a valid VAT number, the _is_vat_exempt flag correctly set to yes, and a tax total that should have been €0,00. Instead every order had €6,632.43 of VAT spread across line items. The customer-facing invoice PDF was already auto-emailed to nine of the eleven buyers.

First job, before any writes: confirm the blast radius. The query we ran against the read replica:

SELECT p.ID                                       AS order_id,
       p.post_date,
       pm_email.meta_value                        AS billing_email,
       pm_vat.meta_value                          AS vat_number,
       pm_exempt.meta_value                       AS is_vat_exempt,
       CAST(pm_tax.meta_value AS DECIMAL(12,2))   AS order_tax,
       CAST(pm_total.meta_value AS DECIMAL(12,2)) AS order_total
FROM wp_posts p
JOIN wp_postmeta pm_email  ON pm_email.post_id  = p.ID AND pm_email.meta_key  = '_billing_email'
JOIN wp_postmeta pm_vat    ON pm_vat.post_id    = p.ID AND pm_vat.meta_key    = '_billing_vat_number'
JOIN wp_postmeta pm_exempt ON pm_exempt.post_id = p.ID AND pm_exempt.meta_key = '_is_vat_exempt'
JOIN wp_postmeta pm_tax    ON pm_tax.post_id    = p.ID AND pm_tax.meta_key    = '_order_tax'
JOIN wp_postmeta pm_total  ON pm_total.post_id  = p.ID AND pm_total.meta_key  = '_order_total'
WHERE p.post_type = 'shop_order'
  AND p.post_date >= '2026-06-02 17:30:00'
  AND pm_exempt.meta_value = 'yes'
  AND pm_vat.meta_value <> ''
  AND CAST(pm_tax.meta_value AS DECIMAL(12,2)) > 0
ORDER BY p.post_date;

Eleven rows. Two of them were still in wc-pending because the buyer hadn't completed the iDEAL redirect. Those we could leave alone, they would either complete fresh or drop into trash overnight. Nine were paid and not yet shipped, because the warehouse closes at 18:00. That mattered. It meant we could correct the invoices before any goods physically moved.

Why we did not roll the plugin back first

The instinct on any incident like this is to undo the change. We resisted, for one reason: rolling the plugin back would not retroactively fix the eleven existing orders. Their tax was already serialised into wp_woocommerce_order_itemmeta. A rollback fixes order twelve. It does nothing for orders one through eleven, and it makes the database harder to reason about because some rows now reflect plugin version A and some plugin version B. Fix the data first, on the version that produced it, then roll back.

The fix is not just zero the tax

WooCommerce doesn't store a single tax number per order. It stores tax at four layers: the per-line tax on each wp_woocommerce_order_itemmeta row, the per-line shipping tax, a serialised _line_tax_data array that breaks tax down by rate ID, and the totals stored as _order_tax, _order_shipping_tax, and _order_total in postmeta. If you zero the order total but leave the line-item meta intact, the next time someone refunds, edits, or even just opens the order in admin, WooCommerce recalculates from the line items and overwrites your fix.

So the procedure is: zero the tax at the leaves first, then update the trunk. And do not touch _order_total until you've corrected every line.

We dumped the eleven affected order IDs into a temporary table to keep every subsequent statement scoped to exactly those rows. This is the single most important habit when running surgery on a live database. Never write a WHERE clause more than once when one table can hold the source of truth.

CREATE TEMPORARY TABLE _fix_orders (order_id BIGINT PRIMARY KEY);

INSERT INTO _fix_orders (order_id)
SELECT p.ID
FROM wp_posts p
JOIN wp_postmeta pm_exempt ON pm_exempt.post_id = p.ID AND pm_exempt.meta_key = '_is_vat_exempt'
JOIN wp_postmeta pm_vat    ON pm_vat.post_id    = p.ID AND pm_vat.meta_key    = '_billing_vat_number'
JOIN wp_postmeta pm_tax    ON pm_tax.post_id    = p.ID AND pm_tax.meta_key    = '_order_tax'
WHERE p.post_type = 'shop_order'
  AND p.post_status IN ('wc-processing', 'wc-on-hold')
  AND p.post_date >= '2026-06-02 17:30:00'
  AND pm_exempt.meta_value = 'yes'
  AND pm_vat.meta_value <> ''
  AND CAST(pm_tax.meta_value AS DECIMAL(12,2)) > 0;

Then a backup. Always. Before any UPDATE on a production table.

mysqldump --single-transaction --quick \
  --where="post_id IN (SELECT order_id FROM _fix_orders)" \
  shop_prod wp_postmeta > /backups/postmeta-2026-06-02-2305.sql

mysqldump --single-transaction --quick \
  --where="order_item_id IN (SELECT order_item_id FROM wp_woocommerce_order_items
                              WHERE order_id IN (SELECT order_id FROM _fix_orders))" \
  shop_prod wp_woocommerce_order_itemmeta > /backups/order_itemmeta-2026-06-02-2305.sql

With backups on disk and the order set frozen, the correction is four updates inside a transaction.

START TRANSACTION;

-- 1. Zero per-line tax on each order item.
UPDATE wp_woocommerce_order_itemmeta oim
JOIN wp_woocommerce_order_items oi ON oi.order_item_id = oim.order_item_id
SET oim.meta_value = '0'
WHERE oi.order_id IN (SELECT order_id FROM _fix_orders)
  AND oim.meta_key IN ('_line_tax', '_line_subtotal_tax');

-- 2. Empty the serialised tax-data array.
UPDATE wp_woocommerce_order_itemmeta oim
JOIN wp_woocommerce_order_items oi ON oi.order_item_id = oim.order_item_id
SET oim.meta_value = 'a:2:{s:5:"total";a:0:{}s:8:"subtotal";a:0:{}}'
WHERE oi.order_id IN (SELECT order_id FROM _fix_orders)
  AND oim.meta_key = '_line_tax_data';

-- 3. New order total = old total minus old tax.
UPDATE wp_postmeta pm_total
JOIN wp_postmeta pm_tax
  ON pm_tax.post_id = pm_total.post_id AND pm_tax.meta_key = '_order_tax'
SET pm_total.meta_value =
      CAST(pm_total.meta_value AS DECIMAL(12,2))
    - CAST(pm_tax.meta_value   AS DECIMAL(12,2))
WHERE pm_total.post_id IN (SELECT order_id FROM _fix_orders)
  AND pm_total.meta_key = '_order_total';

-- 4. Zero every tax bucket on the order.
UPDATE wp_postmeta
SET meta_value = '0'
WHERE post_id IN (SELECT order_id FROM _fix_orders)
  AND meta_key IN ('_order_tax', '_order_shipping_tax', '_cart_discount_tax');

-- Spot check before commit.
SELECT pm_total.post_id,
       CAST(pm_total.meta_value AS DECIMAL(12,2)) AS new_total,
       CAST(pm_tax.meta_value   AS DECIMAL(12,2)) AS new_tax
FROM wp_postmeta pm_total
JOIN wp_postmeta pm_tax ON pm_tax.post_id = pm_total.post_id AND pm_tax.meta_key = '_order_tax'
WHERE pm_total.post_id IN (SELECT order_id FROM _fix_orders)
  AND pm_total.meta_key = '_order_total';

COMMIT;

Nine new totals, nine zero tax values, every one matching the manual recalculation we'd done in a spreadsheet five minutes earlier. We committed at 23:38.

The bookkeeping cleanup

Correcting the database is half the work. The other half is paperwork. Nine invoice PDFs had already left the building. The invoice plugin caches generated PDFs in a private uploads folder, so we deleted those, bumped the invoice number suffix from -A to -B on those nine orders, and ran the generator again. The plugin stores its sequence in an option row, not in postmeta, which is the kind of detail you only learn by reading the plugin's source at 23:50.

Then the email. We wrote a single short message in Dutch and English, attached the corrected PDF, explained that the first PDF was incorrect and to discard it, and sent it at 00:14 from the bookkeeper's mailbox rather than a no-reply. Nobody trusts a no-reply when money is involved. Two clients had already paid the wrong total via SEPA. Those went onto the next-day refund list, around €1,400 each.

Finally we rolled the plugin back. WooCommerce's order storage documentation is clear that order metadata writes during checkout must finish before tax calculation runs, which is exactly the contract the plugin update had broken. We pinned the previous version in composer.json and disabled plugin auto-updates in wp-config.php:

define('WP_AUTO_UPDATE_CORE', 'minor');
add_filter('auto_update_plugin', '__return_false');

What we'd have caught in five minutes with the right alert

The fault was live for five hours and twelve minutes. Eleven orders. Nine that needed correcting. We caught it because a bookkeeper happened to check that evening. If she'd looked the next morning, the warehouse would have shipped to nine companies on the wrong invoice, and at least two of those would have been impossible to fully unwind without a credit note.

The detection cost would have been one cron-driven SQL alert. Something close to the query at the top of this post, run every fifteen minutes against the read replica, paged to Slack the moment it returns a non-zero row count. Total build time, maybe ninety minutes. It would have caught the regression inside the first window.

The legal weight on getting this right sits in Article 196 of Directive 2006/112/EC, the source the EU reverse-charge mechanism rests on. It does not care that your plugin updated. It cares that the invoice is correct on the day of the supply.

The prevention checklist we left behind

The day after, we wrote the client a short standing playbook. It is six items.

1. Disable plugin and theme auto-updates on production. Apply updates manually on staging first, with a smoke order through checkout.
2. Run a fifteen-minute SQL alert that counts B2B orders with a VAT number and non-zero tax. Page on count greater than zero.
3. Run a daily diff of wp_options rows whose option_name matches %tax% or %vat%. Plugin updates often write new option keys you didn't approve.
4. Pin every payment, tax, and order-related plugin to an exact version in composer.json. No version ranges for these.
5. Back up wp_postmeta and wp_woocommerce_order_itemmeta before any UPDATE, every time. Use a temporary table to scope writes.
6. Keep one PHP script in the repo that recalculates an order's tax from line items and writes it back, so the next incident is a one-liner, not an hour of SQL.

This client now has all six. The cron alert has fired twice in the four months since, both times catching genuine misconfigurations within the fifteen-minute window. The cost of building it was three hours. The cost of the incident, including our time at midnight and the two SEPA refunds, was closer to twelve.

The work, not the pitch

When we built the order-monitor for this client's store, the thing that surprised us was how much of the WooCommerce-broke category turns out to be plugin contracts breaking against each other on update, not WooCommerce itself. Our standing checklist for any WordPress and WooCommerce rescue now starts with pinned versions and a SQL canary, before anything more ambitious. If you run a B2B store on WooCommerce and your VAT relies on three plugins agreeing with each other, run the query at the top of this post against your own database tonight. If it returns rows, you have homework before morning.