## The Offline POS Problem Majoo's POS works offline. Merchants can keep selling when their internet drops. Transactions are stored locally and synced when connectivity returns. This is a great product feature. It creates a very specific engineering problem. When a merchant — or an entire market with 40 merchants — comes back online after hours of offline operation, hundreds of transactions arrive simultaneously. Each one needs to be validated against current stock levels and COGS needs to be recalculated. The naive approach breaks in two ways: **Problem 1: Negative stock.** Two offline transactions sell the last unit of product X. Both were valid at the time of sale — the POS showed 1 unit in stock. When they sync, both pass stock validation independently (because they both read "1 unit available" before either deduction is committed) and both succeed. Stock goes to -1. **Problem 2: Performance collapse.** The original PHP implementation recalculated COGS inline with every transaction by scanning the full purchase history for that product. For an outlet with 2 years of history, that meant scanning 50,000+ rows per product per transaction. Under normal load this was slow. During a sync storm, it caused cascading timeouts that took down the API for everyone. This is how we redesigned the system to handle both. ## Understanding COGS Valuation Methods Before getting into the solution, it helps to understand what COGS calculation actually does. Majoo supports three inventory valuation methods: - **Average Cost** — rolling weighted average of all purchase prices - **FIFO** (First In, First Out) — oldest purchased stock is sold first, at its original cost - **Specific Identification** — individual units tracked by Serial Number or Batch Number Each method requires knowing the purchase history of every product in every outlet. The difference is how that history is queried and what gets updated on each transaction. ## Fixing the Negative Stock Problem The root cause of negative stock during sync is a classic race condition. Two concurrent transactions both read the same stock level before either commits its deduction. ### The Wrong Fix: Pessimistic Locking The obvious fix is to lock the stock row before reading it: ```sql SELECT qty FROM stock WHERE outlet_id = ? AND product_id = ? FOR UPDATE; ``` This works but doesn't scale. Under a sync storm, every transaction is waiting for a lock. Throughput collapses. You've traded negative stock for timeouts. ### The Right Fix: Kafka Partitioning + Sequential Processing The real fix is to ensure that transactions for the same product in the same outlet are **never processed concurrently**. We achieve this through Kafka partitioning. Every transaction is published to Kafka with a partition key of `outlet_id`. All transactions from the same outlet land on the same partition, consumed sequentially by a single consumer instance. ```go // Producer: partition by outlet_id kafka.Message{ Key: []byte(event.OutletID), Value: payload, } // Consumer: processes one message at a time per partition // No concurrent processing for the same outlet func (c *StockConsumer) processMessage(msg *kafka.Message) error { var event TransactionEvent json.Unmarshal(msg.Value, &event) return c.db.WithTransaction(func(tx *sql.Tx) error { // No race condition — same outlet's transactions are sequential current, err := tx.QueryRow(` SELECT qty FROM stock WHERE outlet_id = ? AND product_id = ? `, event.OutletID, event.ProductID).Scan(¤t) if current < event.QtyRequired { return ErrInsufficientStock // reject, log for merchant notification } return tx.Exec(` UPDATE stock SET qty = qty - ? WHERE outlet_id = ? AND product_id = ? `, event.QtyRequired, event.OutletID, event.ProductID) }) } ``` With partition-level ordering, the second transaction sees the stock level after the first transaction's deduction has been committed. If stock is insufficient, it's rejected — with a notification to the merchant that the offline sale couldn't be fulfilled. Yes, this means some offline sales get rejected on sync. That's the correct behaviour. A sale that happened offline when stock was already zero shouldn't succeed on sync. The alternative — allowing negative stock — produces incorrect financial records. ## Fixing COGS Performance ### The Original Problem: Full History Scans The original COGS calculation for FIFO looked roughly like this: ```sql -- Run on every transaction SELECT unit_cost, quantity FROM purchase_history WHERE outlet_id = ? AND product_id = ? ORDER BY purchased_at ASC; -- Then iterate in application code to dequeue lots ``` For an outlet with 2 years of purchase history, this returns thousands of rows on every transaction. Under a sync storm, this query runs hundreds of times per second. ### The Fix: Running Balance + FIFO Queue Tables Instead of scanning full history, we maintain pre-computed state. **Running balance table** — tracks the current average cost and total value per product per outlet: ```sql CREATE TABLE product_cogs_balance ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, outlet_id VARCHAR(36) NOT NULL, product_id VARCHAR(36) NOT NULL, method ENUM('fifo', 'average', 'specific') NOT NULL, average_cost DECIMAL(20, 4) NOT NULL DEFAULT 0, total_qty DECIMAL(20, 4) NOT NULL DEFAULT 0, total_value DECIMAL(20, 4) NOT NULL DEFAULT 0, version BIGINT UNSIGNED NOT NULL DEFAULT 0, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_outlet_product_method (outlet_id, product_id, method) ); ``` **FIFO queue table** — tracks remaining stock per purchase lot: ```sql CREATE TABLE product_fifo_queue ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, outlet_id VARCHAR(36) NOT NULL, product_id VARCHAR(36) NOT NULL, purchase_id VARCHAR(36) NOT NULL, unit_cost DECIMAL(20, 4) NOT NULL, remaining_qty DECIMAL(20, 4) NOT NULL, purchased_at TIMESTAMP NOT NULL, KEY idx_fifo_dequeue (outlet_id, product_id, purchased_at, remaining_qty) ); ``` On every sale, instead of scanning purchase history, we read only the earliest lots with `remaining_qty > 0`: ```go func (r *COGSRepository) ProcessFIFOSale(ctx context.Context, sale SaleEvent) error { remainingToDeduct := sale.Quantity rows, err := r.db.QueryContext(ctx, ` SELECT id, unit_cost, remaining_qty FROM product_fifo_queue WHERE outlet_id = ? AND product_id = ? AND remaining_qty > 0 ORDER BY purchased_at ASC LIMIT 50 `, sale.OutletID, sale.ProductID) for rows.Next() && remainingToDeduct > 0 { var lot PurchaseLot rows.Scan(&lot.ID, &lot.UnitCost, &lot.RemainingQty) deductFromLot := min(lot.RemainingQty, remainingToDeduct) sale.COGSValue += deductFromLot * lot.UnitCost remainingToDeduct -= deductFromLot r.db.ExecContext(ctx, ` UPDATE product_fifo_queue SET remaining_qty = remaining_qty - ? WHERE id = ? `, deductFromLot, lot.ID) } return r.recordCOGS(ctx, sale) } ``` Query time goes from scanning tens of thousands of rows to reading at most a handful of active purchase lots. Under a sync storm, this is the difference between 200ms and 2ms per transaction. ### Handling Concurrent COGS Updates Even with Kafka partitioning, the COGS consumer can face concurrent updates if a merchant has multiple product lines being processed. We use optimistic locking on the balance table: ```go func (r *COGSRepository) UpdateAverageCost(ctx context.Context, update COGSUpdate) error { result, err := r.db.ExecContext(ctx, ` UPDATE product_cogs_balance SET average_cost = ?, total_qty = ?, total_value = ?, version = version + 1 WHERE outlet_id = ? AND product_id = ? AND version = ? `, update.NewCost, update.NewQty, update.NewValue, update.OutletID, update.ProductID, update.Version) rows, _ := result.RowsAffected() if rows == 0 { return ErrVersionConflict // caller retries with fresh read } return nil } ``` Conflicts are rare in practice because Kafka partitioning means same-outlet transactions are sequential. But they can happen when a purchase and a sale for the same product arrive at slightly different times and both read the same version. The retry handles it cleanly. ## The Offline-Specific Challenge: Timestamps One detail that took us a while to get right: offline transactions have the **timestamp when the sale occurred on the POS**, not when they synced. For FIFO, this matters. If a merchant sold product X offline on Monday, and a purchase arrived on Tuesday, and the transactions sync on Wednesday — the FIFO order should reflect Monday's sale deducting from stock that existed on Monday, not from the Tuesday purchase. We solve this by sorting FIFO dequeue by `purchased_at` relative to `occurred_at` from the transaction event, not by `synced_at`. The `occurred_at` is sent by the POS and stored with the transaction. ```go rows, err := r.db.QueryContext(ctx, ` SELECT id, unit_cost, remaining_qty FROM product_fifo_queue WHERE outlet_id = ? AND product_id = ? AND remaining_qty > 0 AND purchased_at <= ? -- only use stock that existed at time of sale ORDER BY purchased_at ASC LIMIT 50 `, sale.OutletID, sale.ProductID, sale.OccurredAt) ``` ## Results | Metric | Before | After | |--------|--------|-------| | COGS query time (avg) | ~400ms | ~8ms | | Negative stock events per week | 3–5 | 0 | | API timeouts during sync storms | Frequent | None | | COGS-related support tickets/week | ~8 | ~1 | | Main DB CPU at peak sync | 85% | 38% | ## Key Takeaways 1. **Offline-capable POS requires explicit conflict resolution on sync** — decide upfront whether you reject conflicting transactions or allow negative stock. Rejecting is usually the right call for financial integrity. 2. **Kafka partition key determines your ordering guarantee** — partition by the right key (`outlet_id`) and you eliminate an entire class of race conditions without locks. 3. **Pre-compute state, don't scan history** — running balance tables and FIFO queues turn O(n) history scans into O(1) lookups. 4. **Offline timestamps need to be trusted** — if you use sync time instead of occurrence time for FIFO ordering, your COGS is wrong. 5. **Optimistic locking scales better than pessimistic for this pattern** — most of the time there's no conflict, so you pay nothing for the lock.