The /api/pipelines/[id]/stream endpoint runs 3 separate DB queries every 2 seconds indefinitely: (1) full pipeline run + project JOIN, (2) all agentJobs SELECT * for this run, (3) new agent logs since last sequence. The agentJobs query does SELECT * fetching full inputData/outputData JSONB blobs (potentially MBs of tool call history) on every single poll, even when no jobs have changed. For a 30-minute pipeline, this is 900 full agentJobs fetches.
Category: api File: src/app/api/pipelines/[id]/stream/route.ts Recommendation: 1) Add an ETag/last-modified check on pipeline status — only re-query agentJobs when pipeline status changes. 2) For the agentJobs poll, select only id, agentType, status, startedAt, completedAt, error, tokenUsage — never select inputData/outputData in the polling loop (those are huge JSONB blobs). 3) Consider using Postgres LISTEN/NOTIFY instead of polling to eliminate latency and DB load. Estimated Improvement: Reduces DB load by 80%+ per active stream; prevents JSONB blob transfer across every poll; critical for multiple concurrent active pipelines