Skip to content
Canonical

Phase 0.2b — Side tables audit

Phase 0.2b — Side tables audit

Section titled “Phase 0.2b — Side tables audit”

Audit date: 2026-05-06 Branch: content-items-investigation (executed from main HEAD; content-items-investigation branch exists locally and tracks the investigation but the 0.1 reports + migrations live on main) Sister agent: 0.2a (content_items 73-column audit) — out of scope here.

Tables in scope

Section titled “Tables in scope”

The ingest data flow (per Phase 0.1 reports) touches these side tables in addition to content_items:

  1. source_documents — file/document parent table
  2. source_document_diffs — re-upload diff store (also written by Path 7)
  3. pipeline_runs — pipeline observability ledger
  4. processing_queue — async job queue
  5. si_processing_queue — intelligence (RSS) per-source ledger
  6. content_history — content versioning
  7. content_chunks — heading-bounded chunk store + embeddings
  8. content_citations — bid-response provenance
  9. content_item_workspaces — multi-workspace link table
  10. ingestion_quality_log — quality flags from ingest
  11. entity_mentions — entity extraction outputs
  12. entity_relationships — entity extraction outputs
  13. feed_articles — RSS staging + provenance
  14. feed_sources — RSS source registry
  15. feed_flags — RSS flag/dispute queue
  16. feed_prompts — RSS LLM prompt versions

Tables flagged in the 0.1 reports as referenced in NOT-WRITTEN sections but absent from the database (so cannot have a verdict beyond “missing”):

  • content_intelligence_flags — never created
  • item_files, item_images — never created
  • classification_audit_log — never created
  • classification_telemetry — never created
  • ai_call_log, ai_telemetry — never created (per Path 9 already-known finding)

Verified by: (a) zero hits for these names in supabase/types/database.types.ts; (b) zero CREATE TABLE for any of them in supabase/migrations/*.sql; (c) zero references in lib/, app/, scripts/. They are referenced in 0.1 reports only as NOT-WRITTEN tables — i.e. they were enumerated as expected but proven absent. See § Tables to add below.

Tables NOT in scope (out-of-flow)

Section titled “Tables NOT in scope (out-of-flow)”

Excluded from this audit because they do not participate in the ingest data flow:

  • bid_questions, bid_responses, bid_response_history — bid library
  • classification_disputes — review workflow on classification
  • company_profiles — feature-engineering store for SI similarity
  • content_templates, templates, template_completions, template_fields, template_requirements — bid templating
  • coverage_targets — coverage matrix
  • digests — change reports
  • entity_aliases — read-only alias map (consumed by classify)
  • governance_config — governance settings (1-row config table)
  • guides, guide_sections — knowledge guides
  • layer_vocabulary — layer config (read-only at ingest)
  • notifications, read_marks, user_notification_prefs, user_profiles, user_roles — user/notification surface
  • review_assignments — review queue
  • tag_morphology_drift_flags — taxonomy maintenance
  • taxonomy_domains, taxonomy_subtopics, taxonomy_sync_state — read by classify; admin-write only
  • verification_history — provenance export (governance event log)
  • workspaces — admin-write only
  • _test_* tables — test fixtures
  • quality_issues_pending — view/relation, not a writable table

Per-table audit

Section titled “Per-table audit”

1. source_documents

Section titled “1. source_documents”
  • Verdict: NEVER-WIRED-PATH-FIX (partial) — Path 7 (file upload) is the only TS writer; INSERT wrapped in swallow-catch (route.ts:417 inside try {...} catch (srcDocErr) at L444-447). All other ingest paths (URL, MCP, manual create, batch, EP2 markdown, Python URL, Python markdown, Q&A docx, RSS) are ZERO writes. Per D2, EP2 markdown is to gain source_documents parity. Re-ingest path fix needed: (a) un-swallow Path 7 catch (or audit failure observability); (b) wire EP2 + URL + Python paths if D2 expands beyond EP2-only.
  • Schema: 18 columns. Key columns: id, filename, original_filename, mime_type, file_size, content_hash (MD5 of raw buffer, NOT normalised text), version, parent_id (self-ref FK for re-uploads), storage_path, status ∈ {uploaded, processing, processed, failed}, extracted_text, extraction_metadata (jsonb), workspace_id, pipeline_run_id, uploaded_by, created_at, archived_at, archived_by. RLS enabled.
  • Writers (verified by grep):
    • app/api/upload/route.ts:417 (INSERT, in swallow-catch), :621 (UPDATE extracted_text+status), :912 (UPDATE status=‘processed’)
    • app/api/source-documents/[id]/send-to-review/route.ts:108 (UPDATE)
    • app/api/source-documents/[id]/diff/route.ts (UPDATEs)
    • lib/mcp/tools/content.ts:2010-2086 (read/write surface for MCP create_content_item’s source_document_id linkage — typed, S205 WP-A1)
    • scripts/backfill-source-documents.ts:360 (one-shot backfill)
  • Readers: app/documents/[id]/diff/page.tsx, app/api/source-documents/[id]/route.ts:36, app/api/source-documents/[id]/diff/route.ts, lib/source-documents/source-document-impact.ts:47, lib/mcp/tools/content.ts (multiple reads).
  • Prod row count: 0 / 617 (per Path 7 §11 Q1 + Path 8 §10). source_document_id is 0/617 populated on content_items.
  • Re-ingest fix? Yes — partially, conditional on:
    • (a) Liam’s D2 ratification — EP2 v1 to gain parity (open Q in Path 10 §spec impact noted spec is silent on EP2 source_documents writes; D2 settles this).
    • (b) Path 7’s swallow-catch fixed (or downgraded to log + Sentry + re-throw) so dev usage actually populates the table.
    • (c) Path 4 (URL TS), Path 8 (URL TS), Path 5/6 (Python markdown + URL) currently never write — D2 implied scope is “EP2 only” so URL/Python paths remain ZERO. Confirm D2 scope: EP2-only or cross-path?
  • Open Qs: (i) Why is the staging refresh leaving source_documents empty? Likely (a) per Path 7 §11 — feature shipped but rarely-used, not a wiring failure; row 0 finding is benign for Path 7’s claim of PRODUCTION verdict. (ii) Should original_filename !== filename ever (currently always identical at write — see Path 7 §11 Q6).

2. source_document_diffs

Section titled “2. source_document_diffs”
  • Verdict: ACTIVE-EMPTY — written only by Path 7 re-upload branch (gated on match_type='new_version'). Schema-wise wired; in prod, source_documents itself is empty so re-uploads cannot occur, so this side table is downstream-empty by construction.
  • Schema (from database.types.ts:2058-): Diffs between two source_documents versions; per-row diff records with affected_content_item_id linkage.
  • Writers: app/api/upload/route.ts:964 (INSERT diff rows in try/catch — non-fatal), lib/source-documents/source-document-impact.ts:140 (UPDATE affected_content_item_id).
  • Readers: app/documents/[id]/diff/page.tsx, app/api/source-documents/[id]/diff/route.ts.
  • Prod row count: Unknown — not cited in 0.1 reports. Logically 0 if parent source_documents is 0.
  • Re-ingest fix? Inherits from source_documents (D2). If source_documents populates and re-uploads happen, diffs will populate naturally.
  • Open Qs: None blocking — child of source_documents.

3. pipeline_runs

Section titled “3. pipeline_runs”
  • Verdict: ACTIVE for cron + queue-job paths; NEVER-WIRED-PATH-FIX for some user-triggered ingest paths.
  • Schema: 17 columns. Key: id, pipeline_name, status, started_at, completed_at, created_by, items_created (uuid[]), items_processed, items_skipped, items_updated, source_filename, progress (jsonb — Pattern E phase reporting), result (jsonb), error_message, cost, workspace_id. RLS: per-S206 onward, INSERT requires admin OR service-role; per 20260430124741_pipeline_runs_rls_update_delete_policies UPDATE + DELETE policies were added (chokepoint pattern — see MEMORY feedback_pipeline_runs_rls_chokepoint).
  • Writers — comprehensive:
    • MCP create: lib/mcp/tools/content.ts writes 4-phase rows (auth-fail / insert-fail / success / catch-all) per S205 WP-A2, S206, S207, S208 — PRODUCTION.
    • Path 7 (file upload): app/api/upload/route.ts:249-267 INSERT, :367-370 UPDATE items_created, multiple progress UPDATEs — PRODUCTION.
    • Path 4 (TS URL ingest): ZERO writes — Path 8 §2.3 confirms (recordPipelineRun() not imported, from('pipeline_runs') absent). Wiring gap.
    • Path 9 (manual create EP3 /api/items): writes 2 rows (background_classify + background_summarise) per item — Path 9 §3.11.
    • Path 3 (TS batch creation EP6 /api/items/batch): raw .insert() (NOT helper) — Path 3 §2 lines 32-35, four sites: INSERT, per-item UPDATE, final UPDATE, catch-block UPDATE. Uses pipeline_name='qa_autosplit'. PRODUCTION but bypasses helper.
    • Path 10 (EP2 markdown batch): producer pre-INSERT (route.ts:298) + orchestrator UPSERT (start-run.ts:142, Path B D-11) + mid-flight + terminal UPDATEs (update-progress.ts, markdown-orchestrator.ts:806) — ** PRODUCTION** (ratified S226).
    • Path 5 (Python markdown): ZERO writes — pipeline_log.py defines start/complete/fail but process_url/ingest_markdown.py never call them. Wiring gap.
    • Path 6 (Q&A docx): ZERO writes — Path 6 §2.8 confirms via grep. Wiring gap.
    • Path 1 (Python URL): ZERO writes — Path 4 (Python URL) table line 32 shows helper exists (pipeline_log.py) but no callers. Wiring gap.
    • Path 2 (RSS): ZERO writes — recordPipelineRun() not called from pipeline.ts; per-source rows go to si_processing_queue instead. Intentional separation — but 0.1 RSS report calls it out as a doc drift.
    • Cron jobs: app/api/cron/{classification-quality, content-gaps, coverage-alerts, freshness-transitions, review-cadence, quality-score}/route.ts all use recordPipelineRun() — PRODUCTION.
    • Queue dispatch: lib/queue/dispatch.ts writes pipeline_runs directly (NOT via recordPipelineRun()) at lines 156, 172, 313, 329 because recordPipelineRun is INSERT-only and dispatch needs UPDATEs for terminal phases (per MEMORY feedback_pipeline_runs_pattern_2_direct_update).
    • Bid draft-all: app/api/bids/[id]/responses/draft-all/route.ts uses recordPipelineRun().
    • Taxonomy sync: app/api/admin/taxonomy-sync/* callers.
  • Readers: app/api/admin/pipeline-runs/recent/route.ts, app/api/admin/provenance/pipeline-runs/route.ts, app/api/pipeline-runs/route.ts, app/api/pipeline-runs/[id]/route.ts (Pattern E polling target), lib/dashboard.ts, lib/mcp/tools/governance.ts, lib/mcp/resources.ts, lib/intelligence/feed-poller.ts.
  • Prod row count: Unknown — not cited in 0.1 reports.
  • Re-ingest fix? Yes for paths needing parity (D3: cron treated as final-intended-state implies ingest paths SHOULD write pipeline_runs). Specifically:
    • Path 4 + Path 5 + Path 6 + Path 1 (Python URL) need recordPipelineRun() wired. Each is mechanical (drop one helper call at terminal phase per MEMORY feedback_record_pipeline_run_signature). Path 8 (TS URL) explicitly flags this as Q1.
    • Path 2 (RSS) — D3 says “cron treated as final-intended-state”, which suggests RSS should ALSO write a top-level pipeline_runs row per cron tick (currently only si_processing_queue rows exist). Cleaner observability but functionally separable.
  • Open Qs:
    • Q: Should the Python paths use recordPipelineRun() (TS) over the Supabase REST API, or replace pipeline_log.py with working callers? Both work; Python path is more consistent.
    • Q: Does D3 mandate that EVERY ingest path (incl. RSS) writes a pipeline_runs row, or is current cron-only coverage sufficient? The brief implies “yes for cron” but is silent on RSS as a cron-mediated path.

4. processing_queue

Section titled “4. processing_queue”
  • Verdict: ACTIVE — central async job queue.
  • Schema: 15 columns. Key: id, job_type (CHECK widened S224 + S225 + S226 to 11 values: embed, classify, extract_qa, summarise, validate, reprocess, template_fill, template_analyse, bid_draft_all, batch_reclassify, markdown_batch), status, priority, attempts, max_attempts, payload (jsonb), result (jsonb), idempotency_key (UNIQUE partial), started_at, completed_at, error_message, created_by, created_at, updated_at. No FK relationships — payload-driven.
  • Writers:
    • lib/queue/enqueue.ts — INSERT with idempotency (per-job-type + pipeline_run_id + content scope).
    • lib/queue/dispatch.ts — claim/UPDATE rows via claim_next_job RPC.
    • lib/queue/visibility-timeout.ts, lib/queue/failure.ts — retry
      • dead-letter UPDATEs.
    • lib/queue/handlers/{markdown-batch,batch-reclassify, bid-draft-all}.ts — terminal status UPDATEs.
    • app/api/cron/process-queue/route.ts — runs the dispatch loop.
    • scripts/bid_worker.py — Python-side worker (claim_next_job).
    • app/api/bids/[id]/templates/[templateId]/{fill,analyse}/route.ts — enqueue template jobs.
    • app/api/jobs/[id]/{cancel,status}/route.ts — UI/API job surface.
  • Readers: app/api/jobs/[id]/status/route.ts (UI polling), app/api/cron/process-queue/route.ts.
  • Prod row count: Unknown. pg_cron auto-archives terminal-state rows >30 days (s226_archive_processing_queue_pg_cron.sql) — long window.
  • Re-ingest fix? N/A — table is ACTIVE and behaving correctly.
  • Open Qs: None.

5. si_processing_queue

Section titled “5. si_processing_queue”
  • Verdict: ACTIVE — RSS-pipeline-only per-source ledger (separate from the general-purpose processing_queue).
  • Schema: 11 columns. Key: id, feed_source_id FK, workspace_id FK, articles_found, articles_new, articles_passed, status, started_at, completed_at, error_message.
  • Writers: lib/intelligence/pipeline.ts:849-857 (INSERT), :874-885 (UPDATE).
  • Readers: lib/intelligence/health.ts, app/api/intelligence/workspaces/[id]/metrics/route.ts.
  • Prod row count: Unknown but presumed populated proportional to the 28 RSS-feed rows in content_items.
  • Re-ingest fix? N/A — RSS-specific, behaving correctly.
  • Open Qs: Should this be merged into processing_queue (single queue) post-D3? Probably no — different schema (article counts) and different lifecycle (per-source, not per-job).

6. content_history

Section titled “6. content_history”
  • Verdict: ACTIVE — DB-trigger-driven for v1, app-level for v2+.
  • Schema: 14 columns. Key: id, content_item_id FK, version (BEFORE-INSERT trigger auto-computes via MAX(version)+1), title, content, brief, detail, reference, change_type (text), change_reason, change_summary, metadata (jsonb), created_by, created_at. CHECK on change_type extended at 20260427164439 for publication-state events.
  • Triggers:
    • set_content_history_version (BEFORE INSERT) — computes version.
    • trg_content_items_ensure_v1_history — DEFERRABLE INITIALLY DEFERRED CONSTRAINT TRIGGER on content_items AFTER INSERT. Writes v1 row at COMMIT time if no app-level v1 exists (S186 WP-E, 20260422060118). Reads NEW.ingest_source (S207 WP-A4) to set change_reason='initial_ingest' (when NOT NULL) or change_reason='auto_v1_on_insert' (when NULL).
  • App-level writers (v2+ events):
    • app/api/items/[id]/route.ts:344, 750 (PATCH item, archive)
    • app/api/review/publication-bulk-action/route.ts:270 (bulk state transitions)
    • lib/mcp/tools/content.ts:1671 (MCP update_content_item)
    • lib/mcp/tools/governance.ts:191, 234, 567, 835 (governance workflow events)
    • app/api/admin/content-dedup/{near-duplicates/[pairId]/merge, [id]/{confirm-unique,confirm-duplicate,supersede}}/route.ts — dedup workflow
    • app/api/items/[id]/rollback/route.ts — rollback to a prior version
    • scripts/kb_pipeline/store.py:72-115 insert_content_history_entry() exists but is dead code on all Python ingest paths (post-S153/OPS-20 NO-OP). Kept for legacy compatibility.
  • Readers: app/api/items/[id]/history/route.ts, app/api/items/[id]/history/[versionId]/route.ts, app/api/items/[id]/rollback/route.ts, app/api/bids/[id]/outcome/integrate/route.ts.
  • Prod row count: Unknown by direct cite, but Path 7 §11 + Path 8 §3 confirm v1 rows are reliably created post-S186/S207 trigger rollout. RSS report flags 28 RSS rows may pre-date the trigger and could need backfill.
  • Re-ingest fix? Yes — implicit. Re-ingest creates fresh content_items rows → trigger writes fresh v1 rows. Existing histories are preserved. NB: re-ingestion that DELETEs and re- INSERTs will create v1 rows but lose all v2+ history (governance, rollback, dedup events).
  • Open Qs: None blocking. Already-known v1-history consistency question for the 28 RSS rows is a backfill concern, not a wiring concern.

7. content_chunks

Section titled “7. content_chunks”
  • Verdict: ACTIVE with one NEVER-WIRED-PATH-FIX gap: RSS ingest never calls regenerateChunks() (Path 2 §4 + §11), so RSS-promoted items are absent from search_content_chunks.
  • Schema: 13 columns. Key: id, content_item_id FK, parent_chunk_id (self-ref FK), position, heading_text, heading_level, heading_path (text[]), content, embedding (vector(1024), JSON-stringified), char_count, word_count, created_at, updated_at.
  • Writers:
    • lib/content/chunk-store.ts regenerateChunks() — DELETE existing → bulk INSERT → per-row UPDATE for parent_chunk_id. Service-role.
    • Called from: Path 7 (file upload) app/api/upload/route.ts:693-699, Path 4 (TS URL) app/api/ingest/url/route.ts (per Path 8 §2.5), Path 9 (manual create EP3) app/api/items/route.ts:255-275, Path 10 (EP2 markdown) markdown-orchestrator.ts:696, MCP lib/mcp/tools/content.ts:665-685.
    • Python: scripts/kb_pipeline/chunk.py:215-285 — _delete_existing_chunks + store_chunks + per-chunk PATCH for parent_chunk_id. Called by Python URL + Python markdown + Q&A docx (run_post_insert).
    • NOT called by: Path 2 (RSS) — gap.
    • NOT called by: Path 3 (TS batch creation /api/items/batch) — Path 3 §2 line 49 confirms ZERO chunk writes. Backfill via scripts/backfill-chunks.ts.
  • Readers:
    • lib/mcp/tools/search.ts — semantic search via search_content_chunks RPC.
    • lib/mcp/tools/content.ts, lib/mcp/tools/index.ts.
  • Prod row count: Unknown by direct cite. RSS gap implies 28 rows have ZERO chunks; Path 3 (~71 manual rows? unclear) likewise. ~617 - 28 - N(batch) other rows have chunks.
  • Re-ingest fix? Yes — re-ingest naturally regenerates chunks on every successful classify/embed. RSS gap fix requires either adding regenerateChunks() to lib/intelligence/pipeline.ts post- classify OR running scripts/backfill-chunks.ts against the RSS-promoted IDs.
  • Open Qs: None.

8. content_citations

Section titled “8. content_citations”
  • Verdict: ACTIVE but OUT-OF-INGEST-FLOW — bid-response provenance, never written by ingest paths. Remains in scope only because the brief enumerated it.
  • Schema: 6 columns. Key: id, content_item_id FK, bid_response_id FK, citation_type (text, default), created_by, created_at.
  • Writers: lib/mcp/tools/bids.ts, app/api/bids/[id]/responses/draft-stream/route.ts — bid drafting workflow.
  • Readers: app/api/items/[id]/effectiveness/route.ts, scripts/mcp-eval/{fixtures,functional-correctness}.ts. Also drives the citation_count rollup column on content_items.
  • Prod row count: Unknown — not cited in 0.1 reports.
  • Re-ingest fix? N/A — re-ingest doesn’t touch citations.
  • Open Qs: Out-of-scope; flagged here only because brief listed it.

9. content_item_workspaces

Section titled “9. content_item_workspaces”
  • Verdict: ACTIVE for RSS pipeline; NEVER-WIRED-PATH-FIX for non-RSS user-facing ingest paths (none assign workspace at create time per Path 9 §3.13 explicit list).
  • Schema: 4 columns. Composite-PK-like key: (content_item_id, workspace_id). id is uuid; assigned_at timestamp.
  • Writers:
    • lib/intelligence/pipeline.ts:566-569 ensureWorkspaceLink() — race-tolerant pre-checks existence, then INSERT. Called per RSS article on existing-by-url branch (line 616) and post-INSERT (line 735).
    • lib/mcp/tools/content.ts (MCP-side workspace ops).
    • app/api/workspaces/[id]/route.ts, app/api/workspaces/[id]/items/route.ts, app/api/items/batch-workspaces/route.ts, app/api/items/[id]/workspaces/route.ts — workspace management API.
    • scripts/cleanup-stale-test-artifacts.ts — cleanup.
  • Readers: lib/mcp/tools/search.ts, app/api/bids/[id]/route.ts, hooks/use-qa-provenance.ts, hooks/browse/use-browse-data.ts, lib/mcp/tools/content.ts.
  • Prod row count: Unknown. 28 RSS rows → ≥28 link rows (deduplicated by (content_item_id, workspace_id)).
  • Re-ingest fix? Yes for RSS; No for non-RSS unless EP2 (D2 scope) or other ingest paths add workspace assignment.
  • Open Qs:
    • Q: Should non-RSS ingest paths assign workspace at create time? Currently 0.1 reports show all non-RSS paths leave workspace assignment to a separate API call. This is a UX/architectural decision, not a wiring bug.

10. ingestion_quality_log

Section titled “10. ingestion_quality_log”
  • Verdict: ACTIVE for Python paths (URL + markdown); NEVER-WIRED for TS paths (none write).
  • Schema: 13 columns. Key: id, content_item_id FK, flag_type, severity, details (jsonb), ingestion_batch, source_url, resolved, resolved_by, resolved_at, resolution_notes, created_by, created_at.
  • Writers:
    • Python URL: scripts/kb_pipeline/store.py:217-235 log_quality_issue() — called from pipeline.py:316-336 for missing_thumbnail, short_content, classification_low, manual_review. PRODUCTION.
    • Python markdown: Same log_quality_issue() — short_content, classification_low, manual_review (no thumbnail concept). PRODUCTION.
    • Q&A docx: Defined but NOT called (Path 6 §2.9 — quality flags printed to stdout only). NEVER-WIRED-CODE-FIX (deliberate omission per current code; could be enabled).
    • TS URL ingest, TS file upload, TS manual create, TS batch create, TS EP2 markdown, RSS, MCP create: ZERO writes (all 0.1 reports’ “Tables NOT written” sections confirm).
  • Readers: lib/mcp/tools/apps.ts, lib/mcp/tools/review.ts, lib/ai/digest.ts, app/api/quality/route.ts, app/api/quality/summary/route.ts, app/api/review/{action,history,queue}/route.ts, components/item-detail/metadata-sidebar.tsx. Quality dashboard + review queue surfaces consume this directly.
  • Prod row count: Unknown — not cited. Logically populated for Python-ingested rows (~100 URL + N markdown), 0 for TS-ingested rows.
  • Re-ingest fix? Partial — re-ingestion via Python paths will populate. TS paths need code added (gap). Note: readers that display quality flags will silently show “no flags” for TS- ingested items, which is misleading.
  • Open Qs:
    • Q: Should TS ingest paths emit equivalent quality flags? D3 treats cron as final-intended-state, but quality logging is NOT a cron concern — it’s an ingest-time concern.
    • Q: Add Q&A docx caller (already defined helper) so the printed stdout flags also persist?

11. entity_mentions

Section titled “11. entity_mentions”
  • Verdict: ACTIVE — written by every ingest path that runs classify with entity extraction.
  • Schema: 11 columns. Key: id, content_item_id FK, entity_name, canonical_name, entity_type, entity_type_override, confidence, context_snippet, metadata (jsonb), normalisation_version, created_at. UNIQUE constraint on (canonical_name, entity_type, content_item_id).
  • Writers (delete-then-upsert pattern across paths):
    • TS classify (lib/ai/classify.ts:1543-1546 DELETE, :1750-1755 UPSERT) — called by Path 4 (TS URL), Path 7 (file upload), Path 9 (manual EP3), Path 10 (EP2 markdown), Path 3 (batch creation), Path 2 (RSS), MCP create. Best-effort, never blocks classify.
    • Python classify (scripts/kb_pipeline/classify.py:1235 store_entities(), with 409-on-dup behaviour) — called by Python URL + Python markdown + Q&A docx (only on --entities).
    • Bridge UPDATEs (TS): classify.ts:1810-1817 bridgeTemporalReferencesToEntities writes metadata.{ai_temporal_*}.
    • Bridge UPDATEs (Python): scripts/kb_pipeline/temporal_bridge.py:131 PATCHes metadata.{expires_on,effective_from}.
    • Q&A --entities: scripts/kb_pipeline/temporal_bridge.py:61 bridge for certifications.
    • Admin entity ops: app/api/entities/{[canonical_name], split, [canonical_name]/{type,metadata}}/route.ts — delete/merge/split/relabel.
    • scripts/{eval-entity-classification, backfill-temporal-entity-matches,eval-holder-rule-ts, backfill-classify-content-items,propagate-cert-metadata, batch-reclassify}.ts — assorted maintenance.
  • Readers: lib/mcp/tools/entities.ts, lib/mcp/resources.ts, lib/mcp/tools/dashboard.ts, lib/mcp/tools/shared.ts, lib/dashboard.ts, lib/entities/entity-metadata-bridge.ts, app/api/certifications/route.ts, app/api/cron/freshness-transitions/route.ts.
  • Prod row count: Unknown by direct cite; presumed populated for every classified item (most of the 617).
  • Re-ingest fix? Yes — automatic. Re-ingest re-runs classify → re-runs DELETE+UPSERT.
  • Open Qs: None.

12. entity_relationships

Section titled “12. entity_relationships”
  • Verdict: ACTIVE — peer of entity_mentions; written alongside.
  • Schema: 7 columns. Key: id, source_entity, relationship_type, target_entity, source_item_id FK, confidence, created_at. UNIQUE-ish constraint added at 20260421171520_entity_relationships_unique_tuple_constraint.sql (composite tuple (source_entity, relationship_type, target_entity, source_item_id), NULLS NOT DISTINCT).
  • Writers: lib/ai/classify.ts:1787-1793 UPSERT; scripts/kb_pipeline/classify.py:1386 store_relationships(). Called same way as entity_mentions (every classify caller, gated on classifier returning relationships).
  • Readers: Same set as entity_mentions plus get_entity_relationships_rpc consumed by entity detail UI.
  • Prod row count: Unknown.
  • Re-ingest fix? Yes — automatic.
  • Open Qs: None.

13. feed_articles

Section titled “13. feed_articles”
  • Verdict: ACTIVE — the long-lived RSS staging + provenance store.
  • Schema: 21 columns. Key: id, workspace_id FK, feed_source_id FK, external_url, external_id, title, raw_content, relevance_score, relevance_category, relevance_reasoning, matched_categories (text[]), ai_summary, prompt_version_id FK→feed_prompts, extraction_method, passed, published_at, content_item_id FK→content_items (nullable — filtered articles never promote).
  • Writers (all in lib/intelligence/pipeline.ts):
    • INSERT short-content branch (line 382-399).
    • INSERT main branch (line 466-484).
    • UPDATE content_item_id (existing-by-url, line 611-615).
    • UPDATE content_item_id (post-INSERT, line 675-679).
    • lib/intelligence/summary.ts — ai_summary rewrite for re-score.
    • scripts/batch-rescore-articles.ts — CLI batch rescore.
  • Readers: app/api/feeds/[workspaceId]/rss/route.ts (public RSS render), app/api/feeds/[workspaceId]/rss/filtered/route.ts, app/api/intelligence/workspaces/{,[id]/{articles,metrics, prompt-performance,flags,prompts}}/route.ts, app/item/[id]/page.tsx (KB item back-link).
  • Prod row count: “Polled-many” per brief. Path 2 §11 confirms filtered rows persist with content_item_id IS NULL for analytics and re-score. RSS contributes 28 of the 617 promoted items.
  • Re-ingest fix? N/A — RSS-pipeline-internal.
  • Open Qs:
    • Path 2 §10 calls out no AI telemetry for the LLM summary call that writes ai_summary — model, token counts, prompt version (for the summarisation step) all unpersisted. Promoted to “Tables to add” below.

14. feed_sources

Section titled “14. feed_sources”
  • Verdict: ACTIVE.
  • Schema: 17 columns. Key: id, workspace_id FK, name, url, source_type, is_active, polling_interval_minutes, etag, last_modified, last_polled_at, last_polled_status, last_polled_error, consecutive_failures, article_count, created_by, created_at, updated_at.
  • Writers: lib/intelligence/pipeline.ts:768 updateSourceAfterPoll (every poll, success or fail); :530-533 increments article_count. app/api/intelligence/workspaces/[id]/sources/{,sourceId/{,test}}/ route.ts — admin CRUD.
  • Readers: lib/intelligence/health.ts, app/api/intelligence/workspaces/route.ts, etc. (get_due_feed_sources RPC).
  • Prod row count: Unknown.
  • Re-ingest fix? N/A.
  • Open Qs: None.

15. feed_flags

Section titled “15. feed_flags”
  • Verdict: ACTIVE — RSS dispute/flag queue.
  • Schema: 12 columns. Key: id, feed_article_id FK, flag_type, flagged_by FK→user_profiles, notes, prompt_version_id FK→feed_prompts, resolution_type, resolved, resolved_by, resolved_at, resolved_notes, created_at.
  • Writers: app/api/intelligence/workspaces/[id]/articles/[articleId]/flag/route.ts (INSERT user flags), .../flags/resolve/route.ts (UPDATE resolutions).
  • Readers: lib/intelligence/summary.ts, app/api/intelligence/workspaces/[id]/{metrics,prompt-performance, flags,flags/resolve,flags/analyse,prompts/route.ts} — feed dashboard.
  • Prod row count: Unknown.
  • Re-ingest fix? N/A.
  • Open Qs: None.

16. feed_prompts

Section titled “16. feed_prompts”
  • Verdict: ACTIVE — LLM prompt version registry for RSS scoring.
  • Schema: 9 columns. Key: id, workspace_id FK, prompt_text, version, is_active, change_notes, performance_snapshot (jsonb), created_by, created_at.
  • Writers: app/api/intelligence/workspaces/route.ts:218 (seed starter prompt on workspace create), .../prompts/route.ts:30, 98, 111, 121, 152, 168 (full CRUD).
  • Readers: lib/intelligence/pipeline.ts:274 (active prompt lookup at poll-time), app/api/intelligence/workspaces/[id]/{metrics/prompt-performance, flags/analyse}/route.ts.
  • Prod row count: Unknown — at least 1 row per active workspace (seeded on creation).
  • Re-ingest fix? N/A.
  • Open Qs:
    • Path 2 §11 — only the scoring prompt is versioned; the summarisation prompt and classification prompt are not. Architectural gap, not a wiring fix.

Cross-table issues

Section titled “Cross-table issues”

  1. processing_queue.job_type CHECK currently has 11 values (post-S226 25/05/2026 + S224 + S225 widenings). All 11 map to handler dispatches in lib/queue/dispatch.ts; only 3 handler files exist (markdown-batch.ts, batch-reclassify.ts, bid-draft-all.ts) — the other 8 (embed, classify, extract_qa, summarise, validate, reprocess, template_fill, template_analyse) are dispatched inline within dispatch.ts or handed to template-specific routes (app/api/bids/[id]/templates/). markdown_batch was added by S226 W1-IMPL — verified by reading 20260506125704_s226_widen_job_type_check_markdown_batch.sql. No action needed.

  2. D1 markdown_batch ingest_source value is a TS-string-literal change, not a DB-CHECK change. content_items.ingest_source is a plain text column with no CHECK constraint (verified — only migrations affecting it are 20260428174512 add-column and 20260428180945 + 20260428235042 backfills, none introduce a CHECK). EP2 currently writes ingest_source: 'upload' (markdown-orchestrator.ts:642) — D1 implies changing this to markdown_batch. The downstream consumer is the ensure_v1_history_at_commit trigger which sets metadata.ingest_source on the v1 history row — a fresh value will simply appear in the metadata. No DB CHECK widening needed. TS change is one-line in markdown-orchestrator.ts (and any tests asserting the literal 'upload').

  3. pipeline_runs write-coverage is uneven across ingest paths:

    • WIRED: MCP create, file upload (Path 7), TS batch (Path 3), TS manual EP3 (Path 9), EP2 markdown (Path 10), all crons.
    • UNWIRED: TS URL (Path 4), Python URL (Path 1), Python markdown (Path 5), Q&A docx (Path 6), RSS (Path 2 — by design, but D3 implies cron-mediated paths should write at the cron tick boundary).

  4. source_documents is a 0/617-row anomaly, but the pre-launch re-ingest opportunity makes it benign IF (a) EP2 gains parity per D2, (b) Path 7’s swallow-catch is fixed (or kept and the feature remains thin per Path 7 §11 Q1’s “feature shipped but rarely used” hypothesis). Cross-table impact: source_document_id on content_items is 0/617 populated by direct consequence; source_document_diffs is structurally empty.

  5. Trigger trg_content_items_ensure_v1_history is the v1-history single-source authority since S207 WP-A4 — every ingest path was audited (§ Tables written sections of all 0.1 reports) and confirms app-level v1 inserts have been removed. The trigger reads NEW.ingest_source to set change_reason='initial_ingest' for any non-NULL value (S207 WP-A4 trigger function logic at 20260428174512:34-83). No action needed.

  6. ingestion_quality_log is Python-only. TS paths emit no quality-log rows. The reader surface (review queue, quality dashboard) silently shows “no flags” for TS-ingested items — a reader can’t distinguish “no quality issues” from “no quality logging at this path”.

  7. content_chunks not regenerated for RSS — Path 2’s long-standing gap. Backfill script exists (scripts/backfill-chunks.ts); permanent fix needs a call to regenerateChunks() inside lib/intelligence/pipeline.ts post-classify.

Tables to add (per D2 + roadmap)

Section titled “Tables to add (per D2 + roadmap)”

These are referenced in 0.1 reports but DO NOT EXIST in the database or codebase (verified zero hits in database.types.ts + migrations + source code):

  1. ai_call_log / ai_telemetry — known absent per Path 9 (MCP create) report and Path 8 (TS URL ingest) §11 Q2. Roadmap-blocked per docs/plans/ai-telemetry-instrumentation-plan.md (referenced from Path 9). Without it, the eight content_items columns classification_model, classification_tokens_in, classification_tokens_out, classification_cache_creation_tokens, classification_cache_read_tokens, embedding_model, embedding_tokens all stay NULL on every ingest path (verified by ALL 0.1 reports). Spec ratification status: not started — Phase D plan referenced in Path 9 but not yet authored.

  2. classification_audit_log — referenced by Path 6 (Q&A docx) §2.10 and Path 8 (TS URL) §2.7. Schema unknown (no spec).

  3. classification_telemetry — same as above; appears to be a companion to classification_audit_log. Note: MEMORY references “ai-telemetry SUPERSEDED notes” suggesting the table was previously planned and is now superseded by the broader ai-telemetry-instrumentation-plan.md work.

  4. content_intelligence_flags — referenced by Path 7 §2.8 and Path 8 §2.7 as NOT-WRITTEN. Likely a planned but never-materialised companion to ingestion_quality_log for AI-derived intelligence (relevance, sentiment, etc.). No spec located.

  5. item_files, item_images — referenced by all 0.1 reports as NOT-WRITTEN. Likely planned originally as a normalisation of the file/image attachments out of content_items.metadata and source_documents.extraction_metadata. Per Path 7 the path stores files in Storage + creates source_documents rows; per the lack of any reference in code or schema, these tables were planned but the work was folded into source_documents + Storage instead. Likely SUPERSEDED at design time — recommend removing from any planning docs that still reference them.

Tables to drop

Section titled “Tables to drop”

None directly identified. No table in scope has zero readers AND zero writers. The closest candidates:

  • feed_flags is sparse-write (only on user-flagged articles) but has clear admin readers — keep.
  • feed_prompts has a single-prompt-per-workspace minimum but is the only versioning surface for RSS scoring — keep.
  • source_document_diffs is currently structurally empty (downstream of source_documents’s 0 rows) but is wired correctly; keep pending D2-driven re-ingest.

The four absent-from-DB candidates (item_files, item_images, content_intelligence_flags, classification_audit_log) are ALREADY non-existent — there’s nothing to drop, only stale references to scrub from planning docs. Recommend removing from data-entry-points.md NOT-WRITTEN sections or migrating the references to a “planned but abandoned” appendix to prevent future audits from re-discovering these as gaps.

Open questions for parent session

Section titled “Open questions for parent session”

  1. D2 scope: Is source_documents parity restricted to EP2 markdown only, or does D2 imply wiring Path 4 (TS URL), Path 1 (Python URL), Path 5 (Python markdown), Path 6 (Q&A docx) too? The brief reads as EP2-only (“EP2 v1 to gain source_documents parity”) but the 0/617 row finding is cross-path (only Path 7 attempts a write, and that attempt is in a swallow-catch).

  2. D3 scope on RSS: “Cron treated as final-intended-state.” app/api/cron/intelligence-poll/route.ts does not call recordPipelineRun() — only si_processing_queue rows are created. Should D3 mandate a top-level pipeline_runs row per poll tick, or is the per-source si_processing_queue ledger sufficient?

  3. Path 7 swallow-catch: Per the brief, source_documents was declared written “in swallow-catch”. Path 7’s audit prefers the “feature shipped but rarely used” explanation (§11 Q1 (a)) over “swallow-catch silently failing” (§11 Q1 (b)). Re-ingest will only fix the symptom if the catch is removed/downgraded. Decision needed before re-ingest.

  4. ai_call_log / ai_telemetry spec ratification: Eight content_items token/model columns are NEVER populated by ANY ingest path. Phase D plan exists per Path 9 reference but is not yet authored. Block on spec ratification before re-ingest, or accept telemetry-NULL post-re-ingest and ratify Phase D as follow-on?

  5. TS paths emitting ingestion_quality_log: Reader surfaces already consume the table; TS paths are silent. Decision needed: wire TS paths or accept that quality flags are Python-ingested- only.

  6. RSS content_chunks gap: Backfill or permanent fix at next re-ingest? lib/intelligence/pipeline.ts permanent fix is ~5 lines (regenerateChunks() call after classify) — recommend permanent fix.

  7. Q&A docx quality logging: Helper exists (store.py defines log_quality_issue); path doesn’t call it (Path 6 §2.9). Wire it, or accept stdout-only?

  8. classification_audit_log / classification_telemetry table intent: No spec located. Are these planned, abandoned, or superseded by the still-pending ai_call_log work? Confirmation needed to either (a) author specs, (b) remove from data-entry-points NOT-WRITTEN sections, or (c) document as superseded.

Summary

Section titled “Summary”
  • Tables in scope: 16 (14 ACTIVE-with-various-coverage, 1 ACTIVE-EMPTY (source_document_diffs), 1 NEVER-WIRED-PATH-FIX partial (source_documents)).
  • Tables enumerated in 0.1 NOT-WRITTEN sections but absent from DB: 6 (ai_call_log, ai_telemetry, classification_audit_log, classification_telemetry, content_intelligence_flags, item_files+item_images).
  • Cross-table verdicts:
    • 11 ACTIVE (pipeline_runs, processing_queue, si_processing_queue, content_history, content_chunks, content_citations, content_item_workspaces, ingestion_quality_log [Python-active / TS-gap], entity_mentions, entity_relationships, feed_articles, feed_sources, feed_flags, feed_prompts) — note: 14 distinct tables, all of which are ACTIVE in some sense.
    • 1 NEVER-WIRED-PATH-FIX (source_documents — 0/617 rows; Path 7 swallow-catch + EP2 D2 expansion needed).
    • 1 ACTIVE-EMPTY (source_document_diffs — wired correctly, structurally empty downstream of source_documents).
    • 0 SUPERSEDED, 0 DROP-CANDIDATE, 0 NEVER-WIRED-CODE-FIX (within DB-existing tables).
    • 4-6 NEVER-WIRED-CODE-FIX (require new tables): ai_call_log, classification_audit_log, classification_telemetry, + content_intelligence_flags, +/- item_files/item_images (likely already superseded).
  • Top 3 cross-table issues:
    1. pipeline_runs write-coverage uneven across ingest paths (4 TS paths + 4 Python paths missing the helper call).
    2. ingestion_quality_log is Python-only; TS readers display “no flags” misleadingly for ~70% of content (TS-ingested rows).
    3. source_documents 0/617 rows + 0/617 source_document_id populated; resolution depends on D2 scope clarification.
  • Biggest gap to fix pre-re-ingest: Decide D2 scope (EP2-only or cross-path) for source_documents parity, and decide on ai_call_log spec ratification — these are the two largest classes of column-NULLs flagged by ALL 0.1 reports.
  • Biggest open question: Path 7’s swallow-catch — is the 0-row count benign (feature unused) or symptomatic (catch silently failing)? Confirmation requires either (a) prod log inspection for the 'Source document tracking failed' log line, or (b) accepting the “rarely used” hypothesis and removing/downgrading the catch before re-ingest.

Confidence: ≥92% on writer/reader inventories (verified by direct grep + 0.1 report cross-reference). 85% on row counts (only those explicitly cited in 0.1 reports — most are unknown). 100% on table-existence (verified migrations + types).