How to make a web app work offline: SQLite in the browser

Week four. Before sharing, before the family features, before anything that could reasonably be called an app, we stopped and put a real SQLite database inside the browser.

It is a strange thing to do that early. There was barely a product to take offline. But offline-first can't be bolted on afterwards. It decides who owns the data and where the truth lives, and every screen written before that decision has to be rewritten after it.

What "offline" usually means#

Most apps that claim offline support mean one of two much smaller things.

Three things "offline" can mean

Read-only cache

you can look at what you already loaded, and every button is dead.

Optimistic queueing

you can act, the action goes into a list, and the list replays when the network returns. It works until two devices queue conflicting things and one of them silently loses.

A real local database

what we built. Queries, transactions and relational integrity in the browser, with sync as reconciliation between two databases.

Neither is what I wanted, because neither survives the test I actually care about. Not "can I read my notes on a plane" but "can I work on a plane, for four hours, creating and editing and reorganizing, and land with all of it intact and merged."

That requires the local copy to be a real database rather than a cache. Real queries, real transactions, real relational integrity, running in the browser with no network at all. Then sync is a background reconciliation between two databases, instead of a queue of intentions hoping to be replayed.

So the UI reads and writes locally. Always. Even with five bars. The network is never in the path of an interaction, which as a side effect makes the app feel instant, though that was not the point.

The bill#

It arrives immediately and it is large.

Every table you want offline has to exist in seven places:

1. the Postgres schema,
2. the migration,
3. the SQLite mirror,
4. the sync rules that decide who may read it,
5. the ordering list that respects foreign keys,
6. the write-authorization layer,
7. and the server-side writer that actually stores a change from a device.

Miss one and you do not get an error. You get a table that is simply absent on the client, silently, for the users whose data lives in it.

We learned that one the way everyone learns it. A feature worked perfectly in development and was inexplicably empty in production, because its table had been left out of some of those places. There is now a checklist, and a CI script that checks three of the seven against each other on every push.

We were testing it wrong#

Here is the part that still bothers me.

For months, the way we verified offline behavior was the obvious way: open the browser devtools, tick "Offline", use the app.

That does not test what you think it tests. The devtools offline toggle applies to the page's own requests. It does not apply to fetches made by the service worker. So the page believes it is offline, behaves accordingly, and meanwhile the service worker is quietly serving everything over a perfectly live network connection.

You can run that test a hundred times, watch it pass a hundred times, and learn nothing whatsoever about whether your app works on a plane.

The real answer came from an actual device in actual airplane mode, and what it showed was that the app was dead. Not degraded. Dead, on launch, showing nothing at all.

The cause was one file. The precache manifest listed everything the app needed to boot without a network, and it did not list the WebAssembly binary that the database itself is compiled into.

No WASM, no SQLite. No SQLite, no local data. No local data, no app.

A single missing entry in a generated list, invisible to every test we had, turning the entire offline promise into a lie. It had been that way for a while. Nobody could have caught it in devtools, because devtools was the instrument that was lying.

What changed after#

Two things, and only the second one matters.

The obvious fix was to add the file to the precache and verify the manifest includes it on every build.

The real fix was to stop trusting any test whose failure mode is a false pass. Offline claims now get verified on a device in airplane mode, or by an /etc/hosts block, or by reading the cache contents directly. The devtools toggle is treated as what it is: a convenience for checking your own request handling, and evidence of nothing else.

That generalizes well beyond offline. Failing tests get fixed the same day. The ones that worry me now pass while measuring something next to what I believe they measure. The only way I have found them is to ask what a false pass would look like, and then check whether that is what I have been staring at.