Custom Persistent Stores

Custom Persistent Stores#

Overview#

The package ships with in-memory examples for replay tracking and recovery-code tracking, but production systems usually need durable storage.

This guide shows how to build your own database-backed classes using the package contracts.

Contracts#

The relevant contracts are:

Infocyph\OTP\Contracts\RecoveryCodeStoreInterface
Infocyph\OTP\Contracts\ReplayStoreInterface
Infocyph\OTP\Contracts\SecretStoreInterface

Secret storage guidance#

For TOTP, HOTP, and many OCRA deployments, the application must be able to read the secret again later to generate or verify future OTPs.

That means:

hashing alone is not enough for OTP secrets
encryption at rest is usually the correct default
the stored record should have a stable reference or key id
rotation should create a new secret record rather than overwrite history blindly

Recovery code tracking in a database#

Recommended schema#

One practical design is a batch table plus per-code rows.

Example schema:

CREATE TABLE recovery_code_batches (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    binding VARCHAR(190) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    revoked_at TIMESTAMP NULL
);

CREATE INDEX idx_recovery_code_batches_binding
    ON recovery_code_batches (binding);

CREATE TABLE recovery_codes (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    batch_id BIGINT NOT NULL,
    code_hash VARCHAR(255) NOT NULL,
    used_at TIMESTAMP NULL,
    CONSTRAINT fk_recovery_codes_batch
        FOREIGN KEY (batch_id) REFERENCES recovery_code_batches (id)
);

CREATE INDEX idx_recovery_codes_batch
    ON recovery_codes (batch_id);

CREATE INDEX idx_recovery_codes_hash
    ON recovery_codes (code_hash);

How tracking works#

If a user has 6 recovery codes:

total is the number originally issued in the active batch
remaining is the number of rows where used_at IS NULL
used is total - remaining

That means your system can answer, day to day:

how many codes were issued
how many are still unused
when a code was last used

Example PDO store#

<?php

declare(strict_types=1);

use DateTimeImmutable;
use DateTimeZone;
use Infocyph\OTP\Contracts\RecoveryCodeStoreInterface;
use PDO;
use RuntimeException;

final readonly class PdoRecoveryCodeStore implements RecoveryCodeStoreInterface
{
    public function __construct(
        private PDO $pdo,
    ) {}

    public function replace(string $binding, array $hashedCodes, DateTimeImmutable $issuedAt): void
    {
        $this->pdo->beginTransaction();

        try {
            $statement = $this->pdo->prepare(
                'UPDATE recovery_code_batches
                 SET revoked_at = :revoked_at
                 WHERE binding = :binding AND revoked_at IS NULL'
            );

            $statement->execute([
                'binding' => $binding,
                'revoked_at' => $issuedAt->format('Y-m-d H:i:s'),
            ]);

            $statement = $this->pdo->prepare(
                'INSERT INTO recovery_code_batches (binding, created_at, revoked_at)
                 VALUES (:binding, :created_at, NULL)'
            );

            $statement->execute([
                'binding' => $binding,
                'created_at' => $issuedAt->format('Y-m-d H:i:s'),
            ]);

            $batchId = (int) $this->pdo->lastInsertId();
            if ($batchId < 1) {
                throw new RuntimeException('Unable to create recovery code batch.');
            }

            $insert = $this->pdo->prepare(
                'INSERT INTO recovery_codes (batch_id, code_hash, used_at)
                 VALUES (:batch_id, :code_hash, NULL)'
            );

            foreach ($hashedCodes as $hash) {
                $insert->execute([
                    'batch_id' => $batchId,
                    'code_hash' => $hash,
                ]);
            }

            $this->pdo->commit();
        } catch (\Throwable $exception) {
            $this->pdo->rollBack();
            throw $exception;
        }
    }

    public function consume(string $binding, string $hashedCode, DateTimeImmutable $usedAt): bool
    {
        $statement = $this->pdo->prepare(
            'UPDATE recovery_codes
             SET used_at = :used_at
             WHERE id = (
                 SELECT rc.id
                 FROM recovery_codes rc
                 INNER JOIN recovery_code_batches rcb ON rcb.id = rc.batch_id
                 WHERE rcb.binding = :binding
                   AND rcb.revoked_at IS NULL
                   AND rc.code_hash = :code_hash
                   AND rc.used_at IS NULL
                 FETCH FIRST 1 ROWS ONLY
             )'
        );

        $statement->execute([
            'binding' => $binding,
            'code_hash' => $hashedCode,
            'used_at' => $usedAt->format('Y-m-d H:i:s'),
        ]);

        return $statement->rowCount() === 1;
    }

    public function metadata(string $binding): array
    {
        $statement = $this->pdo->prepare(
            'SELECT
                 COUNT(rc.id) AS total,
                 SUM(CASE WHEN rc.used_at IS NULL THEN 1 ELSE 0 END) AS remaining,
                 MAX(rc.used_at) AS last_used_at
             FROM recovery_code_batches rcb
             LEFT JOIN recovery_codes rc ON rc.batch_id = rcb.id
             WHERE rcb.binding = :binding
               AND rcb.revoked_at IS NULL'
        );

        $statement->execute(['binding' => $binding]);
        $row = $statement->fetch(PDO::FETCH_ASSOC);

        if (!is_array($row) || $row['total'] === null) {
            return ['total' => 0, 'remaining' => 0, 'lastUsedAt' => null];
        }

        return [
            'total' => (int) $row['total'],
            'remaining' => (int) $row['remaining'],
            'lastUsedAt' => $row['last_used_at'] !== null
                ? new DateTimeImmutable((string) $row['last_used_at'], new DateTimeZone('UTC'))
                : null,
        ];
    }
}

Using the store#

<?php
use Infocyph\OTP\RecoveryCodes;

$store = new PdoRecoveryCodeStore($pdo);
$codes = new RecoveryCodes($store);

$generated = $codes->generate('user-42');
$result = $codes->consume('user-42', $generated->plainCodes[0]);

Replay tracking in a database#

Recommended schema#

One simple design uses two tables:

one for current replay state
one for consumed tokens

Example schema:

CREATE TABLE otp_replay_state (
    namespace VARCHAR(100) NOT NULL,
    binding VARCHAR(190) NOT NULL,
    state_value VARCHAR(255) NULL,
    updated_at TIMESTAMP NOT NULL,
    PRIMARY KEY (namespace, binding)
);

CREATE TABLE otp_replay_consumed (
    namespace VARCHAR(100) NOT NULL,
    binding VARCHAR(190) NOT NULL,
    token VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NULL,
    PRIMARY KEY (namespace, binding, token)
);

Example PDO store#

<?php

declare(strict_types=1);

use DateTimeImmutable;
use DateTimeZone;
use Infocyph\OTP\Contracts\ReplayStoreInterface;
use PDO;

final readonly class PdoReplayStore implements ReplayStoreInterface
{
    public function __construct(
        private PDO $pdo,
    ) {}

    public function hasConsumed(string $namespace, string $binding, string $token): bool
    {
        $statement = $this->pdo->prepare(
            'SELECT expires_at
             FROM otp_replay_consumed
             WHERE namespace = :namespace
               AND binding = :binding
               AND token = :token'
        );

        $statement->execute([
            'namespace' => $namespace,
            'binding' => $binding,
            'token' => $token,
        ]);

        $row = $statement->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) {
            return false;
        }

        if ($row['expires_at'] !== null) {
            $expiresAt = new DateTimeImmutable((string) $row['expires_at'], new DateTimeZone('UTC'));
            if ($expiresAt <= new DateTimeImmutable('now', new DateTimeZone('UTC'))) {
                return false;
            }
        }

        return true;
    }

    public function markConsumed(string $namespace, string $binding, string $token, ?int $ttl = null): void
    {
        $expiresAt = $ttl !== null
            ? (new DateTimeImmutable('now', new DateTimeZone('UTC')))->modify("+{$ttl} seconds")->format('Y-m-d H:i:s')
            : null;

        $statement = $this->pdo->prepare(
            'INSERT INTO otp_replay_consumed (namespace, binding, token, expires_at)
             VALUES (:namespace, :binding, :token, :expires_at)'
        );

        $statement->execute([
            'namespace' => $namespace,
            'binding' => $binding,
            'token' => $token,
            'expires_at' => $expiresAt,
        ]);
    }

    public function getState(string $namespace, string $binding): int|string|null
    {
        $statement = $this->pdo->prepare(
            'SELECT state_value
             FROM otp_replay_state
             WHERE namespace = :namespace
               AND binding = :binding'
        );

        $statement->execute([
            'namespace' => $namespace,
            'binding' => $binding,
        ]);

        $row = $statement->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row) || $row['state_value'] === null) {
            return null;
        }

        $value = (string) $row['state_value'];

        return ctype_digit($value) ? (int) $value : $value;
    }

    public function setState(string $namespace, string $binding, int|string|null $value, ?int $ttl = null): void
    {
        $statement = $this->pdo->prepare(
            'MERGE INTO otp_replay_state AS target
             USING (SELECT :namespace AS namespace, :binding AS binding) AS source
             ON (target.namespace = source.namespace AND target.binding = source.binding)
             WHEN MATCHED THEN
                 UPDATE SET state_value = :state_value, updated_at = :updated_at
             WHEN NOT MATCHED THEN
                 INSERT (namespace, binding, state_value, updated_at)
                 VALUES (:namespace, :binding, :state_value, :updated_at);'
        );

        $statement->execute([
            'namespace' => $namespace,
            'binding' => $binding,
            'state_value' => $value !== null ? (string) $value : null,
            'updated_at' => (new DateTimeImmutable('now', new DateTimeZone('UTC')))->format('Y-m-d H:i:s'),
        ]);
    }
}

Using the replay store#

<?php
use Infocyph\OTP\Stores\InMemoryReplayStore;
use Infocyph\OTP\TOTP;
use Infocyph\OTP\ValueObjects\VerificationWindow;

$totp = new TOTP($secret);
$store = new PdoReplayStore($pdo);

$result = $totp->verifyWithWindow(
    $submittedOtp,
    window: new VerificationWindow(past: 1, future: 1),
    replayStore: $store,
    binding: 'user-42',
);

Notes#

The SQL above is illustrative. You may need to adapt syntax for PostgreSQL, MySQL, SQLite or SQL Server.
Recovery code consumption should be atomic to prevent double-use under concurrency.
Replay stores should apply indexes on namespace, binding and token.
Recovery code hashes should be treated as sensitive authentication data even though they are hashed.

Custom Persistent Stores

Contents

Custom Persistent Stores#

Overview#

Contracts#

Secret storage guidance#

Recovery code tracking in a database#

Recommended schema#

How tracking works#

Example PDO store#

Using the store#

Replay tracking in a database#

Recommended schema#

Example PDO store#

Using the replay store#

Notes#