ALLI-29238: creatives that no render can ever finish

PR: backend #706 · prevention half is #702 (ALLI-29130)

The ticket's own acceptance criterion was unsafe

ALLI-29238 asked reconcile to archive rows whose supplemental_feed_identifier is empty or whitespace. Measured against prod before writing any code:

blank-identifier rows globally: 2,326
  of which RENDERED and delivering: 2,109   <-- the literal AC would have deleted these
  of which never rendered:            217

They sit on Rosewood V3 Social, Retargeting Q4 2024, Sabrina Test, Hearst 1 and 2, NRG Reliant, GME. Rosewood was still producing them on 9/8. The supplemental_feed_identifier <> '' guard in archive_creatives_for_removed_identifiers is load-bearing, not an oversight. Juntao chose the alternative rule.

Why nothing cleaned these up

flowchart TD
  Z["one row from the 25-26 Aug renders<br/>identifier ' this experience delivers nonstop fun'<br/>never rendered, template file 8ecec117"]
  Z --> R{"cleanup 1: the pre-render reconcile"}
  R -->|"skips it: only archives rows that<br/>already rendered (ADR-008/ADR-013)"| S1["still there"]
  Z --> C{"cleanup 2: a fresh row takes its slot<br/>create_batch displacement"}
  C -->|"never fires: needs an incoming row in the<br/>same slot (variant, identifier, size) and no<br/>feed will emit that identifier again"| S2["still there"]
  Z --> N{"new sweep: delete_unrenderable_creatives"}
  N -->|"never rendered + file superseded +<br/>predates current file + older than 24h"| G["deleted: 11,412 on Viator,<br/>25,925 across 9 templates"]
  classDef bad fill:#ffd7d5,color:#24292f,stroke:#cf222e;
  classDef good fill:#d3f5db,color:#24292f,stroke:#1a7f37;
  classDef ctx fill:#eaeef2,color:#24292f,stroke:#57606a;
  class S1,S2 bad;
  class G good;
  class Z,R,C,N ctx;

Viator's current template file is e9054efd; the residue sits on 8ecec117, and template_file_id is the first component of _creative_hash, so those rows cannot collide with anything a future render produces. That is why deleting is safe where archiving is not: archiving would seed studio_archivedcreative, which the ADR-017 restore-on-return flow scans, with rows it can never legitimately bring back.

Evidence

Adversarial review before push

96 agents, 5 lenses, 3 refuters each. 30 raw findings, 0 survived. Three were refuted as defects but conceded accurate as prose, and all three were fixed before the push:

1. My docstring said create_batch displaces on the hash. It does not: _insert_from_temp keys the displacement DELETE on (variant_id, supplemental_feed_identifier, width, height) with no hash term. Conclusion held, stated reason was wrong. Rewritten.
2. The design doc said "all three hold" where the rule has four clauses.
3. The grace-period comment leaned on the stuck-render watchdog's 2h threshold as if it bounded render duration; it measures inactivity. Reworded to call the margin a margin, not a proof.

The critic's verdict was push-with-follow-up: no hard cap, chunking, or reversible first step needed, but gate the unscoped production run behind a per-template dry run.

Not done

The production cleanup has not been run. It needs #706 deployed first, then a --dry-run per template before the real pass.