The problem with automatic daemonization is that there is no way to indicate startup failure: you just fire and forget the process in the background. If the process dies, there's no clear way of knowing whether it was due to an error in startup conditions (possibly as simple as a configuration error) or a runtime failure at a later time. In the first case, restarting makes no sense. In the latter case it might be a reasonable option.
With manual daemonization, the process can delay forking until it has ensured that its configuration is sound and it has the resources it needs. This way the parent process can indicate with its exit status whether the service was successfully launched.
If your daemon is of Type=forking then failure to initialize should be indicated by the initial process (the one launched by systemd) outputting an error message and then failing.
Only once the grandchild (not the child, you must double-fork in order to start up correctly) has indicated to the initial process that it is ready to provide the service, should the initial process exit, cleanly, thereby notifying systemd that startup was successful.
If the program exits with a special status to indicate non-restartable failure, you could, in principle, recognize that in the process manager and handle it differently, by not restarting. Is there any way to do this in systemd, or in any other process manager?
Usually these daemon managers detect when the process is dying right after starting up and indicate that with a message like "[process] is respawning too fast". Besides, there are always logs.
But that's not a way to detect service failure. That's a way to detect potential service failure. The only unambiguous failure notification is the return code.
With manual daemonization, the process can delay forking until it has ensured that its configuration is sound and it has the resources it needs. This way the parent process can indicate with its exit status whether the service was successfully launched.