Content Items Cleanup Plan

Content Items Cleanup Plan

Version: 1.0 Date: 2026-05-06 Author: Claude Code (S225+) Status: DRAFT — Phase A ready to dispatch; Phases B–D verified, awaiting ratification. Scope: four-phase cleanup of content_items covering AI-telemetry backfill, MCP provenance gate, schema cleanup, and AI call-log sidecar.

Read order. §0 Background and current state. §1 Phase A (WP-B Phase 1 backfill — supersedes parts of docs/specs/ai-telemetry-instrumentation-spec.md §6.1.1 and §6.4 because typed ingest_source is now canonical). §2–4 Phases B–D, each with its own verification report and ratification gate. §5 cross-cuts (reference-doc edits, runbook updates, tests). §6 sequencing and effort.

---

0. Background

The user identified that content_items has accumulated 73 columns with overlapping semantics and several columns that are blank in production despite the schema declaring them. The original entry point was WP-B Phase 0c (per docs/specs/ai-telemetry-instrumentation-spec.md §6.7); investigation revealed three independent issues:

1. WP-B Phase 0c is essentially closed. The triage CSV scripts/output/wp-b-provenance-triage-2026-04-28.csv was Phase 0a output for the legacy JSONB key metadata.ingestion_source. The typed ingest_source column (added by supabase/migrations/20260428174512_add_ingest_source_to_content_items.sql) has since been backfilled by supabase/migrations/20260428180945_backfill_ingest_source.sql and …20260428235042_refine_ingest_source_backfill_residuals.sql.

2. WP-B Phase 1 (telemetry backfill) is the actual outstanding work. classification_model and embedding_model are 617/617 NULL in prod.

3. The deeper problem is column sprawl. 73 columns; multiple legacy/dead candidates; an MCP write path that bypasses provenance; and a fully-designed source_documents table with zero rows because no canonical upload path is wired.

0.1 Verified current state (prod rovrymhhffssilaftdwd, 2026-05-06)

Signal	Value	Source
content_items rows	617	live query
ingest_source typed col NULL	0/617	live query
ingest_source distinct values	qa_import (440) · manual (71) · url_import (55) · rss_feed (28) · python_markdown (23)	live query
metadata.ingestion_source JSONB key set	23 (python_markdown only)	live query
classification_model NULL	617/617	live query
embedding_model NULL	617/617	live query
source_documents rows	0	live query
source_document_id populated	0/617	live query
source_document_id FK constraint	absent	pg_constraint
file_path, source_document (text), source_bid populated	0/617 each	live query
embedding populated	591/617 (26 NULL)	live query

0.2 Verified writers per ingest_source value

Confirmed by grep across app/, lib/, scripts/:

ingest_source (typed)	Writer	Pipeline	Classify model
qa_import	scripts/import_bid_library.py:293 (also :722, :846)	Python	claude-opus-4-6
manual	app/api/items/route.ts:139 (default branch)	TS web form	claude-sonnet-4-6
url_import	app/api/ingest/url/route.ts:191	TS route	claude-sonnet-4-6
rss_feed	lib/intelligence/pipeline.ts:654	TS intelligence	claude-sonnet-4-6
python_markdown	scripts/ingest_markdown.py (writes typed via kb_pipeline/store.py:118-143)	Python	claude-opus-4-6
mcp_create	lib/mcp/tools/content.ts:504	TS MCP	claude-sonnet-4-6
python_url	scripts/kb_pipeline/pipeline.py:223	Python cron	claude-opus-4-6
upload, upload_autosplit, bid_outcome_integration, batch_reclassify	various	TS	claude-sonnet-4-6

Note: python_url and url_import are distinct typed values (TS route = url_import; Python cron = python_url). The original spec’s §6.1.1 marked url_import as ambiguous; the typed enum eliminates that ambiguity.

---

1. Phase A — WP-B Phase 1 backfill

1.1 Goal

Populate classification_model and embedding_model on the 617 historical rows using pipeline-of-origin attribution from the typed ingest_source column. Token columns left NULL per ratified accept-gap recommendation (ai-telemetry-instrumentation-spec.md §6.3).

1.2 Scope

In:

1. New script scripts/backfill-ai-telemetry.ts (mirrors scripts/backfill-classify-content-items.ts shape).
2. Updated lookup table reading the typed ingest_source column (not the legacy JSONB key).
3. Per-column idempotent UPDATE with metadata.telemetry_source='backfill' tagging.
4. pipeline_runs row recorded via recordPipelineRun() with pipeline_name='ai_telemetry_backfill', status='success', result.cost=0.
5. Sanity checks post-run (NULL counts → 0 for rows with classified_at NOT NULL / embedding NOT NULL).
6. Spec amendment: §6.1.1 lookup table updated to read typed ingest_source.

Out (deferred to Phase D / WP-B1 instrumentation):

• Per-call token capture for new ingest (covered by ratified ai-telemetry-instrumentation-plan.md Phase 1+2 — instrumentation at all four AI call sites).
• ai_call_log table (Phase D below).
• Removing the metadata.ingestion_source legacy writes from scripts/ingest_markdown.py (Phase C cleanup).

1.3 Updated lookup table — typed ingest_source (supersedes spec §6.1.1)

This table replaces the spec’s metadata.ingestion_source-keyed table. Source of truth: typed column content_items.ingest_source.

ingest_source value	classification_model	embedding_model	Pipeline	Note
qa_import	claude-opus-4-6	text-embedding-3-large	Python	Bid library importer
manual	claude-sonnet-4-6	text-embedding-3-large	TS web form	app/api/items/route.ts
url_import	claude-sonnet-4-6	text-embedding-3-large	TS route	app/api/ingest/url/route.ts
python_url	claude-opus-4-6	text-embedding-3-large	Python cron	scripts/kb_pipeline/pipeline.py
rss_feed	claude-sonnet-4-6	text-embedding-3-large	TS intelligence	lib/intelligence/pipeline.ts
python_markdown	claude-opus-4-6	text-embedding-3-large	Python	scripts/ingest_markdown.py
upload	claude-sonnet-4-6	text-embedding-3-large	TS upload	app/api/upload/route.ts
upload_autosplit	claude-sonnet-4-6	text-embedding-3-large	TS batch	app/api/items/batch/route.ts
mcp_create	claude-sonnet-4-6	text-embedding-3-large	TS MCP	lib/mcp/tools/content.ts
bid_outcome_integration	claude-sonnet-4-6	text-embedding-3-large	TS	bid-derived
batch_reclassify	claude-sonnet-4-6	text-embedding-3-large	TS	reclassification path
(unknown — not in enum above)	leave NULL; log + emit triage row	NULL	n/a	should not occur on prod 617 rows

TS-side defaults grounding: lib/anthropic.ts:18 returns process.env.AI_SUMMARY_MODEL || 'claude-sonnet-4-6'. The model recorded is the env-default at the time of ingest. Spec §6.1.1 already calls out the historical-attribution risk if AI_SUMMARY_MODEL was overridden via Vercel env; current observed value is claude-sonnet-4-6 per git log -- lib/anthropic.ts (no overrides since Phase 6B).

1.4 Acceptance criteria

• A1.1 Backfill writes classification_model for all rows where classification_model IS NULL AND classified_at IS NOT NULL.
• A1.2 Backfill writes embedding_model = 'text-embedding-3-large' for all rows where embedding_model IS NULL AND embedding IS NOT NULL.
• A1.3 Token columns left NULL.
• A1.4 Each backfilled row receives metadata.telemetry_source='backfill' via JSONB || merge in the same UPDATE.
• A1.5 Per-column idempotent: re-runs only update columns that are still NULL.
• A1.6 --dry-run prints candidate count, first-5 example assignments, emits no writes.
• A1.7 --limit N (default 50, cap 1000); --env=prod flag required for prod target. Defaults to staging per CLAUDE.md.
• A1.8 Fail-fast on missing NEXT_PUBLIC_CLIENT_ID, SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY.
• A1.9 recordPipelineRun(supabase, { pipelineName: 'ai_telemetry_backfill', status: 'success', itemsCreated: [...ids], result: { cost: 0, rowsUpdated: N } }) on completion (signature per feedback_record_pipeline_run_signature).
• A1.10 Rows with ingest_source NOT IN <lookup table> log a warning, emit a triage row, do not write a model. Should not occur on the 617 prod rows but defensive.
• A1.11 Sanity check after prod run: SELECT COUNT(*) FROM content_items WHERE classification_model IS NULL AND classified_at IS NOT NULL returns 0 (or matches a known small count of pathological rows).

1.5 Implementation outline

File: scripts/backfill-ai-telemetry.ts

// Pseudocode — full impl follows backfill-classify-content-items.ts pattern
import { createServiceClient } from '@/lib/supabase/server-service';
import { recordPipelineRun } from '@/lib/pipeline/record-run';

const MODEL_LOOKUP: Record<string, { classify: string; embed: string }> = {
  qa_import: { classify: 'claude-opus-4-6', embed: 'text-embedding-3-large' },
  manual: { classify: 'claude-sonnet-4-6', embed: 'text-embedding-3-large' },
  url_import: { classify: 'claude-sonnet-4-6', embed: 'text-embedding-3-large' },
  python_url: { classify: 'claude-opus-4-6', embed: 'text-embedding-3-large' },
  rss_feed: { classify: 'claude-sonnet-4-6', embed: 'text-embedding-3-large' },
  python_markdown: { classify: 'claude-opus-4-6', embed: 'text-embedding-3-large' },
  upload: { classify: 'claude-sonnet-4-6', embed: 'text-embedding-3-large' },
  upload_autosplit: { classify: 'claude-sonnet-4-6', embed: 'text-embedding-3-large' },
  mcp_create: { classify: 'claude-sonnet-4-6', embed: 'text-embedding-3-large' },
  bid_outcome_integration: { classify: 'claude-sonnet-4-6', embed: 'text-embedding-3-large' },
  batch_reclassify: { classify: 'claude-sonnet-4-6', embed: 'text-embedding-3-large' },
};

// 1. Parse args (--dry-run, --limit, --env)
// 2. Resolve Supabase URL; print host portion; fail-fast on env
// 3. Query candidates:
//      SELECT id, ingest_source, classified_at, embedding IS NOT NULL AS has_embedding,
//             classification_model IS NULL AS needs_class,
//             embedding_model IS NULL AS needs_embed
//      FROM content_items
//      WHERE classification_model IS NULL OR embedding_model IS NULL
//      ORDER BY created_at ASC
//      LIMIT $1
// 4. Per row: resolve writes via lookup; skip rows with no entry, log warning
// 5. Execute single raw UPDATE per row:
//      UPDATE content_items
//      SET metadata = COALESCE(metadata, '{}'::jsonb)
//                     || jsonb_build_object('telemetry_source','backfill'),
//          classification_model = $1,
//          embedding_model = $2
//      WHERE id = $3
// 6. recordPipelineRun() with itemsCreated = updated UUIDs
// 7. Sanity-check query; print summary

1.6 Test plan

Test	File	Coverage
Unit — lookup table covers all enum values	__tests__/scripts/backfill-ai-telemetry.test.ts	A1.10
Unit — per-column idempotency	same	A1.5
Unit — classified_at IS NULL skips classification_model write	same	A1.1
Unit — embedding IS NULL skips embedding_model write	same	A1.2
Integration — staging dry-run prints expected count	__tests__/integration/backfill-ai-telemetry.integration.test.ts	A1.6
Integration — staging real run; verify metadata merge preserves existing keys	same	A1.4
Pipeline parity — pipeline_runs row written	covered by recordPipelineRun unit tests	A1.9

Why integration tests need staging: the JSONB merge invariant (preserving existing metadata keys) is hard to mock-verify; staging branch is the canonical environment per CLAUDE.md.

1.7 Run order

1. Spec amendment. Edit docs/specs/ai-telemetry-instrumentation-spec.md §6.1.1 to use typed-column lookup; mark §6.7 (triage report) closed; bump spec version to 1.2.
2. Implementation. Land scripts/backfill-ai-telemetry.ts + unit + integration tests on a feature branch.
3. Staging dry-run. bun run scripts/backfill-ai-telemetry.ts --dry-run (default staging). Confirm candidate count matches staging DB state.
4. Staging real run. bun run scripts/backfill-ai-telemetry.ts --limit 20. Verify writes; verify metadata.telemetry_source set; verify pipeline_runs row.
5. Prod dry-run. bun run scripts/backfill-ai-telemetry.ts --dry-run --env=prod. Expected ~591 rows for classification_model (617 − 26 with no classified_at) and ~591 for embedding_model (617 − 26 NULL embeddings).
6. Prod real run. bun run scripts/backfill-ai-telemetry.ts --limit 1000 --env=prod.
7. Sanity check. Run §1.4 A1.11 query; confirm 0/expected NULL count.
8. Stale-CSV cleanup. Move scripts/output/wp-b-provenance-triage-2026-04-28.csv to scripts/output/.archive/ with note in commit message.

1.8 Effort estimate

~3-4h end-to-end (script + tests + spec amendment + staging+prod runs + sanity check). Mostly write-and-verify, no design open questions.

1.9 Rollback

metadata.telemetry_source='backfill' flag enables targeted rollback:

UPDATE content_items
SET classification_model = NULL,
    embedding_model = NULL,
    metadata = metadata - 'telemetry_source'
WHERE metadata->>'telemetry_source' = 'backfill';

---

2. Phase B — MCP provenance gate

2.1 Goal

Prevent the WP-B Phase 0c-equivalent triage from recurring by enforcing provenance at the MCP write path. AI clients (Claude Desktop, Claude.ai) must supply at least one of source_url / source_file / source_document_id when calling create_content_item.

2.2 Verified gap

lib/mcp/tools/content.ts:315-336 declares all three provenance fields as .optional() Zod strings. The tool description (line 261) advises supplying “one of” but no schema-level enforcement exists. AI clients can create rows with all three NULL; the row inserts at line 516–520 with provenance silently omitted via ...(args.source_url && { source_url: args.source_url }).

update_content_item (line 862–928) cannot patch provenance — provenance fields are absent from the allowed-fields list.

kb://items/{id} (lib/mcp/resources.ts:106) and kb://qa/{id} (lib/mcp/resources.ts:308) hide all provenance from the AI client view.

MCP eval Layer 4 has no test asserting provenance-at-create. Test fixtures currently pass with NULL provenance.

2.3 Acceptance criteria

• B1.1 create_content_item Zod schema rejects calls with all three of source_url, source_file, source_document_id absent. Use a top-level .refine() so the error message is structured.
• B1.2 Error message is actionable: "At least one of source_url, source_file, or source_document_id must be provided so the row has attributable origin".
• B1.3 kb://items/{id} resource projection includes source_url, source_file, source_document_id, and ingest_source so AI clients can see origin.
• B1.4 kb://qa/{id} resource projection includes source_url, source_file, ingest_source.
• B1.5 New MCP eval test under scripts/mcp-eval/ Layer 1 (protocol): create_content_item without provenance returns a structured Zod error, not a 500.
• B1.6 New MCP eval test Layer 4 (functional): create_content_item with source_url only succeeds and the row has ingest_source='mcp_create' set.
• B1.7 Tool description (line 261) updated to make the requirement prominent — top-level “MUST supply one of …” phrasing.
• B1.8 No data migration. Existing 617 rows are not retroactively blocked; the gate applies to future writes only.

2.4 Verified non-issues

• update_publication_status does not touch provenance — out of scope.
• update_content_item cannot patch provenance — out of scope (and arguably correct: provenance is set at create time and represents historical truth).
• Prompts in lib/mcp/resources.ts:806-1078 do not create rows directly.

2.5 Implementation outline

// lib/mcp/tools/content.ts — create_content_item input schema
const inputSchema = z
  .object({
    title: z.string().min(1).max(500),
    content: z.string().min(1),
    content_type: z.enum([...]),
    source_url: z.string().url().max(2048).optional(),
    source_file: z.string().max(500).optional(),
    source_document_id: z.string().uuid().optional(),
    // ... other fields ...
  })
  .refine(
    (v) => Boolean(v.source_url || v.source_file || v.source_document_id),
    {
      message:
        'At least one of source_url, source_file, or source_document_id must be provided',
      path: ['source_url'],
    },
  );

Resource projection update (line 106 in resources.ts):

.select(
  'id, title, suggested_title, content_type, primary_domain, primary_subtopic, ' +
  'summary, ai_keywords, freshness, content, created_at, updated_at, ' +
  'source_url, source_file, source_document_id, ingest_source',
)

2.6 Test plan

Test	File	Coverage
Zod refine rejects all-NULL provenance	__tests__/lib/mcp/tools/content.test.ts	B1.1
Error message matches	same	B1.2
Resource includes new fields	__tests__/lib/mcp/resources.test.ts	B1.3, B1.4
Layer 1 MCP eval — protocol error	scripts/mcp-eval/protocol.ts	B1.5
Layer 4 MCP eval — happy path with source_url	scripts/mcp-eval/functional-correctness.ts	B1.6

2.7 Effort estimate

~2-3h. Schema change is straightforward; testing surface is well-trodden via MCP eval suite.

2.8 Risk

• LOW — gate applies to future writes; no data migration; no breaking change to existing successful callers.
• Caveat: Claude Code chat sessions that have memorised “create_content_item without source fields works” will hit the gate. The Zod error message is the signal. No memory amendment needed by us.

---

3. Phase C — schema cleanup (revised after verification)

3.1 What the verification changed

The initial recommendation flagged ten columns as “drop candidates”. Post-grep verification, only source_bid is safe to drop. Eight other candidates have active consumers; one (metadata.ingestion_source JSONB key) is tied to the WP-B triage workflow.

3.2 Verified verdicts

Candidate	Initial verdict	Post-verification	Reason
source_document (text)	DROP	HOLD	Read by components/item-detail/* and components/item-row/* — display path for Q&A pair source attribution. 0 writers but readers exist.
file_path	DROP	HOLD	Read by app/api/items/[id]/files, app/api/items/[id]/images, lib/ai/vision.ts, scripts/backfill-* — storage path for download handlers. 0 current writers but legacy data populates it.
parent_id	DROP	HOLD	Self-FK chain on source_documents (not on content_items) plus index. Drop is safe on content_items but value is structurally the same as superseded_by; verify before dropping.
previous_freshness	DROP	HOLD	Trigger writes on freshness transition; read by app/api/cron/freshness-transitions/route.ts (6 matches) for change detection.
notes	DROP	HOLD	Read by intelligence flags.notes — used by review UI. 0 content_items writers, but logical-coupling to flags makes drop risky.
secondary_domain / secondary_subtopic	DROP	HOLD	Active classifier writes (lib/ai/classify.ts); read by review queue + detail view + templates. Roadmap §3.7.4 plans to tighten Zod enums.
source_bid (FK to workspaces)	DROP	DROP	0 writers, 0 readers, FK + index unused. Safe.
priority	DROP	HOLD	Form input on create, read by review queue + quality actions.
user_tags	DROP	HOLD	Form input + batch ingest writers; read by browse filters + library merge + review queue.

3.3 Verified extraction targets

Target	Initial recommendation	Post-verification
Q&A answer cluster (brief, detail, reference, answer_standard, answer_advanced)	Extract to qa_answers sidecar	DO NOT EXTRACT. brief / detail / reference are generic layer-inference flags (lib/layer-inference.ts:23-25), not Q&A-specific. answer_standard / answer_advanced are canonical for q_a_pair per docs/specs/p0-bm-phase3-qa-library-importer-markdown-spec.md. Schema is intentional.
Governance review cluster	Extract to content_governance 1:1 sidecar	DO NOT EXTRACT. These are primary item state, not denormalised cache. Optional follow-up: append-only governance_review_history sidecar for audit, but item columns stay.
AI telemetry sidecar	Extract to ai_call_log	Phase D — see §4. Spec already ratified.

3.4 Revised Phase C scope

In:

1. Drop source_bid column + FK + index (single migration).
2. Add FK constraint on source_document_id → source_documents(id) ON DELETE SET NULL.
3. Stop writing metadata.ingestion_source from scripts/ingest_markdown.py:441. Now redundant with typed column.
4. Move legacy CSV scripts/output/wp-b-provenance-triage-2026-04-28.csv to archive.

Out (deferred):

• All other column drops — held pending consumer migration.
• Q&A and governance cluster extraction — unnecessary per verification.

3.5 Acceptance criteria

• C1.1 Migration drops content_items.source_bid + the FK + the index; bun run knip shows no orphan TS code referencing source_bid.
• C1.2 Migration adds FK constraint content_items_source_document_id_fkey FOREIGN KEY (source_document_id) REFERENCES public.source_documents(id) ON DELETE SET NULL.
• C1.3 scripts/ingest_markdown.py no longer writes metadata.ingestion_source (typed column suffices). The 23 historical rows retain the legacy key for auditability — no retroactive scrub.
• C1.4 Pipeline parity guard test (__tests__/scripts/pipeline-parity.test.ts) updated to assert TS and Python no longer write metadata.ingestion_source at insert.
• C1.5 Migration follows CLAUDE.md function-search_path discipline (n/a here — DDL only) and SECURITY/REVOKE discipline (n/a — no new functions).
• C1.6 bun run test:integration --changed green.

3.6 Migration outline

-- supabase/migrations/<ts>_content_items_phase_c_cleanup.sql

-- 1. Drop source_bid (column + FK + index)
ALTER TABLE public.content_items
  DROP CONSTRAINT IF EXISTS content_items_source_bid_fkey;
DROP INDEX IF EXISTS public.idx_content_items_source_bid;
ALTER TABLE public.content_items DROP COLUMN IF EXISTS source_bid;

-- 2. Add source_document_id → source_documents FK
ALTER TABLE public.content_items
  ADD CONSTRAINT content_items_source_document_id_fkey
  FOREIGN KEY (source_document_id)
  REFERENCES public.source_documents(id)
  ON DELETE SET NULL;

3.7 Test plan

Test	File	Coverage
FK rejects orphan source_document_id	__tests__/integration/content-items-source-doc-fk.integration.test.ts	C1.2
TS+Python no longer write metadata.ingestion_source	__tests__/scripts/pipeline-parity.test.ts	C1.4
Knip shows no source_bid references	manual bun run knip	C1.1

3.8 Effort

~2h. Migration + Python edit + test updates.

3.9 Risk

• LOW for source_bid drop — verified zero readers/writers.
• MEDIUM for source_document_id FK addition — currently 0/617 populated; no orphan rows possible. Future writes will need a valid source_documents row first.
• CAVEAT: the source_document_id FK is a forward-looking constraint. No existing app path creates source_documents rows except app/api/upload/route.ts, which already sets source_document_id correctly. Q&A and markdown pipelines do not currently populate the FK; they will continue to leave it NULL until §6 sequencing decides whether to extend them.

---

4. Phase D — ai_call_log sidecar

4.1 Goal

Land per-AI-call telemetry in a normalised sidecar table per docs/specs/ai-telemetry-instrumentation-spec.md §7.1. Enables fine-grained cost rollups by model / call_type / pipeline / time window without bloating content_items.

4.2 Verified preconditions

• Spec §7.1 schema is fully designed.
• §7.4 hybrid recommendation (per-item denormalised cache + per-call log + cron rollup) is ratified per Wave 4 OQ-4.
• ai-telemetry-instrumentation-plan.md covers WP-B1 instrumentation at all four AI call sites (lib/ai/classify.ts:1212 Pass 1, Pass 2 in validateEntities(), lib/ai/embed.ts, Python kb_pipeline/classify.py, kb_pipeline/embed.py). Phase D builds on that plan.
• Phase A landing populates classification_model / embedding_model on the historical 617 rows; Phase D adds the per-call detail going forward.

4.3 Acceptance criteria

• D1.1 Migration creates ai_call_log per spec §7.1 schema with FK to content_items and pipeline_runs, indexes on content_item_id and created_at DESC.
• D1.2 Migration includes REVOKE EXECUTE ON FUNCTION + RLS as appropriate per CLAUDE.md function-search_path / anon-grant discipline.
• D1.3 TS classify Pass 1 writes a row per call (call_type='classify_pass1', pipeline=‘ts’).
• D1.4 TS classify Pass 2 writes a row per call (call_type='classify_pass2').
• D1.5 TS embed writes a row per call (call_type='embed').
• D1.6 Python classify writes a row per call (Python pipeline, call_type='classify_pass1').
• D1.7 Python embed writes a row per call.
• D1.8 Cost rollup query SUM(estimated_cost) GROUP BY model, call_type returns expected values on staging integration test.
• D1.9 content_items per-item cache columns (classification_model, classification_tokens_*) remain as denormalised cache; populated from the same data on insert. CI test verifies they don’t drift.

4.4 Out of scope

• Cron rollup table cost_aggregations — defer until cost queries become slow or until UI / alerting needs it.
• Removing the per-item cache columns from content_items — keep for now; drift-check via spec §1.5 cache-effectiveness caveat.

4.5 Schema (per spec §7.1, verbatim)

CREATE TABLE public.ai_call_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content_item_id UUID NOT NULL REFERENCES public.content_items(id) ON DELETE CASCADE,
  call_type TEXT NOT NULL CHECK (call_type IN
    ('classify_pass1', 'classify_pass2', 'embed', 'summary')),
  model TEXT NOT NULL,
  tokens_in INT NOT NULL,
  tokens_out INT NOT NULL DEFAULT 0,
  cache_creation_tokens INT NOT NULL DEFAULT 0,
  cache_read_tokens INT NOT NULL DEFAULT 0,
  estimated_cost NUMERIC NOT NULL,
  pipeline TEXT NOT NULL CHECK (pipeline IN ('ts', 'python')),
  pipeline_run_id UUID REFERENCES public.pipeline_runs(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ai_call_log_content_item_id_idx ON public.ai_call_log(content_item_id);
CREATE INDEX ai_call_log_created_at_idx ON public.ai_call_log(created_at DESC);

4.6 Effort

~6-8h (migration + 5 call-site instrumentations + tests). Significantly larger than Phases A-C due to dual-pipeline coverage.

4.7 Sequencing dependency

Phase D depends on:

• Phase A complete (populates initial classification_model so the cache invariant is meaningful).
• WP-B1 instrumentation landed at the four call sites (per ai-telemetry-instrumentation-plan.md Phase 1+2). Without that, Phase D writes have nowhere to capture token data.

---

5. Cross-cuts — reference doc edits

Apply alongside Phase A landing. Severity per audit:

5.1 HIGH-severity edits (must land with Phase A)

1. docs/reference/product-roadmap.md §3.7 — replace stale Phase 0c blocking claim. Suggested text:

   Phase 0c (scripts/wp-b-apply-triage.ts SQL UPDATE batch) landed via S207-S209 (supabase/migrations/20260428180945_backfill_ingest_source.sql, …20260428235042_refine_ingest_source_backfill_residuals.sql). Typed ingest_source column now 100% populated. Phase 1 (telemetry backfill) active under docs/plans/content-items-cleanup-plan.md §1.

2. docs/reference/product-roadmap.md §3.7 — row count drift “606 production rows” → “617 production rows (verified 2026-05-06)“.

5.2 MEDIUM-severity edits (land with Phase A spec amendment)

3. docs/specs/ai-telemetry-instrumentation-spec.md §6.1.1 — supersede the metadata.ingestion_source-keyed lookup table with the typed-column lookup from §1.3 of this plan. Bump spec version to 1.2; record in §13 changelog.

4. docs/specs/ai-telemetry-instrumentation-spec.md §6.7 — annotate Phase 0a/0b/0c as CLOSED with reference to the migration filenames that shipped them.

5.3 LOW-severity edits (optional, can land any time)

5. docs/specs/p0-15-wave-a-provenance-spec.md — add status banner:

   STATUS: SHIPPED (S207–S209). This spec documents Phase A provenance instrumentation; WP-A4 migrated S207, OPS-41 backfill S209. See docs/reference/state-of-the-product.md §5.2 for current state.

6. docs/reference/field-consumer-dependency-map.md — clarify “11.6% NULL on legacy rows” → “11.6% NULL pre-OPS-41 backfill (now 0% post-S209)“.

5.4 Reference docs verified accurate (no edit)

• docs/reference/SCHEMA-QUICK-REFERENCE.md
• docs/reference/data-entry-points.md
• docs/reference/state-of-the-product.md §5 / §5.2

---

6. Sequencing and effort summary

Phase	Description	Depends on	Effort	Risk
A	WP-B Phase 1 backfill + spec amendment + ref-doc edits §5.1, §5.2	nothing (ready)	~3-4h	LOW
B	MCP provenance gate	nothing (independent of A)	~2-3h	LOW
C	Schema cleanup (source_bid drop + source_document_id FK + python_markdown JSONB stop)	nothing (independent)	~2h	LOW
D	ai_call_log sidecar + 5 call-site instrumentations	Phase A complete + WP-B1 instrumentation per existing plan	~6-8h	MEDIUM

Recommended order: A → C in parallel with B → D last.

A and C can land in either order; both are short and independent. B is also independent and can run alongside. D depends on A finishing (so the cache invariant is meaningful) and on WP-B1 instrumentation landing per the existing ai-telemetry-instrumentation-plan.md (currently RATIFIED, not yet implemented at the call sites).

Total effort: ~13-17h cumulative. Spread across two sessions naturally (A+B+C in one; D + WP-B1 instrumentation in another).

---

7. Open questions

1. Q1. Should we drop the per-item denormalised telemetry cache on content_items once ai_call_log is populated, or keep both?
   • Default: keep both, with CI drift check (per spec §7.1 Cons).
2. Q2. Should we tighten the Zod ingest_source enum at lib/validation/schemas.ts:247-249 to match the canonical 11-value list the typed column accepts?
   • Default: yes — file as separate roadmap item (Roadmap §3.7.4).
3. Q3. Should Q&A and markdown pipelines start populating source_documents rows so source_document_id FK gets used?
   • Default: no for Q&A (not version-tracked file origins per spec); maybe for markdown if dogfood / production-readiness wants document-level versioning. Defer.
4. Q4. Do we want a “create source_documents row, then content_items row” wrapper for the upload path so the FK is more uniformly populated?
   • Default: defer — app/api/upload/route.ts already does this; no other ingest path has files-with-versioning semantics.