Content Management — Workflows

Content Management — Workflows

Last verified: Session 215 W5 V_W4 (30 April 2026). W5 V_W4: Pattern E §7.2 EP3 file/count attribution corrected to app/api/upload/route.ts (11 call sites); §5.4 dangling backlog reference replaced with 61 “EP3 UI Pattern E retrofit”. S215 W4 T9 update: Workflow 12 Markdown Batch Ingest UI added (EP2 §1.11 — app/api/ingest/markdown/route.ts

• lib/ingest/markdown-orchestrator.ts; Pattern E lifecycle introduced end-to-end: server-side pipeline_runs writes + UI polling). S210 update: ingest path consistency end-to-end (S205 + S207 + S209 — 8 INSERT-time entry points all populate typed ingest_source); v1 history trigger is single authority (S207 WP-A4 removed app-level v1 inserts); MCP create_content_item emits pipeline_runs audit row across all paths (S208 OPS-40); typed source-document linkage via source_document_id FK (S205 WP-A1); content owner auto-assigned at all 6 ingest entry points (S206); recurring review cadence cron + MCP filter widening (§5.5 Phases 1-5). Prior baseline: S188 (22 April 2026).

Overview

Content management workflows cover data flows from user / agent / cron action through API to database for content creation, ingestion, editing, versioning, archival, supersession, and lifecycle management. Eight INSERT-time entry points (4 TS + 4 Python) feed the central content_items table; each is paired with a typed ingest_source value and an audit row in pipeline_runs.

Workflow 1: Content Creation Pipeline (Web Form, EP5)

Trigger: User submits POST /api/items Owner: app/api/items/route.ts Typed value: ingest_source='manual'

Flow

Validate → Resolve owner → Embed (if auto_embed) → Dedup soft-block → Normalise ai_keywords → INSERT (typed ingest_source) → V1 history (DB trigger) → Chunk (non-draft) → Classify → Summarise → Layer inference → Quality score → Topic suggestion → Guide section suggestion → Return 201 + warnings[]

Detailed Steps

1. Auth + role check

   • getAuthorisedClient(['admin', 'editor']) returns { user, supabase, role }. Failure routed via authFailureResponse(auth).

2. Rate limit

   • 20 requests / minute via checkRateLimit.

3. Validate input

   • Schema: ItemCreateBodySchema (from lib/validation/schemas.ts). Includes optional skip_dedup, content_owner_id, publication_status, source_document_id, ingestion_source, governance_review_status.

4. Resolve content owner (S206 WP-A Phase 2)

   • resolveContentOwnerId({ explicit: content_owner_id, role, userId: user.id }) — admin override accepted; non-admin override silently forced to userId.

5. Generate embedding (synchronous when auto_embed=true)

   • Model: text-embedding-3-large (1024 dimensions)
   • Truncates at MAX_EMBEDDING_CHARS = 24_000
   • JSON.stringify(embeddingArray) for Supabase RPC

6. Dedup soft-block

   • checkForDuplicates(supabase, plainText, embeddingArray) → resolveDedupStamp(exactMatch?.id, { skipDedup }) returns { dedup_status, suspected_duplicate_of? } for spread into payload.

7. Normalise ai_keywords (S197 §1.17)

   • [...new Set(ai_keywords.map(normaliseTag).filter(Boolean))] at the write boundary so web-form keywords match classify-time canonicalisation.

8. INSERT content item

   • Typed columns include ingest_source: 'manual', content_owner_id: ownerId, dedup_status, optional publication_status, source_document_id. Legacy metadata.ingestion_source mirror retained for back-compat reads.
   • Never write content_text_hash — GENERATED ALWAYS.

9. V1 content_history (DB trigger — single authority)

   • trg_content_items_ensure_v1_history fires at transaction commit, reads NEW.ingest_source, emits change_reason='initial_ingest' (or 'auto_v1_on_insert' for legacy NULL), writes metadata.ingest_source for granular per-path observability.
   • App-level v1 inserts have been removed (S207 WP-A4 Task 3.4).

10. Chunking (non-draft only)

    • regenerateChunks(serviceClient, newItem.id, content) splits markdown at H2 boundaries (H1 fallback) and writes per-chunk vector(1024) embeddings to content_chunks. Drafts skip chunking — chunks become searchable once published.

11. Classify

    • classifyContent({ supabase, itemId, force: true, userId }) via classifyInBackground — sets primary_domain, primary_subtopic, secondary_domain, secondary_subtopic, classification_confidence, classification_reasoning.
    • Records a pipeline_runs row (pipeline_name='background_classify').

12. Summarise

    • generateSummary({ supabase, itemId, force: true, userId }) — sets summary, ai_keywords, summary_data JSONB.
    • Records pipeline_name='background_summarise'.

13. Layer inference, quality score, topic + guide section suggestions

    • Each step is non-fatal; failures captured as warnings.
    • Quality score uses calculateAndRoundQualityScore({ freshness, classification_confidence, brief, detail, reference, summary, citation_count, next_review_date, review_cadence_days }) — the last two fields trigger the §5.5 Phase 5 cadence-compliance penalty when non-null.

Error Handling

Error Condition	Handling	User Feedback
Validation failure	400 returned, no insert	Field-level error messages
Embedding generation fails	Warning added, item still created	Warning in response
Classification fails	Warning added, item unclassified	Warning in response
Duplicate detected	Soft-block: item created with dedup_status='suspected_duplicate' stamp	Duplicate warning in response
Insert fails	500 returned	Server error message

Database Operations

Operation	Table	Key Columns	Auth
INSERT	content_items	All identity, content body, AI, audit columns + ingest_source + content_owner_id	Editor+
INSERT	content_history	content_item_id, version=1, change_reason='initial_ingest' (via trigger)	service via DEFINER
INSERT	content_chunks	content_item_id, position, heading_path, embedding	service-role
INSERT	pipeline_runs	pipeline_name='background_classify' and 'background_summarise' (via recordPipelineRun)	service-role

---

Workflow 2: Single-Field Update (PATCH)

Trigger: User edits a field inline via PATCH /api/items/[id] Owner: app/api/items/[id]/route.ts

Flow

Validate → Branch on field
   ├─ field='publication_status' → optimistic-concurrency guarded transition
   ├─ field='answer_standard' / 'answer_advanced' → rebuild content with Q: prefix
   └─ default → direct column update + history row
→ Conditional embedding regeneration → Conditional reclassification warning → Return with warnings envelope

Detailed Steps

1. Validate update

   • Schema: ItemUpdateBodySchema
   • Body: { field, value, regenerate_embedding?, reclassify?, change_reason?, fromStatus? }. The discriminated union field accepts publication_status, governance_review_status, superseded_by, content-affecting fields, etc.

2. publication_status branch (§5.2 Phase 2 + 2.5)

   • Fetch current row including publication_status, archived_at, archived_by, archive_reason.
   • Compute allowedTransitions = computeAllowedTransitions(fromStatus, role) from lib/governance/publication-transitions.ts. An empty allowed array returns 403.
   • Update with .eq('publication_status', fromStatus) filter (optimistic-concurrency guard, V1-M4 fix). 0 rows on the row-existence check → 409 PGRST116.
   • Apply applyTransitionSideEffects({ supabase, itemId, fromStatus, newStatus, ... }) — e.g. archived ⇄ NULL archived_at invariant enforced by the bidirectional enforce_archive_state_consistency trigger.
   • Insert content_history row with change_summary='Publication status: {fromStatus} -> {newStatus}'.

3. Q&A field branch (S198)

   • When field === 'answer_standard' or 'answer_advanced' AND currentItem.content_type === 'q_a_pair', the route rebuilds content_items.content as Q: {question}\n\n{answer_standard}\n\n{answer_advanced} (omits Q: prefix when question is empty per §4.1 H2 fix).
   • The new value of the edited field is written; the other answer field is preserved.

4. Default branch

   • Direct column update on content_items (sets updated_at, updated_by).

5. Conditional embedding regeneration

   • When regenerate_embedding: true or content-affecting fields change, regenerate the embedding from ${title}\n\n${plainText}.

6. Reclassification warning

   • PATCH does not trigger reclassification directly. User is warned to call POST /api/items/[id]/classify separately when content has changed.

7. History entry

   • New version in content_history with change_reason and change_summary (and change_type='update' for default-branch edits, change_type='publication_state_change' for publication transitions).

State Transitions (publication_status §5.2 Phase 2)

Current State	Role-allowed transitions (illustrative — see computeAllowedTransitions)
draft	editor → in_review / published (publish from draft); admin → all
in_review	editor → published / draft; admin → all
published	editor → archived (with reason); admin → all
archived	admin → published (un-archive)

The legacy governance_review_status column is now restricted to {'pending', 'approved', 'reverted', 'changes_requested', 'review_overdue'} ('draft' removed S201).

---

Workflow 3: File Upload Pipeline (EP3)

Trigger: User uploads file via POST /api/upload Owner: app/api/upload/route.ts Typed value: ingest_source='upload'

Flow

Auth → Parse multipart → Validate size + magic bytes → Resolve owner → Compute MD5 → Create pipeline_run → detect_reupload RPC → INSERT content_item (typed ingest_source) → Upload to storage → INSERT source_documents (with parent_id if new_version) → Link via source_document_id FK → Extract text (PDF/DOCX/MD/TXT) → Date extraction → Dedup soft-block → UPDATE content (single pass with stamp) → Embed → Chunk → Classify → Summarise → Quality score → Layer + topic + guide section

Detailed Steps

1. Auth + role check (['admin','editor']).

2. Parse + validate

   • 50 MB cap. MIME types: PDF / DOCX / MD / TXT.
   • Magic-byte validation: PDF must start with %PDF (0x25 50 44 46); DOCX must start with PK\x03\x04 (ZIP signature). Markdown / TXT have no reliable magic bytes — extension is trusted.

3. Resolve owner

   • Form-data content_owner_id UUID Zod-validated via narrow schema before resolveContentOwnerId({ explicit, role, userId }) (S206 M-2 fix).

4. Compute MD5 hash of the buffer for re-upload detection.

5. Create pipeline_runs record

   • pipeline_name='file_upload', status='running', with progress JSONB tracking the 6-step pipeline.

6. Re-upload detection

   • detect_reupload(filename, uploaded_by, content_hash) RPC. Returns match_type='identical' (warn but continue) or 'new_version' (sets parent_id on the new source_documents row).

7. Initial content_items INSERT (with typed ingest_source='upload')

   • Empty content field initially; populated post-extraction.
   • metadata.ingestion_source='upload' mirror retained for back-compat.
   • Trigger writes v1 content_history.

8. Storage upload + cleanup-on-failure

   • Upload to Supabase Storage documents bucket. Failure → service-role delete on the empty content_items row.

9. Create source_documents row

   • With version, parent_id (when re-upload), content_hash, storage_path, pipeline_run_id, optionally workspace_id.
   • Link content_item via source_document_id FK on content_items.

10. Text extraction

    • PDF: extractPdfText from lib/extraction/pdf.ts (unpdf). Returns { text, pageCount }. Tables NOT supported by unpdf.
    • DOCX: mammoth.convertToHtml → turndown.turndown (gfm) — preserves tables (the convertToMarkdown shortcut drops them).
    • MD/TXT: buffer.toString('utf-8').

11. Date extraction

    • extractTemporalReferences + extractDates + findExpiryDate from lib/date-extraction. Sets expiry_date + lifecycle_type='date_bound' when high/medium-confidence expiry is found.

12. Dedup soft-block

    • checkForDuplicates(serviceClient, extractedText, undefined, { excludeId: itemId }) — excludeId avoids self-match. Stamp written in the same UPDATE pass as the content.

13. UPDATE content_items

    • { content, file_path, dedup_status, metadata, expiry_date?, lifecycle_type? } in a single pass. Metadata includes original_filename, file_size, mime_type, ingestion_source, page_count, tables, temporal_references, suspected_duplicate_of if any.

14. UPDATE source_documents

    • extracted_text, extraction_metadata, status ('processing' or 'failed').

15. Embed → Chunk → Classify → Summarise → Quality → Layer + topic + guide

    • Each step updates the pipeline_runs.progress JSONB and may add warnings.

Error Handling

Error Condition	Handling	User Feedback
Unsupported file type	400 returned	File type error
File too large	413 returned	Size limit error
Magic-byte mismatch	415 returned (declared type mismatch)	Spoof error
Empty file	400 returned	Empty error
Storage upload fails	500 + cleanup of content_items row	Upload error
Text extraction fails	Warning; item created with metadata only	Extraction warning

---

Workflow 4: URL Import Pipeline (EP4)

Trigger: User submits URL via POST /api/ingest/url Owner: app/api/ingest/url/route.ts Typed value: ingest_source='url_import'

Flow

Auth → Rate limit (10/min) → Validate body → SSRF validate → URL identity dup check → Resolve owner → Extract content (Readability) → Quality check → Embed → Dedup soft-block → INSERT (typed ingest_source) → V1 history (trigger) → Classify → Summarise → Quality → Inference

Detailed Steps

1. SSRF validation — validateUrl(url) blocks private/internal IP ranges, localhost, and validates URL format.

2. URL identity check — Direct match on content_items.source_url for non-archived items. Returns { url_already_exists: true, existing_item: {...} } and short-circuits the pipeline.

3. Extract content — extractFromUrl(url) returns { title, content (markdown via Turndown gfm), author, ogImage, ogDescription, contentLength, pageCount, extractionMethod }.

4. Quality check — < 100 chars → 422; < 500 → warning.

5. Embed + Dedup — same as Workflow 1 but excluded from the URL identity branch above.

6. INSERT with typed ingest_source: 'url_import', platform: 'web', source_url, source_domain, optional thumbnail_url and author_name.

7. V1 history via trigger; downstream classify / summarise / quality / inference identical to Workflow 1.

---

Workflow 5: Batch Q&A Creation (EP6)

Trigger: POST /api/items/batch Owner: app/api/items/batch/route.ts Typed value: ingest_source='upload_autosplit'

Flow

Validate + consume single-use batch token → Resolve owner → Create pipeline_run → For each item: INSERT (typed ingest_source) → V1 history (trigger) → Embed → Classify → Summarise → Inference → Quality → Update pipeline_run → Return results

Key Characteristics

• maxDuration: 120 seconds
• Single-use batch token: Prevents duplicate submissions (form-data field; consumed exactly once).
• Sequential processing: Items processed one at a time within the batch.
• Pipeline tracking: Progress recorded in pipeline_runs (pipeline_name='batch_create').
• No chunking: EP6 batch creation does NOT call regenerateChunks(). Items lack heading-level chunks until a manual reclassify or backfill (limitation 9).

---

Workflow 6: Non-Destructive Rollback

Trigger: POST /api/items/[id]/rollback Owner: app/api/items/[id]/rollback/route.ts

Flow

Fetch target version from content_history → Create new version with old content → UPDATE content_items → Return updated item

Behaviour

• Rollback is non-destructive — creates a new version, preserving full history.
• New content_history row with change_type='rollback'.
• Does NOT modify governance_review_status or publication_status.
• Does NOT re-trigger classification or embedding; call separately.

---

Workflow 7: Source Document Version Detection and Diff

Trigger: Re-upload of existing document or manual diff request Owner: app/api/source-documents/[id]/ routes

Flow

Upload → detect_reupload RPC → Create new source_documents version (parent_id link) → Compute diff → Store diff → Impact analysis → Notify → Review (applied/dismissed/pending_review) → Optional: send to governance

State Transitions (Diff Review)

Current State	Action	Next State	Side Effects
pending_review	Apply changes	applied	Content items updated
pending_review	Dismiss	dismissed	No content changes
pending_review	Send to governance	pending_review	Governance review triggered

Diff Algorithms

Mode	Algorithm	Use Case
Q&A	Dice coefficient	Question-answer pair comparison
Full-text	Myers	General document comparison

Version Chain

• Linked via parent_id FK on source_documents.
• Traversed via get_document_version_chain RPC.
• Each version has unique content_hash.

---

Workflow 8: Content Archival

Trigger: POST /api/items/[id]/archive (legacy direct archive route) or PATCH /api/items/[id] with { field: 'publication_status', value: 'archived', archive_reason } (canonical S202).

Flow

Validate reason → publication_status transition → enforce_archive_state_consistency trigger → Item hidden from default browse / search

Behaviour

• Soft archive — record remains in database.
• The enforce_archive_state_consistency PL/pgSQL trigger keeps archived_at <-> publication_status='archived' in sync across 4 directions.
• Archived items excluded from default browse and from hybrid_search / search_for_form_response (unless include_archived=true).
• archived_at timestamp + archived_by user ID + archive_reason text.
• No cascade effects on linked records (workspaces, history, read marks preserved).

---

Workflow 9: Deduplication Check

Trigger: Part of creation/ingestion pipelines or standalone POST /api/dedup/check Owner: app/api/dedup/check/route.ts

Two-Stage Process

1. Exact match: MD5 hash comparison via content_items.content_text_hash (GENERATED ALWAYS).
2. Near-duplicate: Cosine similarity of embeddings with threshold 0.92.

Coverage (S183 + S184)

Dedup soft-block is wired at all 11 ingest entry points:

Layer	Entry Points	Helper
Python (4)	EP1 URL / EP2 markdown / EP2b Stage 2 markdown / EP8 Q&A import	check_content_hash_duplicate(), normalise_title_for_dedup() (scripts/kb_pipeline/dedup.py)
TypeScript (7)	EP3 upload / EP4 URL / EP5 manual / EP6 batch / EP9 MCP / EP10 form outcome / EP11 RSS	resolveDedupStamp() (lib/dedup/content-dedup.ts)

Admin override: skip_dedup=true on 5 Zod schemas; non-admin silently ignored.

Result

• Soft-block: items stamped dedup_status='suspected_duplicate' with pointer in metadata.suspected_duplicate_of. Insert succeeds.
• Standalone endpoint can be used for pre-flight checks.

---

Workflow 10: Supersession (S186)

Trigger: Three mechanisms — admin UI / MCP supersede_content_item / Python CLI --auto-supersede (filename heuristic).

Flow

Validate inputs (both rows exist, neither already superseded, not self-ref) → setSupersession() / set_supersession() → Write superseded_by FK on old row → Transition dedup_status to 'superseded' → Sentry breadcrumb

Shared Setter

Language	File	Function
TypeScript	lib/supersession/set.ts	setSupersession()
Python	scripts/kb_pipeline/supersede.py	set_supersession()

Search Behaviour

include_superseded BOOLEAN DEFAULT false param on hybrid_search and search_for_form_response. WHERE clause: (include_superseded OR ci.superseded_by IS NULL). All MCP retrieval tools default to include_superseded=false. Direct ID lookup still returns the row.

---

Workflow 11: Recurring Review Cadence (§5.5 Phases 1-5)

Trigger: Daily cron at app/api/cron/review-cadence/route.ts (03:45 UTC). Owner: Cron route.

Flow

Daily 03:45 UTC → Query content_items WHERE next_review_date < CURRENT_DATE AND governance_review_status IN (NULL, 'approved') → Flag as 'review_overdue' → Insert 1 notification per recipient (idempotent within day) → Batch summary at threshold 20 → Record pipeline_runs (pipeline_name='review_cadence')

Auto-renewal

The 'approve' branch in both app/api/governance/review/route.ts and lib/mcp/tools/governance.ts calls computeNextReviewDate({ reviewedAt, cadenceDays }) from lib/governance/cadence-renewal.ts (GREATEST formula with NaN coercion) and writes the new next_review_date.

MCP filter widening (S208 §5.5 Phase 4)

• The find tool’s chunk branch (backed by the search_content_chunks RPC) accepts overdue_review: boolean and review_due_within_days: integer (1-365) (RPC-level filters).
• whats_in_my_queue (facet: governance) accepts include_overdue: boolean (default true) and status_filter: enum('pending'|'review_overdue'|'all').

Cadence-compliance scorer (S208 §5.5 Phase 5)

cadenceCompliancePenalty(nextReviewDate, now) in lib/quality/quality-score.ts returns 0/5-10/15/25/40 per the spec §9.3 schedule:

• > 30d until due → no penalty
• 1-30d until due → graduated linear -10
• 1-14d overdue → -15
• 15-30d overdue → -25
• > 30d overdue → -40

Boundary: daysUntilDue === 0 falls into the overdue ≤14 tier (-15) per spec control flow. Preservation rule §9.4: items with no next_review_date produce IDENTICAL scores to pre-Phase-5.

---

Workflow 12: Markdown Batch Ingest UI (EP2 §1.11)

Trigger: Admin or editor drops one or more .md files on /item/new “Upload file” tab. Owner: app/api/ingest/markdown/route.ts + lib/ingest/markdown-orchestrator.ts. Typed value: ingest_source='upload'. Pipeline name: upload_markdown_batch.

Flow (two-phase POST on the same endpoint)

[ANALYSE] Auth → Multipart parse → Validate (count ≤10, per-file ≤1 MB, total ≤5 MB, .md only, UTF-8 only) → For each file: front-matter parse + extractMarkdownTitle + cleanMdxTags + diff-marker scan + checkExactDuplicate + sourceFileMatch query → Return MarkdownIngestAnalysis[] (no DB writes)

[IMPORT] Auth → Multipart parse → Same validation as analyse → BatchOptionsSchema parse via parseBody() → startPipelineRun (Pattern E Step 1: status='running' INSERT) → Per-file loop: for each file → cleanMdxTags + dedup pre-check → INSERT content_items (with publication_status from draft/final mapping per D-A) → classifyContent() → regenerateChunks() → push to stored[] / dedup_flagged[] / errored[] → updatePipelineProgress (Pattern E Step 2: silent-catch UPDATE per file) → finaliseRun (Pattern E Step 3: terminal UPDATE via service-role client) → Return { pipeline_run_id, results_summary } to caller

Detailed Steps (import phase)

1. Auth + role check (['admin', 'editor']) via getAuthorisedClient.
2. Multipart parse + validation — file count ≤10, per-file size ≤1 MB (early-return 413 on first violation), total batch size ≤5 MB, .md extension only, UTF-8 decoding via TextDecoder('utf-8', { fatal: true }).
3. BatchOptionsSchema.parseBody() — Zod schema for the JSON options field; rejects unknown fields per feedback_validation_sweep_safeparse_ban.
4. startPipelineRun — INSERT pipeline_runs row with pipeline_name='upload_markdown_batch', status='running', pre-generated UUID, progress.detail carrying the file count.
5. Per-file loop (sequential — Vercel single-Lambda; serverless without parallelism per spec D-B):
   • cleanMdxTags() strips PascalCase MDX tags (Python parity per clean_mdx_tags()).
   • checkExactDuplicate() returns dedupVerdict.{isDuplicate, existingId, existingTitle}.
   • INSERT content_items row with full payload (title, content, ingest_source='upload', content_owner_id, publication_status from draftFinalToPublicationStatus() mapping per D-A: draft → 'draft', final → 'in_review', unknown → 'draft'; soft-block dedup_status stamped if applicable). governance_review_status left NULL on insert.
   • classifyContent() populates entities, relationships, embedding, summary in-place.
   • regenerateChunks() splits markdown at H2 boundaries into content_chunks rows with per-chunk embeddings.
   • Push outcome to stored[]. If suspectedDuplicateOf set, ALSO push to dedup_flagged[] (subset relationship — dedup_flagged[] is a SUBSET of stored[], never disjoint).
   • On per-file error: catch + push to errored[]; loop continues.
   • updatePipelineProgress() mid-flight UPDATE with files_completed counter (silent-catch on transient DB blip).
6. finaliseRun — terminal pipeline_runs UPDATE via internal createServiceClient() (chokepoint per S213 fix; closed S214 by adding admin UPDATE/DELETE policies but service-role pattern retained for cron/orchestrator parity per CLAUDE.md gotcha “RLS chokepoint”). Sets status='completed' | 'completed_with_errors' | 'failed' (computeRunStatus per error/success counts), result=results_summary JSONB, completed_at.
7. Return { pipeline_run_id, results_summary } to the caller. UI stops polling.

Pattern E (server-writes + client polling) is introduced END-TO-END

Spec §7.2 (post-S215 T9 + W5 V_W4 correction): EP2 is the FIRST surface that ships both halves of Pattern E — server-side writes (via lib/ingest/markdown-orchestrator.ts calling lib/pipeline/start-run.ts + lib/pipeline/update-progress.ts + finaliseRun) AND a client polling consumer (UI fires GET /api/pipeline-runs/:id every 1-2s during the import POST). EP3 has the server-side half today via app/api/upload/route.ts — 11 call sites total: 1 INSERT (L250-251), 1 raw UPDATE (L367-371), 9 updatePipelineProgress invocations. EP3 UI consumer is the 61 backlog retrofit.

Error Handling

Error Condition	Handling	User Feedback
File count >10	400 returned	”Maximum 10 files per batch”
Single file >1 MB	413 returned (early-return)	Per-file size error
Total batch >5 MB	413 returned	Total size error
Mixed batch (some .md + some non-.md)	400 returned	Mixed-type error
All-non-.md batch	415 returned	Type mismatch
Non-UTF-8 file	415 returned (BEFORE orchestrator runs)	Encoding error
Per-file pipeline error	Push to errored[]; loop continues	Surfaced in summary card
pipeline_runs finaliseRun fails	Sentry breadcrumb; results still returned	Toast warning (rare)

---

Automated Processes

Process	Trigger	Route/Module	Purpose
Freshness calculation	On-demand / batch	/api/freshness/calculate	Score based on lifecycle_type rules
Freshness recalc all	Admin action	/api/freshness/recalculate-all	Recalculate entire knowledge base
Quality scoring	Post-creation / cron	/api/cron/quality-score/route.ts	Recompute composite quality score
Read mark tracking	User reads content	/api/read-marks	UPSERT per user per item
Review cadence flagging	Daily 03:45 UTC	/api/cron/review-cadence/route.ts	Flag overdue items + emit notifications

Integration Points

External System	Direction	Protocol	Purpose
Supabase	Read/Write	REST + RPC	Primary data store, vector search, RPC
Claude API	Request	HTTP	Classification, summarisation, vision
OpenAI API	Request	HTTP	Embedding generation (text-embedding-3-large)
Anthropic Files API	Request	HTTP	PDF upload for vision analysis (max 32MB)
Supabase Storage	Read/Write	REST	File upload for PDF / DOCX

Handoff Points to Other Feature Areas

Target Feature Area	Mechanism
Quality Governance	governance_review_status field; send-to-review route; review-on-change triggers. publication_status='in_review' is the new canonical “awaiting publication” state (§5.2).
Knowledge Organisation	Classification populates domain/subtopic; tag morphology canonicalisation feeds ai_keywords; guide section + topic suggestions feed taxonomy; tag drift triage at /settings?section=tag-morphology
Search	Embedding vector + chunk embeddings consumed by hybrid_search / search_content_chunks RPCs. search_content_chunks accepts cadence filters (S208 §5.5 Phase 4).
Bid Management	Workspace assignments via content_item_workspaces; effectiveness via get_content_win_rate RPC; source_bid FK; bid-outcome integration EP10 writes back via bid_outcome_integration ingest source
AI Integration	pipeline_runs rows record every classify / summarise / mcp_create_content_item / batch_create / file_upload / url_ingest / review_cadence run; consumed by Provenance Pipeline Health tab

Governance Review Status — State Machine (post-S201)

Status	Meaning
NULL	Default — no active change-management review
pending	Submitted for governance review
approved	Approved — transitions to NULL (published)
changes_requested	Reviewer requested modifications
reverted	Valid status value — not set by rollback route
review_overdue	Cadence-elapsed (set by daily cron — §5.5 Phase 2)

'draft' was REMOVED from the CHECK constraint S201; draft state lives on the new publication_status column.

Transition Rules

NULL ←→ pending → approved → NULL
NULL → pending → changes_requested → (edit) → pending → ...
NULL / approved → review_overdue (via cron only)
review_overdue → approved (with auto-renewed next_review_date)

Content Freshness — Lifecycle Rules

Lifecycle Type	Fresh	Aging	Stale	Expired
evergreen	< 6 months	6-12 months	12-18 months	> 18 months
date_bound	Before expiry	Near expiry	At expiry	Past expiry
regulation	Current	Review due	Outdated	Superseded
bid_discovered	< 3 months	3-6 months	6-9 months	> 9 months

Freshness transitions: fresh → aging → stale → expired.

Current Limitations

1. Pipeline steps that fail non-fatally are captured as warnings but not retried automatically.
2. Batch Q&A processes items sequentially — no parallel processing within a batch.
3. EP6 batch / EP10 form outcome / EP11 RSS feed do not call regenerateChunks() — items from these paths lack heading-level chunks until a manual reclassify or backfill.
4. Re-upload detection relies on content_hash — renamed files with identical content are correctly matched, but modified files with the same name create new versions.
5. quality_score calculation handles both ‘ageing’ and ‘aging’ spellings due to legacy normalisation.
6. §5.2 Phases 3 (RPC visibility flip), 4 (publication-review queue tab), and 5 (supersession + cron-exclusion) remain on the roadmap.
7. Near-duplicate cosine matches are detected by POST /api/dedup/check but not wired to the soft-block stamp flow (only exact-hash matches stamp). The near-dedup review dashboard + merge UI is OPS-3 Phase 2.