Problem RestatementProblem
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.
API Design
CRUD: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} → 204POST /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.
ArchitectureArchitecture
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
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.
Data Model and Job StatesData model
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
);queued → running → succeeded | failed, with cancelling → cancelled if the user cancels. Workers only move forward (conditional updates on the current status).
Deep Dive — A job that outlives the request that started itDeep dive
An export runs for twenty minutes. The HTTP request that started it returned long ago, the worker may die, and the dataset it is reading can be deleted while it works.
Start a thread in the API process
The handler spawns a background thread and returns 202 Accepted.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
REQ["POST /datasets/7/exports"] --> TH["Thread in the API process"]
TH --> DEPLOY["Deploy or restart - process dies"]
DEPLOY --> GONE["Job vanishes, status stuck at 'running' forever"]
TH --> SCALE["Work lands wherever the request landed - no balancing"]
TH --> BLIND["No retry, no visibility, no cancel"]The job's lifetime is tied to a process that restarts on every deploy. There is no record to retry from, so the failure mode is a job that is permanently running and a user with no way to tell.
A queue and a worker pool
Persist a job row, push to a queue, let workers pick it up and update status. Deploys no longer kill work, and capacity is independent of the API tier.
The durable parts are right. What is undefined is everything about a worker that stops responding mid-job: the row says running with no way to tell a working job from a dead one, so either it is retried too eagerly and runs twice, or never and hangs forever.
Leases, idempotent outputs, and a stated policy for the resource
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
J["Job row - queued"] --> W["Worker: status running, lease_until now + 60 s"]
W --> HB["Heartbeat extends the lease"]
HB -->|"stops"| EXP["Lease expires - retried as attempt + 1"]
W --> OUT["Write to results/job_id/... - same key on every attempt"]
W --> CAN{"cancelling flag set?"}
CAN -->|"yes"| STOP["Stop between chunks, clean up, set cancelled"]
W --> VER["Job params record dataset version"]
DEL["Dataset deleted mid-job"] --> POL["Stated policy: block the delete, or fail the job"]- Leases plus heartbeats distinguish "working" from "dead". The lease is the liveness signal; the status column alone never can be.
- Result keys derived from
job_idmake a retry overwrite rather than duplicate, which is what makes at-least-once execution safe. - Cancellation is a flag, not a kill. The worker checks it between chunks, stops cleanly and tidies up. Killing a worker mid-write leaves partial output that looks like success.
- Say what happens when the resource is deleted while a job runs. Either block the delete until jobs finish, or let the job fail with "resource deleted" — both are defensible, and leaving it undecided means it is decided differently in each code path. Record the dataset
versionin the job params so a completed export states which version it came from.
Expose all of this through the job resource: GET /jobs/{id} returning status, attempt, progress and a terminal result or error. The API contract is what makes a long-running operation usable, not the worker.
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.
Wrap-UpWrap-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.