The terrible, dark secret: Species 001 are the ancient Vulcans. Before Surak, in a desperate effort to bring unity to their people and curtail violence, a Vulcan faction experimented with collective consciousness and artificial life. The experiment broke containment and attempted to absorb the entire race (against their will, obviously). Given their nascent state, they were mostly destroyed by the more powerful Vulcan military forces, with a handful of surviving members of the experiment managing to escape off world and depart for parts unknown. The faction responsible were exiled from Vulcan and became what we now know as the Romulans.
The Zhat Vash hate artificial life due to being exiled from their homeland and fear them given how quickly the experiment spiraled out of control.
And... nope, doesn't work, does it? It's not really a deep, terrible secret, when you think about it.
Perhaps... The Borg are a misdirect. Perhaps we're trying to connect too many dots here. It's likely The Borg have nothing to do with the deep, dark secret the Zhat Vash have been keeping close to their chest for thousands of years. However, there is the possibility that whatever the secret is, The Borg learned of it when they assimilated that particular Romulan ship. Is it possible The Borg want possession of whatever the secret is/represents, but are unable to assimilate it because they don't know it's location or it doesn't exist yet? Instead, they shut the cube down as bait - programmed to reactivate once what they're after is detected. In this scenario, all signs would point to Soji. She activates, The Borg activate.
That's likely hogwash as well. All we know is, the Zhat Vash have cause for hating/fearing artificial life. At this point, I'm inclined to believe Agnes killed Maddox of her own free will. So, whatever Oh shared with her must've scared the bejesus out of her.