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

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.
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().
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.
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).
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

Where should stock live, and how do I stop overselling?

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.

How do I decide which indexes to add?

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.

What goes in the scaling section if I've never run at scale?

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.

How do I keep the layers clean under time pressure?

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.