Small update:
I upgraded to TrueNAS Scale 24.10. This immediately stopped the issue with the application service constantly writing to the mirrored pool.
I then configured the disks in the mirrored pool to have Adv. Power Management: 1 (lowest power setting with spindown).
When I run smartctl I get the following:
root@truenas[~]# sudo /usr/sbin/smartctl -i -n standby /dev/sdc
smartctl 7.4 2023-08-01 r5530 [x86_64-linux-6.6.44-production+truenas] (local build)
Copyright (C) 2002-23, Bruce Allen, Christian Franke, www.smartmontools.org
Device is in STANDBY mode, exit(2)
It looks like everything is now worked as I would have hoped. I think we can mark this one as solved.
Thanks for the tips and support!