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.

An entity-relationship diagram. donors has many donor_locations. donor_locations has many offers. An offer has zero or one run. A run belongs to one driver and has zero or one receipt. drivers refer to a volunteer id in the PHP application. start_areas stands alone and is referenced by drivers.
The Rescue Runs data model

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.

JavaScript Example · One stored unit, two displays
// 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');
Output
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.

services/rescue-runs/migrations/0001_create_rescue_tables.sql
-- 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.

JavaScript Example · Run status as a state machine
// 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;
}
Output
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.

JavaScript Example · A repository for tests
// 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');
Output
open: 7, 8
Wendy claims 7: { id: 1, offerId: 7, driverId: "wendy", status: "claimed" }
Raj claims 7: null
open: 8
Wendy has 1 run

What 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.

JavaScript Example · claim-demo.mjs
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.

Running it
$ 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.

JavaScript Example · Reproducible seed data
// 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' : ''}`);
}
Output
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
  1. Weight: 22.6 kg × 1,000 = 22,600 g stored.
  2. 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.
  3. Temperature: 3.8 °C × 10 = 38 tenths stored; 38.8 °F is (38.8 − 32) × 5 ÷ 9 ≈ 3.78 °C, stored as 38 tenths.
  4. 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.

  1. 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.

  2. 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.

  3. Why do touching pickup windows not violate the exclusion constraint?

    Show answer

    PostgreSQL ranges include their start and exclude their end by default.

  4. 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.

Take the quiz