Project 12 — Migrate to Postgres, Justify It

“Behold, I am the one who has laid as a foundation in Zion, a stone, a tested stone, a precious cornerstone, of a sure foundation: ‘Whoever believes will not be in haste.’” — Isaiah 28:16

Chapter: 12 — Persistence II — PostgreSQL and Scaling Up Due: End of Week 12 Submit: A link to your code — a public GitHub repo URL with your migrated API, the migration scripts, the measurement output, the decision memo, and agent-log.txt. See Appendix A for the local toolchain + git workflow. Allowed tools: Python 3, PostgreSQL (local, Appendix B), psycopg + psycopg_pool, your editor, git, the textbook, and — Phase 2 — agentic AI. Phase 2 (wk 9–16): agentic AI is ON, and an agent-log.txt is REQUIRED. Log every task you delegated to an agent, what it built, where it went wrong, and where you intervened. The agent can write the migration and the pool. The agent cannot decide whether to migrate, or defend the choice — that judgment is yours, and it is graded.

The Setup

The ministry from Project 11 came back. Their memory-verse app — the one you gave a memory with SQLite — got popular. Three things happened at once.

First, they hired two more staff to curate verses, and now three people edit the catalog at the same time from three different offices. Second, they moved the API off one laptop and onto a small server that, under their growth plan, will soon run more than one copy behind a load balancer. Third, last Tuesday, a curator saw database is locked for the first time, lost an edit, and called you in a mild panic.

You recognize this. It is the exact wall you measured at the end of Project 11 — the single-writer limit of the single-file model, hit by real concurrent writers. The constraint that forces a client/server database has arrived. So this week you migrate the catalog to PostgreSQL.

But the ministry’s director, who has been burned by over-engineering before, asks you a fair question: “How do we know this isn’t us reaching for the fancy tool because it’s fancy? Write it down. Convince me.” That memo is the assignment as much as the migration is. You are not just moving the data. You are justifying the move — and demonstrating that you would have told them to stay on SQLite if the constraints hadn’t actually forced the change.

Learning Targets

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

• Migrate a relational schema and its data from SQLite to PostgreSQL faithfully.
• Write a reproducible, versioned migration script (not ad-hoc CREATE TABLE).
• Stand up and use a connection pool, and explain why per-request connections are a bug.
• Run CRUD against Postgres with correctly parameterized SQL.
• Use EXPLAIN ANALYZE to measure a query, add an index, and quantify the read/space/write tradeoff (the Week 5–6 lesson, live).
• Make a multi-step operation atomic with a transaction and prove a clean mid-failure rollback.
• Decide SQLite-vs-Postgres from constraints — and defend the decision in writing.
• Direct an agent on the mechanical parts while keeping the architectural judgment human (and logging the seam).

Normal Tier

Goal: Migrate your Project 11 memory-verse database to PostgreSQL with a reproducible migration script and a connection pool, and re-run your CRUD against the new foundation.

Required features

1. A reproducible migration script. A migrations/ directory with at least 0001_initial_schema.sql that creates the schema in Postgres (SERIAL primary keys, TEXT, NOT NULL, DEFAULT, REFERENCES, the UNIQUE (reference, translation) constraint). Each migration is wrapped in a transaction and records its version in a schema_migrations table. A reader must be able to build the exact schema from an empty database by running the migrations in order. No ad-hoc, hand-typed CREATE TABLE on a live database — the schema lives in versioned files.
2. A data migration. A script (migrate_data.py or similar) that reads the rows from your P11 SQLite database and inserts them into Postgres, parameterized. Your verses must arrive intact. If your P11 database also has a collections table (that was P11’s Medium tier — not everyone has it), migrate those too, with the collection_id relationships preserved. You do not need to have done P11’s Medium tier to do this project. If you only did P11’s Normal tier — a verses table and nothing else — that is enough: the migration moves your verses, and the (empty) Postgres collections table the schema creates is fine to leave empty. The starter migrate_data_starter.py already detects whether a collections table is present and migrates it only if it is. No P11 database at all? Run python seed_p11.py (in this chapter’s code/) to build a P11-Normal-shaped verses.db with a few real ESV verses, and migrate that.
3. A connection pool. A single psycopg_pool.ConnectionPool created once at startup, with a sensible min_size/max_size. All database access borrows from the pool. No psycopg.connect() per operation.
4. CRUD re-run against Postgres. Your create/read/update/delete operations (from P11’s API, or a standalone module) now run against Postgres, all parameterized with %s. Reading a verse, listing verses, creating one, updating one, deleting one — all work, and the data survives a restart of your program and of the Postgres server.
5. Credentials from the environment. The password (and ideally the whole connection string) comes from os.environ, never hard-coded. Your repo must not contain a real password.
6. A short README documenting how to run the migration, connect, and exercise the CRUD against a fresh local Postgres.

Example output

$ python migrate_data.py
Connected to PostgreSQL 16.2 on localhost:5432/verses
Applied migration 0001_initial_schema
Migrated 0 collections, 42 verses from verses.db (SQLite) -> Postgres.
Verifying... 42/42 verses present. Done.

$ python crud_demo.py
get_verse(12) -> ('John 3:16', 'For God so loved the world...')
add_verse('Romans 8:28', '...') -> id 43
update_verse_text(43, '...') -> 1 row updated
delete_verse(43) -> 1 row deleted

(If you did P11’s Medium tier, the migrate line will also report your collections — e.g. Migrated 3 collections, 42 verses .... If you only did P11 Normal, 0 collections is exactly right and full credit.)

Normal-tier rubric (out of 100)

Medium Tier (+up to 25% extra credit)

M1. Index a hot column and measure it

Pick a column you actually query (e.g., reference). Load the table with enough rows to matter — use generate_series to get at least 200,000 rows (a few hundred verses won’t show anything; that’s the point). Then:

1. Run EXPLAIN ANALYZE on a lookup by that column. Record the plan and execution time (you should see Seq Scan).
2. Create the index. Run EXPLAIN ANALYZE again. Record the new plan and time (you should see Index Scan).
3. Put both plans in your README or a measurements.xlsx, with the before/after execution times.

M2. Discuss the index’s space and write cost

In measurements.xlsx, write a short paragraph that ties this directly back to Projects 5 and 6:

• Report the space the index occupies (SELECT pg_size_pretty(pg_relation_size('idx_verses_reference'));) versus the table size.
• State the write cost: every insert/update on that column now also maintains the B-tree.
• Name this explicitly as the same space/time tradeoff you measured by hand when you chose a tree over a hash in Project 6. The index is one of those structures, built and maintained by the database. Spell out the connection.

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

H1. An atomic transaction with a demonstrated rollback

Implement a genuinely multi-step operation that would corrupt the data if it half-completed — e.g., move a verse from one collection to another (remove from A, add to B), or merge two collections (reassign all verses, then delete the empty one). Make it one transaction.

Then demonstrate the rollback: write a test (or a clearly-commented script) that injects a failure between the steps, runs the operation inside a try/except, and then queries the database to prove that none of the steps took effect — the data is exactly as it was before. Include the output. A passing rollback demonstration is the deliverable; “it should roll back” without proof earns nothing.

H2. The SQLite-vs-Postgres decision memo (the judgment an agent can’t make)

Write DECISION.docx: a one-to-two-page memo with three concrete scenarios, and for each, a SQLite-or-Postgres recommendation driven by the specific constraint that decides it. At least one scenario must be one where the right answer is SQLite, and you must say plainly why reaching for Postgres there would be the mistake — premature complexity paid for a scale that isn’t coming.

Use the ministry’s actual situation (from The Setup) as one of the three, and justify this migration on its real constraints — concurrent writers, multiple processes — not on “Postgres is more serious.” If you cannot point at a concrete constraint that forces the move, you have not justified it.

This memo is the heart of the project. An agent can write every line of migration SQL for you. It cannot make this call. The grader reads this memo most closely of anything you submit.

Submission

Submit one URL via the course portal: a public GitHub repo.

What the repo must contain

1. migrations/ — at least 0001_initial_schema.sql, transactional and versioned.
2. migrate_data.py (or equivalent) — the SQLite→Postgres data migration.
3. The CRUD code / migrated API — running against Postgres, through the pool, fully parameterized.
4. measurements.xlsx (Medium) — the before/after EXPLAIN ANALYZE plans, times, and the index space/write-cost discussion tied to P5/P6.
5. The transaction + rollback demo (Hard) — code and proof output.
6. DECISION.docx (Hard) — the three-scenario SQLite-vs-Postgres memo.
7. README.txt:

# Project 12 — Migrate to Postgres, Justify It

**Tier targeted:**  Normal / Medium / Hard
**Why Postgres (the forcing constraint):**  (one sentence — the real reason)
**How to run:**  (migration, then CRUD, against a fresh local Postgres)
**Connection pool:**  min/max size and where it's created
**Index measured (Medium):**  column, before/after times, index size
**Transaction (Hard):**  the operation made atomic; how the rollback is demonstrated
**What I learned:**  (one paragraph)
**What I'd change:**  (one sentence)

8. agent-log.txt (REQUIRED — Phase 2):

# Agent Log — Project 12

## Tasks delegated to the agent
- (task) — what I asked, what the agent produced

## Where the agent was wrong or incomplete
- (e.g., used `?` placeholders instead of `%s`; opened a connection per call;
   hard-coded a password; suggested Postgres without asking about constraints)

## Where I intervened / what I decided myself
- The migrate-or-don't decision and its justification (DECISION.docx): MINE.
- The index choice and the reading of the measurements: MINE.
- (anything else you judged rather than delegated)

## Signed: <your name>

9. No real credentials in the repo. The grader will run against their own local Postgres with their own password in an env var.

Hints (Read Before You Begin)

• Get the setup done first (Appendix B). Half of the pain in this project is environment, not code. Install Postgres, create the verses database and a user, set PG_PASSWORD, and confirm psql connects before you write a line of Python. A working SELECT version(); from Python is your green light.

• Migrate the schema before the data. Run the migration to create empty tables, confirm with \d verses in psql, then run the data migration. Trying to do both at once just makes failures harder to read.

• Don’t have a P11 verses.db? Make one — this project stands alone. If you skipped P11’s Medium tier, or just want a clean source, run python seed_p11.py to build a P11-Normal-shaped SQLite database (a verses table, a few real ESV verses, no collections). The migration handles the no-collections case on purpose: a verses-only migration is full credit on Normal. Collections were P11’s Medium tier; you are not required to have them.

• SERIAL vs INTEGER PRIMARY KEY. SQLite auto-assigned ids via INTEGER PRIMARY KEY. In Postgres that’s SERIAL. When migrating data that already has ids, either insert the ids explicitly (and then fix the sequence with setval) or let Postgres reassign them and remap the foreign keys. Decide which, and say so in your README.

• Make the table big with generate_series, not by hand. The index measurement is meaningless on small data. INSERT INTO verses (...) SELECT ... FROM generate_series(1, 300000). An index’s win only appears at scale — the same lesson as Project 6.

• Prove the rollback by querying after. Don’t just assert “it rolled back.” Inject the failure, catch it, then SELECT and show the rows are untouched. The proof is in the query output, not the claim.

• Let the agent draft, then audit every line. Agents love to write ? placeholders (SQLite habit), open a connection per call, and propose Postgres without asking about your constraints. Read what it gives you against this chapter’s Common Bugs before you trust it — and log what you caught.

What Mastery Looks Like (Beyond the Rubric)

A great Project 12 reads like a careful migration done by someone who did not want to do it unless it was necessary — and then did it cleanly because it was. The migration scripts are reproducible: a stranger clones the repo, runs them against an empty database, and gets your exact schema. The pool is created once and never thought about again. Every query is parameterized without a single exception. The data arrived intact and survives a server bounce.

A great Project 12’s measurements.xlsx does not just paste two EXPLAIN plans — it reads them, the way Coding 2 taught you to read code. It says: here is O(n) caught in the act, here is O(log n) after the index, here is what the index cost me in disk and in write-time, and here is why that trade was worth it for this column and would not have been for that one. It connects the dots back to the tree you built with your own hands in Week 6.

And a great Project 12’s DECISION.docx could be handed to a non-engineer director and convince them — because it argues from constraints, not from fashion. It names the specific thing about this problem that forced the move, and it is honest enough to include a scenario where the answer was SQLite all along. That honesty is the mark of an architect rather than a tool-collector. The engineer who can say “you don’t need Postgres yet, and here’s the signal that will tell us when you do” is more valuable than the one who migrates everything to the heaviest tool available.

Coach’s Note — The agent will offer to “upgrade you to Postgres” the moment you mention scale, the same way it offers the most elaborate solution to everything — it has no skin in the cost. You have skin in the cost: you’re the one who has to run the server, secure it, back it up, and pay the latency on every query. The whole point of this project is to make a decision the agent structurally cannot make well, because it cannot feel the cost. Make it. Defend it. Log where you made it. That seam — between what you delegated and what you decided — is exactly what the grader is looking for.

When You’re Done

1. Clone your own repo into a fresh directory and run the migrations against an empty Postgres. Did you get the exact schema? If not, your migrations aren’t reproducible — fix them.
2. Run the data migration. Confirm the counts match.
3. Exercise the CRUD. Restart the Postgres server. Confirm the data is still there.
4. (Medium) Re-run the EXPLAIN ANALYZE before/after and confirm your recorded numbers.
5. (Hard) Run the rollback demo and confirm the database is untouched after the injected failure.
6. Read your DECISION.docx out loud. Would it convince the director? Would it convince you if you were the one paying to run the server?
7. Submit.
8. Read Chapter 13. Next week the question changes shape entirely: what if the data isn’t a table at all?

A theological footnote. The cornerstone in Isaiah is a tested stone — proven able to bear the weight before the building rises on it. “Whoever believes will not be in haste”: the one who trusts a sure foundation does not panic, does not over-build out of fear, does not under-build out of haste. That is the posture this project asks of you toward your own foundations. Test the stone. Build to the real weight — not the imagined weight, not the fashionable weight, the real one. The Christian engineer, of all people, has reason not to build in haste: the foundation that bears every other weight was laid once, and it holds.

See you next week.