Chapter 9 — Project P5 — Scalable SaaS Backend

Why this project

Building a scalable SaaS backend is the capstone of backend engineering. Unlike consumer apps where users are anonymous and free, a SaaS platform has tenants who pay real money, expect their data to be isolated, and will leave if your platform is slow or unreliable. This project forces you to make every tradeoff we've discussed in L5 — tenant isolation strategy, database scaling, caching layers, queue resilience, and observability — in the context of a real multi-tenant system that needs to onboard 10,000 businesses and serve 1 million end-users.

By the end of this design, you'll have an architecture document you can use as a reference for any SaaS product you build in your career.

Spec — what we're designing

Product: "DashCore" — A SaaS analytics dashboard platform where businesses connect their data sources (Stripe, Google Analytics, database) and get unified dashboards.

Scale targets:

• 10,000 tenants (businesses) within 24 months
• 1,000,000 end-users (employees of tenant businesses)
• 50,000 dashboard queries per minute at peak
• 5,000 data source syncs per minute
• 99.9% availability SLA for dashboard viewing
• 99.95% durability for synced data

Key features:

• Tenant onboarding (signup, workspace creation, team invites)
• Data source connections (API integrations with Stripe, GA, PostgreSQL)
• Dashboard builder (drag-and-drop widgets, custom charts)
• Real-time data sync (pull from sources every 5-60 minutes)
• Team collaboration (roles: owner, admin, viewer)
• Billing (per-seat pricing, usage-based add-ons)
• White-labeling (custom domain, custom branding per tenant)

Architecture diagram

┌──────────────────────────────────────────────────────────────────┐
│                         CDN (CloudFront)                          │
│               Static assets, cached dashboard data                │
└─────────────────────────────┬────────────────────────────────────┘
                              │
                              ▼
┌──────────────────────────────────────────────────────────────────┐
│                    API Gateway (Kong / AWS API Gateway)           │
│  - Tenant identification (JWT with tenant_id claim)               │
│  - Rate limiting per tenant (tiered: 100/1000/10000 req/min)     │
│  - Request validation, TLS termination                            │
└─────────────────────────────┬────────────────────────────────────┘
                              │
          ┌───────────────────┼───────────────────┐
          ▼                   ▼                   ▼
┌──────────────────┐ ┌──────────────────┐ ┌──────────────────┐
│  Auth Service    │ │ Dashboard Svc    │ │ Data Source Svc  │
│  - Signup/login  │ │ - Widget CRUD    │ │ - OAuth flows    │
│  - JWT issue     │ │ - Query engine   │ │ - Sync scheduler │
│  - Team invites  │ │ - Caching layer  │ │ - Data transform │
│  - RBAC          │ │                  │ │                  │
└────────┬─────────┘ └────────┬─────────┘ └────────┬─────────┘
         │                    │                    │
         ▼                    ▼                    ▼
┌──────────────────────────────────────────────────────────────────┐
│                     Message Queue (RabbitMQ)                      │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────┐  │
│  │ sync.jobs    │  │ notifications│  │ analytics.events     │  │
│  │ (data source │  │ (email,      │  │ (usage tracking,     │  │

Design decisions (ADR-style)

ADR-001: Tenant isolation — Row-Level Security (RLS) over schema-per-tenant

Decision: Use PostgreSQL Row-Level Security (RLS) with a tenant_id column on every table, rather than creating a separate schema or database per tenant.

Rationale:

• At 10,000 tenants, schema-per-tenant means 10,000 schemas — connection pooling becomes a nightmare (you can't share connections across schemas without SET search_path on every query)
• Database-per-tenant means 10,000 databases — backups, migrations, and monitoring become operations at impossible scale
• RLS with tenant_id on every table gives us: one connection pool, one set of migrations, one backup, and tenant isolation enforced at the database level

Implementation:

-- Enable RLS on every table
ALTER TABLE dashboards ENABLE ROW LEVEL SECURITY;
ALTER TABLE data_sources ENABLE ROW LEVEL SECURITY;
ALTER TABLE widgets ENABLE ROW LEVEL SECURITY;
 
-- Create policy: users can only see their tenant's data
CREATE POLICY tenant_isolation ON dashboards
  FOR ALL
  TO authenticated_user

Tradeoffs:

• (+) Simple operations: one database to back up, one migration to run, one connection pool
• (+) Cost-effective: 10,000 tenants share database resources efficiently
• (+) Scalable: vertically scale the database, add read replicas, eventually consider sharding by tenant_id
• (-) Noisy neighbor risk: one tenant's heavy query can impact others. Mitigation: statement_timeout per role, connection pooling limits per tenant
• (-) Compliance complexity: data of all tenants is in one database. GDPR deletion requires row-level deletion, not database drop

Alternatives considered:

• Schema-per-tenant: Better isolation but connection pool management at 10k schemas is infeasible
• Database-per-tenant (e.g., Citus): Excellent isolation, expensive at scale, complex operations
• Hybrid (RLS + sharding): For >50k tenants, shard by tenant_id range with RLS within each shard

ADR-002: Read replicas for dashboard queries, primary for writes

Decision: Route all dashboard reads (widget data, dashboard metadata) to read replicas. Route all writes (dashboard creation, settings updates, data sync writes) to the primary.

Rationale:

• Dashboard reads are 95% of database traffic — users view dashboards far more than they edit them
• Read replicas can be scaled independently (add more replicas as read traffic grows)
• Write throughput for a SaaS dashboard platform is modest (dashboard edits, data syncs) — a single primary handles it until ~50k writes/sec

Tradeoffs:

• (+) Read scalability: add replicas without affecting write performance
• (+) Reporting queries: expensive aggregate queries (monthly reports) run on replicas without impacting users
• (-) Replication lag: new data from syncs may take 50-200ms to appear on replicas. Mitigation: show a "data refreshing" indicator
• (-) Complexity: application must route writes and reads to different database connections

ADR-003: Queue-based data sync with idempotent workers

Decision: All data source syncs go through a message queue (sync.jobs). Workers pull sync jobs, connect to external APIs, transform data, and write to TimescaleDB.

Rationale:

• External APIs fail (rate limits, authentication expiry, network issues). Queue retries with exponential backoff handle this gracefully
• Sync volume varies (Stripe syncs every 5 min, GA every 60 min). Queue absorbs the variance — workers scale independently
• Idempotent workers prevent duplicate data: each sync job has a sync_id (timestamp + tenant + source). Workers check "has this sync already been written?" before inserting

Queue topology:

sync.jobs (main queue)
  ├── Stripe syncs (high priority, frequent)
  ├── GA syncs (medium priority, hourly)
  └── Custom DB syncs (low priority, daily)
 
sync.jobs.dlq (dead letter queue)
  └── Jobs that failed after 5 retries
      → Alert on-call → Inspect → Replay or discard

ADR-004: Tiered rate limiting at API gateway

Decision: Implement rate limiting per tenant at three levels: API Gateway (per-tenant limit), application-level (per-endpoint cost), and database-level (connection limits).

Tier structure:

Implementation:

# Kong rate limiting plugin configuration
plugins:
  - name: rate-limiting
    config:
      minute: 1000  # default for Pro tier
      policy: redis  # shared counter across API gateway instances
      redis_host: redis-cluster.internal
      fault_tolerant: true  # allow requests if Redis is down
      hide_client_headers: false
 

Database design

Schema (simplified core tables)

-- Every table includes tenant_id for RLS
-- Every table includes created_at/updated_at for auditing
 
CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  plan TEXT NOT NULL DEFAULT 'free',  -- free, pro, enterprise
  custom_domain TEXT

Sharding strategy

Phase 1 (0-10k tenants): Single PostgreSQL instance with RLS. Vertical scaling as needed. Read replicas for dashboard queries.

Phase 2 (10k-50k tenants): Shard by tenant_id range using Citus or manual hash sharding. Shard 1: tenants A-M, Shard 2: tenants N-Z. Each shard is a complete PostgreSQL instance with its own RLS policies.

Phase 3 (50k+ tenants): Consistent hashing by tenant_id with dynamic resharding. Each shard handles ~5k tenants. A shard router in the application layer maps tenant_id → shard connection pool.

Read replicas

• 2 read replicas for the primary
• Dashboard reads → replicas (round-robin)
• Dashboard writes, sync writes → primary
• Reporting/analytics queries → dedicated replica (separate from user-facing replicas)
• Replication lag monitoring: alert if lag > 5 seconds

Scaling strategy (how it handles 1M users)

Traffic distribution

At 1M end-users across 10k tenants:

• Average users per tenant: 100
• Peak concurrent users: ~50,000 (5% of total)
• Dashboard queries: 50,000/min → ~833 QPS
• Data source syncs: 5,000/min → ~83 sync jobs/sec

Scaling each layer

Layer           | 1k tenants | 10k tenants | Scaling mechanism
----------------|------------|-------------|------------------
API Gateway     | 2 instances| 5 instances | Auto-scale on request count
Auth Service    | 2 instances| 4 instances | Auto-scale on CPU
Dashboard Svc   | 3 instances| 8 instances | Auto-scale on request latency
Sync Workers    | 5 workers  | 30 workers  | Scale on queue depth
PostgreSQL      | db.r6g.xlarge| db.r6g.4xlarge| Vertical + read replicas
Redis Cluster   | 3 nodes   | 6 nodes     | Consistent hashing
RabbitMQ        | 1 node    | 3-node cluster| Clustering + quorum queues
CDN             | CloudFront| CloudFront  | Already scales infinitely

Hot tenant mitigation

Enterprise tenants with 5,000+ users get:

• Dedicated read replica (their dashboard queries don't impact other tenants)
• Higher rate limits (10k req/min)
• Higher sync frequency (5 min vs 15 min)
• Optional: dedicated shard for their data (cost covered by enterprise pricing)

Resilience strategy

Circuit breakers

// Circuit breakers on external API calls (sync workers)
const stripeCircuitBreaker = new CircuitBreaker({
  failureThreshold: 5,
  timeout: 60000,        // 60s open
  halfOpenMaxRequests: 3,
});
 
const gaCircuitBreaker = new CircuitBreaker({
  failureThreshold

Retry strategies

Graceful degradation

Chaos engineering

• Monthly game day: Simulate 1 read replica failure during business hours
• Quarterly: Simulate primary database failover (promote replica) in staging
• Bi-annual: Simulate full region failure, failover to warm standby in secondary region

Implementation checklist

Phase 1 — Foundation (Weeks 1-4)

• PostgreSQL schema with RLS on all tables
• Auth service: signup, login, JWT with tenant_id claim
• Tenant middleware: extract tenant from JWT, set app.current_tenant_id
• Dashboard CRUD API (scoped to tenant via RLS)
• Basic rate limiting at API gateway (global limits, not per-tenant yet)

Phase 2 — Core Features (Weeks 5-8)

• Data source connections: OAuth flow for Stripe, Google Analytics
• Credential encryption at rest (AES-256-GCM, key in KMS)
• Sync job queue: RabbitMQ setup, sync worker, idempotency
• Time-series storage: TimescaleDB hypertable for synced metrics
• Dashboard query engine: fetch synced metrics, compute aggregations
• Redis caching for dashboard query results (TTL: sync interval)
• Webhook receiver for real-time data sources

Phase 3 — Scale & Resilience (Weeks 9-12)

• Read replicas: route dashboard reads to replicas
• Per-tenant rate limiting (tiered: free/pro/enterprise)
• Circuit breakers on all external API calls
• Dead letter queue with monitoring and replay UI
• Graceful degradation: fallback components for each service
• Horizontal auto-scaling for API and worker services

Phase 4 — Production Readiness (Weeks 13-16)

• Observability: OpenTelemetry traces, Prometheus metrics, structured logging
• SLOs defined: 99.9% dashboard view availability, 99.95% sync durability
• Alerting: error budget burn rate, sync backlog, replication lag
• Dashboard as code: Grafana dashboards in version control
• Runbook: documented procedures for common incidents
• Chaos engineering: game day schedule and automation
• Billing integration: per-seat pricing, usage-based metering
• White-labeling: custom domain support, branding per tenant

Interview questions

1. Q: Why did you choose Row-Level Security (RLS) over schema-per-tenant for tenant isolation? A: At 10,000 tenants, schema-per-tenant creates an operational nightmare. Each schema requires its own connection pool configuration — you can't share connections across schemas without SET search_path on every query. Database-per-tenant means 10,000 databases to back up, migrate, and monitor. RLS with a tenant_id column gives us: one connection pool, one set of migrations, one backup strategy, and tenant isolation enforced at the database level by PostgreSQL. The tradeoff is the "noisy neighbor" problem — one tenant's heavy query can impact others. We mitigate this with statement_timeout, per-tenant connection limits, and eventually sharding by tenant_id range.

2. Q: How does your architecture handle a sudden spike from a single enterprise tenant (e.g., a Black Friday event causing 100× normal dashboard views)? A: Several layers of protection: (1) Rate limiting at the API gateway prevents any single tenant from consuming disproportionate resources — enterprise tiers get 10× the limit, not unlimited. (2) Dashboard query caching in Redis with TTL matching the data freshness requirements — if data syncs every 15 minutes, cache dashboard data for 14 minutes. A 100× view spike hits Redis, not the database. (3) CDN caching for publicly shared dashboards — no backend hit at all. (4) For enterprise tenants with sustained high load, we provision a dedicated read replica. Their dashboard traffic routes to their replica, isolating them from other tenants. (5) Auto-scaling of dashboard service instances based on request latency, not just count — if p99 crosses 500ms, add instances.

3. Q: Walk through the data flow when a tenant connects Stripe as a data source and views their first dashboard. Where are the failure points, and how do you handle each? A: The flow has 4 stages and 4 failure modes. Stage 1: OAuth connection — tenant clicks "Connect Stripe" → redirected to Stripe OAuth → receives refresh_token. Failure: Stripe OAuth is down. Handle: show friendly error, suggest trying later. No data lost. Stage 2: Initial sync — sync worker receives job, calls Stripe API with , gets metrics, transforms, writes to TimescaleDB with sync_id for idempotency. Failure points: Stripe API timeout (circuit breaker opens after 5 failures, job goes to retry queue), network error (exponential backoff retry up to 4 times), database write failure (DLQ after 4 retries). — user opens dashboard, query engine checks Redis cache (miss on first view), queries TimescaleDB for metrics, computes aggregation, caches result. Failure: database slow (statement_timeout at 10s, return partial data with "some metrics unavailable" banner). — subsequent views hit Redis cache. Cache TTL = sync interval minus 10% (if 60-min sync, cache 54 min). This ensures cache expires before new sync data arrives, preventing stale reads. At every stage, the user sees either the data or a clear ("Connecting to Stripe..." → "Syncing data..." → "Dashboard ready" or "Some data delayed — retrying").

Summary

DashCore's architecture makes intentional tradeoffs at every layer. Tenant isolation uses PostgreSQL RLS — simple to operate at 10k tenants, with a clear path to sharding when needed. Dashboard reads are routed to read replicas for independent scaling, while writes stay on the primary for consistency. Data syncs flow through a message queue with idempotent workers, circuit breakers, and DLQs — because external APIs fail in every way possible. Rate limiting is tiered per tenant at the API gateway, preventing any single tenant from degrading the platform. Redis caches dashboard query results with TTLs aligned to sync intervals. Graceful degradation ensures that when Stripe's API is down, only Stripe dashboards are affected — everything else keeps working. The scaling strategy is deliberately phased: start with a well-tuned monolith-ish architecture, introduce read replicas at 1k tenants, shard by tenant_id at 10k, and run chaos experiments at every phase.

Quick recall

• Tenant isolation: PostgreSQL RLS with tenant_id on every table. Simpler than schema-per-tenant at 10k scale.
• Read replicas: dashboard reads → replicas, writes → primary. Add replicas as read traffic grows.
• Data sync: queue-based with idempotent workers. Each sync job has a unique sync_id. Workers check before inserting.
• Rate limiting: tiered per tenant (100/1k/10k req/min). API gateway enforces with Redis-backed counters.
• Cache strategy: dashboard results cached in Redis. TTL = sync interval minus 10%.
• Circuit breakers: wrap all external API calls. Open circuit after 5 failures, half-open after 60s.
• Graceful degradation: each data source fails independently. Other dashboards unaffected.
• Sharding path: single DB with RLS → shard by tenant_id range at 10k → consistent hashing at 50k+.
• ADR format for design decisions: Decision, Rationale, Tradeoffs, Alternatives considered.
• Phase approach: Foundation → Core Features → Scale & Resilience → Production Readiness.