Production-Ready Multi-Tenant SaaS for Intelligent Data Analysis
Enterprise-grade SaaS platform that transforms natural language into intelligent SQL queries with advanced security, monitoring, and multi-tenant architecture.
- Multi-tenant SaaS with database-per-tenant isolation
- FastAPI backend with async PostgreSQL and Redis
- JWT authentication with RSA key rotation and service mesh
- Role-based access control (RBAC) with fine-grained permissions
- Multi-factor authentication (TOTP, SMS, email, backup codes)
- Zero-trust architecture with service mesh authentication
- Client-side encryption with column-level access control
- Data masking and automated compliance reporting
- Security scanning integrated in CI/CD pipeline
- Vulnerability management with automated patching
- Prometheus metrics with custom business KPIs
- Grafana dashboards for system and application monitoring
- ELK stack for centralized logging and analysis
- Jaeger tracing for distributed request tracking
- SLI/SLO monitoring with automated alerting
- AI API cost tracking with per-token pricing models
- Infrastructure cost monitoring via AWS Cost Explorer
- Cost anomaly detection with statistical analysis
- Budget management with configurable alerts
- Automated optimization recommendations
- Automated backups (full, incremental, transaction log)
- Cross-region replication with S3 storage
- Point-in-time recovery with RTO/RPO targets
- Backup scheduling and retention policies
- Recovery plan generation and execution
API Framework: FastAPI with async/await
Database: PostgreSQL 15 with SQLAlchemy ORM
Cache: Redis 7 with async support
Task Queue: Celery with Redis broker
Authentication: JWT with RSA256 signingContainerization: Docker with multi-stage builds
Orchestration: Kubernetes with Helm charts
Monitoring: Prometheus + Grafana + ELK
Service Mesh: Istio with mTLS
Storage: AWS S3 with encryptionSQL Generation: Claude 3.5 Sonnet
Query Optimization: Custom SQL parser
Semantic Caching: Vector similarity matching
Business Intelligence: Automated insights
Anomaly Detection: Statistical analysisCI/CD: GitHub Actions with security scanning
Security: TruffleHog, Snyk, Bandit, Semgrep
Container Security: Trivy, Dockle
Performance: Load testing with Locust
License Compliance: Automated validation- Live Demo: https://sql-genius.vercel.app
- Metrics Dashboard: https://sql-genius.vercel.app/metrics
- API Backend: https://sql-genius-api.onrender.com
- API Documentation: https://sql-genius-api.onrender.com/docs
- Fork this repository
- Connect to Render
- Create new Blueprint from
render.yaml - Set environment variables:
ANTHROPIC_API_KEY: Your Claude API keyJWT_SECRET_KEY: Auto-generatedDATABASE_URL: Auto-configured
cd sql-genius-frontend
npx vercel --prod- SQL Generation: Natural language to SQL with AI
- Live Playground: Interactive query builder
- Real-time Metrics: Performance monitoring dashboard
- Schema Templates: E-commerce, SaaS, Healthcare, Financial
- Sandboxed Execution: Safe query testing environment
# Check API health
curl -sSf https://sql-genius-api.onrender.com/health
# View API documentation
curl -sSf https://sql-genius-api.onrender.com/docsFor detailed deployment instructions, see the render.yaml Blueprint configuration.
- JWT with RSA rotation - Automatic key rotation every 24 hours
- Multi-factor authentication - TOTP, SMS, email, backup codes
- Single Sign-On (SSO) - SAML 2.0 and OpenID Connect
- Service mesh auth - mTLS between microservices
- Session management - Redis-based with automatic cleanup
- Multi-step reasoning - Complex query decomposition
- Validation pipeline - Syntax and semantic checking
- Security scanning - SQL injection prevention
- Query sandboxing - Isolated execution environment
- Performance optimization - Automatic query tuning
- Database isolation - Database-per-tenant pattern
- Dynamic routing - Tenant-aware request routing
- Resource quotas - Per-tenant limits and throttling
- Usage metering - Detailed consumption tracking
- Billing integration - Stripe subscription management
- Automated insights - AI-powered data analysis
- Anomaly detection - Statistical outlier identification
- Natural language reporting - Executive summaries
- Predictive analytics - Trend forecasting
- Custom dashboards - Real-time visualizations
- Semantic similarity - Vector-based query matching
- TTL optimization - Dynamic cache expiration
- Distributed cache - Redis cluster support
- Cache invalidation - Smart dependency tracking
- Performance metrics - Cache hit/miss analytics
- Python 3.11+
- Docker & Docker Compose
- PostgreSQL 15
- Redis 7
- Node.js 18+ (for frontend)# Clone repository
git clone https://github.com/cbratkovics/sql-genius-ai.git
cd sql-genius-ai
# Start infrastructure services
docker-compose -f infrastructure/docker/docker-compose.yml up -d
# Install Python dependencies
cd backend
pip install -r requirements.txt
# Run database migrations
alembic upgrade head
# Start the API server
uvicorn main:app --reload --host 0.0.0.0 --port 8000
# Start monitoring stack
docker-compose -f infrastructure/monitoring/docker-compose.monitoring.yml up -d# .env file
DATABASE_URL=postgresql://user:pass@localhost:5432/sqlgenius
REDIS_URL=redis://localhost:6379
CLAUDE_API_KEY=your_claude_api_key
JWT_SECRET_KEY=your_jwt_secret
STRIPE_SECRET_KEY=your_stripe_key
AWS_ACCESS_KEY_ID=your_aws_key
AWS_SECRET_ACCESS_KEY=your_aws_secret- Secret detection - TruffleHog for exposed secrets
- Dependency scanning - Snyk for vulnerabilities
- SAST analysis - Bandit and Semgrep
- Container security - Trivy and Dockle
- License compliance - Automated validation
- Code coverage - 90% minimum threshold
- Type checking - MyPy static analysis
- Code formatting - Black and isort
- Performance testing - Regression detection
- Security benchmarks - OWASP compliance
- GitOps workflow - ArgoCD for deployments
- Blue-green deployment - Zero-downtime releases
- Automated rollback - Health check failures
- Progressive delivery - Feature flags and canary
- Post-deployment testing - Smoke and integration tests
Application Metrics:
- Request latency and throughput
- Error rates and success rates
- SQL query performance
- Cache hit/miss ratios
- Business KPIs and conversion
Infrastructure Metrics:
- CPU, memory, disk usage
- Database connection pools
- Queue depth and processing
- Network I/O and latency
- Container resource usageCritical Alerts:
- API error rate > 1%
- Response time > 2s
- Database connections > 80%
- Disk usage > 85%
- Memory usage > 90%
Warning Alerts:
- Cache hit rate < 80%
- Queue processing delay > 30s
- Cost anomaly detected
- Backup failure
- SSL certificate expiry < 30 days- Real-time cost tracking - Per-tenant consumption
- Budget alerts - Configurable thresholds
- Cost allocation - Department and project tags
- Optimization recommendations - AI-powered suggestions
- Anomaly detection - Statistical analysis of spending
AI API Costs:
- Token usage optimization
- Model selection automation
- Batch processing
- Caching strategies
- Rate limiting
Infrastructure Costs:
- Auto-scaling policies
- Reserved instance planning
- Storage lifecycle management
- Spot instance utilization
- Resource right-sizingPOST /auth/login # User authentication
POST /auth/register # User registration
POST /auth/mfa/setup # MFA configuration
POST /auth/refresh # Token refresh
DELETE /auth/logout # Session terminationPOST /sql/generate # Generate SQL from natural language
POST /sql/execute # Execute SQL query
GET /sql/history # Query history
POST /sql/validate # Validate SQL syntax
GET /sql/schema # Database schemaGET /tenants # Tenant management
POST /tenants/{id}/backup # Create backup
GET /metrics # Prometheus metrics
GET /health # Health check
GET /docs # OpenAPI documentationgraph TB
LB[Load Balancer] --> API[FastAPI Services]
API --> DB[(PostgreSQL)]
API --> CACHE[(Redis)]
API --> QUEUE[Celery Workers]
API --> AUTH[Auth Service]
API --> SQL[SQL Engine]
API --> BI[BI Service]
MONITOR[Prometheus] --> GRAFANA[Grafana]
LOGS[ELK Stack] --> KIBANA[Kibana]
BACKUP[Backup Service] --> S3[(AWS S3)]
COST[Cost Service] --> AWS[AWS APIs]
sequenceDiagram
participant C as Client
participant LB as Load Balancer
participant API as API Gateway
participant AUTH as Auth Service
participant SQL as SQL Engine
participant DB as Tenant DB
C->>LB: Request with JWT
LB->>API: Route to service
API->>AUTH: Validate token
AUTH->>API: Tenant context
API->>SQL: Process query
SQL->>DB: Execute on tenant DB
DB->>SQL: Results
SQL->>API: Formatted response
API->>C: Business insights
Response Times:
- SQL Generation: <500ms (p95)
- Query Execution: <2s (p95)
- Authentication: <100ms (p95)
- Dashboard Load: <1s (p95)
Throughput:
- Concurrent Users: 10,000+
- Requests/Second: 5,000+
- SQL Queries/Hour: 1,000,000+
- Cache Hit Rate: >90%Horizontal Scaling:
- API Services: Auto-scaling 2-50 instances
- Database: Read replicas + sharding
- Cache: Redis cluster (6 nodes)
- Queue: Celery workers (10-100 instances)
Resource Efficiency:
- CPU Usage: <70% average
- Memory Usage: <80% average
- Storage Growth: <10GB/month/tenant
- Network I/O: <1Gbps sustained- Authentication: JWT with RSA256, MFA required
- Authorization: Fine-grained RBAC permissions
- Encryption: TLS 1.3 in transit, AES-256 at rest
- Network: VPC isolation, security groups
- Monitoring: Real-time threat detection
- SOC 2 Type II - Security and availability
- GDPR - Data privacy and protection
- HIPAA - Healthcare data security
- PCI DSS - Payment card industry
- ISO 27001 - Information security management
# Build and push images
docker build -t sqlgenius/api:latest -f infrastructure/docker/Dockerfile.backend .
docker push sqlgenius/api:latest
# Deploy with Helm
helm upgrade --install sqlgenius ./charts/sqlgenius \
--namespace production \
--values values.production.yaml
# Verify deployment
kubectl get pods -n production
kubectl logs -f deployment/sqlgenius-api -n production# Kubernetes manifests
- Deployments and Services
- ConfigMaps and Secrets
- Ingress and NetworkPolicies
- HorizontalPodAutoscaler
- PersistentVolumeClaims
# Helm Charts
- Application deployment
- Configuration management
- Environment-specific values
- Dependency management
- Upgrade strategies- Code Style: Black formatting, PEP 8 compliance
- Testing: 90%+ coverage, unit and integration tests
- Documentation: Docstrings, API documentation
- Security: Security review for all changes
- Performance: Benchmark critical paths
- Fork the repository
- Create feature branch
- Implement changes with tests
- Run security and quality checks
- Submit pull request
- Code review and approval
- Automated deployment
This project is licensed under the MIT License - see the LICENSE file for details.
Multi-tenant • Secure • Scalable • Observable • Cost-optimized
Built by Christopher Bratkovics
Star this repo to support enterprise-grade open source!