Make the sale real.
Atomic checkout. Or nothing.
The cart moves to the server. Three new tables — sales, sale_lines, payments — with two foreign keys. The checkout is a single transaction that either commits all five writes (sale, lines, payment, stock movements, quantity updates) or rolls back every one. Prices and names are snapshotted on each line, so yesterday's receipts don't change when today's prices do. The catalog is read from the same database the inventory app uses. And if the network fails mid-checkout, the cart survives — the cashier retries, the cart reconciles, nothing vanishes.
Short answer: Three tables. sales(id, cashier, subtotal, tax_rate, tax_total, grand_total, status, created_at, paid_at), sale_lines(id, sale_id FK, product_id FK, product_name, qty, unit_price, line_total, created_at) with ON DELETE RESTRICT on product_id, and payments(id, sale_id FK, method, amount, tendered, change, created_at). The checkout is with get_db() as conn: — validate lines, compute totals server-side, insert sale → lines → payment → movements → quantity updates, commit. Any failure rolls back. The frontend posts the cart, gets a sale back, shows the receipt from the server response.
✅ The three tables
Two foreign keys. One financial record.
sales
One row per transaction. Cached totals (subtotal, tax_total, grand_total) for fast history queries. status is 'paid' now; Stage 04 adds 'held' and 'voided'.
sale_lines
One row per line item. product_id FK with RESTRICT. product_name and unit_price are snapshots. This is the third table — non-negotiable.
payments
One row per payment. method (cash/card/other), amount, plus tendered and change for cash sales. Stage 04 adds split payments.
CREATE TABLE sales (
id INTEGER PRIMARY KEY AUTOINCREMENT,
cashier TEXT NOT NULL DEFAULT 'Alex Rivera',
subtotal REAL NOT NULL CHECK (subtotal >= 0),
tax_rate REAL NOT NULL CHECK (tax_rate >= 0),
tax_total REAL NOT NULL CHECK (tax_total >= 0),
grand_total REAL NOT NULL CHECK (grand_total >= 0),
status TEXT NOT NULL DEFAULT 'paid'
CHECK (status IN ('open', 'paid', 'voided', 'held')),
created_at TEXT NOT NULL,
paid_at TEXT
);
CREATE TABLE sale_lines (
id INTEGER PRIMARY KEY AUTOINCREMENT,
sale_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
product_name TEXT NOT NULL, -- snapshot
qty INTEGER NOT NULL CHECK (qty > 0),
unit_price REAL NOT NULL CHECK (unit_price >= 0), -- snapshot
line_total REAL NOT NULL CHECK (line_total >= 0), -- snapshot
created_at TEXT NOT NULL,
FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);
CREATE TABLE payments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
sale_id INTEGER NOT NULL,
method TEXT NOT NULL CHECK (method IN ('cash', 'card', 'other')),
amount REAL NOT NULL CHECK (amount >= 0),
tendered REAL NOT NULL DEFAULT 0 CHECK (tendered >= 0),
change REAL NOT NULL DEFAULT 0 CHECK (change >= 0),
created_at TEXT NOT NULL,
FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE
);
Why three tables, not one fat "sales" table:
A sale with 5 items is not one row. It's
one sale + five lines. If you cram the lines
into columns:
product_1, qty_1, price_1, product_2, ...
you cap the line count, can't index by
product, and every query is awful.
Three tables is the normalized shape:
sales → one row per transaction
sale_lines → one row per line item
payments → one row per payment
Query "what did we sell today" — one query
against sales WHERE created_at >= today.
Query "how many keyboards sold this month" —
JOIN sale_lines ON sales, GROUP BY product_id.
Impossible with a fat table.
Why the CASCADE vs RESTRICT split:
sales ←─ CASCADE ── sale_lines
←─ RESTRICT ── products
Delete a sale → its lines and payments go
too. The sale is the aggregate root; the
lines have no meaning without it. CASCADE.
Delete a product → RESTRICT. The DELETE
fails because sale_lines reference it. The
product can be archived, not deleted. The
sale line is a financial record that must
survive.
Two FKs, two actions, two meanings. Each
matches the relationship.
Why cached totals on sales:
subtotal, tax_total, grand_total are all
derivable from the lines (SUM(line_total),
SUM(line_total) * tax_rate / 100, etc.).
But the history page needs them for every
row. Recomputing them means joining lines
on every list query. For a sale list of
20 rows, that's 20 joins. For 10,000, it's
10,000 joins.
So we cache them. The transaction guarantees
they stay in sync with the lines — the same
INSERT that creates the lines writes the
totals. The cache and the source of truth
cannot disagree.
Same trade-off as Inventory's cached
products.quantity: pay on writes (in a
transaction) to save on reads. Correct
only when writes are atomic.
The CHECK constraints:
qty > 0 ← no zero-quantity lines
unit_price >= 0 ← no negative prices
line_total >= 0 ← no negative totals
subtotal >= 0 ← no negative subtotals
grand_total >= 0 ← no negative totals
status IN (...) ← no typo'd statuses
method IN (...) ← no typo'd payment methods
Every one of these is defense in depth.
The app validates before INSERT; the schema
validates again. If you bypass the app
(curl, direct SQL), the schema still holds.
Never trust any single boundary.
✅ The atomic checkout
Five writes. One transaction. Or nothing.
The checkout endpoint does five things, and they all succeed together or none of them do:
1. INSERT sales
One row with cached totals computed server-side from the resolved lines.
2. INSERT sale_lines
One row per cart line, with product_name and unit_price snapshotted from the products table at this moment.
3. INSERT payments
One row. Cash: tendered + change. Card: amount only.
4. INSERT stock_movements
One row per line with reason='sold' and negative delta. This is what Inventory's audit log sees.
5. UPDATE products.quantity
Decrement per line. If the CHECK constraint fires (qty would go negative), the whole transaction rolls back.
COMMIT
The with get_db() as conn: block exits cleanly → COMMIT. If any statement above raised → ROLLBACK.
@app.route("/api/sales", methods=["POST"])
def create_sale():
data = request.get_json(silent=True) or {}
cashier = (data.get("cashier") or "Alex Rivera").strip()
tax_rate = float(data.get("taxRate", 8.5))
lines = data.get("lines") or []
payment = data.get("payment") or {}
if not lines:
return jsonify({"error": "Cart is empty."}), 400
if tax_rate < 0:
return jsonify({"error": "Tax rate cannot be negative."}), 400
method = (payment.get("method") or "cash").lower()
if method not in ("cash", "card", "other"):
return jsonify({"error": "Invalid payment method."}), 400
# ── BEGIN TRANSACTION ──
# Every write below shares one transaction.
# Any exception → sqlite3 rolls back everything.
with get_db() as conn:
# 1. Validate lines + build price snapshot
resolved_lines = []
for raw in lines:
pid = int(raw["productId"])
qty = int(raw["qty"])
if qty <= 0:
return jsonify({"error": "Quantity must be positive."}), 400
product = conn.execute(
"SELECT id, name, price, quantity FROM products WHERE id = ?",
(pid,),
).fetchone()
if not product:
return jsonify({"error": f"Product {pid} not found."}), 404
if product["quantity"] < qty:
return jsonify({
"error": f'Only {product["quantity"]} of "{product["name"]}" '
f'in stock (tried {qty}).'
}), 409
resolved_lines.append({
"product_id": product["id"],
"product_name": product["name"],
"qty": qty,
"unit_price": product["price"],
"line_total": round(product["price"] * qty, 2),
})
# 2. Compute totals SERVER-SIDE
subtotal = round(sum(r["line_total"] for r in resolved_lines), 2)
tax_total = round(subtotal * tax_rate / 100, 2)
grand_total = round(subtotal + tax_total, 2)
# 3. Payment guard
tendered = float(payment.get("tendered", 0)) if method == "cash" else grand_total
if method == "cash" and tendered < grand_total:
return jsonify({
"error": f"Cash tendered ({tendered:.2f}) is less "
f"than total ({grand_total:.2f})."
}), 400
change = round(max(0, tendered - grand_total), 2) if method == "cash" else 0
timestamp = now_iso()
# 4. INSERT sale
cur = conn.execute(
"""INSERT INTO sales
(cashier, subtotal, tax_rate, tax_total, grand_total,
status, created_at, paid_at)
VALUES (?, ?, ?, ?, ?, 'paid', ?, ?)""",
(cashier, subtotal, tax_rate, tax_total, grand_total,
timestamp, timestamp),
)
sale_id = cur.lastrowid
# 5. INSERT lines (with price snapshot)
for r in resolved_lines:
conn.execute(
"""INSERT INTO sale_lines
(sale_id, product_id, product_name, qty,
unit_price, line_total, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?)""",
(sale_id, r["product_id"], r["product_name"],
r["qty"], r["unit_price"], r["line_total"], timestamp),
)
# 6. INSERT payment
conn.execute(
"""INSERT INTO payments
(sale_id, method, amount, tendered, change, created_at)
VALUES (?, ?, ?, ?, ?, ?)""",
(sale_id, method, grand_total, tendered, change, timestamp),
)
# 7. Stock movements + quantity updates
for r in resolved_lines:
conn.execute(
"""INSERT INTO stock_movements
(product_id, delta, reason, note, created_at)
VALUES (?, ?, 'sold', ?, ?)""",
(r["product_id"], -r["qty"], f"POS sale #{sale_id}", timestamp),
)
conn.execute(
"""UPDATE products
SET quantity = quantity - ?, updated_at = ?
WHERE id = ?""",
(r["qty"], timestamp, r["product_id"]),
)
# Exit block cleanly → COMMIT
# Re-fetch the complete sale to return
sale = _fetch_sale(conn, sale_id)
return jsonify(sale), 201
What could go wrong without a transaction:
Scenario: 3-item sale, failure on item 3.
Without a transaction:
✓ INSERT sale
✓ INSERT line 1
✓ INSERT line 2
✓ INSERT line 3
✓ INSERT payment
✓ UPDATE products qty (item 1)
✓ UPDATE products qty (item 2)
✗ UPDATE products qty (item 3) — CHECK fails
Result: the sale exists with 3 lines,
the payment exists, stock was decremented
for items 1 and 2 but not item 3. The
inventory is wrong forever. Every report
that queries SUM(quantity) is wrong.
With a transaction:
All writes succeed
Item 3 UPDATE fails → ROLLBACK
sale, lines, payment, movements — all gone
products.quantity unchanged for items 1, 2
Response: 500 with the error
The cashier sees "checkout failed." The
cart is still there. Nothing was lost. The
customer can be told to retry. No silent
data corruption.
Why validate inside the transaction:
There's a check-then-act race here. Two
cashiers sell the last monitor stand at the
same time:
Cashier A reads quantity = 1
Cashier B reads quantity = 1
Both compute new_qty = 0
Both write 0
Result: 2 sold, quantity 0 — oversold by 1
A true fix uses SELECT ... FOR UPDATE or
SQLite's BEGIN IMMEDIATE to lock the row.
We're not adding that here — for a
single-register demo it's not a concern.
The lesson: transactions guarantee atomicity
(all-or-nothing), not isolation (no
interleaving). Those are two of the four
ACID properties, and isolation is a bigger
topic than Stage 03 needs.
What we DO get from the transaction: no
half-committed sale. If the qty UPDATE
fails the CHECK constraint, the whole thing
rolls back — the sale is simply not created.
The cashier retries with a smaller qty.
Clean recovery.
Why the server recomputes the totals:
The client sends:
{ lines: [{productId: 1, qty: 2}], taxRate: 8.5 }
It does NOT send prices. It does NOT send
subtotal, tax, or grand total. Those are
computed here, from the products table's
current prices.
If the client sent prices, a malicious
cashier could POST {productId: 1, qty: 1,
unit_price: 0.01} and get an 89-dollar
keyboard for a penny. The server would
happily store it because it trusted the
client.
"Never trust the client with money math"
isn't paranoia. It's the most basic rule
of any billing system. The client is a
preview; the server is the truth.
Why 409 for insufficient stock:
400 is "bad request" — the client sent
something malformed.
409 is "conflict" — the request was valid,
but the server's current state conflicts
with it.
"You asked to sell 5 of a product with 2
in stock" is a conflict, not a bad request.
The line item was well-formed. The
inventory just doesn't support it right
now. 409 tells the client "this is a
reconciliation issue, not a bug."
The frontend's catch block sees err.status
=== 409 and knows to reload the catalog
and reconcile the cart, rather than
showing a generic error.
✅ Price snapshotting
The sale line is immutable. Tomorrow's price doesn't rewrite yesterday.
The most important schema decision in this stage is that sale_lines stores three snapshots:
product_name
What the product was called when sold. If the product is renamed later, the receipt still shows the original name.
unit_price
What the customer was actually charged per unit. If the price goes up tomorrow, yesterday's receipts don't change.
line_total
qty × unit_price, stored rather than recomputed. Faster queries, and protected from any future reinterpretation.
Why this matters: imagine a year-end report that sums revenue by month. If sale_lines referenced the current product price, raising a price in December would retroactively change November's revenue. The report would be wrong — not "reflecting current prices," just wrong. Financial records must be immutable. The line captures what happened; the catalog reflects what's true now. Two different truths, two different places.
# When the sale is created, snapshot the product's state:
product = conn.execute(
"SELECT id, name, price, quantity FROM products WHERE id = ?",
(pid,),
).fetchone()
conn.execute(
"""INSERT INTO sale_lines
(sale_id, product_id, product_name, qty,
unit_price, line_total, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?)""",
(
sale_id,
product["id"],
product["name"], # ← snapshot
qty,
product["price"], # ← snapshot
round(product["price"] * qty, 2), # ← snapshot
timestamp,
),
)
# Later, if the product price changes:
# UPDATE products SET price = 99.99 WHERE id = ?
#
# The existing sale_lines rows do NOT change.
# The receipt for sale #42 still shows $89.00.
# That's correct — the customer paid $89.00.
✅ RESTRICT vs CASCADE — the two FKs disagree
Because the two relationships mean different things.
sale_lines.sale_id
ON DELETE CASCADE. A line item has no meaning without its sale. Delete the sale, the lines go too.
sale_lines.product_id
ON DELETE RESTRICT. A product that has been sold cannot be deleted. The financial record must survive.
This is the opposite of Inventory Stage 04, where stock_movements.product_id uses CASCADE. That's not an inconsistency — it's the correct semantics for each case:
A stock movement is ABOUT a product. "8 units sold on Tuesday" — only meaningful if you know which product. Delete the product and the movement is noise. CASCADE. A sale line REFERENCES a product but is ALSO a financial record. "Customer #1234 paid $89 for item SKU-002" — that fact is true independent of whether SKU-002 is still in the catalog. Delete the product and the record must survive. RESTRICT. The FK action encodes what the relationship means, not a global default.
Stage 04 will add products.archived_at. A product that has been sold can be archived — removed from the POS catalog, hidden from the inventory list, but not deleted. The RESTRICT constraint is what forces that design. Without it, the app would happily let an admin delete a product and break a year of sales history.
✅ When the checkout fails
The cart survives. The cashier retries.
This is the most important UX difference between Stage 02 and Stage 03. In Stage 02, checkout never fails — the cart is cleared and a sale "completes" without ever touching a server. In Stage 03, checkout can fail for real reasons:
Stock conflict
409. Another register sold the last unit while this cart was open. The line item quantity is now invalid.
Network failure
Fetch throws. The server was unreachable. The sale did not happen — but the cart is still valid.
Server error
500. The transaction rolled back. The sale did not happen. The cart is intact.
Validation error
400. Something the client sent was wrong (empty cart, invalid payment method). Usually indicates a bug — the client should have caught it.
try {
const sale = await api.createSale(payload);
// SUCCESS: clear the cart, close the modal, show the receipt
state.lines = [];
saveCart();
paymentBackdrop.classList.remove('is-open');
renderCart();
await loadCatalog(); // stock changed
showReceiptFromServer(sale);
showToast('Sale #' + sale.id + ' completed · ' + money(sale.grand_total));
} catch (err) {
// FAILURE: cart SURVIVES. Never auto-clear on failure.
showToast(err.message, true);
await loadCatalog(); // stock may have changed
reconcileCartAgainstCatalog(); // drop sold-out lines, cap qty
} finally {
state.checkoutInFlight = false;
paymentSubmit.textContent = 'Complete sale';
updateChange();
renderTotals();
}
The checkoutInFlight guard prevents double-clicks on "Complete sale" from submitting the sale twice. Without it, a fast double-click fires two POSTs — two sales created, two sets of stock movements. The flag disables the button for the duration of the request and unlocks in finally, whether the request succeeded or failed.
✅ Run it
Three commands. If Inventory exists, it's shared.
apps/pos/downloads/backend/
├── app.py ← Flask routes + atomic checkout
├── database.py ← connection helpers
├── schema.sql ← 3 POS tables + shared inventory tables
├── seed.py ← seeds products (if empty) + sample sales
├── requirements.txt ← Flask + flask-cors
├── README.md
└── static/
└── pos-stage-03.html ← fetch-based frontend
cd apps/pos/downloads/backend python -m venv .venv source .venv/bin/activate # Windows: .venv\Scripts\activate pip install -r requirements.txt python seed.py # creates inventory.db (or uses existing) python app.py # http://localhost:5000
Open http://localhost:5000/ — Flask serves the frontend from static/. The catalog loads from GET /api/products. The charge button posts to POST /api/sales. The receipt is built from the server's response, not the client's snapshot.
Test the happy path
Add 2 items, Charge, pay exact cash, complete. The receipt shows the sale ID. Reload the page — the sale is in the database (visible via GET /api/sales or sqlite3 inventory.db "SELECT * FROM sales").
Test stock conflict
Add 8 of the Mechanical Keyboard (8 in stock). Open a second browser tab and add 3. Complete sale in tab 2. Go back to tab 1 and complete sale. 409 error: "Only 5 of ... in stock." The cart reconciles to qty 5, toast explains.
Test transaction rollback
With curl: POST /api/sales with an invalid line (product 9999). 404. Then check SELECT COUNT(*) FROM sales — the count is unchanged. The transaction rolled back cleanly.
Test price snapshot
Complete a sale of SKU-002. Then update the product: UPDATE products SET price = 99.99 WHERE id = 2. The existing sale's GET /api/sales/<id> still shows the original price. The catalog in the POS shows the new price.
Test RESTRICT
Try to delete a sold product: DELETE FROM products WHERE id = 2. SQLite refuses: "FOREIGN KEY constraint failed." The sale line protects the product.
Test network failure
Stop the Flask server. Try to complete a sale. Toast shows "checkout failed." The cart is still there. Start the server. Retry — it succeeds.
Seven files. Drop them into apps/pos/downloads/backend/, keep the static/ subfolder intact, and the three-command run sequence works. The README.md has the full instructions.
http://localhost:5000/apps/pos/downloads/backend/ ├── app.py ├── database.py ├── schema.sql ├── seed.py ├── requirements.txt ├── README.md └── static/ └── pos-stage-03.html
products from the shared inventory.db. If you've already built Inventory Stage 04, the seed script leaves your products alone and only seeds the three POS tables. If you haven't, seed.py creates inventory.db with a minimal inventory schema and 8 sample products, so the POS works standalone.
⚠️ Common mistakes
What to check if Stage 03 doesn't work.
If you split the five writes across multiple commits, a failure halfway leaves a corrupt sale. Fix: with get_db() as conn: around everything.
The client sends {productId, qty}, not prices. The server looks up the price. If you accept client prices, a malicious cashier sells a $89 keyboard for $0.01.
Storing only product_id means tomorrow's price change rewrites yesterday's receipts. Fix: snapshot the three fields on the line.
Deleting a product would orphan its sales. Fix: ON DELETE RESTRICT. Add archived_at in Stage 04 for the "delete" use case.
The cashier loses their work. Fix: on failure, cart survives, catalog reloads, cart reconciles against new stock.
checkoutInFlight guardDouble-clicks create two sales. Fix: disable the button for the duration of the request, unlock in finally.
Stock never goes down. The POS sells phantom inventory. Fix: UPDATE products SET quantity = quantity - ? in the transaction.
Inventory's audit log doesn't see POS sales. Fix: one INSERT stock_movements per line with reason='sold'.
❓ Frequently asked
Why is the checkout one transaction instead of separate API calls?
Because a sale touches five tables. If you split them into separate API calls, a failure halfway leaves the database corrupt — a sale with no payment, or stock decremented with no sale to explain it. One transaction means all five writes commit together or none do. A sale in the database is a complete sale.
Why snapshot unit_price on the sale line instead of looking it up?
Because prices change. If you store only product_id and look up the price at report time, then raising a product's price tomorrow rewrites every past report. The sale_line stores unit_price at the moment of sale. The receipt, the report, the refund calculation all use that snapshot.
Why RESTRICT on sale_lines.product_id?
You cannot delete a product that has been sold. If you could, the sale_line would reference a nonexistent product_id — every report, refund, and receipt rebuild would break. Products can be archived instead. This is the opposite of stock_movements (CASCADE) — a stock movement has no meaning without its product, but a sale line is a financial record that must survive.
Why does the client still compute totals if the server also computes them?
The client computes totals for display — the running cart subtotal, the tax amount, the Charge button label. The server computes totals for the actual sale — the values that go into the database. The client values are a preview; the server values are the truth. Never trust the client with money math.
What happens to the cart if the checkout fails?
The cart survives. If the server returns 409 because a product sold out while the cashier was in the payment modal, the cart is preserved, the catalog is refreshed, and the cart is reconciled against the new stock. The cashier can retry with the corrected cart.
Why does the POS share inventory's database instead of having its own?
Because the catalog is the same. If the POS had its own products table, changing a price in the inventory app wouldn't affect the POS — you'd have two catalogs that drift. Sharing the database means one source of truth. The inventory owns the catalog, the POS reads it and writes stock_movements.