CRUD APIs with Asynchronous Background Jobs

0) Problem Restatement

Design a backend service (asked at Databricks) that offers CRUD APIs (create, read, update, delete) for a resource, say datasets, plus long-running operations on those resources that can't finish within one HTTP request: e.g., "export this dataset to a file", "recompute statistics", "import 10 GB from a URL". Clients start a job, check progress, maybe cancel it, and get the result when it's done.

1) API Design

POST   /v1/datasets                 { name, schema }           → 201 { dataset }
GET    /v1/datasets/{id}                                       → 200 { dataset }
GET    /v1/datasets?cursor=&limit=                             → 200 { items, next_cursor }
PATCH  /v1/datasets/{id}            { name? }  If-Match: "v3"  → 200 { dataset } | 412 if stale
DELETE /v1/datasets/{id}                                       → 204

POST   /v1/datasets/{id}/exports    { format: "parquet" }  Idempotency-Key: k1
       → 202 Accepted, Location: /v1/jobs/{job_id}, body { job_id, status: "queued" }
GET    /v1/jobs/{job_id}            → { status, progress: 0.42, result_url?, error? }
POST   /v1/jobs/{job_id}/cancel     → 202

• 202 Accepted means "we've accepted the work, and it's not done yet". The job is its own resource that the client can poll.
• Idempotency-Key on job creation: if the client retries, return the same job, don't start a second one.
• Optional webhook callback when the job finishes, so clients don't have to poll.

2) Architecture

flowchart LR
    C["Client"] --> API["API service - CRUD + job creation"]
    API --> DB[("Postgres - datasets, jobs, outbox")]
    DB -->|"outbox relay"| Q[("Job queue")]
    Q --> W["Workers"]
    W --> DB
    W --> OS[("Object storage - results")]
    W -->|"done"| WH["Webhook sender"]
    C -->|"poll status"| API

• The API writes the job row and an outbox row in one transaction. A relay pushes it to the queue, so no job is created without being queued, or queued without existing.
• Workers pull jobs, update progress, and write results to object storage.

3) Data Model and Job States

CREATE TABLE datasets (id UUID PRIMARY KEY, name TEXT, schema JSONB, version INT, deleted_at TIMESTAMP);
CREATE TABLE jobs (
  job_id UUID PRIMARY KEY, type TEXT, resource_id UUID, params JSONB,
  status TEXT,          -- queued, running, succeeded, failed, cancelling, cancelled
  progress REAL, attempt INT, lease_until TIMESTAMP, result_url TEXT, error TEXT,
  idempotency_key TEXT UNIQUE, created_at TIMESTAMP, updated_at TIMESTAMP
);

4) Workers and Reliability

• Leases: a worker sets status=running, lease_until=now+60s and heartbeats. If it dies, the lease expires and the job is retried (attempt+1).
• Idempotent work: write results to results/{job_id}/..., so a retry overwrites instead of duplicating.
• Retries with backoff for transient errors, and failed with a clear error for permanent ones.
• Cancellation: set cancelling. The worker checks the flag between chunks, stops, cleans up, and sets cancelled.
• Consistency with the resource: if the dataset is deleted while an export runs, either block deletion until the job ends, or let the job fail with "resource deleted". Choose one and document it. Use a dataset version in job params so results say which version they came from.

5) Operational Concerns

• Per-tenant limits: max concurrent jobs per tenant, and queue priority for small jobs.
• Timeouts: max runtime per job type.
• Cleanup: result files expire (e.g., 7 days), and job records are kept for history.
• Observability: metrics on queue depth, job latency, and failure rate by type.

6) Wrap-Up

Expose standard REST CRUD with cursor pagination and ETag-based optimistic concurrency, and model long operations as job resources: POST returns 202 with a job ID (idempotent via Idempotency-Key), and clients poll GET /jobs/{id} or receive a webhook. Create job and outbox rows in one transaction, let leased workers with heartbeats process jobs idempotently with retries and cancellation, and define clearly how job state interacts with changes to the underlying resource.