Repair WooCommerce core: products and catalog
Product lookup table out of sync
A shop manager changes a price, saves it, and the storefront still shows the old number. Or a product says in stock everywhere except the low stock report, which insists it sold out days ago. Nothing in the product editor looks wrong. The real data is fine. The problem is a second copy of that data, a lookup table WooCommerce keeps for fast filtering and sorting, and that copy has drifted from the truth. Here is why it happens and a small script that finds the rows that are wrong and rebuilds them.
WooCommerce copies each product's price, stock, and a few other fields into a fast lookup table called wp_wc_product_meta_lookup. That copy only refreshes when normal WooCommerce code saves the product. A direct database edit, a raw SQL import, or a plugin that writes post meta straight to the database can change the real product without ever touching the lookup row, so storefront filters, sorting, and reports quietly show stale numbers. Run a small Python or Node.js script on a schedule that reads each product through the REST API, cross-checks its price and stock against what recent paid orders actually charged using Stripe as the source of truth for the charged amount, and resaves any product whose lookup data looks stale so WooCommerce rebuilds that row itself. Full code, tests, and a dry run guard are below.
The problem in plain words
A WooCommerce product's real data lives as post meta on the product post: _price, _regular_price, _stock, _stock_status, and more. Reading post meta for every product on every storefront page load would be slow, so WooCommerce also keeps a plain, flat table with one row per product, wp_wc_product_meta_lookup, built for fast sorting and filtering. Price range widgets, stock filters, and the "sort by price" dropdown all read from this table instead of the slower post meta.
The lookup row is meant to be a mirror, never the source of truth. WooCommerce keeps it current by refreshing the row every time a product is saved through its own code, whether that is the admin editor, the REST API, or a scheduled stock update. The trouble starts when something changes the real product data through a path that skips that refresh: a direct UPDATE statement from a database tool, a bulk import plugin that writes rows straight into postmeta, or a staging-to-live migration that copies the posts and postmeta tables but not the lookup table. The product itself is now correct. The mirror is not, and nothing tells you.
Why it happens
The WooCommerce developer docs describe wp_wc_product_meta_lookup as a denormalized table maintained for query performance, refreshed by WooCommerce's own product save and stock update hooks. A few common ways it falls behind:
- A database tool or a support script runs an
UPDATEdirectly againstwp_postmetato fix a price or stock number in bulk, which changes the real data but never fires the WooCommerce hook that refreshes the lookup row. - A CSV import plugin writes product meta straight into the database for speed on large catalogs, skipping the normal save path.
- A staging site is cloned to production by copying specific tables, and the lookup table is missed or copied from an older snapshot than the postmeta tables.
- A caching or object cache plugin serves a stale lookup read after a bulk stock update job partially fails partway through.
Because the lookup table backs sorting and filtering rather than the product page itself, this can run for weeks before anyone notices, usually when a customer complains that a product they bought at one price shows a different price in a filtered category view, or a manager cannot find a product in a "low stock" report that everyone knows is nearly sold out.
The lookup table is a cache, not a record. WooCommerce's own product save code is the only thing that should ever repair it, since it recalculates every column the same way a normal edit would. A script should never write directly into wp_wc_product_meta_lookup. It should find the products whose cached row looks wrong and ask WooCommerce, through the REST API, to resave them.
The fix, as a flow
We do not touch the lookup table directly and we do not touch checkout. We add a job that runs on a schedule, reads each product's current price and stock through the REST API, and compares that against what recent paid orders for that product actually charged, using the Stripe PaymentIntent amount as the ground truth for what money actually moved. When a product shows a steady mismatch across more than one recent order, that is a sign of a stale lookup row rather than a one-off coupon, so we resave the product through the REST API, which makes WooCommerce rebuild the lookup row itself.
Build it step by step
Get access to both systems
You need a WooCommerce REST API key pair (a consumer key and a consumer secret) with read and write access to products and orders, and a Stripe secret key with read access. Create the WooCommerce key under WooCommerce, Settings, Advanced, REST API. Keep every value in environment variables, never in the file.
pip install stripe requests
export STRIPE_SECRET_KEY="sk_live_..."
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export LOOKBACK_DAYS="14"
export MIN_MISMATCHED_ORDERS="2"
export DRY_RUN="true" # start safe, change to false to writenpm install stripe
export STRIPE_SECRET_KEY="sk_live_..."
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export LOOKBACK_DAYS="14"
export MIN_MISMATCHED_ORDERS="2"
export DRY_RUN="true" // start safe, change to false to writeList recent paid orders and their line items
Pull orders from the last lookback window that are Processing or Completed. For each order we only need the product ids in its line items, the per-item price the order recorded at checkout, and the PaymentIntent id saved in order meta so we can confirm the amount against Stripe rather than trust the order row alone.
import os, requests
from requests.auth import HTTPBasicAuth
WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])
def intent_id_of(order):
for meta in order.get("meta_data") or []:
if meta.get("key") == "_stripe_intent_id" and meta.get("value"):
return meta["value"]
tid = order.get("transaction_id")
return tid if tid and tid.startswith("pi_") else None
def recent_paid_orders(lookback_days):
after = f"{__import__('datetime').date.today() - __import__('datetime').timedelta(days=lookback_days)}T00:00:00"
page = 1
while True:
r = requests.get(
f"{WOO_URL}/wp-json/wc/v3/orders",
params={"status": "processing,completed", "after": after, "per_page": 50, "page": page},
auth=AUTH, timeout=30,
)
r.raise_for_status()
batch = r.json()
if not batch:
return
yield from batch
page += 1const WOO_URL = process.env.WOO_STORE_URL.replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
`${process.env.WOO_CONSUMER_KEY}:${process.env.WOO_CONSUMER_SECRET}`
).toString("base64");
async function woo(path, options = {}) {
const res = await fetch(`${WOO_URL}/wp-json/wc/v3${path}`, {
...options,
headers: { "Content-Type": "application/json", Authorization: AUTH, ...(options.headers || {}) },
});
if (!res.ok) throw new Error(`Woo ${path} returned ${res.status}`);
return res.json();
}
function intentIdOf(order) {
for (const meta of order.meta_data || []) {
if (meta.key === "_stripe_intent_id" && meta.value) return meta.value;
}
const tid = order.transaction_id;
return tid && tid.startsWith("pi_") ? tid : null;
}
async function* recentPaidOrders(lookbackDays) {
const after = new Date(Date.now() - lookbackDays * 86400000).toISOString();
let page = 1;
while (true) {
const batch = await woo(`/orders?status=processing,completed&after=${after}&per_page=50&page=${page}`);
if (!batch.length) return;
for (const order of batch) yield order;
page++;
}
}Confirm the charged amount against Stripe
An order row can itself be edited by hand, so do not trust order.total alone. Retrieve the PaymentIntent and use amount_received, in cents, as the real amount the customer paid. This mirrors how a reconciler treats Stripe as the source of truth for money.
import stripe
stripe.api_key = os.environ["STRIPE_SECRET_KEY"]
def get_intent(intent_id):
if not intent_id:
return None
try:
return stripe.PaymentIntent.retrieve(intent_id)
except stripe.error.InvalidRequestError:
return Noneimport Stripe from "stripe";
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);
async function getIntent(intentId) {
if (!intentId) return null;
try {
return await stripe.paymentIntents.retrieve(intentId);
} catch {
return null;
}
}Decide, with one pure function
Keep the decision in its own function that takes a product's current lookup data (price and stock, as WooCommerce reports them right now) plus a small list of recent per-order facts for that product, that is, the line item's unit price at checkout and whether the matching Stripe amount matches it, and returns an action. One mismatched order is often just a coupon or a bundle discount. A steady mismatch across several orders, with none of them explained by a discount, is the signature of a stale lookup row.
def product_price_minor(product):
# Works for two decimal currencies. Zero decimal currencies (JPY and friends)
# have their own guide, since 50.00 is wrong for those.
return round(float(product["price"]) * 100)
def decide(product, order_facts, min_mismatched_orders=2):
"""order_facts: list of dicts like
{"order_total_minor": 4500, "stripe_amount_minor": 4500, "discounted": False}
for recent paid orders that contained this product.
"""
if not product.get("purchasable", True):
return ("skip", "product is not purchasable")
if len(order_facts) == 0:
return ("skip", "no recent paid orders to compare against")
current_price = product_price_minor(product)
mismatched = [
f for f in order_facts
if not f["discounted"] and abs(f["order_total_minor"] - current_price) > 1
and abs(f["order_total_minor"] - f["stripe_amount_minor"]) <= 1
]
if len(mismatched) >= min_mismatched_orders:
return ("resave", "lookup price looks stale against confirmed Stripe charges")
if product.get("stock_status") == "instock" and product.get("stock_quantity") == 0:
return ("resave", "lookup shows in stock with zero quantity")
return ("ok", "lookup data matches recent activity")export function productPriceMinor(product) {
// Works for two decimal currencies. Zero decimal currencies (JPY and friends)
// have their own guide, since 50.00 is wrong for those.
return Math.round(parseFloat(product.price) * 100);
}
export function decide(product, orderFacts, minMismatchedOrders = 2) {
// orderFacts: [{ orderTotalMinor, stripeAmountMinor, discounted }] for recent
// paid orders that contained this product.
if (product.purchasable === false) return ["skip", "product is not purchasable"];
if (orderFacts.length === 0) return ["skip", "no recent paid orders to compare against"];
const currentPrice = productPriceMinor(product);
const mismatched = orderFacts.filter(
(f) =>
!f.discounted &&
Math.abs(f.orderTotalMinor - currentPrice) > 1 &&
Math.abs(f.orderTotalMinor - f.stripeAmountMinor) <= 1
);
if (mismatched.length >= minMismatchedOrders) {
return ["resave", "lookup price looks stale against confirmed Stripe charges"];
}
if (product.stock_status === "instock" && product.stock_quantity === 0) {
return ["resave", "lookup shows in stock with zero quantity"];
}
return ["ok", "lookup data matches recent activity"];
}Repair it by resaving the product, never by writing SQL
When the action is resave, send a REST API update for that product using its own current values. Even a no-op update, sending the same price back to itself, runs WooCommerce's full save path and rebuilds the lookup row. Then add an order-independent log entry so a shop manager can see which products were touched and why.
def resave_product(product):
# Sending the product's own current price and stock back through the
# REST API forces WooCommerce to run its normal save path, which
# rebuilds the wp_wc_product_meta_lookup row for this product.
requests.put(
f"{WOO_URL}/wp-json/wc/v3/products/{product['id']}",
json={
"regular_price": product.get("regular_price", product["price"]),
"stock_quantity": product.get("stock_quantity"),
},
auth=AUTH, timeout=30,
).raise_for_status()async function resaveProduct(product) {
// Sending the product's own current price and stock back through the
// REST API forces WooCommerce to run its normal save path, which
// rebuilds the wp_wc_product_meta_lookup row for this product.
await woo(`/products/${product.id}`, {
method: "PUT",
body: JSON.stringify({
regular_price: product.regular_price || product.price,
stock_quantity: product.stock_quantity,
}),
});
}Wire it together with a dry run guard
The loop groups recent order facts by product, runs the decision for each product WooCommerce reports, and logs what it finds. Leave DRY_RUN on for the first few runs so the script only reports which products it would resave. Read the output, trust it, then switch it off. Run it once a day with cron, since lookup drift is rarely urgent.
Always start with DRY_RUN=true. This script writes to real products, so you want to see its plan before it acts. Never point it at wp_wc_product_meta_lookup directly. Let WooCommerce's own save path rebuild the row.
The full code
Here is the complete script in one file for each language. It reads settings from the environment, logs what it does, respects the dry run flag, and is safe to run again and again because it only resaves a product when the evidence points at a stale lookup row.
View this code on GitHub Full runnable folder with tests in the woocommerce-fixes repo.
"""Find WooCommerce products whose wp_wc_product_meta_lookup row has drifted
from the real product data, and repair them by resaving through the REST API.
Never writes to wp_wc_product_meta_lookup directly. Resaving a product runs
WooCommerce's own save path, which is what rebuilds that row. Run on a
schedule. Safe to run again and again.
"""
import os
import datetime
import logging
import stripe
import requests
from requests.auth import HTTPBasicAuth
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("rebuild_lookup_rows")
stripe.api_key = os.environ["STRIPE_SECRET_KEY"]
WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])
LOOKBACK_DAYS = int(os.environ.get("LOOKBACK_DAYS", "14"))
MIN_MISMATCHED_ORDERS = int(os.environ.get("MIN_MISMATCHED_ORDERS", "2"))
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
def intent_id_of(order):
for meta in order.get("meta_data") or []:
if meta.get("key") == "_stripe_intent_id" and meta.get("value"):
return meta["value"]
tid = order.get("transaction_id")
return tid if tid and tid.startswith("pi_") else None
def get_intent(intent_id):
if not intent_id:
return None
try:
return stripe.PaymentIntent.retrieve(intent_id)
except stripe.error.InvalidRequestError:
return None
def recent_paid_orders(lookback_days):
after = f"{datetime.date.today() - datetime.timedelta(days=lookback_days)}T00:00:00"
page = 1
while True:
r = requests.get(
f"{WOO_URL}/wp-json/wc/v3/orders",
params={"status": "processing,completed", "after": after, "per_page": 50, "page": page},
auth=AUTH, timeout=30,
)
r.raise_for_status()
batch = r.json()
if not batch:
return
yield from batch
page += 1
def get_product(product_id):
r = requests.get(f"{WOO_URL}/wp-json/wc/v3/products/{product_id}", auth=AUTH, timeout=30)
if r.status_code == 404:
return None
r.raise_for_status()
return r.json()
def order_line_facts(order):
"""Yield (product_id, order_total_minor, discounted) for each line item."""
for item in order.get("line_items", []):
product_id = item.get("product_id")
if not product_id:
continue
quantity = item.get("quantity") or 1
unit_minor = round(float(item.get("price", 0)) * 100)
discounted = float(item.get("total", 0)) != float(item.get("subtotal", item.get("total", 0)))
yield product_id, unit_minor, discounted, quantity
def product_price_minor(product):
return round(float(product["price"]) * 100)
def decide(product, order_facts, min_mismatched_orders=2):
"""order_facts: list of dicts like
{"order_total_minor": 4500, "stripe_amount_minor": 4500, "discounted": False}
for recent paid orders that contained this product.
"""
if not product.get("purchasable", True):
return ("skip", "product is not purchasable")
if len(order_facts) == 0:
return ("skip", "no recent paid orders to compare against")
current_price = product_price_minor(product)
mismatched = [
f for f in order_facts
if not f["discounted"] and abs(f["order_total_minor"] - current_price) > 1
and abs(f["order_total_minor"] - f["stripe_amount_minor"]) <= 1
]
if len(mismatched) >= min_mismatched_orders:
return ("resave", "lookup price looks stale against confirmed Stripe charges")
if product.get("stock_status") == "instock" and product.get("stock_quantity") == 0:
return ("resave", "lookup shows in stock with zero quantity")
return ("ok", "lookup data matches recent activity")
def resave_product(product):
requests.put(
f"{WOO_URL}/wp-json/wc/v3/products/{product['id']}",
json={
"regular_price": product.get("regular_price", product["price"]),
"stock_quantity": product.get("stock_quantity"),
},
auth=AUTH, timeout=30,
).raise_for_status()
def collect_order_facts_by_product():
by_product = {}
for order in recent_paid_orders(LOOKBACK_DAYS):
intent = get_intent(intent_id_of(order))
stripe_amount = intent.get("amount_received") if intent else None
if stripe_amount is None:
continue
for product_id, unit_minor, discounted, quantity in order_line_facts(order):
by_product.setdefault(product_id, []).append({
"order_total_minor": unit_minor,
"stripe_amount_minor": round(stripe_amount / max(quantity, 1)),
"discounted": discounted,
})
return by_product
def run():
resaved = 0
facts_by_product = collect_order_facts_by_product()
for product_id, order_facts in facts_by_product.items():
product = get_product(product_id)
if product is None:
log.warning("Product %s from recent orders is missing now", product_id)
continue
action, reason = decide(product, order_facts, MIN_MISMATCHED_ORDERS)
if action != "resave":
continue
log.info("Product %s: %s. %s", product_id, reason, "would resave" if DRY_RUN else "resaving")
if not DRY_RUN:
resave_product(product)
resaved += 1
log.info("Done. %d product(s) %s.", resaved, "to resave" if DRY_RUN else "resaved")
if __name__ == "__main__":
run()/**
* Find WooCommerce products whose wp_wc_product_meta_lookup row has drifted
* from the real product data, and repair them by resaving through the
* REST API.
*
* Never writes to wp_wc_product_meta_lookup directly. Resaving a product
* runs WooCommerce's own save path, which is what rebuilds that row. Run on
* a schedule. Safe to run again and again.
*/
import Stripe from "stripe";
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);
const WOO_URL = process.env.WOO_STORE_URL.replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
`${process.env.WOO_CONSUMER_KEY}:${process.env.WOO_CONSUMER_SECRET}`
).toString("base64");
const LOOKBACK_DAYS = Number(process.env.LOOKBACK_DAYS || 14);
const MIN_MISMATCHED_ORDERS = Number(process.env.MIN_MISMATCHED_ORDERS || 2);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
async function woo(path, options = {}) {
const res = await fetch(`${WOO_URL}/wp-json/wc/v3${path}`, {
...options,
headers: { "Content-Type": "application/json", Authorization: AUTH, ...(options.headers || {}) },
});
if (res.status === 404) return null;
if (!res.ok) throw new Error(`Woo ${path} returned ${res.status}`);
return res.json();
}
function intentIdOf(order) {
for (const meta of order.meta_data || []) {
if (meta.key === "_stripe_intent_id" && meta.value) return meta.value;
}
const tid = order.transaction_id;
return tid && tid.startsWith("pi_") ? tid : null;
}
async function getIntent(intentId) {
if (!intentId) return null;
try {
return await stripe.paymentIntents.retrieve(intentId);
} catch {
return null;
}
}
async function* recentPaidOrders(lookbackDays) {
const after = new Date(Date.now() - lookbackDays * 86400000).toISOString();
let page = 1;
while (true) {
const batch = await woo(`/orders?status=processing,completed&after=${after}&per_page=50&page=${page}`);
if (!batch.length) return;
for (const order of batch) yield order;
page++;
}
}
function orderLineFacts(order) {
return (order.line_items || [])
.filter((item) => item.product_id)
.map((item) => {
const quantity = item.quantity || 1;
const unitMinor = Math.round(parseFloat(item.price || 0) * 100);
const subtotal = item.subtotal !== undefined ? item.subtotal : item.total;
const discounted = parseFloat(item.total || 0) !== parseFloat(subtotal || 0);
return { productId: item.product_id, unitMinor, discounted, quantity };
});
}
export function productPriceMinor(product) {
return Math.round(parseFloat(product.price) * 100);
}
export function decide(product, orderFacts, minMismatchedOrders = 2) {
// orderFacts: [{ orderTotalMinor, stripeAmountMinor, discounted }] for recent
// paid orders that contained this product.
if (product.purchasable === false) return ["skip", "product is not purchasable"];
if (orderFacts.length === 0) return ["skip", "no recent paid orders to compare against"];
const currentPrice = productPriceMinor(product);
const mismatched = orderFacts.filter(
(f) =>
!f.discounted &&
Math.abs(f.orderTotalMinor - currentPrice) > 1 &&
Math.abs(f.orderTotalMinor - f.stripeAmountMinor) <= 1
);
if (mismatched.length >= minMismatchedOrders) {
return ["resave", "lookup price looks stale against confirmed Stripe charges"];
}
if (product.stock_status === "instock" && product.stock_quantity === 0) {
return ["resave", "lookup shows in stock with zero quantity"];
}
return ["ok", "lookup data matches recent activity"];
}
async function resaveProduct(product) {
await woo(`/products/${product.id}`, {
method: "PUT",
body: JSON.stringify({
regular_price: product.regular_price || product.price,
stock_quantity: product.stock_quantity,
}),
});
}
async function collectOrderFactsByProduct() {
const byProduct = new Map();
for await (const order of recentPaidOrders(LOOKBACK_DAYS)) {
const intent = await getIntent(intentIdOf(order));
const stripeAmount = intent ? intent.amount_received : null;
if (stripeAmount == null) continue;
for (const fact of orderLineFacts(order)) {
const list = byProduct.get(fact.productId) || [];
list.push({
orderTotalMinor: fact.unitMinor,
stripeAmountMinor: Math.round(stripeAmount / Math.max(fact.quantity, 1)),
discounted: fact.discounted,
});
byProduct.set(fact.productId, list);
}
}
return byProduct;
}
export async function run() {
let resaved = 0;
const factsByProduct = await collectOrderFactsByProduct();
for (const [productId, orderFacts] of factsByProduct) {
const product = await woo(`/products/${productId}`);
if (!product) {
console.warn(`Product ${productId} from recent orders is missing now`);
continue;
}
const [action, reason] = decide(product, orderFacts, MIN_MISMATCHED_ORDERS);
if (action !== "resave") continue;
console.log(`Product ${productId}: ${reason}. ${DRY_RUN ? "would resave" : "resaving"}`);
if (!DRY_RUN) await resaveProduct(product);
resaved++;
}
console.log(`Done. ${resaved} product(s) ${DRY_RUN ? "to resave" : "resaved"}.`);
}
run().catch((err) => { console.error(err); process.exit(1); });Add a test
The decision rule is the part most worth testing, because it decides which products get resaved. Because we kept decide pure, the test needs no network, no WordPress database, and no Stripe account. It just feeds in plain objects and checks the action.
from rebuild_lookup_rows import decide
def fact(**over):
base = {"order_total_minor": 4500, "stripe_amount_minor": 4500, "discounted": False}
base.update(over)
return base
def test_resave_when_price_steadily_mismatched():
product = {"price": "60.00", "purchasable": True, "stock_status": "instock", "stock_quantity": 5}
facts = [fact(), fact()]
assert decide(product, facts)[0] == "resave"
def test_ok_when_only_one_mismatch_below_threshold():
product = {"price": "60.00", "purchasable": True, "stock_status": "instock", "stock_quantity": 5}
facts = [fact()]
assert decide(product, facts, min_mismatched_orders=2)[0] == "ok"
def test_ok_when_price_matches():
product = {"price": "45.00", "purchasable": True, "stock_status": "instock", "stock_quantity": 5}
facts = [fact(), fact()]
assert decide(product, facts)[0] == "ok"
def test_skip_discounted_orders_are_not_counted_as_mismatch():
product = {"price": "60.00", "purchasable": True, "stock_status": "instock", "stock_quantity": 5}
facts = [fact(discounted=True), fact(discounted=True)]
assert decide(product, facts)[0] == "ok"
def test_skip_when_not_purchasable():
product = {"price": "60.00", "purchasable": False, "stock_status": "instock", "stock_quantity": 5}
assert decide(product, [fact(), fact()])[0] == "skip"
def test_resave_when_stock_says_instock_with_zero_quantity():
product = {"price": "45.00", "purchasable": True, "stock_status": "instock", "stock_quantity": 0}
assert decide(product, [fact()])[0] == "resave"
def test_skip_when_no_recent_orders():
product = {"price": "45.00", "purchasable": True, "stock_status": "instock", "stock_quantity": 5}
assert decide(product, [])[0] == "skip"import { test } from "node:test";
import assert from "node:assert/strict";
import { decide } from "./rebuild-lookup-rows.js";
const fact = (over = {}) => ({ orderTotalMinor: 4500, stripeAmountMinor: 4500, discounted: false, ...over });
test("resave when price steadily mismatched", () => {
const product = { price: "60.00", purchasable: true, stock_status: "instock", stock_quantity: 5 };
assert.equal(decide(product, [fact(), fact()])[0], "resave");
});
test("ok when only one mismatch below threshold", () => {
const product = { price: "60.00", purchasable: true, stock_status: "instock", stock_quantity: 5 };
assert.equal(decide(product, [fact()], 2)[0], "ok");
});
test("ok when price matches", () => {
const product = { price: "45.00", purchasable: true, stock_status: "instock", stock_quantity: 5 };
assert.equal(decide(product, [fact(), fact()])[0], "ok");
});
test("discounted orders are not counted as mismatch", () => {
const product = { price: "60.00", purchasable: true, stock_status: "instock", stock_quantity: 5 };
assert.equal(decide(product, [fact({ discounted: true }), fact({ discounted: true })])[0], "ok");
});
test("skip when not purchasable", () => {
const product = { price: "60.00", purchasable: false, stock_status: "instock", stock_quantity: 5 };
assert.equal(decide(product, [fact(), fact()])[0], "skip");
});
test("resave when stock says instock with zero quantity", () => {
const product = { price: "45.00", purchasable: true, stock_status: "instock", stock_quantity: 0 };
assert.equal(decide(product, [fact()])[0], "resave");
});
test("skip when no recent orders", () => {
const product = { price: "45.00", purchasable: true, stock_status: "instock", stock_quantity: 5 };
assert.equal(decide(product, [])[0], "skip");
});Case studies
The database script that fixed the product but not the shop
A store ran a one-off SQL script to correct a currency rounding mistake across four hundred products, updating wp_postmeta directly to save time. The product pages showed the right price everywhere. The "sort by price" and price range filter on the shop page kept the old numbers for weeks, since the lookup table was never touched.
The script above flagged twelve products with a steady mismatch against confirmed Stripe charges, all from that batch. Resaving them through the REST API rebuilt the lookup rows in under a minute and the filters matched again.
The migration that copied posts but not the cache
A developer refreshed production from a staging snapshot but restored only the WordPress core tables and postmeta, skipping the WooCommerce-specific lookup table on purpose to save time on a large import. Every product looked right in the editor. The low stock report, which reads the lookup table, still showed last month's stock counts.
Running the script in dry run mode produced a short list of items whose lookup stock status disagreed with their real quantity. A single resave pass on that list brought the report back in sync without anyone touching the database again.
After this runs on a schedule, a stale lookup row is caught within a day instead of surfacing as a customer complaint weeks later. The script never guesses at SQL and never writes to the cache table directly, it only asks WooCommerce to redo the save it already knows how to do. Keep it running even after a specific bulk edit or migration is fixed, since any future direct database change can reintroduce the same drift.
FAQ
Why does the WooCommerce product lookup table show the wrong price or stock?
WooCommerce stores the real product data as post meta, and copies a fast-to-query summary of it into wp_wc_product_meta_lookup. That copy is only refreshed when normal WooCommerce code saves the product. A direct database edit, a bad import, or a plugin that writes meta straight to the database can change the real data without ever refreshing the lookup row, so the two fall out of sync.
Is it safe to fix the lookup table with a script?
Yes, when the script never writes SQL by hand and instead asks WooCommerce itself to resave each affected product through the REST API, the same update path a shop manager clicking Save would trigger. That save is what refreshes the lookup row. Start in dry run mode to see the list of affected products before anything is touched.
How do I know which products are actually affected?
Compare the price and stock quantity WooCommerce reports for a product through the REST API against what recent paid orders for that product actually charged, using the PaymentIntent amount from Stripe as the source of truth for what a customer paid. A steady mismatch across several recent orders for the same product points at a stale lookup row, not a one-off discount or coupon.
Related field notes
Citations
On the problem:
- WooCommerce developer docs: the product data store and the wc_product_meta_lookup table. developer.woocommerce.com/docs/category/data-management
- WooCommerce docs: how High Performance Order Storage and related lookup tables keep catalog queries fast. woocommerce.com/document/high-performance-order-storage
- WordPress support forum: reports of stock filters and price sorting showing stale data after a bulk import. wordpress.org/support/plugin/woocommerce
On the solution:
- WooCommerce REST API: update a product, including price and stock fields. woocommerce.github.io/woocommerce-rest-api-docs
- WooCommerce REST API: list orders and read line items and order meta. woocommerce.github.io/woocommerce-rest-api-docs
- Stripe API: retrieve a PaymentIntent to confirm the amount actually charged. docs.stripe.com/api/payment_intents/retrieve
Stuck on a tricky one?
If you have a bug in WooCommerce, WooCommerce Subscriptions, or the WooCommerce Stripe gateway that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.
Did this fix your catalog?
If this saved you a pile of confused support tickets about wrong prices or phantom stock, you can buy me a coffee. It is the best way to keep these field notes free and growing.
Buy me a coffee on Ko-fi