RAG Content Creator Audit: How We Fixed CCC's Stack

We audited and remediated a stalled retrieval augmented generation content-creator for Crohn's and Colitis Canada: the RAG answers became relevant again once we fixed corpus coverage first, cleaned queries, and added an answerability gate. The stack used Airtable, Make.com, Cloudflare Workers, and Pinecone. This guide shows what we changed and why it worked.

RAG content-creator automation is: a pipeline that ingests your approved pages into a vector index, retrieves the best matches for a question, then composes a grounded draft. When it fails, coverage and query quality are usually the culprits, not the embedding model.

The problem it solves

The content team had a RAG assistant that drafted FAQ answers and articles from their knowledge base. Output drifted off-topic and often quoted unrelated YouTube transcripts. Staff lost trust and fell back to manual drafting. The organization suspended use across teams until a fix existed.

How the automation works

We did not swap models or vendors. We fixed the foundation. The architecture keeps the original tools and adds guardrails.

• Airtable content registry: One table tracks approved URLs and high priority documents. This becomes the corpus source of truth instead of pointing the crawler loosely at a whole domain.
• Make.com ingestion flow: A disciplined ingest: fetch source text, normalize and chunk, generate stable IDs, delete stale vectors, then upsert. We added testable logs and retry posture.
• Pinecone vector index: Stores embeddings per chunk with deterministic IDs. We added delete-before-upsert semantics and index health checks to prevent orphans and accidental duplication.
• Cloudflare Worker retrieval: Cleans queries, retrieves candidates, runs an answerability gate, and only then composes a draft with ground-truth excerpts and links.
• Answerability gate: A lightweight LLM decision that separates covered vs uncovered questions so the system can gracefully decline, suggest tracked pages, or ask for human input.

Step-by-step: how to build it

1) Establish a tracked source registry

Answer-first: list exactly which pages and documents the RAG is allowed to cite, and keep that list in one place the pipeline reads.

We moved the source of truth into a single table. Each row defines a canonical URL, a priority flag, and a last-seen checksum. The ingestion flow reads only this list.

// normalize a tracked URL row into a fetch job
type Tracked = { url: string; priority: 'high' | 'normal'; lastHash?: string };
export function toJob(row: Tracked) {
  return {
    url: row.url.trim(),
    priority: row.priority,
    // downstream uses this to decide chunk size and refresh cadence
    chunkStrategy: row.priority === 'high' ? 'fine' : 'coarse'
  };
}

Gotcha: do not anchor ingestion to a naive sitemap or whole-domain crawl. If your overview pages are missing, retrieval will fall back to whatever is overrepresented.

2) Normalize, chunk, and hash deterministically

Answer-first: chunking and IDs must be stable so re-ingests replace the right vectors instead of creating dupes.

import crypto from 'crypto';
 
export function normalizeText(raw: string) {
  // strip boilerplate, collapse whitespace, standardize quotes
  return raw
    .replace(/\r\n?/g, "\n")
    .replace(/[\t ]+/g, ' ')
    .replace(/[""]/g, '"')
    .replace(/['']/g, "'")
    .trim();
}
 
export function chunk(text: string, max = 800) {
  const parts: string[] = [];
  let buf: string[] = [];
  for (const line of text.split('\n')) {
    if ((buf.join(' ').length + line.length) > max) {
      parts.push(buf.join(' ').trim());
      buf = [];
    }
    buf.push(line);
  }
  if (buf.length) parts.push(buf.join(' ').trim());
  return parts.filter(Boolean);
}
 
export function stableId(url: string, part: string) {
  const h = crypto.createHash('sha256').update(url + '::' + part).digest('hex');
  // short, fixed, collision resistant
  return `ccc_${h.slice(0, 24)}`;
}

Gotcha: truncating human-readable IDs can collide and silently create duplicates. Prefer short cryptographic digests derived from URL plus exact chunk text.

3) Delete-before-upsert to prevent orphaned chunks

Answer-first: when a source page changes, remove all prior chunks for that page before adding the new ones.

type VectorClient = {
  queryByUrl: (url: string) => Promise<string[]>; // returns vector IDs
  deleteByIds: (ids: string[]) => Promise<void>;
  upsert: (items: { id: string; values: number[]; meta: any }[]) => Promise<void>;
};
 
export async function replaceVectors(vc: VectorClient, url: string, embeds: { id: string; values: number[]; meta: any }[]) {
  const existing = await vc.queryByUrl(url);
  if (existing.length) {
    await vc.deleteByIds(existing);
  }
  await vc.upsert(embeds);
}

Gotcha: appending without deletes accumulates orphans. Over time this drowns retrieval in stale chunks even if each single run looked fine.

4) Clean queries before retrieval

Answer-first: strip template boilerplate so retrieval sees the real question a reader would ask.

export function cleanQuery(input: string) {
  const drop = [/^topic:/i, /^additional instructions:/i, /^notes:/i];
  return input
    .split('\n')
    .map(l => l.trim())
    .filter(l => l && !drop.some(rx => rx.test(l)))
    .join(' ')
    .replace(/\s+/g, ' ')
    .trim();
}

Gotcha: query pollution lowers relevance even with a great index. Retrieval works best when the query mirrors how a person would phrase it.

5) Add an answerability gate

Answer-first: if nothing retrieved can actually answer the question, do not draft. Decline with a reason or suggest tracked pages to add.

type Candidate = { text: string; url: string };
 
export async function answerable(question: string, cands: Candidate[], decide: (q: string, c: Candidate[]) => Promise<'yes'|'no'|'needs_more'>) {
  if (!cands.length) return { ok: false, reason: 'no_candidates' };
  const verdict = await decide(question, cands.slice(0, 5));
  return { ok: verdict === 'yes', reason: verdict };
}

Gotcha: cosine cutoffs alone rarely separate covered vs uncovered topics. A lightweight LLM decision on top of retrieval avoids confidently wrong drafts.

6) Health checks: coverage mix and self-similarity

Answer-first: instrument the pipeline. Check corpus composition and verify embedding self-similarity to rule out basic failures.

export function mixReport(rows: { source: 'site'|'video'|'doc' }[]) {
  const total = rows.length || 1;
  const by = rows.reduce((a, r) => (a[r.source] = (a[r.source]||0)+1, a), {} as Record<string, number>);
  return Object.fromEntries(Object.entries(by).map(([k,v]) => [k, +(100*v/total).toFixed(1)]));
}
 
export function sanitySimilarity(embed: (t: string) => Promise<number[]>, chunk: string, search: (vec: number[]) => Promise<{ id: string }[]>) {
  // store-then-search the same string should return itself at rank 1 in a healthy index
  return embed(chunk).then(v => search(v));
}

Gotcha: if your corpus mix skews heavily to one noisy source, even perfect retrieval returns noise. A simple composition report surfaces it before editors do.

Where it gets complicated

Coverage, not embeddings, is usually the root cause. In this case the index overrepresented auto transcripts while many canonical site pages were missing. Retrieval dutifully returned what it had. Rebalance the corpus first.

Query pollution blocks relevance. Boilerplate like Topic or Additional Instructions at the top of a prompt pollutes retrieval. Clean queries down to the reader question before you embed.

Titles in metadata do not fix scoring. If titles and categories are only stored as metadata and never embedded, they will not influence similarity. Put meaning inside the embedded text or use rerankers that read metadata explicitly.

Deterministic IDs prevent silent duplication. Truncated or human-readable IDs collide. Use a stable hash from URL plus chunk. That makes delete-before-upsert safe and reversible.

No delete-before-upsert means orphans. Without deletes, re-ingests layer on top. A later search returns outdated fragments mixed with new ones and increases hallucination risk.

Public endpoints and imported webhooks have operational traps. Leaving a worker reachable without a shared secret can be acceptable in a gated rollout, but you must plan a follow-up to add auth. Importing Make.com blueprints mints new webhook URLs. Update any Airtable buttons or scripts that call them.

What this actually changes

For Crohn's and Colitis Canada we built and verified a full remediation package: a coverage-first ingest plan, deterministic IDs and deletes, query cleaning, an answerability gate, and a phased deploy plan. Their organization paused production use, and we respected that: our code and runbooks shipped, tested locally, and remained ready for a client-side deploy when credentials and timing allow.

The structural win is trust: editors get grounded drafts on topics the corpus truly covers, and a graceful decline when it does not. That reduces the rewrite burden and lets the team spend time improving coverage instead of fixing drafts. As context, knowledge workers spend an estimated 1.8 hours a day searching and gathering information, which is roughly 20 percent of a work week (McKinsey Global Institute, 2012: https://www.mckinsey.com/industries/technology-media-and-telecommunications/our-insights/the-social-economy).

Frequently asked questions

Did you replace the model or vendors to fix this?

No. We kept Airtable, Make.com, Cloudflare Workers, and Pinecone. The fix was coverage and guardrails: track sources in one place, clean queries, add an answerability gate, and enforce delete-before-upsert with deterministic IDs. Model swaps are a last resort once foundations are clean.

Can this run in real time for a content team?

Yes. Ingest runs on an interval or on button press when a page changes. Retrieval is fast and stateless. The answerability gate adds a small decision step before drafting. The more important factor is editorial workflow: define which pages are in scope and who approves expanding coverage.

How long does a remediation like this take?

Our audit and fix set for CCC fit into a focused sprint. We shipped code, tests, and a phased deploy plan. Timeline mostly depends on corpus size and access. The biggest variable is approving the tracked URLs and running the initial rebalance.

Do we need to change embeddings or add a reranker?

Not to restore baseline relevance. Coverage and query cleaning delivered the largest gains. Rerankers can add a small lift once the corpus is healthy. Without coverage, rerankers and new embeddings only reorder noise.

What does it cost to run monthly?

Operational costs are modest: vector storage, occasional embeddings for changed chunks, and serverless retrieval. The main investment is the initial audit and the editorial habit of keeping the tracked-URLs list current.

What if our worker is public now?

Plan a two-step rollout. First, fix coverage and retrieval so you can see clean wins. Second, add a shared secret or authentication and rotate any imported webhook URLs. That sequencing avoids debugging auth while you are still diagnosing relevance.

If your RAG assistant lost trust, we have shipped this exact remediation: coverage-first ingest, deterministic IDs and deletes, query cleaning, and an answerability gate. See our playbook on AI search optimization in How to Get Your Business in AI Search Results, explore our AI Search and SEO services, or book a 15 minute call and we will map your stack to this plan.