The Foreign Key That Made Buddy Say Nothing Needed Attention

Most of Bakery Buddy's automations report an "influenced" number that's an honestly bounded guess, not a fact. Review Requests probably helped bring in that five-star review. Win Back probably nudged that lapsed customer back. "Probably" is doing real work in both sentences.

Referral Request is the one exception. When a customer forwards their own referral link and a stranger fills out the public inquiry form carrying that signed, bakery-matched token, the new order gets a column set on it: `referred_by_customer_id`. If that column has a value, the whole chain actually happened. Nobody is estimating anything.

Shipping that column on August 12th also broke almost every green number in the app, for reasons that had nothing to do with referrals at all.

What the migration actually did

`orders` already had a foreign key to `customers`, the ordinary one, `orders_customer_id_fkey`, tying an order to the customer who placed it. The migration that added `referred_by_customer_id` declared that column as a foreign key too, pointing back at the same `customers` table. Two columns on `orders`, both constrained to reference `customers`.

Read on its own, that's a completely reasonable thing to write. It also happens to be the one shape of migration this app's data layer cannot survive.

Why a second relationship breaks the first one

Bakery Buddy talks to its database through Supabase's PostgREST layer, which gives you a convenient shortcut called embedding: ask for orders and get each one's customer in the same query, something like `select('*, customers(*)')`. PostgREST works out how to do that by following the foreign key between the two tables.

Give it two foreign keys between the same two tables and it stops being able to guess which one you meant. It doesn't pick one and move on. It refuses the query outright, with an error code that says exactly that: `PGRST201`, "more than one relationship was found between orders and customers." Every query anywhere in the app shaped like `orders` embedding `customers` started failing the moment that migration applied, not just the ones that touched the new column. The ambiguity isn't about which row you asked for. It's about whether the two tables can be joined at all.

Why it looked like "nothing to do" instead of an outage

About 33 places in the codebase made that exact kind of query: `getBuddyItems`, the single function every attention badge in the app reads from. The dashboard. The calendar. The pickup-reminders cron. Payment confirmations. Both money reports. The automation enrollment finders.

Here's the part that made it worse than an ordinary outage. Several of those call sites use a pattern that looks careful: fall back to an empty array whenever a query comes back with no data. That's a sensible guard against a genuinely empty result. It is not a sensible guard against a hard query failure, and PostgREST refusing to embed is a hard query failure. The code had no way to tell those two things apart, so a real error turned into a real, well-formed, empty list.

The app didn't show an error banner anywhere. It showed the ordinary, calm state of a bakery with nothing pending. Buddy reported that nothing needed attention while every one of those 33 queries was actively failing behind the screen.

The fix, and what it costs

The fix wasn't rewriting 33 queries. It was dropping the new foreign key and keeping the column. `orders_customer_id_fkey` goes back to being the only relationship between the two tables, every embed becomes unambiguous again, and `referred_by_customer_id` is now a plain uuid with nothing enforcing that it points at a real row.

The other option was writing `customers!orders_customer_id_fkey(...)` at every one of those 33 sites to tell PostgREST which relationship to use. That would have worked, and it was rejected on purpose, because it doesn't just fix today's 33 queries. It means every future `orders` to `customers` query also needs that same hint, forever, for as long as the second foreign key exists, and nothing would warn the next person about it until their query shipped broken.

Dropping the constraint gives up something real: the database no longer guarantees the referral column points at an actual customer, and it no longer clears itself if that customer's row is deleted later. That trade is acceptable here because exactly one function writes this column, from a signed and verified token, and nothing in the app joins back through it to render anything on screen. If the referring customer is later deleted, the order still legitimately arrived through a referral. It just can't prove that against a live row anymore.

What generalizes past this one column

Before adding a foreign key, it's worth one query to check whether the two tables already have one. If they do, there are exactly two honest options, and both cost something:

• Don't make the new column a foreign key. You lose referential integrity on that one column, and you keep every existing and future query working the way it already does.
• Own the disambiguation hint everywhere, forever. Every existing embed between those two tables needs the hint added, and every future one needs to be written with it from the start, because nothing will remind anyone it's required.

And a migration reporting success only proves the DDL applied. It proves nothing about whether the queries built on top of that schema still work. The only real check is running one of those embeds afterward and getting back a normal result, not a `PGRST201`.

Why nothing else caught it

Bakery Buddy's database client is untyped by design, for reasons that have nothing to do with this bug, so there's no compiler standing between a schema change and a query built against it. A typecheck passed. A build passed. Nothing about the migration looked wrong sitting in a diff. The only thing that would have caught this before it shipped was asking, out loud, whether `orders` and `customers` already had a relationship before adding a second one, and then actually running an embed after the migration instead of trusting that a clean apply meant a working app.