10 · Capstone: a trustworthy local job notebook

Build a local notebook for jobs and their execution events. It should answer “what ran?”, “how much time did each job accumulate?” and “which jobs could run after all their prerequisites finish?” It should not lose the distinction between an event and a delivery attempt. The goal is a small system whose guarantees you can explain—not an impressive architecture diagram with untested boxes.

The assignment and its boundaries

Your submission has four parts:

1. A catalog mapping job IDs to prerequisite IDs, validated before topological ordering. Reject references to undefined jobs and cycles. Independent jobs may be ordered arbitrarily unless you explicitly promise deterministic tie-breaking.
2. An ingestion boundary for a stable event ID, a job name and a nonnegative integer duration. Same ID and same canonical content returns the original operation result; same ID with different content is a conflict. Distinct IDs remain distinct even with identical payloads.
3. A persistent representation with one event per logical identity and a per-job count/total, changed atomically. The reference below isolates this component in memory; your optional disk-backed extension must add restart tests before claiming persistence.
4. Read-only summaries, a shortest-hop dependency explanation using the correct outgoing-edge representation, and a top-k report. Reuse earlier lessons rather than pasting unexamined recipes. Do not use knapsack to select jobs with dependencies without extending its state model.

No HTTP server, account service, external queue, cloud deployment or machine-learning model is needed. An adapter that reads a local file is optional; if added, define file size limits, encoding and error reporting. Do not silently convert a partially malformed batch into “all accepted.”

A runnable reference for the atomic core

The source-backed building blocks are SQLite transactions, table constraints and Python's parameter-binding API. They supply mechanisms. The SQLite UPSERT reference specifies that ON CONFLICT(job) responds to the uniqueness conflict and that excluded.duration_ms refers to the proposed inserted value. The application invariant is ours: the totals table equals the aggregation of all committed event rows. An event record and its effect must commit together.

Save this block as notebook_core.py and run python3 notebook_core.py. It is self-contained, uses only an in-memory database and should exit without output. Assertions are acceptance checks; explicit exceptions implement runtime input validation.

import sqlite3
 
 
def open_notebook():
    db = sqlite3.connect(":memory:", isolation_level=None)
    db.executescript("""
        CREATE TABLE events(
            id TEXT PRIMARY KEY NOT NULL,
            job TEXT NOT NULL,
            duration_ms INTEGER NOT NULL CHECK(
                typeof(duration_ms) = 'integer' AND duration_ms >= 0)
        );
        CREATE TABLE totals(
            job TEXT PRIMARY KEY NOT NULL,
            runs INTEGER NOT NULL CHECK(typeof(runs) = 'integer' AND runs > 0),
            duration_ms INTEGER NOT NULL CHECK(
                typeof(duration_ms) = 'integer' AND duration_ms >= 0)
        );
    """)
    return db
 
 
def record(db, event_id, job, duration_ms, fail_at=None):
    if (not isinstance(event_id, str) or not 1 <= len(event_id) <= 128
            or event_id.strip() != event_id):
        raise ValueError("event ID must be 1..128 characters without outer whitespace")
    if not isinstance(job, str) or not job.strip() or len(job) > 128:
        raise ValueError("job must be a nonblank string of at most 128 characters")
    job = job.strip()  # the canonical payload uses this normalized name
    if type(duration_ms) is not int or not 0 <= duration_ms <= 1_000_000_000:
        raise ValueError("duration must be an integer in [0, 1000000000]")
    if db.in_transaction:
        raise ValueError("record requires a connection without an active transaction")
    if fail_at not in (None, "after_event", "after_total"):
        raise ValueError("unknown test failure point")
    db.execute("BEGIN IMMEDIATE")
    try:
        old = db.execute(
            "SELECT job, duration_ms FROM events WHERE id = ?", (event_id,)
        ).fetchone()
        if old is not None:
            if old != (job, duration_ms):
                raise ValueError("event ID reused with different content")
        else:
            db.execute("INSERT INTO events VALUES (?, ?, ?)",
                       (event_id, job, duration_ms))
            if fail_at == "after_event":
                raise RuntimeError("injected after event insert")
            db.execute("""
                INSERT INTO totals VALUES (?, 1, ?)
                ON CONFLICT(job) DO UPDATE SET
                    runs = totals.runs + 1,
                    duration_ms = totals.duration_ms + excluded.duration_ms
            """, (job, duration_ms))
            if fail_at == "after_total":
                raise RuntimeError("injected after aggregate update")
        db.execute("COMMIT")
    except Exception:
        if db.in_transaction:
            db.execute("ROLLBACK")
        raise
    return {"event_id": event_id, "job": job, "duration_ms": duration_ms}
 
 
def assert_reconciled(db):
    actual = db.execute(
        "SELECT job, runs, duration_ms FROM totals ORDER BY job"
    ).fetchall()
    expected = db.execute("""
        SELECT job, COUNT(*), SUM(duration_ms)
        FROM events GROUP BY job ORDER BY job
    """).fetchall()
    assert actual == expected
 
 
db = open_notebook()
try:
    first = record(db, "e1", " ingest ", 20)  # imagine its reply is lost
    retry = record(db, "e1", "ingest", 20)
    assert retry == first
    record(db, "e2", "ingest", 20)  # identical payload, distinct logical event
    assert db.execute("SELECT * FROM totals").fetchall() == [("ingest", 2, 40)]
 
    try:
        record(db, "e1", "ingest", 99)
    except ValueError:
        pass
    else:
        raise AssertionError("conflict was accepted")
 
    for point in ("after_event", "after_total"):
        try:
            record(db, "failed-" + point, "publish", 9, fail_at=point)
        except RuntimeError:
            pass
        else:
            raise AssertionError("failure injection did not fire")
        assert not db.in_transaction
        assert db.execute("SELECT COUNT(*) FROM events").fetchone() == (2,)
        assert_reconciled(db)
 
    record(db, "e3", "publish", 0)
    for bad in (True, -1, 1.5, 1_000_000_001):
        try:
            record(db, "bad", "ingest", bad)
        except ValueError:
            pass
        else:
            raise AssertionError("invalid duration accepted")
    assert db.execute("SELECT COUNT(*) FROM events").fetchone() == (3,)
    assert_reconciled(db)
finally:
    db.close()

Read the failure boundaries

There are three important intervals. Before BEGIN, validation can fail without opening a transaction. After the event insert but before the aggregate update, the injected error rolls back the event. After both writes but before commit, the second injected error rolls back both. After a successful commit but before the client observes the return value, the event already exists; replaying the same request returns the same per-event result without increasing the aggregate.

The result deliberately does not contain the current global total: later events can change that total, while the accepted event's result remains stable. The function's transaction belongs to the function. A caller with an active transaction is rejected rather than silently committed or rolled back. This ownership rule is as important as the SQL itself.

BEGIN IMMEDIATE establishes the write transaction before the key lookup. This aligns the check and write under SQLite's single-writer model. It can fail under contention; this example does not implement busy retries. A unique primary key remains a database-level invariant. The exception tests do not simulate a process being killed, a power loss or a second simultaneous connection.

Totals are derivable rather than fundamental facts. Maintaining them eagerly makes reads simpler, but creates an invariant to reconcile. For a small notebook, computing totals on demand from events may be the better design. Here the second table exists to make atomic multi-write behavior observable. The integer checks also prevent silent acceptance if a sum grows beyond SQLite's integer representation and becomes a floating value; capacity planning and a larger-number policy remain future design decisions.

Integration exercises and answer sketches

Exercise A — catalog policy. Before recording an event, require its canonical job name to exist in a catalog. Reject missing prerequisite definitions before invoking dependency_order. Write tests for a misspelled prerequisite, a self-cycle, an independent job and a valid chain.

Sketch: validate that the union of prerequisite IDs is a subset of catalog keys, then call the graph utility. For the reference ingestion core, perform the membership check before opening its transaction or use a transactional catalog table with a foreign key when catalog mutation is permitted. An in-memory catalog checked before a later database write is not an atomic catalog-consistency guarantee under concurrent mutation.

Exercise B — reconciliation. Deliberately corrupt a total in a disposable database. Make assert_reconciled fail, then rebuild totals from committed events in one transaction.

Sketch: aggregate events grouped by job and replace derived totals inside one transaction. Do not alter or delete the event history to force a passing check. This is an exercise on disposable data, not a production repair command.

Exercise C — disk extension. Replace the in-memory database with a temporary file you own, ingest an event, close the connection, reopen it without re-creating the schema, retry, and check one counted event.

Sketch: separate schema initialization from connection creation. A successful close/reopen test establishes clean-restart persistence for that environment; it still does not establish crash durability. The reference block above does not perform this extension.

Assessment rubric

Score the submitted evidence, not the number of files. A sensible completion threshold is 80/100 with no mandatory invariant failing. This threshold is a course policy, not an empirically validated predictor of interview performance.

Mandatory invariants: a failed transaction cannot leave a counted partial event; a duplicate logical event cannot increment totals; a conflicting identity must not overwrite the original; cyclic prerequisites must not produce an apparently complete schedule. A submission failing any of these is not complete regardless of points.

What this course did not promise

This is a foundations course, not a full operating-systems, distributed-consensus, security, cloud or interview curriculum. Advanced trees, string matching, segment trees, weighted paths and broad SQL optimization remain outside the reviewed implementation. The separate deep-learning track owns neural networks and LLM topics; they are not smuggled into this capstone under a generic “AI product” label.

The old chronological sessions and weekly drills are recoverably archived. Their URLs are migration decisions, not proof that every old exercise has a new equivalent. Where this course covers only a subset, a removal notice is more honest than an unrelated redirect.

Return to the course map, or use the algorithm practice route to repeat the reasoning without a new technology stack.