If I don't like what I make = failure.
If people it's targetted at don't like what I make = failure.
If I don't make enough money from it = failure.
That's three areas to improve on until I can assume a creative endeavor is a success. Personal pride, recognition, sustainability.
When it comes to these things, there's no need to overthink it. Soul-searching is all well and good, but agonizing to yourself in your own mind about whether you're sabotaging yourself or whether you can be satisfied with a less-than-perfect outcome doesn't really do anything for you. These aren't steps forwards, they're shower thoughts. Any time spent thinking that it's good to accept failure could've been spent trying to bring success to yourself instead. Whether you believe in yourself or not isn't necessarily relevant, what's relevant is whether you're getting closer to the goal, and talent or confidence are just ways to get there, much like hard work. Plenty of people who never believed in themselves found success, they simply kept working instead of brooding.
While work isn't a guarantee something will work, no work is a guarantee that it won't, so it's the one thing you should keep in check.
I feel like the best moment for doubt is at the very start of a project. Assess your skills, assess the available assets, look at the competition, think about whether what you have to bring is different enough (through sheer quality or originality), or whether doing this will help you in some way (like a training project, learning to create a new mechanic, etc), and then get to work.
But doubt is a poison after that. Once you decided to greenlight your own project you have to focus your energy into that and leave fears at the door. Even if you do reassess the situation from time to time, it should be quick, lucid, decisive, and consequential, not a way to get stuck in your own mind while slowing yourself down.