Name: Wahengbam Paikhomba Kumar Reg. No.: «REG_NO» Email: wahengbamkumar546@gmail.com · GitHub: github.com/PRCSM
| Repository | https://github.com/PRCSM/codity-scheduler |
| Run it | docker compose up then pnpm db:demo |
| Dashboard | http://localhost:5173 |
| API docs (Swagger) | http://localhost:3000/api/v1/docs |
| Demo login | operator@codity.test / correct-horse-battery-staple |
There is no hosted demo. The stack runs end-to-end from a fresh clone with one command; setup is verified in §11.
A production-inspired distributed job scheduling platform. Postgres is both the queue and the system of record; there is no Redis or message broker.
The central problem: 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.
Four deployables, one database:
| Service | Responsibility |
|---|---|
api |
REST, auth, RBAC, queue/job CRUD, enqueue, read models |
worker (×N) |
poll → atomically claim → execute → record → heartbeat |
scheduler (×1..N) |
cron materialization, lease reaper, dead-worker sweep |
web |
React operator dashboard |
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
┌──────────────┐
│ React SPA │ Vite build, static
└──────┬───────┘
│ REST + 2s polling
┌──────▼───────┐
│ API Service │ Express 5
└──────┬───────┘
│
┌──────────▼─────────────────┐
│ PostgreSQL 17 │
│ queue + system of record │
└──────▲──────────────┬──────┘
│ │
┌──────────────┴──┐ ┌──────▼──────────────┐
│ Worker × N │ │ Scheduler × 1..N │
│ claim→run→ │ │ cron materialize │
│ record→beat │ │ lease reaper │
└─────────────────┘ │ dead-worker sweep │
└─────────────────────┘
┌──────────┐
│ scheduled│ run_at in future / awaiting retry
└────┬─────┘
│ run_at <= now
┌────▼─────┐
┌─────►│ queued │◄──── reaper (lease expired, attempt refunded)
│ └────┬─────┘◄──── retry (backoff + full jitter)
│ │ SKIP LOCKED claim
│ ┌────▼─────┐
│ │ claimed │
│ └────┬─────┘
│ ┌────▼─────┐
│ │ running │
│ └──┬────┬──┘
│ ok │ │ error
│ ┌─────▼─┐ │
│ │ done │ │
│ └───────┘ │
│ ┌─────▼─────┐
└────────┤ failed │ attempt < max
└─────┬─────┘
│ attempt >= max
┌─────▼──────┐
│ dead_letter│
└────────────┘
Transitions are enforced by a frozen allowed-map in packages/core/src/lifecycle.ts. Illegal transitions throw rather than silently corrupting state.
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 ← graded logic
apps/api HTTP only, never executes a job
apps/worker consumes core, never writes claim SQL of its own
apps/scheduler cluster-wide loops, multi-replica safe
apps/web React dashboard
Dependency direction is strictly one-way: shared ← db ← core ← {api, worker, scheduler}. core never imports from apps/, which is what keeps the concurrency tests fast and honest.
15 tables, 5 native enums, 11 named indexes (7 partial), 14 CASCADE + 8 SET NULL foreign keys, 4 CHECK constraints.
organizations ─┬─< organization_members >─ users
└─< projects ─┬─< api_keys
├─< retry_policies ──┐
├─< job_batches │ SET NULL
└─< queues <─────────┘
│
┌─────────────────────────┼──────────────────────┐
│ │ │
scheduled_jobs dead_letter_jobs jobs
│ SET NULL │ SET NULL │
└────────────────────────► └────────────────────► ├─< job_executions ─< job_logs
│
workers ──────────┘ SET NULL
│
└─< worker_heartbeats
jsonb for payloads, not json or a relational schema. Binary storage, GIN-indexable, key dedup. Job payloads are arbitrary by nature — forcing a relational schema on them would be incorrect normalization, not better normalization. Capped at 64 KB by a database CHECK as a storage-DoS guard.
Retry policies are their own table. One policy is reused across queues and a job can override it. Third normal form: retry configuration depends on the policy, not on the queue. ON DELETE SET NULL so deleting a policy cannot cascade-delete queues.
One execution row per attempt, not per job. jobs holds current state; job_executions is the immutable audit trail. This is what gives retry history for free.
UNIQUE (job_id, execution_seq), not (job_id, attempt). The obvious design is incompatible with refunding an attempt on infrastructure failure (§4). Separating identity (execution_seq, monotonic, never reused) from accounting (attempt, plain, repeatable) lets both invariants hold at once.
workers.last_heartbeat_at is deliberately denormalized from worker_heartbeats. The reaper queries it every 10s; a MAX() aggregate over a growing time-series table on every sweep is the wrong shape.
Cascade rule: cascade down ownership, null out references. Deleting a worker must never delete job history. DLQ entries copy the payload and failure reason rather than referencing the job, so they survive the original being purged — which is the entire point of a dead letter queue.
CREATE INDEX idx_jobs_claim
ON jobs (queue_id, priority DESC, run_at, id)
WHERE status = 'queued';A partial index: only claimable rows are in it. A table holding 85,000 completed jobs still has a claim index containing only the queued few thousand, so it stays resident in memory and rows drop out automatically on status update — no cleanup job.
All four ORDER BY terms are index columns, so the plan is Limit → LockRows → Nested Loop → Index Scan with no Sort node and exactly LIMIT rows read.
Measured against exactly 100,000 rows (5,065 claimable):
| 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× |
| With priority aging enabled | 2.362 ms | 21× |
Regenerable with pnpm db:explain — the numbers in docs/EXPLAIN.md are written by the harness and cannot drift from reality.
Every index is either partial or covering a known access path. There are no speculative indexes: each one costs on write, and an index nothing queries is pure overhead.
UPDATE jobs j
SET status = 'claimed', worker_id = $1, claimed_at = now(),
lock_expires_at = now() + ($2 || ' seconds')::interval,
attempt = j.attempt + 1, updated_at = now()
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 entire mechanism. Two workers executing this simultaneously never receive the same row — the second skips rows the first has locked instead of blocking on them.
| Alternative | Why it fails |
|---|---|
SELECT ... LIMIT n then UPDATE |
Two workers read the same rows between statements. Double execution. Single-worker testing never reveals it |
FOR UPDATE without SKIP LOCKED |
Correct, but every worker serializes behind the first — throughput collapses to single-worker |
pg_advisory_xact_lock |
Correct, but needs a lock per job id and does not compose with the ordering |
Claiming from a single queue (queue_id = $1, not ANY(...)) is what makes queue_id an equality prefix on the index, so the index supplies the ordering and LIMIT terminates early. Workers iterate their queues in queue-priority order instead.
The queue row is locked before counting in-flight jobs, then LEAST(worker_capacity, max_concurrency - running) are claimed. Trade-off accepted: this serializes claim transactions per queue, capping per-queue claim throughput; cross-queue claims stay fully parallel. Correctness over throughput. The scale-out answer is queue sharding, which is designed but deliberately not built.
Every claim sets lock_expires_at; a live worker extends it by 30s on each 5s heartbeat. The reaper sweeps every 10s and requeues jobs whose lease has passed.
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, and their last_error would read like a genuine handler fault.
The same distinction applies at shutdown: an aborted handler is not a failed handler. During graceful drain, in-flight jobs are released to queued without charging the attempt. This was found in a live container, not in a test — the original test used a never-settling promise, so it exercised the grace-expiry path and missed the handler-rejects-on-abort path entirely.
Fixed, linear, and exponential, each clamped to max_delay_ms, then full jitter: actual = random(0, computed).
Without jitter, 500 jobs failing from a single downstream outage all retry at the same instant and knock the recovering service over again. Observed live: 322 ms → 665 ms → 1541 ms.
This system provides at-least-once delivery with idempotency support, not exactly-once.
Exactly-once delivery is not achievable in a distributed system without consumer cooperation: a worker can complete a side effect and die before committing the status update, and no amount of database cleverness fixes that. The two mitigations built are:
UNIQUE (queue_id, idempotency_key) WHERE idempotency_key IS NOT NULLon enqueue, inserted withON CONFLICT DO NOTHINGthen selected — never check-then-insert, which is the same race in different clothingUNIQUE (job_id, execution_seq)on execution, making a duplicated physical run fail at the database level rather than only in application code
Handlers receive { jobId, attempt } so they can deduplicate downstream effects. Note that attempt is stable across a crash-and-retry but not unique per physical run — usually the semantics you want, since a retried interruption should not charge a card twice.
Cron materialization uses FOR UPDATE SKIP LOCKED on scheduled_jobs, which makes concurrent replicas structurally safe without Raft or a lock table. The whole pass is one transaction: the locks must still be held when next_run_at is written, or a second replica picks up a row the first already decided to fire.
Missed-run policy: fire once, skip the backlog. The next occurrence is computed from now, never from the stored next_run_at. Advancing from next_run_at after an hour of downtime yields a time still 59 minutes in the past, so the row is due again next tick and the dispatcher replays the entire backlog one job per tick — while looking like a working scheduler.
| Area | Control |
|---|---|
| Passwords | argon2id, m=19456 t=2 p=1 (OWASP 2024) |
| Tokens | jose, 15-min access + 7-day rotating refresh, stored hashed; replay of a rotated token revokes the whole family |
| Tenant isolation | one assertProjectAccess() guard; org_id derived from the token, never from a request parameter |
| RBAC | owner / admin / member / viewer, enforced in middleware not handlers |
| API keys | 32-byte CSPRNG, SHA-256 at rest, secret shown once, revocable |
| SSRF | http_request resolves DNS then checks every address, blocks private CIDRs including 169.254.169.254, re-validates on each of ≤3 redirects, never forwards Authorization |
| Injection | parameterized queries only, including the raw claim SQL |
| Rate limiting | 5 auth attempts / 15 min / IP; 1000 enqueues / min / project |
| Payload | 64 KB cap in zod and as a database CHECK |
| Logging | pino redaction; job payloads log size and keys, never values |
| Errors | no stack traces in production responses — logged, with the request_id returned |
| Containers | multi-stage, non-root uid 1000, no dev dependencies in the runtime image |
Every route is an entry in a single ALL_ROUTES array declaring its method, path, scope, zod schemas, and handler. mountRoutes is the only place a handler chain is assembled, and it attaches the access guard to every entry scoped project.
The guard cannot be forgotten by omission — you would have to declare a project-scoped route as user-scoped, which is a visible lie the test suite then fails on. The same array feeds the router, the OpenAPI generator, and the isolation test, so the three cannot drift apart.
50 isolation tests: one generated 403 assertion per project-scoped route plus every org-scoped one, enumerated from the router definitions rather than hand-listed. Four integrity checks prevent coverage lapsing silently — a drift check against the live Express stack, a structural check that the guard is on each layer, a reviewed allowlist for non-project-scoped routes, and a fixture-completeness check (validation runs before the guard, so a missing body would surface as 422 and mask the 403).
Verified by breaking it: dropping userId from the guard's join fails 37 of 50; reclassifying one route as user-scoped fails the classification check; a router.get backdoor bypassing mountRoutes fails the drift check.
/api/v1, OpenAPI generated from the zod schemas via zod-to-openapi so documentation cannot drift from code. Swagger UI at /api/v1/docs.
- Cursor pagination only, never
OFFSET. Offset breaks on a table with constant inserts — rows shift between pages and you both skip and duplicate records. Verified against 100k rows: page 1 →next_cursor→ page 2, zero overlap. - One error envelope everywhere:
{ error: { code, message, details, request_id } } 201created ·202enqueue accepted ·409idempotency conflict ·422validation with per-field detail ·429withRetry-AfterX-Request-Idon every request and response, threaded into every log line/healthz(liveness) and/readyz(DB reachable + migrations applied) are separate. Liveness failing means restart me; readiness failing means stop routing to me. Conflating them causes restart loops during a transient DB blip.
Coverage: auth · orgs · members · projects · API keys · queues · retry policies · jobs (immediate, delayed, scheduled, cron, batch) · executions · logs · schedules with preview · workers · DLQ with replay · metrics.
React 19 + Vite 6 + Tailwind v4 + TanStack Router/Query/Table. Live updates via 2-second polling — the brief permits either, and on a monitoring dashboard the difference from WebSockets is imperceptible while the cost is roughly two hours.
| Screen | Purpose |
|---|---|
| Overview | queue count, in-flight vs capacity, completed/failed executions, throughput bars, status split, queue and worker summaries |
| Queue detail | configuration left, live stats right; pause/resume; recent jobs |
| Job explorer | filterable over 100k jobs, cursor paginated, filters in typed URL search params |
| Job detail | per-attempt timeline, payload, worker assignment, logs |
| Workers | list plus topology canvas — queue nodes, worker nodes, connectors, frozen uptime for dead workers |
| Schedules | cron entries, human-readable, next fire times |
| DLQ | expandable rows with editable payload and replay |
Design language: off-white canvas, white cards with hairline borders, 6 px default radius, 13 px base type, monospace for every identifier, status as dot-plus-label rather than filled badges. Borders rather than shadows — the emitted CSS carries exactly one elevation rule.
246 automated 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 of claimed ids, and a database-side count(*) FILTER (WHERE attempt <> 1) = 0.
A green test nobody has tried to break proves nothing. Each of these was introduced deliberately, observed to fail, and reverted:
| Injected fault | Observed |
|---|---|
Naive SELECT-then-UPDATE claim |
2,560 jobs handed out for 1,000 rows — 1,560 duplicates, one row claimed 7 times, repeated deadlocks |
SKIP LOCKED removed from cron |
duplicate job materialized in the same minute |
| Attempt refund removed | 5 tests fail |
userId dropped from the access guard |
37 of 50 isolation tests fail |
| Route reclassified as user-scoped | classification check fails |
Router backdoor bypassing mountRoutes |
drift check fails |
--scale scheduler=3against a live* * * * *schedule for eight real minutes: exactly one job per minute, no duplicate minute- Worker SIGKILLed mid-execution: job recovered by the reaper with
attempt_refunded: true docker compose stop: all services drained and exited 0 in 0.7 s- Timezone:
0 9 * * 1-5inAsia/Kolkata→ 03:30 UTC, and real DST transitions in New York (23- and 25-hour days) and London
On the last point: the original test plan called for a DST boundary in Asia/Kolkata. India abolished DST in the 1940s — IST is a fixed UTC+05:30, so that test would have asserted nothing and passed forever. It was replaced with Kolkata's actual hazard, the half-hour offset that breaks integer-hour implementations, plus real transitions elsewhere.
| Feature | Status |
|---|---|
| Rate limiting | Built — auth and enqueue |
| Role-based access control | Built — 4 roles, middleware-enforced |
| Workflow dependencies | schema column present; promotion logic designed, not built |
| Queue sharding | designed — shard_id, workers claim a shard, concurrency divided; removes per-queue claim serialization |
| Distributed locking | pg_advisory_xact_lock for cross-job mutual exclusion; unnecessary because SKIP LOCKED already solves claiming |
| Event-driven execution | LISTEN/NOTIFY on insert to wake idle workers, cutting poll latency to near-zero with polling retained as the safety net |
| WebSocket live updates | Postgres LISTEN → API fanout → SPA; polling chosen for the reasons in §7 |
| AI failure summaries | error_fingerprint column and index shipped; clustering by normalized signature then LLM summary designed, not built |
Unbuilt items are designed rather than omitted. The brief prioritizes engineering quality over feature count, and a correct core with documented extension paths seemed the better answer than a wider surface with a weaker centre.
A predicted performance number that was wrong, and why. The design document predicted Seq Scan 240 ms → Index Scan 0.4 ms for the claim query. Measurement showed no improvement at all. The cause was in the design: a priority-aging term in the ORDER BY (priority + hours_waiting) is computed per row, so no B-tree can supply that ordering — every plan fetched all candidates into a Sort before LIMIT, meaning no early termination and therefore no sub-millisecond result. The claim was reshaped to a single queue with aging demoted to an opt-in flag, and the prediction became reachable: 0.110 ms.
A silent index rejection. After that change the plan still refused the index. Drizzle's .desc() emits DESC NULLS LAST; plain SQL DESC means NULLS FIRST. PostgreSQL will not use an index whose null placement differs from the requested ordering — even on a NOT NULL column where the difference is unobservable — and it says nothing. Pinning nullsFirst() took the claim from 4.887 ms scanning 1,740 rows to 0.230 ms scanning 33.
scheduled was a terminal state — and eight sessions of green gates missed it. Retry backoff writes status = 'scheduled', and the claim requires status = 'queued' (it must — the partial index is defined on exactly that predicate). Nothing implemented the transition between them, so a job that failed once was permanently invisible to every worker. The retry system was not working at all. The evidence had been sitting in the schema since the first session: idx_jobs_scheduled ON jobs (run_at) WHERE status = 'scheduled' — an index built for a reader that was never written. It survived because newly enqueued and cron-materialized jobs go straight to queued, so any demo where nothing fails looks healthy, and because the retry test performed the transition by hand while commented as merely skipping the backoff wait. Fixed with a promoter loop in the reaper using FOR UPDATE SKIP LOCKED; the 2,510-job backlog cleared in five passes. Queue depth is now reported as claimable and scheduled separately, because a large scheduled count is normal while claimable-nonzero with zero running is a real problem.
Two documented rules that contradicted each other. The design called for both refunding a crashed attempt and using UNIQUE (job_id, attempt) as the execution guard. These are incompatible: a refund reuses attempt numbers, so every post-crash retry violated the constraint. Resolved by separating identity from accounting — execution_seq for the constraint, attempt for the retry policy. The audit trail now records a SIGKILL as execution_seq 1 and 2 both charged to attempt 1, with row 1 still running because nobody finished it.
Prerequisites: Docker with Compose v2. Node 22 + pnpm 9+ only if running tests outside containers.
git clone https://github.com/PRCSM/codity-scheduler.git
cd codity-scheduler
cp .env.example .env # works as-is, no manual edits
docker compose up # postgres + api + worker + scheduler + web
pnpm db:demo # migrate, reset, seed, ensure nothing is pausedDashboard on http://localhost:5173, signing in as operator@codity.test / correct-horse-battery-staple.
Postgres is published on host port 55432, not 5432 — a host Postgres install wins the standard port over Docker's proxy and produces confusing authentication failures.
Demonstrating the reliability guarantees:
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 chargedpnpm test --concurrency=1 # 246 tests (use --concurrency=1 on low-memory machines)
pnpm db:explain # regenerate docs/EXPLAIN.md
docker compose up --scale worker=3 # concurrency demonstrationFull documentation lives in docs/ — ARCHITECTURE.md, DATABASE.md, CONCURRENCY.md, API_CONTRACT.md, SECURITY_GUIDELINES.md, TESTING_RULES.md, DESIGN_DECISIONS.md, EXPLAIN.md, and a session-by-session engineering log in PROGRESS.md.