Project 11 — Persist It in SQLite

“And rejoice not in this, that the spirits are subject unto you; but rather rejoice, because your names are written in heaven.” — Luke 10:20 (ESV)

Chapter: 11 — Persistence I: SQLite and the Relational Model Due: End of Week 11 Submit: A link to your code — a public GitHub repo URL — containing the API source, schema.sql, the database, the README, and (required) agent-log.txt. See Appendix A for the real toolchain + git workflow. Allowed tools: Python 3, the standard-library sqlite3 module, FastAPI (or Node, if that’s your Week 9/10 stack), the sqlite3 shell, a real editor, git, the textbook. AI — Phase 2 (agentic AI is ON): You may delegate implementation to an agent. An agent-log.txt is REQUIRED — every task you delegated, what the agent built, where it was wrong, and where you intervened. The schema is yours. The agent may write route handlers and parameterized SQL once you’ve specified the columns; it may not decide the shape of your data. The shape of the data is the architecture, and the architecture is the part of this project an agent cannot do for you.

The Setup

The ministry from Weeks 9 and 10 has been using your memory-verse API. A volunteer adds verses for the small groups each week; the small-group leaders pull them on Sunday. It works — until the night the host reboots the server for an update, and Monday morning every verse the volunteer entered is gone. All of it. The volunteer had hand-typed forty verses with their references and the ESV text, and your beautiful, fast, fluent API forgot every one the instant the process restarted.

The volunteer is gracious about it. They shouldn’t have to be. A tool that forgets is not done.

Your job this week is to give the API a memory that survives a restart — a real book of remembrance, a file on disk that outlives the process. You’ll design the schema, wire SQLite in behind the existing routes (the API’s promise to the world doesn’t change — only its memory gets an upgrade), and write every query the safe, parameterized way, because the moment this is on the internet, a stranger can type into it. And because you are now an architect, you’ll also state — precisely, with a measurement to back it — exactly where this tool stops being the right one.

Learning Targets

By completing this project, you will demonstrate that you can:

• Design a relational schema: tables, typed columns, a primary key, and sensible constraints, written down in schema.sql before any code.
• Wire SQLite into a real API with Python’s sqlite3 (connections, cursors, commit, lastrowid).
• Write every CRUD operation as a parameterized query and explain why string-built SQL is a security hole.
• Make data survive a server restart — and prove it.
• Model a relationship with a second table and reassemble it with a JOIN.
• Handle a constraint violation as a clean 409 Conflict instead of a 500.
• Name and measure the constraint that marks the edge of SQLite’s fitness — and direct an agent without surrendering that judgment.

Normal Tier

Goal: Replace your Week 10 API’s in-memory store with SQLite. The data must survive a server restart. Every query is parameterized. You design the schema.

Required features

1. A schema you designed, written in schema.sql and loaded on startup. At minimum a verses table with: an INTEGER PRIMARY KEY, a NOT NULL reference, a NOT NULL text, and a translation column defaulting to 'ESV'. You choose the column names and types; you justify them in the README.
2. Full parameterized CRUD, all five operations, every value passed through a ? placeholder — never an f-string, never concatenation:
   • GET /verses — list all, ordered.
   • GET /verses/{id} — one verse, or a clean 404 if absent.
   • POST /verses — insert, return 201 with the database-assigned id (cur.lastrowid).
   • PUT /verses/{id} — update, 404 if the id doesn’t exist.
   • DELETE /verses/{id} — delete, 404 if the id doesn’t exist.
3. Persistence proven. The data survives Ctrl-C and a fresh uvicorn start. Document the exact steps you used to prove it (POST, restart, GET, verse still there).
4. commit() after every write. No “it worked but didn’t save” bugs.
5. Pydantic models preserved (or your Node validation) — the API’s external behavior is identical to Week 10; only the storage changed.
6. No SQL injection holes. Not one query built by string concatenation. The grader will read your source for this specifically.

Example session

$ curl -s -X POST localhost:8000/verses -H 'content-type: application/json' \
    -d '{"reference":"Psalm 46:1","text":"God is our refuge and strength, a very present help in trouble."}'
{"id":1,"reference":"Psalm 46:1","text":"God is our refuge and strength, a very present help in trouble.","translation":"ESV"}

# ... stop the server with Ctrl-C, start it again ...

$ curl -s localhost:8000/verses/1
{"id":1,"reference":"Psalm 46:1","text":"God is our refuge and strength, a very present help in trouble.","translation":"ESV"}
# Still there. The process forgot. The file remembered.

Normal-tier rubric (out of 100)

Medium Tier (+up to 25% extra credit)

M1. A second table and a JOIN

Add a collections table (a verse belongs to a collection — “Romans Road,” “Comfort”). Give verses a collection_id foreign key, and turn foreign keys on (PRAGMA foreign_keys = ON; on every connection). Add an endpoint — e.g. GET /collections/{id}/verses — that returns every verse in a collection by JOINing the two tables, including each verse’s collection name in the response. Store the collection name once, in its own table; do not duplicate it on every verse.

M2. A uniqueness constraint handled as 409

Add a UNIQUE constraint (e.g. UNIQUE(reference, translation) — no two ESV “John 3:16” rows). When a POST violates it, sqlite3 raises IntegrityError. Catch it and return a clean 409 Conflict with a useful message, not an unhandled 500:

$ curl -s -o /dev/null -w "%{http_code}\n" -X POST localhost:8000/verses \
    -H 'content-type: application/json' \
    -d '{"reference":"John 3:16","text":"...","translation":"ESV"}'   # second time
409

A constraint violation is expected input, not a server error. Handle it the way you already handle a 404.

Hard Tier (+up to 25% additional extra credit)

H1. The limit, measured

Write a small standalone script, contention.py, that demonstrates SQLite’s one-writer-at-a-time limit on purpose. Spin up several threads (or processes) that each open a write transaction against the same file, hold the write lock briefly, and commit. Show that, past a small number of concurrent writers and a short busy-timeout, some of them fail with sqlite3.OperationalError: database is locked. Capture the actual numbers: how many writers, how many failed, at what timeout. Real measurement, not a guess.

H2. The architect’s memo (the deliverable that an agent cannot write for you)

Write MEMO.docx: the honest limits of SQLite for this application. It must:

1. State, in one sentence, the exact constraint under which SQLite stops being the right tool for the memory-verse API. (Hint: it is about concurrent writers, and you should be able to say how many, given your H1 numbers.)
2. Cite your own H1 measurements — “with N concurrent writers at a T-second timeout, K of them failed with database is locked” — not generic claims you read somewhere.
3. Name what you would migrate to (Postgres) and the one specific property of it that removes this exact bottleneck (MVCC: writers don’t block each other the way SQLite’s file lock does).
4. State, just as honestly, the case for not migrating — the conditions under which this app should stay on SQLite forever, and why reaching for Postgres without a named reason would be over-engineering.

This memo is the heart of the project and the part an agent genuinely cannot produce for you, because it requires your numbers and your judgment about this app’s real load. An agent can write a generic “SQLite vs Postgres” essay in seconds; it cannot tell you whether your ministry’s forty-verses-a-week traffic needs Postgres. (It doesn’t — and saying so, with evidence, is the architect’s answer.)

Submission

Submit one URL — a public GitHub repo. It must contain:

1. The API source — your Week 10 app with SQLite wired in.
2. schema.sql — your schema, the design you’ll defend.
3. verses.db — a database file with a few rows in it, so the grader can see your schema populated. (In real projects you’d .gitignore the database; here we commit it so the grader can inspect it.)
4. contention.py and MEMO.docx — for Hard tier.
5. README.txt — your reflection (template below).
6. agent-log.txt — REQUIRED. Every task you delegated, what the agent did, where it went wrong, where you intervened.

README template

# Project 11 — Persist It in SQLite

**Tier targeted:** Normal / Medium / Hard
**Stack:** FastAPI + sqlite3  (or Node + better-sqlite3, etc.)
**Schema design — and why:**
  - Tables: (list)
  - Primary key: (which column, why)
  - Constraints: (NOT NULL / UNIQUE / FOREIGN KEY — and why each)
  - One thing I considered and rejected: (e.g. "a single denormalized table")
**Persistence proven by:** (the exact steps — POST, restart, GET, still there)
**Parameterization:** Every query uses ? placeholders. Confirmed by: (how you checked)
**409 handling (M2):** yes / no — where in the code
**SQLite's limit for this app (H2):** (one-sentence summary)
**What I learned:** (one paragraph)
**What I'd change:** (one sentence)
**AI usage:** Agentic AI ON. See agent-log.txt. Signed: <your name>

agent-log.txt (required for every Phase 2 project)

# Agent Log — Project 11

## Task 1: <what you asked the agent to do>
- **Delegated:** (the prompt / the goal)
- **Agent did:** (what it built)
- **Where it was wrong:** (hallucinated API? f-string SQL? invented a schema?)
- **My intervention:** (what you fixed or decided yourself)

## Task 2: ...

## Decisions I made myself (the agent did NOT make these):
- The schema (tables, keys, constraints) — and why.
- (Hard tier) The migration constraint and the memo's conclusion.

Hints (Read Before You Begin)

• Write schema.sql first, by hand, before you ask an agent for a single line. The schema is the spec. Decide the columns, the types, the key, and the constraints yourself. If you let an agent invent the schema, you have handed it the one decision this project exists to teach.
• Watch the agent for the f-string. Agents frequently generate f"... WHERE id = {id}" because that pattern is everywhere in their training data. When you delegate a query, read what came back and confirm it uses ?. This is the Coding 1 reading muscle doing security work. Log every time you caught it.
• Prove persistence the brutal way. POST a verse. Kill the server with Ctrl-C. Start it fresh. GET the verse. If it’s gone, you forgot commit() or you’re still writing to RAM. Don’t claim persistence you haven’t watched survive a restart.
• A single parameter needs a trailing comma. con.execute("... WHERE id = ?", (id,)) — the (id,) is a one-element tuple. (id) is just id, and it’ll error.
• Turn foreign keys on (Medium). PRAGMA foreign_keys = ON; on every connection, or your REFERENCES constraint is decoration.
• For the Hard-tier demo, hold the lock long enough to collide. A time.sleep(0.3) inside a BEGIN IMMEDIATE transaction, a short connection timeout, and several threads firing at once will reliably produce database is locked. Tune the numbers until you see it, then report the numbers you used.

What Mastery Looks Like (Beyond the Rubric)

A great Project 11 has a schema.sql you could hand to another engineer and they’d understand the data model in thirty seconds — typed columns, an obvious primary key, constraints that say what’s true about the data (“a reference is never null,” “no two identical ESV references”). The schema reads like a specification because it is one, and the database enforces it on every write.

A great Project 11 has not one query you’d be embarrassed to show a security reviewer. Every value goes through a placeholder. You could publish the source and the worst an attacker could do is fail.

A great Project 11’s agent-log.txt shows a real partnership: the agent moved fast on the route handlers and the boilerplate, and you caught the place where it reached for an f-string, and you made the schema decision it could not make. That is the Phase 2 skill — direction, not abdication. The grader reads that log as carefully as the code.

And a great Hard tier ends with a memo that is honest in both directions: it names the exact constraint that would force a migration, backs it with measured numbers, and then has the discipline to say “but this app will never hit it, so we stay on SQLite.” Knowing when not to upgrade is as much the architect’s job as knowing when to. The over-engineer reaches for Postgres reflexively; the architect reaches for it with a reason, or not at all.

Coach’s Note — The schema is the line in this project the agent must not cross, and I mean it. I have watched strong students hand an agent “build me a memory-verse database” and get back a perfectly plausible schema they did not design and could not defend — extra tables they didn’t need, a missing constraint they didn’t notice, a key choice they never made. The data model is the architecture. You can delegate the typing. You cannot delegate the deciding. If your agent-log shows the agent designed your schema, you missed the entire point of the week, however well the code runs.

When You’re Done

1. POST a verse, restart the server, GET it back. Watch it survive. That is the project.
2. Read every SQL string in your code. Confirm every value is a ?. If you find an f-string, you found a vulnerability — fix it and log it.
3. (Medium) Insert a duplicate. Confirm you get a 409, not a 500.
4. (Hard) Run contention.py. Watch it say database is locked. Put the real numbers in MEMO.docx.
5. Fill in agent-log.txt honestly. Where did the agent help? Where was it wrong? What did you decide?
6. Submit, then read Chapter 12 — Postgres, the database built for the one thing SQLite can’t do.

A theological footnote. In Luke 10, the seventy-two return thrilled that even the demons submit to them, and Jesus redirects their joy: rejoice that your names are written in heaven. The record that ultimately matters is not one you keep — it is one kept for you, faithfully, beyond the reach of any reboot or crash or corrupted file. Your work this week is a small, honest echo of that: you built a record that outlives the process that wrote it, so that what a volunteer entrusted to your program on Tuesday is still there on Wednesday. That is stewardship — keeping faithfully what someone handed you to keep. The God who writes names in heaven loses none of them. Keep your little book of remembrance with the same care, in proportion, and you are practicing a real virtue, not just a technique.

See you next week.