How SQLite backups work
How Rowsafe gets access to a SQLite file, copies it safely next to your app, and restores it.
A SQLite database is one file your app opens directly; there's no server. The agent joins as one more careful connection on the same server. Nothing passes through Rowsafe's service. Setup: Protect SQLite.
One file, one database
Each SQLite file is one database in Rowsafe. A server often has several (Rails 8 keeps its cache and job queue in separate files): add each one you'd want back. You only name the database file; Rowsafe handles its helper files too.
| File | What it is |
|---|---|
production.sqlite3 | The database. |
production.sqlite3-wal | In WAL mode: changes committed but not yet written into the database file. |
production.sqlite3-shm | In WAL mode: a small shared-memory index that every connection uses to find its way in the -wal file. |
Getting access, without changing ownership
The agent runs as its own user, rowsafe. At install, root gives it read and write access to the file, its -wal and -shm files and their folder:
- with a POSIX ACL on each, plus a default ACL on the folder for helper files created later;
- or, without ACL support, by adding
rowsafeto the file's group, when that group is the app user's own and can already write it.
The installer prints what it changed. The owner never changes; everyone else's access stays the same. In Docker, the agent runs as your app's user, so nothing changes.
Write access is needed because SQLite readers write too (locks, the -shm index), and for checkpoints, bringing rows back and rewinds when someone asks.
Safe next to your app
To SQLite, the agent is one more connection following the same locks.
- Backups use SQLite's online backup API: a consistent copy of one moment, even while your app writes.
- WAL mode: your writes never wait for Rowsafe's reads.
- Rollback-journal mode: a write may wait a moment, as behind any reader.
- On a server, the agent gets lower CPU and IO weight than your app. When the file is busy it waits and retries; Pulse counts those moments.
Network drives (NFS, SMB/CIFS, sshfs and similar) are refused: their locks aren't reliable.
Backups
Daily by default: a full copy through the online backup API, compressed and encrypted on your server with your repository passphrase (ROWSAFE_REPO_CIPHER_PASS), then uploaded. Without the passphrase, nothing can be restored.
Each copy passes SQLite's quick_check before it counts, and each table's row count is recorded for Proof.
Every change, in WAL mode
In WAL mode (journal_mode=WAL, Rails 8's default), SQLite appends each transaction to the -wal file; a checkpoint later writes them into the database file.
- Every few seconds, the agent uploads newly committed whole transactions as an encrypted, time-stamped piece.
- It holds a read transaction so your app's checkpoint can't overwrite changes not yet copied.
- About every ten seconds, it takes the write lock for a few milliseconds and checkpoints what it copied, so the
-walfile doesn't grow. Changes wait in a small bounded spool until every bucket has them; after a long outage, Rowsafe takes a fresh full copy.
Your bucket is usually seconds behind; the dashboard shows how far.
When the chain breaks
These break the chain of changes: the agent stopped while your app reset its -wal file, your app ran a TRUNCATE or RESTART checkpoint, the page size changed, or the file was replaced. Rowsafe starts again with a fresh full copy by itself. Moments before the break stay restorable. (VACUUM doesn't break it.)
If your app runs a TRUNCATE or RESTART checkpoint, Rowsafe lets go after about two seconds. Better to leave checkpoints to Rowsafe.
Without WAL mode
In rollback-journal mode there's no change stream: daily backups only, no Marks. Pulse offers Turn on continuous backups to switch to WAL mode. Your app keeps working, -wal and -shm files appear, and it stays that way unless your app switches back.
Restoring to a second
Rowsafe takes the newest backup before your moment and replays every transaction up to it (or exactly to a Mark) into a new file. Changes are stamped within about a second of commit.
- Proof (weekly) restores into a scratch file, runs
PRAGMA integrity_checkandPRAGMA foreign_key_check, compares row counts with backup time, then deletes it. - A Rewind copy sits next to the live file. Rowsafe compares by primary key (or row number) and puts missing rows back in one transaction, never overwriting an existing row.
- Rewinding the whole database writes the restored file into the live one through the online backup API while your app runs; writes wait a few seconds. The old file is kept 7 days for Undo.
Finding the moment
The change stream holds pages, not statements. For Find the moment, the agent restores the file at the start of the range into a private scratch file and replays transactions one by one, comparing rows (as hashes) by primary key or row number: gone means deleted, different means changed, a missing table means dropped. Only table names, times and counts reach Rowsafe. "Just before" is the millisecond before Rowsafe copied the change.
Copies for everything else
Migration previews, safe copies and clones all start from a file restored from your bucket, never the live one. A preview runs your migration on it; a safe copy masks it (or keeps only the schema) in a folder only the agent can read; a clone goes in a folder root allowed and becomes a new database. Recommendations and index advice read only the live schema, and test an index on a restored copy first.
Health
About every minute: file size, -wal size, unused pages, journal mode, how far behind backups are, plus the last integrity check and busy moments. About every 30 minutes: which tables' query statistics are stale. Only these numbers, the path and table names reach Rowsafe. Findings and fixes: Protect SQLite.