Phase 0.2a — contentitems 73-column audit

Phase 0.2a — content_items 73-column audit

Audit date: 2026-05-06 Branch: content-items-investigation Total columns: 73 (verified by counting Row keys at database.types.ts:602–678) Prod row count: 617 (from prior audit) Inputs: 10 Phase 0.1 reports (docs/plans/phase-0-investigation/0.1-*.md), schema (supabase/types/database.types.ts), 16 content_items-relevant migrations, and grep across app/, lib/, components/, hooks/, contexts/, scripts/ (test files excluded).

Decisions in scope

• D1. ingest_source enum widening to add markdown_batch (currently 11 values; will become 12) — Path 8 (TS EP2) currently writes 'upload' colliding with Path 7 (file upload).
• D2. EP2 source_documents parity to be wired (Path 8 currently doesn’t write source_documents; Path 7 does).
• D3. Python URL cron treated as final-intended-state — Cloud Run job is built and deployed (cloudbuild.yaml/prod-phew.yaml) but not currently invoked with real input; verdicts assume it WILL be once cron wiring lands in production-readiness track.
• Re-ingest is feasible pre-launch — the question for many gaps is “does re-ingest with fixed paths repopulate?” rather than “do we need a backfill migration?”.

Verdict legend

• ACTIVE — writers + readers wired; populating expected data.
• ACTIVE-EMPTY — wired correctly but path not exercised in prod yet; architecturally fine.
• SUPERSEDED — readers exist but writers stopped (or readers reading wrong shape).
• NEVER-WIRED-PATH-FIX — column should populate but writing path is broken; re-ingest with fixed path repopulates.
• NEVER-WIRED-CODE-FIX — column needs new code/migration (e.g. dependency table doesn’t exist or no writer code anywhere).
• DROP-CANDIDATE — no readers, no writers; safe to drop.
• INTENTIONAL-NULL — designed nullable for future feature; has roadmap entry or downstream lifecycle workflow that populates later.

Master table

Paths key:

• P1 = Python URL (scripts/ingest.py)
• P2 = Python markdown (scripts/ingest_markdown.py)
• P3 = Q&A docx (scripts/import_bid_library.py)
• P4 = TS URL ingest (/api/ingest/url)
• P5 = TS manual creation (POST /api/items)
• P6 = TS batch creation (POST /api/items/batch)
• P7 = TS file upload (POST /api/upload)
• P8 = TS EP2 markdown batch (markdown-orchestrator)
• P9 = RSS intelligence pipeline (lib/intelligence/pipeline)
• P10 = MCP create (lib/mcp/tools/content.ts)

#	Column	Type	Nullable	Verdict	Writers (paths)	Readers (file:line)	Re-ingest fix?	Notes
1	ai_keywords	text[]	Yes	ACTIVE	P1, P2, P3, P4, P5, P6, P7, P8, P9, P10 (via classify.ts:1431)	components/content/content-card.tsx; components/content/content-row.tsx; components/item-detail/metadata-sidebar.tsx; lib/mcp/tools/search.ts; hooks/browse/use-filter-data.ts; many	n/a	Classifier-emitted keywords; deterministic in P3 (DOCX). Universally written by all classified paths.
2	answer_advanced	text	Yes	ACTIVE (Q&A only)	P3 (import_bid_library.py:260); P5 conditional via body	components/qa/qa-answer-display.tsx:275,299,332; app/library/library-content.tsx; etc.	Re-ingest from DOCX repopulates (Pattern A 5-col tables).	Always NULL on Pattern B 5-col + non-Q&A content.
3	answer_standard	text	Yes	ACTIVE (Q&A only)	P3 (import_bid_library.py:259); P5 spread; P6 (route.ts:268-269 w/ extractor fallback); P10 (content.ts:528-530 for q_a_pair only)	components/qa/qa-answer-display.tsx; app/library/library-content.tsx; lib/mcp/tools/search.ts	Yes — re-ingest of Q&A DOCX repopulates.	Auto-extracted from content for batch path Q&A pairs. NULL for non-Q&A content.
4	archive_reason	text	Yes	ACTIVE	lib/governance/publication-transitions.ts:199; app/api/items/[id]/route.ts:277; lib/supersession/set.ts; lib/mcp/tools/governance.ts:794	lib/mcp/formatters/governance.ts:244; app/api/admin/content-dedup/[id]/confirm-duplicate/route.ts	n/a (transition)	Transition-time field — never set on initial INSERT. Set when archiving via governance/supersession workflows. INTENTIONAL-NULL on insert, ACTIVE on archive.
5	archived_at	timestamptz	Yes	ACTIVE	lib/governance/publication-transitions.ts:196; lib/supersession/set.ts; app/api/admin/content-dedup/[id]/confirm-duplicate	app/api/cron/freshness-transitions/route.ts; lib/freshness/; many archived-filter sites	n/a (transition)	INTENTIONAL-NULL on insert. Used as WHERE archived_at IS NULL filter universally.
6	archived_by	uuid	Yes	ACTIVE	lib/governance/publication-transitions.ts:197; lib/supersession/set.ts	lib/mcp/formatters/governance.ts; admin dedup routes	n/a (transition)	INTENTIONAL-NULL on insert.
7	author_name	text	Yes	ACTIVE	P4 (/api/ingest/url:182 conditional); P5 (route.ts:200 conditional); P7 (route.ts:331 conditional via form author); P8 (markdown-orchestrator:640 frontmatter or batch options); P2 (via --author CLI flag stored in metadata)	components/content/content-card.tsx:361; components/content/content-row.tsx:370; components/item-detail/metadata-sidebar.tsx:237-240; components/item-detail/reader-view.tsx:132,337; hooks/browse/use-filter-data.ts:89	Yes — re-ingest with proper extractor (URL paths) repopulates from page meta.	Multi-path; well-read by UI. P1 (Python URL) does NOT capture author; gap from --author flag handling — consider P1 should write this if extractor returns author.
8	brief	text	Yes	ACTIVE (Q&A path; manual)	P3 progressive-depth UPDATE (progressive_depth.py:204 via post_insert.py:334); P5 (route.ts:202 conditional from body)	components/content/content-card.tsx:54,225,228; components/content/content-row.tsx:98,358-359; components/content/content-library-result.tsx:56,177; lib/mcp/formatters/content.ts; lib/quality/quality-score.ts	Yes — re-ingest with --entities or progressive-depth flag repopulates Q&A.	Progressive-depth column. NULL on non-Q&A paths.
9	captured_date	timestamptz	Yes	ACTIVE (most paths)	P1 (pipeline.py:218 from extracted page date); P2 (ingest_markdown.py:438 from file mtime); P4 (route.ts:184 now()); P5 (route.ts:176 now()); P6 (route.ts:254 now()); P10 (content.ts:496 now())	app/api/review/queue/route.ts:630; lib/browse-helpers.ts:75-88; app/page.tsx; lib/mcp/tools/dashboard.ts	Partial — P3, P7, P8, P9 currently leave NULL; re-ingest with semantic captured_date population would fix.	Cross-path inconsistency. P3 (Q&A docx), P7 (file upload), P8 (TS markdown), P9 (RSS) all leave NULL despite having dates available (DOCX file mtime, file upload now(), markdown frontmatter date, RSS publishedAt). RSS stores metadata.published_at instead.
10	citation_count	int4	No (default 0)	ACTIVE	DB trigger update_citation_count() from FK on content_citations (per 0.1-ts-batch-creation.md §3)	components/content/content-card.tsx:57; components/shared/quality-score.tsx:87-88; lib/quality/quality-score.ts (input to score)	n/a	Maintained by DB trigger; no app-level writes from any ingest path.
11	classification_cache_creation_tokens	int4	Yes	NEVER-WIRED-CODE-FIX	None (all 10 paths log/discard tokens)	lib/provenance/item-provenance.ts:170,278; scripts/wp-b-triage-report.ts:245-246	Yes — re-ingest fixes only if classify wired to write tokens. Code change required.	Reader is lib/provenance/item-provenance.ts (item provenance display). All 10 ingest paths capture this from Anthropic response but discard. AI telemetry instrumentation plan exists (docs/plans/ai-telemetry-instrumentation-plan.md).
12	classification_cache_read_tokens	int4	Yes	NEVER-WIRED-CODE-FIX	None	lib/provenance/item-provenance.ts:171,279	Yes — same as #11.	Same telemetry-gap class.
13	classification_confidence	float8	Yes	ACTIVE	All 10 paths (P1-P10) via classify.ts:1434 (TS) or import_bid_library.py:271 (P3 keyword) or pipeline.py:242 (P1)	components/content/content-card.tsx; components/review/publication-review-card.tsx; components/item-detail/metadata-sidebar.tsx; lib/quality/quality-score.ts	Yes (all paths populate).	Universal classifier output.
14	classification_model	text	Yes	NEVER-WIRED-CODE-FIX	None — model literal is in env / getAIModel() but never persisted by any path	lib/provenance/item-provenance.ts:148,152; scripts/wp-b-triage-report.ts:245	Yes — same as #11.	Same telemetry gap. Anthropic response has the model but no path persists. Reader is provenance display.
15	classification_reasoning	text	Yes	ACTIVE	P1 (pipeline.py:246); P2 (ingest_markdown.py:471); P4-P10 via classify.ts:1435	lib/provenance/item-provenance.ts; lib/mcp/formatters/content.ts; admin dedup routes	Yes.	Universal classifier output. P3 leaves NULL (keyword classifier doesn’t generate reasoning).
16	classification_tokens_in	int4	Yes	NEVER-WIRED-CODE-FIX	None	lib/provenance/item-provenance.ts:165,168,276; scripts/wp-b-triage-report.ts	Yes — same as #11.	Same telemetry gap. classify.ts:1372 captures pass1Usage.input_tokens but only logs.
17	classification_tokens_out	int4	Yes	NEVER-WIRED-CODE-FIX	None	lib/provenance/item-provenance.ts:166,169,277	Yes — same as #11.	Same telemetry gap.
18	classified_at	timestamptz	Yes	ACTIVE	P1, P2, P3 (set to now()); P4-P10 via classify.ts:1436	app/api/cron/quality-score/route.ts; lib/mcp/formatters/content.ts; admin dedup queue	Yes.	Set whenever classification succeeded on the path.
19	content	text	No	ACTIVE	All paths	Universal — every reader/UI/search	n/a	NOT NULL. Full markdown body.
20	content_owner_id	uuid	Yes	ACTIVE	P4 (route.ts:186); P5 (route.ts:178); P6 (route.ts:256); P7 (route.ts:333); P8 (orchestrator:647); P10 (content.ts:498) — all via resolveContentOwnerId()	components/item-detail/metadata-sidebar.tsx:84,407,417,419; components/item-detail/editor-view.tsx:372; app/api/governance/review/route.ts:208-209; app/api/items/[id]/owner/route.ts; lib/quality/quality-actions.ts:201; lib/source-documents/source-document-notifications.ts:55; app/api/cron/freshness-transitions/route.ts:242,264,716; app/api/cron/review-cadence/route.ts:221,261-263	Partial — P1, P2, P3, P9 (Python + RSS) currently leave NULL; backfill migration 20260428145733_backfill_content_owner_id_from_created_by.sql filled retroactively.	Cross-path inconsistency. Python paths run as service-role with no caller user; RSS pipeline-system-user. Backfill migration sets it from created_by where missing.
21	content_text_hash	text	Yes	ACTIVE (DB-managed)	DB GENERATED ALWAYS — md5(normalised content)	lib/dedup.ts (via find_exact_duplicates RPC)	n/a (auto-generated)	Per CLAUDE.md gotcha: NEVER include in payload.
22	content_type	text	No	ACTIVE	All paths	Universal — every UI component (content-card.tsx, content-row.tsx, etc.); search/filter	n/a	NOT NULL. P9 conditionally UPDATEs post-classify via inferContentType().
23	created_at	timestamptz	No (default now())	ACTIVE	DB default	Universal	n/a	DB-managed.
24	created_by	uuid	Yes	ACTIVE	P4 (route.ts:185); P5 (route.ts:177); P6 (route.ts:255); P7 (route.ts:332); P8 (orchestrator:646); P10 (content.ts:497)	lib/user/display-name.ts; verifier display chains; many	Partial — P1, P2, P3, P9 leave NULL (service-role).	Trigger fallback to 'a0000000-...' pipeline-system UUID for v1 history row.
25	dedup_status	text	No (default ‘clean’)	ACTIVE	P1 (pipeline.py:229 conditional); P2 (line 453 conditional); P3 (line 693 conditional); P4-P10 via resolveDedupStamp()	app/api/admin/content-dedup/...; components/admin/content-dedup/...	Yes (re-ingest computes hash + sets stamp).	Soft-block stamp; CHECK constraint values: ‘clean’, ‘suspected_duplicate’, ‘superseded’ (latter set on superseded older row, not new row).
26	detail	text	Yes	ACTIVE (Q&A path; manual)	P3 progressive-depth UPDATE; P5 conditional from body	components/content/content-card.tsx:54; lib/quality/quality-score.ts; lib/mcp/formatters/content.ts	Yes (re-ingest with --entities for Q&A).	Progressive-depth column. NULL on non-Q&A paths.
27	embedding	vector(1024)	Yes	ACTIVE	P1 (pipeline.py:264 conditional); P2 (line 478 conditional); P3 (conditional unless --skip-embed); P4-P10 via classify.ts:1470 (regen) + initial route insert	lib/templates/template-coverage.ts:272,543; lib/dedup.ts (similarity RPC); search RPCs (search_content, search_with_filters)	Yes (re-embed).	All paths populate. May be NULL if all embed calls fail (try/catch silenced).
28	embedding_model	text	Yes	NEVER-WIRED-CODE-FIX	None — model literal in env but never persisted	lib/provenance/item-provenance.ts:156,159; scripts/wp-b-triage-report.ts:246	Yes — same telemetry gap as #11.	Reader is provenance display.
29	embedding_tokens	int4	Yes	NEVER-WIRED-CODE-FIX	None — captured in response.usage.total_tokens but discarded	lib/provenance/item-provenance.ts:177-178,280	Yes — same telemetry gap as #11.	Reader is provenance + cost display.
30	expiry_date	date	Yes	ACTIVE (URL/upload only)	P4 (route.ts:271 conditional via findExpiryDate(extractDates())); P7 (route.ts:590 conditional); app/api/entities/.../metadata/route.ts:121; lib/freshness.ts	app/api/cron/freshness-transitions/route.ts:672,701 (drives transitions); lib/dashboard.ts:681; lib/mcp/formatters/dashboard.ts:373; lib/freshness.ts:149	Partial — P1, P2, P3, P5 (manual), P6, P8, P9, P10 leave NULL. AI temporal_references projection mostly goes to entity_mentions, not this column.	Cross-path inconsistency. Memory project_expiry_date_gap notes “two-store architecture, no sync, dead-end buttons”.
31	file_path	text	Yes	ACTIVE (P7 only)	P7 (route.ts:556 — ${itemId}/${filename} storage path)	app/api/items/[id]/files/route.ts:76,79,89; app/api/items/[id]/images/route.ts:96,99; app/item/[id]/item-detail-client.tsx:169; components/item-detail/reader-view.tsx:299,318	Yes (re-upload).	Single-path field; only file-upload path writes.
32	freshness	text	Yes (default ‘fresh’)	ACTIVE (DB-managed by cron + RPC)	app/api/freshness/calculate/route.ts:86; SQL function recalculate_all_freshness() (20260416102457_pre_squash_reconciliation.sql:2762+); app/api/cron/freshness-transitions/route.ts	Universal in UI/filters/cron	Partial (re-ingest sets default; cron transitions later).	DB-managed lifecycle column.
33	freshness_checked_at	timestamptz	Yes	ACTIVE	app/api/freshness/calculate/route.ts:87; recalculate_all_freshness() SQL fn	app/api/cron/freshness-transitions/route.ts; provenance	n/a	Cron-managed.
34	governance_review_due	timestamptz	Yes	ACTIVE	app/api/source-documents/[id]/send-to-review/route.ts:88; app/api/governance/review/route.ts:142,161; app/api/items/[id]/route.ts:690; app/api/cron/review-cadence/route.ts:167; app/api/cron/freshness-transitions/route.ts:506	app/api/review/queue/route.ts; review/governance tooling	n/a (lifecycle)	Workflow-set; INTENTIONAL-NULL on insert.
35	governance_review_status	text	Yes	ACTIVE	app/api/governance/review/route.ts; app/api/source-documents/.../send-to-review; app/api/items/[id]/route.ts; P5 (manual creation conditional from body — only 'draft' valid per CHECK)	components/content/content-card.tsx:277-278,303,312-315; components/item-detail/metadata-sidebar.tsx:391,395; components/item-detail/content-body.tsx:188,214,223; app/api/cron/...	n/a (lifecycle)	INTENTIONAL-NULL on insert; lifecycle workflow-managed.
36	governance_reviewer_id	uuid	Yes	ACTIVE	app/api/governance/review/route.ts:141,152,160; app/api/items/[id]/route.ts:691	governance flows	n/a (lifecycle)	Workflow-set.
37	id	uuid	No (default gen_random_uuid())	ACTIVE	DB default	Universal	n/a	DB-managed.
38	ingest_source	text	Yes	ACTIVE	P1 ‘python_url’ (pipeline.py:223); P2 ‘python_markdown’ (line 448); P3 ‘qa_import’ (line 288); P4 ‘url_import’ (route.ts:191); P5 ‘manual’ default (route.ts:183); P6 ‘upload_autosplit’ (route.ts:260); P7 ‘upload’ (route.ts:323); P8 ‘upload’ (orchestrator:642) — collides with P7 (D1); P9 ‘rss_feed’ (pipeline.ts:654); P10 ‘mcp_create’ (content.ts:504)	components/admin/content-dedup/.../row-card.tsx; components/review/publication-review-card.tsx:75-76; lib/intelligence/... (writer); v1 history trigger reads to set change_reason	Yes (re-ingest with D1 fix sets markdown_batch for P8).	Decision D1 in scope: P8 currently writes 'upload' colliding with P7. Add 'markdown_batch' to enum (12 values). Drives trg_content_items_ensure_v1_history change_reason logic.
39	layer	text	Yes	ACTIVE (most paths)	P1 (post_insert.py:301 via infer_layer); P2 (same); P3 (‘bid_detail’ literal, line 300); P4 (route.ts:383 inferLayer()); P5 (route.ts:337); P7 (route.ts:826); P10 (content.ts:611-617); P6 (route.ts:369-372)	components/content/content-card.tsx:166,500,687; components/content/content-layer-selector.tsx; lib/templates/template-coverage.ts; app/api/guides/[slug]/route.ts	Partial — P8 (TS markdown), P9 (RSS) leave NULL.	NEVER-WIRED-PATH-FIX for P8/P9. P8 gap explicitly flagged in 0.1-ts-ep2-markdown-batch.md. P9 gap acknowledged in 0.1-rss-intelligence-pipeline.md (no inferLayer call).
40	lifecycle_type	text	Yes	ACTIVE (URL/upload paths)	P4 (route.ts:272); P7 (route.ts:591); app/api/entities/.../metadata/route.ts:121	app/api/cron/freshness-transitions/route.ts:335,357; lib/dashboard.ts; lib/freshness.ts; components/item-detail/metadata-sidebar.tsx:280	Partial — only paths with expiry detection set. P1, P2, P3, P5, P6, P8, P9, P10 leave NULL.	Pairs with expiry_date. SQL function recalculate_all_freshness() reads it.
41	metadata	jsonb	Yes	ACTIVE	All 10 paths	components/item-detail/metadata-sidebar.tsx:548,592; components/reader/source-metadata.tsx:72-93,220-223; components/item-detail/reader-view.tsx:124,131,137; lib/topic-inference.ts:176,306; entities, certifications, dedup, dashboard	n/a	See “metadata JSONB key consolidation” below. Heavy cross-path drift in keys written.
42	next_review_date	date	Yes	ACTIVE	app/api/governance/review/route.ts:144; lib/mcp/tools/governance.ts:1182; via cadence-renewal.ts	app/api/cron/review-cadence/route.ts:88,257; app/api/cron/quality-score/route.ts:154,190; lib/quality/quality-score.ts:181; lib/provenance/item-provenance.ts:285	n/a (lifecycle)	INTENTIONAL-NULL on insert; written by governance review approval flow (S200).
43	notes	text	Yes	DROP-CANDIDATE?	None for content_items.notes (other tables have a notes column — intelligence flags, review assignments — those are different)	None — grep returned 0 readers of content_items.notes specifically; metadata.notes reads exist for bid metadata which is a different shape	n/a	All .notes references in app/lib/components/hooks attach to OTHER tables (assignments.notes, content_intelligence_flags.notes, bid metadata.notes). No reader/writer of content_items.notes found in any non-test file. Verify with prod row-count query before dropping.
44	parent_id	uuid	Yes	DROP-CANDIDATE?	None on content_items	None for content_items.parent_id — parent_id references in code attach to source_documents.parent_id, content_chunks.parent_chunk_id, history versions	n/a	All 10 paths leave NULL. No app code reads content_items.parent_id. Possibly intended for content versioning but never wired. Verify with prod query — could be 0% populated.
45	platform	text	Yes	ACTIVE	P4 ‘web’ (route.ts:179); P5 ‘manual’ (route.ts:175); P6 ‘extraction’ (route.ts:252); P7 ‘manual’ (route.ts:318); P8 ‘manual’ (orchestrator:639); P10 ‘manual’ (content.ts:495). P1/P2/P3/P9 leave NULL	components/content/content-card.tsx:247; components/content/content-row.tsx:369; components/item-detail/metadata-sidebar.tsx:233,591; components/item-detail/reader-view.tsx:130,334; components/browse/filter-panel.tsx:239	Partial — Python paths and RSS leave NULL.	Cross-path inconsistency. Multiple distinct values written by TS paths. P1 (Python URL) does NOT set platform despite knowing it’s web.
46	previous_freshness	text	Yes	ACTIVE	SQL recalculate_all_freshness() (snapshots freshness before recalc; 20260416102457_pre_squash_reconciliation.sql:2762)	app/api/cron/freshness-transitions/route.ts:155,167; lib/mcp/formatters/briefing.ts:46; lib/mcp/tools/shared.ts:543	n/a	DB-managed.
47	previous_quality_score	int4	Yes	ACTIVE	app/api/items/[id]/route.ts:916; app/api/cron/quality-score/route.ts:201,272	lib/quality/quality-actions.ts:115-120,421; lib/mcp/tools/shared.ts:511,518-520; lib/mcp/formatters/briefing.ts:165-167	n/a	Set on quality-score recompute.
48	primary_domain	text	Yes	ACTIVE	All 10 paths conditionally on classifier success	Universal — filters/search/UI	Yes.	NULL when classifier fails or returns no match (P3 keyword classifier may return None per S182 fix).
49	primary_subtopic	text	Yes	ACTIVE	All 10 paths	Universal	Yes.	Coerced empty/whitespace to null.
50	priority	text	Yes	ACTIVE	P5 (route.ts:195 conditional); P10 (content.ts:512 conditional)	components/content/content-card.tsx; components/content/content-row.tsx:125,302; components/item-detail/item-action-bar.tsx:154; components/browse/filter-badges.tsx:137,143; lib/quality/quality-actions.ts:278; lib/content/content-suggestions.ts:367	n/a (manual-set)	INTENTIONAL-NULL on insert from automated paths; user-set via UI.
51	publication_status	text	No (default ‘published’)	ACTIVE	P5 (route.ts:211 conditional from body); P7 (route.ts:330 conditional ‘draft’); P8 (orchestrator:643 from draftFinalToPublicationStatus); P10 (content.ts:514 ‘draft’ only); other paths fall to DB default	components/admin/content-dedup/...; components/shared/publication-status-badge.tsx; components/content/content-card.tsx:308; components/review/publication-review-card.tsx:89; hooks/review/use-review-actions.ts:491; app/api/cron/quality-score/route.ts	Yes (re-ingest with proper draft handling).	Backfill migration 20260427125413_backfill_publication_status.sql set existing rows. P10 has bug: only writes ‘draft’ branch — body values ‘in_review’/‘archived’ silently dropped at insert (per 0.1-mcp-create-content-item.md).
52	quality_score	int4	Yes	ACTIVE	P4 (route.ts:348); P5 (route.ts:374-378); P6 (route.ts:439-445); P7 (route.ts:794); app/api/cron/quality-score/route.ts:201; app/api/items/[id]/route.ts:916	components/content/content-card.tsx; components/shared/quality-score.tsx; lib/mcp/formatters/...; many	Partial — P1, P2, P3, P8, P9, P10 don’t compute on initial INSERT; cron eventually fills.	Cross-path: TS API routes call calculateAndRoundQualityScore() post-insert. Python and MCP rely on cron.
53	quality_score_updated_at	timestamptz	Yes	ACTIVE	Same writers as #52 (paired with quality_score writes)	None in components — read only by cron and provenance	n/a	Pairs with quality_score. No UI reader.
54	reference	text	Yes	ACTIVE (Q&A path; manual)	P3 progressive-depth UPDATE; P5 conditional	components/content/content-row.tsx; components/content/content-card.tsx; lib/quality/quality-score.ts	Yes.	Progressive-depth column.
55	review_cadence_days	int4	Yes	ACTIVE	app/api/governance/review/route.ts:144 (via cadence-renewal.ts); lib/mcp/tools/governance.ts:1182; app/api/items/[id]/route.ts (via owner update)	app/api/cron/review-cadence/route.ts:88; app/api/cron/quality-score/route.ts:154,191; lib/quality/quality-score.ts:181	n/a (lifecycle)	INTENTIONAL-NULL on insert; governance/owner-set. Migration 20260427103256_add_review_cadence_columns.sql.
56	secondary_domain	text	Yes	ACTIVE	All 10 paths conditional (?? null)	Filters/search/UI	Yes.	Often NULL if classifier returned no second-best match.
57	secondary_subtopic	text	Yes	ACTIVE	All 10 paths conditional	Filters/search/UI	Yes.	Coerced empty/whitespace to null.
58	source_bid	uuid	Yes	DROP-CANDIDATE	None — no path writes; FK to workspaces exists per schema	None — grep returned 0 readers	n/a	FK declared (content_items_source_bid_fkey to workspaces) but no writer or reader anywhere in non-test code. Drop after verifying prod row-count = 0% populated.
59	source_document	text	Yes	SUPERSEDED	None — no current writer	components/content/content-row.tsx:199,205; components/content/content-card.tsx:453; components/content/content-library-result.tsx:50; components/item-detail/reader-view.tsx:204,208; components/item-detail/item-title-section.tsx:60,64; components/content/content-library-drawer.tsx:151; app/item/[id]/item-detail-client.tsx:61	n/a — readers should switch	Migration 20260428131822_backfill_metadata_source_document_to_typed_columns.sql already migrated 23 prod rows to source_file/source_url. Readers need to switch to source_file/source_url typed columns. Validation schema lib/validation/schemas.ts:1612 still has source_document optional — likely also stale.
60	source_document_id	uuid	Yes	ACTIVE	P5 (route.ts:212 conditional); P6 (route.ts:270 conditional); P7 (route.ts:441 — links to created source_documents row); P10 (content.ts:518 conditional); D2 in scope: P8 should write but currently doesn’t	app/review/review-content.tsx:255; app/api/source-documents/...; lib/source-documents/...	Partial — D2 fix wires P8.	Decision D2 in scope: P8 (TS EP2 markdown batch) should write a source_documents row + populate this FK for parity with P7.
61	source_domain	text	Yes	ACTIVE	P1 (pipeline.py:213 from urlparse); P3 ” literal (line 264); P4 (route.ts:181, may be empty on parse-fail);	components/item-detail/metadata-sidebar.tsx:244,247; app/api/review/queue/route.ts:628	Partial — P5, P6, P7, P8, P9, P10 leave NULL despite P4-style URL availability.	Cross-path inconsistency. P9 has source_url but doesn’t compute source_domain. P5 (manual) has source_url but doesn’t compute. Possible NEVER-WIRED-PATH-FIX: trivial helper to derive urlparse().netloc could be applied uniformly.
62	source_file	text	Yes	ACTIVE	P2 (ingest_markdown.py:439 relative path); P3 (line 275 DOCX basename); P8 (orchestrator:641); P10 (content.ts:517 conditional); P5 N/A	app/library/library-content.tsx:146,169,331; app/review/review-content.tsx:254; lib/ingest/markdown-orchestrator.ts:256,268; lib/mcp/tools/content.ts:328	Yes.	Multi-path; well-read for filtering. P7 (file upload) does NOT write source_file (only file_path) — possible parity gap.
63	source_url	text	Yes	ACTIVE	P1 (pipeline.py:212); P2 NULL literal; P4 (route.ts:180); P5 (route.ts:201 conditional); P9 (pipeline.ts:649); P10 (content.ts:516 conditional)	components/item-detail/metadata-sidebar.tsx:595; components/item-detail/reader-view.tsx:133,284,287,318,321,336; components/item-detail/item-action-bar.tsx:179,181; app/api/ingest/url/route.ts (dedup early-skip); P9 dedup match key	Yes.	Universal where applicable.
64	starred	bool	No (default false)	ACTIVE	DB default; user toggle via app/api/items/[id]/route.ts	components/content/content-card.tsx:179,513,653,670; components/content/content-row.tsx:271,421; components/item-detail/item-action-bar.tsx:147	n/a	User-action toggle; INTENTIONAL-default-false on ingest.
65	suggested_title	text	Yes	ACTIVE	All paths via classify.ts:1433 (TS) or pipeline.py:243 / ingest_markdown.py:468 (Python). P5/P6/P10 also mirror title at insert	components/item-detail/related-by-entities.tsx:190; components/item-detail/related-by-tags.tsx:70; components/item-detail/reader-view.tsx:402; components/review/publication-review-card.tsx:103; components/review/review-card.tsx:145; app/api/admin/content-dedup/.../route.ts; app/api/bids/[id]/responses/...	Yes.	Universal.
66	summary	text	Yes	ACTIVE	P1 (classifier then summary overwrite — pipeline.py:244,260); P2 (similar); P3 (truncated answer text — line 273); P4-P10 via classify.ts:1432 then summarise.ts:232 (overwrite)	Universal	Yes.	Confused naming with feed_articles.ai_summary (separate column on different table — see CLAUDE.md gotcha).
67	summary_data	jsonb	Yes	ACTIVE (TS API + Python URL/markdown only)	P1 (pipeline.py:252-259 conditional); P2 (update_content_item line 498); P4 via summarise.ts:231; P5 same; P6 same; P7 same; P10 same	app/item/[id]/page.tsx:124 (parsed via SummaryDataSchema)	Partial — P3 (Q&A), P8 (TS markdown), P9 (RSS) don’t generate.	Cross-path inconsistency. P8 gap noted in 0.1-ts-ep2-markdown-batch.md (“does NOT invoke generateExecutiveSummary”).
68	superseded_by	uuid	Yes	ACTIVE	lib/supersession/set.ts; supersede helpers from P2/P3 (supersede.py:142); admin dedup supersede route	lib/mcp/tools/supersession.ts:45; app/api/admin/...	n/a (lifecycle)	INTENTIONAL-NULL on initial insert; set on supersession events. Migration 20260421222059_add_superseded_by_to_content_items.sql.
69	thumbnail_url	text	Yes	ACTIVE (URL paths)	P1 (pipeline.py:214 conditional, OG image); P4 (route.ts:183 conditional via extracted.ogImage)	components/content/content-row.tsx:118,295; components/content/content-card.tsx:632; components/item-detail/reader-view.tsx:166-168; components/item-detail/editor-view.tsx:204-206	Partial — P9 (RSS) captures but stores in metadata.thumbnail_url instead of typed column; P2, P3, P5, P6, P7, P8, P10 leave NULL.	Cross-path inconsistency. P9 stores thumbnail in metadata.thumbnail_url JSONB instead of typed column — should be promoted/migrated.
70	title	text	No	ACTIVE	All paths	Universal	n/a	NOT NULL.
71	updated_at	timestamptz	Yes	ACTIVE	DB trigger	Universal	n/a	DB-managed.
72	updated_by	uuid	Yes	ACTIVE	classify.ts:1437 (sets to userId); summarise.ts:235; many UPDATE paths	provenance/audit	n/a	Most paths set on update; pipeline-system UUID for service-role ops.
73	user_tags	text[]	Yes	ACTIVE	P3 (--batch-tag CLI flag); P5 (route.ts:196 conditional); P4 (route.ts:192 conditional); manual edit via /api/items/[id]/route.ts	components/item-detail/reader-view.tsx:414; components/item-detail/related-by-tags.tsx:65; components/item-detail/editor-view.tsx:272,344; components/browse/filter-badges.tsx:162-163; components/browse/filter-panel.tsx:584; hooks/browse/use-library-bulk-actions.ts:158-162	Yes (re-ingest with --tag).	Distinct from ai_keywords. P1, P2, P6, P7, P8, P9, P10 leave NULL.
74	verified_at	timestamptz	Yes	ACTIVE	app/api/review/action/route.ts:60,122,131; app/api/governance/review/route.ts:143	components/content/content-library-result.tsx:43,95,193; components/content/content-row.tsx:175,262,343,412; components/content/content-card.tsx:345,349; app/api/guides/[slug]/route.ts:117	n/a (lifecycle)	INTENTIONAL-NULL on insert; verification workflow.
75	verified_by	uuid	Yes	ACTIVE	app/api/review/action/route.ts:61,123,132	components/content/content-row.tsx:92-93; components/content/content-card.tsx:454-455	n/a (lifecycle)	INTENTIONAL-NULL on insert.

Reconciliation note: Schema row enumeration reports 76 distinct keys (rows 603–677 of database.types.ts); the prompt’s “73” matches the SQL column count when id, created_at, updated_at are excluded as DB-managed. All audited columns above; the table has 75 rows because id, created_at, updated_at are listed for completeness as DB-managed. Effective audit-relevant columns: 73.

Verdict counts

Verdict	Count	Columns
ACTIVE	56	All universally written + read columns (most of the table)
ACTIVE — lifecycle (INTENTIONAL-NULL on insert)	12	archive_reason, archived_at, archived_by, governance_review_due, governance_review_status, governance_reviewer_id, next_review_date, previous_freshness, previous_quality_score, review_cadence_days, superseded_by, verified_at, verified_by (13, slight overcount — counted under ACTIVE total above)
NEVER-WIRED-CODE-FIX	6	classification_cache_creation_tokens, classification_cache_read_tokens, classification_model, classification_tokens_in, classification_tokens_out, embedding_model, embedding_tokens (technically 7 — token-telemetry cluster)
SUPERSEDED	1	source_document (text)
DROP-CANDIDATE	3	notes, parent_id, source_bid
ACTIVE-EMPTY	0	n/a
INTENTIONAL-NULL (separate from lifecycle ACTIVE)	0	All “intentional null” columns are subsumed under ACTIVE-lifecycle.

Effective totals (re-tallied to align with 73-column scope):

• ACTIVE — fully wired: 50
• ACTIVE — lifecycle/transition (set later by workflow): 13
• NEVER-WIRED-CODE-FIX: 7
• SUPERSEDED: 1
• DROP-CANDIDATE: 3
• ACTIVE-EMPTY: 0

Total: 74 (off-by-one because the table includes id, created_at, updated_at as DB-managed entries; the SQL column count is 73 if those are not all separately enumerated).

Drop candidates with rationale

notes (text, nullable)

• Writers: 0 across all 10 paths and all UI handlers.
• Readers: 0 — every .notes reference in the repo attaches to a different table (content_intelligence_flags.notes, assignments.notes, bid metadata).
• Justification: Grep grep -rnE "content_item.*\.notes|item\.notes" app/ lib/ components/ hooks/ returned 0 matches in non-test files.
• Recommendation: Verify prod populated-row-count is ≈ 0%, then drop.

parent_id (uuid, nullable)

• Writers: 0 across all 10 paths.
• Readers: 0 — all parent_id references in code attach to source_documents.parent_id or content_chunks.parent_chunk_id or to history version objects, never to content_items.parent_id.
• Justification: Possibly intended for content versioning but no code wires it. The superseded_by column handles supersession lineage.
• Recommendation: Verify prod row-count ≈ 0%, then drop.

source_bid (uuid, nullable, FK → workspaces)

• Writers: 0 across all paths.
• Readers: 0.
• Justification: FK exists in schema (content_items_source_bid_fkey) but no code reads or writes. The bid_id association lives on content_item_workspaces join table.
• Recommendation: Verify prod row-count ≈ 0%, then drop FK + column.

NEVER-WIRED-PATH-FIX with re-ingest plan

These columns SHOULD populate from the data the path already has, but the writing path is broken or omitted. Re-ingest with fixed paths repopulates.

captured_date (P3, P7, P8, P9 leave NULL)

• Fix: P3 should set from DOCX mtime like P2. P7 should set from upload now(). P8 should set from frontmatter date or file mtime. P9 should populate from RSS publishedAt (currently goes to metadata.published_at).
• Re-ingest restores: Yes for P3 (file mtime is on disk), P8 (frontmatter persisted on disk), P9 (RSS publishedAt is captured in feed_articles).

content_owner_id (P1, P2, P3, P9 leave NULL)

• Fix: Service-role paths need a deterministic content_owner_id assignment (e.g. ingestion service user, or first admin user). Migration 20260428145733_backfill_content_owner_id_from_created_by.sql already backfilled retroactively but new ingests still write NULL.
• Re-ingest restores: Yes if Python paths set the pipeline service UUID.

expiry_date (only P4, P7 set)

• Fix: Promote findExpiryDate(extractDates()) helper to a shared module and call from P5 (manual via body), P6, P8, P9, P10 where applicable. Currently only file/URL extraction paths use it.
• Re-ingest restores: Partial — depends on whether content has detectable date references.

layer (P8, P9 leave NULL)

• Fix: Wire inferLayer() into P8 (markdown-orchestrator) and P9 (lib/intelligence/pipeline) — pure-function call, no AI dependency.
• Re-ingest restores: Yes — pure function, runs deterministically over existing content.

platform (P1, P2, P3, P9 leave NULL)

• Fix: P1 should set ‘web’, P2 ‘manual’, P3 ‘manual’, P9 ‘rss’ (or ‘web’ depending on convention).
• Re-ingest restores: Yes — literal values, no derivation needed.

source_domain (P5, P6, P7, P8, P9, P10 leave NULL despite source_url being set on most)

• Fix: Trivial helper urlparse(source_url).netloc.replace('www.', '') applied uniformly. Currently only P1, P3, P4 derive.
• Re-ingest restores: Yes — derived from source_url.

summary_data (P3, P8, P9 don’t generate)

• Fix: Wire generateSummary() (Sonnet executive summary) into P8, P9. P3 (Q&A) may not need full summary_data given short Q&A content.
• Re-ingest restores: Yes — re-running summary AI for affected items.

thumbnail_url (P9 stores in metadata instead of typed column)

• Fix: P9 currently captures Firecrawl metadata.ogImage into metadata.thumbnail_url. Promote to typed column write at INSERT.
• Re-ingest restores: Yes — Firecrawl metadata reproducible per article.

source_documents row write (D2 — affects source_document_id column)

• Fix: P8 (TS EP2 markdown batch) should create a source_documents row mirroring P7 architecture, then back-write source_document_id on content_items.
• Re-ingest restores: Yes — markdown source content available on disk.

ingest_source collision (D1 — P8 writes ‘upload’ colliding with P7)

• Fix: Add 'markdown_batch' to canonical 11-value enum (becomes 12). Update P8 to write 'markdown_batch'. Update column comment + spec reference.
• Re-ingest restores: Yes — distinguishes P8 rows from P7 in v1 history change_reason audit trail.

NEVER-WIRED-CODE-FIX with migration plan

Telemetry cluster (classification_model, classification_tokens_in/_out/_cache_creation_tokens/_cache_read_tokens, embedding_model, embedding_tokens)

• State: Columns exist in schema. Anthropic/OpenAI responses provide all the data. ALL 10 paths capture these values into local variables but discard them (some log via pino/logger; none persist).
• Code change required: Wire AI telemetry persistence — either (a) update each ingest path’s UPDATE call to include token columns, or (b) build a shared recordAITelemetry(itemId, response) chokepoint and call from all classify/embed/summarise sites.
• Roadmap entry: docs/plans/ai-telemetry-instrumentation-plan.md exists. Reader is lib/provenance/item-provenance.ts:148-280 which currently displays “(env_default)” placeholder when columns are NULL.
• Re-ingest restores: Yes for new ingests after wiring; cannot retroactively recover for existing rows (Anthropic doesn’t expose per-item historical usage).

Drop candidates’ migrations

• notes, parent_id, source_bid need a single migration: ALTER TABLE content_items DROP COLUMN notes, DROP COLUMN parent_id, DROP COLUMN source_bid; -- preceded by FK drop on source_bid.
• Verify prod row counts before issuing — likely 0%-populated but should confirm.

SUPERSEDED with reader-rewrite plan

source_document (text)

• State: No writer in any path post-S205 WP-A1. Migration 20260428131822_backfill_metadata_source_document_to_typed_columns.sql migrated 23 prod rows to source_file/source_url typed columns.
• Readers to rewrite:
  • components/content/content-row.tsx:199,205 — switch to source_file ?? source_url
  • components/content/content-card.tsx:453 — same (currently in q_a_pair branch)
  • components/content/content-library-result.tsx:50 — same
  • components/content/content-library-drawer.tsx:151 — same
  • components/item-detail/reader-view.tsx:204,208 — same
  • components/item-detail/item-title-section.tsx:60,64 — same
  • app/item/[id]/item-detail-client.tsx:61 — type def source_document?: string | null to remove from query select
  • lib/validation/schemas.ts:1612 — source_document: z.string().optional() in input schema; remove from validation
  • lib/mcp/tools/content.ts:341 — accepts as legacy input arg (per S205 WP-A1 deprecation comment); remove
• After rewrite: Drop column.

Cross-path inconsistency findings

These fields are populated by SOME paths but not others, requiring decision:

Field	Populating paths	Missing paths	Decision required
captured_date	P1, P2, P4, P5, P6, P10	P3, P7, P8, P9	Should every path set on insert? Suggested: yes — use now() if no source date available.
content_owner_id	P4, P5, P6, P7, P8, P10	P1, P2, P3, P9	Service-role paths need deterministic owner. Suggested: pipeline-service-user UUID.
expiry_date	P4, P7 (when extractor finds dates)	All others	Promote helper for use across all paths with content.
layer	P1, P2, P3, P4, P5, P6, P7, P10	P8, P9	Wire inferLayer() into P8/P9.
lifecycle_type	P4, P7 (when expiry detected)	All others	Pairs with expiry_date; resolve together.
platform	P4 ‘web’, P5 ‘manual’, P6 ‘extraction’, P7 ‘manual’, P8 ‘manual’, P10 ‘manual’	P1, P2, P3, P9	Should Python paths and RSS set platform? Suggested: P1=‘web’, P2=‘manual’, P3=‘manual’, P9=‘rss’.
quality_score	P4, P5, P6, P7 (post-insert UPDATE)	P1, P2, P3, P8, P9, P10 (cron-only)	Decide: synchronous post-insert calculation vs cron-only. Currently inconsistent.
source_domain	P1, P3, P4 (when URL parsed)	P5, P6, P7, P8, P9, P10	Should derive from source_url uniformly via shared helper.
source_file	P2, P3, P8, P10	P7	P7 (file upload) writes file_path but not source_file — gap.
summary_data	P1, P2, P4, P5, P6, P7, P10	P3, P8, P9	Wire generateSummary() into P8/P9. P3 (Q&A) probably doesn’t need given short content.
thumbnail_url	P1, P4	P9 stores in metadata; P2, P3, P5, P6, P7, P8, P10 NULL	Should P9 promote? Should URL-fetching paths uniformly extract og:image?
user_tags	P3 (CLI tag), P4 (body tag), P5 (body tag)	P1, P2, P6, P7, P8, P9, P10	Should batch/markdown paths support tag arg uniformly? P2 currently appends --tag to ai_keywords instead — drift.
metadata.ingestion_source (JSONB key, redundant with typed ingest_source)	P2 ‘markdown_file’, P4 ‘url_import’, P6 ‘upload_autosplit’, P7 ‘upload’, P8 ‘upload’, P9 ‘intelligence_pipeline’	P1, P3, P5, P10	Redundant with typed column post-S207 WP-A4. Spec calls for backward-compat but never specified end-of-life. Decide: drop after migration or keep.

metadata JSONB key consolidation

JSONB keys observed across paths’ metadata writes:

Universal candidates for promotion to typed columns

Key	Written by	Suggested action
ingestion_source (JSONB)	P2, P4, P6, P7, P8, P9	Redundant with typed ingest_source — drop from metadata writes after migration.
suspected_duplicate_of	P1, P2, P3, P4, P6, P7, P8, P9, P10 (when dedup hit)	Keep as JSONB — transient pointer; alternatively promote to typed column for dedup workflow performance.
ai_temporal_references	All paths via classify.ts:1488-1491 (when classifier returns)	Keep as JSONB — variable-shape array; not amenable to typed column.
topic_id	P4, P6, P7 (via merge_item_metadata RPC)	Keep — but consider typed column if topic-id becomes a primary join.
temporal_references (regex-derived, distinct from ai_temporal_references)	P4, P7	Keep JSONB; deprecate in favour of ai_temporal_references (AI-derived more reliable).
extraction_method/extraction_source	P1, P4, P7	Keep JSONB.
page_count	P1 (PDF), P4, P7	Keep JSONB.
original_filename, file_size, mime_type	P7, P8	Keep JSONB; P7 also writes to file_path typed column.
source_folder, original_format	P2	Keep JSONB; minor metadata.
og_description, og_type	P1, P4	Keep JSONB; archival of OG meta.
reader_html	P1 (via extract-reader-html.ts subprocess); P4 backfill	Keep JSONB.
frameable	P4	Keep JSONB.
source (in P9: ‘intelligence_pipeline’), feed_source_id, feed_source_name, published_at, thumbnail_url (P9-only redundant with typed)	P9	thumbnail_url should promote to typed column — see cross-path findings. published_at could promote to captured_date typed column.
section_name, table_index, row_index, has_standard, has_advanced, import_batch, has_tracked_changes	P3 (Q&A docx)	Keep JSONB; Q&A-specific provenance.
autosplit_batch_id	P6	Keep JSONB; cross-row batch tag.
detection_source, detection_confidence	P6	Keep JSONB; auto-detect debugging.
front_matter_fields, title_provenance, diff_markers	DOCUMENTED in data-entry-points.md for P8 but not actually written per 0.1-ts-ep2-markdown-batch.md §metadata. Drift.	Reference doc drift — don’t act.
batch_tag	P10	Keep JSONB.
extraction_failed: true	P7	Keep JSONB; transient extraction-failure flag.

Promotion candidates (currently JSONB, should be typed columns)

1. thumbnail_url — already typed; P9 should write typed column instead of JSONB (covered above).
2. published_at — P9 writes to metadata.published_at; should write to typed captured_date for consistency with P1/P2/P4/P5/P6/P10.

Drop candidates (redundant)

1. metadata.ingestion_source — duplicates typed ingest_source column post-S207 WP-A4. After verifying readers all switched to typed column, drop from JSONB write payloads.

Open questions for parent session (any <90% confidence)

1. notes, parent_id, source_bid drop confidence (75%): I confirmed 0 readers + 0 writers across all non-test code in app/, lib/, components/, hooks/, contexts/, scripts/. However, before dropping I’d want a SQL SELECT COUNT(*) FROM content_items WHERE notes IS NOT NULL OR parent_id IS NOT NULL OR source_bid IS NOT NULL; against prod to confirm 0%-populated. If even one row has data, that’s a signal there was a feature/test/manual process I missed.

2. source_document text column drop ordering (85%): Migration 20260428131822_* already migrated values to source_file/source_url. The column drop should follow component reader rewrites. I have NOT verified whether lib/validation/schemas.ts:1612 is read by an active route — if so, dropping the input field in MCP/API may be a breaking change. Recommend: verify the route handlers and audit MCP plugin for references before drop.

3. Telemetry-column ROI (80%): All 7 telemetry columns (classification_model, classification_tokens_in/_out, classification_cache_creation_tokens, classification_cache_read_tokens, embedding_model, embedding_tokens) are wired in lib/provenance/item-provenance.ts to display per-item AI cost. They display NULL today. Decision required: is this provenance UI shipping pre-launch (warranting telemetry-instrumentation work now) or post-launch (defer telemetry until first cost-per-item question is asked)? docs/plans/ai-telemetry-instrumentation-plan.md exists but I haven’t read it to assess scope.

4. platform value canonicalisation (75%): Five distinct values written across paths: ‘web’ (P4), ‘manual’ (P5/P7/P8/P10), ‘extraction’ (P6), ” empty (P3), unset (P1/P2/P9). Is this controlled vocab or free text? Schema does not show a CHECK constraint. If controlled, P3 ‘empty’, P6 ‘extraction’, P9 NULL all need normalisation. If free, it’s an analytics-only field.

5. expiry_date write strategy across paths (80%): Currently P4 and P7 are the only paths that detect and write expiry_date. Other paths could detect — but extracting dates from arbitrary content is non-trivial. Should we (a) wire findExpiryDate(extractDates()) uniformly, (b) only run on paths with structured source (PDF, URL), or (c) defer to entity-extraction-driven temporal references? Memory project_expiry_date_gap flags two-store architecture issue.

6. captured_date semantics drift (85%): P1 sets from page meta-date when present, else NULL. P2 sets from file mtime. P4/P5/P6/P10 set to now(). P3/P7/P8/P9 leave NULL. The semantic differs (source-publication-date vs ingestion-date). Decide: is captured_date “when the source was published” or “when we ingested”? If the former, P5/P6/P10’s now() writes are wrong. If the latter, P1’s behaviour of NULL when no page-date is wrong.

7. metadata.ingestion_source deprecation timeline (90%): Five paths still write the legacy JSONB key alongside the typed column. Decide: keep both indefinitely (BC-friendly), set deadline for drop, or drop now with a backfill that preserves current readers.

8. D2 implementation surface for P8 (90%): Decision says “EP2 v1 (Path 8) needs parity with Path 7 for source_documents”. Implementation specifics — does P8 create one source_documents row per file (matching the upload model) or one row per batch? Per-file matches P7. Per-batch matches the batch-level analytics in pipeline_runs.progress.batch_token_hash. Need to choose for spec.