Avoca — Internal

Appearance

PostgREST FK ambiguity in booking-windows (service_areas → teams) ​

What happened ​

Migration 20260508120003_add_service_areas_route_to_team_id.sql added a second FK from service_areas to teams:

sql
ALTER TABLE public.service_areas
  ADD COLUMN IF NOT EXISTS route_to_team_id bigint
  REFERENCES public.teams(id) ON DELETE RESTRICT;

The migration is explicit that the new column is consumed only by the enterprise web-chat zip-entry resolver and that existing consumers should keep reading by team_id. PostgREST didn't get the memo. Once the migration shipped, every embedded select that starts from teams and embeds service_areas(*) saw two candidate FKs (team_id and route_to_team_id) and refused to choose, returning an ambiguity error.

apps/web/lib/booking-windows/utils/helpers.ts in getTypeData() does exactly that embed pattern, which is on the analyze/get-availabilities request path. That path broke between the migration shipping (2026-05-08) and the consumer fix (2026-05-11).

Why we care ​

Same pattern can bite our work. AutoOps reschedule/cancel writes against team-scoped tables; if we ever add an embedded select like teams ... <some_table>(*) and a future migration adds a second FK to <some_table>, the failure mode is silent until a real request lands. The fix shape is trivial, but discovery is reactive (5xx in production) unless we adopt the explicit-FK syntax as a default.

Christian raised the latent pattern on Slack 2026-05-11: "should we proactively fix any FK queries that might become ambiguous in the future". The answer is yes, and the simplest approach is a grep audit for embedded selects that match the at-risk shape.

The fix ​

PR #10202 (c97b5c55ca, kshitij947, merged 2026-05-11 11:56 ET). One-line change in getTypeData():

diff
-    service_areas(*),
+    service_areas!service_areas_team_id_fkey(*),

Same response key, explicit FK chosen, intended behavior preserved.

What we should mirror ​

  • When writing embedded selects in our own code, prefer the explicit-FK syntax (table!fk_name(*)) any time the embedded table has more than one FK to the parent, even when the syntax is uglier.
  • Treat the implicit-FK form as a latent bug. A migration somewhere else in the codebase can break our query without touching our file.
  • A worthwhile audit: grep apps/web for from('<table>').*embed-style patterns and disambiguate any select that touches tables with multiple FKs to the same parent.