Case study
Privacy-Safe WhatsApp AI Analyst & Lead Alerting Platform
A WhatsApp-based operational intelligence platform combining scheduled lead monitoring with a conversational AI data analyst. The system transforms CRM and reporting data into authorized alerts, natural-language answers, charts, and file exports while protecting sensitive information at external AI boundaries.
- Project context
- Corporate Project
- Year
- 2025
- Categories
- AI Agent, Data Analytics, Workflow Automation, Backend Engineering
- Source code
- Not publicly available
- Technology stack
- Python, FastAPI, PostgreSQL, SQLAlchemy, Pandas, SQLite, Node.js, Express.js, whatsapp-web.js, Gemini API, OpenRouter, Matplotlib, Google Sheets API
System design
A public-safe view of the components, integrations, and data flow described in this case study.
Scroll horizontally to inspect the full diagram, or open it full size.
Problem
A lead-driven service operation relied on CRM and reporting databases for time-sensitive decisions, but stakeholders needed a faster way to identify unhandled leads, unusual lead volume, pipeline issues, and performance changes. Ad-hoc questions also required analysts to manually write SQL, prepare reports, and distribute results to different operational teams. The solution needed to deliver insights through WhatsApp, respect role-based access, support both scheduled monitoring and conversational analysis, and prevent customer identifiers from being exposed to external AI providers.
Role
Abdul led the architecture and implementation of the analytics automation and conversational AI capabilities. His responsibilities included: - Translating operational requirements into monitoring and reporting workflows. - Developing CRM queries, transformation rules, anomaly detection, funnel analysis, and team-specific report distribution. - Designing the Python and Node.js integration used to receive and deliver WhatsApp messages. - Building the multi-turn AI analyst, structured action contract, provider routing, persistent conversation memory, and natural-language export flow. - Implementing authorization, sensitive-data redaction, read-only query controls, and safe failure handling. - Creating regression tests and operational documentation for maintenance and handover.
Architecture
The platform uses a hybrid Python and Node.js architecture: 1. A Node.js bridge maintains the WhatsApp Web session, classifies commands, mentions, and replies, and handles outbound text, images, and documents. 2. FastAPI receives inbound events and delegates processing to background tasks. 3. A Python command layer performs authorization and routes requests to either deterministic operational workflows or the conversational AI analyst. 4. PostgreSQL provides separate CRM and reporting data sources. 5. The AI layer translates natural-language requests into structured actions: query data, request clarification, answer from existing context, or return a safe error. 6. Gemini and OpenRouter are routed as primary and secondary providers with retry and failover behavior. 7. SQLite provides durable, chat-scoped conversation history while only a bounded recent window is sent to AI providers. 8. Charts, CSV files, and Excel workbooks are generated locally before delivery. 9. Google Sheets integration provides best-effort usage logging and selected reporting uploads. Sensitive phone numbers, email addresses, customer identifiers, and customer names are replaced with stable placeholders before provider calls. Raw exports remain restricted to explicitly authorized chats.
Implementation
The deterministic monitoring layer implements nine operational workflow families, including lead-volume anomaly monitoring, untouched lead detection, potential lead identification, eligibility and contact analysis, missing action-result reporting, unfinished-ticket reporting, and lead-funnel assessment. The conversational analyst supports: - Multi-turn questions initiated through commands, mentions, or replies. - Shared context within authorized WhatsApp groups. - Schema-grounded PostgreSQL generation using documented query examples. - Text, chart, CSV, and Excel delivery. - Context-aware clarification and escalation to a human analyst. - Jakarta timezone context for relative-date questions. - Persistent conversations that can resume after process restarts. - Configurable multi-group identity and language context. AI output is treated as untrusted. Actions and response formats are normalized in application code, export permissions are checked before file delivery, and generated database statements are rejected unless they begin with a read-only `SELECT` or `WITH` operation.
Results
The project consolidated scheduled operational monitoring and ad-hoc analytics into a single WhatsApp interface. Authorized stakeholders can receive targeted alerts, ask follow-up questions, request visualizations, and export query results without switching to a separate analytics application. The implementation provides durable conversation memory, two-provider AI failover, four delivery formats, privacy filtering at AI boundaries, and audience-specific authorization. Its critical conversation, privacy, routing, persistence, export, and WhatsApp identifier behavior is covered by 101 passing automated tests across Python and Node.js.