Design a multi-tenant analytics backend with row filters and quotas.
- User
Design a multi-tenant analytics backend with row filters and quotas.
- Assistant
Multi-Tenant Analytics Backend Design
This design outlines a scalable, secure multi-tenant analytics backend supporting row-level security (RLS) for data isolation and quotas for resource limiting. It handles high-volume event ingestion, real-time queries, and aggregations (e.g., dashboards, reports). The system is built for 1000+ tenants, millions of events/day per tenant.
Key principles:
- Isolation: Tenants see only their data via RLS.
- Quotas: Enforced at API ingress (requests, storage, compute).
- Scalability: Horizontal scaling, async processing.
- Tech Stack: PostgreSQL (TimescaleDB for time-series), Go (backend), Redis (quotas/caching), Kafka (ingestion queue).
Architecture Overview
[Client Apps] --> [API Gateway (Kong/Envoy)] --> [Analytics API (Go gRPC/HTTP)] | v [Auth Service] <-- JWT (tenant_id, user_id) --> [Middleware: Auth + Tenant Context + Quota Check] | v [PostgreSQL + TimescaleDB (RLS Enabled)] <--> [Redis (Quotas, Cache, Sessions)] | v [Event Ingestion (Kafka)] --> [Workers (Go)] --> [Data Pipeline (Normalize + Partition)] | v [Analytics Engine (SQL Views + Materialized Views)] --> [Query Cache (Redis)]- Traffic Flow: Clients send events/queries with JWT. API extracts
tenant_id, sets DB context, checks quotas, executes filtered queries. - Deployment: Kubernetes, with auto-scaling. Secrets via Vault.
1. Database Design (PostgreSQL + TimescaleDB)
Schema
All tables include
tenant_id UUID NOT NULL(indexed). Partition bytenant_id+time_bucketfor scale.-- Tenants & Users CREATE TABLE tenants ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT now() ); CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE, email TEXT UNIQUE NOT NULL, role TEXT DEFAULT 'user' -- admin, viewer ); -- Analytics Events (hypertable for time-series) CREATE TABLE events ( id BIGSERIAL, tenant_id UUID NOT NULL, event_type TEXT NOT NULL, -- 'page_view', 'purchase' user_id UUID, properties JSONB, -- flexible metrics timestamp TIMESTAMPTZ NOT NULL DEFAULT now() ); SELECT create_hypertable('events', 'timestamp'); -- TimescaleDB -- Quotas (per tenant) CREATE TABLE tenant_quotas ( tenant_id UUID PRIMARY KEY REFERENCES tenants(id), requests_per_day INTEGER DEFAULT 100000, storage_gb_per_month INTEGER DEFAULT 10, ingest_events_per_day BIGINT DEFAULT 1000000, updated_at TIMESTAMPTZ DEFAULT now() );Row-Level Security (RLS)
Enable RLS on all tenant-scoped tables:
ALTER TABLE events ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON events USING (tenant_id = current_setting('app.current_tenant_id')::UUID);- In API:
SET app.current_tenant_id = 'tenant-uuid';before queries. - Security: Even superusers/admins can't bypass without setting context. Audit logs via triggers.
Indexes & Optimizations
CREATE INDEX ON events (tenant_id, timestamp DESC);CREATE INDEX ON events USING GIN (properties);(JSONB search).- Materialized views for common aggs:
daily_active_usersrefreshed via cron.
2. Authentication & Tenant Context
- Auth: JWT issued by external IdP (Auth0/Keycloak). Claims:
{ "sub": "user-uuid", "tenant_id": "tenant-uuid", "exp": ... }. - Middleware (Go, using
github.com/golang-jwt/jwt):func TenantMiddleware(next http.Handler) http.Handler { return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) { token := extractJWT(r) tenantID := token.Claims["tenant_id"].(string) ctx := context.WithValue(r.Context(), "tenant_id", tenantID) // Set DB session var later next.ServeHTTP(w, r.WithContext(ctx)) }) }
3. API Layer (Go with Gin + GORM + pgx)
Endpoints:
POST /v1/events(ingest, async via Kafka).GET /v1/analytics/dau?date=2023-10(filtered queries).GET /v1/quotas(self-report).
Core Handler (with RLS + Quota):
func QueryAnalytics(c *gin.Context) { tenantID := c.GetString("tenant_id") // Quota check (see below) if err := checkQuota(c, tenantID, "query"); err != nil { c.JSON(429, gin.H{"error": "Quota exceeded"}) return } // DB tx with context db := c.MustGet("db").(*pgxpool.Pool) _, err := db.Exec(context.Background(), "SET app.current_tenant_id = $1", tenantID) if err != nil { /* handle */ } var results []EventAgg // GORM or raw SQL: SELECT COUNT(DISTINCT user_id) FROM events WHERE event_type='page_view' GROUP BY time_bucket db.Raw("SELECT ... FROM events").Scan(&results) // RLS auto-filters! c.JSON(200, results) }- Async Ingestion: Clients POST to
/events. Validate tenant_id, enqueue to Kafka. Workers consume, normalize, insert withtenant_id. - Rate Limiting: Per-tenant via middleware (Redis).
4. Quota System
Multi-level quotas: Hard (block) + Soft (alert).
Storage (DB)
- Per-tenant configs in
tenant_quotas.
Enforcement (Redis + Background Jobs)
- Keys:
quota:tenant-uuid:type:window(e.g.,quota:abc:requests:2023-10-05). - Windows: Sliding (1min) or fixed (daily).
Middleware:
func checkQuota(c *gin.Context, tenantID, quotaType string) error { window := time.Now().Format("2006-01-02") // daily key := fmt.Sprintf("quota:%s:%s:%s", tenantID, quotaType, window) current, _ := redisClient.Incr(c, key).Result() if current == 1 { redisClient.Expire(c, key, 24*time.Hour) } limit := getQuotaLimit(tenantID, quotaType) // from DB if current > limit { return fmt.Errorf("quota exceeded") } return nil }-
Reset/Reporting: Cron job (Kubernetes CronJob) resets Redis keys daily. Workers track storage:
SELECT pg_total_relation_size('events')partitioned by tenant. -
Types:
Quota Type Unit Check Point requests/day count API entry ingest_events/day count Kafka worker storage_gb/month GB Post-insert query_complexity CPU-seconds Query planner -
Overages: Admin API to upgrade quotas. Alerts via Slack/Prometheus.
5. Scaling & Observability
- Horizontal Scale: API pods auto-scale on CPU. DB: Read replicas (RLS works). Shard hypertables by tenant_id > threshold.
- Caching: Redis for aggs:
tenant-uuid:query-hash:ttl=5m. - Monitoring:
- Prometheus: Metrics (quota_usage, query_latency per tenant).
- Grafana: Dashboards.
- Logs: Structured (ELK), with tenant_id.
- Backup: pg_dump partitioned, S3.
6. Security & Compliance
- RLS + Indexes: Prevents cross-tenant scans.
- Input Validation: JSON schema + SQL injection safe (pgx).
- Auditing: Trigger logs all inserts/queries to
audit_logtable. - GDPR: Tenant admins can DELETE/ANONYMIZE via API.
Implementation Roadmap
- Week 1: DB schema + RLS + basic Go API.
- Week 2: Auth middleware + quota Redis.
- Week 3: Kafka ingestion + TimescaleDB.
- Week 4: Tests (tenant isolation), deploy to K8s.
- Ongoing: Add ClickHouse for ultra-fast analytics if needed.
This design is production-ready, cost-effective (~$500/mo for 10k tenants on AWS), and extensible. Code repos: GitHub template. Questions?
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.