Off-line POS

POS (Point-of-sale) apps need to work even when there is no internet connection. For this reason we say that POS apps are "off-line first": they work locally even without internet connectivity, but when the connection is re-established they can replicate data to remote servers or even other stores.

POS database

As they need to work off-line, the POS apps cannot use a client-server database engine (DBMS) on a cloud or on remote servers, they need to use a local database.

If the store has a single device to register sales, then it can use SQLite. But when the store uses many devices with the Point-of-sale application, then it needs to have replication of the SQLite database.

POS database replication

Many mobile POS systems use SQLite as the database engine to be able to work off-line.

The replication of SQLite databases can be done using tools like LiteSync. It will replicate the database in all the local devices, working off-line, and when on-line the database can also be replicated to a backend (web-site, management) or even to other stores.

Preparing the POS app for replication

You must pay attention to the stock control, the available quantity for each product. As each device has a local copy of the database, if 2 sales happen at the same time both may get the same amount of stock, and both will modify the stock at the same time, each one in its local database first, to later be synchronized with the other devices.

In this case, avoid setting the remaining stock amount with a command like:

UPDATE products SET qty = ? WHERE id = ?

Because both nodes would set the same value.

Prefer instead to decrease the total amount using a command like:

UPDATE products SET qty = qty - ? WHERE id = ?

Where the first ? is the amount being purchased.

When the synchronization takes place, the database will end up having the correct stock amount for each product, on all the devices.

Conflicts

Conflicts can happen on replicated databases.

To avoid conflicts on INSERTions, we recommend to use tables with INTEGER PRIMARY KEY. Replication tools generally use a special numbering for the inserted rows to avoid conflicts.

If the tool you choose does not support such feature, you can use random numbers for the row ids. Although this does not remove the chance of conflicts, it only decreases the probability of happening.

If you still need to use tables with text or multi-columns as the primary key, you can use a prefix that is unique for each node. In the case of text columns the prefix would be as string, and in multi-column primary keys you can use the first column as the prefix.

For conflicts on UPDATEs, generally the last UPDATE will overwrite values from previous UPDATES. This is the reason of the recommendation on the previous section.

Programming languages for Mobile POS

The POS application can be written in native language for the desired mobile platform or using a framework.

For Android we can use Java or Kotlin languages and the Android Studio as the IDE.

For iOS we use Swift and the Xcode as the IDE.

For cross-platform development we can use React Native (Javascript), Flutter (Dart) or Xamarin (C#).

We can have SQLite database replication with all the above options when using LiteSync.