Building Offline-Resilient POS Systems with Database Replication

The Challenge of Network Outages in Retail

For point-of-sale (POS) systems operating in busy environments, even a few minutes of network downtime can lead to lost transactions, delayed checkouts, and customer frustration. Devising a database model that works independently of constant cloud connectivity is essential to keep cashier queues moving and ensure transactions are recorded reliably. An offline-resilient architecture allows sales registers to function smoothly during network drops, synchronizing data when the connection returns.

"Resilience in POS means zero interruptions at the cashier terminal, regardless of the internet status. If your cashier is stuck because of a slow web service, your architecture has failed."

Traditional web-based applications require an active connection to function. A modern POS, however, must adopt an offline-first strategy. This is achieved by storing critical data locally on each register, using browser databases or lightweight local databases, and syncing updates with a central server in the background.

Local Database Replication Strategy

Our architecture utilizes local lightweight databases on each checkout terminal. The primary strategy consists of these features:

1. SQLite Local Caches: Sales entries, cashier shifts, and customer details are stored locally on terminal SSDs immediately, ensuring instant page response times.
2. Conflict-Free Replicated Data Types (CRDTs): Ensures data consistency when combining multiple terminal logs into the master database, preventing record overwrites.
3. Back-end Reconciliation Services: Auto-detects network status changes, queueing up outgoing sync payloads, and executing data transfers in the background.
4. Local Backup Snapshots: Automated hourly local database backups prevent data loss in the event of local hardware failures.

Sync Reconciliation Trigger Logic

Below is a simplified example of the JavaScript loop monitoring status changes to trigger a sync sequence:

Safeguarding Data During Synchronization

When designing offline database structures, prioritize transactional integrity:

1. UUIDs over Auto-Incrementing IDs: Assign primary keys using UUIDs to prevent primary key conflicts during merge sequences. Auto-incrementing integers can lead to duplicate keys when multiple registers create orders offline.
2. Log everything chronologically: Keep absolute logs of timestamps, cashier IDs, and transaction states to resolve conflict records. If two registers update the same stock item, use version vectors to determine the correct state.
3. Automated integrity hashes: Hash local data packages before transmission to verify packet safety. The backend validates these hashes before merging them into main ledgers, preventing corrupted entries.
4. Two-Phase Commits: During data sync, use a two-phase commit protocol. The central server must confirm receipt and validation of transactions before the local terminal is permitted to clear its offline logs.

Detailed Synchronization Workflow

To establish sync, the terminal constantly polls network status. Once online, the system begins a handshake: it queries the master API to verify structural schema alignment. Following verification, the local client bundles unsynced sales transactions, computes check hashes, and transmits the package. The master database parses the package, processes inventory deductions, registers payments, and saves the entries. Finally, the master returns a list of successfully integrated record IDs. The client compares this list with its local storage and prunes successfully synced logs, freeing up memory.

Conflict Resolution Rules

When conflicts occur—for instance, when a product price is updated on the cloud while a register is processing offline sales—you need strict conflict resolution rules. Implementing a Last-Write-Wins (LWW) policy based on synchronized atomic clocks works for retail. For inventory records, however, relative calculations are preferred: instead of overwriting quantities, subtract sold quantities from the central warehouse total during merge sequences, keeping calculations precise.

Sajjan Studio deploys offline-first POS systems across prominent restaurant and retail chains, guaranteeing smooth operations regardless of connectivity drops. This eliminates cashier frustration and protects corporate financial records from getting mismatched or missing.