Consume one time authentication tokens atomically

Consume a single use authentication token atomically by verifying and invalidating it inside one database transaction so only one process can succeed. For most applications this means either (a) performing a conditional UPDATE that sets a used flag and uses RETURNING to both check and claim the token, or (b) selecting the token row with a row lock and then marking it used before commit. Both approaches rely on Postgres transaction semantics and row locking to avoid races (see PostgreSQL transaction isolation documentation: https://www.postgresql.org/docs/current/transaction-iso.html and PostgreSQL explicit locking documentation: https://www.postgresql.org/docs/current/explicit-locking.html). OWASP requires tokens to be random, stored securely, scoped to a single user, expiring, and invalidated after use, which these atomic consumption patterns enforce at the database level (OWASP Forgot Password Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/Forgot_Password_Cheat_Sheet.html).

Why atomic consumption matters for single use tokens

A magic link or password reset token is only safe when the system prevents multiple consumers from using the same token. Without atomic consumption, two concurrent requests that validate the same token can both create sessions or change credentials. This is a classic race between read and write operations. Postgres transaction isolation and explicit row locks control that race by making updates or locks visible only at commit and by preventing conflicting modifications while a transaction is open (see PostgreSQL transaction isolation documentation: https://www.postgresql.org/docs/current/transaction-iso.html and PostgreSQL explicit locking documentation: https://www.postgresql.org/docs/current/explicit-locking.html).

Two safe Postgres patterns for atomic consumption

Pattern A: Conditional UPDATE with RETURNING. This is typically the simplest and most efficient approach. In a single SQL statement you check that the token exists, is not used, and is not expired, then mark it used and return the row. The UPDATE is atomic so only one concurrent UPDATE can match and be returned.

-- SQL pattern: conditional update consuming a token
UPDATE magic_links
SET used_at = now(), consumed_by = $2
WHERE token_hash = $1
  AND used_at IS NULL
  AND expires_at > now()
RETURNING id, user_id;

Pattern B: SELECT ... FOR UPDATE then UPDATE. If you need to read several columns and perform application logic before deciding to consume, acquire a row lock with FOR UPDATE inside a transaction, verify conditions in application code, then UPDATE to mark consumed and COMMIT. Use this when multiple statements are unavoidable. Remember that locks are released at transaction end, so keep the transaction short (PostgreSQL explicit locking documentation: https://www.postgresql.org/docs/current/explicit-locking.html).

-- SQL pattern: lock then consume
BEGIN;
SELECT id, user_id, expires_at, used_at
FROM magic_links
WHERE token_hash = $1
FOR UPDATE;
-- application verifies expires_at and used_at locally
UPDATE magic_links
SET used_at = now(), consumed_by = $2
WHERE id = $3 AND used_at IS NULL;
COMMIT;

Why prefer conditional UPDATE when possible

The conditional UPDATE with RETURNING keeps the work in the database engine and avoids long held locks. It is resilient to concurrent requests because only one UPDATE will match the WHERE clause and return a row. If the UPDATE returns no rows, the token was already consumed or expired. This approach also reduces the window where a connection holds a lock, minimizing contention. These behaviors follow from Postgres update and locking semantics described in the transaction isolation and explicit locking documentation (https://www.postgresql.org/docs/current/transaction-iso.html, https://www.postgresql.org/docs/current/explicit-locking.html).

Node.js example using a conditional UPDATE pattern

// Node.js example using a Postgres client
// Parameters: tokenHash (hex or binary), actorId (who consumes it)
await client.query('BEGIN');
const res = await client.query(
  `UPDATE magic_links
   SET used_at = now(), consumed_by = $2
   WHERE token_hash = $1
     AND used_at IS NULL
     AND expires_at > now()
   RETURNING id, user_id`,
  [tokenHash, actorId]
);
if (res.rowCount === 0) {
  await client.query('ROLLBACK');
  throw new Error('token invalid or already used');
}
const { id, user_id } = res.rows[0];
// create session or perform followup actions, then commit
await client.query('COMMIT');

This code uses a single conditional UPDATE to both claim and verify the token. If no row is returned, the token was invalid, expired, or already consumed. Keep followup work short or perform postcommit work asynchronously to avoid lengthening transactional locks.

Operational and security notes (OWASP)

OWASP lists essential token properties you must enforce: make tokens cryptographically random, store only a hash in the database, tie tokens to a single user or context, expire tokens, and invalidate them after successful use. A database level atomic consume ensures the last item is enforced even under heavy concurrency (OWASP Forgot Password Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/Forgot_Password_Cheat_Sheet.html). Do not log raw tokens and ensure transport security when sending links.

• Store only token hashes in the magic_links table, not raw tokens.
• Keep expiry short and enforce it in the WHERE clause of the conditional UPDATE.
• Record a used_at timestamp and optional consumer id for auditing, set by a single atomic statement.
• Avoid long transactions and avoid performing external I O while a lock is held.

Recommendation for Own Auth users and implementers

If you are using the Own Auth package, prefer the built in auth.verifyMagicLink API to implement magic link flows whenever it meets your needs. Own Auth stores magic link tokens as expiring, single use tokens and exposes a verification method so you do not have to implement token tables and locking yourself. If you maintain your own token table or you extend Own Auth at the storage layer, implement one of the Postgres patterns above to consume tokens atomically. This preserves the single use property and aligns with OWASP recommendations (OWASP Forgot Password Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/Forgot_Password_Cheat_Sheet.html).

Finally, test concurrent use cases during development. Simulate multiple parallel requests attempting to redeem the same token and assert that at most one request succeeds. Validate that locks are short lived and that your instrumentation captures failed redeem attempts for anomaly detection. For low latency production systems prefer the single statement conditional UPDATE pattern because it minimizes lock duration and contention (see PostgreSQL transaction isolation documentation: https://www.postgresql.org/docs/current/transaction-iso.html and PostgreSQL explicit locking documentation: https://www.postgresql.org/docs/current/explicit-locking.html).