As decided to work at home tomorrow, have a chance to stay up late tonight to fix the DC water pump issue.
Last week, found that the water level does matter for DC water pumps to run properly but keeping up a high water level defeated the purpose of the rain barrel feature; moreover, in the long run it will not help in creating a reliable aquaponics system.
The solution was increased the voltage of the dc water pumps from 12v to 18v (with a voltage step up circuit). The results for water cutoff and the siphon action were positive for the smaller pump but for the extreme pump, it needed to be tuned further and it shouldn't be hard to fix it.
Of course, higher voltage here meaning higher energy usage and to me every watts count as the whole system is solar powered.