do the upgrade features for modules only work as packages?

For example, the UpdateFromX methods - are they only called if you're upgrading from one version of a package to another?

I ask because there isn't a prompt for an upgrade if I'm just building locally on my development machine; the version numbers just update as they are set in the module.txt, and the UpdateFromX methods are never called, nor is a update ever prompted.

So do I have to Package my modules in order to leverage module updating?

The migration methods are invoked automatically for enabled features. A module doesn't have to be packaged in order for the migrations to be run, nor is that possible, since a package is a kind of zip file. You need to unpackage a module in order for it
to be usable by Orchard. If your migrations aren't being invoked, you may have made an error. Perhaps you can find something in the logs in App_Data/Logs.

Right, okay. So the timing of a module upgrade is essentially...as soon as Orchard gets to it? ASAP? There's just no indication of it happening correctly / no prompt / no notice so I didn't know if I was doing something wrong.

Something like that, I haven't looked into its details. But yes, migrations are run automatically. No prompts, no notices. Usually if something goes wrong, you see a warning notification that some features need to be upgraded.