Redshift gate follow-ups to #1832: adoption window for settled or retry-refused failed rows, scanner targets, status table #1839

Summary

Follow-up to #1832 (#702 PR3b), to land in a small PR right after #1832 merges. These are verified review findings left out of #1832 so its approvals stand.

1. A settled failed row is excluded from the adoption-window check. The C9 failed clause (internal/config/store_postgres_redshift_lineage.go, redshiftFailedClause) excludes outcome_settled_at IS NOT NULL. PR2b's recent-buy branch covers only finished statuses. So a failed row that did buy and was settled within minutes is invisible to a fresh same-tuple purchase dispatched inside the adoption window, and go can adopt its node: an under-buy, with one node counted twice.
2. Failed rows whose Retry the remaining guard refuses are dropped by dropPersistentFailures (internal/purchase/redshift_lineage.go, retryRefusedForRecs). That is right for the 24h horizon, because such a row can never re-drive. But a row of that kind that may have bought (failed without no_submission) can still be adopted by a fresh purchase inside the adoption window.
3. The writer scan over-counts. It counts idempotency_key anywhere in UPDATE or upsert SET text as a write, including a predicate inside a SET subquery (internal/purchase/idempotency_key_writers_test.go). A valid UPDATE would then fail the test. This fails closed: it could never miss a real writer.
4. (darling, Low) The inside-settle integration case covers only completed. Make it a table over completed, partially_completed and revocation_requested, so that dropping either of the last two fails a test.

Expected behaviour

• (1, 2) Any other-family row that may have bought, meaning failed without the no-submission proof (settled or not, and whether or not Retry is refused), or finished, conflicts while its updated_at is within the adoption window of the dispatch's stamp.
• (3) The scan counts only assignment targets.
• (4) Each finished status is pinned.

Proposed fix

• (1, 2) Extend PR2b's adoption-window branch (the other-family finished clause on updated_at >= stamp - window) to failed rows with NOT no_submission, regardless of settlement or retry-refusal. Then have dropPersistentFailures keep a candidate when it falls inside the adoption window. Integration subtests: a settled failed row inside the window conflicts; a mixed EC2+Redshift failed row inside the window conflicts; both pass outside it.
• (3) Parse the SET list into assignment targets at depth 0, and match idempotency_key only as a target. Add fixtures for a predicate-only reference, both in a subquery and in WHERE.
• (4) Turn the subtest into a table over the three statuses.

References

• fix(purchase): Redshift reserved-node purchases without tag-based idempotency #702, fix(purchase): allow Redshift re-drive behind the retry, lineage and journal gates #1832; review threads on fix(purchase): allow Redshift re-drive behind the retry, lineage and journal gates #1832 (CodeRabbit); darling's review of cd8c36a.

Severity

Medium for (1, 2): under-buy, in narrow windows only. Low for (3, 4).

🤖 Generated with Claude Code