Inventory Build

Stage 04 · Advanced — Final Stage

Home › Apps › Inventory › Stage 04
📊 Stage 04 · 3–4 hours · FINAL

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.

3–4 hours Intermediate SQL · Flask · transactions · fetch
Inventory hub Third table Transactions Pagination + sorting KPIs in SQL Supplier CRUD Run it Done — next steps

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.

schema.sql (movements)
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.

movements.py
@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.

app.py (list_products)
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.

stats.py
@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.

suppliers.py
@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.

Folder structure:
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
Commands — note the reseed:
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.

1. Not reseeding after the schema change

The old two-table DB doesn't have stock_movements. Every movement query fails. Fix: python seed.py (destructive — recreates the DB).

2. Updating quantity without a transaction

If the INSERT fails after the UPDATE, the cache and log disagree. Fix: both writes inside with get_db() as conn:.

3. Interpolating sort params into ORDER BY

f"ORDER BY {sort}" is SQL injection — placeholders don't work in ORDER BY. Fix: the SORT_COLUMNS whitelist.

4. Computing KPIs from the current page

The client has 20 rows, not all of them. Summing state.products gives the wrong total. Fix: GET /api/stats.

5. Using INNER JOIN for suppliers

Suppliers with no products vanish. Fix: LEFT JOIN + GROUP BY.

6. Forgetting the negative-stock guard

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.

7. Not logging quantity changes from the edit form

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.

8. Forgetting CASCADE means data loss

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.

The full build
✓ Stage 01 — Interface · static shell, layout, KPI markup, modal structure
✓ Stage 02 — Interactions · CRUD + localStorage, derived state, XSS-safe rendering, duplicate-SKU check
✓ Stage 03 — Backend · Flask + SQLite, two tables, six REST routes, client check repeated server-side
✓ Stage 04 — Advanced · third table, transactions, pagination, SQL KPIs, full supplier CRUD
📦 Back to the Inventory hub
All four stages in one place, with the download links for each stage's working demo and the full backend source.

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.

← Back to Inventory build hub
Inventory Stage 04 · Advanced · Final