Phase 0.2.9 — FK coverage audit (createdby / updatedby / id columns)

Phase 0.2.9 — FK coverage audit (created_by / updated_by / *_id columns)

Audit date: 2026-05-06 Branch: content-items-investigation Schema source: supabase/types/database.types.ts (3973 lines, 46 tables, 1 view) + supabase/migrations/ (72 files; FK constraint inventory primarily in 20260416102457_pre_squash_reconciliation.sql and 20260503225703_migrate_auth_user_fks_to_user_profiles.sql)

Method

1. Enumerated every *_by and non-PK *_id column across all 46 public.* tables and 1 view by reading the Row blocks of supabase/types/database.types.ts.
2. For each column, cross-referenced the table’s Relationships array (the auto-generated Database types only enumerate columns with declared FK constraints) and confirmed against the canonical ADD CONSTRAINT ... FOREIGN KEY ... REFERENCES lines in the pre-squash baseline migration. There are 82 declared FK constraints across the public schema; the S27 retarget migration (20260503225703_migrate_auth_user_fks_to_user_profiles.sql) flipped 35 of them from auth.users(id) to public.user_profiles(id).
3. For columns lacking a declared FK, classified by examining (a) column type (uuid vs text/varchar/uuid[]), (b) write-site behaviour in lib/, app/, scripts/, (c) intent comments in pre-squash DDL (e.g. COMMENT ON COLUMN).
4. Severity-tagged P0/P1/P2 against missing FKs based on whether values are user UUIDs that should JOIN to user_profiles (P0/P1), structural lineage IDs (P0), array/polymorphic shapes that cannot have a single FK (N/A), or label strings (N/A).

Suspect column inventory

Total enumerated: 35 user-attribution *_by columns + 13 non-PK *_id columns + 4 array/uuid-collection columns = 52 candidate columns, of which 39 are wired correctly, 9 are missing FKs, and 4 are N/A — intentional (polymorphic, label string, or array).

The 35 user-attribution FKs retargeted by S27 (20260503225703) are all wired correctly post-retarget. The 9 missing FKs are clustered in two groups: 6 missed content_items columns (archived_by, verified_by, governance_reviewer_id, content_owner_id, parent_id, source_document_id) and 3 missed elsewhere (workspaces.created_by, workspaces.updated_by, processing_queue.created_by, digests.created_by).

Per-column verdict

Table.column	Type	FK target (declared)	Verdict	Severity	Notes
bid_questions.assigned_to	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
bid_questions.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
bid_questions.project_id	uuid	workspaces(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
bid_questions.template_requirement_id	uuid	template_requirements(id)	WIRED-CORRECTLY	—
bid_questions.matched_content_ids	uuid[]	—	N/A — INTENTIONAL	—	Postgres cannot FK array elements; integrity at app layer.
bid_response_history.edited_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
bid_response_history.response_id	uuid	bid_responses(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
bid_response_history.source_content_ids	uuid[]	—	N/A — INTENTIONAL	—	Array column.
bid_responses.approved_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
bid_responses.drafted_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
bid_responses.last_edited_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
bid_responses.question_id	uuid	bid_questions(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
bid_responses.source_content_ids	uuid[]	—	N/A — INTENTIONAL	—	Array column.
classification_disputes.content_item_id	uuid	content_items(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
classification_disputes.disputed_by	uuid	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
classification_disputes.resolved_by	uuid	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
company_profiles.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
content_chunks.content_item_id	uuid	content_items(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
content_chunks.parent_chunk_id	uuid	content_chunks(id) ON DELETE CASCADE	WIRED-CORRECTLY	—	Self-FK (heading hierarchy).
content_citations.bid_response_id	uuid	bid_responses(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
content_citations.content_item_id	uuid	content_items(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
content_citations.created_by	uuid	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
content_history.content_item_id	uuid (nullable)	content_items(id) ON DELETE SET NULL	WIRED-CORRECTLY	—
content_history.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
content_item_workspaces.content_item_id	uuid	content_items(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
content_item_workspaces.workspace_id	uuid	workspaces(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
content_items.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
content_items.updated_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
content_items.source_bid	uuid	workspaces(id) ON DELETE SET NULL	WIRED-CORRECTLY	—	Scoped to workspace.type = ‘bid’ (semantic, not enforced).
content_items.superseded_by	uuid	content_items(id) ON DELETE SET NULL	WIRED-CORRECTLY	—	Self-FK; CHECK constraint prevents self-reference.
content_items.archived_by	uuid	— (idx only)	MISSING-FK	P0	Index idx_content_items_archived_by; written as user.id from session; NOT in S27 retarget list. Audit trail orphan-prone.
content_items.verified_by	uuid	— (idx only)	MISSING-FK	P0	Index idx_content_items_verified_by; written as user.id in app/api/review/action/route.ts; NOT in S27 retarget list.
content_items.governance_reviewer_id	uuid	— (idx only)	MISSING-FK	P0	Index idx_content_items_governance_reviewer_id; written as user.id in lib/mcp/tools/governance.ts; NOT in S27 retarget list.
content_items.content_owner_id	uuid	— (idx only, partial)	MISSING-FK	P0	Index idx_content_items_content_owner_id + composite freshness index; written via lib/auth/owner-default.ts resolver as a UUID; NOT in S27 retarget list. Backfill migration exists (20260428145733).
content_items.parent_id	uuid	— (no idx, no FK)	MISSING-FK or DEAD-COLUMN	P1	Declared in DDL but not written anywhere in TS/Python codebase. Possibly legacy/unused. Open question — confirm before adding FK or marking for drop.
content_items.source_document_id	uuid	— (idx only)	MISSING-FK	P0	Pre-squash COMMENT ON COLUMN reads “FK to the source_documents row…” but the constraint was never created. Index idx_content_items_source_document_id exists. Lineage tracking compromised.
content_templates.created_by	uuid	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
coverage_targets.created_by	uuid	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
coverage_targets.domain_id	uuid	taxonomy_domains(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
coverage_targets.updated_by	uuid	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
digests.created_by	uuid	— (idx only)	MISSING-FK	P1	Index idx_digests_created_by; written as user UUID; NOT in S27 retarget list. Lower severity because change reports are derivative artefacts.
digests.generated_by	character varying NOT NULL DEFAULT ‘claude-sonnet’	—	N/A — INTENTIONAL	—	Model identifier label (e.g. claude-sonnet), not a user FK.
digests.item_ids	uuid[]	—	N/A — INTENTIONAL	—	Array column.
entity_mentions.content_item_id	uuid	content_items(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
entity_relationships.source_item_id	uuid	content_items(id) ON DELETE SET NULL	WIRED-CORRECTLY	—
feed_articles.content_item_id	uuid	content_items(id) ON DELETE SET NULL	WIRED-CORRECTLY	—
feed_articles.external_id	text	—	N/A — INTENTIONAL	—	External RSS GUID, not a public table id.
feed_articles.feed_source_id	uuid	feed_sources(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
feed_articles.prompt_version_id	uuid	feed_prompts(id)	WIRED-CORRECTLY	—
feed_articles.workspace_id	uuid	workspaces(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
feed_flags.feed_article_id	uuid	feed_articles(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
feed_flags.flagged_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
feed_flags.prompt_version_id	uuid	feed_prompts(id)	WIRED-CORRECTLY	—
feed_flags.resolved_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
feed_prompts.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
feed_prompts.workspace_id	uuid	workspaces(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
feed_sources.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
feed_sources.workspace_id	uuid	workspaces(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
governance_config.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
governance_config.reviewer_id	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
governance_config.updated_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
guide_sections.guide_id	uuid	guides(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
guide_sections.parent_section_id	uuid	guide_sections(id) ON DELETE CASCADE	WIRED-CORRECTLY	—	Self-FK.
guides.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
ingestion_quality_log.content_item_id	uuid	content_items(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
ingestion_quality_log.ingestion_batch	text	—	N/A — INTENTIONAL	—	Liam’s specific suspect. Populated by run_quality_scan(p_batch_name) with a free-form batch label string. No target table exists or is intended. The column propagates through the quality_issues_pending view.
ingestion_quality_log.created_by	uuid (nullable)	— (no idx)	MISSING-FK	P2	Column exists in database.types.ts but no migration declares it; need ground-truth check. Open question — was column added post-squash without an FK, or is it a stub?
ingestion_quality_log.resolved_by	uuid (nullable)	— (no idx)	MISSING-FK	P2	Same caveat as created_by — appears in types but not in any migration’s REFERENCES clause. Open question.
notifications.entity_id	uuid NOT NULL	—	N/A — INTENTIONAL	—	Polymorphic; CHECK enumerates 6 entity_types pointing at different tables (content_item, digest, template_requirement, domain, source_document, entity_mention). Single FK impossible.
notifications.user_id	uuid NOT NULL	auth.users(id)	WIRED-CORRECTLY	—	Intentionally retained on auth.users per S27 narrative (per-user identity link).
pipeline_runs.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
pipeline_runs.workspace_id	uuid (nullable)	workspaces(id) ON DELETE SET NULL	WIRED-CORRECTLY	—
pipeline_runs.items_created	uuid[]	—	N/A — INTENTIONAL	—	Array column.
processing_queue.created_by	uuid	— (idx only)	MISSING-FK	P1	Index idx_processing_queue_created_by; written as user UUID for content-creation jobs. NOT in S27 retarget list.
read_marks.content_item_id	uuid	content_items(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
read_marks.user_id	uuid	auth.users(id)	WIRED-CORRECTLY	—	Intentionally retained on auth.users per S27.
review_assignments.assigned_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
review_assignments.reviewer_id	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
si_processing_queue.feed_source_id	uuid	feed_sources(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
si_processing_queue.workspace_id	uuid	workspaces(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
source_document_diffs.affected_content_item_id	uuid	content_items(id) ON DELETE SET NULL	WIRED-CORRECTLY	—
source_document_diffs.created_by	uuid	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
source_document_diffs.new_document_id	uuid	source_documents(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
source_document_diffs.old_document_id	uuid	source_documents(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
source_document_diffs.reviewed_by	uuid	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
source_documents.archived_by	uuid	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
source_documents.parent_id	uuid	source_documents(id) ON DELETE SET NULL	WIRED-CORRECTLY	—	Self-FK (version chain).
source_documents.pipeline_run_id	uuid	pipeline_runs(id) ON DELETE SET NULL	WIRED-CORRECTLY	—
source_documents.uploaded_by	uuid	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
source_documents.workspace_id	uuid	workspaces(id) ON DELETE SET NULL	WIRED-CORRECTLY	—
tag_morphology_drift_flags.decided_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
tag_morphology_drift_flags.affected_content_ids	uuid[]	—	N/A — INTENTIONAL	—	Array column.
taxonomy_domains.recommended_by	text	—	N/A — INTENTIONAL	—	Source/model label (e.g. 'pipeline', 'manual'), not a user FK.
taxonomy_subtopics.domain_id	uuid	taxonomy_domains(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
taxonomy_subtopics.recommended_by	text	—	N/A — INTENTIONAL	—	Source/model label.
taxonomy_sync_state.synced_by	text NOT NULL DEFAULT ‘manual’	—	N/A — INTENTIONAL	—	Sync source label.
template_completions.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
template_completions.job_id	uuid (nullable)	processing_queue(id)	WIRED-CORRECTLY	—
template_completions.template_id	uuid	templates(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
template_fields.question_id	uuid (nullable)	bid_questions(id) ON DELETE SET NULL	WIRED-CORRECTLY	—
template_fields.template_id	uuid	templates(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
templates.created_by	uuid	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
templates.project_id	uuid	workspaces(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
user_notification_prefs.user_id	uuid PRIMARY KEY	auth.users(id) ON DELETE CASCADE	WIRED-CORRECTLY	—	Intentionally retained on auth.users per S27.
user_profiles.id	uuid PRIMARY KEY	auth.users(id) ON DELETE CASCADE	WIRED-CORRECTLY	—	Intentionally retained on auth.users per S27 (the mirror table itself).
user_roles.granted_by	uuid (nullable)	user_profiles(id) (post-S27)	WIRED-CORRECTLY	—
user_roles.user_id	uuid NOT NULL	auth.users(id) ON DELETE CASCADE	WIRED-CORRECTLY	—	Intentionally retained on auth.users per S27.
verification_history.content_item_id	uuid NOT NULL	content_items(id) ON DELETE CASCADE	WIRED-CORRECTLY	—
verification_history.performed_by	uuid NOT NULL	user_profiles(id) ON DELETE SET NULL (post-S27)	WIRED-CORRECTLY	—
workspaces.created_by	uuid	— (idx only)	MISSING-FK	P1	Index idx_workspaces_created_by; written as user UUID; NOT in S27 retarget list.
workspaces.updated_by	uuid	— (idx only)	MISSING-FK	P1	Index idx_workspaces_updated_by; written as user UUID; NOT in S27 retarget list.

Missing FKs by severity

P0 — silent referential integrity break

Five high-impact content_items columns plus one structural lineage column. All are written as UUIDs and intended to JOIN; orphans accumulate silently today.

1. content_items.archived_by — should reference public.user_profiles(id) (ON DELETE SET NULL). Audit trail for archived content is not enforced — deleting a user_profiles row leaves dangling references.
2. content_items.verified_by — should reference public.user_profiles(id) (ON DELETE SET NULL). Verification is a content-quality signal; orphans pollute “Verified by N users” UI.
3. content_items.governance_reviewer_id — should reference public.user_profiles(id) (ON DELETE SET NULL). Governance review queues JOIN on this; broken refs silently drop items from reviewer dashboards.
4. content_items.content_owner_id — should reference public.user_profiles(id) (ON DELETE SET NULL). The owner-notification pipeline (lib/source-documents/source-document-notifications.ts) blasts emails based on this column; orphans silently mute alerts to a content owner who no longer exists, which is closer to “wrong” than “no-op”.
5. content_items.source_document_id — should reference public.source_documents(id) (ON DELETE SET NULL). The pre-squash COMMENT ON COLUMN explicitly says it is a FK, but the constraint was never created. Lineage tracking and re-ingestion diffing assume integrity.

Proposed fix migration name (do not implement now): 20260507XXXXXX_add_missing_content_items_fks.sql. Mirror the S27 idempotent precondition-repair pattern — null out orphan UUIDs that don’t exist in the target table before adding the constraint, otherwise the ALTER will fail on production data.

P1 — degraded query

Lower severity because either the table is operational rather than user-facing, or the writer pattern means orphans are extremely unlikely.

6. workspaces.created_by — should reference public.user_profiles(id).
7. workspaces.updated_by — should reference public.user_profiles(id).
8. processing_queue.created_by — should reference public.user_profiles(id). Processing queue rows are short-lived; orphans expire within retention.
9. digests.created_by — should reference public.user_profiles(id). Change-report artefacts are derivative; a broken FK on this side affects “who triggered this digest” attribution only.
10. content_items.parent_id — direction unclear: column declared in pre-squash DDL but not actively written or read anywhere in the TS/Python codebase. Either add an FK to content_items(id) (self-FK) or drop the column. Tracked as P1 pending the open-question resolution below.

P2 — hygiene

11. ingestion_quality_log.created_by + ingestion_quality_log.resolved_by — appear in database.types.ts but no migration declares them. Either typegen drift or an undocumented manual schema patch. Resolve provenance before adding FKs (see open questions).

auth.users vs user_profiles re-targeting status

The S27 migration (20260503225703_migrate_auth_user_fks_to_user_profiles.sql) retargets 35 application-level FKs from auth.users(id) to public.user_profiles(id). Per its own narrative, 5 user-attribution columns are intentionally retained on auth.users:

• user_profiles.id (the mirror PK)
• user_roles.user_id
• user_notification_prefs.user_id
• notifications.user_id
• read_marks.user_id

These five are wired correctly by design.

No *_by columns currently present in the schema still point at auth.users(id) when they should point at user_profiles(id). The S27 migration was complete for the columns that had FKs to retarget. The 9 P0/P1 missing FKs identified above were never wired in the first place — they are the columns that S27 couldn’t retarget because no FK existed.

Re-ingest readiness gate

If re-ingest writes new historical user-attribution rows, the P0 fixes should land first. Specifically:

• All 5 P0 content_items FKs must land before re-ingest. The pipeline writes content_owner_id, created_by, updated_by, and (post-classification) governance_reviewer_id — adding these constraints first means any orphan UUID written by a buggy or stale pipeline is rejected immediately, not silently accepted as an orphan that we discover months later.
• The two ingestion_quality_log P2 columns should be ground-truthed before re-ingest because the pipeline writes to that table.

P1 (workspaces, processing_queue, digests) can land after re-ingest — these aren’t on the ingest write path, and they don’t accumulate row-level drift from a single ingest pass.

Open questions for parent session

1. content_items.parent_id — keep, drop, or wire? The column is declared in the pre-squash baseline but is not written or read in any TS or Python code. There is no idx_content_items_parent_id index. It may be vestigial (carried over from an early dual-purpose content_items/source_documents design before they split), or it may be reserved for a future hierarchical content feature. Confidence ≈ 60% it’s vestigial. Recommend a one-line SELECT count(*) FROM content_items WHERE parent_id IS NOT NULL; before deciding.

2. ingestion_quality_log.created_by and resolved_by provenance. These columns appear in database.types.ts (regenerated from the live DB) but no migration in supabase/migrations/ declares them. The pre-squash baseline DDL (20260416102457_pre_squash_reconciliation.sql:3823-3831) shows the table as 8 columns: id, content_item_id, flag_type, details, resolved, created_at, severity, ingestion_batch. The current types file shows 12 columns including created_by, resolution_notes, resolved_at, resolved_by. Confidence < 80% on origin — likely an undocumented manual ALTER TABLE or a missing migration. Need to either find the origin or treat the production schema as canonical and write a reconciling migration.

3. source_document_id comment-vs-implementation drift detection. The pre-squash COMMENT ON COLUMN public.content_items.source_document_id IS 'FK to the source_documents row…'; is the only place the FK is documented. Are there other columns where comments describe FKs that were never created? Recommend a sweep: pg_attribute.attname || ' ' || obj_description joined against pg_constraint to surface comment-vs-FK gaps. Out of scope for 0.2.9 itself but a good follow-up audit.

4. S27 narrative says “30 user-referencing columns across 24 tables retargeted from auth.users(id) to user_profiles(id)” — but the migration body retargets 35 constraints across 24 tables. The brief said 30; the brief was wrong. Confirmed by counting the ALTER TABLE ... ADD CONSTRAINT blocks in the S27 migration. Note for any future docs that quote the count.