sql
CREATE TABLE IF NOT EXISTS totp_users (
    user_id VARCHAR(128) NOT NULL,
    PRIMARY KEY (user_id)
);

CREATE TABLE IF NOT EXISTS totp_user_devices (
    user_id VARCHAR(128) NOT NULL,
    device_name VARCHAR(256) NOT NULL,
    secret_key VARCHAR(256) NOT NULL,
    period INTEGER NOT NULL,
    skew INTEGER NOT NULL,
    verified BOOLEAN NOT NULL,
    PRIMARY KEY (user_id, device_name),
    FOREIGN KEY (user_id) REFERENCES totp_users(user_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS totp_used_codes (
    user_id VARCHAR(128) NOT NULL,
    code VARCHAR(8) NOT NULL,
    is_valid BOOLEAN NOT NULL,
    expiry_time_ms BIGINT UNSIGNED NOT NULL,
    created_time_ms BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, created_time_ms),
    FOREIGN KEY (user_id) REFERENCES totp_users(user_id) ON DELETE CASCADE
);

CREATE INDEX totp_used_codes_expiry_time_ms_index ON totp_used_codes(expiry_time_ms);