Writing/SQLite
Two databases, and a boundary you never cross
An app that ships its own content has a problem: every update replaces that content, and the user's notes are sitting in the same file.
I am building an offline-first reader that ships its text inside the app. No accounts, no network, no ads. Which raises a question most apps never have to answer: what happens to a person's highlights and notes when the text they attach to is replaced by an update?
The convenient answer is one database with a migration that carries the notes across. The correct answer is two databases and a rule that nothing may ever cross between them.
One is disposable, one is irreplaceable
The content database is read-only, bundled with the binary, and replaced wholesale on every app update. It is disposable by design. If a build corrupts it, the next build fixes it, and nobody loses anything.
The user database lives in app documents, is never shipped and is never replaced. It holds highlights, notes, bookmarks and reading position. It is the only thing in the app that cannot be regenerated, and it belongs to a person rather than to a release.
“An update that can reach the user's notes is not an update. It is a risk you ship on a schedule.”
The rule that makes this hold is that no migration may copy across the boundary. The moment one does, the two files are coupled, and an update is capable of destroying years of somebody's annotations. Notes reference content by stable identifier rather than by row, so the content underneath can be replaced without the reference meaning anything different.
Testing SQL by running it
The repositories take a small database interface rather than reaching for the platform SQLite library directly. That is not layering for its own sake. It means the real SQL, exactly as written, runs in tests against an in-process SQLite rather than being mocked out.
Mocking a database tests that you called your own wrapper correctly. It cannot tell you that a query is wrong, and a query being wrong is the entire risk. This way the tests are slower by a few milliseconds and are testing the thing that can actually break.
One detail worth stealing: full-text search is configured with diacritics removed, because a Romanian reader will type a word without its diacritical marks and expect to find it. That is the sort of thing you verify empirically against real text rather than assume from documentation, and it is why the search spike existed before any screen did.
