Content Items Cleanup Phase 0 Investigation

Reframing — what changed vs prior session

Prior session’s Phase C verification answered “is column read?” — wrong question. Right question per your feedback: “is current consumption correct, or is it a symptom of supersession / build-not-wired?”

Same reframing applies wider:

• 73 columns on content_items → which are right shape vs wrong shape vs dead?
• Schema-wide → same audit needed for other tables
• Knip 280+ items now suppressed by config — which were genuine signals?

So we step back. Phase 0 (investigation) BEFORE Phases A–D.

Proposed Phase 0 — comprehensive investigation

Goal: for every column / table / ingest path, know with ≥90% confidence: ACTIVE / SUPERSEDED / NEVER-WIRED / INTENTIONAL-NULL. Anything <90% → flagged for sub-investigation.

0.1 — Document every ingest path (parallel sub-agents)

10 paths confirmed in prior plan. One sub-agent per path, each produces:

• Entry point (route/CLI)
• Fields written to content_items + side tables
• AI calls made + models + tokens captured
• Provenance fields populated (source_url/source_file/source_document_id/file_path/source_document)
• source_documents writes (yes/no/should-be)
• metadata JSONB keys written
• Status: production / dev / dead / planned

Paths:

1. scripts/ingest.py — Python URL cron
2. scripts/ingest_markdown.py — Python markdown
3. scripts/import_bid_library.py — Q&A .docx
4. app/api/ingest/url/route.ts — TS URL ingest
5. app/api/items/route.ts (POST) — manual creation
6. app/api/items/batch/route.ts — batch creation
7. app/api/upload/route.ts — file upload
8. app/api/ingest/markdown/route.ts + lib/ingest/markdown-batch.ts — EP2 (also covers §5.4.4 spec impact)
9. lib/intelligence/pipeline.ts — RSS feeds
10. lib/mcp/tools/content.ts:create_content_item — MCP

0.2 — Schema-wide audit (sequential sub-agent after 0.1)

Every table + column in public.* schema, classified:

• ACTIVE — writers + readers both present, behaviour matches column purpose
• SUPERSEDED — readers exist but writers stopped (or vice versa) → readers reading wrong source
• NEVER-WIRED — declared in schema but never connected end-to-end
• INTENTIONAL-NULL — designed to be nullable, populated by future feature, has roadmap entry

Output: table-by-table status report with row-count signals + writer/reader cite + roadmap-entry link if applicable.

0.3 — Knip 280+ revisit

bun run knip originally surfaced 280+ items; an agent updated config to suppress. Re-investigate:

• Which suppressions were correct (genuinely intentional re-exports etc.)
• Which were genuine “build but not wired” signals masked by config
• Cross-reference against 0.2 schema findings

0.4 — Reference doc verification

Compare 0.1/0.2/0.3 findings against:

• docs/reference/data-entry-points.md (1508L) — does it cover all 10 paths? Drift?
• docs/reference/field-consumer-dependency-map.md (657L) — drift since S192/S207–209?
• docs/reference/SCHEMA-QUICK-REFERENCE.md (1867L) — drift?
• docs/reference/state-of-the-product.md §5 — accurate?

Edits surgical; flag drift severity.

0.5 — §5.4.4 EP2 spec impact

Spec is DRAFT pending your decisions. Key Q for our audit: does EP2 v1 populate source_documents + source_document_id? If yes, that’s the wiring fix for markdown path. If no, we have a decision to make. Read spec → answer.

0.6 — Synthesis: data flow diagrams + schema status

Two diagrams:

• Python ingest flow — entry points → AI calls → tables written → fields populated
• TS ingest flow — entry points (UI/API/MCP) → AI calls → tables written → fields populated

Each diagram annotated with: which fields flow through which path; which fields ARE NEVER populated (and why); which fields are populated by SOME paths but not others (= inconsistency to fix or document).

Plus consolidated schema status report (output of 0.2).

0.7 — Confidence gate + escalations

Anything <90% confidence → enumerated as open question for you. Don’t proceed past 0.7 with unresolved <90% items.

Sequencing

0.1 (10 parallel agents, ~30 min wall clock)
 └─ 0.2 (sequential, depends on 0.1) ~45 min
     └─ 0.3 (sequential, depends on 0.2) ~30 min
         └─ 0.4 (sequential, depends on 0.1–0.3) ~30 min
             └─ 0.5 (parallel with 0.4) ~15 min
                 └─ 0.6 (sequential synthesis) ~30 min
                     └─ 0.7 (confidence gate) ~15 min

Total Phase 0 wall clock: ~3h with full verification. Output: ratified data-flow + schema-status report. Then re-plan Phases A–D against verified findings.

After Phase 0

Phases A–D get rebased:

• A (telemetry backfill) — likely unchanged but verify against 0.6 findings
• B (MCP gate) — confirmed unchanged
• C (cleanup) — fully re-derived from 0.2 verdicts. Some HOLD items from prior verification may flip to DROP (if reader is reading-superseded-column). Some KEEP items may surface as never-wired.
• D (ai_call_log) — gated by A + WP-B1 per your call

Plus possibly NEW Phase E (broader schema cleanup beyond content_items) if 0.2 surfaces issues elsewhere.