Sharepoint - Powershell/task scheduler OR timer job(Farm solution) OR workflow/retention policy . when i want to implement a background jobs
Workflows
The last option I'd go with is the workflow: workflows are slow, buggy, do not work at all when there's a lot of them running, complicated to debug, and most of all they completely disturb the users since they're visible in the UI (with a messy UI). Why would a background task be visible in the UI? In addition, conceptually, your task is not a workflow: there's no interaction with users, no need for a UI, etc.
And also: workflows don't migrate well, especially running instances... and in your case, you'll have running instances.
And also (bis): your workflow would do what? Wait most of the time? Have a big one "Wait" activity? Just by imagining what he'd look like, you can see it's not "right".
Scheduled PowerShell
I like that one for its simplicity and the ease to maintain for a non-dev person. No need to know Visual Studio, WSP, packages, etc. You can create your script, test it, improve it, and once it's ready: you schedule it. But, as you, said, you will create dependencies on SharePoint outside the SharePoint core: on which server do you deploy? What happens if the server is removed from the farm? Will somebody else be reminded she/he has to deploy it on the new farm in case of migration?
Another thing: a scheduled task will give you the opportunity to retry failed work items (the same applies to jobs).
If you go that way, the most important point will be: add logging and document your work!
Timer job
Definitely the way I'd do it. WSP are not deprecated. Microsoft just wants you to move to the add-in (App) concept so you're "cloud-ready". But WSP are still there, as long as SharePoint will exist on-premises. The only downside I can see with this approach is: you need a good SharePoint developer to do it, and to do it good.
Another one: create a client app
Another approach you may consider: develop a C# client application (or service) leveraging the client API of SharePoint. That client could run on a third-party server (not part of the SP farm). While this has the same downside as the "Scheduled PowerShell" (outside of SP, and even a need for a third-party server) it has the advantage of being "cloud/O365-ready". But still, not the one I prefer...
TL;DR
- You are/have/can_buy a good SharePoint developer: a job in a WSP package
- You aren't/don't_have/can't_buy a good SharePoint developer: scheduled PowerShell + documentation
- You want to be "cloud-ready": client app
- You want problems: workflows
To be honest, based on the requirements:
Check if a column named "Review Date" of an item is met (Review date = today), and if so, to change the item status to be "In Progress".
Then to send an email to the "Assigned To" user , that there is an item assigned to him with “Review Date” that have been met.
I would go for a Workflow. Why? Well, because it has the characteristics of a workflow. You could write a PowerShell script and have it execute from the task scheduler, you also could write some custom code and deploy it to the farm as a timer job, but to me that would be like cutting holes in the floor of your car and using your feet to power it -- why not just use the motor!?
The workflow engine is there to solve tasks just like this. It can be set up with minimal code and can easily be managed and altered without having to do any code deployments, or fiddled with the task scheduler.
You can utilize custom timer jobs for pieces of work that are too complex to package into a workflow, and you can go down the PowerShell/Scheduler route when you aren't able to use a timer job (i.e. SharePoint Online), or for when custom code deployment are disallowed by policy.
(A code-only option, as an extra, not necessarily a suggested answer but one for others who will in future come across this question)
I'm surprised nobody's brought up the pretty awesome Patterns and Practices Remote Timer Job Framework
https://github.com/OfficeDev/PnP/tree/master/Solutions/Core.TimerJobs.Samples
Basically it's a .NET library that lets you define a Timer Job class, much like the server side OM equivalent, but what's better is you implement and run in a .NET Console app, which lets you schedule it via the Windows server task schedular, or the exact same console app zipped up can be used as an Azure Web Job.
Maximum flexibility with this model, and you can just use the Client object model to get what you need, and you can then do absolutely anything else you want in .NET code.