One edit needed: the half-scrubbed project codename `an ecommerce business-os-supply-chain` — the business name was replaced in a prior pass but the -os-supply-chain suffix (and its backticks) survived. Rule 3 says drop the codename and keep the function. The appositive already does that job, so I'll collapse into it. Everything else — 28,700, the product ID, Recharge, Shopify, Supabase, all the technical numbers — is untouched per the KEEP list.

TLDR: If you're syncing Recharge subscriptions to a database and your product links are all null — check external_product_id.ecommerce, not external_product_id. It's an object. The docs don't scream about this.

The Setup

I was building an internal ops dashboard that pulls subscription data from Recharge (our subscription billing platform) and links it to our Shopify product catalog inside Supabase.

Simple enough idea: sync every active subscription, resolve the Shopify product ID, join on inventory.

The Wall

First full sync ran clean.

No errors. No warnings. Exit code 0.

I ran the verification query… and stopped cold.

28,700 orphan rows. Out of 28,700 total.

Every single subscription had a null product link. The sync had "worked" in the sense that it had written rows. Those rows were just… useless.

What Actually Broke

The Recharge docs — and, honestly, the community TypeScript types floating around — describe external_product_id as a string reference to the Shopify product ID.

So I typed it that way: external_product_id: string | null.

TypeScript was fine with this. The field IS present on every response. Typecheck passed.

But at runtime, when I did typeof s.external_product_id === 'string' — it returned false.

Because it's not a string.

It's an object.

{
  "external_product_id": { "ecommerce": "9611959306" }
}

The actual Shopify product ID lives one level deeper, at .ecommerce, as a string. For multi-platform Recharge accounts there can be other keys too (magento, bigcommerce) — but for a Shopify-only account you're always after ecommerce.

So typeof === 'string' returned false. My fallback was null. No exception was raised. No row was skipped. Every row just… silently wrote a null product link. The runtime swallowed it completely.

The Fix

Two lines:

// old
const productId = s.external_product_id // typed string | null — wrong

// new
const productId = Number(s.external_product_id?.ecommerce ?? null) || null

And the type corrected to:

external_product_id: { ecommerce?: string | null } | null

After the fix: 28,700 rows linked correctly. Same data, same sync, two lines changed.

(Same fix applied a day earlier for available on inventory: Shopify returns null for some variants, had to coerce to 0. This API boundary bites in multiple spots.)

Why This One Stuck With Me

The typecheck passed. The sync completed. Nothing threw. In every signal I could reasonably check in the moment — it looked fine.

The only thing that caught it was a deliberate count after the write: how many rows have a null product ID? That query saved hours of downstream confusion about why reports were empty.

That's the lesson I'm carrying forward: at a third-party API boundary, "no error" is not confirmation. The types you have may be wrong. The coercion may silently fail. The only honest check is a post-write verification — count the rows that should have been filled, confirm they actually are.

Always run that count.