Icarus'
Cook with Any Ingredients mod is what you are describing in the beginning. It has nothing to do with custom foods, although it does work well with them, nor does it change the base recipes. If what you are preparing requires an egg, bread, and an apple but you are out of apples, you could substitute something else for that one instance of dish prep. If you substitute another piece of fruit, there likely won't be much or any degradation in the quality of the finished dish, but such would be more likely if you used something in a different class of food (like meat). If you substitute all three ingredients with different classes, chances are the dish would come out very low quality. But who can resist baking cookies made with potatoes, garlic, and whipped cream instead of the usual ingredients to hand out to your enemies? ;)
The also popular
Ingredients Overhaul mod is what changes all the base recipes. The two mods do work well together. Be sure not to accidentally get either of the two older Overhaul mods from BlackCat007 or Anak_Ponti, they are still available and were great on earlier patch levels, but this is the updated one after Cinderellimouse took it over. She also added Greek ingredients, harvestables, and recipes in her version by way of an optional add-on.
There's a whole bunch of other custom ingredient and recipe mods available that again work with the above. They do require CCLoader, which you already have.
Icemunmun has some great ones on MTS; click All Downloads then be sure to restrict to TS3 because she also makes mods for TS4.
These are all mods and therefore already package files. They go in Mods\Packages, a subfolder for food mods would be good for better organization. Nothing needs to go in Overrides. None of them conflict with the Canning Station Overhaul. The only performance issue worth mentioning is that there might be a slight delay on clicking the fridge and then Prepare/Serve Meal while an expanded list of available recipes builds, but for most of us that's like a second or so. Perhaps around the same at the grocery store since more types of ingredients are in stock.
Ani's Drinks mod isn't really related to any of the above, that one changes the impact that "adult" beverages have on sims. I haven't played around with Sandy's custom edible foods in years, so any information I have on those is likely outdated now.