So I've discovered that for any scalability I have to use postgresql but what I've also found is that even if I have a working install in my singularity container that other things can use prefect seems to not accept the configuration.
The prefect docker containers don't seem to have posgresql in them so that assumes a separate postgresql server elsewhere. Orchestration in HPC is not straightforward so I really need to make it work within a single container unfortunately. Bit stuck.😅
Now no matter how i change my container prefect throws an error.
RuntimeError: Cannot create flow run. Failed to reach API at http://127.0.0.1:4200/api/.
welp i've managed to fix the issue. Prefect stores config in a .prefect folder in my home directory so no matter what I did with the image because singularity loads your home folder by default it always took its config from there.
I'll need to work out how to do a bind or something to put it somewhere more tightly linked to my singularity builds.
Now I just need to work out how to get the postgresql backend working. Once I've done that I should be able to scale my workflows up to actually take advantage of the HPC and not just fail where the default backend(sqlite) gets overwhelmed from concurrent writes.