Multi-Store Intelligence Layer for Retail Pharmacy Chains
End-to-end architecture for 313 pharmacies — real-time KPI dashboards, demand forecasting, AI chatbot, and cloud cost optimisation across AWS, Azure, and GCP.
313
Pharmacies
7 GB
Per Store MySQL
~2.2 TB
Total Data
266K
Max Daily Txns
54.5M
Max Yearly Records
10 yrs
Historical Depth
📊 Data Volume Profile
Single Store (Average Performer)
Table
Daily Min
Daily Max
Yearly Max
vw_all_sales
282
850
174,030
sale_items
282
850
174,030
sales
132
313
60,649
purchase
—
16
2,029
Current MySQL size: 7 GB · 10 years of history
All 313 Pharmacies (Chain-Wide)
Table
Daily Min
Daily Max
Yearly Max
vw_all_sales
88,266
266,050
54,471,390
sale_items
88,266
266,050
54,471,390
sales
41,316
97,969
18,983,137
purchase
—
5,008
635,077
Total data: ~2.2 TB · Annual growth: ~220 GB/yr
Ingestion Rate
~3 rows/sec
Peak across all stores
ETL Approach
CDC + Batch
Change data capture nightly
Storage Tier
Hot / Warm
2yr hot, 8yr warm/archive
Format at Rest
Parquet
~70% compression vs raw SQL
🏗 System Architecture
Five-layer architecture: Store Edge → Ingestion → Data Lake → Intelligence → Presentation. Designed to be cloud-agnostic at the storage and compute layers.
Layer 1 — Store Edge (313 Stores)
🏪
MySQL / POS
Sales, Inventory, Purchase
→
🔄
CDC Agent
Debezium / custom connector
→
🔒
VPN / TLS
Encrypted tunnel to cloud
↓
Layer 2 — Ingestion & Streaming
📨
Kinesis / Kafka
AWS MSK / Azure Event Hub / GCP Pub/Sub
→
⚡
Lambda / Functions
Validate, deduplicate, enrich
→
🗂
Raw Landing Zone
S3 / ADLS / GCS — Parquet/ORC
↓
Layer 3 — Data Lake & Warehouse
🏔
Bronze Layer
Raw CDC events
→
🪙
Silver Layer
Cleaned, store-partitioned
→
🥇
Gold Layer
KPI aggregates, mart tables
→
🔍
Query Engine
Athena / Synapse / BigQuery
↓
Layer 4 — Intelligence (AI/ML)
📈
Demand Forecast
Prophet / XGBoost per SKU
🚨
Anomaly Detection
Isolation Forest — stock outs
🛒
Basket Analysis
Apriori / FP-Growth rules
🤖
AI Chatbot
LLM + RAG on KPI store
📦
Reorder Engine
Auto PO suggestions
↓
Layer 5 — Presentation
📊
KPI Dashboard
React + Recharts (your current)
📱
Mobile App
Store manager view
💬
Chatbot UI
Web widget + WhatsApp
📧
Alerts
Email / SMS / WhatsApp
📑
Reports
Scheduled PDF exports
⚙️ Data Pipeline Design
🔄 Ingestion Strategy
1
Nightly Batch Pull (Primary) — Cron at 2 AM pulls delta from each store's MySQL using last-modified timestamps. Safest for existing POS systems.
2
CDC via Debezium — For stores with binlog enabled, real-time change capture gives near-realtime dashboard updates (15–30 min lag).
3
Manual Upload Fallback — Stores with connectivity issues upload CSV dumps via secure portal. ETL handles dedup.
🧹 Transformation Rules
1
Deduplication — Hash on (store_id, bill_no, item_id, txn_date) to prevent double-counting during retries.
2
Drug Master Mapping — Normalise SKU names across stores (generic vs branded, spelling variants).
3
Partitioning — Partition Parquet files by (year / month / store_id) for optimal Athena / BigQuery scan costs.
A RAG-powered chatbot that lets pharmacists, store managers, and HQ staff query business data in plain English — via web widget or WhatsApp.
Query Flow
1
User Input — "Show me top 10 slow-moving drugs in store #45 this month" via chat widget or WhatsApp.
2
Intent Classification — LLM classifies: KPI query / forecast query / reorder alert / general drug info.
3
SQL Generation (Text-to-SQL) — GPT-4o / Gemini converts natural language to SQL against your Gold layer schema.
4
Query Execution — Generated SQL runs on Athena / BigQuery against pre-aggregated KPI tables. Results in <1 sec for Gold layer.
5
Response Synthesis — LLM formats result as natural language + optional chart data payload.
6
Delivery — Response sent back via web widget with inline mini-chart, or WhatsApp text summary.
Chatbot Capabilities
📊 Analytics Queries
"What are my top 5 selling brands across all stores today?" · "Which store has the highest average basket size?"
⚠️ Alerts & Anomalies
"Any stores with unusually low sales today?" · "Which drugs are about to go out of stock in Ernakulam stores?"
🔮 Forecasts
"What will Paracetamol demand look like next 30 days?" · "Suggest reorder quantities for store #12."
📋 Reports
"Give me a daily summary for all stores" · "Export this month's purchase report as PDF."
🔧 Tech Stack Options
LLM Core
GPT-4o / Gemini 1.5
Text-to-SQL
Langchain + few-shot
Vector Store
Pinecone / pgvector
Framework
FastAPI backend
WhatsApp
Meta Cloud API
🛡 Safety & Guardrails
✅ Role-based: Store managers see only their store data
✅ SQL injection prevention via parameterised queries
✅ Max row limit: 10K per query to prevent abuse
✅ Query cost cap via Athena workgroup limits
✅ Audit log of all chatbot queries per user
💰 Chatbot Cost/Month
GPT-4o (1K queries/day)
~$90
Gemini 1.5 Flash (alt)
~$12
Vector DB (Pinecone)
~$70
WhatsApp API (500 msg)
~$3
Hosting (FastAPI)
included
💰 Multi-Cloud Cost Estimator
Adjust the sliders to model cost for different scale scenarios. All prices are based on ap-south-1 (AWS Mumbai), Central India (Azure), and asia-south1 (GCP Mumbai) regions.
313
566
500
100
Detailed Breakdown
Component
AWS ($/mo)
Azure ($/mo)
GCP ($/mo)
Notes
TOTAL / MONTH
—
—
—
TOTAL / YEAR
—
—
—
⚠️ Estimation Notes: Costs are approximations based on public pricing (June 2025, South Asia regions). Actual costs vary with reserved instance discounts (up to 60%), committed use discounts, negotiated enterprise agreements, and data egress patterns. AWS and Azure prices based on ap-south-1 and Central India respectively. GCP based on asia-south1. Always run cloud provider calculators for production budgeting.
⚖️ Cloud Provider Comparison
Capability
AWS
Azure
GCP
Data Lake
S3 + Glue + Athena
ADLS Gen2 + Synapse
GCS + Dataflow + BigQuery
Serverless Compute
Lambda + Step Functions
Azure Functions + Logic Apps
Cloud Functions + Workflows
ML Platform
SageMaker
Azure ML
Vertex AI
Streaming
Kinesis / MSK
Event Hubs
Pub/Sub + Dataflow
Managed DB
Aurora Serverless
Azure SQL Serverless
AlloyDB / Cloud SQL
CDN / API GW
CloudFront + API GW
Azure CDN + APIM
Cloud CDN + Apigee
India Presence
Mumbai (ap-south-1)
Central India
Mumbai (asia-south1)
MySQL Migration
DMS (native MySQL)
Database Migration Service
Database Migration Service
AI/LLM Access
Bedrock (Claude, Titan)
Azure OpenAI (GPT-4o)
Vertex AI (Gemini)
Best For This Project
✅ Strong all-round, mature ecosystem
⚠️ Best if already in Microsoft stack
✅ Best BI / BigQuery pricing
🏆 Recommendation
Recommended for Scale
AWS — Primary Choice
Lambda + S3 + Athena + Aurora Serverless. Mumbai region. Best mature ecosystem for this use case. Your existing AWS cost calculator shows $300/mo for prediction layer alone — add ~$200 for ingestion, storage, and dashboard = ~$500/mo total.
✅ Recommended: Lambda architecture (Design A) at your current scale
Best for Analytics
GCP — Analytics-Heavy Builds
BigQuery is significantly cheaper for ad-hoc analytics vs Athena at scale. Vertex AI + Gemini gives native LLM integration. Consider GCP if your primary workload is querying and BI, not heavy ML training.
BigQuery: $5/TB scanned vs Athena $5/TB — similar, but BQ has flat-rate options
If Microsoft-First
Azure — Microsoft Ecosystem
Best if client already uses Microsoft 365, Dynamics, or Power BI. Azure Synapse + Power BI Embedded gives excellent out-of-box dashboards. Higher baseline cost but good enterprise support in India.
⚠️ Typically 15–25% more expensive than AWS/GCP for this workload
🗺 Implementation Roadmap
Phase 1
Foundation
Cloud account + VPC setup
Data migration: 10yr MySQL → S3 Parquet
Bronze/Silver/Gold lake structure
ETL pipeline (nightly batch)
Basic dashboard API
⏱ 6–8 weeks
Phase 2
KPI Layer
KPI aggregation tables
Dashboard (your existing UI)
Multi-store filtering
User auth + RBAC
Alerting (email/SMS)
⏱ 4–6 weeks
Phase 3
Intelligence
Demand forecast (Prophet)
Stockout prediction
Reorder suggestion engine
Basket analysis
Anomaly detection
⏱ 6–8 weeks
Phase 4
AI Chatbot
LLM + Text-to-SQL pipeline
Web widget integration
WhatsApp Business API
Store-level RBAC in bot
Feedback + fine-tuning loop
⏱ 4–6 weeks
💡 Total Timeline: 20–28 weeks (5–7 months) for full platform.
Phase 1+2 alone delivers immediate ROI — stakeholders can see chain-wide KPIs within 10–14 weeks.
Budget Phase 1–2: ~₹15–20 lakhs development + $500/mo cloud. Full platform: ~₹35–50 lakhs + $800–1200/mo cloud.