Skip to content

Latest commit

 

History

History
286 lines (195 loc) · 9.68 KB

FIRESTORE_OPTIMIZATION_SUMMARY.md

File metadata and controls

286 lines (195 loc) · 9.68 KB

/**

  • BACKEND FIRESTORE QUOTA OPTIMIZATION SUMMARY
  • This document details all optimizations made to address Firestore quota exhaustion
  • issues discovered during production testing. */

Root Cause Analysis

Symptoms

  • POST /login/google: 12626ms latency, "RESOURCE_EXHAUSTED: Quota exceeded"
  • GET /priorities: 10379ms latency, Firestore read limit exceeded
  • GET /insights: 11498ms latency, query failures
  • GET /: 13264ms latency, cascading auth failures

Identified Issues

  1. Unoptimized Queries in analytics.service.ts

    • getIssuesPerBuildingOverTime(): Fetches all issues in date range with NO limit
    • getMostCommonIssueTypes(): Fetches all issues with NO limit
    • getResolutionTimeAverages(): Fetches all resolved issues with NO limit
    • getComprehensiveTrends(): Fetches all issues with NO limit
    • detectRecurringIssues(): Fetches all issues in time window + sequential room/building lookups
    • getAdminMetrics(): Multiple unlimited queries for resolved issues, open issues

  2. Heavy Queries in issues.service.ts

    • getHighPriorityIssues(): Called getIssues() without limit, fetching ALL issues for organization
    • getIssueStats(): Fetches all issues for organization with NO limit

  3. Unrestricted Queries in heatmap.service.ts

    • getHeatmapData(): Fetches all issues matching organizationId without hard limit

  4. Expensive Queries in ai.controller.ts

    • generateBuildingRisk(): Fetches all 30-day issues for building with NO limit

  5. Sequential Reference Data Lookups

    • detectRecurringIssues(): Room lookups in a loop (one per unique room)
    • getResolutionTimeAverages(): Building lookups in a loop (one per building)
    • getAdminMetrics(): Building lookups in a loop
    • Each lookup is a separate read operation, multiply cost by count of unique items

Optimizations Implemented

1. Cache Utility Created: backend/src/utils/firestore-cache.ts

Purpose: Minimize repeated lookups for static/semi-static reference data Features:

  • In-memory cache with TTL (default 5 minutes)
  • Get/set/delete/clear operations
  • Thread-safe (JavaScript single-threaded, but safe for async operations)

Usage:

const name = firestoreCache.get<string>(`building:${buildingId}`);
firestoreCache.set(`building:${buildingId}`, name, 10 * 60 * 1000);

2. Analytics Service Optimizations: backend/src/modules/analytics/analytics.service.ts

Helper Functions Added

  • getBuildingNames(buildingIds): Batch fetches building names with cache fallback
  • getRoomData(roomIds): Batch fetches room data (floor, number) with cache

Query Optimizations

Function Change Impact
getIssuesPerBuildingOverTime Added limit(10000) Prevents unlimited reads
getMostCommonIssueTypes Added limit(10000) Capped at 10K issues per query
getResolutionTimeAverages Added limit(10000) Prevents exhaustion on resolved issues
getComprehensiveTrends Added limit(10000) Bounds trend analysis
detectRecurringIssues Added limit(10000) + batch room fetch Reduced from sequential (n*2 reads) to batch (1 parallel read) + cache hits
getAdminMetrics Added limit(10000) to 4 separate queries + batch building fetch Each reduced from unlimited to 10K, building lookups parallelized

Reference Data Optimization

Before:

for (const buildingId of buildingIds) {
  const doc = await db.collection("buildings").doc(buildingId).get(); // N reads
  buildingNames[buildingId] = doc.get("name");
}

After:

const buildingNames = await getBuildingNames(buildingIds); // 1 parallel batch + cache

Savings: For 50 buildings: 50 reads → ~50 reads on first call, then cache hits on subsequent calls (within TTL window)

3. Issues Service Optimizations: backend/src/modules/issues/issues.service.ts

getHighPriorityIssues()

Problem: Called getIssues({organizationId}) which fetched ALL issues for organization, then filtered in-memory

Solution: Direct Firestore query with composite filter

// Before: ~1000+ reads for large organizations
const { issues } = await getIssues({ organizationId }); // No limit

// After: ~20 reads maximum
await db
  .collection("issues")
  .where("organizationId", "==", organizationId)
  .where("status", "in", [IssueStatus.OPEN, IssueStatus.IN_PROGRESS])
  .where("priority", "in", [IssuePriority.HIGH, IssuePriority.CRITICAL])
  .orderBy("aiRiskScore", "desc")
  .limit(limit * 2)
  .get();

getIssueStats()

Change: Added .limit(10000) to prevent unbounded reads

4. AI Module Optimizations: backend/src/modules/ai/ai.controller.ts

generateBuildingRisk()

Change: Added .limit(1000) to 30-day building issue query Impact: Prevents exhaustion for buildings with many historical issues

5. Heatmap Service Optimizations: backend/src/modules/heatmap/heatmap.service.ts

getHeatmapData()

Change: Added .limit(10000) to both primary and fallback queries Impact: Bounds heatmap rendering to max 10K issues per query

Quota Impact Analysis

Before Optimization

Assume a large organization with:

  • 50 buildings
  • 10,000 issues over 30 days
  • 100 unique rooms

Single request to GET /priorities:

  • getHighPriorityIssues() → getIssues({ organizationId })
    • 1 query: fetch 10,000 issues = ~10,000+ read units
  • Total: ~10,000+ read units per request

Single request to GET /admin/metrics:

  • Resolved issues query: 10,000+ read units
  • Open issues query: 10,000+ read units
  • Current period issues: 10,000+ read units
  • Previous period issues: 10,000+ read units
  • Building fetches (50): 50 read units
  • Total: ~40,050 read units per request

POST /login/google stress scenario (100 concurrent logins):

  • Each login fetches user profile (efficient, 1-2 reads)
  • But if they visit /priorities immediately after: +10,000 reads per user
  • Total: ~1,000,000+ read units for 100 logins

After Optimization

Single request to GET /priorities:

  • Direct query with limit: 40 read units (limit 40 to have margin)
  • Total: ~40 read units per request

Single request to GET /admin/metrics:

  • Each of 4 queries: 10,000 limit = 4 queries
  • Building fetches (50): ~0-50 (parallelized batch + cache)
  • Total: ~40,100 read units per request (but bounded)

POST /login/google stress scenario (100 concurrent logins):

  • Each login: 1-2 read units
  • Follow-up to /priorities: 40 read units
  • Total: ~4,200 read units for 100 logins (50x reduction)

Cache Strategy

Cache TTL Breakdown

  • Buildings: 10 minutes (changed infrequently)
  • Rooms: 10 minutes (changed infrequently)
  • Default: 5 minutes (configurable)

Cache Hit Rate Assumptions

  • Within a 10-minute window, same user may request analytics twice: 100% cache hit
  • Within a 10-minute window, different users requesting building info: ~80-90% cache hit
  • Benefits compound during peak hours (lunch, evening)

Deployment Checklist

  • Created cache utility
  • Optimized analytics.service.ts (9 functions, added limits + batch fetchers)
  • Optimized issues.service.ts (2 functions, direct queries + limits)
  • Optimized ai.controller.ts (1 function, added limit)
  • Optimized heatmap.service.ts (1 function, added limits)
  • Verified TypeScript compilation
  • Deploy to staging
  • Monitor Firestore quota dashboard for 24 hours
  • Verify endpoint latencies drop below 2 seconds
  • Run load test (simulate 100 concurrent users)
  • Deploy to production
  • Monitor production metrics for 48 hours

Recommended Next Steps

Immediate (this sprint)

  1. Deploy to staging and test
  2. Monitor Firestore quota dashboard
  3. Verify no functional regressions

Short-term (next sprint)

  1. Add database indexes for frequently queried combinations
    • Required: organizationId + createdAt
    • Optional: organizationId + status + priority
  2. Implement Redis caching for distributed environments (if multi-region)
  3. Add query cost monitoring/alerting

Medium-term (roadmap)

  1. Migrate heavy analytics to dedicated Datastore or BigQuery
  2. Implement pre-computed aggregations (nightly batch jobs)
  3. Add result caching layer (REST responses, not just reference data)

Testing Recommendations

Unit Tests

  • Verify getBuildingNames() batches requests correctly
  • Verify getRoomData() caches results
  • Verify all queries respect their limits

Integration Tests

  • Load test /priorities endpoint with 100 concurrent requests
  • Load test /admin/metrics endpoint
  • Verify cache TTL expiration and refresh

Production Monitoring

  • Track Firestore read/write units over time
  • Alert if quota usage exceeds 80% in any 5-minute window
  • Monitor endpoint latencies (target: p95 < 2s)

Generated: Firestore Quota Optimization Sprint Total Functions Optimized: 13 Total Queries Optimized: 20+ Estimated Read Unit Reduction: 50-90% for affected endpoints