Being married to a person who uses what they want out of life as an excuse to ignore others, pass out blame and emotionally and/or physically abuse their family is tough and you are right there. I am so sorry. You are in a tough spot. Sounds like a man child having a tantrum he gets away with it possibly because you and the children are financially dependent on him? You don't say. Domestic violence is occurring right in front of you and affecting you and the children for very unhealthy outcomes that will negatively affect your children's emotional well being, possibly fo the rest of their lives. I assume you did not have children in order to abuse them, albeit, unintentionally?
Presumably you learned about husbands ambition when you dated yet you married possibly with hope that he was not who he showed you he was? Lesson one here is that people are who they show you they are but we don't see them for their truth because we look only through the lens of our need and attraction.
Or perhaps you admired his ambition for what it could bring you and did not look at the dark side of it. None of that matters now. What matters is that he is having a tantrum and the family is and will pay the price. You are subjecting yourself and your children to a life of pins and needles by letting this unacceptable behavior on both your own and his pass. Your children do not deserve the anxiety you feel. neither do you.
The anxiety you all will experience will negatively affect your children's emotional and cognitive well being as well as your own. This affects physical health too. You are going to pass down familial mental health non-wellness to them if they have to live with his tantrums, mood swings, dominance and your meekness and inability to protect them from his behavior. You will become more anxious and depressed as the years go on. Being a stay at home mother, if that is what you are, does not compensate for the emotional abuse your children will suffer throughout their childhoods and their entire lives as a result of emotional violence in the home. The fact that he stated his life would be where he wanted it to be had he not had family means he regrets all of you. Guilt over saying that might make him backtrack. If he truly did not mean that he needs to back that apology up with individual mental health counseling that explores why he said it in the first place. His backtracking won't be true remorse unless he gets the help he needs to sort himself out. he may backtrack on what he said in order to get what he needs in this game of life he is playing at to win what he wants out of it- Chilling. Your talking with him over your worries, your day, the things you deal with is the ordinary part of a loving relationship where two people who love and support each other exchange what is going on in their lives without the other needing to fix anything. it's how you stay in tune and in touch.
I suspect you are financially dependent? Financially dependent partners often bear a lot of loss and stress which morphs into anxiety which in turn is acted out on any children in the home and even friends and relatives. Possibly no human who is financially dependent or willing to be should entertain having children simply because the emotional cost to the children is too great a burden to bear.
Negotiations: First, if you are not working, I am going to suggest you get a job, love it no matter how difficult it is, love it greatly for getting you some financial independence. Work on upping any employment skills. Save every penny you can so that you can use that to take classes to upgrade your employment opportunities. You let him know that any dependence on his success, promotions, etc. is over. That you respect his need for achieving goals but that you do not respect yourself and you have huge concerns his way of dealing with disappointment is affecting the children and your own self esteem. Therefor you are going to work to offset some of the pressure he had to financially provide. He will try to talk you put of this if you are financially dependent- it's control. It is never best for children to live in an abusive household with a stay at home depressed, anxious mom. Better to live in a busy household with a thriving mom who can genuinely be there for her children, which you can do if you put away the social media at night, massively declutter the home and keep it that way and start teaching any children who can to participate in chores. Your children didn't ask to be born to abusive parents. They are here and both of you are obligated to give them an emotionally healthy childhood (Oprah Winfreys 'What Happened To You" is a great start for any parent wanting to understand the role emotional abuse has on a child's mind).
Negotiations: Let him know that you want to be supportive of his huge feelings of missing out on life but that you will not be supportive of his blaming you and the children for that because he is abusing all of you. You could let him know that you would agree to come up with a coparenting plan that emotionally and financially supports the children if he really feels being in the same household is what is costing him his happiness. That way he can be more free to pursue what he wants out of life. Do you really want to live with a person who blames you for his life not turning out right? Think about that? It's so utterly emotionally abusive that staying means being the horrible thing that got in the way, the thing that deserves nothing but wrath. You can let him know that being made to feel you are the thing that deserves his wrath is unacceptable and then say that you will not even consider staying under the same roof as an intimate partner because he has made you feel small and deserving of being blamed and harmed by him. You can offer to stay under the same roof as a roomie/friend sharing children but that you will be making the changes necessary to protect yourself and the children and hope he will as well. Let him know you respect his adulthood and have time each day to listen to his frustrations but that you have zero tolerance for being the blamed party in his need to place his failures outside of his own behavior, accomplishments, etc.
If you are unable to speak to him in a way that brings you the human dignity you deserve and if you are unable to do the things (take a job) that will free you from dependence and abuse( like taking employment, courses for job skills, going back to school, etc), then you live with a tyrant and are living in domestic violence as are your children. That is a whole other discussion because physical safety might be involved. If that is the situation, please seek help from agencies that help people in such circumstances. If you don't want to work (we don't know as you don't describe your employment/financial situation), then you too are willing to subject your children to the emotional abuse that is occurring as a result of his behavior combined with yours.
You both need help- get it for your children's sake.