Challenge 1 — Solution

Task: Design (in writing) the SQL schema for a "jobs" table suitable
for the queue pattern shown in this chapter — list each column, its
type, and a one-sentence reason it's needed. Include at minimum: an
id, a job type, a payload, a status, and a created timestamp.

CREATE TABLE jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    payload TEXT NOT NULL,
    status ENUM('pending', 'processing', 'complete', 'failed') NOT NULL DEFAULT 'pending',
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL
);

Column-by-column reasoning:

- id (INT AUTO_INCREMENT PRIMARY KEY): a unique identifier for each
  job, needed so markJobComplete()/markJobFailed() can target the
  exact row that was just processed via a WHERE id = :id clause.

- type (VARCHAR(50) NOT NULL): identifies what kind of job this is
  (e.g. 'send_email', 'resize_image') - the worker needs this to know
  which handler function to dispatch to.

- payload (TEXT NOT NULL): stores the job-specific data as a JSON-
  encoded string (e.g. the recipient email and subject) - TEXT rather
  than a fixed VARCHAR length, since different job types could need
  very different amounts of data.

- status (ENUM(...) NOT NULL DEFAULT 'pending'): tracks where the job
  currently is in its lifecycle - the worker's own polling query
  filters on WHERE status = 'pending', and a failed job stays visibly
  marked 'failed' rather than silently vanishing, per the chapter's own
  warn-box.

- error_message (TEXT NULL): records what went wrong if a job fails,
  giving something concrete to investigate later rather than just
  knowing a job failed with no further detail - nullable since a
  successful job has nothing to record here.

- created_at (TIMESTAMP DEFAULT CURRENT_TIMESTAMP): records when the
  job was originally queued, useful for monitoring how long jobs sit
  pending before being picked up.

- completed_at (TIMESTAMP NULL): records when the job actually
  finished (successfully or not) - nullable since a still-pending job
  has no completion time yet, letting a monitoring query measure how
  long a job took to process.

Notes:
- The status column uses an ENUM specifically because it's a
  genuinely closed, small set of well-defined states (matching this
  chapter's own three named states: pending, complete, failed - with
  "processing" added here as a reasonable fourth state marking a job
  currently being worked on) - not a place free-text input would ever
  need to go.
- payload is deliberately stored as JSON text rather than as separate
  columns per job type, since different job types (send_email vs.
  resize_image) need completely different data shapes - a fixed set of
  columns couldn't accommodate both without a lot of unused NULL
  columns for whichever type isn't currently being stored.