J03: Project 3: a transactional local reservation service

Prerequisites and observable goals#

Level: Third integrated project. Educational local service; not production-ready, authenticated, replicated, or Internet-hardened.

Purpose and nonrequirements#

Reserve units from a synthetic inventory stored in SQLite. The initial stock contains ten potions. A request supplies exactly a SKU and positive integer quantity plus a separate idempotency key. Successful reservations decrement stock and return the remaining quantity. Matching retries return the original status and response without decrementing again. Reusing a key with a different payload returns a conflict.

The server binds only to 127.0.0.1; its default port is chosen by the operating system. It has no authentication, TLS termination, distributed replication, production access control, durable external message delivery, or comprehensive resource-exhaustion defenses. Python’s basic HTTP server is not a recommended production deployment server. The connection timeout and body limit bound selected cases, but thread-per-connection handling can still consume resources under many clients. Do not expose this service to the public network. [PYSERVER]

Input, output, and failure contract#

POST /reserve requires JSON content type, one valid decimal Content-Length, a body of at most 4096 bytes, and exactly one Idempotency-Key header. Keys are 1–64 ASCII letters, digits, underscores, or hyphens. A SKU is a string of length 1–32; quantity is an actual integer, not a Boolean, from 1 to 1,000,000. Extra JSON fields, duplicate keys, nonstandard NaN/Infinity constants, unsupported transfer encoding, and malformed UTF-8 are rejected.

A new successful reservation returns status 201. Unknown SKU returns 404. Insufficient stock or key/payload conflict returns 409. Invalid application input returns 400; missing or invalid length returns 411; an oversized body returns 413; unsupported content type returns 415. Database errors return 503 with instructions to preserve the request key on retry. A selected body-read timeout returns 408 when a response can still be written. A broken client connection may prevent any response; that is precisely why retry identity matters.

GET /health reports process liveness only. It does not prove database readiness, replication health, or future request success. Initialization creates schema version one and rejects unknown schema versions rather than silently applying guessed migrations.

Architecture and transaction invariant#

Separate validation, database connection/initialization, transactional reservation, and HTTP adaptation. Each request uses its own database connection. Explicit transactions with BEGIN IMMEDIATE serialize the relevant SQLite writer behavior. A stock table stores current quantity. A request table stores unique key, canonical payload fingerprint, status, and response.

The diagram is a control-flow summary; an existing-key conflict also returns without changing stock. For a new key, the stock effect and request result commit together. The committed record is the durable evidence a retry consults after an ambiguous response failure. Recording a failed business outcome, such as insufficient stock, also makes matching retries return the same original outcome. A new logical attempt after restocking should use a new key under this contract.

Milestones and acceptance criteria#

First implement and test reserve directly against a temporary database. Verify a successful reservation, same-key retry, conflicting reuse, unknown SKU, insufficient stock, and invalid quantity. Next reopen the database after a reservation and verify deduplication survives the connection and process-style reconstruction boundary. Then add concurrent calls: twelve calls sharing one key must have one stock effect; twenty distinct one-item requests against stock ten must produce exactly ten successes without negative stock. Finally test loopback HTTP parsing, status codes, and body limits.

A lost reply is simulated by committing the operation and discarding its returned response before retrying. That is not a physical network-partition deployment test. The HTTP tests use a real loopback server, while persistence tests use a local temporary SQLite file. State clearly which evidence comes from which layer.

Complete solutions: J03#

Compare the first step where your work differs. A corrected answer is not yet an independent success.

Open every solution

Complete reference implementation#

"""J03: loopback-only educational reservation service; NOT production-ready."""
import argparse
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
import json
import re
import sqlite3
MAX_BODY = 4096
KEY = re.compile(r"[A-Za-z0-9_-]{1,64}\Z")

def connect(path):
    db = sqlite3.connect(path, isolation_level=None, timeout=1.0)
    db.execute("PRAGMA foreign_keys=ON")
    return db

def initialize(path):
    db = connect(path)
    try:
        version = db.execute("PRAGMA user_version").fetchone()[0]
        if version not in (0, 1):
            raise ValueError("unsupported schema version")
        db.execute("PRAGMA journal_mode=WAL")
        db.executescript("""
            BEGIN IMMEDIATE;
            CREATE TABLE IF NOT EXISTS stock(
                sku TEXT PRIMARY KEY, quantity INTEGER NOT NULL CHECK(quantity>=0));
            CREATE TABLE IF NOT EXISTS request(
                key TEXT PRIMARY KEY, fingerprint TEXT NOT NULL,
                status INTEGER NOT NULL, response TEXT NOT NULL);
            INSERT OR IGNORE INTO stock VALUES('potion',10);
            PRAGMA user_version=1;
            COMMIT;
        """)
    finally:
        db.close()

def validate(key, payload):
    if not isinstance(key, str) or KEY.fullmatch(key) is None:
        raise ValueError("invalid idempotency key")
    if not isinstance(payload, dict) or set(payload) != {"sku", "quantity"}:
        raise ValueError("exactly sku and quantity are required")
    sku, quantity = payload["sku"], payload["quantity"]
    if not isinstance(sku, str) or not 1 <= len(sku) <= 32:
        raise ValueError("invalid sku")
    if type(quantity) is not int or not 1 <= quantity <= 1000000:
        raise ValueError("quantity must be an integer in [1,1000000]")

def reserve(path, key, payload):
    validate(key, payload)
    fingerprint = json.dumps(payload, sort_keys=True, separators=(",", ":"))
    db = connect(path)
    try:
        db.execute("BEGIN IMMEDIATE")
        old = db.execute("SELECT fingerprint,status,response FROM request WHERE key=?",
                         (key,)).fetchone()
        if old:
            db.execute("COMMIT")
            if old[0] != fingerprint:
                return 409, {"error": "idempotency key conflict"}
            return old[1], json.loads(old[2])
        row = db.execute("SELECT quantity FROM stock WHERE sku=?",
                         (payload["sku"],)).fetchone()
        if row is None:
            status, response = 404, {"error": "unknown sku"}
        elif row[0] < payload["quantity"]:
            status, response = 409, {"error": "insufficient stock"}
        else:
            remaining = row[0] - payload["quantity"]
            db.execute("UPDATE stock SET quantity=? WHERE sku=?",
                       (remaining, payload["sku"]))
            status, response = 201, {"sku": payload["sku"], "remaining": remaining}
        db.execute("INSERT INTO request VALUES(?,?,?,?)",
                   (key, fingerprint, status, json.dumps(response, sort_keys=True)))
        db.execute("COMMIT")
        return status, response
    except Exception:
        if db.in_transaction: db.execute("ROLLBACK")
        raise
    finally:
        db.close()

def unique_object(pairs):
    result = {}
    for key, value in pairs:
        if key in result: raise ValueError("duplicate JSON key")
        result[key] = value
    return result

def reject_constant(value):
    raise ValueError("nonstandard JSON constant: " + value)

def make_server(path, port=0):
    class Handler(BaseHTTPRequestHandler):
        def setup(self):
            super().setup()
            self.connection.settimeout(2.0)

        def log_message(self, format, *args):
            pass  # no request-body or credential logging in this teaching server

        def send_json(self, status, obj):
            body = json.dumps(obj, sort_keys=True, allow_nan=False).encode("utf-8")
            self.send_response(status)
            self.send_header("Content-Type", "application/json")
            self.send_header("Content-Length", str(len(body)))
            self.end_headers()
            self.wfile.write(body)

        def do_GET(self):
            if self.path == "/health":
                self.send_json(200, {"ok": True})  # process liveness only
            else:
                self.send_json(404, {"error": "not found"})

        def do_POST(self):
            if self.path != "/reserve":
                self.send_json(404, {"error": "not found"}); return
            try:
                if self.headers.get("Transfer-Encoding") is not None:
                    raise ValueError("chunked request bodies are unsupported")
                if self.headers.get("Content-Type", "").split(";")[0] != "application/json":
                    self.send_json(415, {"error": "JSON content type required"}); return
                lengths = self.headers.get_all("Content-Length", [])
                if len(lengths) != 1 or not lengths[0].isdigit():
                    self.send_json(411, {"error": "one valid content length required"}); return
                length = int(lengths[0])
                if length > MAX_BODY:
                    self.send_json(413, {"error": "body too large"}); return
                body = self.rfile.read(length)
                if len(body) != length: raise ValueError("truncated body")
                payload = json.loads(body.decode("utf-8"), object_pairs_hook=unique_object,
                                     parse_constant=reject_constant)
                keys = self.headers.get_all("Idempotency-Key", [])
                if len(keys) != 1: raise ValueError("one idempotency key required")
                status, response = reserve(path, keys[0], payload)
            except (UnicodeError, ValueError) as error:
                status, response = 400, {"error": str(error)}
            except TimeoutError:
                status, response = 408, {"error": "request body timeout"}
            except sqlite3.Error:
                status, response = 503, {"error": "database unavailable; retry same key"}
            self.send_json(status, response)
    return ThreadingHTTPServer(("127.0.0.1", port), Handler)

def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--db", default="learning-service.sqlite")
    parser.add_argument("--port", type=int, default=0)
    args = parser.parse_args()
    initialize(args.db)
    with make_server(args.db, args.port) as server:
        print(f"Loopback only: http://127.0.0.1:{server.server_port}", flush=True)
        try: server.serve_forever()
        except KeyboardInterrupt: pass

if __name__ == "__main__":
    main()

Running the bounded service#

From the companion root, run:

python -m solutions.service --db learning-service.sqlite --port 0

The process prints its actual loopback port. A separate local client can send one JSON request with an Idempotency-Key header. The automated tests already construct such requests, so no external service, credentials, or paid tooling is required. Stop the foreground server with the terminal’s interrupt command; automated tests explicitly shut down and join their server threads.

The default database file is persistent within the chosen directory. Re-running initialization does not restock existing inventory: INSERT OR IGNORE preserves the current stock row. To begin a fresh exercise, use a new temporary database path rather than manually changing production-like state and then claiming the original test is unchanged.

Tests, failure cases, and debugging#

When a duplicate effect appears, inspect whether the client reused the same key, whether both key and effect share a transaction, and whether the database was unexpectedly recreated or request records removed. When concurrency tests oversell, inspect the transaction boundary rather than adding a sleep. When an HTTP test fails, distinguish header parsing, JSON validation, application policy, database availability, and response transport.

The reference suite tested direct and loopback paths, concurrent same-key and distinct-key calls, conflicting payloads, invalid inputs, and reopening stored state. It did not run a multi-host deployment, kill the operating system during disk writes, measure filesystem durability under power loss, perform a penetration test, or establish load capacity. Those omissions prevent calling this a production reservation backend.

SQLite is suitable for this bounded exercise because a single-file local database and explicit transactions make the invariants inspectable. A different database or replicated architecture needs its own isolation and recovery analysis. Adding replicas without a consistency protocol would not preserve the proof automatically. Similarly, replacing the request table with an in-memory cache would lose restart durability.

Evaluation rubric and continuation#

Highest setup credit requires exact request identity and payload validation. Highest reasoning credit locates the atomic commit and explains lost-reply histories. Highest implementation credit preserves loopback binding and correct status behavior. Highest verification credit includes concurrent and reopened-state tests, not only a happy-path HTTP request. Highest communication credit states the storage, retention, authentication, and deployment limitations. Unassigned extensions include authenticated local users, bounded worker pools, tested database backups, and an outbox with a deduplicating receiver; each adds a new contract rather than completing an already claimed feature.

Retention and readiness#

Retrieve C11 isolation, C12 framing, C13 ambiguous failure, and C17 trust boundaries. Advance when you can draw a crash timeline and explain the recovered database state without running the code first.