tabdeal-job-queue/README.md

# Job Queue

This is a deliberately small PostgreSQL-backed job queue for the interview assignment.

The important parts are:

- one internal priority queue
- two tables: `jobs` and `job_events`
- atomic claiming with PostgreSQL row locks and `SKIP LOCKED`
- deterministic status transitions
- automatic retries with exponential backoff
- lease renewal for long-running jobs
- lease timeout cleanup when workers die
- idempotent job creation
- demo UI with job state and event polling

## Architecture

![prompt for generating an svg image for a minimal PostgreSQL-backed job queue architecture showing React UI talking to Django API, Django API using PostgreSQL, and a separate Django worker process with N configured threads claiming jobs from PostgreSQL with SKIP LOCKED; use clean interview-project style, simple labeled boxes, directional arrows, and callouts for two tables jobs and job_events](assets/images/project-architecture.png)


There is no queue table and no worker table. Workers are ephemeral process threads with generated ids. The queue is internal and ordered by:

```text
priority DESC, available_at ASC, created_at ASC, id ASC
```

## Statuses

![prompt for generating an svg image for the job status state machine with four states queued, running, succeeded, failed; arrows queued to running, running to succeeded, running to queued for retry after failure or timeout, running to failed when attempts are exhausted, and failed to queued for manual retry; use clear color coding and small labels on each transition](assets/images/job-status-state-machine.png)

The database also validates row shape:

- queued jobs cannot have locks or finish timestamps
- running jobs must have a lock owner and lease deadline
- terminal jobs must have a finish timestamp and no lock

## At-Least-Once Execution

![prompt for generating an svg image for at-least-once job execution failure recovery showing worker A claims attempt 1, worker A crashes or lease expires, cleanup requeues the job, worker B claims attempt 2, and stale worker A cannot complete attempt 1 because ownership no longer matches; use a horizontal timeline with worker lanes and database state callouts](assets/images/at-least-once-lease-recovery.png)

This queue provides at-least-once execution, not exactly-once execution.

A worker can perform an external side effect and crash before marking a job succeeded. The lease will expire and the job can run again. Real handlers should therefore be idempotent.

## Why PostgreSQL

The assignment requires PostgreSQL, and PostgreSQL gives a compact solution for safe concurrent claiming through `SELECT ... FOR UPDATE SKIP LOCKED`. This keeps the implementation transactional, inspectable, and easy to demo.

For a high-throughput distributed production queue, Redis-backed systems such as BullMQ or Sidekiq-style designs are common. That is documented as the next architecture, not implemented here.

## Assumptions And Interview Simplifications

This project is intentionally scoped as an internal single-queue system, not a multi-tenant queue platform. There is no queue CRUD, queue table, or dynamic routing model because the assignment focuses on safe claim semantics and deterministic job state.

PostgreSQL is used because the assignment requires it and because it makes transactional state easy to inspect during a demo. Redis, BullMQ, Sidekiq-style designs, or a dedicated broker would be better for very high throughput or broader distributed queue use cases.

The system provides at-least-once execution, not exactly-once execution. Handlers that perform external side effects must be idempotent because a worker can crash after the side effect and before marking the job succeeded.

The UI is demo and observability oriented. It shows queue pressure, job state, retries, leases, and event logs, but it is not a full production operator console.

Authentication and authorization are intentionally omitted from the public API for interview simplicity. A production deployment would protect all write endpoints and usually restrict operator actions by role.

## Production Follow-Ups

- Add authentication, authorization, and role-based permissions.
- Add metrics, alerting, and dashboards for queue depth, age, throughput, failures, and retry rate.
- Add event retention, archival, or partitioning so `JobEvent` does not grow forever.
- Add rate limits, producer quotas, and backpressure controls.
- Add cancellation or cooperative stop support for jobs.
- Add dead-letter metadata or a dead-letter inspection view.
- Consider Redis or a dedicated broker if throughput or cross-service distribution becomes the main requirement.

## Run

```powershell
copy .env.sample .env
npm.cmd --prefix frontend install
npm.cmd --prefix frontend run build
docker compose up --build
```

Open:

```text
http://localhost:5173
```

API docs:

```text
http://localhost:8000/api/docs/
```

Admin panel:

```text
http://localhost:8000/admin/
```

Create an admin user:

```powershell
docker compose exec backend python manage.py createsuperuser
```

Worker logs default to `INFO`. To see every poll, claim, lease renewal, progress update, retry, and completion, set:

```env
JOB_WORKER_LOG_LEVEL=DEBUG
```

Then recreate the worker:

```powershell
docker compose up -d --build worker
docker compose logs -f worker
```

## Useful Commands

Run backend tests locally with SQLite fallback:

```powershell
$env:TEST_DATABASE_ENGINE="sqlite"
python manage.py test
```

Run pytest:

```powershell
$env:TEST_DATABASE_ENGINE="sqlite"
python -m pytest
```

Run pytest with coverage:

```powershell
$env:TEST_DATABASE_ENGINE="sqlite"
python -m pytest --cov --cov-report=term-missing
```

Run worker locally:

```powershell
python manage.py run_job_workers
```

## References

- PostgreSQL `SKIP LOCKED`: https://www.postgresql.org/docs/current/sql-select.html
- pg-boss: https://github.com/timgit/pg-boss
- Solid Queue: https://github.com/rails/solid_queue
- BullMQ concurrency: https://docs.bullmq.io/guide/workers/concurrency
- Distributed task queue article: https://medium.com/@sindhukripa007/i-built-a-distributed-task-queue-from-scratch-to-actually-understand-how-they-work-37fa0452ff9b