Media tagging

In-app tagging of media assets (#377, part of the media-management capability #376). Tagging organizes the library by product so an admin can find imagery among the tens of thousands of assets the Drupal sync (#365) can bring in — it is the spine the scalable picker (#375) filters on.

The model

media_tags(id, media_asset_id → media_assets.id (cascade), tag_type, tag_ref, source)
  UNIQUE (media_asset_id, tag_type, tag_ref)

media_tags is the single live store (#1805). Every live classification tag — whatever its origin — is a media_tags row; source records provenance: human (an admin tagged it), dump (seeded by a db-dump load, #1097), or ai (promoted from an accepted AI proposal at the accept boundary, see below). The Classification tab badges each tag's source, with ai in the accent tone.

• Polymorphic, like media_links. tag_type starts at system and extends (attribute, factor, …) without a schema change. Because tag_ref is polymorphic it carries no database foreign key — the same trade-off media_links makes for entity_id.
• Uniqueness — a given (tag_type, tag_ref) tags an asset at most once.
• Cascade on the asset — deleting a media asset removes its tags. Deleting the tagged entity does not (systems are soft-deleted, never removed).

Referential integrity (app-enforced)

Since tag_ref has no DB foreign key, integrity is enforced in the domain layer (src/lib/server/admin/media-tags.ts):

• addMediaTag validates both ends exist — the media asset and the referenced entity (for system, a systems.id) — before it writes. A bad id is returned as a problems[] (a clean 400 at the endpoint), never an FK-violation 500, and never a dangling tag.
• Systems are soft-deleted (retiredAt), so a tag never dangles from a deletion. A future hard-delete or id-rename must rewrite media_tags in step, the same way the [#147] rename actions rewrite media_links.
• Writes are idempotent: addMediaTag uses onConflictDoNothing; removal of an absent tag is a no-op.

The vocabulary (tag_type)

The axes (#1112, #1279), each resolving its tag_ref against a governing store:

tag_type	tag_ref	Governing store
system	systems.id	systems
project	drupal_projects.uuid	drupal_projects
attribute	attributes.id	attributes (whole facet)
factor	factors.id	factors (whole facet)
application	an enum value	attribute_enum_values of the applications attribute
factor_option	factor_options.id	factor_options (leaf choice)
attribute_value	${attributeId}:${value}	attribute_enum_values (leaf value)

Leaf vs parent (#1279). factor_option / attribute_value name the choice or value an image depicts (Folding, Aluminum), not just the whole facet. Leaf-preferred, parent-allowed: the whole-facet factor / attribute axes stay for imagery about a facet as a whole (a comparison/diagram with no single value). There is no auto-backfill — an existing bare-facet tag's depicted value can't be inferred, so it is left as-is.

Enum-only scope for attribute_value. Only attribute_enum_values is a discrete-value catalog; range/tier/boolean/dimension attributes have no per-value target (matching the illustration limit, #1244). The ref is the ${attributeId}:${value} composite (enum values have no surrogate id).

One ref contract with illustration (#1244). The two leaf axes reuse the illustration layer's targetExists / parseAttributeValueRef (illustrations.ts) — the table-driven TAG_GOVERNANCE can't express the composite — so a factor_option / attribute_value ref validates identically whether it is a classification tag or an illustration target. A curated leaf depicts tag surfaces on the illustration page as an illustration candidate for the same target (illustrationCandidatesForTarget); the intents stay distinct (factual depicts vs chosen illustrates), and it is never auto-assigned.

Rename propagation (#1279 AC-05). factor / attribute / factor_option refs are immutable surrogate ids, so a display-label edit needs no propagation. The attribute_value composite embeds the value text, so renameEnumValue rewrites the composite in media_tags, media_visual_tags, andmedia_illustrations in step — classification and illustration never drift from the renamed catalog value.

Extending tag_type

Adding a further taggable type is a two-line change — no migration:

1. Add the literal to MEDIA_TAG_TYPES in media-tags.ts.
2. Add its existence check to the per-type resolver (tagRefExists) so addMediaTag can validate the new tag_ref kind (for a composite ref, delegate to a shared validator as the leaf axes do).

The unique index, the endpoint, and the read arms (tagsForAsset, assetSourcesForTag) are already type-agnostic. Multi-select falls out of the unique index: an asset may carry many leaf tags across facets and more than one option within one factor (scrubber / compare shots).

Read arms (what the picker uses)

Function	Purpose
tagsForAsset(db, assetId)	Every tag on one asset (the admin card).
tagsForAssets(db, assetIds)	Batched tags for a page of assets.
assetSourcesForTag(db, tagType, tagRef)	{id, source} rows carrying a tag — the filter arm, with provenance for consumers that rank human-curated over AI (media retrieval, illustration ranking).

Accepting an AI proposal promotes it (#1805)

The AI review layer (media_visual_tags, #1113) is purely the **proposal inbox

• rejection memory** — it holds no live tags:

• Accept promotes the proposal to a real media_tags row (source='ai') via addMediaTag — governing-store validation still gates the write — and removes the proposal. The audit trail records the accepting admin, the timestamp, and the promoted tag identity. The tag then appears on the Classification tab with an ai badge, exactly like any other live tag.
• Reject marks the row rejected/human so the analysis pass won't re-propose the same (asset, tag) — unchanged.
• Bulk accept batches carry promoted-tag snapshots; undo deletes a promoted row only while it is still source='ai' (a re-decided tag is never clobbered) and re-seeds the proposal with its original score, model, and fingerprint.
• Backfill: migration 0083_promote_accepted_visual_tags.sql converted the rows accepted before #1805 the same way, idempotently, preserving each original accepted-by/accepted-at in the audit trail.

Consumers no longer union two stores — retrieval and illustration ranking read assetSourcesForTag and rank human/dump as curated over ai.

AI proposals never duplicate classification tags (#1781)

Classification tags (media_tags, provenance human/dump) and AI visual-tag proposals (media_visual_tags) are deduplicated in the classification tags' favor, on every axis:

• Proposal generation (the visual-analysis pass and the shot-group run-on-cover path) skips any (tagType, tagRef) the asset already carries as a classification tag, and deletes a lingering proposed duplicate on the next analysis of that asset.
• The AI review surfaces hide a proposed row that duplicates a classification tag, so an operator never sees the duplicate even before a pass has converged. rejected rows keep their history.
• Accepting a proposal whose tag has since become a classification tag is a clean no-op: the promotion insert conflicts away (the first write's provenance wins) and the redundant proposal is removed instead of minting a visible duplicate.

Admin surface

The media library screen (/admin/media) renders each asset's system tags as removable pills and offers a labeled system <select> + Add. Mutations go through POST /admin/media/tag (edit_data guard, { op, assetId, tagType, tagRef }), which echoes the asset's refreshed tags. The control is keyboard-operable, with aria-labeled remove buttons and a :focus-visible ring.

Seeds

seed/sql/120-media-tags.sql tags several seed-media-* assets with a system — including a tagged-but-unused asset — so the picker's tag filter and usage filter can be exercised together. It is a dev-lifecycle fragment (local only, applied by dev-up), not a catalog fragment: it references dev-only seed-media-* assets, so it stays out of catalog-fragments.txt (same as 110-media.sql).