Make it real.
Audit log. Pagination. Transactions.
Three upgrades that separate a CRUD demo from an app you could plausibly use: a third table that logs every stock change (stock_movements, a second foreign key), server-side pagination and sorting so the table works with 60,000 rows, and SQL-computed KPIs so the totals are always right even though the client only holds one page. Plus full supplier CRUD, and the transactional write that keeps the audit log and the product quantity in sync.
This is the final stage. After it, the inventory app is complete: four stages, three tables, a working Flask + SQLite backend. No Stage 05.
Short answer: Add stock_movements(id, product_id FK, delta, reason, note, created_at) with ON DELETE CASCADE. When you record a movement, wrap the INSERT and the UPDATE products SET quantity = quantity + delta in one transaction — both succeed or neither does. Move pagination and sorting into SQL (LIMIT ? OFFSET ?, ORDER BY ? with a whitelist). Move the KPI math into one aggregate SELECT (SUM(quantity*cost), COUNT FILTER). Add full supplier CRUD with ON DELETE SET NULL. The UI gains a supplier page, a stock-movement button, and pager controls.
✅ The third table — an audit log
A number is not a story. A log is.
What it stores
One row per stock change: delta (+10, −3), reason (received / sold / damaged / adjustment), a free-text note, and a timestamp.
What it answers
"Why is the keyboard at 8?" → 20 received, 12 sold, 3 damaged. The number has a history now.
The FK, and why CASCADE
product_id → products.id ON DELETE CASCADE. A movement is about its product — delete the product, its history goes too.
The trade-off
products.quantity is now a cached total. It must be updated in the same transaction as the movement insert, or the two disagree.
CREATE TABLE stock_movements ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_id INTEGER NOT NULL, delta INTEGER NOT NULL, -- +10 received, -3 sold reason TEXT NOT NULL, -- received | sold | damaged | adjustment note TEXT, created_at TEXT NOT NULL, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ); CREATE INDEX idx_movements_product ON stock_movements(product_id); CREATE INDEX idx_movements_created ON stock_movements(created_at);
Two FK actions, two meanings: products.supplier_id → suppliers.id ON DELETE SET NULL stock_movements.product_id → products.id ON DELETE CASCADE Why the difference? A supplier is a SEPARATE entity. A product references it but does not depend on it. If the supplier goes away, the product survives with supplier_id = NULL. SET NULL. A stock movement is ABOUT its product. It has no independent existence — "8 units sold on Tuesday" only means something if you know which product. If the product is deleted, the movement is meaningless. CASCADE. The FK action is a statement about the relationship, not a generic default. Choose it to match what the data actually is. Why store delta, not the resulting quantity: BAD: stock_movements(quantity_after) After: 42, 34, 8, 0 What was each change? Subtract adjacent rows. Fragile — one deleted row breaks the math. GOOD: stock_movements(delta) After: +50, -8, -12, -3 Each row is self-describing. Sum the deltas and you get the current quantity. Delete a row and you know exactly what you removed. Deltas are additive; absolute quantities are not. Prefer the additive representation. The cached quantity problem: products.quantity is now redundant with SUM(stock_movements.delta). Why keep both? Because SELECT SUM(delta) on every product list query is O(movements) — slow at scale. The cached column makes reads O(1). The cost: every write must update both. That is exactly what a transaction is for. Stage 04 wraps the INSERT + UPDATE in one atomic unit. If either fails, both roll back. The cache and the log cannot disagree. This is the standard denormalization trade-off: pay on writes to save on reads. Correct only when the writes are transactional.
✅ Transactions — both or neither
Two tables, one atomic write.
@app.route("/api/products/<int:pid>/movements", methods=["POST"])
def create_movement(pid):
data = request.get_json(silent=True) or {}
try:
delta = int(data.get("delta"))
except (TypeError, ValueError):
return jsonify({"error": "delta must be an integer."}), 400
if delta == 0:
return jsonify({"error": "delta cannot be zero."}), 400
reason = (data.get("reason") or "adjustment").strip().lower()
if reason not in ("received", "sold", "damaged", "adjustment"):
return jsonify({"error": "Invalid reason."}), 400
# The `with get_db() as conn:` context manager commits on success
# and rolls back on exception. That is the transaction.
with get_db() as conn:
product = conn.execute(
"SELECT * FROM products WHERE id = ?", (pid,)
).fetchone()
if not product:
return jsonify({"error": "Product not found"}), 404
new_qty = product["quantity"] + delta
if new_qty < 0:
return jsonify({
"error": f"Cannot apply {delta}: stock would go negative "
f"(currently {product['quantity']})."
}), 400
# Write 1: update the cached quantity
conn.execute(
"UPDATE products SET quantity = ?, updated_at = ? WHERE id = ?",
(new_qty, now_iso(), pid),
)
# Write 2: append the audit row
conn.execute(
"""INSERT INTO stock_movements
(product_id, delta, reason, note, created_at)
VALUES (?, ?, ?, ?, ?)""",
(pid, delta, reason, data.get("note"), now_iso()),
)
# Both writes commit together. An exception anywhere above
# rolls both back.
return jsonify({"ok": True}), 201
What could go wrong without a transaction:
Write 1 succeeds: products.quantity = 42
Write 2 fails: stock_movements INSERT crashes
Result: the product says 42, but there's no
log row explaining how it got there. The
cache and the log disagree. Silent corruption.
With a transaction:
Write 1 executes
Write 2 fails → exception → ROLLBACK
products.quantity is back to its old value
No movement row was inserted
The request returns 500
The client can retry cleanly
The invariant — "quantity == SUM(deltas)" —
holds. Always.
How Python's sqlite3 gives you this:
conn = sqlite3.connect(...)
...
conn.commit() ← explicit commit
The `with get_db() as conn:` pattern uses
a connection context manager. On clean exit
of the block, it commits. On exception, it
rolls back and re-raises.
This is why the Stage 03 code used
`with get_db() as conn:` — not just for
cleanliness, but because every multi-statement
operation needs the transaction guarantee.
For a single INSERT (create product), the
guarantee matters less — but the pattern is
consistent. Always use it.
The check-then-act race:
Two simultaneous requests:
A: "sell 5" (reads quantity=5)
B: "sell 5" (reads quantity=5)
Both see 5. Both compute new_qty = 0. Both
write 0. Result: 10 units sold, quantity 0.
Oversold by 5.
For a single-user demo, not a problem. For
real inventory, you'd use SELECT ... FOR UPDATE
(or SQLite's BEGIN IMMEDIATE) to lock the row.
We're not adding that here, but it's the next
thing to learn: transactions guarantee
atomicity, not isolation. Those are two of the
four ACID properties, and both matter at scale.
✅ Pagination and sorting — in SQL
The client stops owning the full list.
Before
Client had state.products = every row. Filtered, sorted, sliced in JS. Fine at 6 rows. Unusable at 60,000.
After
Client sends ?page=2&perPage=20&sort=price&dir=asc&status=low. Server returns {items, page, perPage, total, pages}. Client renders 20 rows and a pager.
SORT_COLUMNS = {
"name": "p.name", "sku": "p.sku",
"price": "p.price", "cost": "p.cost",
"quantity": "p.quantity", "category": "p.category",
"supplier": "s.name", "created": "p.created_at",
}
STATUS_CLAUSES = {
"in": "p.quantity > p.reorder_level",
"low": "p.quantity > 0 AND p.quantity <= p.reorder_level",
"out": "p.quantity = 0",
}
@app.route("/api/products", methods=["GET"])
def list_products():
q = (request.args.get("q") or "").strip().lower()
category = (request.args.get("category") or "all").strip().lower()
status = (request.args.get("status") or "all").strip().lower()
# Whitelist sort columns — NEVER interpolate raw user input into ORDER BY.
sort_key = request.args.get("sort", "created")
sort_col = SORT_COLUMNS.get(sort_key, "p.created_at")
sort_dir = "ASC" if request.args.get("dir") == "asc" else "DESC"
page = max(1, request.args.get("page", 1, type=int))
per_page = min(100, max(1, request.args.get("perPage", 20, type=int)))
offset = (page - 1) * per_page
clauses, params = [], []
if q:
clauses.append("(LOWER(p.name) LIKE ? OR LOWER(p.sku) LIKE ?)")
params += [f"%{q}%", f"%{q}%"]
if category != "all":
clauses.append("LOWER(p.category) = ?")
params.append(category)
if status in STATUS_CLAUSES:
clauses.append(STATUS_CLAUSES[status])
where = (" WHERE " + " AND ".join(clauses)) if clauses else ""
base = f"""FROM products p
LEFT JOIN suppliers s ON s.id = p.supplier_id
{where}"""
with get_db() as conn:
total = conn.execute("SELECT COUNT(*) " + base, params).fetchone()[0]
rows = conn.execute(
f"""SELECT p.*, s.name AS supplier_name
{base}
ORDER BY {sort_col} {sort_dir}
LIMIT ? OFFSET ?""",
params + [per_page, offset],
).fetchall()
return jsonify({
"items": rows_to_list(rows),
"page": page, "perPage": per_page,
"total": total,
"pages": (total + per_page - 1) // per_page,
})
Why the SORT_COLUMNS whitelist:
Naive version:
order_by = request.args.get("sort")
sql = f"ORDER BY {order_by}" ← injection
A client sends ?sort=price;DROP TABLE products
and the server concatenates it into the SQL.
Parameterized queries don't work here —
SQL doesn't allow ? placeholders in ORDER BY.
The column name is an identifier, not a value.
The fix: a whitelist. Map the client's key
("price") to a known-safe identifier
("p.price"). Unknown keys fall back to
a default. The client never sees the SQL.
Same for sort direction: only "asc" and
"desc" are valid, and we map to "ASC"/"DESC".
Everything else becomes the default.
Why count separately from fetch:
The COUNT(*) tells the client how many pages
exist. The LIMIT/OFFSET query fetches one page.
Two queries, two purposes.
Could you do it in one? SQLite has
COUNT(*) OVER () as a window function,
which adds the count to each row. That
works but sends the same number 20 times.
Two queries is clearer and marginally faster.
Why LIMIT/OFFSET, not keyset pagination:
OFFSET is O(offset) — fetching page 500 of
60,000 rows means SQLite scans 10,000 rows
and discards them. For a few thousand rows,
fine. For millions, too slow.
Keyset pagination ("WHERE id > last_id LIMIT 20")
is O(1) but doesn't support jumping to page N.
It's the right choice for infinite scroll,
wrong for a numbered pager.
We use OFFSET because the UI has a numbered
pager. The lesson: OFFSET works for
thousands, keyset for millions. Know which
you're building.
Why filter server-side:
The client cannot filter what it doesn't have.
It has 20 of 60,000 rows. The category filter
must run in SQL, on the full table, before
LIMIT slices. This is why Stage 04 moves the
filter out of getFiltered() and into the WHERE
clause. The client's job becomes: send the
filter, receive the result, render it.
The derived-status filter in SQL:
Stage 02 computed stockStatus in JS:
if (p.quantity === 0) return 'out-of-stock';
if (p.quantity <= p.reorder_level) return 'low-stock';
return 'in-stock';
Stage 04 computes the same thing as a
WHERE clause:
"low": "p.quantity > 0 AND p.quantity <= p.reorder_level"
"out": "p.quantity = 0"
Same logic, different execution environment.
Derived state doesn't care whether it's
computed in JS or SQL — only that it's
computed from source columns, never stored.
The status badge in the table is still
computed client-side from quantity and
reorder_level. Both places derive from the
same source. No stored status field exists.
✅ KPIs in SQL
One aggregate query. Four numbers. Any dataset size.
@app.route("/api/stats", methods=["GET"])
def stats():
with get_db() as conn:
row = conn.execute("""
SELECT
COALESCE(SUM(quantity * cost), 0) AS total_value,
COUNT(*) AS product_count,
SUM(CASE WHEN quantity = 0 THEN 1 ELSE 0 END) AS out_count,
SUM(CASE WHEN quantity > 0
AND quantity <= reorder_level
THEN 1 ELSE 0 END) AS low_count,
(SELECT COUNT(*) FROM suppliers) AS supplier_count,
(SELECT COUNT(*) FROM stock_movements) AS movement_count
FROM products
""").fetchone()
return jsonify(row_to_dict(row))
Why the client can no longer compute these:
Stage 02: the client had every product.
state.products.forEach(p => totalValue += p.quantity * p.cost)
→ correct because state.products was the full list.
Stage 04: the client has 20 of 60,000.
state.products.forEach(...) → sums 20 rows
→ WRONG. The total would be 0.03% of the real value.
The aggregate must run where all the data lives:
the database. One SELECT, four numbers, any
dataset size. The client sends no query, gets
the summary, renders it.
This is the fundamental shift from "client
computes" to "server computes." Stage 02's
derived state was a convenience. Stage 04's
is a necessity.
Why COALESCE(SUM(...), 0):
SUM() returns NULL when the table is empty.
COALESCE turns that NULL into 0 so the
frontend's formatMoney() doesn't crash on
NaN. Defensive — cheap, clear.
Why SUM(CASE WHEN ... THEN 1 ELSE 0 END):
This is the portable "conditional count."
COUNT(*) FILTER (WHERE ...) is cleaner
but SQLite only added FILTER in 3.30 (2019).
The CASE form works everywhere, including
older SQLite and most other databases.
Why scalar subqueries for supplier_count:
(SELECT COUNT(*) FROM suppliers) is a
scalar subquery — it returns one row, one
column, and is evaluated once. It runs in
the same round-trip as the main aggregate.
Could you do separate queries? Yes, three
round-trips instead of one. For a demo, the
difference is imperceptible. For a
latency-sensitive API, every round-trip
counts. One query, all the numbers.
The cross-check:
SUM(quantity * cost) — the KPI
SUM(stock_movements.delta) per product — the
source of truth for quantity
If these disagree, there's a bug. The
transaction guarantee says they can't. In
production, you'd run a nightly reconciliation:
SELECT p.id, p.quantity, COALESCE(SUM(m.delta), 0) AS computed
FROM products p LEFT JOIN stock_movements m ON m.product_id = p.id
GROUP BY p.id
HAVING p.quantity != computed
Empty result = healthy. Non-empty = investigate.
This is how you trust a denormalized cache:
you have a way to recompute it and compare.
✅ Supplier CRUD — the full resource
Four routes. One FK action that matters.
@app.route("/api/suppliers", methods=["GET"])
def list_suppliers():
with get_db() as conn:
rows = conn.execute("""
SELECT s.*, COUNT(p.id) AS product_count
FROM suppliers s
LEFT JOIN products p ON p.supplier_id = s.id
GROUP BY s.id
ORDER BY s.name
""").fetchall()
return jsonify(rows_to_list(rows))
@app.route("/api/suppliers/<int:sid>", methods=["DELETE"])
def delete_supplier(sid):
with get_db() as conn:
existing = conn.execute(
"SELECT id FROM suppliers WHERE id = ?", (sid,)
).fetchone()
if not existing:
return jsonify({"error": "Supplier not found"}), 404
# Count products that will be unlinked — useful for the UI
n_affected = conn.execute(
"SELECT COUNT(*) FROM products WHERE supplier_id = ?", (sid,)
).fetchone()[0]
conn.execute("DELETE FROM suppliers WHERE id = ?", (sid,))
conn.commit()
return jsonify({"ok": True, "productsUnlinked": n_affected})
What ON DELETE SET NULL actually does:
Before DELETE:
suppliers(id=1, name='Acme')
products(id=10, supplier_id=1)
products(id=11, supplier_id=1)
DELETE FROM suppliers WHERE id = 1
With SET NULL, the FK action fires:
products(id=10, supplier_id=NULL)
products(id=11, supplier_id=NULL)
suppliers(id=1) ← gone
Products 10 and 11 still exist. They just
have no supplier. The UI renders "—" for
the supplier column.
With RESTRICT, the DELETE would fail:
"FOREIGN KEY constraint failed"
With CASCADE, products 10 and 11 would be
deleted too — usually catastrophic for
inventory. Deleting a supplier should never
delete stock.
Why count products before deleting:
The UI shows a confirm dialog:
"Delete supplier Acme? 5 products use this
supplier. They will be unlinked (supplier
set to none) but not deleted."
The count comes from the API response —
productsUnlinked. The user sees exactly what
will happen before clicking yes.
This is a UX principle: destructive actions
tell you what they'll do. The count makes
the consequence concrete.
The GROUP BY in list_suppliers:
SELECT s.*, COUNT(p.id) AS product_count
FROM suppliers s
LEFT JOIN products p ON p.supplier_id = s.id
GROUP BY s.id
LEFT JOIN — every supplier appears, even
those with no products. COUNT(p.id) counts
non-NULL matches; a supplier with no
products gets COUNT = 0.
If you used INNER JOIN, suppliers with no
products would vanish from the list. Wrong —
the supplier page should show all suppliers.
This is the same LEFT JOIN logic as the
products list (Stage 03). Preserve the
entity even when the joined data is absent.
✅ Run it
Reseed. Restart. Same three commands.
apps/inventory/downloads/backend/
├── app.py ← supplier CRUD, movements, pagination, stats
├── database.py ← unchanged from Stage 03
├── schema.sql ← three tables
├── seed.py ← seed movements
├── requirements.txt ← unchanged
├── README.md ← updated
└── static/
└── inventory-stage-04.html ← updated frontend
cd apps/inventory/downloads/backend python seed.py # WIPES inventory.db and reseeds — required for the new schema python app.py # http://localhost:5000
You must reseed. The Stage 03 database has two tables; Stage 04 expects three. seed.py drops and recreates everything. Any data you added in Stage 03 will be lost — that's fine, it was sample data. In a real migration you'd write an ALTER TABLE script; here, reseeding is the honest reset.
Test the movement log
Click ± on any product. Record "sold 5". The row's qty drops by 5. Open the product's edit modal — the movement appears in the history panel with a timestamp.
Test the negative guard
Try to sell 999 of a product with 8 in stock. The API returns 400 with "stock would go negative." The toast shows it. Nothing changes.
Test pagination
Set perPage=20 in the code (or add more seed products). Click Next. The pager shows "Showing 21–40 of…" and the KPIs stay constant — they're computed over the full table, not the page.
Test the FK action
Go to Suppliers, delete "Acme Peripherals" (which supplies 3 products). The confirm says "3 product(s) will be unlinked." After deletion, those products still exist with "—" in the Supplier column.
⚠️ Common mistakes
What to check if Stage 04 doesn't work.
The old two-table DB doesn't have stock_movements. Every movement query fails. Fix: python seed.py (destructive — recreates the DB).
If the INSERT fails after the UPDATE, the cache and log disagree. Fix: both writes inside with get_db() as conn:.
f"ORDER BY {sort}" is SQL injection — placeholders don't work in ORDER BY. Fix: the SORT_COLUMNS whitelist.
The client has 20 rows, not all of them. Summing state.products gives the wrong total. Fix: GET /api/stats.
Suppliers with no products vanish. Fix: LEFT JOIN + GROUP BY.
Selling more than you have drives quantity negative, violating the CHECK constraint (500) or corrupting the cache. Fix: check new_qty < 0 and return 400.
If you edit quantity directly in the product modal, the audit log misses it. Fix: compare old_qty vs new_qty in update_product and insert an "adjustment" movement.
Deleting a product deletes its movements permanently. The confirm dialog should warn: "Its stock movement history will also be deleted."
❓ Frequently asked
Why add a third table (stock_movements)?
Because quantity is not enough. A number like 42 tells you what's on the shelf, but not how it got there. Did 50 arrive and 8 sell? Or did 100 arrive and 58 get damaged? The stock_movements table logs each change as a row. The products.quantity column becomes a cached total updated transactionally. The audit log answers questions the single number can't.
Why CASCADE for movements but SET NULL for suppliers?
The two relationships have different semantics. A stock movement is about its product — it has no meaning without it, so it goes with the product (CASCADE). A supplier is a separate entity that products reference — if the supplier disappears, the products still exist, they just have no supplier (SET NULL). The FK action should match what the relationship actually means.
What does "transactional" mean here?
Recording a movement touches two tables: it inserts a row in stock_movements and updates products.quantity. Both writes must succeed together, or neither should. A transaction wraps both statements: either both commit or both roll back. The `with get_db() as conn:` context manager provides this.
Why compute the KPIs in SQL instead of JavaScript?
Because the frontend is paginated — it only holds 20 products at a time. It cannot compute total inventory value from 20 of 60,000 products. The aggregate has to happen where all the data lives: the database. One SELECT computes all four KPIs in one round-trip, regardless of dataset size.
How does server-side pagination change the frontend?
The client stops owning the full list. It sends page, perPage, sort, dir, and filters as query params; the server returns {items, page, perPage, total, pages}. Filtering, sorting, and slicing all happen in SQL. The client just displays the response.
Is this the final stage?
Yes. The Inventory build is four stages: Interface, Interactions, Backend, Advanced. Stage 04 completes it. Authentication, deployment, and multi-user support are separate concept courses — they are not Stage 05 of this build. The four-stage arc mirrors the CRM build: shell, then client logic, then a backend, then the advanced data layer. Each stage adds exactly one layer and stops.
What should I build next?
Not another stage of this app. Either take one of the concept courses (deployment, auth, SQL problem solvers), or build something new that reuses this pattern — the four-stage arc (shell → client logic → backend → advanced data layer) applies to any CRUD app: a task tracker, a booking system, a contacts manager. The inventory app is a template, not a starting point for infinite expansion.
🏁 You're done — the build is complete
Four stages. Three tables. Two foreign keys. A working Flask + SQLite backend with transactions, pagination, and an audit log. The inventory app is finished.
Build something new. The four-stage arc — shell → client logic → backend → advanced data layer — is a template, not an inventory-specific one. Apply it to a task tracker, a booking system, a contacts manager, a reading list. The pattern is the reusable thing.