Capstone project for SQL & Data Modeling

Design the Partner app's ordering database

Mangrove Market is moving partner shop ordering out of a shared spreadsheet and into the Partner app. About 1,800 independent corner shops, such as Ravi's, order stock wholesale from 4 distribution centres (DCs); each order has several lines, prices are in cents, each shop has a credit limit for unpaid orders, and DC stock must be reserved when an order is confirmed, without ever promising cartons that are not there. Imran, the product manager, needs three reports for the wholesale team, and Ken, the engineering manager, wants the design to stay fast with a year of data and to survive its first schema change after launch. Build the schema, the reports, a safe order-placement transaction, the indexes with proof, and one zero-downtime migration plan.

What you hand in

schema.sql: tables for partner shops (as site rows or their own table), articles, DC stock, orders and order lines, with primary and foreign keys, CHECK and UNIQUE constraints, money in integer cents, times in timestamptz, and an index on every foreign key; it must run on PostgreSQL 16 or newer (PGlite in the browser is fine)
reports.sql: shops with no order in the last 14 days (including shops that never ordered), the top 5 products per DC in the last 30 days, and order value per week with the change against the week before, each with sample output
place_order.sql (or a Prisma function): one transaction that checks the shop's credit limit, reserves stock with conditional updates in a consistent order, and creates the order and its lines, failing cleanly when stock or credit is short
indexes.md: the index for each report and for the order screen, with EXPLAIN (ANALYZE, BUFFERS) before and after on generated data of at least 1 million order lines
migration.md: a plan to add a required delivery_slot column to orders without downtime, as deploys and migration files

Check your work

Tick each item when your project meets it. Be strict: this is the rubric the reviewer uses.

Every money column is integer cents, every moment is timestamptz, and every business code (shop code, SKU, order code) is UNIQUE with a surrogate primary key
Order lines copy the unit price at the time of the order, and the design says why
The silent-shops report keeps shops with zero orders, and you proved it with a planted test row
Stock is reserved by conditional UPDATEs whose row counts decide the outcome, and two orders placed at the same moment cannot oversell or break the credit limit
Each index puts equality columns first and the range or sort column last, and comes with before and after numbers on realistic data
The migration plan adds the column, backfills in batches, enforces NOT NULL with NOT VALID and VALIDATE, starts every file with lock_timeout, and says how to roll back
No payment, SMS or other external call sits inside a database transaction

0 of 7 checked

Model answer

The model answer opens once your capstone passes the review, so you can compare your work with an outline of a strong submission.

Hand in your capstone

Your links are checked automatically, then the work is scored against the rubric above, item by item, with comments. A pass counts toward the course certificate and shows on your profile.

Loading…