[Product Gap] Cancel durable ETL jobs with lease-fenced terminal state

[seonghobae]

Buyer-visible gap

Enterprise operators can submit, inspect, paginate, and poll durable ETL jobs, but they cannot stop work that is no longer wanted. A mistakenly submitted pending job can still execute, while an in-flight job can finish after an operator has decided that its target effects must not commit. Production queue products need cancellation semantics that are owner-safe, idempotent, race-safe, observable, and compatible with horizontally scaled workers.

This issue becomes implementation-ready only after PRs #121, #122, #129, #130, and #131 integrate in order. Do not create a parallel feature PR while that stack is open.

Proposed bounded vertical slice

Add one terminal CANCELLED lifecycle state and an authenticated owner-scoped cancellation action:

POST /api/etl/jobs/{job_record_id}/cancellation
Authorization: Basic <credential>
Idempotency-Key: "client cancellation key"

The endpoint should return the existing operator-safe job status representation with Cache-Control: no-store.

Pending job

• atomically transition PENDING to CANCELLED;
• clear request_payload in the same transition;
• persist a stable non-sensitive cancellation code and timestamp;
• never allow a later worker claim.

Running job

• atomically transition the exact currently stored RUNNING row to CANCELLED and clear lease fields and payload;
• do not require possession of the worker's private lease token at the HTTP boundary;
• make a concurrent worker's final exact-lease SUCCEEDED transition fail as stale;
• roll back target rows and response-ledger rows when cancellation commits before terminal success;
• if terminal success commits first, return a deterministic terminal-state conflict rather than pretending cancellation won.

Existing terminal state

• replay the same principal-scoped semantic cancellation request for CANCELLED without changing data;
• reject cancellation of SUCCEEDED or FAILED with stable RFC 9457 problem details and no internal text;
• preserve the same owner-safe 404 etl_job_not_found surface for malformed, absent, and foreign-owned identifiers.

Required state and persistence contract

• extend the lifecycle constraint to include CANCELLED;
• use descriptive multi-word snake_case objects and columns such as cancellation_requested_at or job_cancelled_at;
• require terminal rows, including CANCELLED, to have request_payload IS NULL and no lease fields;
• retain no raw principal or raw cancellation key;
• scope cancellation idempotency by authenticated-principal hash, job identifier, and normalized cancellation key;
• avoid a new unbounded free-text reason field in the first slice; use a stable code vocabulary;
• preserve standalone operation and modular MSA integration.

Concurrency invariants

Exactly one terminal outcome may win.

cancel commits first
→ worker exact-live-lease success update affects zero rows
→ worker transaction rolls back target + ledger effects
→ final state CANCELLED

success commits first
→ cancellation transition affects zero rows
→ endpoint returns stable terminal conflict
→ final state SUCCEEDED

The cancellation endpoint must never report success merely because an HTTP request was accepted. The database transition is the authority. The implementation should use one conditional data-modifying statement with RETURNING where practical, because PostgreSQL returns values only for rows actually modified; separate read-then-write logic must not become the cancellation authority.

Reality-based test requirements

Use the same database for job, target, and response-ledger effects and include deterministic tests for:

1. pending cancellation prevents later claim;
2. pending cancellation clears the payload atomically;
3. repeated same-key cancellation replays the same cancelled representation;
4. same key with a conflicting cancellation identity fails closed;
5. owner A cannot observe or cancel owner B's job;
6. running cancellation invalidates the active lease;
7. cancellation before markSucceeded rolls back processed_data and etl_idempotency_records;
8. success-before-cancellation returns the stable conflict and leaves SUCCEEDED unchanged;
9. two concurrent cancellation requests produce one authoritative transition and deterministic replay;
10. an expired or superseded worker cannot overwrite CANCELLED;
11. terminal metrics record exactly one finite-cardinality cancelled outcome;
12. no payload, principal, key, hash, lease identifier, SQL, exception text, or unbounded label enters logs or telemetry;
13. migration upgrade and rollback constraints are validated against PostgreSQL, not only H2 SQL compatibility;
14. every added production statement and branch is covered with no skipped project test;
15. every public production API has beginner-readable Javadoc.

API and error taxonomy

Suggested stable errors:

etl_job_cancellation_key_required
etl_job_cancellation_key_reused
etl_job_cancellation_in_progress
etl_job_already_succeeded
etl_job_already_failed
etl_job_not_found

Use RFC 9457 application/problem+json. RFC 9110 defines 409 Conflict for a request that cannot be completed because it conflicts with the target resource's current state, so it is appropriate when a terminal durable-job state conflicts with cancellation. Exact status and problem-type mappings must be fixed in the API contract and tests before implementation.

Operational evidence

Document:

• state-transition diagram and race outcomes;
• cancellation latency and database-lock behavior;
• how long-running execution continues computationally before the final stale-lease rollback;
• the first-slice limitation to transactional target effects;
• connector requirements before claiming cancellation safety for external non-transactional destinations;
• metrics, alerts, rollback, migration repair, and operator response;
• CHANGELOG.md entry and APA 7th references.

Explicit non-goals

• arbitrary process termination;
• lease renewal or heartbeat;
• priority queues;
• bulk cancellation;
• recurring schedules;
• manual replay or dead-letter requeue;
• external warehouse compensation transactions;
• cancellation UI.

These remain later slices. The first slice must establish a correct database-owned cancellation state and race contract.

Standards and primary documentation — APA 7th

Fielding, R., Nottingham, M., & Reschke, J. (2022). HTTP semantics (RFC 9110). RFC Editor. https://www.rfc-editor.org/rfc/rfc9110

Nottingham, M., Wilde, E., & Dalal, S. (2023). Problem details for HTTP APIs (RFC 9457). RFC Editor. https://www.rfc-editor.org/rfc/rfc9457

PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: Data consistency checks at the application level. https://www.postgresql.org/docs/18/applevel-consistency.html

PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: SELECT. https://www.postgresql.org/docs/18/sql-select.html

PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: UPDATE. https://www.postgresql.org/docs/18/sql-update.html