content_taxonomies links content and terms with inconsistent locale semantics (term side locale-agnostic, entry side per-locale → tag drift across translations)

[MA2153]

Description

content_taxonomies links the two ends of a content↔term assignment with inconsistent locale semantics, and the asymmetry causes per-locale tag drift.

The junction row is (collection, entry_id, taxonomy_id):

• taxonomy_id is locale-agnostic. Since migration 036_i18n_menus_and_taxonomies, it stores the term's translation_group (not a taxonomies row id). The old FK was deliberately dropped so one junction row covers every locale translation of the term. Reads confirm it — TaxonomyRepository joins taxonomies.translation_group = content_taxonomies.taxonomy_id.
• entry_id is per-locale. It stores a single content row's id. Because content tables are row-per-locale (migration 019_i18n), each translation of a post is a distinct row with its own id, so a junction row attaches a term to one locale's entry only, not to the content translation_group.

So a content_taxonomies row links one locale's entry to a locale-collapsed term concept. The two ends disagree on what "the same thing across locales" means.

Why this is a bug (observable consequence): term assignments are only synced across content translations once, at creation time, via TaxonomyRepository.copyEntryTerms ("Used when creating a translation of a post so the new translation inherits the source's term assignments"). It's a one-time snapshot. After that the locale entries drift:

1. Create an English post, tag it News. Create its Arabic translation → it inherits News (copy at creation). Good so far.
2. Later, edit the English post and add Sports. The Arabic translation does not receive Sports — its entry_id differs and nothing re-syncs.
3. Symmetrically, removing a tag from one locale leaves it on the sibling locale.

The result is that "the same content in another language" can carry a divergent set of terms with no UI/runtime affordance reconciling them, even though the term side was specifically designed to be locale-agnostic. Either both ends should be group-keyed (assignments shared across the content translation_group), or the divergence should be an explicit, surfaced feature rather than silent drift from a creation-time copy.

This is a design/consistency bug rather than data loss — flagging for a decision on intended semantics.

Steps to reproduce

1. Enable a second locale (e.g. ar) and create a posts collection with a taxonomy (e.g. Category).
2. Create an English post, assign term News.
3. Create an Arabic translation of that post (inherits News via copyEntryTerms).
4. Edit the English post; add term Sports and save.
5. Fetch terms for the Arabic translation (getTermsForEntry(collection, arEntryId)).
6. Observed: Arabic translation still has only News; Sports never propagated.
   Expected (or to be decided): assignments stay consistent across translations of the same content, mirroring the locale-agnostic term side — or per-locale divergence is an explicit, documented capability.

Environment

• emdash version: main (current packages/core)
• Runtime: Node + SQLite and Cloudflare D1 (schema-level, dialect-independent)
• Relevant code:
  • packages/core/src/database/migrations/036_i18n_menus_and_taxonomies.ts (taxonomy_id → translation_group)
  • packages/core/src/database/repositories/taxonomy.ts (getTermsForEntry, setTermsForEntry, copyEntryTerms)
  • packages/core/src/database/migrations/019_i18n.ts (row-per-locale content)

Logs / error output

N/A — behavioral/design inconsistency, no error thrown.

[emdashbot]

The investigation bot reproduced this issue.

Root cause (high confidence): packages/core/src/database/repositories/taxonomy.ts:222–360 — the content↔term junction keys the entry side by per-locale content row id while the term side stores the term's translation_group.

• attachToEntry / detachFromEntry / setTermsForEntry / clearEntryTerms (lines 222–264, 279–326, 329–337) write rows in content_taxonomies using the raw entryId argument. They never resolve it to the content translation_group and never fan out to sibling-locale entries.
• getTermsForEntry (lines 268–276) joins taxonomies.translation_group = content_taxonomies.taxonomy_id (locale-spanning on the term side) but filters content_taxonomies.entry_id = <one row id> (locale-pinned).
• copyEntryTerms (lines 343–360) is a one-shot snapshot. Its only production caller is packages/core/src/api/handlers/content.ts:599, inside the create-translation branch (if (body.translationOf)). Nothing replays it on later edits.

Mechanism against the reproducing test: after step 3 there are pivots (post, enEntry.id, newsGroup) and the AR copy (post, arEntry.id, newsGroup). Step 4 hits setTermsForEntry from packages/core/src/astro/routes/api/content/[collection]/[id]/terms/[taxonomy].ts:140 with canonicalId = enEntry.id; it writes (post, enEntry.id, sportsGroup) only. Step 5's read against arEntry.id returns just News. No path joins ec_<collection>.translation_group on the entry side, so sibling-locale rows are invisible to both writes and reads.

This is the asymmetry the issue calls out: migration 036 made the term side locale-collapsed; migration 019 left the entry side per-locale; no code reconciles them. A maintainer decision is required on which side moves.
Proposed fix: The issue asks for a semantics call. Options:

A. Make entry_id store a content translation_group (full symmetry). Forward-only migration per ec_<collection>: rewrite content_taxonomies.entry_id from per-locale row id to the matching ec_<collection>.translation_group; collapse duplicates from copyEntryTerms snapshots. In TaxonomyRepository, resolve entryId → translation_group (one SELECT against the content table) before every read/write. getTermsForEntry becomes a 3-way join applying the entry's locale on the term side. Delete copyEntryTerms and its caller in api/handlers/content.ts:599. Pro: completes the migration-036 design; drift impossible. Con: non-trivial data migration; entry_id semantics change.

B. Keep entry_id per-locale; fan writes across sibling entries. In attachToEntry / detachFromEntry / setTermsForEntry / clearEntryTerms, resolve the entry's content translation_group, list siblings (SELECT id FROM ec_<collection> WHERE translation_group = ? AND deleted_at IS NULL), apply the write to each. Reads unchanged. One-time backfill (or lazy reconciliation on next write) for existing rows. Pro: no schema migration; localised to TaxonomyRepository. Con: N writes per change; term sets become globally shared across locales — admin UI must reflect that.

C. Make per-locale divergence explicit. Drop the misleading copyEntryTerms snapshot, document that term sets are per translation, require explicit assignment in each locale. Pro: smallest change. Con: contradicts migration-036's "one junction row covers every locale translation".

Recommendation: Option A — only path that finishes the migration-036 redesign self-consistently. Option B is the cheaper middle ground if a pivot-table migration is undesirable. The reproducing test packages/core/tests/integration/taxonomies/taxonomy-locale-drift.test.ts is the gate for A/B; C would invert it.
Alternative causes considered: Alternative causes ruled out:

• getTermsForEntry locale filter hides an existing row. The reproducing test confirms the AR pivot for sportsGroup is absent, not filtered; getTermsForEntry without a locale filter against arEntry.id still returns only News.
• Missing repeat call of copyEntryTerms on update. Grep shows exactly one production caller (api/handlers/content.ts:599, create-translation only) plus the wxr-import path. No history of an update-time call; absent by design, not a regression.
• canonicalId resolution in the terms route writes to the wrong id. terms/[taxonomy].ts:105–140 resolves canonicalId from existingItem.id correctly. The EN write is fine; the missing fan-out is the issue.
• Migration 036 backfill leftover. Reproducing test creates fresh data post-migration, so any backfill artefact is unrelated.
  Verdict: bug — The documented intent in docs/src/content/docs/guides/internationalization.mdx:131-150 is explicit and contradicts the current behaviour:

"The pivot content_taxonomies.taxonomy_id stores the term's translation_group, so a single assignment spans every locale of the content."

"Translating a piece of content automatically inherits the source's term assignments — you only need to translate the terms themselves once, and every post that uses them resolves to the right locale at read time."

A single assignment does not span every locale of the content. It spans every locale of the term but is pinned to one locale of the content via entry_id — diagnose confirmed packages/core/src/database/repositories/taxonomy.ts:222–337 never resolves entryId to the content translation_group. The second sentence frames term assignments as a property of "a piece of content" (its translation group), but copyEntryTerms (taxonomy.ts:343–360) is called only once at translation creation (api/handlers/content.ts:599) and never replayed. Migration 036's own docstring (migrations/036_i18n_menus_and_taxonomies.ts:8-12) describes storing the term translation_group so "one junction row covers every locale translation" — the cross-locale symmetry was clearly intended; only half of it shipped.

The existing test packages/core/tests/integration/taxonomies/taxonomy-locale-terms.test.ts (for #1218) asserts the locale filter on the term side only; it says nothing about sibling-locale entry drift, so it does not endorse the current behaviour. No comment, doc, or test in the repo describes the per-locale divergence as intentional. Reporter's expectation matches the published docs; the implementation does not. As diagnose noted, the fix still needs a maintainer choice between options A and B (both honour the documented intent), but the behaviour itself is a bug.
The fix needs a design decision a maintainer should make, so the bot did not attempt it automatically. The proposed options are above.

A maintainer will pick up the fix from here.

_{Run: https://github.com/emdash-cms/emdash/actions/runs/27008389360}

[ascorbic]

I'm inclined to say that taxonomies should be linked to the group rather than the entry. This needs a content migration, but I think it's the most logical solution. Do you agree?

[MA2153]

Yes