Model orders and money in Postgres
Continues from the last build: The API runs locally and you can trace a request from curl to Postgres and back, but the only table in the database is products. There is nowhere to put a customer, an order, or a payment.
Last rung you got the API answering curl requests and reading products out of Postgres.
What you'll build
You will hand off a Postgres schema with five interlocking tables, a domain module that computes order totals in integer cents with unit tests, a Drizzle migration you generated and read line by line, and a seed script that populates realistic data you can inspect with psql. The next rung builds the REST contract on top of this schema, so every column and constraint you add here becomes an API decision you don't have to relitigate later.
See how we teach, before you sign up
You don't just get code dumped on you. Every starter file and every solution is explained line-by-line, in plain English. Here's one real file from this project:
import { pgTable, uuid, text, integer, timestamp, pgEnum } from 'drizzle-orm/pg-core';
export const productsTable = pgTable('products', {
id: uuid('id').defaultRandom().primaryKey(),
merchantId: uuid('merchant_id').notNull(),
name: text('name').notNull(),
unitPriceCents: integer('unit_price_cents').notNull(),
currency: text('currency').notNull().default('USD'),
createdAt: timestamp('created_at').defaultNow().notNull(),
});
// TODO: add merchantsTable (id, name, createdAt)
// TODO: add customersTable (id, merchantId FK restrict, email, name)
// TODO: add orderStatus pgEnum('order_status', ['pending','paid','fulfilled','refunded','cancelled'])
// TODO: add ordersTable (id, merchantId FK restrict, customerId FK restrict, orderNumber unique, status, totalCents, currency, createdAt)
// TODO: add orderItemsTable (id, orderId FK cascade, productId FK restrict, unitPriceCents, quantity)
// TODO: add paymentsTable (id, orderId FK restrict, amountCents, currency, createdAt)
Reading this file
export const productsTable = pgTable('products', {This table already exists from rung 1 and is the pattern to follow for the new tables.merchantId: uuid('merchant_id').notNull(),Every merchant-scoped table needs this same NOT NULL foreign key column pattern.// TODO: add orderStatus pgEnum('order_status', ['pending','paid','fulfilled','refunded','cancelled'])Declare the enum once and reuse it as the status column's type on ordersTable.// TODO: add orderItemsTable (id, orderId FK cascade, productId FK restrict, unitPriceCents, quantity)Note the asymmetric ON DELETE choice: cascade from orders, restrict from products.
Starter has only the products table Drizzle-defined from rung 1. You add the four remaining tables following the paper design from milestone 1.
That's 1 of 4 explained code blocks in this single project.
The build, milestone by milestone
- 1
Design the schema on paper before writing SQL
5 guided stepsSchema mistakes are the most expensive kind to fix because they show up as data corruption weeks later, not a compile error today. A junior who jumps straight to CREATE TABLE almost always discovers mid-migration that orders needs a merchant_id they forgot, or that deleting a customer should not be allowed to silently delete their order history. Fifteen minutes with a pen saves a rewritten migration.
- 2
Represent money as integer cents, never floats
5 guided stepsFloating point cannot represent 0.10 exactly in binary, so summing floats accumulates tiny errors that eventually round a customer's total by a cent, and in payment systems a cent of drift across millions of orders is a real audit finding, not a rounding curiosity. Storing 499 cents plus a currency column sidesteps the entire problem and is what every real payments system, including Stripe, does.
- 3
Write the Drizzle schema and generate the migration
6 guided stepsdrizzle-kit generate turns your TypeScript schema into a real SQL migration file you can read, diff, and check into git. Reading that file before applying it is the habit that catches a wrong ON DELETE or a missing NOT NULL before it becomes a production migration, since undoing a bad migration after real orders exist is far more painful than fixing a typo in a file that has not run yet.
- 4
Decide what belongs in a CHECK constraint versus application code
5 guided stepsApplication code has bugs, gets bypassed by scripts, and gets rewritten in a new language someday. A CHECK constraint at the database level is the last line of defense that survives all of that. But putting every rule in the database makes error messages unreadable to API clients, so the right split is: constraints stop catastrophic data corruption (negative money, an order with no merchant), application code gives clients a friendly 422 before the query even reaches Postgres.
- 5
Seed realistic data and verify it with psql
5 guided stepsA schema that only ever holds the one row you typed by hand while testing hides bugs that only show up at realistic volume, like an index that helps with 5 rows but is useless at 200, or a foreign key you got backwards that only breaks when a second merchant's data exists. Seeding forces you to write real inserts across the whole schema, which is the fastest way to discover a design mistake before the API even exists.
What's inside when you start
You'll walk away with
This is portfolio-grade. Build it free.
Sign up to unlock every milestone step-by-step, the code skeletons, full reference solutions, and checkable tasks, with your progress saved as you build.
Start building