The capstone turns the earlier design choices into a small, inspectable order system. Its input is four seed tables, not a production feed. The goal is to build a constrained application schema, import every supplied fact atomically, and expose a trustworthy order-level report. The report makes mistakes visible: a schema that loses empty orders or reprices historical lines cannot satisfy it.
Start by stating each row's meaning. A customer row identifies a customer even when another customer shares its name. A product row holds current catalog information. An order row is one customer's order at a stored placement time. A line row is one numbered position within that order, so its full identity is (order_id,line_no). Two lines may contain the same SKU and still be separate events.
Purchase-time unit_cents belongs to the line. The catalog's current_cents can change later without rewriting a historical total. This is intentional modeling of two lifecycles, rather than unnecessary duplication of one fact. Quantity and both price fields need their specified bounds, while parent existence belongs in required foreign keys.
Deletion actions follow the same meanings. A customer referenced by an order cannot be removed. A product referenced by a historical line cannot be removed. A line cannot outlive its order, so deleting an order cascades into its lines. The fixture tests the cascade in a rolled-back probe; operational retention policy would still determine who is allowed to request such deletion in an application.
The import creates and loads parents before children inside one transaction. If a line names a nonexistent product, rollback must remove the entire newly created application schema as well as the earlier imported rows. Silently inventing a product or ignoring the line would change the input facts. The seed tables remain available and unchanged for verification.
The report grain is exactly one row per order. Start from orders, join its required customer, and left join optional lines. COUNT(l.line_no) counts real children; COUNT(*) would count an empty order's synthetic outer-join row. SUM has no measured value for that row, so COALESCE turns an empty total into the contracted integer zero. Group by order identity and its selected descriptive fields, not by customer name.
CREATE TABLE orders(id INTEGER PRIMARY KEY);
CREATE TABLE lines(order_id INTEGER,line_no INTEGER,qty INTEGER,unit_cents INTEGER);
INSERT INTO orders VALUES(10),(20);
INSERT INTO lines VALUES(10,1,2,100),(10,2,1,80);
SELECT o.id,COUNT(l.line_no) AS line_count,
COALESCE(SUM(l.qty*l.unit_cents),0) AS total_cents
FROM orders o LEFT JOIN lines l ON l.order_id=o.id
GROUP BY o.id ORDER BY o.id;A view stores this query contract; it is not an independently cached table. Its totals follow the current stored line facts each time it is queried. The build checks empty and cancelled orders, equal customer names, repeated SKUs, sparse IDs, zero prices and invalid imports. Sums are intentionally bounded to fit SQLite integers.
Finish by explaining one key, one dependency, one lifecycle action, one index choice and one failure boundary using your own schema. The graded evidence establishes those local SQLite behaviors. A Go or TypeScript service project is a useful next application after learning that language, HTTP and the project's PostgreSQL tooling; this capstone alone does not supply that preparation.