There are multiple ways to do it for sure:

What I woudl do is,

1. Create a new table for RecurrPayment and use the date(Day of a month) as the partition key to make the query faster.

2. Use a cron job to query the rows daily and queue the tasks in kafka to perform transactions.

3. To handle cancellations: When someone cancels, add the userid to redis. Before performing the transaction check in that redis set and also remove row from db

Based on the QPS, decide which DB to use. Also add checks(while performing the transaction from the kafka queu) to expire the subscription lets say after 1 year or N timeperiod.