Mobile app database design starts with one question about where rows live.

Which rows belong on the phone

The starting point of an app database schema is not normal form, it is reachability. A phone loses the network in lifts, on trains, in basements and abroad. Any screen that has to render without a server needs its rows already on the device, which makes the on-device store part of the design rather than an optimisation you bolt on later.

For anything past a handful of settings, that store is SQLite. Expo ships expo-sqlite, which opens a database file on the device and keeps it across app restarts. It documents async methods such as openDatabaseAsync, runAsync and getAllAsync, plus synchronous versions that the documentation warns can block the JavaScript thread and hurt performance. A key-value store is fine for a token or a theme preference and wrong for a list the user filters, sorts and searches.

There is a third option people skip past. A workout log, a packing list or a site notebook for one person can live entirely on the phone, with no server at all. You lose the data with the phone and you cannot share it, and in exchange you get no accounts, no hosting bill and no sync bugs. Be honest about which of the two you are building, because designing a mobile database for one device and then adding accounts later is close to a rewrite of the data layer.

Expo documentation, expo-sqlite

The four columns a synced table needs

A server-only schema can use an auto-increment integer key, because only the server inserts rows. A mobile schema cannot. A row created in a tunnel needs an identity straight away, so the screen can show it, attach a photo to it and let the user edit it again before anything reaches the server. Generate a UUID on the device and treat that as the real primary key.

Then a clock. Every table gets created_at and updated_at, and updated_at is rewritten on every change, because it is the only way to ask the server what has changed since the last successful sync. Without it every sync is a full table download, and the app gets slower as the data grows rather than as the traffic grows.

Deletion is the one that catches people. Remove the row and the other device has no way of learning it is gone: it still holds its copy, and the next push puts it back. So a synced table deletes by writing deleted_at, filters it out of queries, and only really removes the row later, once both sides agree. The rest of that machinery is mobile app offline sync, which is a design problem of its own.

The fourth column is owner_id, or org_id, or both. It looks like bookkeeping next to the others. It is the column every access rule reads, and adding it after a few hundred rows exist means a backfill and a re-test of every query you have written.

Access control is a schema decision

On a phone, a query leaves a device the user controls. Anything the app is allowed to ask for, a determined person holding that phone can ask for too, with the app's own credentials. That is why mobile schemas push access rules down into the database instead of keeping them in screens and API handlers.

In Postgres the mechanism is row level security. You enable it per table, then write policies that decide which rows a role can see and change. The default is the useful part: with row level security enabled and no policy written, the documented behaviour is default deny, so no rows are visible and none can be modified. A table you switched on and then forgot about is closed, not open. One caveat before you test anything: superusers and roles carrying BYPASSRLS always bypass row security, and a table owner normally bypasses it too unless that table is set to force it. Testing your own policies as the owner will tell you nothing.

This is the practical reason owner_id is not optional. A policy can only compare columns that exist on the row, so a rule like people see their own orders is a schema requirement before it is a rule. Anything with layers, a manager who sees a team, an admin who sees everything, becomes app user roles permissions and needs a membership table the policies can join to.

Worth stating plainly if you build with a tool rather than by hand. A Newly app keeps its data on the device by default and has no server database at all until the app needs accounts, sharing or sync. When it does, the backend it adds includes a Postgres database per environment, a dev one and a separate production one, so the row level security model above is exactly the one that applies. It does not connect to Supabase or Firebase. The documentation does not name a Postgres host or version, and we have not verified either, so do not plan around a specific one.

PostgreSQL documentation, row security policies

Migrating a schema on devices you do not control

A server migration runs once, under your eye. An on-device migration runs on every phone, whenever that phone next opens the app, and some of them will not open it for a year. You never get to choose which version a device is coming from, so the on-device path has to run forward from any older version, in order, without skipping.

SQLite has a place to keep the count: the user_version pragma, a number stored inside the database file, which Expo uses in its own migration example. You read it when the database opens, run each step numbered above it in sequence, then write the new number at the end. Those steps are append-only forever. Editing an old step does not change what already happened on a device that ran it, it just makes two devices disagree about what version 3 means.

The server half of the same problem is that old app builds keep talking to your current API. Dropping a column or renaming one breaks every phone still on last month's build, so additive changes are the rule: add the new column, write both for a release, stop writing the old one once the old builds are gone. Adding a NOT NULL column with no default is the classic way to break inserts from clients you cannot update.

Where this stops being schema design and becomes screens, queries and forms is a database app in practice.