AI-Powered CRM Insights with RAG and Text-to-SQL

tp_icap · Finance · 2025 · source Agentic: no · promotional bias: med · source-backed · extracted with prompt 0.5.4 (schema v2)

Generated from extraction.json — do not edit by hand. Every claim cites verbatim source evidence; stated means the quote directly asserts the claim, implied means it supports it indirectly.

Problem

Domain: Enterprise CRM insight retrieval

TP ICAP needed business users to extract actionable insights from years of Salesforce meeting notes and structured CRM data without hours of manual search, while preserving permission boundaries and context.

Constraints:

Meeting notes contain qualitative context across products, relationships, and strategy
Structured and unstructured CRM questions require different processing paths
Users may only see data permitted by existing Salesforce access controls

Architecture

ClientIQ is a secure CRM insight assistant with a React/S3 frontend, API Gateway WebSocket connection, Lambda/SNS/SQS event processing, Bedrock model routing, a RAG path through Bedrock Knowledge Bases and OpenSearch Serverless, and a text-to-SQL path through Athena and the Glue catalog. A daily Salesforce connector writes one CSV per meeting plus metadata to S3, while Okta claims are mapped into retrieval and SQL filters to preserve Salesforce permissions.

Diagram confidence: explicit (topology described by the source).

flowchart LR
  react-internal-frontend["react-internal-frontend"]
  api-gateway-websocket["api-gateway-websocket"]
  lambda-connection-handler["lambda-connection-handler"]
  dynamodb-tracking-store["dynamodb-tracking-store"]
  sns-sqs-task-bus["sns-sqs-task-bus"]
  query-router-functions["query-router-functions"]
  salesforce-s3-connector["salesforce-s3-connector"]
  s3-meeting-data-lake["s3-meeting-data-lake"]
  glue-data-catalog["glue-data-catalog"]
  athena-sql-path["athena-sql-path"]
  bedrock-knowledge-base["bedrock-knowledge-base"]
  opensearch-vector-store["opensearch-vector-store"]
  okta-permission-filter["okta-permission-filter"]
  bedrock-model-layer["bedrock-model-layer"]
  bedrock-evaluations-pipeline["bedrock-evaluations-pipeline"]
  react-internal-frontend --> api-gateway-websocket
  api-gateway-websocket --> lambda-connection-handler
  lambda-connection-handler --> dynamodb-tracking-store
  lambda-connection-handler --> sns-sqs-task-bus
  sns-sqs-task-bus --> query-router-functions
  query-router-functions --> bedrock-model-layer
  query-router-functions --> athena-sql-path
  query-router-functions --> bedrock-knowledge-base
  salesforce-s3-connector --> s3-meeting-data-lake
  salesforce-s3-connector --> glue-data-catalog
  salesforce-s3-connector --> bedrock-knowledge-base
  s3-meeting-data-lake --> glue-data-catalog
  s3-meeting-data-lake --> bedrock-knowledge-base
  glue-data-catalog --> athena-sql-path
  athena-sql-path --> okta-permission-filter
  bedrock-knowledge-base --> opensearch-vector-store
  bedrock-knowledge-base --> okta-permission-filter
  bedrock-evaluations-pipeline --> bedrock-knowledge-base

Drag to pan · scroll or use +/− to zoom · reconstructed from the source's described component topology

Components

Component	Role	Interacts with
`react-internal-frontend`	Internal web application hosted in S3 behind an internal ALB for authenticated ClientIQ users.	`api-gateway-websocket`
`api-gateway-websocket`	Real-time bidirectional connection between users and backend processing.	`lambda-connection-handler`
`lambda-connection-handler`	Processes incoming payloads, logs tracking data, and publishes downstream work.	`dynamodb-tracking-store`, `sns-sqs-task-bus`
`sns-sqs-task-bus`	Event-driven task distribution for Lambda functions that handle different query workflows.	`query-router-functions`
`query-router-functions`	Bedrock-powered routing layer that chooses structured text-to-SQL or unstructured RAG.	`bedrock-model-layer`, `athena-sql-path`, `bedrock-knowledge-base`
`salesforce-s3-connector`	Daily SOQL connector that exports tailored Salesforce meeting data into S3 as per-meeting CSV and metadata files.	`s3-meeting-data-lake`, `glue-data-catalog`, `bedrock-knowledge-base`
`s3-meeting-data-lake`	Storage location for meeting CSV files and metadata used by both Athena and Bedrock Knowledge Bases.	`glue-data-catalog`, `bedrock-knowledge-base`
`glue-data-catalog`	Catalog populated after S3 ingestion so Athena can query structured CRM data.	`athena-sql-path`
`athena-sql-path`	Structured analytical-query path for counts, regions, and meeting report questions.	`okta-permission-filter`
`bedrock-knowledge-base`	RAG retrieval layer over OpenSearch Serverless with manual and implicit metadata filtering.	`opensearch-vector-store`, `okta-permission-filter`
`opensearch-vector-store`	Vector store containing Titan embeddings and metadata for meeting-note retrieval.	—
`okta-permission-filter`	Access-control layer that maps group claims to Athena and OpenSearch filters.	—
`bedrock-model-layer`	Managed model access for classification, text-to-SQL, tagging, generation, and evaluation.	—
`bedrock-evaluations-pipeline`	Development and CI/CD evaluation pipeline for RAG retrieval and generation quality.	`bedrock-knowledge-base`
`dynamodb-tracking-store`	Tracking-data store for connection and request metadata.	—

Data flow

A user logs into the internal React app and opens a WebSocket session through API Gateway. Lambda records tracking data, publishes work through SNS/SQS, and routes the question to either Athena for structured analytical queries or a Bedrock knowledge base for meeting-note retrieval. Salesforce records are ingested daily into S3, chunked as one CSV per meeting, tagged with metadata, synced into OpenSearch Serverless, and filtered by Okta-derived access claims before answers return over the WebSocket.

3 supporting quotes

“After logging in, a WebSocket connection is opened between the client and Amazon API Gateway to enable real-time, bi-directional communication.”article, stated

“The Lambda functions use Amazon Bedrock FMs to determine whether a question is best answered by querying structured data in Amazon Athena or by retrieving information from an Amazon Bedrock knowledge base.”article, stated

“The custom chunking happens as part of the ingestion process, where the connector splits the data into individual CSV files, one per meeting.”article, stated

Why this architecture

TP ICAP built a custom Bedrock-based system rather than using the CRM's bundled assistant because it needed tighter cost control, Salesforce-specific ingestion, hybrid RAG/text-to-SQL routing, and permission-aware retrieval. Managed Bedrock services let the team move in weeks while still preserving governance, model choice, and CI/CD evaluation discipline.

“Although the team considered using their CRM’s built-in AI assistant, they opted to develop a more customized, cost-effective solution that would precisely match their requirements.”article, stated

“TP ICAP evaluated and selected the best models for their use case and built a production-ready RAG solution in weeks rather than months, without having to manage the underlying infrastructure.”article, stated

Models used: Anthropic Claude 3.5 Sonnet · Amazon Nova Pro · Amazon Titan v1 embeddings · Amazon Bedrock foundation models

Scale

The CRM held tens of thousands of vendor meeting notes collected over many years.
“TP ICAP had accumulated tens of thousands of vendor meeting notes in their CRM system over many years.”article, stated
The system converted research work from hours into seconds.
“their Innovation Lab built a production-ready solution that transforms hours of manual analysis into seconds”article, stated
Initial launch involved 20 users.
“Following the initial launch with 20 users, the results showed that the solution has driven a 75% reduction in time spent on research tasks.”article, stated
The launch drove a 75% reduction in time spent on research tasks.
“the results showed that the solution has driven a 75% reduction in time spent on research tasks.”article, stated
The evaluation set included 100 representative question-answer pairs.
“They worked closely with stakeholders and testing teams to develop a comprehensive set of 100 representative question answers pairs”article, stated

Alternatives considered (roads not taken)

Using the CRM's built-in AI assistant

Rejected because: TP ICAP wanted a more customized and cost-effective solution aligned to its Salesforce data, security, and model-routing needs.

“Although the team considered using their CRM’s built-in AI assistant, they opted to develop a more customized, cost-effective solution”article, stated

Off-the-shelf Salesforce ingestion

Rejected because: Their Salesforce implementation was highly tailored, so they built a custom SOQL connector and per-meeting chunking.

“Rather than using an off-the-shelf option, TP ICAP developed a custom connector to interface with their highly tailored Salesforce implementation”article, stated

Semantic retrieval without metadata filters

Rejected because: Metadata pre-filtering disambiguates queries and preserves permissions before semantic search runs.

“ClientIQ uses hybrid search to first filter documents based on their metadata and then perform semantic search within the filtered results.”article, stated

RAG-only question answering

Rejected because: Structured analytical questions are routed to Athena while unstructured meeting-note questions use the knowledge base.

Patterns

`model-routing` (crm-query-routing)

ClientIQ uses an LLM to decide whether a user question should be answered with RAG over meeting notes or SQL over structured data.

“It uses a large language model (LLM) to analyze each user query to determine the optimal processing path.”article, stated

`rag-retrieval` (meeting-note-search)

Amazon Bedrock Knowledge Bases provides source-attributed retrieval over Salesforce meeting notes for unstructured questions.

“TP ICAP used Amazon Bedrock Knowledge Bases to implement RAG, a technique that enhances generative AI responses by incorporating relevant data”article, stated

`hybrid-search` (metadata-filtered-retrieval)

ClientIQ filters candidate documents by metadata such as city or region before semantic search to disambiguate queries.

“ClientIQ uses hybrid search to first filter documents based on their metadata and then perform semantic search within the filtered results.”article, stated

`query-rewriting` (metadata-filter-inference)

Bedrock FMs analyze queries and rewrite them into metadata-aware retrieval filters before vector search.

“This capability relies on Amazon Bedrock FMs to automatically analyze the query, understand which values can be mapped to metadata fields, and rewrite the query accordingly”article, stated

`eval-driven-development` (rag-quality)

The team built ground-truth questions, ran Bedrock RAG evaluations, compared chunking/model settings, and added evaluation to CI/CD.

“they integrated RAG evaluation directly into their continuous integration and continuous delivery (CI/CD) pipeline”article, stated

`privacy-preserving-processing` (enterprise-access-control)

Okta group claims are mapped into Athena/OpenSearch metadata filters so users only retrieve authorized CRM records.

“Results that don’t match the user’s permission tags are filtered out, so that users can only access data they’re authorized to see.”article, stated

Tradeoffs

Whether to use the CRM's built-in AI assistant.

Chose a customized Bedrock-based solution over the CRM's built-in AI assistant.

Rationale: A custom implementation matched TP ICAP's cost, requirements, security, routing, and tailored Salesforce model more precisely.

“they opted to develop a more customized, cost-effective solution that would precisely match their requirements.”article, stated

How to chunk meeting data for retrieval.

Chose one CSV file per meeting plus metadata tags over default chunking of CRM exports.

Rationale: Meeting-level chunks preserve context and allow metadata filters by date, division, brand, and region.

“the connector splits the data into individual CSV files, one per meeting. These files are also automatically tagged with relevant topics from a predefined list”article, stated

Gotchas (what bit them)

CRM questions can be ambiguous without metadata filtering.

Root cause: A phrase like Chicago can refer to meeting location or company division headquarters.

Resolution: ClientIQ pre-filters by explicit metadata and then runs semantic search within the filtered set.

“a question such as “find notes from executive meetings with AnyCompany in Chicago” can mean meetings with any AnyCompany division that took place in Chicago”article, stated

Off-the-shelf Salesforce ingestion was not flexible enough.

Root cause: TP ICAP's Salesforce implementation had highly tailored data structures.

Resolution: The team built a custom daily SOQL connector and custom chunking/tagging pipeline.

“Rather than using an off-the-shelf option, TP ICAP developed a custom connector to interface with their highly tailored Salesforce implementation”article, stated

RAG quality needed scientific measurement.

Root cause: Model, chunking, embedding, inference, and retrieval settings all affect answers and can regress silently.

Resolution: The team created ground-truth QA pairs and metric-driven automated evaluations.

“The Innovation Lab team faced a common challenge in building their RAG application: how to scientifically measure and improve its performance.”article, stated

Evaluation

TP ICAP evaluates with 100 representative ground-truth QA pairs, Bedrock RAG evaluations, retrieval coverage/relevance metrics, generation factuality/relevance metrics, and CI/CD regression gates.

Metrics: content relevance · content coverage · response relevance · factual accuracy · retrieval precision · contextual comprehension · research-time reduction

“They worked closely with stakeholders and testing teams to develop a comprehensive set of 100 representative question answers pairs”article, stated

“The team tracked content relevance and content coverage for retrieval and quality, and responsible AI metrics such as response relevance, factual accuracy”article, stated

Outcomes

ClientIQ reduced time spent on research tasks by 75%.
The initial launch served 20 users.
Stakeholders reported more comprehensive and contextual insight quality.
Manual analysis workflows that took hours moved toward seconds.

ClientIQ analyzes thousands of CRM responses and tens of thousands of Salesforce meeting records while preserving regional and divisional permission filters.

“Following the initial launch with 20 users, the results showed that the solution has driven a 75% reduction in time spent on research tasks.”article, stated

“transform thousands of meeting records from an underutilized resource into a valuable asset and accelerate time to insights”article, stated