Taxonomy-filtered listings seek a denormalized pivot instead of scanning the collection

[MA2153]

This is a proposed solution for #1834

Problem

A taxonomy-filtered collection listing (getEmDashCollection("posts", { where: { category }, orderBy, limit })) applies the taxonomy filter as a correlated EXISTS on SELECT * FROM ec_<collection> … ORDER BY … LIMIT ? (packages/core/src/loader.ts:938-954, 988-999). The pivot table content_taxonomies carries only (collection, entry_id, taxonomy_id), so the filter (deleted_at IS NULL, status, locale) and the ordering (published_at/created_at/updated_at) can only be evaluated on the ec_* row. The planner therefore drives the scan from the collection's order index and probes the taxonomy EXISTS per row.

On a stats-blind engine (SQLite/D1 never maintains sqlite_stat1), for a selective term the LIMIT never fills, so the query walks the entire collection. Measured on a real dataset: ~75,000 D1 rows read for a page returning one row (#1834). rows-read is the billed/throttled unit on D1.

The root cause is structural, not a planner-hinting problem: the pivot lacks the columns needed to filter and order without bouncing back to ec_*. The ec_* composite indexes name exactly which columns those are.

Approach

Denormalize the filter + sort columns from ec_* onto content_taxonomies, mirror ec_*'s composite sort indexes onto the pivot keyed by (taxonomy_id, collection, …), and restructure the taxonomy-filtered listing to drive from the pivot: seek the term, walk a sort-ordered pivot index, let LIMIT short-circuit, and touch ec_* only by primary key to hydrate the final page.

Why this needs no runtime cost model (unlike #1852): once the sort column lives in the pivot index in sort order, LIMIT short-circuits for a selective term (few rows exist) and a broad term (the limit fills immediately) alike. The index does the work the closed PR tried to do with cached selectivity counts.

Decisions

Question	Decision
Sort orders on the fast path	All three date sorts: published_at, created_at (loader default), updated_at.
Dialects	Unify — both SQLite/D1 and Postgres run the pivot-drive shape (the CTE below carries no json column, so the old EXISTS-vs-DISTINCT Postgres constraint no longer forces divergence).
Multi-term AND	Fixed in the same change (residual pivot-PK EXISTS per extra taxonomy).
Locale coverage	Full — both non-locale and locale-aware index variants ship in this spec.
deleted_at	In the index key, not a partial predicate — so the same indexes serve admin (trash + all-statuses), not only the public live path.
Admin reuse	The pivot-drive query builder is parameterized on the deleted_at predicate and status condition so ContentRepository can adopt it if admin gains taxonomy filtering. Wiring admin itself is out of scope (it has no taxonomy filter today).
Write-path sync	Synchronous re-stamp at the existing data-layer choke points (ContentRepository, TaxonomyRepository). No stray SQL.

---

Part 1 — Schema: denormalize onto content_taxonomies

Add seven columns, all nullable (backfilled by the migration, stamped going forward by Part 4). These are authoritative for which rows appear and in what order, so they must equal the ec_* row exactly at query time — synchronous re-stamp, not eventual (unlike _emdash_media_usage.content_status, which is maintained by an async snapshot path).

Column	Role
status	status condition
scheduled_at	status='scheduled' AND scheduled_at <= now
deleted_at	deleted_at IS NULL (public/admin-live) or IS NOT NULL (admin trash) — index key column
locale	locale = ? filter
published_at	ORDER BY
created_at	ORDER BY (loader default)
updated_at	ORDER BY

ContentTaxonomyTable in packages/core/src/database/types.ts gains these fields.

Part 2 — Indexes: mirror ec_*'s sort indexes onto the pivot

Six composite indexes. Seek prefix is (taxonomy_id, collection) — equality on both leading columns, so a term shared across collections reads only the queried collection's slice — then deleted_at, then (for the locale variants) locale, then the sort column DESC, then entry_id as tiebreaker. This mirrors ec_*'s (deleted_at, [locale,] <sort> DESC, id) shape with the pivot seek prefix prepended.

idx_content_taxonomies_pub      (taxonomy_id, collection, deleted_at,         published_at DESC, entry_id)
idx_content_taxonomies_crt      (taxonomy_id, collection, deleted_at,         created_at   DESC, entry_id)
idx_content_taxonomies_upd      (taxonomy_id, collection, deleted_at,         updated_at   DESC, entry_id)
idx_content_taxonomies_loc_pub  (taxonomy_id, collection, deleted_at, locale, published_at DESC, entry_id)
idx_content_taxonomies_loc_crt  (taxonomy_id, collection, deleted_at, locale, created_at   DESC, entry_id)
idx_content_taxonomies_loc_upd  (taxonomy_id, collection, deleted_at, locale, updated_at   DESC, entry_id)

• deleted_at is a key column, not a partial predicate. SQLite (and Postgres) can seek an indexed column for both IS NULL and IS NOT NULL, so one index set serves the public/admin live path (deleted_at IS NULL → equality-style seek on NULL, clean early-LIMIT) and the admin trash path (deleted_at IS NOT NULL → a bounded range: still seeks the term, temp-sorts the small trashed candidate set, no early-LIMIT but far better than a scan). A partial WHERE deleted_at IS NULL index would exclude trash entirely and lock these indexes to the public surface — which is why we keep the column in the key.
• Both non-locale and locale variants are needed because the loader's locale filter is optional (loader.ts:928, AND locale = ? only when a locale is passed). A locale-filtered query wants locale as an equality prefix before the sort column; a no-locale query needs the sort column immediately after deleted_at. Neither index serves both.
• status/scheduled_at stay residual (not in the index): the status condition is an OR, so it can't sit before the sort column without breaking sort order. The denormalized columns make it a pivot-local read, not an ec_* bounce, and — because status is a filter column rather than an index constraint — the same indexes serve the public published-or-scheduled condition and an admin all-statuses / draft-only condition. This mirrors ec_* exactly (it keeps a separate (deleted_at, status) index and excludes status from the sort indexes).
• Locale-variant priority note: ec_*'s migration 041 added locale variants for updated_at/created_at but not published_at, because it was scoped to the admin i18n content list (which sorts by edited/created). For taxonomy listings the dominant real-world shape is a public category page filtered to a locale and sorted by published_at, so loc_pub is included and is the most-used locale variant — the opposite of ec_*'s omission.
• Existing pivot indexes are left in place (PK (collection, entry_id, taxonomy_id), idx_content_taxonomies_term (taxonomy_id) from migration 048). The new composites lead with taxonomy_id and arguably supersede 048, but dropping it is out of scope (additive-only discipline; 048 was a deliberate restore for Migration 036 can permanently lose idx_content_taxonomies_term on a partial-apply retry (same class as #1665) #1701).

Part 3 — Query restructure (packages/core/src/loader.ts)

Drive from a pivot-only CTE that carries the sort column, then join ec_* by PK for hydration:

WITH picked AS (
  SELECT ct.entry_id, ct.published_at AS sortval          -- single group: covering, early-LIMIT
  FROM content_taxonomies ct
  WHERE ct.collection = ?
    AND ct.taxonomy_id IN (<resolved groups>)             -- exact terms; OR-within-taxonomy = >1 group
    AND <deleted-at predicate>                            -- IS NULL (public/admin-live) | IS NOT NULL (admin trash)
    AND <status condition>                                -- public: published/scheduled ; admin: any/all/draft
    AND ct.locale = ?                                     -- only when a locale filter is present
    -- multi-term AND: one residual EXISTS on the pivot PK per additional taxonomy (see below)
    AND <cursor cond on sortval, ct.entry_id>             -- keyset pagination
  -- GROUP BY ct.entry_id                                 -- ONLY when >1 group (dedup); sortval := MAX(...)
  ORDER BY sortval DESC, ct.entry_id DESC
  LIMIT ?
)
SELECT r.*, <folded terms>, <folded bylines>
FROM picked JOIN ec_<collection> r ON r.id = picked.entry_id
ORDER BY picked.sortval DESC, picked.entry_id DESC;

Single group (the reported bug + most traffic): no GROUP BY; the pivot index is covering for (entry_id, sortval) and gives clean early-LIMIT. Then a bounded PK join to ec_*.

Multiple groups (OR-within-a-taxonomy, ≥2 slugs): an entry tagged with two matched groups yields two pivot rows → fan-out. GROUP BY ct.entry_id restores semi-join semantics (one row per entry), with MAX(sortval) as the representative sort key. This gives up clean early-LIMIT (it aggregates all matching pivot rows before limiting) but stays bounded to tagged rows — still far cheaper than the full-collection scan. Because the driving CTE selects only scalar pivot columns, no DISTINCT/GROUP BY ever touches a json column, so both dialects run the identical shape — this is what makes "unify dialects" safe rather than risky (it sidesteps the original EXISTS-vs-DISTINCT Postgres constraint documented at loader.ts:917-922).

Multi-term AND (an entry must match a term in every requested taxonomy): drive picked from the first taxonomy's groups; each additional taxonomy becomes a residual clause inside picked's WHERE:

AND EXISTS (
  SELECT 1 FROM content_taxonomies ct2
  WHERE ct2.collection = ? AND ct2.entry_id = ct.entry_id
    AND ct2.taxonomy_id IN (<groups for that taxonomy>)
)

This hits the PK (collection, entry_id, taxonomy_id) — a cheap point lookup per candidate.

Cursor / pagination. buildCursorCondition keys on (sortval, entry_id) from the pivot CTE. LIMIT/OFFSET apply to picked.

Non-date orderBy. When orderBy is an arbitrary ec_* field (not one of the three denormalized date columns), the fast path still seeks the term via the pivot but sorts the joined candidate set (no pivot sort index applies). Bounded to tagged rows — worse than early-LIMIT for a broad term, but there is no index for arbitrary-field sorts anywhere (unfiltered listings temp-sort them too), and field-sorted taxonomy pages are rare.

Byline filters are untouched — they keep their existing EXISTS against _emdash_content_bylines (loader.ts:959-966); only the taxonomy path is restructured.

Caller-agnostic builder (admin reuse). Factor the picked-CTE construction into a helper parameterized on: the resolved group set, the sort column, the deleted_at predicate (IS NULL / IS NOT NULL), the status condition, the optional locale, and the cursor/limit. The public loader passes its published-or-scheduled + deleted_at IS NULL shape; ContentRepository can pass an all-statuses + deleted_at IS NOT NULL (trash) or IS NULL (live) shape without any schema change — the columns (Part 1) and indexes (Part 2) already cover it. Actually wiring admin's content list to filter by taxonomy is out of scope (it has no taxonomy filter today, content.ts:510-529); this change only ensures the substrate doesn't preclude it.

Part 4 — Write path: re-stamp in the data layer

The denormalized columns must equal the ec_* row synchronously on every mutation that moves them.

• ContentRepository (packages/core/src/database/repositories/content.ts) — update, delete (soft), restore, schedule, unschedule, and the scheduled-publish cron: after mutating the ec_* row, re-stamp every pivot row for that entry:
  UPDATE content_taxonomies SET status, scheduled_at, deleted_at, published_at, created_at, updated_at, locale = <new> WHERE collection = ? AND entry_id = ?.
• TaxonomyRepository (packages/core/src/database/repositories/taxonomy.ts) — attachToEntry, setTermsForEntry, copyEntryTerms: stamp the entry's current ec_* values into the new pivot rows at insert time.

Both are existing choke points; no state-changing SQL for content_taxonomies lives outside these repositories.

Part 5 — Migration 051_content_taxonomies_denorm.ts (forward-only)

1. ALTER TABLE content_taxonomies ADD COLUMN … × 7 (Part 1).
2. Create the six composite indexes (Part 2).
3. Backfill by looping _emdash_collections (migration 013's multi-table pattern): per collection slug, UPDATE content_taxonomies SET <cols> = (SELECT <cols> FROM ec_<slug> WHERE id = content_taxonomies.entry_id) WHERE collection = ?. Validate the slug (sql.ref) — never interpolate. New rows are stamped by Part 4 going forward.

Register in runner.ts (static import + getMigrations()).

Part 6 — Testing (TDD, describeEachDialect)

The invariant: the seek returns byte-identical rows to the old EXISTS.

1. Parity — seek result == old-shape result, across: selective term, broad term (~50% of collection), OR-within-taxonomy (≥2 slugs), multi-term AND, each of the three sort orders, per-locale, and per-status (a draft query returns drafts, a published query returns published). Both dialects.
   • Admin-shape parity — the caller-agnostic builder returns correct rows for deleted_at IS NOT NULL (trash) and an all-statuses condition, exercising the deleted_at-in-key path. (No admin route consumes this yet; the test targets the helper directly to keep the substrate honest.)
2. Plan (stats-blind SQLite) — EXPLAIN QUERY PLAN: a single-term query (selective and broad) uses a pivot sort index with LIMIT short-circuit and does not full-scan ec_*.
3. Re-stamp sync — after publish / schedule / soft-delete / restore / attach / detach, the entry's pivot columns match ec_*; a status change flips which rows the listing returns.
4. Pagination — keyset cursor over the pivot sort key returns correctly-ordered, non-overlapping pages.
5. Query-counts — pnpm query-counts snapshot reviewed; expected delta is the taxonomy-filtered routes only.

Cost & implications

Six composite indexes on the pivot is not free. Being explicit so a reviewer doesn't have to reverse-engineer the tradeoff:

• updated_at is the dominant write cost. ContentRepository.update stamps updated_at = now on every save (content.ts:595). Because updated_at is a sort key, each save re-stamps the entry's pivot rows and moves them within idx_content_taxonomies_upd and _loc_upd — ~2 index moves per pivot row per save (for an entry with K terms, ~2K). published_at/created_at change rarely, so their four indexes churn far less. Of the six, the two updated_at indexes carry the highest write cost for the lowest read value (updated_at is an admin-oriented sort, seldom a public category-page sort). They are included per the "all three sorts / not half-baked" decision; if write cost on edit-heavy sites proves painful, dropping upd/loc_upd (falling back to temp-sort of the bounded candidate set) is the first lever.
• Write amplification is multiplicative on bulk paths. Every content_taxonomies insert (WXR import wxr-taxonomies.ts, seed/apply.ts, copyEntryTerms on translation create) now maintains six b-trees. On D1, index maintenance counts toward rows-written (billed/throttled), so large imports get measurably heavier. This is the write-side cost of the read-side win.
• deleted_at in the key (vs. a partial index) adds little: trashed rows stay indexed (small size bump) and soft-delete/restore moves rows rather than removing/re-adding them. This is the price of serving the admin trash path from the same indexes — an intentional trade, not overhead.
• Storage. Six wide text-column indexes (ULID taxonomy_id/entry_id ≈ 26 chars, ISO timestamps ≈ 24 chars) over the full pivot — a few MB per large collection's worth. Real on D1's size budget, not alarming.
• Planner surface. On stats-blind D1 the index choice is driven by query shape (equality columns + ORDER BY tail match), so it is deterministic — but the Part 6 EXPLAIN QUERY PLAN tests must assert the right index per case (locale-filtered + published_at → loc_pub, not pub with a residual locale filter).
• Trash doesn't early-LIMIT. deleted_at IS NOT NULL is a range, so the sort column after it isn't globally ordered → SQLite temp-sorts the (bounded) trashed candidate set. Live content keeps the clean LIMIT short-circuit. Acceptable given trash volume and traffic.
• Migration weight. Building six composite indexes over a large existing content_taxonomies is O(n log n) ×6. Build them after the backfill UPDATE; on a big D1 the 051 migration runs longer than the schema change alone implies.

Follow-up (out of scope, flag for the PR)

idx_content_taxonomies_term (taxonomy_id) (migration 048) becomes redundant — all six new indexes lead with taxonomy_id and serve a bare taxonomy_id seek as a prefix. Keeping it means a seventh index churned on writes for no read benefit. Dropping it is a separate, additive-discipline scope call (048 was a deliberate restore for #1701), but it should be raised so the write cost isn't paid twice.

Backwards compatibility & changeset

Additive: new nullable pivot columns, new indexes, a forward-only backfill migration. No API surface change; identical result rows (seek and scan return the same rows — tested). Query results and ordering are unchanged; only the access path and D1 rows-read differ.

Changeset (present-tense, user-facing):

Fixes taxonomy-filtered collection listings reading the entire collection on SQLite/D1 when the term is selective. Such listings now seek the matching entries directly, cutting D1 rows-read from tens of thousands to the page size.

[ascorbic]

This is a good solution and we shoudl do it. Just a couple of things though: D1 doesn't support transactions, so the stamping is non-atomic. When querying we need to make sure that the queries also check the filter values on the ec_ rows as well as the pivot (i.e. it's in the outer query too). Inc;ude a test that e.g. sets publish on the pivot but soft-deletes the ec_ row, and makes sure the queries still filter it out.

Also don't include the updated_at index. As you say, it's the biggest cost but the smallest value. Not worth it.