I recall reading somewhere about a thing which persists data in a particular region of RAM that is guaranteed to be left alone by the kernel, and therefore will persist across a reboot. Buffering in such a region could persist data when the kernel panics but it obviously wouldn't survive loss of power.
Wouldn't opening the file O_APPEND (maybe O_DIRECT also?) and using fdatasync be better? That way we've basically implemented a WAL and skipped all the other database parts we don't need or care about.
With SQLite you’re looking at third party extensions that compress the data, still in row-oriented format, and might rather inefficiently recover some benefit. But WAL probably does help with the write amplification above and beyond this.
journald-style logs really want a column store IMO. It would be highly entertaining to try something like ducklake with SQLite as the catalog — the whole stack is pretty lightweight and there’s support for inlining inserts in the catalog to avoid creating silly numbers of Parquet files.