TLDR: When you bolt a junction table onto a legacy singular FK, your schema is multi-entity. Your validation logic? Still isn't. Every constraint that reads the old FK column is now a latent bug — and it won't surface until you count.

The Setup

We've been building an internal KOL CRM for an ecommerce business — a system for managing Key Opinion Leaders (KOLs, basically expert speakers and brand partners) and the webinars they run.

Early on, a webinar had one KOL, one topic. Clean schema. webinars.kol_id, webinars.topic_id. Done.

Then reality happened.

Webinars started having multiple KOLs. Multiple topics. So we did the right data-modeling thing: added webinar_kols and webinar_topics junction tables.

But we kept the old singular FKs around too.

Why? Because our PostgREST embeds (Supabase's select=*,contacts(*) pattern) had hard-coded FK hints that pointed at webinars_kol_id_fkey — things like kol:contacts!webinars_kol_id_fkey(*). Ripping out the singular FK would have broken all that. So instead we treated it as a mirror: junction table is truth, singular FK is kept in sync as a cached pointer to the primary entity.

Fine architectural call. Except we forgot something.

The Bug We Didn't Know We Had

During a security and correctness audit on 2026-06-05, one of the 22 findings had two parts.

Part 3a: The singular FKs on webinars weren't actually being kept in sync. Some webinars had entries in webinar_kols but webinars.kol_id was pointing at the wrong entity — or missing entirely. 40 webinars were affected.

Part 3b (the one that really got me): Our constraint evaluation code was still reading the singular FK.

That means on a webinar with three KOLs attached, only entity #1 ever got constraint-checked. The other two were completely invisible to business logic.

Every rule we'd ever written about whether an entity was valid for a given webinar? Running against one row. Silently. For every multi-entity webinar in the system.

What We Tried (That Was Actually Just Wrong Architecture)

I want to be honest here: I didn't really "try something that didn't work." The code worked — it just worked on the wrong assumption.

The assumption was: "a webinar has one KOL." That was true when we wrote it.

We updated the schema. We didn't audit the consumers.

That's the mistake. The schema going multi-entity doesn't automatically make your code multi-entity. Every function that reads webinar.kol_id is still living in the old world — and the schema change didn't touch any of them.

The Fix That Actually Worked

Two commits, in order:

3a — migration 020, run live against the shared Supabase project via Arc CDP (our browser automation tool): backfill the kol_id and topic_id singular FK columns by reading the primary entity row from the junction tables. 40 webinars repaired.

3b — rewrite the constraint evaluator to iterate ALL rows in webinar_kols and webinar_topics instead of reading the singular FK. If a webinar has three KOLs, all three get evaluated. If one fails, the whole thing fails.

The logic change itself was small. That's what made it so sneaky.

Why This One Stuck With Me

The lesson isn't "junction tables are tricky." The lesson is that a schema migration is never the whole job.

When you go from one-to-one to many-to-many, you're making a promise: "this entity now exists in a set." Every piece of code that reads a single FK is now breaking that promise — silently, every time it runs.

The move that makes this safe:

  1. Backfill the mirror immediately. If you're keeping a singular FK as a cached pointer, make sure it actually points to the right thing.
  2. Grep for every consumer of that FK. Not just the embed hints — the validation logic, the constraint checks, the business rules. All of them.
  3. Rewrite them to iterate the set.

It's two jobs, not one. Ship both or the second one will find you later — probably in an audit.

P.S. The PostgREST FK-hint pattern (kol:contacts!webinars_kol_id_fkey(*) vs. kol:contacts!webinar_kols_kol_id_fkey(...)) is a whole other rabbit hole — the hints changed depending on which level of the join you're at. I'll write that one up separately.