wulf-pulse/app/api/admin/pipeline-executions/route.ts

/**
 * GET /api/admin/pipeline-executions?limit=N&fallbacks_only=1&pipeline_id=NN
 *
 * Lists pipeline-engine executions across ALL pipelines (or one pipeline
 * when pipeline_id is provided). Joins a `has_fallback` boolean computed
 * from pipeline_execution_steps.output_data ? 'user_route_fallback'.
 * ROUTE-07 / D-11 — admin filter for user_route_fallback events.
 *
 * Uses FOUR complete parameterized SQL strings — never references a
 * SELECT-list alias inside the same SELECT's WHERE clause (HIGH 4 fix).
 */

import { NextRequest, NextResponse } from 'next/server';
import { requireAdmin } from '@/lib/auth-utils';
import { postgresClient } from '@/lib/services/postgres-client';

export async function GET(req: NextRequest): Promise<NextResponse> {
  const { error } = await requireAdmin();
  if (error) return error;

  const { searchParams } = new URL(req.url);
  const fallbacksOnly = searchParams.get('fallbacks_only') === '1';
  const pipelineIdRaw = searchParams.get('pipeline_id');
  const pipelineId = pipelineIdRaw && /^\d+$/.test(pipelineIdRaw)
    ? Number(pipelineIdRaw) : null;
  const limitRaw = searchParams.get('limit');
  const limit = limitRaw && /^\d+$/.test(limitRaw)
    ? Math.min(Number(limitRaw), 500) : 100;

  try {
    const params: (number | string)[] = [];
    let sql: string;

    if (fallbacksOnly && pipelineId !== null) {
      params.push(pipelineId, limit);
      sql = `
        SELECT pe.*, true AS has_fallback
        FROM pipeline_executions pe
        WHERE pe.pipeline_id = $1
          AND EXISTS (
            SELECT 1 FROM pipeline_execution_steps pes
            WHERE pes.execution_id = pe.id
              AND pes.output_data ? 'user_route_fallback'
          )
        ORDER BY pe.created_at DESC
        LIMIT $2
      `;
    } else if (fallbacksOnly) {
      params.push(limit);
      sql = `
        SELECT pe.*, true AS has_fallback
        FROM pipeline_executions pe
        WHERE EXISTS (
          SELECT 1 FROM pipeline_execution_steps pes
          WHERE pes.execution_id = pe.id
            AND pes.output_data ? 'user_route_fallback'
        )
        ORDER BY pe.created_at DESC
        LIMIT $1
      `;
    } else if (pipelineId !== null) {
      params.push(pipelineId, limit);
      sql = `
        SELECT pe.*,
          EXISTS (
            SELECT 1 FROM pipeline_execution_steps pes
            WHERE pes.execution_id = pe.id
              AND pes.output_data ? 'user_route_fallback'
          ) AS has_fallback
        FROM pipeline_executions pe
        WHERE pe.pipeline_id = $1
        ORDER BY pe.created_at DESC
        LIMIT $2
      `;
    } else {
      params.push(limit);
      sql = `
        SELECT pe.*,
          EXISTS (
            SELECT 1 FROM pipeline_execution_steps pes
            WHERE pes.execution_id = pe.id
              AND pes.output_data ? 'user_route_fallback'
          ) AS has_fallback
        FROM pipeline_executions pe
        ORDER BY pe.created_at DESC
        LIMIT $1
      `;
    }

    const result = await postgresClient.query<Record<string, unknown>>(sql, params);
    return NextResponse.json({ data: result.rows });
  } catch (e) {
    console.error('GET /api/admin/pipeline-executions failed:', e);
    return NextResponse.json(
      { error: 'Failed to read executions', message: e instanceof Error ? e.message : 'unknown' },
      { status: 500 },
    );
  }
}