Does it stop working for everyone as soon as someone sees it?
Aye, once someone succeeds the investigation check it disappears.
Though for most uses, this doesn't matter too much, since the check only happens when they're gathered around it already. Hence why activating Turn Based mode is really good because it stops NPC's from moving while you take a turn of actions meaning they're still gathered in a location/facing a direction even if the illusion disappeared immediately.