Platform & Infrastructure Improvement Plan

B2B Analytics SaaS — Series B, 50 Engineers

1. Shared Capabilities Plan

1.1 Problem Statement

Multiple feature teams are independently rebuilding export functionality, filtering logic, and permission checks. This results in:

• Duplicated effort: Engineering hours wasted re-implementing solved problems
• Inconsistent behavior: Users experience different filtering semantics, export formats, and permission enforcement across features
• Maintenance burden: Bug fixes and improvements must be applied in multiple places
• Security risk: Permission checks implemented ad hoc increase the surface area for authorization bugs

1.2 Shared Capability Inventory

1.3 Recommended Shared Services Architecture

A. Export Service

• Scope: Centralized async export pipeline supporting CSV, Excel, PDF, and scheduled exports
• Design:
  • Accept a standardized export request (data source, filters, format, recipient)
  • Use a job queue (e.g., Redis-backed Sidekiq, or a dedicated task queue) for async processing
  • Store generated files in object storage (S3/GCS) with signed URLs
  • Notify users via webhook/email on completion
• API Contract:

  POST /api/v1/exports
  {
    "source": "dashboard_metrics",
    "filters": { ... },
    "format": "csv",
    "notify": "email"
  }
  

• Benefits: Uniform format support, centralized size limits, progress tracking, retry logic

B. Filtering & Query Engine

• Scope: Shared filter definition, parsing, validation, and SQL generation
• Design:
  • Define a filter DSL (JSON-based) that all feature teams use
  • Central library validates filter syntax, prevents injection, applies tenant scoping
  • Generates parameterized SQL or query builder calls
  • Supports common operations: equality, range, contains, in-list, date ranges, null checks
• Filter Schema Example:

  {
    "filters": [
      { "field": "created_at", "op": "gte", "value": "2026-01-01" },
      { "field": "status", "op": "in", "value": ["active", "trial"] }
    ],
    "sort": { "field": "created_at", "dir": "desc" }
  }
  

• Benefits: Consistent UX, single place for query optimization, SQL injection prevention

C. Authorization Service

• Scope: Centralized permission evaluation replacing inline checks
• Design:
  • Implement a policy engine (consider OPA/Rego, Casbin, or custom RBAC/ABAC service)
  • Define resources, actions, and roles in a central policy store
  • Expose a lightweight SDK that feature teams call: authz.check(user, action, resource)
  • Cache permission evaluations with short TTLs for hot paths
  • Emit audit events for every authorization decision
• Migration Path:
  1. Catalog all existing permission checks across the codebase
  2. Map them to a unified role/permission model
  3. Implement the central service with a compatibility shim
  4. Migrate teams incrementally (one feature area per sprint)
  5. Remove legacy inline checks after validation
• Benefits: Single source of truth for access control, auditable, easier compliance (SOC 2, etc.)

1.4 Platform Team Structure

• Recommendation: Form a dedicated Platform Engineering team (3-5 engineers initially)
• Charter: Own shared capabilities, define APIs, provide SDKs, maintain SLOs for platform services
• Operating Model:
  • Platform team builds and maintains core services
  • Feature teams consume via well-documented SDKs and APIs
  • Platform team holds office hours and reviews integration PRs
  • Shared capabilities are versioned; breaking changes follow a deprecation policy

1.5 Migration Strategy

2. Postgres Scaling Analysis

2.1 Current State Assessment

2.2 Immediate Optimizations (Weeks 1-4)

These require no architectural changes and can deliver quick wins:

A. Query Performance Audit

• Run pg_stat_statements to identify the top 20 slowest and most frequent queries
• Use EXPLAIN ANALYZE on each; look for sequential scans, missing indexes, and poor join plans
• Add targeted indexes (partial indexes, covering indexes, expression indexes as needed)
• Identify and fix N+1 query patterns in application code

B. Connection Pooling

• Deploy PgBouncer in transaction mode if not already present
• Target: reduce active connections from potentially hundreds to a managed pool (50-100 server connections)
• This alone often improves p99 latency significantly

C. Configuration Tuning

• Review and optimize: shared_buffers (25% of RAM), effective_cache_size (75% of RAM), work_mem, maintenance_work_mem
• Enable pg_stat_monitor for detailed query analytics
• Set random_page_cost appropriately for SSD storage (1.1-1.5)

D. Table Maintenance

• Identify bloated tables with pgstattuple; run targeted VACUUM FULL during maintenance windows
• Check for unused indexes consuming write overhead
• Add table partitioning to the largest tables (likely time-series analytics data) using native declarative partitioning by date range

2.3 Medium-Term Scaling (Weeks 4-12)

A. Read Replicas

• Deploy 1-2 read replicas for analytics/reporting queries
• Route read traffic at the application level (or use a proxy like PgPool-II)
• Target: offload 60-80% of read traffic from primary
• Acceptable replication lag: <1 second for most analytics use cases

B. Table Partitioning

• Partition the largest tables (analytics events, audit logs, time-series metrics) by month or week
• Benefits: faster queries with partition pruning, easier data lifecycle management, parallel query execution
• Implementation:

  CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    tenant_id uuid NOT NULL,
    created_at timestamptz NOT NULL,
    ...
  ) PARTITION BY RANGE (created_at);
  
  CREATE TABLE events_2026_q1 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
  

C. Data Archival Strategy

• Define retention policies: hot data (0-90 days in primary), warm data (90-365 days in read replica or cheaper storage), cold data (>1 year in object storage/Parquet)
• Implement automated archival jobs that move old partitions
• Target: keep primary database under 500 GB even with 5x growth

D. Caching Layer

• Add Redis/Memcached for frequently-read, slowly-changing data:
  • Permission/role lookups (TTL: 60s)
  • Dashboard metadata and configuration (TTL: 300s)
  • Aggregated analytics results (TTL: varies by freshness requirement)
• Target: 40-60% cache hit rate for read queries, reducing DB load proportionally

2.4 Long-Term Architecture (Months 3-6)

A. Analytical Workload Separation (OLAP Split)

• Move heavy analytical queries to a dedicated analytical store
• Options:
  • ClickHouse: Excellent for time-series analytics, columnar storage, fast aggregations
  • TimescaleDB: If staying in the Postgres ecosystem is important
  • BigQuery/Redshift: If preferring managed services
• Stream data from Postgres via CDC (Debezium/Kafka Connect) to the analytical store
• Feature teams query the analytical store for dashboards and reports; Postgres handles transactional workloads

B. Sharding Evaluation

• If single-tenant queries dominate, consider tenant-based sharding using Citus or application-level sharding
• For a B2B SaaS with analytics, tenant-based sharding is natural since queries rarely cross tenant boundaries
• Citus (Postgres extension) can shard by tenant_id with minimal application changes
• Decision point: pursue sharding only if the OLAP split + read replicas + partitioning are insufficient

C. Connection Architecture

• Evaluate upgrading to a service mesh or sidecar proxy pattern for database connections
• Consider Postgres logical replication for zero-downtime migrations

2.5 Projected Capacity Timeline

3. Reliability SLOs

3.1 SLO Framework

We recommend defining SLOs across four dimensions: availability, latency, correctness, and freshness.

3.2 Proposed SLOs

Tier 1 — User-Facing API (Dashboard, Core Analytics)

Tier 2 — Export & Batch Processing

Tier 3 — Internal Platform Services (AuthZ, Filtering)

Tier 4 — Database & Infrastructure

3.3 Error Budget Policy

• Budget: With 99.9% availability SLO, the error budget is 0.1% per rolling 30-day window (~43 minutes/month)
• When budget is healthy (>50% remaining): Normal feature velocity, deploy at will
• When budget is stressed (10-50% remaining): Require additional review for risky deploys, increase monitoring
• When budget is exhausted (<10% remaining): Freeze non-critical deploys, all engineering effort focuses on reliability until budget recovers
• Review cadence: Weekly SLO review in engineering standup, monthly SLO review with leadership

3.4 Observability Requirements

To measure these SLOs, implement:

1. Metrics: Prometheus/Datadog for request latency histograms, error rates, saturation
2. Logging: Structured JSON logs with request IDs, tenant IDs, and latency breakdowns
3. Tracing: Distributed tracing (Jaeger/Datadog APM) across all services, especially for the shared capability calls
4. Dashboards: One SLO dashboard per tier, visible to all engineers
5. Alerting:
   • Page (PagerDuty): >1% error rate sustained for 5 minutes, database primary down
   • Warn (Slack): SLO burn rate exceeding budget, replication lag >10s, export queue depth >100
6. SLO Tracking Tool: Consider Nobl9, Sloth, or a custom Prometheus recording rules setup

4. Execution Roadmap

4.1 Overview

The plan spans 6 months, divided into three phases. Each phase delivers independent value while building toward the full vision.

Month 1-2:  STABILIZE  — Quick wins, stop the bleeding
Month 3-4:  PLATFORM   — Build shared capabilities, scale reads
Month 5-6:  SCALE      — Analytical workload separation, full migration

4.2 Phase 1: Stabilize (Weeks 1-8)

Goal: Reduce immediate pain, establish foundations

Phase 1 Success Criteria:

• p95 API latency reduced by 30%
• SLO dashboards operational
• Platform team staffed and chartered
• API specs approved by feature team leads

4.3 Phase 2: Platform Build (Weeks 9-16)

Goal: Deliver shared capabilities v1, partition database, begin migrations

Phase 2 Success Criteria:

• Export Service handling 100% of Team 1's exports
• Filter Engine SDK adopted by 3 teams
• Authorization service evaluating permissions for at least 1 feature area
• Database primary under 500 GB with partitioning + archival active
• p95 latency < 200ms

4.4 Phase 3: Scale (Weeks 17-24)

Goal: Handle 5x traffic, complete migrations, operational maturity

Phase 3 Success Criteria:

• System handles 5x current traffic in load tests with SLOs met
• All feature teams consuming shared capabilities (zero legacy export/filter/authz code)
• OLAP store handling 100% of analytical queries
• Database primary stable at <600 GB with archival active
• Error budget healthy across all tiers
• On-call rotation staffed and runbooks complete

4.5 Staffing Summary

4.6 Risk Register

4.7 Key Decision Points

1. Week 4: Finalize analytical store choice (ClickHouse vs. TimescaleDB vs. managed service)
2. Week 8: Decide on authorization model (RBAC vs. ABAC vs. hybrid) based on catalog findings
3. Week 12: Evaluate whether Citus sharding is needed based on partitioning + OLAP split results
4. Week 16: Go/no-go on Phase 3 timeline based on Phase 2 migration velocity
5. Week 20: Assess whether SLO targets need adjustment based on real-world data

5. Summary of Expected Outcomes

This plan should be reviewed with engineering leadership, SRE, and feature team leads before execution begins. Priorities may shift based on the query audit findings in Week 1-2 and the capability catalog in Week 2-4.