perf: Optimize /api/remote-workflows/executions/live route (737ms avg latency)

[louis030195]

Problem

The /api/remote-workflows/executions/live route has performance issues identified via Vercel Observability:

Metric	Current Value	Target
Average Latency	737ms	<200ms
P75 Latency	799ms	<300ms
P95 Latency	979ms	<500ms
Time to First Byte (P75)	811ms	<200ms
Supabase API calls	43K (for 11K invocations)	~11K
Clerk API calls	22K (for 11K invocations)	~11K

Root Cause Analysis

1. Multiple Sequential DB Operations (5 per request)

Request → auth() → owned workflows query → shared workflows query → live_execution_status query → cacheResponse INSERT

Each request makes ~4-5 sequential database calls explaining the 4x Supabase call ratio.

File: src/app/api/remote-workflows/executions/live/route.ts

2. cacheResponse() Called on Every Request

Lines 177-188 and 202-209 insert into api_response_cache on every request. This is for documentation but adds overhead to production traffic.

3. Redundant Clerk API Calls

getEffectiveOrgId() in src/lib/mediarAuth.ts calls:

• auth() (line 103)
• currentUser() (line 104)
• isMediarAdmin() (line 105) which calls currentUser() again

4. Dynamic Import Overhead

// Line 30 - dynamic import on every request
const { getEffectiveOrgId } = await import('@/lib/mediarAuth');

5. Two Separate Queries for Workflow Access

Lines 77-92 make two queries then merge in JS:

// Query 1: owned workflows
const { data: ownedWorkflows } = await supabase
  .from('deployed_workflows')
  .select('id')
  .eq('organization_id', orgId);

// Query 2: shared workflows  
const { data: sharedAccess } = await supabase
  .from('workflow_organization_access')
  .select('workflow_id')
  .eq('organization_id', orgId);

Proposed Fixes

High Impact

• Remove/sample cacheResponse() - Only call for 1% of requests or disable in production
• Create RPC function get_accessible_workflow_ids(org_id) that combines owned + shared workflow queries server-side
• Single Clerk call - Refactor getEffectiveOrgId() to reuse auth data instead of multiple calls

Medium Impact

• Add short-lived caching - Cache response for 2-5 seconds using Cache-Control or in-memory cache (same org/params = same result)
• Static import - Change dynamic import to static: import { getEffectiveOrgId } from '@/lib/mediarAuth'
• Parallel queries - Run owned + shared workflow queries in parallel with Promise.all()

Low Impact

• Select only needed columns - live_execution_status uses select('*'), specify only needed fields
• Add database indexes if not present on organization_id and workflow_id columns

Expected Results

After optimization:

• Latency: 737ms → <200ms (70%+ reduction)
• Supabase calls: 43K → ~22K (50% reduction)
• Clerk calls: 22K → ~11K (50% reduction)

Labels

performance, api, database