Motivation: complete ERD retention semantics for search-request scoped rows by purging context score tables together with expired search requests.
Constraints: retention behavior must remain in stored procedures and use constant error messages.
Decision:
Update job_run_retention_purge_v1 to delete canonical_torrent_source_context_score and canonical_torrent_best_source_context rows where context_key_type='search_request' and context_key_id belongs to purged requests.
Keep existing retention windows and table purges unchanged for outbound logs, RSS seen rows, conflicts, conflict audits, health events, and source reputation.
Add an integration test covering retention windows and search-request context cleanup in one execution path.
Alternatives considered: relying only on application-side cleanup (rejected: retention ownership is database-side) and leaving context rows durable (rejected: violates ERD retention rules).
Consequences:
Positive outcomes: search-request context score tables no longer retain stale rows after request retention purges; policy snapshot ref_count and policy-set cleanup remain coherent.
Risks or trade-offs: retention job touches two additional tables, increasing delete work during purge runs.
Follow-up:
Keep new search-request context rows scoped to context_key_type='search_request' so retention cleanup remains deterministic.
Validate future retention migrations against the ERD retention table list before release.
Test coverage summary:
Added job_run_retention_purge_applies_table_windows to verify old-vs-recent retention behavior across all configured operational tables plus search-request context score cleanup.
Observability updates:
None (database retention behavior change only).
Risk & rollback plan:
Roll back by reverting migration 0087_indexer_retention_purge_context_cleanup.sql.
Dependency rationale:
No new dependencies. Alternatives considered: not applicable.