2.A Counter and a Stockroom

The Problem in Real Life

Coding Straight Away vs. Designing the Resources First

A Simple REST API, a Database and CRUD

• Design the resources: two nouns: events (id, name, date, organiser_id) and waitlist entries (id, event_id, email, created_at). One event has many entries — a one-to-many relationship with a foreign key (Act 13).
• Design the data rules in the database: a unique constraint on (event_id, email) so nobody can join twice — even with two clicks in the same millisecond (Act 13's lesson: let the database decide). NOT NULL on required columns. A position isn't stored; it's calculated from created_at order, so removing someone automatically moves everyone up.
• CRUD — the four things you can do with data: Create (POST), Read (GET), Update (PUT/PATCH), Delete (DELETE) — the HTTP verbs from Act 12. Almost every app is CRUD at its core.
• The endpoints: POST /events (create), GET /events/:id (read one), POST /events/:id/waitlist (join), GET /events/:id/waitlist/position?email= (my position), GET /events/:id/waitlist (organiser: the full list, paginated — Act 23), DELETE /events/:id/waitlist/:entryId (organiser: remove someone).
• Connect the API to the database: a local PostgreSQL in Docker (Act 21), a connection pool with the pg library (Act 13), DATABASE_URL from an environment variable (Act 16), parameterised queries everywhere (Act 22), and a migration file that creates the tables, so the schema lives in Git.
• Good API manners from Act 23: validate input (an email must look like an email), return the right status codes — 201 for created, 404 if the event doesn't exist, 409 if already on the list, 400 for bad input — and consistent JSON errors.

CREATE TABLE events (
  id           SERIAL PRIMARY KEY,
  name         TEXT NOT NULL,
  starts_at    TIMESTAMPTZ NOT NULL,
  organiser_id INTEGER NOT NULL
);

CREATE TABLE waitlist_entries (
  id         SERIAL PRIMARY KEY,
  event_id   INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  email      TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (event_id, email)            -- nobody joins twice, even with two fast clicks
);

app.post("/events/:id/waitlist", async (req, res) => {
  const email = String(req.body.email || "").trim().toLowerCase();
  if (!/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(email)) {
    return res.status(400).json({ error: { code: "invalid_email" } });
  }
  try {
    const { rows } = await db.query(
      "INSERT INTO waitlist_entries (event_id, email) VALUES ($1, $2) RETURNING id, created_at",
      [req.params.id, email]
    );
    res.status(201).json(rows[0]);
  } catch (err) {
    if (err.code === "23505") return res.status(409).json({ error: { code: "already_joined" } });
    if (err.code === "23503") return res.status(404).json({ error: { code: "event_not_found" } });
    throw err;
  }
});

app.get("/events/:id/waitlist/position", async (req, res) => {
  const { rows } = await db.query(
    `SELECT position FROM (
       SELECT email, ROW_NUMBER() OVER (ORDER BY created_at, id) AS position
         FROM waitlist_entries WHERE event_id = $1
     ) ranked WHERE email = $2`,
    [req.params.id, String(req.query.email || "").toLowerCase()]
  );
  if (!rows.length) return res.status(404).json({ error: { code: "not_on_waitlist" } });
  res.json({ position: Number(rows[0].position) });
});