Add media usage index write-side foundation

[khoinguyenpham04]

This Roadmap discussion mirrors #1746: Add media usage index write-side foundation.

Use this discussion to upvote the roadmap item and discuss priority, use cases, and product feedback. Keep implementation tracking, code review, and merge-specific feedback on the source pull request.

• Source pull request: #1746
• Status: Open
• Labels: area/core, overlap, size/XL, area/docs, cla: signed, review/needs-rereview
• Original author: khoinguyenpham04

Original Pull Request

What does this PR do?

Adds the write-side foundation for the Media Usage index. This includes the database tables/status metadata, content media reference extraction, content source snapshots for columns and draft_overlay, repository APIs, runtime refresh wiring for normal content lifecycle writes, stale marking for known bypass paths, and generation-guarded source replacement/deletion so refresh races preserve current usage and mark coverage stale instead of failing content writes.

This PR intentionally stops before repair/rebuild, the usage read API, admin Used In UI, safe delete, replace-in-place behavior, or media delete behavior changes.

Related discussion: #1503

Type of change

• Bug fix
• Feature (requires maintainer-approved Discussion)
• Refactor (no behavior change)
• Translation
• Documentation
• Performance improvement
• Tests
• Chore (dependencies, CI, tooling)

Checklist

• I have read CONTRIBUTING.md
• pnpm typecheck passes
• pnpm lint passes
• pnpm test passes (or targeted tests for my change)
• pnpm format has been run
• I have added/updated tests for my changes (if applicable)
• User-visible strings in the admin UI are wrapped for translation (if applicable). Do not include messages.po changes except in translation PRs — a workflow extracts catalogs on merge to main. N/A: no admin UI strings in this PR.
• I have added a changeset (if this PR changes a published package)
• New features link to an approved Discussion: Media usage index — power image sitemaps, "Used in", and safe deletes #1503

AI-generated code disclosure

• This PR includes AI-generated code — model/tool: OpenCode with GPT-5.5 (openai/gpt-5.5)

Screenshots / test output

Non-visual backend/indexing change.

Verified:

• pnpm lint
• pnpm typecheck
• pnpm format
• pnpm --filter emdash exec vitest run tests/integration/database/media-usage-status-migration.test.ts tests/integration/database/media-usage-content-fields.test.ts tests/integration/database/media-usage-repository.test.ts tests/integration/database/media-usage-migration.test.ts tests/integration/database/media-usage-content-snapshots.test.ts tests/integration/database/media-usage-content-refresh.test.ts tests/integration/database/media-usage-runtime-refresh.test.ts tests/integration/database/media-usage-stale-bypass.test.ts tests/unit/media/usage-extractor.test.ts tests/unit/media/usage-source-key.test.ts (10 files, 98 tests)
• git diff --check

---

Try this PR

Open a fresh playground →

A full working EmDash site, deployed from this branch. Each visit gets its own session-scoped sandbox: no login needed and no shared state. Try the admin, edit content, hit the public site.

_{Tracks feature/media-usage-index-core. Updated automatically when the playground redeploys.}