I want to give users a period of time in which they can undo their account deletion. Someone suggested that I should use a CRON job that (1) checks my Supabase table daily for users who deleted their accounts over 30 days ago and (2) deletes those accounts. I have never used CRON jobs and I have very little experience with SQL. I have enabled the pg_cron extension in the Supabase Database settings but I don't know how to schedule the job or what to use for the SQL code. Below is my attempt at this, using a slightly modified Supabase example.
NOTE: The deleted_time is formatted as milliseconds since epoch so I need to compare that as a date.
select cron.schedule (
'webhook-every-minute', -- name of the cron job
'0 3 * * *', -- every day at 3 A.M.
$$ delete from users where DATEADD(ss, deleted_time,'01 JAN 1970') >= DATEADD(month,-1,GETDATE())
);
