Active chapterCase study
Case study / 01
A production-ready, privacy-focused web analytics platform with an integrated AI 'Data Analyst' that uses Text-to-SQL to answer natural language questions about your data. Built with TypeScript, Express.js, React, and PostgreSQL.
01 / System behavior
A project-specific technical mechanism based on the recorded architecture and implementation context.
Built as a production-ready alternative to Google Analytics, PingLens demonstrates that privacy and powerful analytics aren't mutually exclusive. The platform deliberately chose complexity over simplicity - not just pageview counting, but a full AI-powered analyst that understands natural language, generates safe SQL, and streams conversational responses.
The AI Text-to-SQL pipeline is the heart of the system: users ask questions like 'What were my top pages yesterday?' and the LLM generates PostgreSQL queries with full schema context. Every query passes through five security layers before execution. The dangerous keywords blocklist rejects INSERT/DROP/DELETE operations. Pattern matching catches SQL injection attempts (OR 1=1, UNION SELECT). Table validation ensures only 'events' and 'websites' are queried. The query executes via a read-only database role with SELECT-only grants. If the first attempt fails, the system retries once with error context - teaching the LLM from its mistakes.
03 / Architecture record
A structured reading of the architecture recorded with this project.
Three applications working together:
• Customer Dashboard (dashboard/) - React + Vite + TailwindCSS for website selection, real-time metrics visualization with Recharts, and AI chat interface with streaming responses • Tracker Script (tracker/) - Vanilla TypeScript (<5KB) with intelligent event batching, auto-flush timers, and sendBeacon fallback for reliable data collection • Backend API (api/) - Express.js with TypeScript handling event ingestion, analytics queries, AI Text-to-SQL pipeline, and multi-provider LLM abstraction
The API connects to PostgreSQL via node-postgres with two connection pools: a main pool for read/write operations and a read-only pool (ai_agent_reader role) for AI-generated queries. The AI pipeline validates SQL through regex blocklists, injection pattern detection, and table allowlists before execution.
LLM providers (OpenAI, Anthropic, Gemini) are abstracted via ILLMService interface with discriminated union ProviderConfig. The self-correction mechanism retries failed queries once with error context.
Docker Compose orchestrates the entire stack for local development. The production build uses multi-stage Dockerfiles with non-root users and health checks.
The tracker is engineered for performance and reliability: events queue in memory (max 50), auto-flush every 5 seconds, batch send with keepalive fetch, and sendBeacon fallback on page unload. Zero external dependencies. The entire bundle is <5KB minified.
Security was a first-class concern from day one: Helmet.js configures security headers, custom error classes sanitize messages in production, structured JSON logging includes request ID tracing for debugging, Zod schemas validate every API input, and zero 'any' types exist in production code. The SQL parser uses stateless regex (no /g flag) to avoid the lastIndex bug that causes alternating true/false results.
The LLM abstraction uses a discriminated union (ProviderConfig) with 'vercel' and 'gemini' kinds, allowing type-safe provider switching. OpenAI and Anthropic use the Vercel AI SDK's unified interface. Google Gemini requires custom streaming logic but presents the same ILLMService interface to consumers.
The test suite validates critical paths: SQL parser tests verify the stateful regex fix, injection pattern blocking, and legitimate CAST/DATE function allowance. Validation tests cover Zod schemas and IP/user-agent sanitization. Service tests mock LLM responses and verify retry logic. Tracker tests confirm queue batching and network fallback behavior.
05 / Selected highlights
The decisions and workflows that carry the most explanatory weight.
06 / Supported metrics
TypeScript, Node.js, Express.js, React, Vite, PostgreSQL, OpenAI, Anthropic, Google Gemini, TailwindCSS, Recharts, Zod, Helmet.js, Docker, Jest, Vercel AI SDK
View source repository