JS-290 · Lesson 5 of 12 · 27 min
Building the data layer
▶ Video coming soon A video version of this lesson is on the way.
Sprint 1 begins on Tuesday, April 3, and the first code the team writes is not a screen but a schema. Tavish takes the lead, with Theo reviewing every migration as the CODEOWNERS file requires. His reasoning is the one Theo taught Mira in WEB-201: the database outlives every screen and every service that uses it, so mistakes in it are the most expensive to fix. A badly named React component can be renamed in a minute. A column that mixes kilograms and pounds will confuse reports for years.
This lesson follows the data layer from the first sketch to working code: the entities and their relationships, the tables and constraints that enforce the rules, the migrations that create them, the units the data is stored in, the status of a run as a state machine, and the repository modules that hide SQL from the rest of the service. The same design method from WEB-201 applies: find the things the business talks about, find how they relate, and let the database refuse data that breaks the rules.
The entities
Tavish starts with the nouns in the stories and Lucy's spreadsheet. A donor is a business, such as Copperleaf Bakery. A donor can have more than one location; Pine Hill Grocers has two stores. An offer is food a donor says is available at a location, with a pickup window, a category, an estimated weight and whether it is chilled. When a driver claims an offer it becomes a run, which records who claimed it and moves through pickup and receiving. A receipt records what actually arrived: the weight, and for chilled food the temperature. Start areas are the parts of the city drivers choose from for distance sorting. Drivers themselves are not stored as full records; the service stores only the volunteer's id from the PHP application, a display first name and whether they carry a cooler.
The decision to keep offers and runs separate came from an argument. Odalys wanted one table, because every run starts as an offer. Tavish pointed out that an offer can exist without ever becoming a run, if nobody claims it or the donor withdraws it, and that Lucy's report of missed pickups needs to count exactly those. Separate tables make the question *which offers were never claimed?* a simple query, and they keep the run's columns, which are mostly about the driver, out of the donor's part of the data.
Units: store one, show the right one
Weight and temperature need care because the Canadian and US versions of the screens show different units. The rule from JS-210, where all money is stored in cents, applies again: store a single internal unit as an integer, and convert only when showing a value to a person or reading one from them. Weights are stored in grams as integers. Temperatures are stored in tenths of a degree Celsius as integers, so 38.3 °F is stored as 35. Integers avoid floating-point surprises when reports add up thousands of receipts, and one unit means a report can never mix pounds and kilograms.
// Store grams and tenths of a degree Celsius; convert only for display.
const GRAMS_PER_LB = 453.59237;
function showWeight(grams, units) {
return units === 'metric' ? `${(grams / 1000).toFixed(1)} kg` : `${(grams / GRAMS_PER_LB).toFixed(1)} lb`;
}
function showTemp(tenthsC, units) {
const c = tenthsC / 10;
return units === 'metric' ? `${c.toFixed(1)} C` : `${((c * 9) / 5 + 32).toFixed(1)} F`;
}
function readWeight(value, units) {
return Math.round(units === 'metric' ? value * 1000 : value * GRAMS_PER_LB);
}
const receipt = { grams: 18400, tenthsC: 35 };
for (const units of ['metric', 'us']) {
console.log(units.padEnd(6), showWeight(receipt.grams, units), showTemp(receipt.tenthsC, units));
}
console.log('read 40.5 lb ->', readWeight(40.5, 'us'), 'g');
console.log('read 18.4 kg ->', readWeight(18.4, 'metric'), 'g');metric 18.4 kg 3.5 C us 40.6 lb 38.3 F read 40.5 lb -> 18370 g read 18.4 kg -> 18400 g
What this code does
The stored receipt holds 18,400 grams and 35 tenths of a degree, and the two display functions convert to kilograms or pounds and to Celsius or Fahrenheit. Reading a value typed by a person converts back to grams and rounds to a whole number, so the database only ever sees integers. The pound uses its exact legal definition in grams, so conversions in both directions agree.
The cold-food rule is checked against the stored value, not the displayed one: a chilled receipt is refused if its temperature is above 40 tenths of a degree Celsius. The two countries' food safety guidance round the same idea differently: 4 °C on one side of the border and 40 °F, which is 4.4 °C, on the other. Rosa and Lucy agree the service uses 4.0 °C everywhere, which shows as 39.2 °F on the US screens, so that one rule in one unit replaces two rules that disagree by a fraction of a degree.
Tables and constraints
With the model agreed, Tavish writes the first migration. Constraints do the work that would otherwise depend on every piece of code remembering a rule. A check constraint refuses a pickup window that ends before it starts. A foreign key refuses an offer for a location that does not exist. A unique constraint allows at most one run per offer, which is the database's last line of defense against two drivers claiming the same food. And an exclusion constraint, a PostgreSQL feature, refuses two runs for the same driver whose windows overlap, which enforces Lucy's overlap rule even if a bug in the service forgets it.
-- migrations/0001_create_rescue_tables.sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE donors (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE donor_locations (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
donor_id bigint NOT NULL REFERENCES donors(id),
address text NOT NULL,
lat numeric(8,5) NOT NULL,
lon numeric(8,5) NOT NULL,
instructions text NOT NULL DEFAULT ''
);
CREATE TABLE offers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
location_id bigint NOT NULL REFERENCES donor_locations(id),
category text NOT NULL CHECK (category IN ('bakery', 'produce', 'prepared', 'dairy', 'dry')),
est_grams integer NOT NULL CHECK (est_grams BETWEEN 500 AND 500000),
chilled boolean NOT NULL,
window_start timestamptz NOT NULL,
window_end timestamptz NOT NULL,
status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'claimed', 'withdrawn', 'expired')),
CHECK (window_end > window_start)
);
CREATE TABLE drivers (
volunteer_id bigint PRIMARY KEY, -- id in the PHP application
first_name text NOT NULL,
has_cooler boolean NOT NULL DEFAULT false
);
CREATE TABLE runs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
offer_id bigint NOT NULL UNIQUE REFERENCES offers(id),
driver_id bigint NOT NULL REFERENCES drivers(volunteer_id),
pickup tstzrange NOT NULL,
status text NOT NULL DEFAULT 'claimed'
CHECK (status IN ('claimed', 'picked_up', 'received', 'missed', 'released')),
EXCLUDE USING gist (driver_id WITH =, pickup WITH &&) WHERE (status IN ('claimed', 'picked_up'))
);
CREATE TABLE receipts (
run_id bigint PRIMARY KEY REFERENCES runs(id),
grams integer NOT NULL CHECK (grams > 0),
temp_tenths_c integer, -- required for chilled offers, checked in the service
received_at timestamptz NOT NULL DEFAULT now(),
received_by bigint NOT NULL
);What this code does
The migration creates the six tables with identity keys, foreign keys and check constraints that refuse impossible values, such as a window that ends before it starts. The unique constraint on offer_id allows one run per offer, and the exclusion constraint uses a range of pickup times to refuse overlapping active runs for the same driver. The btree_gist extension is what lets one exclusion constraint combine an equality test on the driver with an overlap test on the range.
Theo's review of the migration asks for two changes. The exclusion constraint applies only to active runs, because a released or missed run should not block a driver from claiming another run at the same time; that is the WHERE clause at the end. And the default range type in PostgreSQL includes its start and excludes its end, written [start, end), which means two runs that only touch, one ending at 17:00 and the next starting at 17:00, do not overlap. That is exactly Lucy's rule that touching windows are fine, and the team adds a comment saying so.
Migrations, forward only
Every change to the schema is a numbered migration file that runs once and is recorded in a table, so that every copy of the database, on a laptop, in CI, on staging and in production, can be brought to the same state by running the files in order. The team follows the studio's rules from JS-260: migrations only move forward; a mistake is fixed by a new migration, not by editing an old one; and a change that would break running code uses expand and contract, adding the new form first, moving the code over, and removing the old form in a later release.
In Sprint 2 this rule is tested. Lucy asks for an *other* category for things like canned goods from a café's storeroom. Changing a check constraint in PostgreSQL means dropping and recreating it, so Tavish writes migration 0004, which does both in one transaction, and the service code that accepts the new value ships in the same release. Because the change only widens what is allowed, nothing already running can break, so no expand-and-contract is needed.
A run is a state machine
The status column on runs looks like a simple label, but it hides rules: a run can be picked up only after it is claimed, received only after it is picked up, and nothing can happen to a run once it is received. Writing these rules as a state machine, a table of which transitions are allowed, makes them testable and makes impossible states impossible. The same table drives the buttons on the driver's screen, so the interface can never offer a transition the service will refuse.
// Allowed status changes for a run, and who may make each one.
const transitions = {
claimed: { picked_up: 'driver', released: 'driver', missed: 'coordinator' },
picked_up: { received: 'receiver', missed: 'coordinator' },
received: {},
missed: {},
released: {},
};
function move(run, to, role) {
const allowed = transitions[run.status]?.[to];
if (!allowed) return { ok: false, why: `cannot go from ${run.status} to ${to}` };
if (allowed !== role) return { ok: false, why: `${to} needs the ${allowed} role` };
return { ok: true, run: { ...run, status: to } };
}
let run = { id: 41, status: 'claimed' };
for (const [to, role] of [['received', 'receiver'], ['picked_up', 'receiver'], ['picked_up', 'driver'], ['received', 'receiver'], ['missed', 'coordinator']]) {
const r = move(run, to, role);
console.log(`${to.padEnd(9)} by ${role.padEnd(11)} -> ${r.ok ? 'now ' + r.run.status : r.why}`);
if (r.ok) run = r.run;
}received by receiver -> cannot go from claimed to received picked_up by receiver -> picked_up needs the driver role picked_up by driver -> now picked_up received by receiver -> now received missed by coordinator -> cannot go from received to missed
What this code does
The transitions object lists, for each status, the statuses it may move to and the role allowed to make that move. The move function refuses anything not in the table, then refuses the right move made by the wrong role, and otherwise returns a new run object rather than changing the old one. The sequence shows a receiver trying to skip pickup, the wrong person marking a pickup, and a coordinator trying to change a run that is already finished.
Repositories hide the SQL
Lesson 3's dependency rule says services call repositories and repositories talk to the database. A repository is a module that offers plain functions such as findOpenOffers, claim and recordReceipt, and hides the SQL behind them. The claiming service never sees a query; it calls repository functions and applies Lucy's rules. This has a practical benefit beyond tidiness: for unit tests the team can swap the PostgreSQL repository for an in-memory one with exactly the same functions, so the claiming rules can be tested in milliseconds without a database.
// An in-memory repository with the same functions as the PostgreSQL one.
function memoryRepo() {
const offers = new Map();
const runs = new Map();
let nextRun = 1;
return {
addOffer(o) { offers.set(o.id, { ...o, status: 'open' }); },
openOffers() { return [...offers.values()].filter((o) => o.status === 'open'); },
claim(offerId, driverId) {
const o = offers.get(offerId);
if (!o || o.status !== 'open') return null; // compare and set: only an open offer can be claimed
o.status = 'claimed';
const run = { id: nextRun++, offerId, driverId, status: 'claimed' };
runs.set(run.id, run);
return run;
},
runsFor(driverId) { return [...runs.values()].filter((r) => r.driverId === driverId); },
};
}
const repo = memoryRepo();
repo.addOffer({ id: 7, donor: 'Copperleaf Bakery', est_grams: 12000 });
repo.addOffer({ id: 8, donor: 'Pine Hill Grocers', est_grams: 30000 });
console.log('open:', repo.openOffers().map((o) => o.id).join(', '));
console.log('Wendy claims 7:', repo.claim(7, 'wendy'));
console.log('Raj claims 7:', repo.claim(7, 'raj'));
console.log('open:', repo.openOffers().map((o) => o.id).join(', '));
console.log('Wendy has', repo.runsFor('wendy').length, 'run');open: 7, 8
Wendy claims 7: { id: 1, offerId: 7, driverId: "wendy", status: "claimed" }
Raj claims 7: null
open: 8
Wendy has 1 runWhat this code does
The repository keeps offers and runs in maps and exposes the same functions the PostgreSQL version does, so code that uses it cannot tell the difference. The claim function only succeeds if the offer is still open, which is the same compare-and-set idea the real query uses. The second claim on offer 7 returns null, so the service can tell Raj that someone got there first.
The real repository has to get the same guarantee from the database, because two requests can arrive at the same moment from two phones. The studio's pattern, from the partner service, is a single conditional update: change the offer to claimed only if it is still open, and look at how many rows changed. If none did, someone else won. Theo shows the team the pattern running against SQLite on his laptop, because it behaves the same way and needs no server.
import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync(':memory:');
db.exec(`CREATE TABLE offers (id INTEGER PRIMARY KEY, status TEXT NOT NULL DEFAULT 'open');
CREATE TABLE runs (id INTEGER PRIMARY KEY, offer_id INTEGER NOT NULL UNIQUE, driver TEXT NOT NULL);
INSERT INTO offers (id) VALUES (7);`);
function claim(offerId, driver) {
db.exec('BEGIN');
try {
const r = db.prepare("UPDATE offers SET status = 'claimed' WHERE id = ? AND status = 'open'").run(offerId);
if (r.changes === 0) { db.exec('ROLLBACK'); return 'already claimed'; }
db.prepare('INSERT INTO runs (offer_id, driver) VALUES (?, ?)').run(offerId, driver);
db.exec('COMMIT');
return 'claimed';
} catch (err) {
db.exec('ROLLBACK');
throw err;
}
}
console.log('wendy:', claim(7, 'wendy'));
console.log('raj:', claim(7, 'raj'));
console.log(db.prepare('SELECT offer_id, driver FROM runs').all());What this code does
The update changes the offer only if its status is still open, and the number of changed rows tells the code whether this request won. Both statements run in one transaction, so a run row is never written for an offer that someone else claimed. The same pattern, with PostgreSQL's unique constraint behind it as a second defense, is what the real repository uses.
$ node claim-demo.mjs
wendy: claimed
raj: already claimed
[ [Object: null prototype] { offer_id: 7, driver: 'wendy' } ]In PostgreSQL the update uses the same WHERE clause, and the unique constraint on runs.offer_id catches any case the code gets wrong. Defense in depth, from WEB-230, applies to data as much as to security: the service checks the rule, the query enforces it atomically, and the constraint refuses anything that slips through.
Invented data for development
The last piece of Sprint 1's data work is a seed script that fills a development or staging database with invented data: nine donors with made-up names and addresses, twenty-two drivers called Driver 01 to Driver 22, a week of offers with realistic windows and weights, and some runs in every status. Real names and real addresses are never used, following the ground rules, and the script refuses to run if the database name contains *prod*. Odalys adds a seeded random generator, the technique from JS-201, so that the same seed always produces the same data, which makes bugs reproducible.
// Deterministic invented offers from a seeded generator.
function mulberry32(seed) {
return () => {
seed = (seed + 0x6d2b79f5) | 0;
let t = Math.imul(seed ^ (seed >>> 15), 1 | seed);
t = (t + Math.imul(t ^ (t >>> 7), 61 | t)) ^ t;
return ((t ^ (t >>> 14)) >>> 0) / 4294967296;
};
}
const rand = mulberry32(2029);
const pick = (list) => list[Math.floor(rand() * list.length)];
const donors = ['Donor A Bakery', 'Donor B Grocer', 'Donor C Cafe'];
const categories = ['bakery', 'produce', 'prepared', 'dairy'];
for (let i = 1; i <= 5; i++) {
const category = pick(categories);
const grams = 500 * (4 + Math.floor(rand() * 40));
const hour = 15 + Math.floor(rand() * 4);
const chilled = category === 'prepared' || category === 'dairy';
console.log(`offer ${i}: ${pick(donors).padEnd(15)} ${category.padEnd(9)} ${String(grams).padStart(6)} g ${hour}:00 ${chilled ? 'chilled' : ''}`);
}offer 1: Donor B Grocer produce 21500 g 15:00 offer 2: Donor C Cafe prepared 18000 g 15:00 chilled offer 3: Donor C Cafe produce 8000 g 18:00 offer 4: Donor C Cafe prepared 3500 g 18:00 chilled offer 5: Donor C Cafe prepared 8500 g 16:00 chilled
What this code does
The mulberry32 function is a small, well-known pseudo-random generator that produces the same sequence every time it starts from the same seed. Each invented offer picks a donor and category and gets a weight in steps of 500 grams and a pickup hour in the afternoon. Prepared food and dairy are marked chilled, so the seed data exercises the cold-food rule on every run of the script.
Worked problem
Converting a receipt for the report
Rosa's kitchen records a chilled receipt as 22.6 kg at 3.8 °C on the Canadian screen. The same delivery recorded in the US version would show 49.8 lb at 38.8 °F. What values are stored, and does the delivery pass the cold-food rule?
Show solution
- Weight: 22.6 kg × 1,000 = 22,600 g stored.
- From the US entry: 49.8 lb × 453.59237 ≈ 22,589 g, which rounds differently because 49.8 lb is itself rounded; both are stored as entered, in grams.
- Temperature: 3.8 °C × 10 = 38 tenths stored; 38.8 °F is (38.8 − 32) × 5 ÷ 9 ≈ 3.78 °C, stored as 38 tenths.
- Rule: 38 tenths is not above 40 tenths, so the chilled delivery is accepted.
Answer: The receipt is stored as about 22,600 g and 38 tenths of a degree Celsius, and it passes the cold-food rule. Storing integers in one unit means Paul's monthly report adds grams and shows kilograms or pounds only at the end.
Common mistakes
MistakeStoring weights in whatever unit the person typed.
InsteadStore one unit, grams, as an integer and convert only for display and input.
MistakeTrusting the service alone to prevent double claims.
InsteadUse a conditional update in a transaction and a unique constraint as a second defense.
MistakeEditing an old migration after it has run.
InsteadWrite a new numbered migration; migrations only move forward.
MistakeLetting any code change a run's status to anything.
InsteadModel status as a state machine with allowed transitions and roles.
MistakeSeeding development databases with real names.
InsteadUse invented, reproducible seed data and refuse to run against production.
Summary and key terms
- The data layer starts from the nouns in the stories: donors, locations, offers, runs, receipts, drivers and start areas.
- Offers and runs are separate because offers can go unclaimed.
- Values are stored in one unit as integers, grams and tenths of a degree Celsius, and converted only at the edges.
- Constraints enforce rules in the database: checks, foreign keys, a unique constraint for one run per offer, and an exclusion constraint for overlapping runs.
- Numbered, forward-only migrations bring every copy of the database to the same state.
- A run's status is a state machine with allowed transitions and roles.
- Repositories hide SQL behind plain functions, with an in-memory version for tests, and claims use a conditional update inside a transaction.
- Seed data is invented and reproducible.
- Entity
- A thing the business talks about that the system stores, such as a donor or an offer.
- Check constraint
- A database rule that refuses rows whose values break a condition.
- Unique constraint
- A database rule that refuses two rows with the same value in given columns.
- Exclusion constraint
- A PostgreSQL rule that refuses rows which conflict with each other by an operator, such as overlapping ranges.
- Migration
- A numbered file that changes a database schema and runs once on every copy of the database.
- State machine
- A model listing the states something can be in and the transitions allowed between them.
- Repository
- A module that hides data storage behind plain functions.
- Compare and set
- Changing a value only if it still has the value you expect, so concurrent changes cannot both succeed.
- Seed data
- Invented data loaded into a development or test database.
Practice problems
Work each problem on paper first, then reveal the answer to check.
Why are offers and runs separate tables?
Show answer
An offer can exist without becoming a run, and missed pickups are exactly the unclaimed offers.
Why store temperatures as tenths of a degree Celsius?
Show answer
Integers avoid floating-point errors and one unit means rules and reports never mix scales.
Why do touching pickup windows not violate the exclusion constraint?
Show answer
PostgreSQL ranges include their start and exclude their end by default.
What does an in-memory repository make possible?
Show answer
Fast unit tests of the rules without a database.
Try it yourself
Run transitions
Complete canMove(from, to) using the transitions in the lesson: claimed can go to picked_up, released or missed; picked_up can go to received or missed; received, missed and released go nowhere.
Return true or false.
function canMove(from, to) {
return false;
}
console.log(canMove('claimed', 'picked_up'));
console.log(canMove('claimed', 'received'));
console.log(canMove('picked_up', 'missed'));
console.log(canMove('received', 'claimed'));Try it yourself
Weight for display
Complete showWeight(grams, units). For metric, return kilograms with one decimal and kg; for us, return pounds with one decimal and lb.
Use 453.59237 grams per pound.
function showWeight(grams, units) {
return '';
}
console.log(showWeight(18400, 'metric'));
console.log(showWeight(18400, 'us'));
console.log(showWeight(500, 'us'));Lesson quiz · 5 questions
Score 60% or more to complete this lesson.
Create a free account to save your progress, take quizzes and earn a certificate.