Move tracker ownership enforcement into Postgres (Clerk↔Supabase third-party auth)

[Jose-Gael-Cruz-Lopez]

Follow-up to #226 / #234, which ships tracker sync using the Supabase
service role key.

Where things stand

web/lib/tracker-store.ts authenticates with SUPABASE_SERVICE_ROLE_KEY, which
bypasses RLS. Ownership of a tracker row is therefore enforced in application
code — by the .eq("user_id", userId) on every read, update and delete, and by
writing user_id explicitly on every insert. The userId always comes from the
Clerk session resolved in the route handler, never from a request body.

That is correct as written, and the surface is deliberately small: four
functions, all short, all requiring userId as a parameter rather than taking it
as an option.

The cost is that the database is not a backstop. A missing filter in that one
file would be a cross-user read, and RLS could not catch it — service_role is
precisely the role those policies don't apply to.

The policies added in 20260725154500_user_hackathons.sql are already written
against auth.jwt() ->> 'sub':

create policy "read own tracker"
  on public.user_hackathons for select
  to authenticated
  using ((select auth.jwt() ->> 'sub') = user_id);

They currently do useful work — anon and authenticated can't touch the table
at all, so nothing reachable with a publishable key can read someone's tracker —
but the owner-matching half has never actually matched anything, because no
authenticated request carries a Clerk sub that Supabase trusts yet.

What to do

Configure Clerk as a third-party auth provider in Supabase so the browser's
own Clerk session token satisfies those policies directly, then move
tracker-store.ts off the service role and onto the user's token.

• Configure the trust relationship — Clerk dashboard and Supabase
  dashboard. Requires admin on both.
• Verify a Clerk-issued token's sub lands where the policies read it.
• Switch the client construction in web/lib/tracker-store.ts from the
  service role key to the request's Clerk token.
• Confirm the .eq("user_id", …) filters are now belt-and-braces rather than
  the only guard — keep them, but they should stop being load-bearing.
• Prove enforcement moved: a request forging another user's hackathon_id
  should be rejected by Postgres, not by app code. Test it with the app
  filters temporarily removed.
• Drop SUPABASE_SERVICE_ROLE_KEY from the runtime environment if nothing
  else needs it, and update web/.env.example + web/README.md.
• Revisit user_id text not null default auth.jwt() ->> 'sub' — dead on the
  service-role path, live once this lands.

Why it isn't urgent

user_hackathons holds no user data yet, and app-layer scoping is genuinely
enforced today. This is about removing a single point of failure, not fixing a
live hole. It needs coordinated dashboard access on two services, which is why it
was split out rather than blocking #226 and #223.

Reference: Clerk × Supabase third-party auth

[Jose-Gael-Cruz-Lopez]

Investigated. The security analysis here holds up, but the issue can't be worked in its current form — and the reason isn't the dashboard access it calls out.

None of this is on main

	on main?
web/lib/tracker-store.ts	no
supabase/migrations/20260725154500_user_hackathons.sql	no — migrations stop at 20260722192614
SUPABASE_SERVICE_ROLE_KEY anywhere in web/	no — zero references

It all lives on two unmerged drafts: #234 (feat/issue-226-supabase-tracker-wins, 8 commits) and #236 (vercel, 19 commits). origin/vercel contains the #234 branch — it's a strict superset, so #234 is superseded and should be closed rather than merged alongside. Both are stale: 45 and 41 commits behind main.

The "Where things stand" section above describes main as though the code were there. Anyone picking this up would go looking for a file that doesn't exist.

The analysis itself checks out

Read against origin/vercel:

• All four store functions scope correctly — .eq("user_id", userId) on read/update/delete, explicit user_id on both upserts — and every one takes userId as a required first parameter, never from a request body.
• The RLS policies really are inert on this path: all four are to authenticated against auth.jwt() ->> 'sub', and service_role is exactly the role they don't apply to.
• The anon lockdown is doing real work — revoke all ... from anon plus no grants means nothing reachable with a publishable key touches the table.

So "correct as written, but the database is not a backstop" is a fair statement of the risk.

Two things worth adding

1. Schema drift, independent of this issue. The migration's own header records that it was applied by hand through the SQL Editor on 2026-07-26 and is not in supabase_migrations.schema_migrations. So public.user_hackathons exists in the live project right now, while main has no record of it. Rebuild from main's migrations and you get a schema missing a table production has; wire up the Supabase CLI and db push tries to replay one that already exists. This outlives #235 and probably deserves its own issue.

2. A lost-update race in upsertTrackerRow. It reads the existing row, computes stage/isWin in JS, then upserts:

const { data: existing } = await client().from(TABLE).select("stage, is_win")...
const stage  = patch.stage ?? (existing?.stage as Stage) ?? "interested";
const isWin  = patch.isWin ?? existing?.is_win === true;
await client().from(TABLE).upsert({ user_id, hackathon_id, stage, is_win, ... });

Two concurrent PUTs to the same row both read the same existing, and the second write clobbers the first:

A: PUT {stage:"applied"}  reads {interested, win:true}  -> writes {applied,     win:true}
B: PUT {isWin:false}      reads {interested, win:true}  -> writes {interested,  win:false}
                                                   net: A's stage change is lost

The docstring's promise — "moving a hackathon between stages must not clear its win" — holds single-threaded but not under concurrent requests. tracker-store.test.ts covers the partial-update case but nothing concurrent. Low impact (tracker state, and it needs two near-simultaneous clicks on one hackathon), but it's the kind of thing that gets harder to fix later. An atomic upsert — COALESCE(excluded.stage, user_hackathons.stage) in an ON CONFLICT DO UPDATE, or a small SQL function — removes the window entirely.

Suggested order

1. Close feat(my): persist the tracker to the signed-in user's account, and record wins (#226) #234 as superseded by feat: persist the tracker to the user's account, add win trophies, and deploy from main (#226, #223) #236.
2. Rebase feat: persist the tracker to the user's account, add win trophies, and deploy from main (#226, #223) #236 onto main and merge it — 41 commits behind, so expect conflicts in the generated files (README.md, listings.json, the banner).
3. Configure the Clerk↔Supabase trust relationship. This needs admin on both dashboards and is the hard blocker on everything below.
4. Then this issue's code swap becomes a small, testable change.

Working step 4 before 1–3 means editing a file that isn't on main against a trust relationship that doesn't exist yet.