I've always assumed sqlite database files are resilient to being copied, mid-write. Sure: maybe that last transaction will be corrupted, but everything up to that point will be in tact, right? The next backup will capture whatever was in the half-complete transaction.
If you make an atomic copy -- like something equivalent to a power failure, then it's transactionally safe. One way this can be done is with a snapshot-capable filesystem like ZFS, btrfs, or XFS.
But a traditional `cp` will go from left to right in the file, it won't take the contents all at once. Which is explicitly documented as a thing that will break Sqlite - https://www.sqlite.org/howtocorrupt.html
Litestream author here. Your backup can be corrupt if you simply use "cp" but the details depend on your journaling mode.
If you're using the default rollback journal mode, it works by copying old pages to a "-journal" file and then updating the main database file with new pages. Your transaction finally commits when you delete the journal file. Copying the main database file during a write transaction can give you either a subset of transaction pages or half-written pages.
If you're using the WAL journaling mode, it works by writing new pages to the "-wal" file and then periodically copying those pages back to the main database file in a process called "checkpointing". If you only "cp" the main database file then you'll be missing all transactions that are in the WAL. There's no time bound on the WAL so you could lose a lot of transactions. Also, you can still corrupt your database backup if you "cp" the main database file during the checkpoint.
You could "cp" the main database file and the WAL file and probably not corrupt it but there's still a race condition where you could copy the main file during checkpointing and then not reach the WAL file before it's deleted by SQLite.
tl;dr is to just use the backup API and not worry about it. :)
Am I deluded in this?