Does sync ever turn off completely?

No. Two-way sync with your Supernote is always available — the Scheduling section only sets how often — and, via Active hours, when — it runs on its own. Even with both sync and push set to Manual, the app still checks once when it starts and before any push, and Sync now works any time; it just won't run a repeating background timer.