Prevent re-running a pipeline with the same settings on the same file (run dedup)

[martsokha]

Problem

A caller can POST .../pipelines/{pipelineSlug}/runs/ with the same fileId (and same/absent scope) repeatedly and get N duplicate runs. The only dedup today is an optional client-supplied Idempotency-Key header (find_pipeline_run_by_idempotency_key); with no key, nothing prevents re-analyzing identical inputs. We want: you should not be able to re-run a pipeline with the same settings on the same file.

Two adjacent asks came up together:

• Dedup the run at create time.
• Find runs by file (and pipeline) — currently the run-list endpoints filter only by status; there is no ?fileId= filter.

Blocker discovered

The effective scope is not persisted on the run. ScopeParams travels on the ephemeral DetectionJob (not written to the DB) and falls back to the pipeline defaultScope. So a pipeline + file + scope dedup cannot be evaluated against existing rows without a schema change — there is nothing to compare scope against.

Design decisions already made (this discussion)

• Duplicate key: pipeline + file + scope (the request's effective ScopeParams). A different scope on the same file is a different run.
• Block set: any non-failed run blocks (queued/analyzing/analyzed/completed); failed/cancelled do not block (retry allowed after failure).
• Duplicate response: plain 409 Conflict, usual error shape (no special run-id embedding in the error).
• Discoverability: instead of pointing at the existing run via the error, add a find-by-file filter (?fileId=, optionally ?pipelineId=) to the run-list endpoints so the client can look up the existing run itself.

Open question — needs a design decision (no legacy constraints)

What is the right identity model for "the same work"? Options investigated:

1. Request-time dedup by (pipeline, file, scope) + partial unique index.
   Persist the effective scope (new scope JSONB column, backfill existing rows to {}), and add a unique index over (pipeline_id, input_file_id, scope) excluding failed/cancelled so a retry after failure is allowed. Simple, faithful to "same settings", but "settings" here is only scope — it ignores pipeline definition/policy changes between runs.

2. Idempotency fingerprint column = hash of the effective config (scope + pipeline definition + resolved policy set + file version/hash). Store fingerprint and a partial unique index excluding failed/cancelled. Re-running after the pipeline config or file changes is naturally allowed (different fingerprint); identical work is rejected. More robust than (1); needs a stable canonical hash of the config.

3. Content-addressed result cache keyed by (file_content_hash, pipeline_config_hash). A re-run becomes a cheap lookup that returns the cached analysis instead of recomputing. Strongest for cost/correctness, biggest change (decouples "result" from "run").

Tradeoffs to weigh

• Staleness when config changes — (1) blocks even after the pipeline definition/policies change (stale); (2)/(3) re-run when config changes (correct).
• File re-versioning — a new file version should be a new run; only (2)/(3) capture this if the file hash/version is in the key.
• Partial-fail retries — the block set must exclude failed/cancelled; a partial unique index is the standard pattern.
• Concurrent submits — the uniqueness must be enforced at the DB (unique index), not just a pre-check, to avoid a race between two POSTs. This composes with the existing claim_run_for_detection lease.

Prior art (to ground the choice)

• Idempotency keys (Stripe / IETF idempotency-key draft): client key or request-hash, TTL'd, dedup at the edge.
• Workflow engines: Temporal workflow-id + reject-duplicate policy; GitHub Actions concurrency groups + cancel-in-progress; Airflow dagrun uniqueness by (dag_id, execution_date).
• Memoized computation: Bazel/Nix/Turborepo content-addressed action caches key results by hash of (inputs + config) and skip identical work — closest analogue to option (3).

Proposed scope for the implementation PR (once the identity model is chosen)

• Persist the effective scope (and/or a config fingerprint) on workspace_pipeline_runs.
• Partial unique index enforcing the chosen dedup key, excluding failed/cancelled.
• create_pipeline_run: pre-check + rely on the unique index for the race; return 409 on conflict.
• Add fileId (and optional pipelineId) filter to cursor_list_workspace_runs / cursor_list_workspace_pipeline_runs and the WorkspaceRunsQuery DTO → "find runs by file".

Not doing yet

Per discussion, hold implementation until the identity model (option 1/2/3) is decided.