Circumstantial evidence is still evidence, evidence by implication. Direct evidence is that which is directly asserted, like eyewitness statements, recordings, or recovered items.
Almost all of the evidence the NTSB accumulated for TWA 800 is circumstantial evidence, apart from things like the flight recorders, the eyewitness statements and physically recovering the aircraft. Even the proverbial phrase "smoking gun" would be circumstantial evidence of a murder, not direct proof.
The NTSB's circumstantial evidence is quite thorough, including literal experiments the Board performed. It's mostly quite strong, and it explains the nearly-irrefutable direct evidence (e.g. cockpit recordings, the state of the recovered instruments at the time of the accident).
Also, direct evidence can sometimes be the most unreliable. Eyewitness statements, for example, are notoriously flawed, as our memories are not perfect hard disk dumps, but are malleable. Even in this case, it's possible that the FBI & CIA or even the NTSB itself "poisoned" the direct testimony of the hundreds witnesses on shore, at sea or even in the air. (My own bias is that I have much more respect for NTSB investigators.)
The NTSB's report is nearly persuasive to me, other than the ignition source. Regardless, I do think the NTSB's recommendations & subsequent FAA action have made air travel safer as a result of their work (again, almost all of it circumstantial).