Taxonomy-filtered listings: how should we cache the term→group resolution? (follow-up to #1962)

[MA2153]

Context: split-out follow-up from #1962

Background

#1962 restructures taxonomy-filtered collection listings to seek a denormalized content_taxonomies pivot (keyed by taxonomy_id = translation_group) instead of scanning ec_* with a correlated EXISTS. To seek the pivot by an explicit taxonomy_id = ? equality, the loader must first turn the public where: { <taxonomy>: <slug> } filter into literal translation_group values. That's resolveTermGroups():

SELECT DISTINCT "translation_group" FROM "taxonomies" WHERE "name" = ? AND "slug" IN (...)

This query is structurally required and can't be inlined: on a stats-blind planner (D1 always; cold SQLite) an inlined taxonomy_id IN (SELECT …) is treated as multi-valued and forces USE TEMP B-TREE FOR ORDER BY, defeating the early-LIMIT seek and re-reading the whole term (verified via EXPLAIN QUERY PLAN). So the literal values must be resolved up front.

It's cheap in rows read — it rides idx_taxonomies_name_locale and touches only one taxonomy's terms. But it's a redundant round-trip: resolveTermGroups is a bare .execute() that goes through neither cache layer (requestCached L1, cachedQuery/object-cache L2). Every neighbouring taxonomy read is L2-shielded on the warm path, so this one query is the lone exception that hits the DB on every request, cold and warm — including production with KV configured. In the query-count snapshot it shows as +1 on both cold and warm for /category/* and /tag/*.

#1962 keeps this +1 as-is (it's correct and justified); this Discussion is about how best to reclaim the warm cost.

What doesn't work: reuse getTaxonomyTerms

The obvious move — resolve the slugs in memory from the already-cached getTaxonomyTerms(name, { locale }) — regresses localized filtering. getTaxonomyTerms gates its terms on getTaxonomyDef(name, { locale }), which returns null when the taxonomy def isn't translated to the requested locale. The default category/tag defs (migration 006) are seeded in the base locale only, so getTaxonomyTerms("category", { locale: "fr" }) returns [] even when fr term rows exist — the filter then resolves to nothing.

Confirmed by failing tests, including the explicit guard for #1480 ("resolve a taxonomy filter by the localized term slug").

Beyond the def gate, getTaxonomyTerms is simply the wrong shape for this: it also loads visible counts and, for hierarchical taxonomies, sorts the rows and assembles them into a nested tree (buildTree). Group resolution wants a flat slug → translation_group lookup — so we'd be paying for counts and tree construction only to walk the tree back down to find matching slugs. So getTaxonomyTerms isn't a clean reuse vehicle.

The two viable options — maintainer's call

A. Dedicated cached term-row accessor. A small helper that caches the raw (slug → translation_group) rows for a (name, locale) under the TAXONOMIES namespace only — no def gate, no counts, no tree. Resolve slugs in memory against it.

• One coarse value per (name, locale); one KV read; invalidates only on taxonomy structure writes (rare).
• Preserves exact current behaviour (incl. Collection where taxonomy filter joins on t.id (term id) instead of translation_group, so it only matches default-locale term slugs #1480); warm → baseline.
• Cost: introduces a new cached value/key. Justified here by correctness (the def gate), not by invalidation tuning — but it is additional cache surface.

B. Factor the structure half out of getTaxonomyTerms. Split its term-load (no def gate, no counts, no tree) into a shared cached primitive that both getTaxonomyTerms (counts + tree composed on top) and the resolver consume.

• No parallel cache; the whole taxonomy read path shares one structure value.
• Cleanest long-term and reduces duplication.
• Cost: touches a hot, widely-used path — larger blast radius.

Recommendation

Ship #1962 with the justified +1. For the follow-up, A is the minimal correct fix; B is the cleaner refactor if we're willing to touch getTaxonomyTerms. Both keep one coarse value (KV is billed per read — no per-term/per-combo keys).

Question for maintainers: A (small dedicated accessor now) or B (refactor getTaxonomyTerms to share a structure primitive)?