Offline-first point of sale

A restaurant does not stop serving because the internet is down. That is the entire premise. Orders keep being taken, bills keep being paid, and the till keeps working, and none of that can wait for a connection to come back.

So the system has three tiers. ERPNext is the central system of record, holding stock, accounting and master data across sites. A Django server with a SQLite database runs at each site and is what the terminals actually talk to. The terminals are Electron and React at the counter.

Only the middle tier needs the internet, and only eventually.

The tier boundary is a write-authority boundary

The interesting decision in this system is not in the sync code. It is what each tier is allowed to own.

The central server owns centralised stock, accounting, and master files. The local server owns working documents: orders, bill payments, shift records, cashier day-end reports, stock takes. Those sets do not overlap, and that is deliberate.

The consequence is that the conflict resolver in this codebase is an empty class. Not an unfinished one, an unnecessary one. If two tiers can never be the authority for the same field, a merge conflict has nowhere to come from. Menu prices flow down and are never edited locally. Orders flow up and are never edited centrally.

I would rather design a conflict away than write a resolver that has to be correct at two in the morning when a site has been offline for a day and a half. The honest caveat, which is written next to it, is that this holds only as long as the boundary holds. The first feature that lets a manager edit stock from the terminal reintroduces the entire problem, and that empty class becomes a real one.

The sync engine

Every local model that needs to reach the server inherits a syncable base that enqueues it automatically when it saves. There is no separate step where somebody remembers to mark a record dirty, because that step is where records go missing.

The queue tracks sync state, retry attempts, the serialised payload, error messages, priority, and dependencies. Dependencies matter more than they sound: an order line refers to a menu item, which may itself refer to another menu item, and pushing a document before the things it points at just produces a server-side error at the far end of a slow connection.

Four loops drain that queue concurrently, at deliberately different cadences. A critical loop runs about every second for high-priority documents, because a completed payment should not sit in a queue. A batch loop runs every thirty seconds for everything ordinary. A downstream loop pulls changes the other way, for the master data the central server owns. A health loop watches whether there is a connection at all.

The terminals themselves never talk to the central server, only to the local one. That is what makes the offline case boring: from a terminal’s point of view, nothing is different during an outage.

Fingerprints, through C++

Staff authenticate at the counter with a fingerprint reader rather than a password, because a shared terminal and a typed password converge on one password everybody knows.

The reader’s vendor SDK is a native Windows library, and the terminal is Electron. So the bridge is a C++ Node addon compiled through node-gyp against node-addon-api, linking the vendor library, exposing capture and match to JavaScript. This is the least glamorous code in the project and one of the more satisfying: a Node process, a C++ boundary, and a piece of hardware, all needing to agree about who is standing at the till.

The part that actually cost the time

None of the above was the hard part. The hard part was SQLite telling us the database was locked.

SQLite locks at the file level, and a sync engine running four concurrent loops against the same file is close to the worst case for that. The first round of fixes was the standard set: write-ahead logging so readers and writers stop blocking each other, a twenty second busy timeout instead of the default five, a retry decorator with exponential backoff around every critical write, and breaking one transaction per batch into one transaction per document so locks are held for milliseconds rather than minutes. Batch size is an environment variable, because these servers are whatever hardware the site already had.

That fixed the errors and the sync still hung, and chasing that produced the sequence I would actually show someone:

Reads were blocking too. Serialisation code called .exists() before fetching child rows, so every document produced several extra queries, each able to block on a write lock. Fetching once and testing the result removed both the queries and the stall.

Menu items pointed at each other. A menu item can carry a side, and a side can carry one back. Dependency collection serialised each linked object as it walked, so a circular pair sent it in circles doing database work each time. Deferring serialisation until a dependency is actually added to a payload fixed it, and made the walk cheaper for everything else.

Django was holding a lock for the whole run. ATOMIC_REQUESTS was on, which wraps operations in a transaction. The sync command runs for minutes or hours. Every read anywhere else on the box was waiting behind one transaction that would not end until the sync finished. Turning it off for that command alone, and letting the sync service manage its own small transactions, was a two-line change after a long hunt.

And then the queries nobody had wrapped. A lookup here, a prune there, an existence check inside a save. Each one able to hang, none of them covered by the retry logic, all found one at a time by running the thing until it stopped again.

The pattern across all four is the same. Every fix was correct and none was sufficient, because the symptom was identical each time and the causes were unrelated. That is the shape of a contention bug, and the only reason it ever finished is that each round was written down as it was found rather than fixed and forgotten.

Where it is

Running in client sites, developed with a team of four, with me leading. The migration path to Postgres is documented and deliberately not taken: SQLite on a low-powered machine with WAL and honest transaction discipline is doing the job, and adding a database server to every site is a support burden somebody has to carry.

The write-authority boundary is the thing I would defend and also the thing I would watch. It is the reason this system is simple, and it is one feature request away from not being.