perf(core): reduce public render query count for menu resolution and page metadata paths

[ahliweb]

Summary

Public menu-heavy routes still show a relatively high query count on render even after downstream template duplication is removed.

The most obvious hotspot is packages/core/src/menus/index.ts, where menu item URLs are resolved per item via resolveContentUrl() / resolveTaxonomyUrl().

Context

This was observed while validating AWCMS-Micro against upstream EmDash.

Downstream-only cleanup has already been done:

• removed incorrect public links that used id instead of slug
• enabled locale-prefixed downstream routing workarounds where needed
• added public link prefetching
• parallelized homepage collection fetches
• removed duplicate homepage menu renders
• removed duplicate footer widget usage on non-detail public pages

Those changes improved correctness and reduced some duplicate downstream work, but they did not materially remove the homepage query-count ceiling.

Evidence

Representative observations from live validation after downstream cleanup:

• / commonly reports db.count=23
• /id commonly reports db.count=23
• /posts commonly reports db.count=13
• /news commonly reports db.count=13
• /about commonly reports db.count=4

The remaining gap strongly suggests a core-owned menu/runtime path rather than a downstream template issue.

Suspected Cause

Current menu flow appears to be:

1. getMenu() fetches the menu row
2. it fetches menu items
3. it fetches collection URL patterns
4. buildMenuTree() resolves each item individually
5. resolveMenuItem() calls resolveContentUrl() / resolveTaxonomyUrl() per item

This means menu-heavy pages can still pay effectively per-item lookup cost, including locale fallback lookups.

Proposed Fix Direction

1. Batch-resolve content references per collection

• collect all referenced content groups by collection first
• fetch requested-locale rows in one query per collection
• fetch fallback rows for unresolved groups in one follow-up query per collection
• build an in-memory lookup map
• make per-item resolution read from the map rather than query again

2. Batch-resolve taxonomy references

• collect referenced taxonomy groups first
• resolve requested-locale rows in one query
• resolve fallback rows for misses in one follow-up query
• build an in-memory taxonomy lookup map

3. Re-profile metadata/fragment collection afterward

EmDashHead.astro already parallelizes site settings, plugin metadata, and fragments, so the menu path looks like the clearest first target.

Acceptance Criteria

• fewer queries on menu-heavy public routes
• no regression in locale-aware menu URLs
• no regression in translation fallback behavior
• no regression for custom URLs, collection items, or taxonomy items
• request-cache behavior remains intact

Suggested Validation

1. Add/update a test around getMenu() with multiple page/post/taxonomy references across locales.
2. Measure query count before/after on a menu-heavy public route.
3. Confirm locale-specific rows are preferred and deterministic fallback still works when a translation is missing.

Related Context

• Discussion about admin/sidebar grouping direction: Proposal: Admin sidebar collection grouping and plugin admin page grouping #1151
• This issue is intentionally narrower and is about public-render query performance in core.

[swissky]

Triage: verified on current main — the analysis holds and nothing has landed that changes it. buildMenuTree still fans out with Promise.all(items.map(resolveMenuItem)) (packages/core/src/menus/index.ts), and each item pays:

• resolveContentUrl: up to 3 sequential queries (requested locale → any-locale fallback → legacy id fallback)
• resolveTaxonomyUrl: same 3-step ladder against taxonomies

So a 10-item menu is 10–30 queries in the worst case, on every route that renders it. requestCached only helps when the same menu renders twice in one request; the URL-pattern lookup is already batched, the item resolution isn't.

The proposed fix direction matches how I'd do it, with one simplification: instead of "requested-locale query + follow-up fallback query" per collection, a single WHERE translation_group IN (...) per referenced collection fetching all locales, then picking requested-locale-else-min-locale in memory, gets it to ~1 query per collection (+1 legacy-id sweep only if there are misses) and keeps the fallback deterministic. Taxonomies collapse the same way into one query.

Two things a PR should carry:

1. A getMenu() test with mixed page/post/taxonomy references across locales asserting both the URLs and (via the counting-dialect test util) the query count.
2. A pnpm query-counts snapshot diff — the repo tracks per-route counts in scripts/query-counts.snapshot.*.json, so the improvement shows up reviewably in CI.

Since this touches locale fallback and the legacy-id path, it's a perf refactor rather than a small fix — flagging for maintainer priority-labeling, but the issue is real and well-scoped.