How would you implement realtime / websockets in RedwoodJS?

Yes, exactly like that! I do that here, in my “Experiment 1” Supabase redwood experiments

If you sign in to Supabase and go to Settings → Database they give you the info you need to connect straight to the DB