A distinct project brief
Build a personal job application ledger. A learner records a company and role, updates the application status, reviews the list, and deletes an accidental entry. Unlike the repair desk or URL catalog, this domain includes private records and complete create, read, update, and delete operations. The central invariant is that every operation stays within its supplied owner scope.
This is a business-layer exercise, not an authentication service. The owner argument identifies the scope chosen by the caller; it does not establish who that caller is. The tests use fictional owners. A public adapter must derive the owner from verified server-side identity, never from an editable form field or an arbitrary request header. Review authentication before adding that boundary.
You need Python 3.9 or newer with SQLite, a terminal, and an editor. No packages are installed. Plan one session for CRUD and one for negative tests. Revisit testing if isolated fixtures are new.
Requirements and data model
An application has a generated identifier, owner, company, role, and status. Company and role are trimmed, nonempty text with limits of 120 and 160 characters respectively. Status is exactly one of applied, interviewing, offer, rejected, or withdrawn. This version permits movement between any valid statuses; it does not pretend that real hiring pipelines always move forward.
The primary key combines owner and identifier, with owner first. That structure supports owner-scoped access and ordered listing. Missing records and records belonging to another owner produce the same absence error. There is no global “does this identifier exist?” query that leaks another person's application.
Updates replace all editable fields. Partial-update semantics, deadlines, notes, attachments, and search are future changes with their own contracts. Do not store derived counts yet. Explore the schema in the SQL playground, then explain how its access pattern differs from the shortener's key lookup.
Milestone 1: implement complete CRUD
Create a separate workspace:
mkdir job-ledger
cd job-ledger
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
Save this complete implementation as jobs.py. Parameter binding keeps values separate from SQL, and each write uses a transaction with explicit connection closure. The sqlite3 reference documents these standard-library mechanisms.
import sqlite3
import uuid
from contextlib import closing
STATUSES = ("applied", "interviewing", "offer", "rejected", "withdrawn")
def validate_owner(owner):
if not isinstance(owner, str) or not 1 <= len(owner) <= 80 or owner != owner.strip():
raise ValueError("owner must be nonempty text without outer whitespace")
return owner
def validate_fields(company, role, status):
cleaned = []
for value, limit in ((company, 120), (role, 160)):
if not isinstance(value, str) or not 1 <= len(value.strip()) <= limit:
raise ValueError("company and role must be valid nonempty text")
cleaned.append(value.strip())
if not isinstance(status, str) or status not in STATUSES:
raise ValueError("invalid application status")
return (*cleaned, status)
class JobStore:
def __init__(self, path):
self.path = str(path)
with closing(self.connect()) as db, db:
db.execute("""
CREATE TABLE IF NOT EXISTS applications (
owner TEXT NOT NULL CHECK(length(owner) BETWEEN 1 AND 80),
id TEXT NOT NULL CHECK(length(id) = 32),
company TEXT NOT NULL CHECK(length(trim(company)) BETWEEN 1 AND 120),
role TEXT NOT NULL CHECK(length(trim(role)) BETWEEN 1 AND 160),
status TEXT NOT NULL CHECK(status IN
('applied', 'interviewing', 'offer', 'rejected', 'withdrawn')),
PRIMARY KEY (owner, id)
)
""")
def connect(self):
db = sqlite3.connect(self.path, timeout=2)
db.row_factory = sqlite3.Row
return db
@staticmethod
def find(db, owner, application_id):
row = db.execute(
"SELECT id, company, role, status FROM applications WHERE owner = ? AND id = ?",
(owner, application_id),
).fetchone()
if row is None:
raise LookupError("application not found")
return dict(row)
def create(self, owner, company, role, status="applied"):
owner = validate_owner(owner)
company, role, status = validate_fields(company, role, status)
application_id = uuid.uuid4().hex
with closing(self.connect()) as db, db:
db.execute(
"INSERT INTO applications (owner, id, company, role, status) VALUES (?, ?, ?, ?, ?)",
(owner, application_id, company, role, status),
)
return self.find(db, owner, application_id)
def list(self, owner):
owner = validate_owner(owner)
with closing(self.connect()) as db:
return [dict(row) for row in db.execute(
"SELECT id, company, role, status FROM applications WHERE owner = ? ORDER BY id",
(owner,),
)]
def get(self, owner, application_id):
owner = validate_owner(owner)
with closing(self.connect()) as db:
return self.find(db, owner, application_id)
def update(self, owner, application_id, company, role, status):
owner = validate_owner(owner)
company, role, status = validate_fields(company, role, status)
with closing(self.connect()) as db, db:
cursor = db.execute(
"UPDATE applications SET company = ?, role = ?, status = ? WHERE owner = ? AND id = ?",
(company, role, status, owner, application_id),
)
if cursor.rowcount == 0:
raise LookupError("application not found")
return self.find(db, owner, application_id)
def delete(self, owner, application_id):
owner = validate_owner(owner)
with closing(self.connect()) as db, db:
cursor = db.execute(
"DELETE FROM applications WHERE owner = ? AND id = ?",
(owner, application_id),
)
if cursor.rowcount == 0:
raise LookupError("application not found")
Identifier order is stable but not chronological: UUIDs do not express application dates. Add an explicit timestamp if users need that ordering. SQLite constraints backstop malformed direct writes; application validation supplies the user-facing errors. Initialization creates a fresh schema but does not migrate an existing one.
Milestone 2: test ownership, not just happy paths
Save the following as test_jobs.py. A cross-owner write test must also verify that the original record survived unchanged. Checking only the exception could miss a method that writes first and reports an error afterward.
import tempfile
import unittest
from pathlib import Path
from jobs import JobStore, STATUSES
class JobTests(unittest.TestCase):
def setUp(self):
temp = tempfile.TemporaryDirectory()
self.addCleanup(temp.cleanup)
self.path = Path(temp.name) / "jobs.db"
self.store = JobStore(self.path)
def test_crud_and_reopen(self):
item = self.store.create("alice", " Example Ltd ", " Developer ")
self.assertEqual(item["company"], "Example Ltd")
self.assertEqual(item["status"], "applied")
self.assertEqual(JobStore(self.path).get("alice", item["id"]), item)
changed = self.store.update("alice", item["id"], "Example Ltd", "Backend developer", "offer")
self.assertEqual(changed["status"], "offer")
self.assertEqual(self.store.list("alice"), [changed])
self.store.delete("alice", item["id"])
self.assertEqual(self.store.list("alice"), [])
with self.assertRaises(LookupError):
self.store.get("alice", item["id"])
def test_owner_isolation(self):
item = self.store.create("alice", "Private Co", "Engineer")
self.store.create("bob", "Other Co", "Designer")
self.assertEqual(len(self.store.list("bob")), 1)
actions = (
lambda: self.store.get("bob", item["id"]),
lambda: self.store.update("bob", item["id"], "Changed", "Changed", "rejected"),
lambda: self.store.delete("bob", item["id"]),
)
for action in actions:
with self.assertRaises(LookupError):
action()
self.assertEqual(self.store.get("alice", item["id"]), item)
def test_statuses_and_invalid_write(self):
item = self.store.create("alice", "Company", "Role")
for status in STATUSES:
self.assertEqual(self.store.update("alice", item["id"], "Company", "Role", status)["status"], status)
before = self.store.get("alice", item["id"])
with self.assertRaises(ValueError):
self.store.update("alice", item["id"], "Changed", "Role", "hired")
self.assertEqual(self.store.get("alice", item["id"]), before)
def test_invalid_fields_and_owners(self):
for company, role in ((" ", "Role"), ("x" * 121, "Role"), (None, "Role"), ("Co", "x" * 161)):
with self.assertRaises(ValueError):
self.store.create("alice", company, role)
for owner in (None, "", " alice ", "x" * 81):
with self.assertRaises(ValueError):
self.store.list(owner)
self.assertEqual(self.store.list("alice"), [])
def test_missing_and_sql_like_values(self):
with self.assertRaises(LookupError):
self.store.delete("alice", "0" * 32)
title = "'); DROP TABLE applications; --"
item = self.store.create("alice", title, "Engineer")
self.assertEqual(self.store.get("alice", item["id"])["company"], title)
self.assertEqual(self.store.list("bob"), [])
if __name__ == "__main__":
unittest.main()
python3 -m unittest -v
python3 -c "from jobs import JobStore; s=JobStore('jobs.db'); x=s.create('demo-owner','Example Ltd','Developer'); print(x); print(s.list('demo-owner')); s.delete('demo-owner',x['id'])"
Expect five passing tests. The demo uses a literal fictional owner to show the API signature, not to simulate login. Tests establish the module's scoped behavior when callers supply the correct identity; they cannot prove a future HTTP adapter chooses that identity correctly.
Milestone 3: design a private API extension
Specify create and list routes under /api/applications, with read, update, and delete under /api/applications/{id}. A successful delete can return 204 without a JSON body. Document that difference before reusing the repair-desk browser helper, which always decodes JSON. Use backend APIs and the API playground to rehearse the contract.
Map invalid editable fields to 400 and unavailable records to 404. Derive owner from a verified session on every route, including listing and deletion. Authentication proves identity; the owner predicates constrain access. Both are needed. Never expose a global list method simply to make an admin dashboard convenient.
Failure, deployment, and review
Two tabs updating the same application can overwrite each other because this version uses last-write-wins semantics. A later revision can add a version column and conditional updates, then test that stale writes fail. Deletion is deliberately not idempotent here: a second deletion reports absence. Explain that contract to clients rather than relying on the button disappearing.
Do not put real candidate details in a shared demonstration database. A production tracker needs verified identity, retention and deletion policies, backups, and careful logs. Parameterized SQL prevents values from becoming query syntax; it does not establish privacy. If browser sessions use cookies, design CSRF protection before enabling writes.
Run locally until a production serving stack and identity boundary exist. Render persistent disks can preserve SQLite only under the mounted path and limit the service to one instance. Multiple application instances require a storage architecture designed for that topology. A preview should use isolated synthetic records, never a copied personal ledger. Read deployment and rehearse restoration before treating the service as dependable.
Score the review out of ten: two each for complete CRUD, field/status validation, owner isolation, meaningful negative tests, and honest deployment assumptions. Cross-owner reads or writes are blocking defects. Interview followups: why must update and delete include owner predicates? What does this module fail to prove about authentication? How would you add optimistic concurrency? How would you sort by application date without confusing UUID order with time?
Compare the URL shortener with the repair-desk backend. Its full-stack milestone offers an integration pattern, and the team workflow provides a review process for a tracker-specific extension.