Week 10: Capstone: Design, Build & Deploy a Real Postgres-Backed API

1. The Capstone Brief

Build a real, small application — not another copy of the customers/orders schema used throughout this course, something with its own genuine reason to need at least one many-to-many relationship — that includes all of the following:

A normalized schema (Week 5) with at least 4 tables, including one many-to-many relationship via a join table.
Foreign keys, appropriate NOT NULL/UNIQUE/CHECK constraints (Week 6), and at least one deliberately-added index justified by a real EXPLAIN ANALYZE comparison.
A REST API (Week 8) with routes for at least two resources, using parameterized queries throughout.
At least one multi-statement write wrapped in a real transaction (Week 7) — something that genuinely needs atomicity, not a transaction added for its own sake.
At least one view or function (Week 9), used because it was the right tool, not because the brief asked for one.

A few ideas with a natural many-to-many relationship: a recipe app (recipes ↔ ingredients), a small course-enrollment system (students ↔ courses, extending Week 5's example into a real API), a tagging system for any kind of content (posts ↔ tags).

2. Building It

Work in the order this course taught the pieces — it's also the order that avoids the most rework:

Design the schema on paper first (Week 5) — every table, every relationship, before writing a single CREATE TABLE.
Create the schema with real constraints from the start (Week 6), not added as an afterthought.
Seed realistic data, including enough volume to make an index decision meaningful — a handful of rows won't show anything in EXPLAIN ANALYZE.
Build the API layer (Week 8) against the working schema, with parameterized queries throughout.
Add the transaction, view/function, and index last, once the core CRUD works — each should solve a real problem you can point to, not be bolted on to satisfy the brief.

3. Verifying It

Before deploying, confirm the pieces that make this capstone-worthy actually work, not just that the happy path runs once:

Attempt to insert data violating a constraint (a duplicate unique value, a negative amount) and confirm it's rejected.
Attempt an operation your transaction wraps, deliberately failing partway through (a bad value in the second statement), and confirm the first statement's change was rolled back, not left half-applied.
Run EXPLAIN ANALYZE on the query your index was added for, both before and after adding it, and keep both outputs as evidence the index is doing something.
Confirm a value containing SQL syntax passed through your API as user input is treated as a literal, not executed.

4. Deploying to a Real Postgres Host

Finish with a real, working deployment — this is the step that turns "a database on my laptop" into a project someone else can actually run:

Provision a managed PostgreSQL instance on a free-tier host (Supabase, Neon, Railway and Render all offer one) and migrate your schema to it.
Point your API's connection configuration at the hosted database via environment variables, never hardcoded credentials.
Confirm the full API works end to end against the hosted database, not just your local one.
A README documenting the schema (a simple table/relationship diagram is enough), how to run it locally, and how it's deployed — the kind of documentation that makes the project legible to someone who wasn't there for the ten weeks that built it.

That's the full 10-week SQL & PostgreSQL curriculum. Congratulations on going from your first SELECT in Week 1 to a normalized, indexed, transaction-safe database behind a real, deployed API here in Week 10.

5. Hands-on Exercise

Hands-on

Build and ship the capstone

Design, build, verify, and deploy a normalized Postgres-backed API with a genuine many-to-many relationship.

Requirements:

A short written schema design (half a page, a simple diagram is fine) naming every table, its relationships, and specifically why the many-to-many relationship you chose is a genuine fit for the domain, not forced.
A working schema with foreign keys, real constraints, and at least one index added and justified with before/after EXPLAIN ANALYZE output.
A REST API with parameterized queries throughout, at least one real transaction, and at least one view or function used deliberately.
Evidence (described in the README or as comments) that constraints, the transaction's rollback behavior, and injection safety were actually tested, not just assumed to work.
A working deployment on a real managed Postgres host, with credentials passed through environment variables, plus a README documenting the schema and how to run it.

Hint

If you're short on time, prioritize in this order: a correctly normalized schema with real constraints over an elaborate feature set; a genuinely tested transaction and injection-safety check over broader API coverage; and an honest, clear README over a longer feature list — a smaller capstone that's genuinely finished, correct, and deployed is a stronger result than an ambitious one still half-built.

6. Knowledge Check

Four quick questions. Expand each to check your answer.

Why does the capstone specifically require a many-to-many relationship rather than allowing an all one-to-many schema?

One-to-many relationships were the default shape throughout most of this course's examples — requiring a genuine many-to-many relationship forces designing and correctly implementing a join table (Week 5) with its composite key, which is a distinct skill from a simple foreign key and is common enough in real applications to be worth demonstrating deliberately.

Why does the brief insist the transaction and index each solve a "real problem you can point to," rather than just being present to satisfy the requirements?

A transaction or index added without a genuine need behind it doesn't demonstrate understanding of when to use the tool — it just demonstrates knowing the syntax. Requiring each to solve an actual, identifiable problem (a real multi-statement write that needs atomicity, a real slow query proven by EXPLAIN ANALYZE) is what shows the judgment this course has been building toward, not just the mechanics.

Why does the capstone ask you to deliberately fail a transaction partway through and verify the rollback, rather than trusting that wrapping statements in BEGIN/COMMIT is enough?

Code that looks correct and code that's actually verified to behave correctly under failure are different things — the whole point of Week 7's atomicity guarantee is what happens when something goes wrong partway through, and the only way to confirm that guarantee actually holds in your specific implementation is to deliberately trigger the failure and check the outcome, rather than assuming BEGIN/COMMIT alone guarantees it regardless of how the code is structured.

Why does deploying to a real managed host, rather than stopping at a local database, matter for what this capstone demonstrates?

A project that only runs on its author's own machine isn't fully verified to work independent of that one environment, and isn't usable or reviewable by anyone else — deploying for real forces confronting environment-based configuration (credentials via environment variables, not hardcoded), and produces something an employer or collaborator can actually see running, the same reasoning behind every other course on this site ending in a deployed capstone rather than a local-only one.