How to Prevent Duplicate POS Sales from Skewing Inventory with Python and SQLite

A point-of-sale register sells two pairs of boots. The inventory worker deducts two units and commits the change. Then the worker crashes before it acknowledges the message.

When the worker restarts, the broker delivers the same sale again. If the handler runs the same inventory update a second time, stock drops from eight units to six even though the register sold only two.

In this tutorial, we’ll build a small Python and SQLite consumer that handles that failure. We’ll also test what happens when a sale references a missing SKU or would drive inventory below zero.

Reproduce the inventory bug

The inventory update itself looks reasonable:

UPDATE inventory SET on_hand = on_hand - ? WHERE sku = ? AND location = ?; 

The problem is that the statement has no record of which sale caused the change. Run it twice for the same message and it deducts the quantity twice.

To make the handler idempotent, give every sale line a stable event ID. The producer must reuse that ID when it retries a publish. For example, pos:store-12:sale-493:line-1 identifies one line on one receipt at one store. A second sale of the same SKU gets a different ID.

The consumer then writes the event ID and changes the stock balance in a single transaction. Both writes commit together.

Build the consumer

The demo uses Python’s standard library. Save the following as inventory_sync.py and run it with python inventory_sync.py. It uses an in-memory database so you can rerun it from a clean state.

import sqlite3 def create_database(): db = sqlite3.connect(":memory:") db.executescript(""" CREATE TABLE inventory ( sku TEXT NOT NULL, location TEXT NOT NULL, on_hand INTEGER NOT NULL CHECK (on_hand >= 0), reserved INTEGER NOT NULL CHECK (reserved >= 0), PRIMARY KEY (sku, location) ); CREATE TABLE processed_sales ( event_id TEXT PRIMARY KEY NOT NULL ); CREATE VIEW storefront_stock AS SELECT sku, location, MAX(0, on_hand - reserved) AS available FROM inventory; INSERT INTO inventory (sku, location, on_hand, reserved) VALUES ('boot-38', 'store-12', 10, 2); """) return db def apply_sale(db, event_id, sku, location, quantity): if not isinstance(event_id, str) or not event_id.strip(): raise ValueError("event_id must be a non-empty string") if type(quantity) is not int or quantity <= 0: raise ValueError("quantity must be a positive integer") with db: claim = db.execute( """ INSERT INTO processed_sales (event_id) VALUES (?) ON CONFLICT (event_id) DO NOTHING """, (event_id,), ) if claim.rowcount == 0: return "duplicate" update = db.execute( """ UPDATE inventory SET on_hand = on_hand - ? WHERE sku = ? AND location = ? """, (quantity, sku, location), ) if update.rowcount != 1: raise LookupError(f"No inventory row for {sku} at {location}") return "applied" def stock(db): return db.execute(""" SELECT i.on_hand, s.available FROM inventory AS i JOIN storefront_stock AS s USING (sku, location) WHERE i.sku = 'boot-38' AND i.location = 'store-12' """).fetchone() def was_processed(db, event_id): return db.execute( "SELECT 1 FROM processed_sales WHERE event_id = ?", (event_id,), ).fetchone() is not None def main(): db = create_database() try: first_sale = ("pos:store-12:sale-493:line-1", "boot-38", "store-12", 2) assert stock(db) == (10, 8) assert apply_sale(db, *first_sale) == "applied" assert stock(db) == (8, 6) # Simulate redelivery after the worker committed the sale.  assert apply_sale(db, *first_sale) == "duplicate" assert stock(db) == (8, 6) # A separate receipt for the same SKU must still deduct stock.  second_sale = ("pos:store-12:sale-494:line-1", "boot-38", "store-12", 1) assert apply_sale(db, *second_sale) == "applied" assert stock(db) == (7, 5) missing_id = "pos:store-12:sale-495:line-1" try: apply_sale(db, missing_id, "missing-sku", "store-12", 1) except LookupError: assert not was_processed(db, missing_id) else: raise AssertionError("Expected the missing SKU to fail") oversized_id = "pos:store-12:sale-496:line-1" try: apply_sale(db, oversized_id, "boot-38", "store-12", 100) except sqlite3.IntegrityError: assert not was_processed(db, oversized_id) else: raise AssertionError("Expected the stock constraint to fail") assert stock(db) == (7, 5) on_hand, available = stock(db) print(f"on_hand={on_hand}, available={available}") finally: db.close() if __name__ == "__main__": main() 

Expected output:

on_hand=7, available=5 

The first sale deducts two units. Its redelivery deducts zero. The second sale deducts one, leaving seven on hand. Two units were already reserved, so the storefront view shows five available.

Why the transaction matters

The first statement in apply_sale() tries to insert the event ID. SQLite’s unique key makes that insert the claim. If rowcount is zero, the ID already exists and the handler exits before touching stock.

If the claim succeeds, the handler updates the inventory row. When the code leaves with db: normally, the transaction commits. If the update raises an exception, the transaction rolls back, including the event ID insert. A missing inventory row can therefore be repaired and the original sale can be retried with the same ID.

Consider what happens when the worker crashes:

The critical detail is that processed_sales and inventory share the same transaction. Saving the event ID in a separate commit can leave a sale permanently marked as processed even though its stock deduction failed.

Connect the handler to a real message worker

The demo calls apply_sale() directly. In a worker, call it after validating the message and acknowledge the broker delivery only after the function returns applied or duplicate. If the database is temporarily unavailable, retry with the original ID. If a SKU is missing or a stock constraint fails, route the message for investigation so an operator can reconcile the sale against the inventory record.

Use durable storage for the event ledger in production. The :memory: database in this tutorial disappears when the connection closes, so it cannot protect against redelivery after a process restart. Keep processed IDs long enough to cover your retry and replay window. If a producer can accidentally send different payloads under the same ID, store a payload fingerprint and flag the mismatch.

The inventory module is where this decision becomes visible to retail teams. In a workflow using ChainDrive Inventory Management, a useful reconciliation check is to trace the SKU and location balance back to the original POS transaction. This demo models the consumer behavior with a local SQLite database.

Try the crash case in your own integration

Run your worker against a test database, let it commit a sale, and stop it before it acknowledges the message. Restart it and confirm the broker redelivers the sale. Check both the inventory balance and the event ledger. You should find one deduction and one processed event ID.

That test exercises the failure window this pattern is designed to handle. A redelivered sale is expected. Its inventory movement should happen once.