TLDR: If a metric varies by both sides of a many-to-many relationship — not just one — it belongs on the join table. I had to learn this a third time before it finally stuck.

The Setup

I'm building a supply-chain forecasting app for an ecommerce supplement brand.

One of the core calculations is reorder point: how many days of inventory do we have, and how does that compare to how long it'll take to restock?

That "how long to restock" number is lead time.

So I needed lead time in the database.

The Wall

I modeled supplier_details.lead_time_days as a single number per vendor.

That seemed fine. Until my client walked me through the actual picture.

The primary co-packer (the company that physically makes the product) makes Product A on a 20-week lead time. But Product B out of the same facility? 8 weeks.

One vendor. Two products. Wildly different numbers.

A single lead_time_days on the vendor row can't hold that.

What I Got Wrong (Again)

Here's the embarrassing part: this was the third time I'd made this exact mistake in this codebase.

The "one number per party" model is the natural first guess. It usually works at first, because early on you only have one product per vendor — "the co-packer's lead time is 42 days" is technically true when they only make one thing for you. The schema looks clean. The queries are simple.

The breakage only surfaces once real-world variance accumulates. By then it's expensive.

The litmus test I should have asked from the start:

"Is there any plausible future where this metric differs for the same vendor but a different product?"

If yes — or even maybe — the metric belongs on the join row, not the vendor row.

The Fix That Worked

Migration 027 introduced a vendor_products join table:

vendor_contact_id, product_id, rank, lead_time_days, is_active, ...

Lead time now lives on the assignment, not the vendor. Product A from the co-packer = 20wk. Product B from the co-packer = 8wk. Backfilled 7 rows from the old columns. The forecast function rebuilt with the new join logic. Reorder point for Product A landed at 162 days — which was finally the right number.

The Gotcha That Still Bit Me

I applied the migration, verified the rows existed in the SQL editor, watched the forecast function return the correct data, and shipped the code.

Opened the vendor assignment dialog in the UI.

"No vendors assigned yet."

What.

Seven rows existed. I just saw them. So I checked pg_class.relrowsecurity — the Postgres system table that tells you if Row Level Security (RLS, Supabase's access control layer that restricts which rows a logged-in user can read) is enabled on a table.

It was true on vendor_products. With no policy attached.

The forecast function is a SECURITY DEFINER function, meaning it runs as the database owner and bypasses RLS entirely. So the forecast numbers were correct. But the UI authenticates as a regular user — and with no RLS policy, they got nothing.

Migration 028: one-line policy. App worked immediately.

The real lesson there: verify at the layer the user actually hits. A privileged function will happily return correct data and give you a false green while the UI starves.

Why This Matters

This pattern shows up everywhere:

  • Discount per (customer, product) — not on the customer
  • SLA per (account, service tier) — not on the account
  • Lead time per (vendor, product) — not on the vendor

The moment a metric varies by the pair, it needs a join row. Ask the question early, while the schema is still cheap to change.

And any time you migrate a new table in Supabase: add the RLS policy in the same migration. Don't let the SECURITY DEFINER smoke screen fool you.

P.S. If you're building in Supabase and a new table "has data" but returns nothing in your app — check pg_class.relrowsecurity first. Saved me an hour of confusion the hard way.