Inventory Build

Stage 03 · Backend

Home › Apps › Inventory › Stage 03
🐍 Stage 03 · 2–3 hours

Give it a server.
Two tables. One foreign key.

The inventory app moves to Python Flask + SQLite. Products get a supplier — a second table, a foreign key, a JOIN. The UI barely changes: localStorage becomes fetch(), and every CRUD function becomes async. The derived-state logic (stock status, KPIs) is identical. This is where you learn that the frontend's job is the same whether the data is on the device or across a network — only the transport changes.

2–3 hours Intermediate Python · Flask · SQLite · fetch
Inventory hub Schema (2 tables) The 6 routes SKU rule, twice Frontend → fetch Run it

Short answer: Two tables. suppliers(id, name, email, created_at) and products(id, sku UNIQUE, name, category, price, cost, quantity, reorder_level, supplier_id FK → suppliers.id, created_at, updated_at). Six routes: list products, get one, create, update, delete, list suppliers (+ a categories helper). The client's localStorage becomes fetch(). The client's duplicate-SKU check is repeated as a UNIQUE constraint plus a 409 on the server. Everything else — derived state, KPIs, filter logic — is unchanged.

✅ The schema — two tables, one foreign key

Each supplier is stored once. Products point to it.

schema.sql
CREATE TABLE suppliers (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT NOT NULL UNIQUE,
  email      TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE products (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  sku           TEXT NOT NULL UNIQUE,
  name          TEXT NOT NULL,
  category      TEXT NOT NULL DEFAULT 'Uncategorized',
  price         REAL NOT NULL CHECK (price >= 0),
  cost          REAL NOT NULL CHECK (cost >= 0),
  quantity      INTEGER NOT NULL DEFAULT 0 CHECK (quantity >= 0),
  reorder_level INTEGER NOT NULL DEFAULT 10 CHECK (reorder_level >= 0),
  supplier_id   INTEGER,
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
);

CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_supplier ON products(supplier_id);
Why two tables, not one:

  Bad design — everything in products:
  
    products
    ├── id
    ├── sku
    ├── name
    ├── supplier_name      ← repeated on every row
    ├── supplier_email     ← repeated on every row
    └── ...
  
  If Acme supplies 20 products, "Acme Peripherals"
  is stored 20 times. Change one and you have to
  change all 20. One typo and the data disagrees
  with itself.

  Good design — normalized:
  
    suppliers
    ├── id (PK)
    ├── name (UNIQUE)
    └── email
  
    products
    ├── id (PK)
    ├── sku (UNIQUE)
    ├── ...
    └── supplier_id (FK → suppliers.id)
  
  Acme is stored once. Twenty products point
  to it. Change the name once, all twenty see
  the new name.

The UNIQUE constraint on sku:

  Stage 02 enforced "SKU must be unique" in
  the form submit handler. That check lives in
  JavaScript — anyone with DevTools can bypass it.

  Stage 03 adds UNIQUE on the column. SQLite
  refuses the INSERT. The check now lives in the
  database — it holds regardless of the client.

  Both stay. The client check is UX (instant
  feedback). The schema check is integrity (the
  last line of defense).

The CHECK constraints:

  price >= 0, cost >= 0, quantity >= 0,
  reorder_level >= 0

  Same idea. The client form has min="0" on
  those inputs. The schema refuses bad data even
  if the client lies. Defense in depth — every
  boundary validates.

The foreign key and ON DELETE:

  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
  ON DELETE SET NULL

  Delete a supplier → products.supplier_id becomes
  NULL. Products survive. The product still exists;
  it just has no supplier listed. This is the safe
  default for a nullable relationship.

  The alternatives:
    ON DELETE CASCADE — deletes the products too.
                        Usually wrong for inventory.
    ON DELETE RESTRICT — refuses the delete if any
                        product uses that supplier.
                        Sometimes right, inconvenient here.

  SQLite requires PRAGMA foreign_keys = ON per
  connection. The schema has it, and both app.py
  and database.py set it on every connect().

The indexes:

  idx_products_category
  idx_products_supplier

  Both columns are used in WHERE clauses:
  filter by category, join by supplier. Without
  an index, every filter is a full table scan.
  For 6 products, irrelevant. For 60,000, the
  index is the difference between 1ms and 50ms.

  You add them now because it's a habit. Index
  the columns you filter and join on.

✅ The 6 REST routes

Six endpoints. Same shape as the data.

GET /api/products

List, with ?q= and ?category=. JOINs suppliers for the supplier_name field.

POST /api/products

Create. Validates input. Checks SKU uniqueness. Returns 201 + new row.

PUT /api/products/<id>

Update. Same validation, same SKU check, excluding self. Returns updated row.

DELETE /api/products/<id>

Delete. Returns {"ok": true} or 404 if not found.

GET /api/suppliers

List for the dropdown. Returns [{id, name}].

GET /api/categories

Distinct categories. Used if you want to build filter chips dynamically.

app.py (excerpt)
@app.route("/api/products", methods=["GET"])
def list_products():
    q = (request.args.get("q") or "").strip().lower()
    category = (request.args.get("category") or "").strip().lower()

    sql = """
        SELECT p.*, s.name AS supplier_name
        FROM products p
        LEFT JOIN suppliers s ON s.id = p.supplier_id
    """
    clauses, params = [], []
    if q:
        clauses.append("(LOWER(p.name) LIKE ? OR LOWER(p.sku) LIKE ?)")
        params += [f"%{q}%", f"%{q}%"]
    if category and category != "all":
        clauses.append("LOWER(p.category) = ?")
        params.append(category)
    if clauses:
        sql += " WHERE " + " AND ".join(clauses)
    sql += " ORDER BY p.created_at DESC"

    with get_db() as conn:
        rows = conn.execute(sql, params).fetchall()
    return jsonify([row_to_dict(r) for r in rows])


@app.route("/api/products", methods=["POST"])
def create_product():
    data = request.get_json(silent=True) or {}
    error = validate_product(data, require_sku=True)
    if error:
        return jsonify({"error": error}), 400

    sku = data["sku"].strip()

    with get_db() as conn:
        # Server-side duplicate SKU check — the client check
        # from Stage 02, repeated at the boundary.
        dup = conn.execute(
            "SELECT id FROM products WHERE LOWER(sku) = LOWER(?)", (sku,)
        ).fetchone()
        if dup:
            return jsonify({"error": f'SKU "{sku}" already exists.'}), 409

        cur = conn.execute(
            """INSERT INTO products
               (sku, name, category, price, cost, quantity, reorder_level,
                supplier_id, created_at, updated_at)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
            (
                sku, data["name"].strip(),
                (data.get("category") or "Uncategorized").strip(),
                float(data["price"]), float(data["cost"]),
                int(data["quantity"]), int(data.get("reorderLevel") or 0),
                data.get("supplierId") or None,
                now_iso(), now_iso(),
            ),
        )
        conn.commit()
        # re-fetch to include supplier_name
        row = conn.execute(
            """SELECT p.*, s.name AS supplier_name
               FROM products p LEFT JOIN suppliers s ON s.id = p.supplier_id
               WHERE p.id = ?""",
            (cur.lastrowid,),
        ).fetchone()

    return jsonify(row_to_dict(row)), 201
Why a LEFT JOIN, not an INNER JOIN:

  INNER JOIN returns only products that HAVE
  a supplier. Products with supplier_id NULL
  disappear from the list.

  LEFT JOIN returns every product, and fills
  supplier_name with NULL if there's no match.
  The frontend renders "—" for NULL.

  Inventory products may legitimately have no
  supplier yet. LEFT JOIN preserves them.

Why filter server-side, not client-side:

  Stage 02 filtered in the browser:
    state.products.filter(...)

  That only works because all products were
  already in memory. With 60,000 products,
  shipping them all to the browser on every
  page load is wasteful.

  Stage 03 filters on the server:
    GET /api/products?q=mouse&category=electronics

  The database does the WHERE. Only matching
  rows cross the wire.

  The "low stock" filter is the exception —
  it's derived from quantity vs reorder_level,
  not a stored column, so it can't be a WHERE
  clause without computing it in SQL. For 6
  products, filtering client-side is fine.
  For 60,000, you'd add a computed column or
  a view. That's Stage 04+.

Why parameterized queries (? placeholders):

  NEVER interpolate user input into SQL:

    sql = f"SELECT * FROM products WHERE sku = '{sku}'"  ← SQL injection

  A user typing  ' OR '1'='1  as a SKU breaks
  the query. Use ? placeholders and pass a
  tuple. SQLite escapes them.

Why 409 for duplicate SKU, not 400:

  HTTP status codes tell the client what kind
  of error it is:
    400 = bad request (validation failure)
    404 = not found
    409 = conflict (the request is valid, but
          the server's current state conflicts)
  
  A duplicate SKU is a conflict: "you asked
  to create SKU-001, but SKU-001 already
  exists." 409 is the right code.

Why re-fetch after INSERT:

  The new row's supplier_name isn't in the
  INSERT payload — it comes from the JOIN.
  So we SELECT it back, including the JOIN,
  and return the full shape to the client.
  The client gets exactly what a subsequent
  GET would return.

✅ The SKU rule, twice

Client check for UX. Schema constraint for integrity.

Client (Stage 02)

Form submit handler checks state.products.find(...). Shows a toast. Blocks the save. Instant, no network round-trip.

Server (Stage 03)

UNIQUE on the column + explicit SELECT check + 409. Holds even if someone bypasses the client.

The rule: validation at every boundary. The client validates for UX. The server validates for integrity. The schema validates for absolute truth. If any one of the three is missing, the data can still be corrupted — but all three together make it very hard.

✅ Frontend → fetch()

Same UI. Data layer swapped.

frontend.js
// ─── Stage 02: localStorage ───
function loadProducts() {
  const raw = localStorage.getItem('mak-inventory-products');
  state.products = raw ? JSON.parse(raw) : SAMPLE_DATA.slice();
}
function saveProducts() {
  localStorage.setItem('mak-inventory-products', JSON.stringify(state.products));
}

// ─── Stage 03: fetch ───
async function refreshProducts() {
  try {
    state.products = await apiListProducts();
    render();
  } catch (err) {
    showToast('Could not load products: ' + err.message, true);
  }
}

async function apiCreateProduct(data) {
  return apiFetch('/api/products', {
    method: 'POST',
    body: JSON.stringify(data)
  });
}

async function apiFetch(path, options) {
  const res = await fetch(API + path, Object.assign({
    headers: { 'Content-Type': 'application/json' }
  }, options || {}));
  let body = null;
  try { body = await res.json(); } catch (_) {}
  if (!res.ok) {
    const msg = (body && body.error) ? body.error : ('Request failed (' + res.status + ')');
    throw new Error(msg);
  }
  return body;
}

// ─── The CRUD call becomes async ───
form.addEventListener('submit', async function (e) {
  e.preventDefault();
  const data = { /* same object as Stage 02 */ };
  if (!data.sku || !data.name) return;

  submitBtn.disabled = true;
  submitBtn.textContent = 'Saving…';
  try {
    if (fieldId.value) await apiUpdateProduct(fieldId.value, data);
    else await apiCreateProduct(data);
    closeModal();
    await refreshProducts();   // re-fetch — the server is the source of truth now
    showToast('Product saved.');
  } catch (err) {
    showToast(err.message, true);  // 409 duplicate SKU, 400 validation, etc.
  } finally {
    submitBtn.disabled = false;
    submitBtn.textContent = 'Save product';
  }
});

// ─── Debounced search hits the server ───
let searchTimer = null;
document.getElementById('search-input').addEventListener('input', function (e) {
  state.query = e.target.value;
  clearTimeout(searchTimer);
  searchTimer = setTimeout(refreshProducts, 250);
});
What changed:

  Stage 02                    Stage 03
  ─────────────────────────   ──────────────────────────
  loadProducts() sync          refreshProducts() async
  saveProducts() sync          await apiCreateProduct()
  localStorage.getItem        fetch('/api/products')
  JSON.parse                  res.json()
  No error handling           try/catch around every call
  Client is source of truth   Server is source of truth

What did NOT change:

  stockStatus(product)        ← identical
  stockLabel(product)         ← identical
  renderTable()               ← identical (one extra <td> for supplier)
  renderKPIs()                ← identical math
  formatMoney / formatNumber  ← identical
  escapeHtml                  ← identical
  The modal open/close logic  ← identical
  The toast                   ← identical

  Every piece of derived-state logic is
  untouched. Only the transport layer moved.

Why re-fetch after save:

  Stage 02 mutated state.products in place:
    state.products.unshift(newProduct);
    render();

  Stage 03 does not trust its own mutation.
  The server may have:
    → Generated the id
    → Added created_at / updated_at
    → Joined supplier_name
    → Applied a UNIQUE conflict
    → Normalized a category

  So the client re-fetches and renders what
  the server says is true. This is the
  "server as source of truth" pattern. It's
  one extra round-trip, but it guarantees
  the UI matches reality.

Why debounce the search:

  Without debounce, typing "keyboard" fires
  8 fetches. With a 250ms debounce, one fetch
  fires after the user stops typing.

  This is the same debounce pattern from the
  JS problem solvers, applied to the network.
  The cost of a keystroke is no longer a
  round-trip.

Error handling — the new requirement:

  In Stage 02, localStorage almost never fails.
  In Stage 03, the network can fail:
    → Flask not running → connection refused
    → 400 validation error
    → 404 product deleted by someone else
    → 409 duplicate SKU
    → 500 server bug

  Every fetch is wrapped in try/catch.
  Every error shows a toast. The UI never
  silently fails.

  This is the real difference between a
  demo and an app: the app handles failure.

✅ Run it

Six files. Three commands.

Folder structure:
apps/inventory/downloads/backend/
├── app.py                  ← Flask routes
├── database.py             ← connection helpers
├── schema.sql              ← two tables
├── seed.py                 ← sample data
├── requirements.txt        ← Flask + flask-cors
├── README.md
└── static/
    └── inventory-stage-03.html   ← the frontend
Commands:
cd apps/inventory/downloads/backend
python -m venv .venv
source .venv/bin/activate        # Windows: .venv\Scriptsctivate
pip install -r requirements.txt
python seed.py                    # creates inventory.db
python app.py                     # http://localhost:5000

Open http://localhost:5000/ — Flask serves the frontend from static/. The demo talks to the same origin, so no CORS issues. If you open the HTML directly as a file:// URL, the API constant in the frontend falls back to http://localhost:5000, and flask-cors allows the cross-origin request.

Test the API directly

curl http://localhost:5000/api/products. Then curl -X POST ... . The API works without the UI — that's the point of a backend.

Test the SKU rule server-side

POST a product with SKU SKU-001 twice via curl. The second returns 409 with an error message. The client check is bypassed; the server holds.

Test the foreign key

Delete a supplier row directly in SQLite. Products that referenced it now show supplier_id = NULL and render "—" in the Supplier column.

Break it

Stop Flask. Reload the frontend. You should see a toast "Could not load products". No silent failure. That's the error handling from this stage.

⚠️ Common mistakes

What to check if the app doesn't work.

1. Forgetting PRAGMA foreign_keys = ON

SQLite doesn't enforce foreign keys by default. Without the pragma, ON DELETE SET NULL doesn't fire. Fix: set it on every connection.

2. Not wrapping fetch in try/catch

Connection refused, 400, 409, 500 — all need handling. Fix: every await is inside try/catch, every error shows a toast.

3. Trusting the client check alone

Anyone can bypass the form with DevTools. Fix: UNIQUE on sku + server-side SELECT check + 409.

4. Interpolating user input into SQL

f"WHERE sku = '{sku}'" is SQL injection. Fix: ? placeholders + tuple params.

5. Mutating local state after save

The server may normalize, generate IDs, or reject. Fix: re-fetch after every successful write.

6. Not debouncing the search

Every keystroke fires a fetch. Fix: 250ms debounce before calling refreshProducts.

7. Forgetting Content-Type: application/json

request.get_json() returns None and validation fails. Fix: the apiFetch helper sets the header on every call.

8. Running app.py before seed.py

The DB doesn't exist; queries fail. Fix: python seed.py first. The app prints a warning if inventory.db is missing.

❓ Frequently asked

What changes from Stage 02 to Stage 03?

The UI barely changes. The derived-state logic (stockStatus, renderKPIs, getFiltered) is identical. What changes is where the data lives: localStorage.getItem/setItem is replaced with fetch() calls to Flask. The CRUD functions become async, and every state change re-fetches from the server.

Why two tables instead of one?

Products have a supplier. Storing the supplier name on every product row repeats it dozens of times and risks inconsistency. A suppliers table with products.supplier_id as a foreign key stores each supplier once. This is normalization — the first normal form of database design. The CRM only had contacts because there was nothing to join to. Inventory has suppliers.

Why keep the client-side SKU check if the server enforces UNIQUE?

Two different jobs. The client check is UX: instant feedback without a round-trip. The server UNIQUE constraint is integrity: it's the last line of defense. If someone opens DevTools and calls the API directly, the client check is bypassed — only the server check holds. Both are needed.

What is CORS and why is flask-cors needed here?

CORS is a browser security rule: a page from one origin cannot fetch from a different origin by default. If you open the demo HTML as a file:// URL and it tries to fetch http://localhost:5000, the browser blocks it unless the server sends Access-Control-Allow-Origin headers. flask-cors adds those headers. If you serve the HTML from Flask itself, CORS isn't strictly needed — but it supports the file:// workflow.

Why ON DELETE SET NULL on the foreign key?

If a supplier is deleted, its products shouldn't be deleted too — they still exist, they just have no supplier. ON DELETE SET NULL makes products.supplier_id become NULL when the supplier disappears. ON DELETE CASCADE would delete the products (usually wrong for inventory). ON DELETE RESTRICT would refuse the delete (inconvenient). SET NULL is the safest default for a nullable relationship.

Why debounce the search input?

Without debouncing, every keystroke fires a fetch request. Typing "keyboard" would fire 8 requests. With a 250ms debounce, only the final keystroke fires. This is the same debounce pattern from the JavaScript problem solvers — now applied to network requests instead of DOM updates.

🔗 Continue learning

📊 Next: Stage 04 — Advanced queries & supplier CRUD
Full supplier management, a paginated product list, server-side sorting, a stock-movement log table (a third table, a second FK), and aggregate endpoints for the KPIs. 3–4 hours.

Or explore related backend concepts:

🐍 Python problem solvers
Real answers to real queries — decorators, generators, context managers, Flask patterns, and more
← Back to Inventory build hub
Inventory Stage 03 · Backend