Case Description The client engaged a21.ai to deliver a production-ready Generative BI chatbot — an AI natural-language query interface that lets pharma business users extract insights from Databricks-hosted Postgres and SQL Server without writing SQL. Built in 2 months on a React/TypeScript frontend and microservices backend with text-to-SQL + RAG
Generative BI for $100M Pharma Analytics Case Description: The client engaged a21.ai to deliver a production-ready Generative BI chatbot — an AI natural-language query interface that lets pharma business users extract insights from Databricks-hosted Postgres and SQL Server without writing SQL. Built in 2 months on a React/TypeScript frontend and microservices backend with text-to-SQL + RAG, AWS Bedrock (Claude), Pinecone/Qdrant on AWS, custom auth + RBAC, prompt governance with versioning, and full HIPAA/PHI/SOC2 compliance controls. Problem Statement: The pharma companies have business analysts, data scientists, and decision-makers who need quick data answers but don't write SQL. The prior state: - SQL is the bottleneck: every question requires someone with schema knowledge to hand-craft a query, batch-run it, and return a result hours or days later. - Generic text-to-SQL tools don't meet pharma bar: off-the-shelf NL-to-SQL tools don't handle pharma-specific HCP, therapy, claims, and market-access schemas; PII/PHI masking required for HIPAA compliance; SOC 2 audit logging and prompt governance; enterprise-grade RBAC where admin-only prompt editing is mandatory; multi-model routing (cost-per-query optimization between Claude and GPT-4). - Context is lost across conversations: asking "and the same for Q4" requires the assistant to remember the prior turn's SQL and data shape. - Debug traceability is weak: when the assistant produces wrong SQL, analysts have no way to see which prompt version, which retrieved example, or which LLM produced it. What Client Needed: A production-grade Generative BI chatbot covering: NL-to-SQL over Databricks-hosted Postgres and SQL Server, RAG-augmented responses, Claude on Bedrock + OpenAI, Pinecone/Qdrant on AWS, custom auth + RBAC, admin prompt governance with approval workflow, real-time chat with history, usage analytics, observability on Client infrastructure, and pharma-grade compliance (PII/HIPAA/PHI/SOC2). Proposed Solution & Architecture: Frontend: React 18 + Vite + TypeScript + Tailwind CSS SPA hosted in Amazon S3 behind Amazon CloudFront (OAC, AWS WAF with Managed Rules + rate-based rules). Backend (Microservices on AWS): - Amazon ECS on AWS Fargate hosting: app-api (Python FastAPI, 2 vCPU / 4 GB, 2-10 tasks behind ALB), app-rag-worker (separate task definition, 4 vCPU / 8 GB, for embedding + retrieval), app-eval-worker. - Application Load Balancer (internal HTTPS with ACM, path-based routing). - Amazon API Gateway (REST) fronts external integrations; Cognito JWT authorizer. Text-to-SQL Pipeline: - Natural-language input, intent classification via Claude 3 Haiku, entity extraction. - Context-aware SQL generation with schema + business-rule grounding. - Cost & rate limiting enforced at API Gateway (per-API-key quotas) + in-app token budget tracker. - Secure execution against Databricks-connected Postgres / SQL Server via Databricks SQL warehouse endpoints (traffic via VPC peering between Client VPC and Databricks workspace). - Result processing, RAG augmentation, response generation. LLM Providers (Multi-provider with Bedrock as Primary): - Amazon Bedrock: anthropic.claude-3-5-sonnet-20241022-v2:0 (enhanced reasoning tier), anthropic.claude-3-haiku-20240307-v1:0 (cost-optimized routing/classification), amazon.titan-embed-text-v2:0 (embeddings). - Model selection router Lambda per query complexity and cost budget. - Amazon Bedrock Guardrails: pharma-grade filters (PII redaction for HCP identifiers, denied topics on off-label promotion, custom word-list filter for regulated drug-name misuse). Vector Store (RAG Layer): - Pinecone on AWS (primary), Qdrant Cloud on AWS (secondary). - Amazon OpenSearch Serverless evaluated as AWS-native alternative. - Hybrid BM25+vector with cross-encoder re-ranking (amazon.rerank-v1:0 Bedrock Reranker). - Context-aware generation with source attribution in JSON output. Data Access: - Primary: Postgres via Databricks SQL warehouses (Databricks on AWS, customer-managed VPC peered to Client VPC). - Secondary: SQL Server via Databricks. - Automated schema discovery via Databricks Unity Catalog API; AWS Glue Data Catalog mirrors metadata for offline analysis. - Amazon Macie scans query results (cached in Redis) for PII/PHI. Authentication & RBAC: - Amazon Cognito User Pools. Single-tenant organization model (one Cognito pool per pharma client). - Amazon Cognito Groups to IAM roles for RBAC (Admin, Analyst, Viewer). - Session state in Amazon ElastiCache for Redis (cluster mode disabled, single shard with replica, encryption-in-transit + at-rest). Prompt Governance (Admin Console): - Prompts stored as versioned JSON in Amazon DynamoDB (PromptVersions table with PromptId + Version composite key). - Approval workflow via Step Functions: Draft, Admin Review, Approved, Deployed (each transition logged to CloudTrail). - One-click rollback via UpdateItem setting ActiveVersion attribute. - Changes audited in AWS CloudTrail Lake with 7-year retention. Observability & Tracing: - AWS Distro for OpenTelemetry (ADOT) instrumentation across all services. - AWS X-Ray for internal traces. - Amazon CloudWatch Logs + Metrics + Alarms. - Automatic PII scrubbing via Amazon Comprehend PII detection inline before log write. - Amazon Managed Grafana dashboards on CloudWatch + X-Ray data. Security & Compliance (Pharma-Grade): - End-to-end encryption: TLS 1.3 in transit; AWS KMS customer-managed CMKs with annual rotation, SSE-KMS on every S3 bucket, SSE-KMS on DynamoDB, storage-encrypted RDS/Aurora, encrypted EBS volumes. - AWS Secrets Manager for credentials with automated rotation Lambda per service. - Tamper-proof audit trails via Amazon S3 Object Lock (compliance mode) on CloudTrail Lake + prompt-approval records. - Data Loss Prevention via Amazon Macie (PHI/PII detection in S3), outbound egress control via AWS Network Firewall. - HIPAA BAA in place with AWS; all services used are HIPAA-eligible. - AWS Config rules for HIPAA Security Rule mappings; AWS Audit Manager for SOC 2 Type II evidence collection. - Amazon GuardDuty + AWS Security Hub for continuous threat detection. Project Outcomes and Success Metrics: Client Scope Commitments Fulfilled: - NL-to-SQL + RAG pipeline - Postgres + SQL Server via Databricks - Claude on Bedrock with cost monitoring - Pinecone + Qdrant on AWS - Custom auth + RBAC (Admin only) - Prompt governance with versioning + approval workflow - Real-time chat with conversation history - Usage analytics dashboard - Observability on Client infrastructure - HIPAA / PHI / SOC 2 compliance controls - Backend-only state management (no localStorage) - Admin console Performance Targets: - Sub-second response for 95% of queries - 99.9% uptime SLA 3-Year TCO Analysis & Savings: Architectural cost controls — including LLM/SLM routing, rate limiting, caching, and multi-vector-DB — keep annual run cost at approximately $170,000/year, while unlocking an estimated $815,000/year in analyst time savings, decision velocity gains, and compliance readiness. Prior to deployment, the client spent over $750,000/year on analyst costs. The 3-year TCO for the GenAI application was $730,000 (build cost + 3-year run cost), compared to $2.25 million for the previous approach — resulting in savings exceeding $1.5 million over the estimated three-year horizon.