[Phase 4] Job processing metrics and monitoring

Description

Add real-time job processing metrics to help users understand system performance and their job queue status.

Dependencies

None - Can be implemented independently

Requirements

User-Facing Features:

1. Personal Job Dashboard (/dashboard page enhancement):

• Active jobs count with status
• Average processing time
• Job success rate (last 30 days)
• Recent failed jobs with error details

1. File Upload Page Enhancement:

• Show current queue depth
• Estimated wait time based on avg processing time
• System health indicator (green/yellow/red)

Component: apps/web/src/components/dashboard/JobMetricsCard.tsx

export async function JobMetricsCard() {
  const supabase = createRouteHandlerClient();
  const { data } = await supabase.rpc('get_user_job_metrics');
  
  if (!data) return null;
  
  return (
    <div className="bg-white rounded-lg shadow p-6">
      <h3 className="text-lg font-semibold mb-4">Processing Metrics</h3>
      
      <div className="grid grid-cols-2 gap-4">
        <div>
          <div className="text-2xl font-bold">{data.active_jobs}</div>
          <div className="text-sm text-gray-500">Active Jobs</div>
        </div>
        
        <div>
          <div className="text-2xl font-bold">
            {data.avg_processing_time_seconds ? 
              `${Math.round(data.avg_processing_time_seconds)}s` : 
              '—'
            }
          </div>
          <div className="text-sm text-gray-500">Avg Processing Time</div>
        </div>
        
        <div>
          <div className="text-2xl font-bold">
            {data.success_rate ? `${data.success_rate.toFixed(1)}%` : '—'}
          </div>
          <div className="text-sm text-gray-500">Success Rate (30d)</div>
        </div>
        
        <div>
          <div className="text-2xl font-bold">{data.failed_jobs_30d}</div>
          <div className="text-sm text-gray-500">Failed Jobs (30d)</div>
        </div>
      </div>
      
      {data.recent_failures && data.recent_failures.length > 0 && (
        <div className="mt-4 border-t pt-4">
          <h4 className="text-sm font-medium text-gray-700 mb-2">Recent Failures</h4>
          <div className="space-y-2">
            {data.recent_failures.map((job: any) => (
              <div key={job.id} className="text-sm bg-red-50 p-2 rounded">
                <div className="font-medium">{job.file_name}</div>
                <div className="text-red-600">{job.error_message}</div>
              </div>
            ))}
          </div>
        </div>
      )}
    </div>
  );
}

Required RPC:

CREATE OR REPLACE FUNCTION get_user_job_metrics()
RETURNS TABLE (
  active_jobs BIGINT,
  avg_processing_time_seconds NUMERIC,
  success_rate NUMERIC,
  failed_jobs_30d BIGINT,
  recent_failures JSONB
) SECURITY DEFINER AS $$
DECLARE
  v_user_id UUID := auth.uid();
BEGIN
  RETURN QUERY
  SELECT 
    -- Active jobs (pending or processing)
    COUNT(*) FILTER (
      WHERE status IN ('pending', 'processing')
    )::BIGINT as active_jobs,
    
    -- Average processing time (completed jobs, last 30 days)
    AVG(
      EXTRACT(EPOCH FROM (completed_at - started_at))
    ) FILTER (
      WHERE status = 'completed' 
      AND completed_at >= NOW() - INTERVAL '30 days'
    ) as avg_processing_time_seconds,
    
    -- Success rate (last 30 days)
    (
      COUNT(*) FILTER (WHERE status = 'completed' AND created_at >= NOW() - INTERVAL '30 days')::NUMERIC /
      NULLIF(COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '30 days'), 0)
    ) * 100 as success_rate,
    
    -- Failed jobs count (last 30 days)
    COUNT(*) FILTER (
      WHERE status = 'failed' 
      AND created_at >= NOW() - INTERVAL '30 days'
    )::BIGINT as failed_jobs_30d,
    
    -- Recent failures (last 5)
    COALESCE(
      (
        SELECT jsonb_agg(
          jsonb_build_object(
            'id', j.id,
            'file_name', f.filename,
            'error_message', j.error_message,
            'created_at', j.created_at
          )
        )
        FROM (
          SELECT * FROM jobs 
          WHERE user_id = v_user_id 
          AND status = 'failed'
          ORDER BY created_at DESC
          LIMIT 5
        ) j
        LEFT JOIN files f ON f.id = j.file_id
      ),
      '[]'::jsonb
    ) as recent_failures
    
  FROM jobs
  WHERE user_id = v_user_id;
END;
$$ LANGUAGE plpgsql;

-- System-wide queue depth (for upload page)
CREATE OR REPLACE FUNCTION get_system_queue_depth()
RETURNS TABLE (
  pending_jobs BIGINT,
  processing_jobs BIGINT,
  estimated_wait_minutes NUMERIC
) SECURITY DEFINER AS $$
BEGIN
  RETURN QUERY
  SELECT 
    COUNT(*) FILTER (WHERE status = 'pending')::BIGINT,
    COUNT(*) FILTER (WHERE status = 'processing')::BIGINT,
    -- Estimate: pending jobs * avg processing time / 60
    (
      COUNT(*) FILTER (WHERE status = 'pending') *
      COALESCE(
        (
          SELECT AVG(EXTRACT(EPOCH FROM (completed_at - started_at)))
          FROM jobs
          WHERE status = 'completed'
          AND completed_at >= NOW() - INTERVAL '1 hour'
        ),
        30 -- Default 30 seconds if no recent data
      ) / 60
    )::NUMERIC as estimated_wait_minutes
  FROM jobs
  WHERE created_at >= NOW() - INTERVAL '1 hour';
END;
$$ LANGUAGE plpgsql;

Upload Page Enhancement:

// Add to apps/web/src/app/(authenticated)/upload/page.tsx

export default async function UploadPage() {
  const supabase = createRouteHandlerClient();
  const { data: queueStatus } = await supabase.rpc('get_system_queue_depth');
  
  return (
    <>
      {queueStatus && (
        <SystemHealthIndicator
          pendingJobs={queueStatus.pending_jobs}
          estimatedWait={queueStatus.estimated_wait_minutes}
        />
      )}
      {/* existing upload form */}
    </>
  );
}

Acceptance Criteria

• [ ] Job metrics display correctly for logged-in users
• [ ] Active jobs count is real-time accurate
• [ ] Success rate calculation is correct
• [ ] Recent failures show helpful error messages
• [ ] Queue depth estimation is reasonable
• [ ] System health indicator reflects actual load

Testing

• [ ] Test with no jobs
• [ ] Test with active jobs
• [ ] Test with only failed jobs
• [ ] Verify avg processing time calculation
• [ ] Test queue depth with high load

Performance Considerations

• Jobs table will grow large - ensure queries use proper indexes
• Consider adding index: CREATE INDEX idx_jobs_user_status_created ON jobs(user_id, status, created_at DESC);

Phase

Phase 4: Enhanced Processing Metrics