-- ===========================================================================
-- Migration: WebAuthn / passkey (fingerprint) sign-in
-- Date: 2026-07-29
--
-- Adds an OPTIONAL biometric sign-in layer on top of the existing email +
-- password login. A user enrols a device (fingerprint / Face ID / Windows
-- Hello) while signed in; the device holds the private key and the server
-- stores only the public key. Password login is untouched and remains the
-- fallback for a new device or a browser without support. Idempotent.
--
--   webauthn_credentials  — one row per enrolled authenticator (public key).
--   webauthn_challenges   — short-lived server challenges bridging the two
--                           round-trips of a register/login ceremony.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS webauthn_credentials (
  id            INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  user_id       INT UNSIGNED  NOT NULL,
  -- the credential id the authenticator returns, base64url-encoded. Globally
  -- unique so an assertion can be resolved straight to its owner (this is what
  -- makes "just tap your finger" discoverable sign-in work without an email).
  credential_id VARCHAR(512)  NOT NULL,
  -- reconstructed SubjectPublicKeyInfo, PEM-encoded (EC P-256 or RSA).
  public_key    TEXT          NOT NULL,
  -- the authenticator's signature counter; a non-increasing value on a later
  -- assertion can signal a cloned authenticator (many passkeys report 0 always).
  sign_count    INT UNSIGNED  NOT NULL DEFAULT 0,
  -- friendly name the user gives the device ("Chrome on Windows", "my phone").
  label         VARCHAR(120)  NULL,
  transports    VARCHAR(191)  NULL,             -- e.g. "internal", "hybrid"
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at  TIMESTAMP     NULL,
  UNIQUE KEY uq_webauthn_cred (credential_id(191)),
  KEY idx_webauthn_user (user_id),
  CONSTRAINT fk_webauthn_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS webauthn_challenges (
  id          INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  -- opaque handle returned to the browser and passed back on verify, so we can
  -- find the exact challenge we issued for this ceremony.
  handle      CHAR(64)      NOT NULL,
  challenge   VARCHAR(255)  NOT NULL,           -- base64url of the random challenge
  purpose     ENUM('register','login') NOT NULL,
  -- for a register ceremony, the signed-in user it belongs to; NULL for login
  -- (the user isn't known until the credential is resolved).
  user_id     INT UNSIGNED  NULL,
  expires_at  TIMESTAMP     NOT NULL,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_webauthn_handle (handle),
  KEY idx_webauthn_expires (expires_at)
) ENGINE=InnoDB;