If the Hydra keeps to spec, you should be able to use any sync type you want; the OSSC will accept all of sync-on-composite, sync-on-luma, and composite sync. As far as SCART cables go, sync-on-composite is the most normal, but you will want CSync cables for consoles that natively output CSync (NTSC SNES, N64 when RGB modded, NTSC GC when RGB modded, Genesis 2, NTSC Saturn), and sync-on-luma for consoles that don’t (PAL SNES, PAL GC, PAL Saturn, PS2, Xbox).
Generally, the only CSync incompatibility problems I’ve read about have been cables with sync strippers not playing nice with the sync stripper in the gscartsw_lite, and you don’t really need CSync cables with sync strippers unless you’re trying to hook them up to devices that only accept CSync, like Extron Crosspoints or PVMs/BVMs.
I recommend going component (YPbPr) for Xbox and PS2, as I don’t think either can do progressive scan over RGB SCART. For Xbox specifically, I would recommend softmodding, switching your video region to NTSC, and going with Component, so that you can use 480p, 720p, and 1080i. For PS2, you will want either first-party cables for PS2 or PS3, or properly-shielded third-party cables (stay far away from Tomee).
PS2 can do progressive over SCART using sync on green, which is fully supported by OSSC.
Indeed. I hesitated to bring up RGsB, because I know it doesn’t work through the gscartsw_lite; I had forgotten that the Hydra lets you manually override the input. (Still, I wonder if the Hydra will forward a manually-selected input even without a sync signal on pin 20.)