It does sound like it was an inappropriate day in terms of location and water conditions, etc to take the kids out like that. And they hadn't thought about the most sensible and safe introduction to kayaking for young, inexperienced swimmers. And that would be seriously alarming at the lack of thought, especially after your discussion.
But I do think that as a big picture, it's fantastic for kids to get out on the water in canoes, kayaks, sailboats, etc. Builds confidence, and sets them up for a lifetime of healthy fun if they enjoy it.
Again, it's about how it's done: a quiet lake or inside the harbour is a better starting point for young children. And the appropriate flotation vest as well, including a neck flotation bit for younger children/inexperienced swimmers. My 3 all started going out on sailboats/canoes as toddlers. My oldest two started sailing their own boats at just 6 and 7 years old. And they are now both in UK regional and national fleets. It's been absolutely brilliant for them. My 14 year old can now drive a powerboat and has started helping instruct younger children. So many friendships, so much fun, and so much confidence. Again, brilliant for them.My youngest is also a competent sailor, although she's not quite as gung ho as her brothers. I do highly recommend water sports for children ... but there are sensible precautions that should always be in play, especially when they're starting out so as not to put them off.
Your FIL is doing courses. That's fantastic imo.
Before kicking off at your DH, perhaps ask him why you're upset. I suspect he'll know why. And then figure out together how to solve where he went wrong today. And how to make it safer and more sensible in the future. The last thing you want to do is put your little ones off the water and water sports entirely. That could have long term consequences, too, to their detriment. Learning to swim and learning about water safety is a life skill, and you don't want them to quit because they have clocked on to your fear and made it their own. Find a way to do it where everyone is happy if this is something your DH and your FIL want to share with the children long term.
I've name changed, obviously, as some of this could be quite outing for me.