Maintenance records and relational UI

Why this matters

Everything so far has been one table on one screen. Real applications are made of related things: a machine has a maintenance history, an order has line items, a patient has notes. Today you build the first relationship end to end, and you meet the failure that defines this kind of work — a workflow that is one action to the user but two writes to the database, where one of them can fail.

By the end, logging a repair will file the record under the right machine, change that machine's status, and leave both screens agreeing with each other.

Foreign keys: what actually links the two

A foreign key is a column in one table whose value must match an existing primary key in another table. Here, maintenance_records.equipment_id holds the id of the equipment row it belongs to. That column is the only thing linking the two. Not a shared name, not the order rows come back in, not anything in your React code — one integer, checked by the database.

CREATE TABLE maintenance_records (
  id            SERIAL PRIMARY KEY,
  equipment_id  INTEGER NOT NULL REFERENCES equipment(id) ON DELETE RESTRICT,
  performed_on  DATE NOT NULL,
  notes         TEXT NOT NULL,
  technician_id INTEGER NOT NULL REFERENCES users(id)
);

Two consequences worth stating out loud, because they are the constraint doing real work for you:

• Inserting a record whose equipment_id does not exist fails. PostgreSQL raises error code 23503, a foreign-key violation. You cannot create an orphan by accident.
• ON DELETE RESTRICT means deleting a machine that still has records fails too. The alternative, ON DELETE CASCADE, deletes the children with the parent. Both are legitimate; choose deliberately. For a maintenance log, silently destroying history is usually wrong.

The key lives on the many side. One machine has many records, so each record points up at one machine. This is the one-to-many shape from Day 43, and the direction catches people out: there is no list of record ids stored on the equipment row.

Nested resources in the API and the UI

A maintenance record has no meaning on its own, so its URL says so:

GET  /api/equipment/7/maintenance      list the records for machine 7
POST /api/equipment/7/maintenance      add a record to machine 7

The nested form makes the parent explicit and gives your Day 52 authorization one obvious place to stand: check that this user may see machine 7 once, before touching any records at all.

This produces the rule that matters most today. The parent id comes from the URL, never from the request body. If your handler reads req.body.equipmentId, a user can post a record onto a machine they were never allowed to open — the frontend would never do that, but the frontend is not what sends the request. Take it from req.params.id and ignore any id in the body.

The UI mirrors the URL. The equipment detail page at /equipment/7 shows the machine at the top and its history beneath, and the child list gets its own loading, empty, and error states. Empty is the common case here: a machine installed last week legitimately has no maintenance. "No maintenance recorded yet" and a spinner that never stops look identical if you only wrote one of them.

One user action, two writes

"Log a repair and mark the machine back in service" is one thing to the technician. To the database it is an INSERT into maintenance_records and an UPDATE to equipment.status.

If you send two requests, four outcomes exist, and two of them are wrong: the record saved but the status did not, or the status changed with no record explaining why. Both leave a maintenance log that lies, and the user has no idea which happened.

The fix is the transaction you met on Day 48. Do both writes inside one request, wrapped in BEGIN and COMMIT, so either both land or neither does:

export async function logMaintenance(equipmentId, input) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query(
      `INSERT INTO maintenance_records (equipment_id, performed_on, notes, technician_id)
       VALUES ($1, $2, $3, $4) RETURNING *`,
      [equipmentId, input.performedOn, input.notes, input.technicianId]
    );
    const equipment = await client.query(
      'UPDATE equipment SET status = $1 WHERE id = $2 RETURNING *',
      [input.status, equipmentId]
    );
    await client.query('COMMIT');
    return { record: rows[0], equipment: equipment.rows[0] };
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Note client.release() in finally. A connection you take from the pool and never give back is gone; do that a few dozen times and the API stops answering with no error to explain it.

Refreshing the related views

After that write, two things on screen are stale: the record list and the machine's status badge. Refresh one and not the other and the page contradicts itself — a status of "active" above a list whose newest entry says the machine was stripped down.

You have two clean options:

1. Return both objects from the endpoint, as the service above does, and set both pieces of state from one response. One round trip, and the two values are guaranteed to be from the same moment in time.
2. Re-fetch both after success. Two requests, simpler code, and still consistent as long as you wait for both before rendering.

Either is fine. What is not fine is refreshing the child list and leaving the parent alone. And keep one more staleness in mind: the equipment list page, on a different route, is still holding the old status from before. Re-fetch it when the user navigates back rather than trusting state that was loaded minutes ago.

Screen action	Request	Tables	Must be true afterwards
Open machine 7	GET /api/equipment/7 + GET /api/equipment/7/maintenance	both, read	history shown, or an explicit empty state
Submit "log repair"	POST /api/equipment/7/maintenance	both, one transaction	record exists and status changed, or neither
Return to the list	GET /api/equipment	equipment, read	machine 7 shows its new status

Walkthrough

The detail page reads its id from the route (Day 62) and loads parent and child together:

export function EquipmentDetailPage() {
  const { id } = useParams();
  const [equipment, setEquipment] = useState<Equipment | null>(null);
  const [records, setRecords] = useState<MaintenanceRecord[]>([]);
  const [status, setStatus] = useState<'loading' | 'error' | 'ready'>('loading');

  const load = useCallback(async () => {
    setStatus('loading');
    try {
      const [item, history] = await Promise.all([
        apiFetch<Equipment>(`/api/equipment/${id}`),
        apiFetch<MaintenanceRecord[]>(`/api/equipment/${id}/maintenance`)
      ]);
      setEquipment(item);
      setRecords(history);
      setStatus('ready');
    } catch {
      setStatus('error');
    }
  }, [id]);

  useEffect(() => { load(); }, [load]);

  if (status === 'loading') return <p>Loading machine…</p>;
  if (status === 'error' || !equipment) return <p role="alert">Could not load this machine.</p>;

  return (
    <>
      <h2>{equipment.name} — {equipment.status}</h2>
      {records.length === 0
        ? <p>No maintenance recorded yet.</p>
        : <ul>{records.map((r) => <li key={r.id}>{r.performedOn}: {r.notes}</li>)}</ul>}
      <LogMaintenanceForm equipmentId={equipment.id} onSaved={load} />
    </>
  );
}

onSaved={load} is the refresh: after a successful submit the form calls the same loader, so parent and child are both re-read and neither can drift.

Your turn

1. Confirm your maintenance_records table has a REFERENCES equipment(id) foreign key. Add it if it is missing, as a migration (Day 48) rather than by editing the original file.
2. Add GET /api/equipment/:id/maintenance, ordered newest first, scoped to that machine only.
3. Add POST /api/equipment/:id/maintenance that takes equipment_id from req.params and ignores any id in the body. Return 404 if the machine does not exist.
4. Do the insert and the status update in one transaction. Return both saved objects.
5. Build the detail page: machine header, history list with a real empty state, and the log form.
6. On success, refresh both. Confirm the status badge and the newest record agree.
7. Reload the page and confirm the record is still there under the same machine.
8. Verify in SQL that it went to the right place: SELECT equipment_id, notes FROM maintenance_records ORDER BY id DESC LIMIT 1;

Common pitfalls

• Taking the parent id from the request body. It lets a client file a record against a machine it may not touch. The URL is the authority.
• Two separate requests for one action. Sooner or later the second one fails and the log is wrong. One request, one transaction.
• No empty state on the child list. A machine with no history looks broken instead of new.
• Refreshing only the list. The status badge above it goes stale and the page contradicts itself in front of the user.

Verify it yourself

Open today's reference, the PostgreSQL tutorial, and find its section on foreign keys.

1. This lesson used ON DELETE RESTRICT. Find what PostgreSQL does by default when no ON DELETE clause is given, and note whether that matches what you assumed.
2. The tutorial describes at least one referential action this lesson did not mention. Name it and say what it would do to your maintenance history.

Write both answers into your project notes, then check which behaviour your own schema actually has. Assumptions about constraints are exactly the kind of thing that stays wrong until data is lost.