Field-Consumer Dependency Map

DRIFT WARNING — added 2026-05-06. The Phase 0 schema investigation (docs/plans/content-items-cleanup-phase-0-investigation.md) found writer/ reader mappings that are stale or incomplete for several content_items columns (notably the 7 AI-telemetry columns documented as never-written by any of 10 ingest paths, the source_documents/source_document_id wiring gap, and feed_articles.updated_by being written but listed as not-written). Per-path findings cited in docs/plans/phase-0-investigation/0.1-*.md. Phase 0.4 (drift correction) is PAUSED pending Phase 0.6 synthesis output and re-ingest design decision. Treat the dependency tables as directionally-correct but not fully accurate until refresh complete.

Maps which database fields are consumed by which UI components, API routes, MCP tools, hooks, and scripts. Excludes test files. Focus on production consumers only.

Last updated: 05/05/2026 (S223 WP3 — feed_sources §6 extended for S222 W3-A native website scraping: API consumers added for POST/PATCH /api/intelligence/workspaces/:id/sources, FeedSourceCreateSchema async superRefine + parseBodyAsync chokepoint, etag/last_modified web parity); 28/04/2026 (S208 — ingest_source typed column added per S207 WP-A4; content_owner_id ingest-schema wiring noted per S206; S205C priors retained: summary rename; publication_status, superseded_by, review_cadence_days, next_review_date)

Note on Q&A pairs: There is no separate qa_pairs table. Q&A items are stored in content_items with content_type = 'q_a_pair' and use the answer_standard / answer_advanced columns.

Column Selection Constants

Two reusable column constants (types/content.ts) govern most reads:

CONTENT_LIST_COLUMNS (used by browse, library, review): id, title, suggested_title, summary, primary_domain, primary_subtopic, content_type, platform, author_name, source_domain, thumbnail_url, captured_date, ai_keywords, classification_confidence, priority, freshness, user_tags, governance_review_status, metadata, verified_at, verified_by, source_document, brief, content, answer_standard, answer_advanced, content_owner_id, quality_score, source_document_id, citation_count, source_file, layer, starred
CONTENT_DETAIL_COLUMNS (extends LIST): adds source_url, file_path, secondary_domain, secondary_subtopic, classification_reasoning, classified_at, summary_data, created_at, updated_at, created_by, updated_by, source_bid, detail, reference, governance_review_status, governance_review_due, governance_reviewer_id, source_document_id, expiry_date, lifecycle_type

Source of truth: types/content.ts lines 207-228. The S164b ai_summary → summary rename (content_items.summary) is fully reflected; feed_articles.ai_summary is intentionally kept as a separate column.

1. content_items

The core table with ~60 columns. By far the most-consumed table in the system.

Identity and Core Content

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`id`	All content components	All content routes	All content tools	All content hooks	All scripts	Primary key, universal
`title`	ContentCard, ReviewCard, ReaderView, EditorView, ItemDetail, RelatedByTags, RelatedByEntities, SearchResult	/api/items, /api/review/queue, /api/governance/review	get_content_item, search_knowledge_base, get_quality_briefing, all resources	useBrowseData, useLibraryData, useItemDetailData, useReviewQueueData	batch-reclassify, batch-generate-summaries, kb-search	Always present
`suggested_title`	Same as title (fallback display)	Same as title	Same as title	Same as title	Same as title	AI-generated; displayed when title is generic
`content`	EditorView, ReaderView, ContentBody	/api/items/[id], /api/review/queue, /api/upload	get_content_item, search_knowledge_base	useBrowseData, useItemDetailData	batch-reclassify, batch-generate-summaries	Full raw text
`content_type`	ContentCard, FilterPanel, ReviewCard	/api/items, /api/review/queue, /api/review/cadence	get_content_item, get_content_items, search_knowledge_base	useBrowseData, useLibraryData	All ingestion scripts	CHECK constraint

Classification

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`primary_domain`	ContentCard, FilterPanel, ReviewCard, RelatedByEntities, OwnedContentHealth	/api/review/queue, /api/governance/review, /api/cron/freshness-transitions, /api/cron/quality-score	get_content_item, get_quality_briefing, kb://coverage, search_knowledge_base	useBrowseData, useLibraryData	batch-reclassify, calibrate-coverage-thresholds	Domain filter everywhere
`primary_subtopic`	ContentCard, FilterPanel	/api/review/queue	get_content_item, get_quality_briefing	useBrowseData, useTopicLayerContent	batch-reclassify
`secondary_domain`	ItemDetail	/api/review/queue		useItemDetailData	batch-reclassify	Detail view only
`secondary_subtopic`	ItemDetail	/api/review/queue		useItemDetailData	batch-reclassify	Detail view only
`classification_confidence`	ContentCard, ReviewCard, AiProcessingIndicators	/api/review/queue	get_content_item, get_quality_briefing	useBrowseData	batch-reclassify	0-1 range
`classification_reasoning`	ItemDetail			useItemDetailData	batch-reclassify	Detail view only
`classified_at`	ItemDetail			useItemDetailData	batch-reclassify	Detail view only

AI-Generated Fields

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`summary`	ContentCard, ReviewCard, ReaderView	/api/items, /api/review/queue	get_content_item, search_knowledge_base, get_quality_briefing, kb://qa/{id}	useBrowseData, useLibraryData	batch-generate-summaries, kb-search	Claude-generated; renamed from `ai_summary` S164b
`ai_keywords`	ContentCard, FilterPanel	/api/items	get_content_item, search_knowledge_base	useBrowseData, useLibraryData	batch-reclassify-keywords, batch-reclassify	Array of strings
`embedding`		/api/items/[id] (for related items)	search_knowledge_base (via RPC)		kb-search, batch scripts	vector(1024); never sent to UI directly
`summary_data`	ItemDetail (structured summary)			useItemDetailData	batch-generate-summaries	JSONB; structured executive/detailed/takeaways from generateSummary

Source and Provenance

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`source_url`	ItemDetail, ReaderView	/api/items/[id]	get_content_item	useItemDetailData	ingestion scripts	Original URL
`source_domain`	ContentCard	/api/items		useBrowseData		Extracted domain
`source_document`	ContentCard	/api/review/queue		useBrowseData		Import origin name
`source_document_id`	ItemDetail, SourceDocumentInfo	/api/source-documents/[id]	get_document_versions, get_document_diff	useItemDetailData	backfill-source-documents	FK to source_documents
`source_bid`	ItemDetail			useItemDetailData		FK to workspaces; bid-discovered content
`source_file`		/api/items		useLibraryData (source file list)		Promoted from metadata
`platform`	ContentCard, FilterPanel	/api/items	get_content_item	useBrowseData	ingestion scripts	CHECK constraint
`author_name`	ContentCard	/api/items		useBrowseData	ingest scripts
`captured_date`	ContentCard	/api/items		useBrowseData		Publication date
`file_path`	ItemDetail	/api/items/[id]/files		useItemDetailData		Uploaded file path
`thumbnail_url`	ContentCard	/api/review/queue		useBrowseData

Progressive Depth Layers

Field	UI Components	API Routes	Hooks	Scripts	Notes
`brief`	ContentCard (preview)	/api/items, /api/review/queue	useBrowseData	backfill-layers	Progressive depth
`detail`	ItemDetail		useItemDetailData	backfill-layers	Detail view only
`reference`	ItemDetail		useItemDetailData	backfill-layers	Detail view only
`layer`	ContentCard, FilterPanel	/api/items, /api/layers/[id]	useBrowseData, useTopicLayerContent	backfill-layers	Matches layer_vocabulary.key

Q&A Pair Fields

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`answer_standard`	QaRow, QaAnswerDisplay, EditorView	/api/items	search_qa_library, kb://qa/{id}	useBrowseData, useQaEditMode	import_bid_library.py	Standard answer text
`answer_advanced`	QaRow, QaAnswerDisplay, EditorView	/api/items	search_qa_library, kb://qa/{id}	useBrowseData, useQaEditMode	import_bid_library.py	Advanced answer text

Freshness and Lifecycle

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Notes
`freshness`	ContentCard, FilterPanel, ReviewCard	/api/review/queue, /api/cron/freshness-transitions, /api/freshness/calculate	get_content_item, get_freshness_report, get_quality_briefing	useBrowseData	CHECK: fresh/aging/stale/expired
`previous_freshness`		/api/cron/freshness-transitions	get_quality_briefing		For transition tracking
`freshness_checked_at`	GovernanceSection (settings)	/api/freshness/calculate			Last calculation time
`lifecycle_type`	ItemDetail	/api/cron/freshness-transitions		useItemDetailData	evergreen/date_bound/regulation/bid_discovered
`expiry_date`	ItemDetail	/api/cron/freshness-transitions	get_expiring_content	useItemDetailData	Hard expiry date

Publication and Supersession (S199–S202 §5.2 + S200 §5.5)

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Notes
`publication_status`	ContentCard, ReviewCard, EditorView, PublicationReviewQueue, PublicationBulkActionBar (S220 §5.3)	/api/items POST + PATCH (`/api/items/[id]`), /api/review/queue (filter), /api/review/publication-bulk-action POST (S220 §5.3 bulk-approve / bulk-return-to-draft; body schema `PublicationBulkActionBodySchema`)	get_content_item, get_quality_briefing, governance.update, supersession formatters	useReviewQueueData	NOT NULL DEFAULT `'published'`. CHECK: `draft \| in_review \| published \| archived`. Canonical lifecycle column (S202 §5.2 Phase 2). State-machine guard via `validatePublicationTransition()`. Bulk surface (§5.3) iterates per-item PATCH semantics with `change_reason='bulk_approve'`/`'bulk_return_to_draft'` audit literals; cap=50 / rate=20 req/min ratified S217 D-3+D-8.
`superseded_by`	SupersedeContentDialog, ContentCard	/api/items/[id] PATCH (clears on publish), /api/cron/review-cadence	mcp supersession.ts, content tools (when set), governance.ts	useReviewActions	FK `content_items.id`. Self-referential. Set when an item is superseded by a newer version (S199 21/04/2026 migration `20260421222059`).
`review_cadence_days`	GovernanceSection, ItemDetail (read)	/api/cron/review-cadence, /api/governance/review	governance.ts (cadence renewal helpers)		Numeric review interval per item (S200 WP5 §5.5 Phase 1; backfilled 27/04/2026 in three cohorts: regulatory 365d, Q&A 180d, methodology/case-study 365d).
`next_review_date`	ItemDetail (badge), GovernanceSection	/api/cron/review-cadence, /api/governance/review	governance.ts (cadence renewal helpers)		Computed forward-looking review deadline from `review_cadence_days`. Auto-renewed via `lib/governance/cadence-renewal.ts`.

Quality and Governance

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Notes
`quality_score`	ContentCard, QualityScore badge	/api/cron/quality-score, /api/review/queue	get_quality_briefing, get_quality_actions	useBrowseData	0-100 computed
`previous_quality_score`		/api/cron/quality-score	get_quality_briefing		Trend tracking
`quality_score_updated_at`		/api/cron/quality-score
`governance_review_status`	ContentCard, ReviewCard	/api/governance/review, /api/review/queue, /api/cron/freshness-transitions	get_content_item, update_governance_status	useBrowseData, useReviewQueueData	Workflow state
`governance_review_due`	ItemDetail	/api/governance/review		useItemDetailData	Deadline
`governance_reviewer_id`	ItemDetail	/api/governance/review		useItemDetailData	Assigned reviewer
`verified_at`	ContentCard, ReviewCard	/api/review/queue, /api/cron/freshness-transitions		useBrowseData, useReviewActions	SME verification
`verified_by`	ContentCard	/api/review/queue		useBrowseData	Who verified
`priority`	ContentCard	/api/items	get_content_item	useBrowseData	high/medium/low

Ownership and Audit

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`content_owner_id`	ContentCard, OwnedContentHealth	/api/governance/review, /api/cron/freshness-transitions, /api/items POST, /api/upload, /api/ingest/url, /api/items/[id] PATCH	assign_content_owner, mcp create_content_item	useBrowseData		FK to auth.users. Ingest schemas widened S206 (5 entry points) to accept caller-provided owner; `lib/auth/owner-default.ts` falls back to caller.
`ingest_source`		/api/items POST, /api/upload, /api/ingest/url, /api/intelligence (worker), /api/bids/[id]/outcome/integrate	mcp create_content_item		scripts/ingest.py, scripts/ingest_markdown.py	Pipeline attribution. Drives `auto_v1_on_insert` trigger’s CASE for `change_reason` (replaces S207-deleted v1 app writes). 11.6% NULL pre-OPS-41 backfill; now 0% post-S209 (verified 2026-05-06: 617/617 populated).
`created_at`	ItemDetail	/api/items, /api/review/queue		useItemDetailData
`updated_at`	ItemDetail	/api/items, /api/review/queue, /api/cron/freshness-transitions		useItemDetailData
`created_by`		/api/items, /api/upload
`updated_by`		/api/governance/review

User-Editable Metadata

Field	UI Components	API Routes	Hooks	Scripts	Notes
`user_tags`	ContentCard, FilterPanel, RelatedByTags	/api/items, /api/review/queue	useBrowseData	cleanup-tags	Array of strings
`starred`	ContentCard (star toggle)	/api/items (toggle_star RPC)	useBrowseData		Boolean flag
`notes`	ReviewCard, EditorView, ReviewHistorySection	/api/items	useReviewActions		Free-form
`metadata`	Various (indirect via JSONB keys)	Many routes	useBrowseData	Many scripts	JSONB; see §35 in schema ref
`citation_count`	ContentCard	/api/items	useBrowseData		Trigger-maintained

Archival

Field	API Routes	MCP Tools/Resources	Notes
`archived_at`	/api/items/[id]/archive, /api/cron/quality-score (filter)	All tools (filter `is null`)	Soft delete
`archived_by`	/api/items/[id]/archive
`archive_reason`	/api/items/[id]/archive

Low/No Consumer Fields

Field	Consumers	Notes
`content_text_hash`	`lib/dedup/content-dedup.ts` only	Dedup detection; no UI or API consumer
`parent_id`	`app/api/upload/route.ts`, source-document components	Self-referential FK; lightly used
`source_bid`	`types/content.ts` (CONTENT_DETAIL_COLUMNS), `app/item/[id]/item-detail-client.tsx`	Only 2 non-test consumers

2. entity_mentions

Entity extraction results forming the context graph.

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`id`	EntityBadges	/api/entities/[canonical_name]/*	get_entity_relationships		normalise-entities	PK
`content_item_id`	RelatedByEntities	/api/entities/[canonical_name], /api/certifications	get_entity_relationships, get_certification_status	useBrowseData (entity filter)	backfill-entities, propagate-cert-metadata	FK to content_items
`entity_type`	EntityBadges, EntityDetailPanel	/api/entities/[canonical_name], /api/certifications	get_entity_relationships, get_certification_status, get_quality_briefing		normalise-entities	CHECK constraint; 12 types
`entity_name`		/api/entities/[canonical_name]			normalise-entities	Original text
`canonical_name`	EntityBadges, RelatedByEntities	/api/entities/[canonical_name], /api/certifications	get_entity_relationships, get_certification_status, get_quality_briefing		normalise-entities, propagate-cert-metadata	Normalised form
`confidence`	EntityBadges	/api/entities/[canonical_name]				0-1 range
`context_snippet`		/api/entities/[canonical_name]			backfill-context-snippets	Excerpt
`entity_type_override`	EntityDetailPanel	/api/entities/[canonical_name]/type, /api/certifications	get_certification_status			Manual type correction
`metadata`		/api/entities/[canonical_name]/metadata, /api/certifications, /api/cron/freshness-transitions	get_certification_status, get_quality_briefing, get_expiring_content		propagate-cert-metadata, backfill-temporal-bridge	JSONB; certification expiry, version
`normalisation_version`					normalise-entities	Batch tracking
`created_at`					normalise-entities

Dead/Low-Use Fields

All fields have at least one consumer. normalisation_version is script-only.

3. workspaces

Generic containers for bids and KB sections.

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`id`	WorkspaceCard, WorkspaceSelector, WorkspaceDetailSheet	/api/workspaces, /api/bids/[id]	kb://bid/{id}, list_active_bids, show_bid_dashboard	useQuickAssign, useQaProvenance	seed-bid-test-data	PK
`name`	WorkspaceCard, WorkspaceSelector, WorkspaceDetailSheet	/api/workspaces, /api/bids/[id]	kb://bid/{id}, list_active_bids, show_bid_dashboard
`description`	WorkspaceDetailSheet	/api/workspaces	kb://bid/{id}
`color`	WorkspaceCard	/api/workspaces				Hex colour
`icon`	WorkspaceCard	/api/workspaces				Icon identifier
`type`	WorkspacesPage (count by type)	/api/workspaces	kb://bid/{id}			bid or kb_section
`status`		/api/workspaces, /api/bids/[id]/outcome	list_active_bids			Workflow state
`domain_metadata`	WorkspaceCard (buyer, deadline), BidListCard, BidContextProvider, ActiveBidsSection	/api/workspaces, /api/bids/[id], /api/bids/[id]/outcome	list_active_bids, show_bid_dashboard		seed-bid-test-data	JSONB; bid details (buyer, deadline, value, etc.)
`is_archived`		/api/workspaces, /api/bids	list_active_bids			Soft delete
`created_at`		/api/workspaces
`updated_at`		/api/workspaces
`created_by`		/api/workspaces
`updated_by`		/api/workspaces

Dead/Low-Use Fields

Field	Consumers	Notes
`updated_by`	Only /api/workspaces GET (selected but not displayed)	Selected but no known UI consumer

4. bid_questions

Extracted tender questions within bid workspaces.

Field	UI Components	API Routes	MCP Tools/Resources	Scripts	Notes
`id`	BidDetailPage, BidQuestionRow	/api/bids/[id]/questions, /api/bids/[id]/responses/*	get_bid_detail, show_bid_dashboard, get_bid_question, kb://bid/{id}	seed-bid-test-data	PK
`project_id`		/api/bids/[id]/questions (filter)	get_bid_detail, show_bid_dashboard		FK to workspaces; legacy name
`question_text`	BidQuestionRow, BidDetailPage	/api/bids/[id]/questions	get_bid_detail, get_bid_question, kb://bid/{id}, search_qa_library		The question itself
`section_name`	BidDetailPage (grouping)	/api/bids/[id]/questions	get_bid_detail, kb://bid/{id}		Section heading
`section_sequence`		/api/bids/[id]/questions (ordering)	get_bid_detail		Sort order
`question_sequence`		/api/bids/[id]/questions (ordering)	get_bid_detail		Sort order
`word_limit`	BidQuestionRow	/api/bids/[id]/questions	get_bid_detail		Max word count
`evaluation_weight`	BidQuestionRow	/api/bids/[id]/questions			Scoring weight
`confidence_posture`	BidQuestionRow, BidDetailPage	/api/bids/[id]/questions, /api/bids/[id]/questions/match	get_bid_detail, kb://bid/{id}, show_bid_dashboard		Match confidence
`matched_content_ids`	BidDetailPage	/api/bids/[id]/questions, /api/bids/[id]/questions/match			KB item matches
`status`	BidQuestionRow, BidDetailPage	/api/bids/[id]/questions	get_bid_detail, kb://bid/{id}, show_bid_dashboard		Workflow state
`has_variants`		/api/bids/[id]/questions			Sub-variants flag
`template_requirement_id`		/api/bids/[id]/templates/*			FK to template_requirements
`assigned_to`	BidQuestionRow	/api/bids/[id]/questions			User assigned
`created_by`		/api/bids/[id]/questions
`created_at`		/api/bids/[id]/questions
`updated_at`		/api/bids/[id]/questions

5. bid_responses

AI-drafted and human-edited responses to bid questions.

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`id`	BidResponseEditor	/api/bids/[id]/responses/[rId]	get_bid_detail	useDraftStream	seed-bid-test-data	PK
`question_id`		/api/bids/[id]/questions (join)	get_bid_detail, show_bid_dashboard			FK to bid_questions
`response_text`	BidResponseEditor, BidResponsePreview	/api/bids/[id]/responses/[rId], /api/bids/[id]/responses/draft*	get_bid_detail, show_bid_dashboard	useDraftStream		Standard response
`response_text_advanced`	BidResponseEditor	/api/bids/[id]/responses/[rId]				Advanced response
`source_content_ids`	BidResponseEditor (citations)	/api/bids/[id]/responses/[rId]				KB source items
`review_status`	BidResponseEditor, BidDetailPage	/api/bids/[id]/responses/[rId], /api/bids/[id]/readiness	get_bid_detail, show_bid_dashboard			draft/review/approved/rejected
`drafted_by`	PerItemTab (provenance)	/api/bids/[id]/responses/draft, /api/provenance/item/[id]		useItemProvenance		`PIPELINE_SYSTEM_USER_ID` = AI-drafted; user UUID = human-drafted
`last_edited_by`		/api/bids/[id]/responses/[rId]
`approved_by`		/api/bids/[id]/responses/[rId]
`metadata`		/api/bids/[id]/responses/draft*		useDraftStream		Drafting metadata
`version`		/api/bids/[id]/responses/[rId]				Response version
`overall_score`	QualityScore, BidContextProvider	/api/bids/[id]/responses/draft*, /api/bids/[id]/readiness		useDraftStream		Quality check score
`created_at`		/api/bids/[id]/responses/[rId]
`updated_at`		/api/bids/[id]/responses/[rId]

6. feed_sources

Intelligence pipeline feed sources. Post-S222 W3-A: feed_sources gains direct API consumers for the §2.3.4 native-website-scraping flow.

Field	API Routes	Scripts	Notes
`id`	`PATCH /api/intelligence/workspaces/:id/sources/:sourceId` (route param)		PK; accessed via `get_due_feed_sources` RPC for pipeline polling
`name`	POST + PATCH `/api/intelligence/workspaces/:id/sources` (FeedSource schemas)		Writable via UI source-add flow
`url`	POST + PATCH `/api/intelligence/workspaces/:id/sources`		Feed URL. POST for `source_type='web'` triggers `FeedSourceCreateSchema.superRefine` async `validateWebUrl` HEAD pre-flight (S222 W3-A)
`source_type`	POST `/api/intelligence/workspaces/:id/sources` (`'rss' \| 'web' \| 'api'`)		Determines downstream pipeline branch + which validator runs at insert time
`workspace_id`	POST + seed-starter-pack route (server sets from URL param)		FK to workspaces
`is_active`	POST + PATCH `/api/intelligence/workspaces/:id/sources` (default `true`)
`polling_interval_minutes`	POST + PATCH `/api/intelligence/workspaces/:id/sources` (default 30)		Bounds: 5..1440
`last_polled_at`		`lib/intelligence/pipeline.ts`	Updated by pipeline
`last_polled_status`		`lib/intelligence/pipeline.ts`	Updated by pipeline
`last_polled_error`		`lib/intelligence/pipeline.ts`	Updated by pipeline
`consecutive_failures`		`lib/intelligence/pipeline.ts`	Updated by pipeline
`article_count`		`lib/intelligence/pipeline.ts`	Incremented by pipeline
`etag`		`lib/intelligence/pipeline.ts`	RFC 7232 conditional request header. Post-S222 W3-A also written for `source_type='web'` (web parity with RSS)
`last_modified`		`lib/intelligence/pipeline.ts`	RFC 7232 conditional request header. Post-S222 W3-A web parity (see `etag` row)
`created_at`
`created_by`	POST `/api/intelligence/workspaces/:id/sources` (server-resolved auth user)
`updated_at`

Key finding: Post-S222 W3-A, feed_sources has direct API consumers for create/update via /api/intelligence/workspaces/:id/sources (POST + PATCH) and the /seed-starter-pack flow. The intelligence pipeline (lib/intelligence/pipeline.ts) remains the polling consumer via the get_due_feed_sources RPC; pipeline writes last_polled_*, etag, last_modified, consecutive_failures, article_count. The split is: routes write user-supplied fields (name/url/source_type/polling_interval_minutes/is_active); pipeline writes runtime telemetry (everything else).

Validation note (S222 W3-A): FeedSourceCreateSchema in lib/validation/schemas.ts:1071-1095 runs an async .superRefine that performs an HTTP HEAD pre-flight on source_type='web' rows via validateWebUrl (lib/intelligence/url-validation.ts). Routes consuming this schema MUST use parseBodyAsync from @/lib/validation/index.ts:55+ not the synchronous parseBody — Zod throws “Encountered Promise during synchronous parse” otherwise. The validation-sweep guard (feedback_validation_sweep_safeparse_ban) covers parseBody adoption; parseBodyAsync is the async-superRefine variant of the same chokepoint.

7. feed_articles

Intelligence pipeline article records.

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`id`						PK
`feed_source_id`						FK to feed_sources
`workspace_id`						FK to workspaces
`external_url`						Article URL
`external_id`						RSS GUID
`title`
`raw_content`						Full content
`relevance_score`						AI relevance 0-1
`relevance_category`						high/medium/low/irrelevant
`relevance_reasoning`						AI reasoning
`matched_categories`
`ai_summary`
`prompt_version_id`						FK to feed_prompts
`passed`						Above threshold flag
`published_at`
`content_item_id`						FK to content_items (when promoted)
`ingested_at`
`created_at`
`updated_at`

Key finding: feed_articles also has no direct UI or API route consumers. All access is through lib/intelligence/pipeline.ts. This is entirely pipeline-internal infrastructure. Articles that pass relevance scoring are promoted to content_items and linked via content_item_id.

8. digests

AI-generated change reports.

Field	UI Components	API Routes	Notes
`id`	ChangeReportDetail, ChangeReportList	/api/change-reports/[id], /api/change-reports/list, /api/change-reports/latest	PK
`frequency`	ChangeReportList	/api/change-reports/[id], /api/change-reports/list, /api/change-reports/latest	weekly/daily
`period_start`	ChangeReportDetail	/api/change-reports/[id], /api/change-reports/list, /api/change-reports/latest	Coverage window
`period_end`	ChangeReportDetail	/api/change-reports/[id], /api/change-reports/list, /api/change-reports/latest	Coverage window
`item_count`	ChangeReportList	/api/change-reports/[id], /api/change-reports/list, /api/change-reports/latest
`domain_summaries`	ChangeReportDetail	/api/change-reports/[id], /api/change-reports/list	JSONB
`narrative_summary`	ChangeReportDetail	/api/change-reports/[id], /api/change-reports/list, /api/change-reports/latest	Overall narrative
`generated_at`	ChangeReportList	/api/change-reports/[id], /api/change-reports/list, /api/change-reports/latest
`generated_by`	ChangeReportList	/api/change-reports/[id], /api/change-reports/list, /api/change-reports/latest	Model identifier
`tokens_used`	ChangeReportDetail	/api/change-reports/[id], /api/change-reports/list, /api/change-reports/latest
`item_ids`	ChangeReportDetail	/api/change-reports/[id]	UUID array
`metadata`			Dead field — never selected in any route
`created_at`	ChangeReportList	/api/change-reports/[id], /api/change-reports/list
`created_by`			Dead field — exists in schema, never selected

Dead Fields

Field	Notes
`metadata`	Never selected in any API route or consumed by UI
`created_by`	Never selected in any API route or consumed by UI

9. guides

Domain-based guide definitions.

Field	API Routes	Scripts	Notes
`id`	/api/guides, /api/guides/[slug], /api/guides/[slug]/sections	seed-phew-guides	PK
`slug`	/api/guides (lookup), /api/guides/[slug]	seed-phew-guides	URL-friendly identifier
`name`	/api/guides, /api/guides/[slug]	seed-phew-guides	Display name
`description`	/api/guides, /api/guides/[slug]	seed-phew-guides
`guide_type`	/api/guides, /api/guides/[slug]	seed-phew-guides
`domain_filter`	/api/guides, /api/guides/[slug]	seed-phew-guides	Taxonomy domain link
`icon`	/api/guides, /api/guides/[slug]
`color`	/api/guides, /api/guides/[slug]
`display_order`	/api/guides, /api/guides/[slug]	seed-phew-guides	Sort order
`is_published`	/api/guides, /api/guides/[slug]	seed-phew-guides	Visibility flag
`created_by`	/api/guides, /api/guides/[slug]
`created_at`	/api/guides, /api/guides/[slug]
`updated_at`	/api/guides, /api/guides/[slug]

Key finding: Guides have API route consumers but no direct UI component consumers. They are consumed indirectly via the guide section mapping system (lib/guide-section-mapping.ts) which matches content items to guide sections.

10. guide_sections

Sections within guides, defining expected content structure.

Field	API Routes	Scripts	Notes
`id`	/api/guides/[slug]/sections, /api/guides/[slug]/sections/[sectionId]		PK
`guide_id`	/api/guides/[slug]/sections		FK to guides
`section_name`	/api/guides/[slug]/sections	seed-phew-guides	Section title
`description`	/api/guides/[slug]/sections
`display_order`	/api/guides/[slug]/sections	seed-phew-guides	Sort order
`subtopic_filter`	/api/guides/[slug]/sections		Used in guide-section-mapping.ts
`expected_layer`	/api/guides/[slug]/sections		Used in guide-section-mapping.ts
`content_type_filter`	/api/guides/[slug]/sections		Used in guide-section-mapping.ts
`is_required`	/api/guides/[slug]/sections		Gap analysis flag
`created_at`	/api/guides/[slug]/sections
`updated_at`	/api/guides/[slug]/sections

11. source_documents

Tracks uploaded source documents with version history. Each row is a specific version; the parent_id chain links versions together.

Field	UI Components	API Routes	MCP Tools/Resources	Hooks	Scripts	Notes
`id`	SourceDocumentInfo, SourceDocumentHistory	/api/source-documents/[id], /api/source-documents/[id]/versions, /api/source-documents/[id]/diff, /api/source-documents/[id]/send-to-review, /api/upload	get_document_versions, get_document_diff	useDiffReview	backfill-source-documents	PK
`filename`	SourceDocumentInfo	/api/source-documents/[id], /api/source-documents/[id]/diff	get_document_versions, get_document_diff		backfill-source-documents	Normalised filename
`original_filename`	SourceDocumentInfo	/api/source-documents/[id]				As uploaded
`mime_type`	SourceDocumentInfo	/api/source-documents/[id]				e.g. `application/pdf`
`file_size`	SourceDocumentInfo, SourceDocumentHistory	/api/source-documents/[id]				Bytes
`content_hash`		/api/source-documents/[id], /api/upload (dedup check)			backfill-source-documents	MD5 of raw file bytes
`version`	SourceDocumentInfo, SourceDocumentHistory, DocumentDiffPage	/api/source-documents/[id], /api/source-documents/[id]/diff	get_document_diff			Version number in chain
`parent_id`		/api/source-documents/[id]/diff, /api/upload (re-upload detection)	get_document_diff		backfill-source-documents	FK to previous version (self-referential)
`storage_path`	SourceDocumentInfo	/api/source-documents/[id]				Supabase Storage path
`status`	SourceDocumentInfo, SourceDocumentHistory	/api/source-documents/[id], /api/upload				CHECK: uploaded/processing/processed/failed
`extracted_text`		— (no production reader; permanently NULL on the pipeline path since id-392)			edit-intent sweep (residual write), `l_records.py` producer payload (residual select)	Documented residual — column retirement deferred to the rebase charter; the document body is composed from `content_chunks.content` ordered by `position` (`reference_items.body` fallback on the URL route) via `lib/source-documents/body.ts`
`extraction_metadata`		/api/upload				JSONB; page count, table count
`workspace_id`		/api/source-documents/[id], /api/upload				FK to workspaces
`pipeline_run_id`		/api/upload				FK to pipeline_runs
`uploaded_by`	SourceDocumentInfo, SourceDocumentHistory, DocumentDiffPage	/api/source-documents/[id] (read), /api/ingest/folder-drop, /api/ingest/url (write)				FK to auth.users (id-407). Fill-once writer `lib/source-documents/uploader-attribution.ts`: UPDATE guarded by `uploaded_by IS NULL` so the first human attribution is never rewritten. NULL is the meaningful system-minted value — the diff adapter `lib/diff/adapters/source-document-revision.ts` maps non-null to the resolved display name and NULL to the literal `'System'`. Search RPCs project it under the alias `sd.uploaded_by AS "created_by"` (`20260716120000_id145_37_…sql:406,467`). Stamped caller-side on the MINT path only (`folder-drop` inside the writer-fence hold; `ingest/url` post-`reference_ingest` RPC, degrading into the route’s `warnings[]` channel on failure). The Python walk stays NULL by design (`uploaded_by` is absent from the ON CONFLICT SET list, so a re-walk cannot clobber a human attribution). No backfill of historical rows (DR-093).
`created_at`	SourceDocumentInfo, SourceDocumentHistory, DocumentDiffPage	/api/source-documents/[id], /api/source-documents/[id]/diff
`archived_at`		/api/source-documents/[id]				Soft delete
`archived_by`		/api/source-documents/[id]

Key consumer paths: SourceDocumentInfo and SourceDocumentHistory components fetch via /api/source-documents/[id] and /api/source-documents/[id]/versions. The diff page (app/documents/[id]/diff/page.tsx) queries directly via Supabase. The upload route (/api/upload) creates and updates rows during file processing. MCP tools get_document_versions and get_document_diff read filename, version, and parent chain. Impact analysis (lib/source-documents/source-document-impact.ts) reads id, filename, and parent_id.

12. read_marks

Per-user read tracking. RLS-scoped to user_id = auth.uid().

Field	UI Components	API Routes	Hooks	Notes
`id`		/api/read-marks		PK
`content_item_id`	ReadToggleButton (via context), ItemActionBar, ReaderView	/api/read-marks (GET batch check, POST toggle, DELETE unmark)	useItemDetailData, useProgress	FK to content_items
`read_at`		/api/read-marks	useProgress (streak calculation)	When the item was read
`source`		/api/read-marks (POST)		How the read was recorded: manual/review/digest/bulk
`user_id`		/api/read-marks (RLS scoped)		RLS filter: `auth.uid()`

Key consumer paths: The ReadMarksProvider context (contexts/read-marks-context.tsx) is the central consumer, mounted at the app layout level. It fetches counts via /api/read-marks and provides isRead(), toggleRead(), markRead(), markBulkRead(), and checkReadStatus() to all children. Direct UI consumers are ReadToggleButton, ItemActionBar, and ReaderView (via context). useProgress queries read_marks directly for streak calculation (last 30 days). lib/dashboard.ts and lib/reorient.ts read read_marks for activity metrics. Browse page (app/browse/browse-content.tsx) and digest page (app/change-reports/page.tsx) trigger lazy loading of read mark counts.

13. layer_vocabulary

DB-driven content layer definitions. Consumed via LayerVocabularyProvider context and useLayerVocabulary() hook. Admin-editable in Settings.

Field	UI Components	API Routes	Notes
`id`	LayersSection (admin CRUD)	/api/layers, /api/layers/[id], /api/layers/reorder	PK
`key`	ContentCard, ContentRow, FilterPanel, CoverageLayerFilter, LayerSwitcherNav, LayerSuggestionBanner, UploadTabContent, IngestionSuccessCard, ContentLayerSelector, GuideSection, FileUploadDialog, SectionFormDialog	/api/layers, /api/layers/[id]	UNIQUE; layer identifier (e.g. `sales_brief`)
`label`	ContentCard, ContentRow, FilterPanel, CoverageLayerFilter, LayerSwitcherNav, LayerSuggestionBanner, UploadTabContent, IngestionSuccessCard, ContentLayerSelector, GuideSection, FileUploadDialog, SectionFormDialog	/api/layers, /api/layers/[id]	Display label (e.g. “Sales Brief”)
`description`	ContentLayerSelector, LayerSuggestionBanner	/api/layers, /api/layers/[id]	Layer description
`display_order`	LayersSection (drag reorder)	/api/layers, /api/layers/[id], /api/layers/reorder	UI ordering
`is_active`	LayersSection (toggle)	/api/layers (filter), /api/layers/[id]	Soft delete
`created_at`		/api/layers
`updated_at`		/api/layers

Key consumer paths: The LayerVocabularyProvider context (contexts/layer-vocabulary-context.tsx) fetches all active layers at app layout level and provides getLayerKeys(), getLayerLabel(), getLayerDescription(), and refresh() to all children. 14 components consume the context via useLayerVocabulary(). The admin LayersSection component manages CRUD via /api/layers/* routes. lib/layer-inference.ts uses layer key constants for programmatic layer assignment. lib/client-config.ts provides fallback layer definitions when the DB fetch fails.

Supporting Tables (Brief Notes)

content_item_workspaces (junction)

All 3 fields (content_item_id, workspace_id, assigned_at) are consumed by:

API: /api/items/[id]/workspaces, /api/items/batch-workspaces, /api/workspaces/[id]/items, /api/bids/[id]
Hooks: useBrowseData (workspace filter), useQaProvenance
Lib: lib/intelligence/pipeline.ts

content_history

Key fields consumed by:

Lib: lib/reorient.ts (team changes, recent work), lib/dashboard.ts (activity feed), lib/ai/change-reports.ts (modified count)
API: /api/upload (insert version 1), /api/ingest/url (insert version 1)
Trigger-populated; immutable records

bid_response_history

Consumed by:

Lib: lib/dashboard.ts, lib/reorient.ts (bid activity tracking)
API: /api/bids/[id]/responses/[rId]/history

company_profiles

Fields consumed only by lib/intelligence/pipeline.ts (loadCompanyContext):

name, sectors, services, key_topics, target_customers, value_proposition
No UI consumers — pipeline-internal

content_citations

Consumed by:

API: /api/bids/[id]/responses/[rId] (citations)
MCP: cite_content tool
Trigger updates content_items.citation_count

notifications

Consumed by:

MCP: get_quality_briefing (quality_flag and coverage_alert types)
Lib: lib/dashboard.ts (unread count)
No direct UI component consumers in application code (consumed via dashboard data)

governance_config

Consumed by:

API: /api/cron/freshness-transitions (posture lookup)
MCP: get_quality_briefing (threshold lookup)
Components: GovernanceSection in settings

user_roles

Application-level role assignments (5 columns: id, user_id, role, display_name, created_at/updated_at). Nearly all consumers follow one pattern: read role for auth gating.

Consumed by:

Hook: useUserRole (role via user_id)
Lib: lib/auth/client.ts, lib/cron-auth.ts, lib/mcp/auth.ts (role lookup), lib/source-documents/source-document-notifications.ts (user_id where admin)
API: /api/admin/users (CRUD), /api/admin/users/invite (pre-create entry), /api/users/display-names (display_name), /api/review/history (display_name), /api/mcp/[transport] (role), /api/dashboard (role)
Pages: app/page.tsx, app/review/page.tsx, app/item/new/page.tsx, app/item/new/batch/page.tsx (all read role for gating)
Components: ContentOwnerManagement (user_id, role for owner picker)

template_requirements

Standardised tender template requirements with embeddings and domain classification. No UI component consumers.

Consumed by:

Lib: lib/templates/template-coverage.ts (semantic matching), lib/content/content-suggestions.ts (gap analysis)
API: /api/cron/content-gaps (gap detection)
Scripts: catalogue-standard-sq.ts, catalogue-charnwood-itt.ts (import), calibrate-coverage-thresholds.ts

Cross-Cutting Observations

Fields with Surprisingly Few Consumers

Table.Field	Consumer Count	Notes
`content_items.content_text_hash`	1 (lib/dedup/content-dedup.ts)	Dedup-only; consider if this should be more widely used
`content_items.source_bid`	2 (types, item detail client)	FK to workspaces but barely referenced
`content_items.parent_id`	2 (upload route, source-document components)	Self-referential hierarchy unused elsewhere
`digests.metadata`	0	Dead field
`digests.created_by`	0	Dead field
`bid_responses.overall_score`	Low (draft stream, quality check, readiness)	Quality check score, sparse UI consumption
`feed_sources.*` (all fields)	1 (pipeline only)	Entire table is pipeline-internal
`feed_articles.*` (all fields)	1 (pipeline only)	Entire table is pipeline-internal
`company_profiles.*` (all fields)	1 (pipeline only)	Entire table is pipeline-internal

Tables with No Direct UI Component Consumers

feed_sources — pipeline-internal only
feed_articles — pipeline-internal only
company_profiles — pipeline-internal only
guides — API routes only, consumed indirectly via guide-section-mapping
guide_sections — API routes only, consumed indirectly via guide-section-mapping
governance_config — API/MCP only, GovernanceSection in settings is the sole component
notifications — consumed via dashboard data, no direct component
template_requirements — lib/API/scripts only, no UI component consumers

Heaviest Consumer Paths

content_items browse path: useBrowseData hook → CONTENT_LIST_COLUMNS → 33 fields per row
content_items detail path: useItemDetailData + SSR page → CONTENT_DETAIL_COLUMNS → ~45 fields per row
content_items review path: /api/review/queue → REVIEW_COLUMNS → 27 fields per row
bid_questions + bid_responses bid path: Always fetched together; ~15 fields each

Sync drift — 02/07/2026

[path] lib/templates/template-coverage.ts no longer exists on main; the symbol moved to lib/domains/procurement/form-templating/template-coverage.ts (doc line 633 cites the old path).
[route] Doc line 169 cites /api/bids/[id]/outcome/integrate; the live route is /api/procurement/[id]/outcome/integrate (bid → procurement umbrella rename, S248).

Sync drift — 10/07/2026

[path] The kh_code_sources frontmatter glob lib/dedup/content-dedup.ts matches zero files under the public repo — the lib/dedup/ directory does not exist on main. The doc body still cites lib/dedup/content-dedup.ts as the sole consumer of content_text_hash (lines 197 and 647). Live dedup primitives live elsewhere (lib/q-a-pairs/dedup-merge.ts for curator-approved Q&A pair merges, lib/entities/entity-dedup.ts for entity dedup); neither is a 1:1 replacement for the documented content_text_hash dedup consumer, so the field-consumer row needs a human to confirm where hash-based content dedup now lives (or whether it was retired).