Vaibhav More Logo
VAIBHAVMORE
Systems Architecture
6 min readOct 15, 2024

Building Offline-First Desktop POS Systems: Why SQLite WAL Mode Beats Cloud Databases for Retail Intermittency

Architectural insights from engineering AutoBillDrive: achieving sub-150ms bill dispatch with zero lockups during internet outages.

VM

Vaibhav More

Full-Stack Developer & Systems Engineer

#Electron.js#SQLite#WAL Mode#Offline-First#Architecture

In modern retail engineering, developer intuition often pushes teams toward cloud-first architectures: an API gateway communicating with a managed serverless database. However, in tier-2 and semi-urban commercial retail, network intermittency is not an edge case—it is a daily operating reality. When a billing terminal freezes waiting for an HTTP handshake while a customer waits, trust evaporates.

The Pitfall of Default SQLite Journaling in Multi-Threaded Desktop Apps

When running an Electron desktop application where the main process triggers thermal PDF generation while background worker threads simultaneously write audit logs and barcode scans, SQLite's default rollback journal introduces severe reader-writer locks.

Under traditional rollback journaling, whenever a transaction begins writing to the database file, an exclusive database lock prevents any concurrent reads. Cashiers typing item SKUs would experience micro-stutters because the invoice generation routine held an exclusive write lock.

Database Lock Contention

In default journal mode, writers block readers and readers block writers. In a busy checkout lane doing 4 actions per second, this creates catastrophic UI blocking.

Enabling Write-Ahead Logging (WAL) for Concurrent Non-Blocking Throughput

By enabling PRAGMA journal_mode = WAL;, SQLite inverts this dynamic. Changes are appended to a separate -wal file rather than directly overwriting the main database pages. Readers continue reading the immutable main database pages and recent WAL frames without acquiring table-level locks.

This architectural switch reduced our read latencies from ~35ms under write load to an astonishing <2ms, allowing instant barcode lookups while bulk receipt history compiles in the background.

db/connection.tstypescript
// Initializing SQLite with Write-Ahead Logging & synchronous optimizations
import Database from "better-sqlite3";

export function initializePOSDatabase(dbPath: string) {
  const db = new Database(dbPath, { timeout: 5000 });
  
  // Enable WAL mode for high concurrency
  db.pragma("journal_mode = WAL");
  
  // Normal synchronous balance between durability & disk IO
  db.pragma("synchronous = NORMAL");
  
  // Cache size allocation: 64MB memory page cache
  db.pragma("cache_size = -64000");
  
  // Memory-mapped I/O for lightning fast read operations
  db.pragma("mmap_size = 30000000000");
  
  return db;
}

Asynchronous Background Sync with Cloud Buffering

Offline durability is useless if data never reaches enterprise reporting. We designed an idempotent sync queue. Every invoice transaction produces an append-only JSON event record in a local sync_queue table.

A detached worker process monitors system connectivity via network interface socket probes. When connectivity is established, batches of 25 transactions are compressed and dispatched to the Google Drive backup vault using exponential backoff with randomized jitter.

Key Architectural Takeaways

  • SQLite WAL mode decouples readers from writers, enabling non-blocking POS checkout loops.
  • Local memory-mapped I/O (mmap_size) delivers microsecond SKU queries without socket overhead.
  • Append-only sync queues with exponential backoff guarantee zero data loss during network restoration.

Conclusion & Next Steps

Building local-first requires rethinking cloud dogmas. By treating the local device as the primary source of operational truth and the cloud as a replicated backup sink, AutoBillDrive achieves true 100% offline availability with sub-150ms receipt generation.

More Engineering Deep Dives