Retry failed runs as fresh runs with explicit restart lineage

[jumski]

Summary

Add a small SQL API that retries a failed pgflow run as a new execution. The new run must use the same flow and input, receive a new run_id, and record the failed source run through a nullable restart_of_run_id relationship.

This is a full retry: it reuses no step or task execution state. Task-level continuation belongs in a separate issue.

User report or idea

A failed run should be startable again without resurrecting or mutating the terminal run. The operation should:

• create a new run rather than reuse the failed run_id;
• clone the source run's flow_slug and input exactly;
• record the immediate source run in metadata;
• execute through the normal run-start path;
• leave the source run unchanged.

The lineage column must be named restart_of_run_id, not parent_run_id. parent_run_id is reserved for a planned subflow model where a task starts a child run and completes when that child run completes. A retried child run may eventually need both relationships:

parent_run_id      = outer workflow run
restart_of_run_id  = failed child run

restart_of_run_id denotes the immediate prior execution, not necessarily the root of a retry chain. Names such as source_run_id and origin_run_id were rejected because they do not distinguish direct predecessor lineage from root ancestry.

A future subflow design may also link the parent step_tasks row to its exact child run, for example through child_run_id. That proposal is outside this issue.

Evidence supplied

No runtime logs or external reproduction were supplied. The design came from inspection of the current schema source:

pkgs/core/schemas/0060_tables_runtime.sql
pkgs/core/schemas/0100_function_start_flow.sql
pkgs/core/schemas/0100_function_start_ready_steps.sql
pkgs/core/schemas/0100_function_complete_task.sql
pkgs/core/schemas/0100_function_fail_task.sql
pkgs/core/schemas/0100_function_maybe_complete_run.sql
pkgs/core/schemas/0105_function_get_run_with_states.sql
pkgs/core/schemas/0110_function_start_flow_with_states.sql

Current run creation entry point:

create or replace function pgflow.start_flow(
  flow_slug TEXT,
  input JSONB,
  run_id UUID default null
)
returns setof PGFLOW.RUNS

Current pgflow.runs has no retry or restart lineage column.

Investigation and findings

pgflow.start_flow already owns the complete bootstrap transaction. It validates root-map input, inserts the runs row, inserts every step_states row, emits run:started, and invokes this sequence:

cascade_resolve_conditions
cascade_complete_taskless_steps
start_ready_steps
maybe_complete_run

Reusing this function avoids duplicating step initialization, condition handling, taskless-map completion, queue message generation, events, and completion checks.

A failed run is intentionally terminal in the current implementation:

• start_ready_steps returns without mutation for failed or completed runs;
• complete_task does not advance a failed run;
• fail_task disallows further retries after the run fails;
• run-failure paths archive active PGMQ messages.

Restarting the same row would require clearing terminal timestamps, rebuilding messages, resetting task attempts and errors, restoring counters, and bypassing those guards. A new run matches the existing state model and keeps the failed execution available for audit.

Embedding restart metadata inside runs.input is not safe. It would mutate user-owned data, could collide with user keys, and would break root-map flows whose input must remain a JSON array.

No existing restart, retry, resume, or run-parent relationship was found in pkgs/core/schemas/.

Proposed solution or design

Data model

Add a nullable self-reference to pgflow.runs:

restart_of_run_id uuid references pgflow.runs(run_id)

Consider an index on restart_of_run_id for direct lineage queries. Do not add parent_run_id for this feature.

Extend normal run creation

Add a fourth optional argument to pgflow.start_flow:

restart_of_run_id uuid default null

Include it in the pgflow.runs insert and the run:started payload. Existing positional calls remain valid because the new argument has a default.

Add the retry API

Add a function such as:

pgflow.retry_run(run_id uuid)
returns setof pgflow.runs

It should:

1. Read the source run.
2. Raise a clear error when it does not exist.
3. Raise a clear error unless its status is failed.
4. Call pgflow.start_flow with the source flow_slug, unchanged input, a generated run ID, and restart_of_run_id set to the source run.
5. Return the newly created run.

The source run and all source step/task rows remain unchanged.

Tests and migration

Develop from pkgs/core/schemas/ first with focused pgTAP tests. Generate the migration from schema source through Atlas after focused and full tests pass.

Likely test location:

pkgs/core/supabase/tests/retry_run/

Acceptance criteria

• pgflow.runs can record an immediate restart predecessor through nullable restart_of_run_id.
• pgflow.retry_run accepts a failed source run and returns a different run_id.
• The new run has the same flow_slug and byte-equivalent JSONB input as the source.
• The new run sets restart_of_run_id to the source run.
• The source run, step states, tasks, outputs, errors, counters, and timestamps remain unchanged.
• The new run follows the normal start_flow bootstrap, including root-map validation, condition resolution, taskless completion, initial task queueing, events, and immediate completion checks.
• A missing source run raises a clear error.
• A source run whose status is not failed raises a clear error.
• Existing start_flow and start_flow_with_states callers continue to work without supplying restart metadata.
• Restart lineage does not use or introduce parent_run_id.
• Focused pgTAP tests, full pgTAP tests, migration checks, and generated-type checks pass.

Related work

• Failed runs leave unfinished sibling tasks queued or started instead of cancelled #645 — terminalizes unfinished task rows on failed runs. Retry creation must not depend on stale active task statuses or mutate the source run.
• A separate task-level resume issue will reuse successful tasks and continue only unfinished task indexes. It builds on the same run-lineage model but has different behavior and implementation risk.
• Planned subflows may use parent_run_id for structural nesting and may link a parent task to its child run. That work is outside this issue.

Open questions and risks

• Decide whether direct lineage queries need an index on restart_of_run_id in the first migration.
• The SQL function return shape is proposed as setof pgflow.runs to match start_flow; no retry_run_with_states wrapper is proposed.
• Retry chains record immediate predecessors. Root ancestry remains derivable by traversing restart_of_run_id.