Building Offline-First POS Systems in Go with SQLite: Architecture and Sync

Most restaurants and retail operations in Lebanon and across MENA experience internet outages regularly. A POS system that goes down when the internet drops is not a POS system: it is a liability. This is the architecture we use to build offline-first POS backends in Go with SQLite, including how we handle the sync process that brings local and remote state back into agreement.

Why offline-first is a hard requirement, not a nice-to-have

In Lebanon, power outages take routers offline multiple times a week in many areas. In Jordan and Egypt, POS terminals in markets and food courts operate on mobile internet connections that drop during peak hours. In Gulf shopping malls, shared enterprise networks impose unpredictable latency spikes that cause cloud-first POS systems to hang at exactly the wrong moment.

A POS system built on a cloud-first assumption fails when the operator needs it most: a busy lunch rush, a peak sales hour, a Friday night in a restaurant district. The solution is not to improve the network. The solution is to design the system so the network becomes optional for all core operations.

Sales, inventory deductions, receipt printing, cash drawer control, and end-of-day session reporting must all work without any remote connection. Cloud sync happens when the connection is available, not as a hard dependency.

The local database layer with SQLite and WAL mode

Each POS terminal runs a local SQLite database embedded in the Go application binary. SQLite is the correct choice for this layer for specific operational reasons: it runs in-process without a separate database server, stores data in a single portable file, survives unexpected process kills without corruption when configured in WAL (Write-Ahead Logging) mode, and adds no external runtime dependencies to the terminal application.

Enable WAL mode immediately after opening the database connection:

db, err := sql.Open("sqlite3", "./pos_local.db?_journal_mode=WAL&_synchronous=NORMAL")

The schema mirrors a subset of the central PostgreSQL schema with two critical additions: every syncable table has a local_id column (UUID v4 generated locally on insert) and a synced_at column (nullable timestamp, set to NULL until the central API acknowledges the record).

CREATE TABLE IF NOT EXISTS sales (
  local_id    TEXT PRIMARY KEY,
  remote_id   TEXT,
  tenant_id   TEXT NOT NULL,
  terminal_id TEXT NOT NULL,
  total_amount INTEGER NOT NULL,
  created_at  TEXT NOT NULL,
  synced_at   TEXT
);

All local inserts set local_id to a UUID v4 generated in Go using github.com/google/uuid, leave remote_id NULL, and leave synced_at NULL. The sync process fills both fields after the central API acknowledges the record.

Never use auto-increment integer primary keys in a local-first database schema. Sequential integers generated locally on two terminals will collide when both try to sync to the same central PostgreSQL table.

The sync loop architecture

Sync runs as a background goroutine within the terminal application. It wakes on a configurable interval (default: 30 seconds when online) and immediately when the device detects network connectivity returning after an outage.

The sync loop steps in order:

1. Query all records with synced_at IS NULL across each syncable table.
2. Group them into batches of maximum 100 records per request.
3. POST each batch to the central sync API endpoint as a JSON payload.
4. The API returns a mapping of local_id to remote_id for each accepted record.
5. Update each local record with its remote_id and set synced_at = NOW().
6. On network failure or 5xx response, log the attempt and retry on the next interval.

type SyncBatch struct {
    TerminalID string     `json:"terminal_id"`
    TenantID   string     `json:"tenant_id"`
    Sales      []SaleLine `json:"sales"`
}

type SyncResult struct {
    LocalID  string `json:"local_id"`
    RemoteID string `json:"remote_id"`
}

type SyncResponse struct {
    Accepted []SyncResult `json:"accepted"`
    Rejected []struct {
        LocalID string `json:"local_id"`
        Reason  string `json:"reason"`
    } `json:"rejected"`
}

The central API endpoint uses the local_id as an idempotency key. If a record with the same local_id already exists in PostgreSQL (because the terminal retried after a timeout), the API returns a success response with the existing remote_id without inserting a duplicate.

How to handle conflicts without overcomplicating the model

The most common conflict in a multi-terminal restaurant: the same product code is rung up on two terminals at the same moment. Both terminals assign different local_id values, so there is no conflict at the database key level. Both records sync independently and correctly.

The more operationally significant conflict: a price update is pushed from the central system while a terminal has pending unsynced sales recorded at the old price. The resolution rule must be an explicit business decision, not a technical default. Our standard is: pending unsynced records are never retroactively repriced. They sync at the price they were recorded at. Price changes apply only to sales recorded after the terminal downloads and applies the new catalog version.

This is the operationally correct behavior for accounting reconciliation. An end-of-day Z report must match what the cashier collected, at the prices the cashier charged.

Product catalog sync from central to terminal

Sync is bidirectional. The central system pushes product catalog updates, price changes, and tax configuration down to each terminal. The terminal maintains a local catalog_version value. During each sync loop, the terminal sends its current catalog_version to the central API. If the central system reports a newer version, the terminal downloads a full catalog diff and applies it atomically before the next transaction is processed.

The catalog update is atomic in the SQLite database: begin a transaction, delete all catalog rows, insert the new catalog rows, commit. If the terminal process dies mid-update, the transaction is rolled back and the old catalog remains intact. The terminal downloads the update again on the next sync.

Common failure patterns in naive implementations

Using sequential integer PKs locally. Two terminals both auto-increment from ID 1. When synced, records collide. Fix: always use UUID v4 generated at insert time on the terminal.

Not using WAL mode and explicit transactions. A terminal process killed mid-write (power outage, crash) leaves the database in an inconsistent state. Fix: WAL mode plus explicit BEGIN and COMMIT on every write.

Marking sync status per batch rather than per record. If the central API accepts 90 of 100 records in a batch, the 10 rejected records must still be retried individually. Marking the entire batch as synced silently loses data. Fix: track synced_at per record, parse the rejected array in the API response, and leave rejected records with synced_at = NULL.

Not handling idempotency on the server side. A terminal that retries a sync after a timeout may submit the same records twice. Without idempotency, this creates duplicate sales in the central database. Fix: use local_id as an idempotency key on the central API and handle duplicates with PostgreSQL's ON CONFLICT DO NOTHING or equivalent.

Key lessons from production

Use SQLite WAL mode and explicit transactions for every local write. Use UUID v4 as the primary key on all local tables. Build the sync loop to track status per record, not per batch. Apply idempotency on the server side using the terminal-generated local_id. Define conflict resolution rules as explicit business decisions before writing any sync code. Product catalog updates must apply atomically or not at all.