Offline first, because the site has no network

What building Batipro and Officine taught us about local databases, sync, licensing, and the bugs that only exist on a real machine.

Seven in the morning, a chantier outside the city. The chef de chantier walks through the gate, and his phone loses the last bar. He will take attendance, log two deliveries of cement, photograph a cracked beam and note three expenses before he sees a network again at half past six, on the office Wi-Fi. Behind a pharmacy counter across town, a PC that has never had a server runs the registers of the whole shop. Both of them run our software, and both taught us the same lesson from opposite ends.

0
network calls on the write path: 51
schema migrations after two months of daily use: 14 d
trial computed on the device, no backend: 0
rows lost in the rename incident, thanks to one test

The site has no network. The software has.

Fig. 1.A day on the site: the phone loses the network at the gate, the outbox fills all day, and everything leaves in one go from the office at night.

The release checklist for Batipro ends with two real-world tests: a real payroll run, and one person syncing from a site with no signal. That second test is the whole product in a sentence. Nothing a worker does on site may depend on a network being there, and nothing they did all day may be lost when it comes back.

So every write lands in SQLite immediately. Attendance, materials, expenses and photos are local facts the moment they are entered. The network is not slow or unreliable in this design; it is simply not on the path. When the phone reaches the office at night, an outbox drains in order, and the day leaves in one go.

Where the truth lives

Fig. 2.Where the truth lives: every device holds the whole database; the cloud, when there is one, only shuttles rows.

The README of Batipro says it in one line: all data lives in an on-device SQLite database, through Drift, and the app works fully offline. The cloud is an optional, last-resort layer used only when backup or sync is wanted. That ordering matters. When the local database is the primary store, every screen, every report and every join runs against SQLite on the device, and the server, if there is one, only shuttles rows.

Officine goes further and has no sync at all in its first version: one user, one machine, one encrypted file. On Windows the usual Flutter sqflite package does not work, so the app initialises sqflite_common_ffi and swaps the database factory before anything else runs. Migrations live in a single versioned helper with onCreate and onUpgrade, and the schema starts small: customers, doctors, credits, credit payments, prescriptions, orders, medicines, with an index on medicine expiry because expiry alerts are the feature pharmacists asked for first.

Batipro's schema is at version 51 today. Fifty-one migrations is what a real product looks like after two months of daily use, and it is only survivable because migrations were versioned from the first one.

The folder that ate the pharmacy

Fig. 3.The folder that ate the pharmacy: a renamed product points at an empty directory with no error. The fix copies the legacy database forward and leaves the original untouched.

This one cost us a scare. On Windows, the company and product names in the runner decide where the database lives, something like %APPDATA%\Officine\Officine\officine.db. Change either string, or the file name, and a new build points at an empty folder with no error at all. To the pharmacist, their records look deleted.

We renamed the product once, early on. The build now adopts the legacy database on first launch by copying it forward, never moving it, so the original stays in place as a fallback. Two tests pin this behaviour: one checks the brand identifiers never drift, one checks that a first-build database is carried forward without losing a row. The commit that closed the incident is titled exactly that.

“
Treat the storage location as a public contract. Write it down, test it, and make renames a migration, not a rename.
”
Batipro README

Sync is a merge, not a wire

Fig. 4.One write, one transaction: the row and its outbox entry land together; the server only orders and forwards; other seats pull.

When Batipro grew from one device to a small team, we designed sync around one sentence from our own notes: the difficult part of sync is the merge, not the transport. Outbox drain, ordering, conflict resolution, idempotent replay, schema migration across app versions. So the engine was built first against a LAN transport, and only later pointed at the cloud.

The design that came out of it:

Every write puts the row and an outbox entry in the same Drift transaction. A crash can never lose a pending write; offline, rows simply wait in the outbox.
The server assigns a monotonic serverVersion to every row it accepts. Conflicts are resolved last-writer-wins on updatedAt, and clients never compare clocks with each other.
The server is a row shuttle, not a query engine. It stores (table, rowId, JSON, serverVersion) and knows nothing about the schema. Reports, joins and aggregations stay local, where the full replica already is.
Photos and documents ride outside the socket, uploaded straight to object storage and keyed by row.
Plain row-level last-writer-wins is enough because teams are one to five seats and the modules barely overlap between roles. We wrote that justification down so nobody "improves" it into CRDTs later.

The relay only forwards ciphertext encrypted on the client, under a company key that never leaves the owner's machine. The backup envelope is built; delta encryption is not, and the status table in the repo says so in orange. Honest status tables are how a solo studio keeps a roadmap truthful.

A backend the customer owns

Why Appwrite for the sync layer? Because it can be self-hosted. Firebase and Supabase are cloud-only, which forces an international bank card on every customer, and an international card is exactly the obstacle that shaped our whole payment stack. So each Batipro customer gets their own Appwrite project in their own name, with three hosting options: Appwrite Cloud, a VPS in Algeria billed in dinars with a local invoice, or a machine in their own office. The app code is identical in all three. Only the endpoint URL differs.

A licence that works by phone

Fig. 5.A licence that survives a dead network: the trial is computed on the device, activation can be read out over the phone, and channels are set per licence, not per build.

A fresh install starts a fourteen-day trial computed locally, with no backend call and no signed token. When it lapses, the app falls back to a free tier instead of locking the user out. Activation talks to a small licensing API on Cloudflare Workers, but there is also offline activation: the user reads a request code out over the phone, the vendor console issues an activation code back. That path exists because the first pilots activated from places where the phone was the only thing with a signal.

Release channels are per licence, not per build. Every client asks for stable; the licence can override it to beta for one key. We deliberately did not ship a "beta builds" toggle in the app, because a toggle means one support call to turn it on and no way to know which build was running when something broke.

Installers are built by CI with Inno Setup, checksummed, and signed with an Ed25519 key. An unsigned installer must not be publishable, and a tag that is not on main is refused by the workflow.

Bugs that only exist on real machines

Our schema file used CREATE TABLE IF NOT EXISTS, so adding a column did nothing on an existing database. The deploy tool reported success, the column was not there, and one admin endpoint answered 500 against the real database while passing every unit test against the in-memory repository. The fix was a real migration script plus a test that fails the build on schema drift.
The app needs two build-time defines: the backend address and the public key. With only the first, it reaches the server and then refuses the licence it is handed. With only the second, it never leaves the machine. For a while both produced the same "activation failed" as a wrong key, which sends you to check the console and the licence, the two places the problem is not. Error messages now name the missing define.
A CSV export opened in Excel showed a stray character in the first cell. UTF-8 BOM. One line to strip it, one afternoon to find it.
Twenty-three editable fields destroyed their value when the interface was in English or Arabic, because the number formatter and the parser disagreed about the decimal separator. Locale bugs hide in the write path, not the read path.

If you build for the same places

Decide who holds the truth, device or server, and never argue about it again.
Put the write and its outbox entry in one transaction. Everything else in sync is easier than losing a write.
Build the sync engine against a fake transport first.
Pin the storage path, the identifiers and the migrations with tests. They are the parts a rename silently breaks.
Keep a status table with three colours and update it when something is not built.
Validate a release the way a customer would: a real payroll run, and one person syncing from a site with no signal.