PGRST200: could not find a relationship in the schema cache (Supabase)

Add the constraint, do not fake the join

The fix is a real foreign key, which you want anyway — it is what stops an order from referencing a profile that does not exist. Add it, reload the schema cache, and the nested select starts working with no client change.

Expect the alter table itself to fail the first time with violates foreign key constraint. That is the constraint doing its job on data that predates it: some rows point at an id that is not there, which is precisely the corruption the convention-only relationship allowed. Find them with a left join before you add the constraint, and decide whether they should be deleted or repaired — do not reach for not valid to skip the check unless you have a plan to clean up later.

If two paths exist between the tables, disambiguate in the query by naming the constraint rather than dropping one of them: select('*, profiles!orders_user_id_fkey(*)'). The error is PostgREST refusing to guess, which is the correct behaviour when guessing wrong would return the wrong person's row.

Why a view is the dangerous shortcut here

The workaround that circulates is to create a view that joins the two tables and query that instead. It works immediately, and in PostgreSQL a view executes with the privileges of its OWNER, not its caller. A view created in the SQL editor is owned by a privileged role, so it reads the underlying tables with Row Level Security effectively bypassed — every user's rows, returned to whoever can select from the view.

This is measurable in one query. On a table with a working owner-scoped policy, selecting the table directly returns your row; selecting a plain view over that same table returns everybody's. The policy did not change and the dashboard still shows RLS enabled, which is what makes this so much worse than simply forgetting a policy — every indicator says the table is protected.

Since PostgreSQL 15 the option that fixes it is security_invoker = true, which makes the view run as the caller and apply their policies. Set it on every view you expose through the API, not just this one.

The fix

-- 0. Orphans first — the ALTER below fails if any of these come back.
select o.id, o.user_id
from public.orders o
left join public.profiles p on p.id = o.user_id
where o.user_id is not null and p.id is null;

-- 1. The actual fix: declare the relationship PostgREST is looking for.
alter table public.orders
  add constraint orders_user_id_fkey
  foreign key (user_id) references public.profiles(id);

notify pgrst, 'reload schema';

-- 2. If you do want a view, it MUST run as the caller. Without this option a
--    view over an RLS-protected table returns every row to everyone.
create or replace view public.orders_with_profile
  with (security_invoker = true) as
  select o.*, p.handle
  from public.orders o
  join public.profiles p on p.id = o.user_id;

grant select on public.orders_with_profile to authenticated;

-- 3. Audit every view you already have. Anything false here is reading your
--    tables with the owner's privileges, RLS included.
select c.relname,
       coalesce((c.reloptions::text[] @> array['security_invoker=true']), false) as respects_rls
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'v' and n.nspname = 'public'
order by respects_rls, c.relname;