From Spec to Production: Deconstructing the Schema and Sync Logic of Offline POS Systems

A point-of-sale system that tracks inventory using a single quantity field fails as soon as a business purchases stock in cartons and sells in loose pieces. Storing separate stock counts for each unit type forces the application to manually balance quantities, causing inevitable stock drift.

The correct relational approach stores stock in a single atomic base unit, representing the smallest possible sellable quantity. Packaging types such as cartons, packs, or boxes are modeled in a dedicated units table with exact conversion factors pointing back to the base unit.

When processing sales during a network outage, line items are calculated using the base-unit multiplier and recorded into an offline IndexedDB ledger. Selling two cartons at a 24-to-1 ratio immediately deducts 48 base units from local inventory state.

When the network reconnects, local transactions are submitted to the primary PostgreSQL database inside a single isolation block. Read-locks prevent race conditions, ensuring that concurrent transactions from multiple registers do not allow stock levels to drop below zero.

Floating-point numbers must never be used for inventory quantities or currency values. Standard floating-point rounding errors compound rapidly over thousands of micro-transactions, creating discrepancies that require manual database audits to fix.

Both base-unit quantities and unit conversion factors must be stored using fixed-precision decimal types. Combined with append-only transaction logs, this setup guarantees that historical sales data can be replayed cleanly without losing precision.

Designing robust retail systems requires enforcing clean schema abstractions and atomic database constraints from day one. Normalizing inventory around base units and using strict decimal precision ensures that stock reports stay accurate under heavy operational usage.