[Product Gap] Replay terminal durable jobs with immutable lineage

[seonghobae]

Buyer-visible gap

mightyETL can now accept, execute, discover, poll, conditionally validate, and cancel durable ETL jobs. Failed or deliberately cancelled work, however, cannot be safely retried by an operator after the terminal payload has been cleared. Enterprise operations need an explicit replay resource that creates new work, preserves the original terminal evidence, proves the exact payload being replayed, and never mutates history.

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

Proposed bounded vertical slice

Add an authenticated owner-scoped replay action:

POST /api/etl/jobs/{source_job_record_id}/replays
Authorization: Basic <credential>
Idempotency-Key: "new replay key"
Content-Type: application/json

[{"id":"record_alpha","name":"accepted"}]

The request supplies the complete candidate payload again because terminal source jobs retain only request_digest, not raw payload. The service must validate the bounded JSON through the existing intake contract and require its SHA-256 digest to equal the immutable source request_digest before creating any new job.

A successful first replay returns RFC 9110 202 Accepted, a new PENDING job resource, Location, Cache-Control: no-store, and Idempotency-Replayed: false. Repeating the same source, principal, semantic key, and byte-identical payload returns the same new job with Idempotency-Replayed: true.

Immutable lineage

The source job remains unchanged. The replay creates a new row with descriptive multi-word snake_case lineage fields:

replay_source_job_record_id
replay_root_job_record_id
replay_generation_count

Required invariants:

• replay_source_job_record_id identifies the immediate terminal source;
• replay_root_job_record_id identifies the first job in the lineage;
• a first replay has generation 1;
• a replay of a replay increments the generation exactly once;
• generation is bounded, initially 1 through 100;
• the root row has all replay fields null;
• a replay row has all replay fields non-null;
• source and root rows belong to the same principal scope;
• source and root references cannot point to the new row itself;
• a terminal source can never be rewritten to PENDING;
• the lineage can be exported as W3C PROV prov:wasDerivedFrom without changing the relational authority.

The first slice may use nullable self-referencing foreign keys only when migration and deletion/retention behavior are explicit. Silent cascade deletion of replay history is prohibited.

Replay eligibility

Allowed source states:

• FAILED;
• CANCELLED.

Rejected source states:

• PENDING and RUNNING: active work is not replayable;
• SUCCEEDED: replay could duplicate target effects and requires a separately authorized duplicate-processing workflow;
• missing, malformed, or foreign-owned identifiers: one owner-safe 404 etl_job_not_found surface.

Suggested stable errors:

etl_job_replay_key_required
etl_job_replay_payload_mismatch
etl_job_replay_key_reused
etl_job_replay_source_active
etl_job_replay_source_succeeded
etl_job_replay_generation_exhausted
etl_job_replay_in_progress
etl_job_not_found

Use RFC 9457 application/problem+json. Current-state conflicts should use RFC 9110 409 Conflict; payload mismatch and key reuse should use a fixed, tested 422 mapping.

Database authority and idempotency

Replay creation must be a single transactional new-resource operation. It must never be implemented as UPDATE source SET job_status='PENDING'.

Recommended authority:

1. authenticate and validate identifier, key, and bounded payload before table access;
2. hash principal, replay key, and exact payload;
3. owner-scope and lock the terminal source row;
4. verify source state and immutable request_digest;
5. derive root and bounded generation;
6. insert one new PENDING row with lineage through a deterministic unique replay identity;
7. use a unique constraint plus INSERT ... ON CONFLICT or an equivalent transaction lock to make concurrent identical requests converge on one created job;
8. classify a conflicting key/payload or source through fixed errors without exposing hashes or foreign existence.

PostgreSQL 18 documents that ON CONFLICT DO UPDATE provides an atomic insert-or-update outcome under concurrency and that RETURNING contains only rows actually inserted or updated. The first slice should prefer an insert-or-replay contract that does not update the immutable source or an already-created replay job.

Reality-based test requirements

Use the same database and transaction manager as production behavior and include deterministic tests for:

1. replaying a failed job creates a distinct pending job;
2. replaying a cancelled job creates a distinct pending job;
3. the source status, timestamps, failure/cancellation evidence, and terminal payload state remain unchanged;
4. supplied payload digest must equal the source digest before insertion;
5. same source + key + exact payload replays one created job;
6. same key with another source or payload fails closed;
7. foreign-owned and missing sources remain indistinguishable;
8. pending, running, and succeeded sources are rejected with stable errors;
9. replay-of-replay preserves root identity and increments generation;
10. generation 100 cannot produce generation 101;
11. two concurrent identical replay requests create one new row;
12. lineage never forms a self-reference or cycle;
13. the new job follows the normal claim, lease, retry, success, failure, cancellation, pagination, polling, and ETag contracts;
14. no raw principal, raw key, source payload, hash, lease identifier, SQL, exception text, or unbounded label enters responses or telemetry;
15. migration upgrade and rollback are exercised against PostgreSQL semantics, not only H2 compatibility;
16. every added production statement and branch is covered with no skipped project test;
17. every public production API has beginner-readable Javadoc.

API and representation boundary

The accepted representation may extend the existing job-accepted model with operator-safe lineage identifiers only when compatibility is preserved. Internal digests, keys, and hashes remain excluded.

A replay response is noncommittal about execution: 202 Accepted means a new job resource was durably accepted, not that the ETL effects succeeded. The new resource uses the ordinary status, pagination, cancellation, and conditional polling APIs.

Provenance and audit export

The relational model is authoritative. An optional JSON-LD export should map:

source job     → prov:Entity
replay action  → prov:Activity
new job        → prov:Entity
new job        → prov:wasDerivedFrom → source job
replay action  → prov:used → source job
new job        → prov:wasGeneratedBy → replay action

W3C PROV-O defines prov:Entity, prov:Activity, prov:Agent, prov:used, prov:wasGeneratedBy, and prov:wasDerivedFrom for interoperable provenance chains. Export is not a substitute for database constraints or owner authorization.

Operational evidence

Document:

• source immutability and replay lineage;
• payload re-supply and digest-verification behavior;
• concurrent replay convergence;
• lineage generation limits;
• retention and deletion behavior for self-references;
• replay rate limits and abuse controls;
• rollout, migration repair, rollback, and downgrade constraints;
• metrics and alerts without high-cardinality identifiers;
• connector caveats for targets that cannot provide idempotent or transactional effects;
• CHANGELOG.md entry and APA 7th references.

Explicit non-goals

• mutating a terminal row back to pending;
• replaying succeeded work;
• editing the source payload or digest;
• bulk replay;
• scheduled or recurring replay;
• automatic DLQ requeue policy;
• arbitrary connector compensation;
• a graphical dead-letter console;
• CloudEvents publication or webhook delivery.

Those remain later slices. This slice establishes one safe, auditable, operator-initiated replay resource.

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: INSERT. https://www.postgresql.org/docs/18/sql-insert.html

World Wide Web Consortium. (2013). PROV-O: The PROV ontology. https://www.w3.org/TR/prov-o/