Entity table keyed by product id, derived from the sitemap: make a slug change a retarget instead of a suppressed render

## Proposal

Keep an entity table keyed by the **stable product id embedded in the URL**, derived from the sitemap, and use sitemap membership transitions (additions, removals) as the signal that a product needs checking. The change probe would then sweep that table rather than the whole target registry.

## What the data says

Measured 2026-09-16 against a deployment whose product sitemap is 17 children of exactly 50,000 entries (the 17th partial):

| | |
| --- | --- |
| total product URLs | 847,238 |
| distinct product ids | **847,236** |
| URLs not matching the product-id pattern | **0** |
| ids appearing under more than one slug in the same snapshot | **2** |

The id is effectively a perfect key, it is present on every entry, and it is extractable from the path with no extra origin cost — the sitemap walk already has the URL in hand.

## What it actually buys, in order

**1. Product identity, which `Target` structurally cannot express.** `Target` is keyed by URL. When a product's slug changes, the old URL and the new URL are two unrelated rows: the new one is CREATEd, the old one departs, renders once more, declares a canonical elsewhere and is suppressed. Measured on one node over 22.8h, `render/outcome/suppressed/canonical-mismatch` was **12,618** (~50k/day fleet-wide). #158's follow-up 4 already identifies a slug change as the suspected cause and wants it to become a retarget rather than a render. An id-keyed index is the missing piece: the sitemap walk sees "id X, new URL" and can move the target instead of discovering one and suppressing the other.

**2. It de-conflates "product gone" from "slug changed".** This matters now, because #164's departure check cannot tell them apart. A slug change presents as a departure of the old URL, so the check hard-expires its pages and spends a render to learn what the sitemap already stated. The behaviour is not wrong — the old URL's page *should* stop serving — but it is avoidable work, and the volume is whatever the slug-churn rate turns out to be.

**3. Membership history, which today is a single bit with no memory.** `Target.sitemapUrl` is null-or-not; there is no record of when a product left, whether it came back, or how often it flips. Per #158, sold-out products leave the sitemap the same day, so those transitions are an availability signal we currently observe once and discard.

**4. A smaller probe sweep.** Real, but the weakest leg — see below.

## Where I'd push back

**"The probe can just scan the products table" has a residency hazard.** The probe sweep is owner-scoped and `ProbeState` is node-local, both derived from the *target's* residency, which is computed from the URL. A table keyed by product id would place the row for product P on whichever node owns the *id*, which is not the node that owns P's URL. That breaks the "each node sweeps only what it owns" invariant, and an unowned point read on a residency-pinned table takes Harper's replication fetch, which has no timeout. Either the products table is residency-pinned by its current URL so ownership matches, or the probe keeps walking targets and the products table is used for identity only. This needs deciding before anything is built.

The scan saving is also smaller than it looks: the probe's dominant cost is one origin read per product per pass, not the walk.

**"The sitemap is the source of truth for active products" needs to be said more precisely.** Per #158, membership tracks *availability*, not *existence* — sold-out products leave the same day and come back. So a row must not be deleted when it leaves; it needs `lastListedAt` / `firstSeenAt` and stays, which is also what makes "came back" detectable at all. Naming the field `active` would guarantee every later reader misreads it.

**A second registry needs to justify itself against this codebase's own precedent.** The `NonIndexable` table was deliberately removed in favour of state on `Target` — "one row answers both questions the old shape needed two tables for". The justification here is that this table is keyed *differently*: it is an index over the corpus by product id, not a second copy of it. That should be explicit in the design — `Target` stays authoritative for rendering, the products table is derived, and where they disagree `Target` wins.

## Sketch

- Per-route config for extracting an entity key from the path (a pattern with one capture group), in the same spirit as `changeProbe.rules` and `departureAction`: the mechanism is generic, the pattern is per deployment.
- The sitemap walk populates it, since it already parses every entry. Additions = an id not seen before. Removals = an id whose URL departed and was not re-attached (the post-walk check from #164 already computes exactly this set). Slug change = a known id at a new URL — which the walk can see *before* the prune, turning a departure into a retarget.
- Row: `id` (PK), `url`, `sitemapUrl`, `firstSeenAt`, `lastListedAt`, `leftListingAt`, and whatever the probe wants to hang off it.

## Open, and worth measuring before building

- **How much slug churn is there per day?** This is the number that sizes benefit #1 and #2, and nobody has it. Within a single snapshot the id→URL mapping is 1:1 (2 exceptions in 847k), which says nothing about day-over-day. The direct measurement is to diff two consecutive days' sitemaps by id — cheap, needs only a stored snapshot.
- **What fraction of `canonical-mismatch` suppressions are slug changes?** Attribution is currently assumed, not verified. #158 records ~153k/day; the measurement above came out ~50k/day, so the figure has moved (possibly the 2026-08-13 cache-key change) and is worth re-establishing before it justifies work.
- **Measuring accumulated duplicates is currently blocked**: `GET /prerender_admin/pages?prefix=…` returns 504 on a deep prefix on this corpus, so "how many slugs do we hold pages for under one product id" cannot be answered from the console today. That is its own small gap.

## Relationship to existing work

Subsumes #158 follow-up 4 (canonical slug as a signal) and generalizes follow-up 2 (sitemap-membership availability, built in #164). Should not be folded into #158, which is already carrying five follow-ups.

Start by reading the Target and ProbeState ownership model, the sitemap walk, and the departure check from #164. Measure day-over-day sitemap changes by product id and attribute canonical-mismatch suppressions before choosing whether residency follows the current URL. Done means the design resolves residency, availability history, retargeting, and the role of the derived table before implementation begins.

Scritto dal modello di indicizzazione a partire dal testo della issue.