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.
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.
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.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.
// ─── 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.
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
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.
PRAGMA foreign_keys = ONSQLite doesn't enforce foreign keys by default. Without the pragma, ON DELETE SET NULL doesn't fire. Fix: set it on every connection.
Connection refused, 400, 409, 500 — all need handling. Fix: every await is inside try/catch, every error shows a toast.
Anyone can bypass the form with DevTools. Fix: UNIQUE on sku + server-side SELECT check + 409.
f"WHERE sku = '{sku}'" is SQL injection. Fix: ? placeholders + tuple params.
The server may normalize, generate IDs, or reject. Fix: re-fetch after every successful write.
Every keystroke fires a fetch. Fix: 250ms debounce before calling refreshProducts.
Content-Type: application/jsonrequest.get_json() returns None and validation fails. Fix: the apiFetch helper sets the header on every call.
app.py before seed.pyThe 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
Or explore related backend concepts: