I suspect partitioned tables would be great for this - with a stored procedure to create partitions on-demand, you could split tasks up by date-range and type,

I suspect partitioned tables would be great for this - with a stored procedure to create partitions on-demand, you could split tasks up by date-range and type, and then drop the old tables once their time-range has passed and their jobs all processed. Job workers could query the parent table, no need to modify them.