Building an Agency Knowledge RAG System: A Practical Architecture

Most agencies store important information across multiple systems:

• Contracts and proposals in shared folders
• Invoices and payments in QuickBooks
• Companies and deals in a CRM
• Project details inside PDFs, spreadsheets, and presentations
• Client aliases and naming variations across all of them

A user may ask:

"How much revenue did we receive from Acme last quarter, and which contract was it related to?"

No single system can answer that question.

The CRM knows the customer. QuickBooks knows the payment. The contract document explains the scope. A useful answer requires combining all three while preserving permissions, citations, and confidence.

That is the purpose of an Agency Knowledge RAG system.

This article walks through the architecture as I would explain it to an engineer building their first production RAG system.

RAG stands for Retrieval-Augmented Generation.

Instead of asking a language model to answer entirely from its training data, we first retrieve relevant information from our own systems. We then provide that evidence to the model.

The basic flow is:

Copy
User question
    ↓
Search internal knowledge
    ↓
Retrieve relevant evidence
    ↓
Give evidence to the language model
    ↓
Generate an answer with citations

This sounds simple, but production RAG is not just:

Copy
Upload PDFs → create embeddings → ask ChatGPT

That approach quickly breaks when you introduce:

• Structured financial data
• Duplicate company names
• Outdated documents
• User permissions
• Conflicting sources
• Missing evidence
• Questions that require combining multiple systems

A reliable architecture needs separate layers for ingestion, storage, retrieval, authorization, and answer generation.

The system has six major parts:

Copy
Data Sources
    ↓
Ingestion Pipeline
    ↓
Knowledge Storage
    ↓
Retrieval and Reranking
    ↓
Permission Filtering
    ↓
Answer Layer
    ↓
CLI, MCP server, Claude, or ChatGPT

Each layer should have one clear responsibility.

Do not build one large function that loads documents, searches them, checks permissions, and calls the model. That design becomes impossible to debug.

The architecture starts with three types of data.

NAS Documents

The first source is a shared network drive or NAS containing files such as .docx, .pptx, and .pdf.

These documents may contain:

• Client contracts
• Proposals
• Statements of work
• Meeting summaries
• Project plans
• Pricing documents
• Internal reports

This is mostly unstructured data.

A PDF might say:

Acme Corporation signed a six-month website redevelopment agreement for $120,000.

The information is useful, but it is trapped inside natural-language text.

QuickBooks CSV Exports

QuickBooks exports contain structured financial information such as invoices, payments, outstanding balances, invoice dates, payment dates, customer names, and transaction identifiers.

Example:

Unlike documents, this information should not be treated only as text.

Financial questions often require exact operations:

sql
Copy
1SUM(amount)
2WHERE customer_id = ?
3AND payment_date BETWEEN ? AND ?

Embeddings are useful for finding relevant text. They are not a replacement for SQL.

CRM CSV Exports

The CRM may contain companies, contacts, deals, deal stages, account owners, expected revenue, and client status.

The CRM helps connect financial transactions and documents to one business entity.

For example:

Copy
CRM company: Acme Corporation
QuickBooks customer: ACME Ltd.
Contract name: Acme Web Redesign

A human understands that these probably refer to the same client.

The system must learn that relationship explicitly.

The ingestion layer converts raw files and exports into clean, searchable knowledge.

Its responsibilities are:

Copy
Parse
Chunk
Apply lifecycle rules
Resolve entities
Add metadata
Store normalized records

This is where most production RAG quality is won or lost.

A better model cannot compensate for poor ingestion.

Each file type requires a parser.

For example:

Copy
PDF   → text, page numbers, headings, tables
DOCX  → paragraphs, headings, tables
PPTX  → slide text, speaker notes, slide numbers
CSV   → validated structured rows

Do not throw away source structure.

The following metadata becomes important later:

json
Copy
1{
2  "source_file": "Acme Contract.pdf",
3  "page": 7,
4  "section": "Payment Terms",
5  "document_type": "contract",
6  "modified_at": "2026-06-10"
7}

Without this metadata, you cannot provide useful citations.

A citation saying "Acme Contract.pdf" is weaker than:

Acme Contract.pdf, page 7, Payment Terms

Large documents must be divided into smaller searchable units called chunks.

A naive implementation splits text every 500 characters. That often cuts sentences, tables, and sections in the wrong place.

Prefer semantic boundaries:

Copy
Heading
    ├── Paragraph
    ├── Paragraph
    └── Table

A contract should ideally be chunked by clause, section, subsection, table, and page boundary when necessary.

For example:

json
Copy
1{
2  "chunk_id": "contract_acme_4_2",
3  "title": "Payment Schedule",
4  "text": "The client will pay $20,000 at the end of each month...",
5  "page": 4
6}

Keep enough context for the chunk to make sense independently.

Not every document should remain active forever.

Agencies often have draft proposals, superseded contracts, cancelled statements of work, old pricing documents, duplicate exports, and archived client folders.

Lifecycle rules decide whether a record is:

Copy
active
draft
expired
superseded
archived
deleted

For example, suppose both files exist:

Copy
Acme-SOW-v2.pdf
Acme-SOW-FINAL.pdf

The retrieval system should not treat them as equally authoritative.

The final signed version should receive a higher source priority.

A useful metadata field might be:

json
Copy
1{
2  "lifecycle_status": "active",
3  "is_signed": true,
4  "supersedes": "Acme-SOW-v2.pdf"
5}

Entity resolution connects different names that refer to the same real-world object.

Consider these names:

Copy
Acme
Acme Ltd
ACME Corporation
Acme Corp.
ACME-001

Without entity resolution, the system may think they are five separate customers.

A normalized entity record might look like:

json
Copy
1{
2  "entity_id": "company_00142",
3  "canonical_name": "Acme Corporation",
4  "aliases": [
5    "Acme",
6    "Acme Ltd",
7    "ACME Corp."
8  ],
9  "quickbooks_customer_id": "QB-9182",
10  "crm_company_id": "CRM-441"
11}

All related documents, invoices, payments, and deals should point to the same internal entity ID.

This is essential for cross-system questions.

The design uses different storage engines for different data types.

Copy
Vector index → semantic document search
SQLite       → exact financial queries
Metadata     → filtering, authority, aliases, permissions

This separation is important.

Do not force every type of information into a vector database.

The vector index stores embeddings for document chunks.

It is useful for questions such as:

"What were the termination terms in the Acme agreement?"

The question may not contain the exact wording used in the contract. Semantic search can still find related text such as:

Either party may terminate the engagement with thirty days' written notice.

A stored vector record may contain:

json
Copy
1{
2  "id": "chunk_8841",
3  "text": "Either party may terminate...",
4  "embedding": [0.018, -0.021, 0.044],
5  "metadata": {
6    "company_id": "company_00142",
7    "document_type": "contract",
8    "page": 12,
9    "permission_group": "legal",
10    "lifecycle_status": "active"
11  }
12}

Chroma is reasonable for an initial deployment because it is simple to run and works well for a contained internal knowledge base.

At larger scale, the same architecture could use PostgreSQL with pgvector, OpenSearch, Qdrant, Weaviate, or Pinecone.

The component can change without changing the overall design.

Invoices and payments belong in a relational database.

Example schema:

sql
Copy
1CREATE TABLE invoices (
2    invoice_id TEXT PRIMARY KEY,
3    company_id TEXT NOT NULL,
4    invoice_date DATE NOT NULL,
5    amount_cents INTEGER NOT NULL,
6    status TEXT NOT NULL,
7    source_file TEXT NOT NULL
8);
9
10CREATE TABLE payments (
11    payment_id TEXT PRIMARY KEY,
12    invoice_id TEXT,
13    company_id TEXT NOT NULL,
14    payment_date DATE NOT NULL,
15    amount_cents INTEGER NOT NULL,
16    source_file TEXT NOT NULL
17);

Now a question such as:

"How much did Acme pay between April and June?"

can be answered with an exact query:

sql
Copy
1SELECT SUM(amount_cents)
2FROM payments
3WHERE company_id = 'company_00142'
4  AND payment_date >= '2026-04-01'
5  AND payment_date < '2026-07-01';

That produces a deterministic result.

You can still create text summaries of financial records for semantic discovery, but final calculations should come from structured queries.

Metadata ties the entire system together.

Useful fields include:

Copy
company_id
document_type
source_system
created_at
modified_at
effective_date
expiration_date
lifecycle_status
author
permission_group
page_number
deal_id
invoice_id
source_priority

Aliases should also be searchable.

When a user asks about "Acme," the system should resolve it to the canonical entity before retrieval begins.

When a query arrives, the system does not immediately send it to the language model.

It first converts the user's question into a retrieval plan.

For example:

"How much did Acme pay last quarter, and what project was the payment for?"

The planner should identify:

Copy
Entity: Acme Corporation
Time range: previous quarter
Intent 1: calculate received payments
Intent 2: identify related project or contract
Required sources: QuickBooks + contract documents + CRM

This is a multi-source query.

Hybrid search combines multiple search techniques.

A useful retrieval pipeline may include:

Copy
Semantic vector search
+
Keyword search
+
SQL queries
+
Metadata filters

Each method solves a different problem.

Semantic search — useful when wording differs:

Copy
Query: ending the agreement
Document: termination clause

Keyword search — useful for exact identifiers:

Copy
INV-1024
SOW-ACME-2026
Project Falcon

SQL — useful for exact calculations:

Copy
total payments
unpaid invoices
revenue by customer

Metadata filtering — useful for narrowing the search:

Copy
company_id = Acme
document_type = contract
lifecycle_status = active

A production retrieval flow might look like:

python
Copy
1def retrieve(query, user):
2    entity = resolve_entity(query)
3    intent = classify_intent(query)
4
5    document_hits = vector_search(
6        query=query,
7        filters={
8            "company_id": entity.id,
9            "lifecycle_status": "active",
10        },
11    )
12
13    keyword_hits = keyword_search(query)
14
15    financial_rows = run_financial_query(
16        query=query,
17        entity_id=entity.id,
18    )
19
20    return merge_results(
21        document_hits,
22        keyword_hits,
23        financial_rows,
24    )

Retrieval gives us candidate results. It does not guarantee that the best result appears first.

That is the job of reranking.

A possible ranking policy could be:

The exact ordering depends on the organization.

The important point is that authority should be encoded in the system, not left entirely to the language model.

A simple scoring model could be:

Copy
Final score =
    semantic relevance
    + keyword relevance
    + source authority
    + freshness
    + entity match
    - stale-document penalty

For example:

python
Copy
1score = (
2    0.40 * semantic_score
3    + 0.20 * keyword_score
4    + 0.20 * authority_score
5    + 0.10 * freshness_score
6    + 0.10 * entity_match_score
7)

Do not treat those weights as universal. They should be tested against real questions.

Suppose the CRM says a deal is worth $120,000, but the signed contract says $110,000.

The system should not silently choose one.

It should recognize the conflict:

Copy
Signed contract: $110,000
CRM deal record: $120,000

Because the signed contract has higher authority, the answer may say:

The signed contract lists the project value as $110,000. The CRM currently lists $120,000, so the CRM record may need to be updated.

That is much better than inventing a single "clean" answer.

Internal knowledge systems must enforce access control.

A junior implementation often retrieves everything and tells the model:

Do not reveal documents the user cannot access.

That is not security.

The model should never receive unauthorized evidence.

The permission layer must filter retrieved results before they are added to the model context.

Example roles:

Copy
account_manager
finance
legal
operations
admin

Example access rules:

Copy
Account managers → assigned clients
Finance          → invoices and payments
Legal            → contracts
Operations       → project documents
Admin            → all sources

A permission check may look like:

python
Copy
1def can_access(user, record):
2    if user.role == "admin":
3        return True
4
5    if record.permission_group in user.permission_groups:
6        return True
7
8    if record.company_id in user.assigned_company_ids:
9        return True
10
11    return False

Then:

python
Copy
1authorized_results = [
2    result
3    for result in retrieved_results
4    if can_access(user, result)
5]

Permission filtering after retrieval is necessary, but permission rules should also be applied during retrieval whenever possible.

For example:

python
Copy
1vector_search(
2    query=query,
3    filters={
4        "permission_group": {"$in": user.permission_groups}
5    },
6)

Why do both?

• Retrieval-time filtering reduces exposure.
• Post-retrieval filtering provides a second security boundary.
• Defense in depth protects against configuration mistakes.

Never depend only on prompt instructions for authorization.

After retrieval, reranking, and permission checks, the system builds a compact evidence package.

For example:

json
Copy
1[
2  {
3    "rank": 1,
4    "source": "QuickBooks payments.csv",
5    "fact": "Acme paid $60,000 during Q2 2026.",
6    "confidence": 0.99
7  },
8  {
9    "rank": 2,
10    "source": "Acme Website SOW.pdf",
11    "page": 3,
12    "fact": "The engagement concerns website redevelopment.",
13    "confidence": 0.96
14  },
15  {
16    "rank": 3,
17    "source": "CRM deals.csv",
18    "fact": "The deal is named Acme Website Modernization.",
19    "confidence": 0.88
20  }
21]

The answer layer should receive only the strongest relevant evidence.

Sending fifty chunks to the model usually makes the answer worse, not better.

More context does not automatically mean better context.

The answer layer uses the retrieved evidence to produce the final response. Claude works well as the language model here, but the same design works with other capable models.

The answer layer is responsible for:

Copy
Fact versus assumption labels
Citations
Confidence
Conflict reporting
Refusal when evidence is missing

This is where the system turns raw records into a useful explanation.

The model should distinguish between statements directly supported by evidence and reasonable inferences.

Example:

Fact: QuickBooks records show that Acme paid $60,000 during Q2 2026. Fact: The active statement of work describes a website redevelopment project. Assumption: The payment appears to relate to that project because the invoice and contract reference the same client and period.

This is better than presenting the assumption as certainty.

A structured output format can help:

json
Copy
1{
2  "answer": "...",
3  "facts": [],
4  "assumptions": [],
5  "conflicts": [],
6  "citations": [],
7  "confidence": "high"
8}

Every important factual claim should point back to its source.

Example:

Acme paid $60,000 during Q2 2026. Source: QuickBooks payments export, transactions PAY-1041, PAY-1088 and PAY-1120.

For document claims:

The project covers website redevelopment and analytics integration. Source: Acme Website SOW.pdf, page 3, "Scope of Work."

Citations are not decoration. They make the system debuggable.

When a user reports an incorrect answer, an engineer should be able to inspect:

Copy
Which sources were retrieved?
Why were they ranked highly?
Which statements used each source?

Confidence should be based on evidence quality, not on how confident the model sounds.

A simple policy could be:

High confidence

• Multiple authoritative sources agree
• Exact financial query succeeded
• Entity resolution is unambiguous
• Sources are current

Medium confidence

• One strong source exists
• Some inference is required
• Entity match is likely but not perfect

Low confidence

• Sources conflict
• Only weak notes are available
• Important records are missing
• Entity resolution is ambiguous

Avoid fake precision such as:

Copy
Confidence: 93.7%

unless you have actually calibrated that probability against a labelled dataset.

Labels such as high, medium, and low are usually more honest.

A strong knowledge assistant must be able to say:

I could not find enough evidence to answer this reliably.

This is a feature, not a failure.

Suppose a user asks:

"Why did Acme cancel the project?"

The system may find a cancelled deal but no explanation.

It should not invent a reason.

A good response would be:

The CRM shows that the deal was cancelled, but the available records do not contain a documented cancellation reason.

This protects user trust.

The final answer can be exposed through a command-line tool, an internal web application, Slack, Microsoft Teams, an API, an MCP server, or Claude and ChatGPT clients.

MCP stands for Model Context Protocol. It provides a standardized way for an AI client to call tools and access external systems.

The server might expose tools such as:

Copy
search_documents
get_company
get_invoices
calculate_payments
get_active_contract
ask_agency_knowledge

Example tool definition:

json
Copy
1{
2  "name": "calculate_payments",
3  "description": "Calculate payments received from a company during a date range.",
4  "input_schema": {
5    "type": "object",
6    "properties": {
7      "company_id": {
8        "type": "string"
9      },
10      "start_date": {
11        "type": "string"
12      },
13      "end_date": {
14        "type": "string"
15      }
16    },
17    "required": [
18      "company_id",
19      "start_date",
20      "end_date"
21    ]
22  }
23}

This is better than giving the model unrestricted database access.

The model receives controlled, auditable tools.

Let us follow one question through the entire system.

User question:

"How much did Acme pay us last quarter, and what work was it for?"

Step 1: Resolve the entity. The system detects "Acme" and maps it to:

json
Copy
1{
2  "entity_id": "company_00142",
3  "canonical_name": "Acme Corporation"
4}

Step 2: Interpret the time range. The system converts "last quarter" into exact dates:

Copy
Start: 2026-04-01
End:   2026-06-30

The interpretation should be included in the response when date ambiguity matters.

Step 3: Query financial data. SQLite returns three payments, totalling $60,000 received.

Step 4: Search documents. The vector index returns:

Copy
Acme Website SOW.pdf
Acme Analytics Proposal.pdf
Acme Meeting Notes.docx

Step 5: Apply lifecycle rules. The proposal is marked as superseded. The system keeps Acme Website SOW.pdf and reduces or removes the outdated proposal.

Step 6: Rerank sources. The signed statement of work ranks above meeting notes.

Step 7: Apply permissions. The user has access to financial information and Acme's project documents. Unauthorized legal attachments are excluded.

Step 8: Generate the answer. The system produces:

Acme Corporation paid $60,000 between April 1 and June 30, 2026. The payments appear to relate to the website redevelopment engagement, which includes frontend development, CMS migration, and analytics integration.

Sources: QuickBooks payment export; Acme Website SOW.pdf, pages 2–4. Confidence: High.

The number comes from SQL. The project explanation comes from the signed statement of work. The model's job is to connect and explain the evidence, not calculate or invent it.

Putting everything into a vector database. Vector databases are not good at exact arithmetic. Do not calculate financial totals from retrieved text chunks. Use SQL for structured data.

Ignoring entity resolution. Without canonical entities, cross-system retrieval becomes unreliable. "Acme Ltd" and "Acme Corporation" must point to one internal company ID.

Keeping old and new documents equally active. Drafts, expired contracts, and final signed agreements should not have equal authority. Introduce lifecycle status and source priority.

Filtering permissions only in the prompt. The model should never see data the user is not authorized to access. Apply permissions before context construction.

Returning answers without citations. Without citations, users cannot verify answers and engineers cannot debug them. Store source-level metadata during ingestion.

Forcing the model to answer every question. When evidence is missing, refuse or state the limitation. A hallucinated answer is more damaging than no answer.

Retrieving too much context. Ten highly relevant chunks are usually better than fifty loosely related chunks. Retrieval quality matters more than retrieval volume.

A production RAG system needs normal software observability.

For every question, log:

Copy
Query ID
User ID
Resolved entities
Generated retrieval plan
SQL queries executed
Retrieved chunk IDs
Permission decisions
Reranking scores
Sources used in the final answer
Model latency
Token usage
Final confidence

Useful metrics include:

Copy
Retrieval latency
Answer latency
No-evidence rate
Permission-filter rate
Citation coverage
Entity-resolution failure rate
User correction rate
Cost per query

Also store user feedback:

Copy
Correct
Partially correct
Incorrect
Missing source
Outdated source
Permission issue

This is how you improve the system over time.

Do not tune RAG only by manually asking five questions and deciding that the output "looks good."

Create a test dataset of real agency questions.

For each question, define:

Copy
Expected entity
Expected sources
Expected financial result
Sources that must not be used
Required permission role
Expected confidence

Example:

yaml
Copy
1question: "How much did Acme pay in Q2?"
2expected_entity: "company_00142"
3expected_answer: 60000
4required_sources:
5  - "payments_2026_q2.csv"
6forbidden_sources:
7  - "draft_revenue_forecast.xlsx"
8minimum_role:
9  - "finance"

Test each layer separately:

1. Parser tests
2. Chunking tests
3. Entity-resolution tests
4. SQL tests
5. Retrieval tests
6. Permission tests
7. Citation tests
8. End-to-end answer tests

The language model is only one component. Most bugs will come from the surrounding data pipeline.

A reliable agency knowledge assistant is not just a chatbot connected to a folder of PDFs.

It is a controlled data system with:

Copy
Clean ingestion
Canonical entities
Structured and unstructured storage
Hybrid retrieval
Source-aware reranking
Role-based authorization
Evidence-backed generation
Citations and confidence
Refusal when evidence is missing

The main engineering principle is simple:

Let databases provide facts, let retrieval provide evidence, and let the language model explain the evidence.

When these responsibilities are separated correctly, the system becomes easier to test, secure, and trust.

When everything is delegated to the model, the demo may look impressive, but the production system will eventually produce incorrect numbers, expose unauthorized information, or confidently answer from outdated documents.