Database Viewer

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  total_cents INTEGER NOT NULL
);

CREATE TABLE jobs (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind TEXT NOT NULL,
  payload JSONB NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'running', 'done', 'failed')),
  attempts INTEGER NOT NULL DEFAULT 0,
  max_attempts INTEGER NOT NULL DEFAULT 3,
  run_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  locked_until TIMESTAMPTZ,
  last_error TEXT
);

-- Workers only ever look for pending jobs that are due, oldest first.
CREATE INDEX jobs_pending_idx ON jobs (run_at, id) WHERE status = 'pending';

CREATE TABLE invoices (
  order_id INTEGER PRIMARY KEY REFERENCES orders (id),
  total_cents INTEGER NOT NULL
);


INSERT INTO orders (id, customer, total_cents)
VALUES
  (1, 'alice', 4200),
  (2, 'bob', 1500),
  (3, 'carol', 9900);

-- Three jobs waiting to run.
INSERT INTO jobs (kind, payload)
VALUES
  ('send_receipt', '{"order_id": 1}'),
  ('send_receipt', '{"order_id": 2}'),
  ('create_invoice', '{"order_id": 3}');

-- Two jobs whose workers died ten minutes ago, so their leases have expired.
INSERT INTO jobs (kind, payload, status, attempts, run_at, locked_until)
VALUES
  ('send_receipt', '{"order_id": 3}', 'running', 1,
   now() - interval '10 minutes', now() - interval '5 minutes'),
  ('sync_crm', '{"customer": "carol"}', 'running', 3,
   now() - interval '10 minutes', now() - interval '5 minutes');