Currently vetting a way to sync my database files with my markdown files on my laptop, so it functions similar to Obsidian. I enjoy Vim too much to work constrained to Directus' markdown editor!
I saw that you didn’t want to use a 3rd party provider, but why not stick a git repo on your VPS (which you are trusting with your data today) and use that to coordinate syncs between your client devices?
I expect my PKMS to evolve and wouldn't rule out a self-hosted Git server if I find it's a better option long term.
I don't think you really get it. Git is distributed. There's no need for "a git server". You already have a machine on which you host the SQL database, you can just use that as yet another git remote.
If you’re not trying to coordinate work among multiple people, and aren’t trying to enforce a single source of truth with code, you don’t _need_ “git server” software. You just need a git repository (folder & file structure) in a location that you consider to be your source of truth.
I’m not trying to convince you to change it now, especially if you’re happy with what you have, but I would suggest reading some (or all) of https://git-scm.com/book/en/v2
I think the first ~4 subsections of chapter 4 cover what I & tasuki were suggesting could be sufficient for you. If you’re the type of engineer to read through the code of your data storage layer, I think you’d find Chapter 10 (Git Internals) interesting, because it can demystify git. I enjoyed the whole book.
As with any engineering project, I see lots of questions about your choices, and I applaud you for sticking around. I would make very different decisions than you, based on your stated priorities, but that’s okay.
I learnt this quite late and was not obvious to me so hope it's helpful for you too.
I'd recommend you to look into SyncThing Fork or a similar tool if you never want your notes to leave your own server.
I wrote about ways to sync Obsidian here: https://bryanhogan.com/blog/how-to-sync-obsidian
[0] https://relay.md