Skip to content

Module 4: Networks, APIs, and storage

Turn the task tracker into a local HTTP service backed by SQLite. Keep the domain behavior from Module 1: this module changes how clients reach it and how records are stored.

Learning outcomes

By the end of this module, you should be able to:

  • trace a request through name resolution, transport, HTTP, application code, SQL, and back;
  • define an API contract before implementing handlers;
  • use HTTP methods, status codes, headers, and representations deliberately;
  • encode data invariants as database constraints;
  • use parameterized SQL and explicit transactions;
  • add an index from a query requirement and inspect its query plan;
  • design pagination, timeout, and retry behavior without hiding failure.

Keep the architecture small

Reuse the existing task operations. Add two adapters:

CLI ───────┐
           ├→ task operations → SQLite repository → database file
HTTP API ──┘

The CLI and HTTP handlers should not implement separate versions of “complete task” or “validate title.” The transport converts input and output; the domain decides behavior; the repository executes SQL.

Use SQLite for this module. A small framework is acceptable for HTTP routing and JSON handling, but do not add an ORM, cache, queue, reverse proxy, or separate database server. Bind to 127.0.0.1; authentication and internet exposure belong to later modules.

Define the HTTP contract

Implement this resource surface:

Method and path Behavior Success
POST /tasks Create a task 201 Created with task and Location header
GET /tasks/{id} Retrieve one task 200 OK with task
GET /tasks?limit=&after= List a page ordered by ID 200 OK with items and next cursor
PATCH /tasks/{id} Change title or completion state 200 OK with updated task
DELETE /tasks/{id} Delete a task 204 No Content

Use JSON for request and response bodies. Keep one error shape:

{
  "error": {
    "code": "invalid_title",
    "message": "title must not be blank"
  }
}

Map outcomes consistently:

  • malformed JSON or invalid query syntax → 400 Bad Request;
  • valid JSON that violates a task rule → 422 Unprocessable Content;
  • missing task → 404 Not Found;
  • reused idempotency key with different input → 409 Conflict;
  • unsupported content type → 415 Unsupported Media Type;
  • unexpected internal failure → 500 Internal Server Error without implementation details.

Document the exact fields, types, defaults, maximum limit, and whether unknown JSON fields are rejected. A contract is incomplete when clients must inspect the implementation to learn these rules.

Design the schema from invariants

Create a numbered migration rather than creating tables implicitly at application startup:

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL CHECK (length(trim(title)) BETWEEN 1 AND 200),
    done INTEGER NOT NULL DEFAULT 0 CHECK (done IN (0, 1))
);

CREATE TABLE task_creations (
    request_key TEXT PRIMARY KEY,
    normalized_title TEXT NOT NULL,
    task_id INTEGER NOT NULL UNIQUE,
    response_body TEXT NOT NULL
);

The application still validates requests for useful errors. Database constraints protect the invariant when another code path or a bug bypasses that validation.

AUTOINCREMENT is intentional here: an ID referenced by a retained idempotency record must not later identify a different task. task_creations stores the original response and deliberately outlives task deletion, so it is not a foreign key. A real service would define a retention window; this lab retains keys indefinitely.

Keep connection creation in one place and use parameter placeholders for every client-provided value.

Migrate the existing JSON data

Write a one-use migration command that:

  1. reads and validates the entire JSON file;
  2. starts a database transaction;
  3. inserts every task with its existing ID;
  4. commits only after all rows succeed;
  5. leaves the source file untouched;
  6. reports a count and any validation failure.

Run it twice against a copy during testing. The second run should fail clearly or produce the same state by an explicitly documented rule; it must not create silent duplicates.

Compare task count, IDs, titles, and completion states before declaring the migration successful. A successful exit code alone is not data verification.

Implement one request end to end

Start with GET /tasks/{id}:

  1. router extracts the path ID;
  2. handler validates its syntax and range;
  3. task operation asks the repository for the record;
  4. repository runs parameterized SQL;
  5. handler maps found or missing state to the contract;
  6. server serializes JSON and writes the HTTP response.

Only after this slice works should you add the other routes. This keeps failures attributable to one layer.

Make creation safe to retry

POST normally creates a new record each time. A client that times out after the server commits cannot know whether retrying will create a duplicate.

Require an Idempotency-Key for task creation and handle it in one transaction:

  1. normalize and validate the title;
  2. look up the request key;
  3. if key and title match, return the stored original 201 response;
  4. if the key exists with different input, return 409;
  5. otherwise insert the task and key mapping;
  6. commit both records together.

Test a failure between the two inserts. The transaction must leave neither half committed. Do not implement automatic retries inside the server until you can name the transient failure they address and bound the attempts.

Add cursor pagination

Use the primary key as a simple cursor:

SELECT id, title, done
FROM tasks
WHERE id > ?
ORDER BY id
LIMIT ?;

Fetch one more row than the requested limit. Return only the requested rows and include a next cursor when the extra row proves another page exists. Validate and cap limit; never interpolate it or the cursor into SQL text.

This scheme is stable for inserts with larger IDs, but deletion may make pages contain fewer items and it cannot support arbitrary sort orders. Record those boundaries rather than solving hypothetical pagination needs.

Inspect the query plan

Use SQLite's planner output:

EXPLAIN QUERY PLAN
SELECT id, title, done
FROM tasks
WHERE done = 0
ORDER BY id
LIMIT 50;

First measure the query against representative data. If filtering incomplete tasks is frequent and a scan is material, add the smallest index that matches the access pattern, then inspect and measure again.

Do not create an index for every column. Each index occupies space and adds work to inserts, updates, and deletes. Keep a note beside each non-primary index stating the query it supports and evidence for retaining it.

Observe the network path

Start the API on loopback and inspect it:

curl -v 'http://localhost:8080/tasks?limit=10'
lsof -nP -iTCP:8080 -sTCP:LISTEN

Use curl -v to identify the resolved address, connection, request line, headers, status, and response. Compare localhost with 127.0.0.1; depending on local configuration, localhost may resolve to IPv4, IPv6, or both.

For this local HTTP connection, trace:

URL → local name resolution → IP address and port → TCP connection
→ HTTP request bytes → handler → SQL transaction → HTTP response bytes

TLS would add certificate validation and encryption between transport and HTTP. Do not claim the local exercise demonstrates production HTTPS, routing across the internet, or QUIC.

Test the contract

Automate requests against an isolated temporary database. Cover at least:

  • create, retrieve, update, list, and delete success;
  • malformed JSON and incorrect Content-Type;
  • blank title and invalid IDs;
  • missing task;
  • pagination boundary and invalid cursor;
  • repeated creation with the same key and input;
  • repeated key with different input;
  • rollback when an idempotent creation fails midway;
  • database constraint rejection through a direct repository call;
  • JSON migration success and rollback on one invalid record.

Also test that every response declares the expected content type, has the documented shape, and returns no body with 204.

Timeouts and failure boundaries

Set a client timeout in tests and document a server request limit appropriate to the chosen framework. A timeout does not prove that the server stopped processing; this is why retry semantics and idempotency matter.

Classify failures by layer before changing code:

Symptom First checks
Name cannot be resolved host spelling and resolver result
Connection refused address, port, listener, process state
Connection established but no response handler state, blocking query, request timeout
4xx response request versus documented contract
5xx response server logs, transaction state, dependency error
Slow list request timing by layer and query plan

Evidence to keep

Your repository should now include:

  • the API contract and executable request examples;
  • numbered schema migrations;
  • the JSON-to-SQLite migration and verification result;
  • API and repository tests using isolated databases;
  • one annotated verbose HTTP exchange;
  • query plans and before/after measurements for any added index;
  • a diagram tracing one request from client to database and back.

Completion check

You are ready for the next module when you can:

  • explain every byte-level boundary in the local request path at the appropriate level;
  • predict status codes and response shapes without running the server;
  • show that constraints reject invalid stored state;
  • demonstrate atomic idempotent creation and migration rollback;
  • explain why parameter binding prevents SQL input from becoming executable syntax;
  • justify every index from a query and its measured plan;
  • distinguish connection failure, timeout, application error, and database error.

Read only when needed

Continue with Module 5: Security and correctness after the completion check passes.