If anything, maybe give people the option to export to SQLite and then use that going forward but keep it entirely optional.
None at all. That is why I am saying it should be entirely optional so most people can just ignore it and those wanting the alternate schema can get their feature request fulfilled.
I do not see how it would benefits end users in any significant way. Sure, you can look inside the database, how many people need that?
Dumping the database to CSV is not a good backup, schema changes over time, what was dumped from one version of the app would not work for importing into another version if schema changes. Backup it needs a versioned schema format, which would actually look like KDBX format if implemented in XML :-)
As for scale this app is for one person doing one query at a time or saving one password at a time. One person will not be saving millions of passwords or if for some reason they are then they can export to a format that an enterprise solution could take it's place. People have written team / company password managers that can use Oracle, MySQL and Postgres that can track and audit user and team password changes. This is not something that should ever be expected of an individual personal password manager like KeepassXC.
This is just my take but I would rather have the maintainers of KP focus on bugs, usability issues, quality of life enhancements which they have been doing great thus far in my opinion. Forcing a schema migration is just asking for trouble and potentially causing bugs that turn some people away from using it or cause people to lose data if they do not have snapshots of their database. Or if forcing a schema change make for damn sure there are many backups in each format and encourage the users to back up all the files to many external encrypted drives and store some of the encrypted devices off-site. Something everyone should be doing regardless.
Most probable reason would be that I ended up editing on the wrong machine while being offline or having it open on another one, so user error. But I don't know.
In any case, the only 2 moving parts were KeePassXC (I think, not sure when I moved on from KeePass) and syncthing. But most importantly, it was easily fixed every time, no broken file - which points me more into a syncthing diverge.
Otherwise, no, apparently no issues with kdx itself, or I wouldn't have been able to merge these problems. (which brings me to my pet peeve, a good working merge is missing...)
File locks or attempting to overwrite and open database would be a problem regardless of schema format or what application is holding the database file open for writing. This should be expected behavior and one would hope the app would detect this and seqfault / core dump without corrupting the original / previous version of the database file.
That problem that the current schema doesn't have enough ways to declare custom memory-protected fields outside of user-facing attributes seems just as likely to be replicated and maybe worsened in an SQL schema.
Changing the database engine doesn't fix the architecture problems nor schema migration problems. It's certainly a good time to reevaluate the architecture problems and the schema migration problems. But the huge caution I'd suggest here would be look at the ossification complaints about the current XML schema and expect SQL migrations to be worse and plan for worse multi-schema operations and intenser ossification. (Especially because these files are expected to live in a large multi-vendor ecosystem, SQLite schema migration management is going to be much worse than any XML schema management.)