TLDR: Alias products that mirror a canonical's inventory were being SUM'd into the canonical's total. SKU-A showed 3N on-hand. Real count: N. The system was calling "soon" on a reorder that was already critical.

The Setup

We're building a supply chain system for an ecommerce business — a supplement brand running two Shopify stores, a main store and a secondary product line — on top of Supabase (our Postgres backend).

Products have a canonical_product_id self-reference: alias products (store mirrors, bundle variants, cross-store duplicates) all point to their canonical. Reports group by canonical and SUM inventory to get a single on-hand number per SKU.

Clean design. In theory.

The Email

A developer I work with, who was building his own tooling against the same Supabase, emailed me to flag something weird.

Three products — IDs 35, 36, 1107 — all showed the same quantity, the same warehouse, the same sync timestamp.

His read: shared inventory_item_id on the Shopify side, causing duplicate rows in our inventory table.

Plausible. I dove in.

What I Found Instead

Wrong.

Our sync builds a Map<inventory_item_id, product_id> — so if two products shared a Shopify inventory item ID, the second sync would just overwrite the first. You'd get one row, not three parity rows.

Three rows at identical quantities meant something different: separate inventory items in Shopify, kept in manual lockstep. Classic virtual-mirror aliases — the canonical product plus its two store-mirror aliases, each carrying a copy of the same N-unit physical count.

His diagnosis was wrong. But his instinct was RIGHT.

The Bug Was Real — Just One Layer Down

Because here's what was actually happening.

Three of our most important reports — report_ledger, report_forecast, and report_ledger_daily — all aggregated with the same pattern:

SUM(il.quantity) GROUP BY canonical_id

The canonical row: N. Mirror alias 1: N. Mirror alias 2: N.

3 × N = 3N.

That's what the system reported for a skincare SKU (SKU-A). The true number was N.

Forecast runway: X days — "soon." Reality: Y days — critical.

And it wasn't just SKU-A. Three other product families were also affected. We were making reorder decisions on numbers that were systematically three times too high.

The Fix

Migration 037 (e844c30): exclude virtual-mirror aliases from canonical on_hand aggregations — filter to the canonical product only when rolling up inventory totals.

The model was always right. The queries just never asked whether a row was a physical pile or a pointer to one.

Why This Matters

I had seen the SKU-A numbers. I hadn't doubted them.

That's the unsettling part. The data looked clean — consistent timestamps, plausible quantities, no obvious errors. There was no alarm. Just a SUM that was adding up physical reality three times and calling it the truth.

The lesson I'm carrying: a wrong diagnosis still points at a real problem.

His theory about inventory_item_id was off. But the anomaly he flagged — three rows at identical values — was absolutely worth flagging.

If you're building aggregation queries on top of a canonical/alias product model, inventory is EXACTLY where aliases will betray you. Aliases inherit identity from their canonical. They don't inherit the right to add to its physical count.

Alias rows are pointers, not piles. Build your queries like you know the difference.