# Module 11: Capstone Project

## Why this matters

Every module in this track taught one layer of competence in isolation:
choosing a store, ACID guarantees, joins, schema and indexing, concurrency,
ORMs and migrations, layered architecture, query optimization, and
replication/sharding. Real data-layer work never comes one concept at a time —
it comes as "build the persistence for this system, correctly, and be ready to
scale it." This capstone is that. There is no solution code and no step-by-step
script; you design and build a real data layer end to end, make the trade-offs
yourself, and justify them. If you can complete this without peeking back at the
earlier modules, you own this track. If you get stuck, the stuck *is the
signal* — go redo the exercises in whichever module the gap points to, then come
back.

This is also the direct on-ramp to the rest of the backend curriculum: the
schema, transactions, and connection handling you build here are exactly what
track 05 (caching and performance) sits on top of, and the scaling note you
write feeds track 10 (distributed systems).

## The project

Build the complete data layer for an **order and inventory management system** —
the kind of thing behind any store that sells physical goods and must never
oversell stock. You will design the schema, implement it with SQLAlchemy and
Alembic, index it based on real query patterns, make its critical writes
transactionally correct under concurrency, and document where it would scale.

Use Postgres in Docker, Python with SQLAlchemy 2.x, Alembic for migrations, and
`psycopg` as the driver — the same stack you've used all track. Structure the
code in the clean layers from module 06 (a repository layer is the only thing
that touches SQLAlchemy; a service layer holds the business rules).

### The domain

At minimum, model these related entities (at least **three related tables** with
real foreign keys; more is fine and likely):

- **Products** — a sellable item (name, SKU, price), with a current
  **stock level** (on-hand quantity). Decide whether stock lives on the product
  or in a separate inventory table, and justify it.
- **Customers** — who places orders.
- **Orders** — a customer's purchase, with a status lifecycle (e.g.
  `pending → paid → shipped → cancelled`) and a timestamp.
- **Order items** — the line items linking an order to products with quantities
  (the many-to-many resolution from module 02) and the price captured at
  purchase time.

### What you must build

1. **A normalized schema (3NF), with a deliberate exception.** Design to 3NF and
   be able to defend each table. Then identify *one* place you deliberately
   **denormalize** for a real read pattern (e.g. a stored `order_total` rather
   than re-summing line items on every read), and document why the trade is
   worth it and how you keep the denormalized value correct.

2. **A real SQLAlchemy + Alembic implementation.** Models with relationships; an
   Alembic migration history (an autogenerated initial migration you have *read
   and cleaned up*, plus at least one later migration). One of your later
   migrations must be written **as if the table were huge and live** — additive,
   backfilled in batches if it touches data, and reversible with a working
   `downgrade()`.

3. **Indexes justified by real query patterns.** Write down the 4–6 queries your
   system actually runs hot (e.g. "a customer's order history," "is product X in
   stock," "all pending orders older than an hour"). For each index you create,
   name the query it serves and confirm with `EXPLAIN ANALYZE` that the query
   uses it. Include at least one **composite** index whose column order you can
   justify, and name one column you chose *not* to index and why.

4. **Transactionally-correct, correctly-isolated multi-table writes.** The
   "place an order" operation must, as one atomic unit: create the order and its
   items, and **decrement product stock without overselling** under concurrent
   orders. Choose and justify your concurrency control (pessimistic
   `SELECT ... FOR UPDATE`, atomic conditional `UPDATE`, or optimistic
   versioning), and make sure two simultaneous orders for the last unit of stock
   cannot both succeed. Handle the failure path cleanly (roll back, surface a
   domain error).

5. **A short design doc (1–2 pages)** covering:
   - your schema and the reasoning behind the normalization and the one
     denormalization;
   - your indexing decisions tied to the hot queries;
   - how "place an order" achieves atomicity and prevents overselling, and which
     isolation level / locking you rely on and why;
   - **the scaling section (required):** if this system had to handle **100× the
     traffic**, where *exactly* would you introduce a **read replica** (which
     reads move to it, and which reads must stay on the primary for
     read-your-writes), and where — if anywhere — would you **shard** (what shard
     key, what breaks, and why you would or wouldn't shard rather than scale
     vertically / add replicas / cache first). Be specific and honest about the
     trade-offs, not aspirational.

### Acceptance checklist

Tick every box before you call it done:

- [ ] Postgres runs in Docker; the whole thing stands up from a documented
      command sequence (or a `docker-compose.yml`).
- [ ] At least three related tables with real foreign keys and appropriate
      `ON DELETE` behaviour, normalized to 3NF.
- [ ] Exactly one deliberate, documented denormalization, with a stated method
      for keeping it consistent.
- [ ] Schema is created and evolved *entirely through Alembic migrations* (no
      `create_all` in the real path); `alembic upgrade head` then
      `alembic downgrade base` both run cleanly.
- [ ] At least one later migration is written safely (additive, batched backfill
      if data is touched, working `downgrade()`).
- [ ] 4–6 hot queries are written down, each backing index is justified, and
      `EXPLAIN ANALYZE` confirms index usage (before/after plans saved).
- [ ] At least one justified composite index and one justified *non*-index.
- [ ] "Place an order" is a single atomic transaction spanning ≥2 tables.
- [ ] Two concurrent orders for the last unit of stock: exactly one succeeds,
      the other fails with a clean domain error — demonstrated (two sessions or
      two threads), not just asserted.
- [ ] Clean layering: only the repository layer imports SQLAlchemy; the service
      layer holds the rules and raises domain exceptions; nothing leaks upward.
- [ ] The design doc exists and includes the required scaling section with a
      concrete replica plan and an honest shard decision.

### Hints

<details>
<summary>Where should stock live, and how do I stop overselling?</summary>

Stock on the product row is simplest and fine to start; a separate inventory/
stock-movement table is more auditable (you can see *why* stock changed) but
more work — either is defensible if you justify it. The overselling prevention
is the module 04 core: the naive "read stock, check it's ≥ quantity, then update"
in two steps is a lost-update/race that lets two orders both pass the check. Do
it as an **atomic conditional update** — `UPDATE products SET stock = stock -
:qty WHERE id = :id AND stock >= :qty` and treat *0 rows affected* as "out of
stock, abort the transaction" — or lock the product row with `SELECT ... FOR
UPDATE` before checking. Prove it by running two orders for the last unit
concurrently and confirming exactly one wins.

</details>

<details>
<summary>How do I decide which indexes to add?</summary>

Module 03 and 07: write the query first, run it under `EXPLAIN ANALYZE` on a
table with enough rows (generate a few hundred thousand with
`generate_series`), and let the plan tell you. A `Seq Scan` with a selective
`WHERE` is the signal to add an index; then confirm the plan flips to an `Index
Scan`. For "all pending orders older than an hour," think about a *partial* index
(`WHERE status = 'pending'`) and composite column order (equality column first,
range column second). Don't index low-selectivity columns like a 3-value status
on its own — that's your justified *non*-index.

</details>

<details>
<summary>What goes in the scaling section if I've never run at scale?</summary>

You're not predicting the future; you're reasoning from the escalation ladder in
module 08. Identify which of your hot queries are read-heavy and lag-tolerant
(order history, product browse → **read replica**) versus read-your-writes (the
order-confirmation read right after placing → **primary**). For sharding, state
honestly that you'd exhaust single-node tuning, a bigger box, replicas, and
caching (track 05) first, and that you'd shard only if *write* throughput on
orders genuinely outgrew one primary — then pick a shard key that co-locates a
query's data (e.g. `customer_id`, so a customer's orders and items live
together) and name what breaks (cross-customer reports become scatter-gather;
global order-id generation needs UUIDs/Snowflake). An honest "I would *not*
shard this yet, and here's the cheaper thing I'd do instead" is a *stronger*
answer than premature sharding.

</details>

<details>
<summary>How do I keep the layers clean under time pressure?</summary>

Module 06: the fastest check is a grep — `sqlalchemy` should be imported only by
your repository and models modules, never by services or handlers. The service
"place_order" expresses the unit of work and the business rules and raises
domain exceptions (`OutOfStock`, `OrderNotFound`); the repository owns the
session, the transaction boundary, and the `FOR UPDATE`/conditional update.
Convert to a response/DTO shape at the edge — don't hand a raw ORM object to the
outside.

</details>

## Further reading & sources

- [PostgreSQL 16 Documentation](https://www.postgresql.org/docs/16/index.html) - the complete reference for the database you are building against.
- [SQLAlchemy 2.0 Documentation](https://docs.sqlalchemy.org/en/20/) - models, sessions, relationships, and loading strategies for the implementation.
- [Alembic Documentation](https://alembic.sqlalchemy.org/en/latest/) - authoring and running the migration history this capstone requires.
- [Use The Index, Luke!](https://use-the-index-luke.com/) - to justify each index from its query pattern and execution plan.
- [Designing Data-Intensive Applications (Kleppmann)](https://dataintensive.net/) - the reference for the transactional-correctness and scaling reasoning in the design doc.

## Next

[../../05-caching-and-performance/README.md](../../05-caching-and-performance/README.md)
— you now have a correct, fast, well-structured data layer and a plan for
scaling it. Track 05 builds directly on it: applying the right caching strategy
at the right layer (starting with the data-layer query caching you previewed in
module 07), finding and fixing performance bottlenecks systematically, and
reasoning about concurrency versus parallelism. The data layer is where your
data *lives correctly*; caching and performance are how you make it *fast at
scale* — and that's the next track.