Codity Scheduler

A distributed job scheduling platform. PostgreSQL is both the queue and the system of record — no Redis, no message broker.

The problem it solves: when N workers request jobs at the same instant, no two may receive the same job; when a worker is killed mid-execution, its job must return to the queue — not vanish, and not run twice.

Runs locally with a single command. There is no hosted demo — everything below is verified against a fresh clone.

Run it	docker compose up then pnpm db:demo
Dashboard	http://localhost:5173
API	http://localhost:3000
API docs (Swagger)	http://localhost:3000/api/v1/docs
Demo login	operator@codity.test / correct-horse-battery-staple

---

Quickstart

Prerequisites: Docker (with Compose v2), and Node 22 + pnpm 9+ only if you want to run the test suite outside containers.

git clone https://github.com/PRCSM/codity-scheduler.git
cd codity-scheduler
cp .env.example .env          # works as-is — no manual edits required
docker compose up             # postgres + api + worker + scheduler + web

Wait for all services to report healthy, then seed demo data in a second terminal:

pnpm db:demo                  # migrate, reset, seed, ensure nothing is paused

Open http://localhost:5173 and sign in with operator@codity.test / correct-horse-battery-staple.

You should see three queues (emails, reports, webhooks) with differing retry policies, a live worker, a cron schedule firing every minute, populated retry history, and dead-lettered jobs.

Verifying it works

docker compose up --scale worker=3    # three workers compete; no job runs twice
docker kill codity-scheduler-worker-2 # lease expires, reaper requeues within ~40s
docker compose stop                   # graceful drain, exit 0, no attempts charged

Watch the Workers screen: killed workers turn red with frozen uptime, and their in-flight jobs return to the queue with the attempt refunded.

Ports

Service	Port
web	5173
api	3000
postgres	55432 (host) → 5432 (container)

Postgres is published on 55432 deliberately — a host Postgres install will win port 5432 over Docker's proxy and produce confusing authentication failures.

pnpm test --concurrency=1            # 246 tests
pnpm db:explain                      # regenerate docs/EXPLAIN.md
docker compose up --scale worker=3   # concurrency demonstration
pnpm db:resume                       # un-pause every queue and schedule

--concurrency=1 is needed on machines with limited RAM — four parallel Testcontainers suites will OOM.

---

Architecture

flowchart TD
    web["<b>apps/web</b><br/>React 19 · polls every 2s"]

    subgraph services["long-running services"]
        direction LR
        api["<b>apps/api</b><br/>Express 5 · :3000"]
        worker["<b>apps/worker</b><br/>poll 500ms · scales out"]
        sched["<b>apps/scheduler</b><br/>3 loops · 10s · no leader"]
    end

    pg[("<b>PostgreSQL 17</b><br/>queue + metadata · single source of truth")]
    ext["<b>external http</b><br/>SSRF-allowlisted"]

    web -- "REST + JWT" --> api
    api --> pg
    worker --> pg
    sched --> pg
    worker -.-> ext

    classDef client fill:#EDEAE3,stroke:#73726C,stroke-width:1.5px,color:#2C2C2A
    classDef svc fill:#DCE9F7,stroke:#4A6E96,stroke-width:1.5px,color:#1B3A5C
    classDef store fill:#E4E0F4,stroke:#6B5CA5,stroke-width:1.5px,color:#3B2E6E
    classDef outside fill:#F7E4DC,stroke:#A5674A,stroke-width:1.5px,color:#6E3B2E
    class web client
    class api,worker,sched svc
    class pg store
    class ext outside

Loading

Service	Responsibility
api	REST, auth, RBAC, CRUD, enqueue, read models. Never executes a job
worker ×N	poll → atomically claim → execute → record → heartbeat
scheduler ×1..N	promoter, cron materialization, lease reaper, dead-worker sweep
web	React operator dashboard

Full diagrams: docs/ARCHITECTURE_DIAGRAM.md — lifecycle state machine, claim sequence, crash recovery.

---

Job lifecycle

stateDiagram-v2
    [*] --> queued: enqueue (due now)
    [*] --> scheduled: enqueue (delayed / cron)
    scheduled --> queued: promoter — run_at <= now()
    queued --> claimed: claim — FOR UPDATE SKIP LOCKED
    claimed --> running: handler starts
    running --> completed: success
    running --> failed: handler error
    running --> queued: SIGTERM drain (attempt NOT charged)
    failed --> scheduled: attempt < max — backoff + jitter
    failed --> dead_letter: attempt >= max
    claimed --> queued: lease expired (attempt refunded)
    running --> queued: lease expired (attempt refunded)
    dead_letter --> queued: DLQ replay
    completed --> [*]
    dead_letter --> [*]

Loading

Transitions are enforced by a frozen allowed-map in packages/core/src/lifecycle.ts. Illegal transitions throw.

---

The claim

UPDATE jobs j
SET status = 'claimed', worker_id = $1, claimed_at = now(),
    lock_expires_at = now() + ($2 || ' seconds')::interval,
    attempt = j.attempt + 1
FROM (
  SELECT j2.id FROM jobs j2
  JOIN queues q ON q.id = j2.queue_id
  WHERE j2.queue_id = $3 AND j2.status = 'queued'
    AND j2.run_at <= now() AND q.is_paused = false
  ORDER BY j2.priority DESC, j2.run_at ASC, j2.id ASC
  FOR UPDATE OF j2 SKIP LOCKED
  LIMIT $4
) sel
WHERE j.id = sel.id
RETURNING j.*;

FOR UPDATE SKIP LOCKED is the whole mechanism — the second worker skips locked rows rather than blocking on them.

Alternative	Why it fails
SELECT then UPDATE	Two workers read the same rows between statements. Single-worker testing never reveals it
FOR UPDATE alone	Correct, but every worker serializes behind the first
Advisory locks	Correct, but needs a lock per job id and doesn't compose with the ordering

Backed by a partial index whose four columns match the ORDER BY term for term, so the plan is Limit → LockRows → Nested Loop → Index Scan with no Sort node:

CREATE INDEX idx_jobs_claim ON jobs (queue_id, priority DESC, run_at, id)
  WHERE status = 'queued';

Measured against 100,000 rows (pnpm db:explain):

Configuration	Select	Penalty
Full claim index	0.110 ms	—
Without id tiebreak	0.149 ms	1.4×
Claim index dropped	1.802 ms	16×
No indexes on jobs	7.643 ms	69×

---

Reliability

• Leases — every claim sets lock_expires_at; a live worker extends it by 30 s on each 5 s heartbeat. The reaper requeues expired leases every 10 s.
• The reaper refunds the attempt. A SIGKILLed worker is infrastructure failure, not job failure. Without the refund, three rolling deploys against max_attempts = 3 would dead-letter jobs that never failed.
• Aborted ≠ failed. During graceful drain, in-flight jobs are released without charging the attempt.
• Backoff — fixed, linear, exponential, each clamped to max_delay_ms, then full jitter. Without it, 500 jobs failing from one outage all retry at the same instant and knock the recovering service over again.
• At-least-once, not exactly-once. Exactly-once delivery isn't achievable without consumer cooperation — a worker can complete a side effect and die before committing. Mitigations: UNIQUE (queue_id, idempotency_key) on enqueue, UNIQUE (job_id, execution_seq) on execution. Handlers receive { jobId, attempt } to deduplicate downstream.
• Multi-replica scheduler, no leader election — SKIP LOCKED on scheduled_jobs makes concurrent replicas structurally safe.
• Missed cron runs: fire once, skip the backlog. The next occurrence is computed from now, never from the stored next_run_at — otherwise an hour of downtime replays 60 jobs one per tick while looking like a working scheduler.

---

Database

15 tables · 5 native enums · 11 indexes (7 partial) · 14 CASCADE + 8 SET NULL FKs · 4 CHECKs. Full ER diagram and index rationale: docs/ER_DIAGRAM.md

Selected decisions:

• jsonb payloads, capped at 64 KB by a database CHECK. Job payloads are arbitrary by nature; forcing a relational schema would be incorrect normalization, not better normalization. The cap is a storage-DoS guard.
• One execution row per attempt. jobs holds current state; job_executions is the immutable audit trail — retry history for free.
• UNIQUE (job_id, execution_seq), not (job_id, attempt). Refunding an attempt makes attempt numbers reusable, so identity (execution_seq, monotonic) is separated from accounting (attempt, repeatable).
• workers.last_heartbeat_at is deliberately denormalized. The reaper queries it every 10 s; a MAX() over a growing time-series table on every sweep is the wrong shape.
• Cascade down ownership, null out references. Deleting a worker never deletes job history. DLQ entries copy payload and failure reason, so they survive the original being purged.

---

Security

Area	Control
Passwords	argon2id, m=19456 t=2 p=1
Tokens	jose, 15-min access + 7-day rotating refresh, hashed; replay revokes the family
Tenant isolation	one assertProjectAccess() guard; org_id from the token, never from a param
RBAC	owner / admin / member / viewer, middleware-enforced
API keys	32-byte CSPRNG, SHA-256 at rest, shown once
SSRF	DNS resolved then every address checked; private CIDRs and 169.254.169.254 blocked; re-validated on each redirect
Rate limiting	5 auth / 15 min / IP · 1000 enqueues / min / project
Logging	pino redaction; payloads log size and keys, never values

Routes as data. Every route is an entry in one ALL_ROUTES array declaring method, path, scope, schemas, and handler. mountRoutes is the only place a chain is assembled, and it attaches the guard to every project-scoped entry. The guard can't be forgotten by omission. The same array feeds the router, the OpenAPI generator, and the isolation test, so the three can't drift.

---

Testing

246 tests — api 117, core 46, scheduler 42, worker 41. Real PostgreSQL 17 via Testcontainers; the database is never mocked, because a mocked concurrency test always passes and proves nothing.

1000 queued jobs · 10 concurrent claim loops · real Postgres

  claimed              1000
  claimed twice           0     ← the assertion
  distinct worker_ids    10
  left in 'queued'        0

  distribution: 100 110 100 100 110 100 100 90 100 90

Five consecutive runs, zero duplicates. Two independent detectors: an in-process Set, and a database-side count(*) FILTER (WHERE attempt <> 1) = 0.

Every claim verified by breaking it first:

Injected fault	Observed
Naive SELECT-then-UPDATE	2,560 jobs handed out for 1,000 rows — 1,560 duplicates, one row claimed 7 times
SKIP LOCKED removed from cron	duplicate job in the same minute
Attempt refund removed	5 tests fail
userId dropped from the access guard	37 of 50 isolation tests fail
Router backdoor bypassing mountRoutes	drift check fails

Beyond the suite: --scale scheduler=3 against a live * * * * * schedule for eight real minutes produced exactly one job per minute. Worker SIGKILLed mid-execution recovered with attempt_refunded: true.

---

Stack

Node 22 · TypeScript 5.7 strict · Express 5 · PostgreSQL 17 · Drizzle ORM · postgres.js · zod · pino · argon2id · jose · React 19 · Vite 6 · Tailwind v4 · TanStack Router/Query/Table · Vitest + Testcontainers · Biome · pnpm + Turborepo · Docker

Why not Prisma: SKIP LOCKED requires $queryRaw, forfeiting the type safety that justified it. Why not Redis/RabbitMQ: job state and metadata mutate in one transaction — no dual-write problem, and one less service. Revisit past ~5k jobs/sec.

---

Layout

packages/shared     types, zod schemas, constants — zero side effects
packages/db         Drizzle schema, migrations, seed, EXPLAIN harness
packages/core       claim · lifecycle · backoff · lease · retry · dlq
apps/api            HTTP only
apps/worker         consumes core, never writes claim SQL of its own
apps/scheduler      cluster-wide loops, multi-replica safe
apps/web            React dashboard
docs/               architecture, database, concurrency, API, security,
                    testing, design decisions, EXPLAIN, engineering log

Dependency direction is one-way: shared ← db ← core ← {api, worker, scheduler}. core never imports from apps/.

---

Documentation

File	Contents
docs/ARCHITECTURE_DIAGRAM.md	system, lifecycle, claim sequence, crash recovery
docs/ER_DIAGRAM.md	full ER diagram, index rationale
docs/DATABASE.md	every table, key, cascade, constraint
docs/CONCURRENCY.md	claim protocol, leases, backoff, idempotency
docs/API_CONTRACT.md	endpoints, envelopes, pagination, auth
docs/SECURITY_GUIDELINES.md	14 areas + pre-submission checklist
docs/TESTING_RULES.md	testing philosophy and required coverage
docs/DESIGN_DECISIONS.md	ADRs with trade-offs
docs/EXPLAIN.md	generated query plans — cannot drift
docs/PROGRESS.md	session-by-session engineering log

---

Environment

Variable	Purpose
DATABASE_URL	Postgres connection string
JWT_SECRET / JWT_REFRESH_SECRET	32-byte signing secrets
WORKER_CAPACITY	max concurrent jobs per worker (5)
WORKER_POLL_INTERVAL_MS	idle poll interval (500)
WORKER_HEARTBEAT_MS	heartbeat + lease extension (5000)
SHUTDOWN_GRACE_MS	drain window on SIGTERM (30000)
SCHEDULER_TICK_MS	promoter / reaper / sweeper interval (10000)
HTTP_HANDLER_ALLOWLIST	SSRF allowlist for http_request
CORS_ORIGINS	comma-separated origin allowlist

.env.example is committed; .env is gitignored.

---

Runbook

Symptom	Cause	Action
depth_claimable climbing, Running 0	too few workers, or queue paused	scale workers; check is_paused; pnpm db:resume
depth_scheduled large	normal — delayed and backoff jobs	none
Jobs stuck running	reaper down	check scheduler logs
Same job twice	claim broken	pnpm test --concurrency=1 immediately
DLQ filling fast	downstream outage	pause queue, fix, bulk replay
/readyz failing	DB unreachable or migrations pending	check connection and migration state