Is there a reason you’re not looking at tools explicitly built for this like orgmode, obsidian, task-warrior, etc? There’s a plethora of these tools and my experience with this is you really don’t want to over-engineer your productivity suite.
That said, if you go the SQL route, sqlite is the way to go. Other SQL databases must be run as a daemon whereas sqlite operates on a local file directly.
However any SQL database isnt going to have the CLI youre asking for. Its interface is… SQL, so you’re scripts are going to have a bunch of SQL code embedded that isnt easily reusable. A non-sql database will probably be better. I’m not familiar with them but I think there’s some that store their data as text files in a folder which is organized a certain way. But that starts looking like the tools I mentioned before.
Yeah I only suggested obsidian because its so popular and is completely out-of-the-box.
If you want everything exactly as you want it you’ll need to spend time coding it all yourself. Otherwise you’re shopping around for different tools for specific things. Some editor plugin for notes. Another for tasks. Another for reminders etc.
My issue with task warrior was its syncing service
taskd
. It required that you generate a self signed ssl certificate. You couldn’t host it behind caddy. But all the issues listed I’m pretty sure it covers. Its extremely robust.