The pipeline.list router queries pipelineRuns with WHERE project_id = ANY($ids) ORDER BY started_at DESC LIMIT 50. The schema defines idx_runs_project_status (projectId, status) but there is no index covering (projectId, startedAt). For projects with thousands of pipeline runs, the ORDER BY started_at DESC requires a sort pass after the index scan, which is expensive as data grows.
Category: database File: src/db/schema.ts Recommendation: Add a composite index: index('idx_runs_project_started').on(table.projectId, table.startedAt) in the pipelineRuns table definition. This enables an index-only range scan + sort in one pass for the most common pipeline list query. Also consider a partial index on (projectId, startedAt) WHERE status NOT IN ('completed', 'failed') for the 'active pipelines' dashboard widget. Estimated Improvement: Eliminates table sort for paginated pipeline list queries. For 10,000+ runs per project, query time could drop from 50–200ms to <5ms.