What should happen with records that are left behind in the database - for instance, if the BEAM is restarted after one is inserted but before the deletion?

The answer to that may suggest alternative approaches:

• if leftover records should be periodically cleaned up, does there need to be a per-record deletion process at all?
• if leftover records are ignored, do any of them need to be deleted?

Another thing to think about: while @LostKobrakai is 100% correct that sleeping processes are basically free, having an unlimited number of them wake up simultaneously can still create a mess. Here’s a scenario:

• a fast API client calls the endpoint that inserts records + schedules deletion 1000 times in 5s. Easy to do, and the server has no problem handling the requests
• X minutes later, 1000 processes all wake up, and all try to run a Repo.delete.
  • some of them will manage to grab a connection and send a query to the DB
  • then the connection pool will run out. Now every connection in the pool is running a Repo.delete statement.
  • everything else starves: the other 900-some waking-up processes, and every Phoenix request handler block waiting for a DB connection
  • if the Repo.delete statements take too long (~100ms or so with default settings) then waiting processes will start to crash with messages like: ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after NNNNms. You can configure how long requests wait in the queue using :queue_target and :queue_interval. See DBConnection.start_link/2 for more information

The result is that a sufficiently-large-and-fast burst of requests to the API creates a brief whole-server outage a few minutes later.

Solutions like a job queue or a worker pool avoid this “thundering herd” problem by only trying to do a fixed amount of work at once.

Think we can safely count this thread in the XY problem category?

(We don’t have one but maybe we should? )

Still, to answer the original question, your best bet for a periodic worker would be to just register a worker inside your supervision tree and have it recurse infinitely:

# lib/your_app/periodic_delete_worker.ex
defmodule YourApp.PeriodicDeleteWorker do
  use Task, restart: :permanent

  def start_link(_arg) do
    Task.start_link(__MODULE__, :run, [])
  end

  def run do
    # Do your stuff here

    # Wait for a message for 1 minute.
    # If no message is received, still wake up after the 1 minute has passed
    # and reschedule the same worker.
    receive do
    after
      60_000 -> run()
    end
  end
end

And then:

# lib/your_app.ex
defmodule YourApp do
  use Application

  def start(_type, _args) do
    children =
      [
        YourApp.Repo, # just an example!
        YourApp.PeriodicDeleteWorker # 👈 this is what you want
      ]

    options = [strategy: :one_for_one, name: YourApp.Supervisor]

  Supervisor.start_link(children, options)
end

For reference, I finally went with a periodic worker. Turns out, talking to the person reviewing your PR before posting on the internet is a good idea!