Skip to main content

Keeping the token table small

Every request inserts a row, and consumption only marks it consumed, so the table needs a regular purge. The package ships the command and can register the schedule for you:

// config/email-magic-link.php
'prune' => [
    'schedule' => true,
    'frequency' => 'daily',   // hourly · daily · weekly · monthly
],

It is off by default. A package that deletes rows on a cadence nobody chose is making your decision, and an application that already wires the command itself would end up running it twice.

If you manage the migrations yourself and call ignoreMigrations(), pass tablesExist: true. Without it the package cannot tell whether its tables exist and leaves the entry out rather than run a nightly command against tables that may be missing; email-magic-link:doctor reports a schedule that was asked for and left out, and a purge that nothing in the scheduler runs at all. If you would rather keep the schedule in your own file, leave the switch off:

use Illuminate\Support\Facades\Schedule;

Schedule::command('email-magic-link:purge --isolated')->daily();

Either way the command deletes rows that are expired or already consumed and reports how many it removed. It deletes in chunks (prune.chunk, 1000 rows by default), so no single statement holds its row locks for long on a table that grew for months, and it is Isolatable: pass --isolated on your own schedule line and a second copy refuses to start while one runs. The package-registered entry adds withoutOverlapping() and a description; schedule:list shows it under its command, email-magic-link:purge. See the command reference.

Watching it run​

A failed purge surfaces the way any failed scheduled command does: the exception reaches your handler and your error tracker. A success heartbeat is yours to add, and so is onOneServer(): it needs a cache store with atomic locks, which the package cannot assume on your behalf, and it hangs off the same loop. With prune.schedule on, the package owns the schedule entry, so attach the ping through the scheduler's list rather than to a line of your own:

foreach (app(Schedule::class)->events() as $event) {
    if (str_contains($event->command ?? '', 'email-magic-link:purge')) {
        $event->pingOnSuccess('https://heartbeat.example/purge');
    }
}

Or keep the manual Schedule::command(...) line, where thenPing() and friends attach directly.

With Telescope recording, the purge is slower and holds more memory: Telescope keeps an entry for every statement until the command ends. Measured over 100,000 expired tokens, that was 255 entries, about 1.3 MB more peak memory and 3.3 s instead of 2.0 s. It grows with the table, so for a first purge over millions of rows, run the command with TELESCOPE_ENABLED=false.

Under multi-tenancy​

The self-registered entry runs email-magic-link:purge as a plain subprocess, on the central connection, with no tenant context: with the tables only on tenant databases it fails every night, and with the tables also on the central one it purges nothing and reports success. Leave prune.schedule off there and run the command through your tenancy runner — see Running under multi-tenancy.

What the purge does to your locks​

The purge never waits. It claims each chunk with SELECT ... FOR UPDATE SKIP LOCKED and then deletes the rows it holds, so a row another transaction is currently touching is skipped and collected on the next run rather than queued behind. That is deliberate: a background job is the one caller that can always come back later, and a statement that never waits cannot take part in a deadlock, whatever order anything else acquires its locks in.

On MySQL each chunk also runs at READ COMMITTED. At the default REPEATABLE READ, InnoDB locks the index gaps a locking read scans as well as the rows it returns, and the chunk's select scans the expires_at and consumed_at indexes to their ends: every sign-in and every new link then waited until the chunk committed. At READ COMMITTED it locks only the rows it deletes. Two cases keep your default level, and in them a sign-in can wait for one chunk:

  • a server that writes its binary log as statements (binlog_format=STATEMENT), because InnoDB refuses writes at READ COMMITTED there;
  • a purge you call inside a transaction of your own, because the level of an open transaction cannot change.

This matters most for invitations, because accepting one runs your handler inside the transaction that holds the invitation row. Without the skip, a handler that also writes to the invitations table -- calling revoke() for the same address, say -- could form a cycle with a purge chunk, and the database picks the victim. It might pick the person signing in.

Your server's timeouts are still yours​

PostgreSQL and MySQL both ship with no statement timeout, no lock timeout, and no idle transaction timeout. The package cannot set them for you: they are server or role settings, and a package that changed them would be changing the behavior of every other query in your application.

Two consequences worth knowing about, both measured on PostgreSQL 18:

  • An acceptance handler that hangs holds its transaction open, and an open transaction blocks VACUUM from reclaiming dead rows across the whole database, not only on the tables it touched.
  • Nothing bounds how long your handler may run inside that transaction, so nothing bounds how long the invitation row stays locked.

If your handler does real work -- provisioning an account, calling an external service -- set statement_timeout and idle_in_transaction_session_timeout on the role your application connects as, and keep lock_hold_seconds above the slowest issuance you expect.

Why rows are kept until then​

Consumption is a single race-free conditional claim: the row has to survive the claim so the update can prove it changed exactly one row. Deleting on consume would also destroy the evidence that a token was already spent, which is what makes a replay fail cleanly rather than look like an unknown token.

Calling it yourself​

TokenStore::purge() is public and returns the number of deleted rows, so a custom store or your own scheduled job can drive it directly:

$deleted = app(\EmailMagicLink\Contracts\TokenStore::class)->purge();