-- SMS-based multi-factor authentication (see apps/demo/lib/mfa).

-- One enrollment per user: the phone their codes are texted to. verified flips
-- only once a code sent to that phone has been confirmed, so an unconfirmed phone
-- never counts as a second factor.
CREATE TABLE mfa_enrollments (
    user_id  TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    phone    TEXT NOT NULL,
    verified INTEGER NOT NULL DEFAULT 0
) STRICT;

-- Short-lived login/enrollment challenges. The code is stored only as a SHA-256
-- hash; attempts caps brute force (see mfa.MaxAttempts). id is the opaque handle
-- the client echoes back — it is not a secret, the texted code is.
CREATE TABLE mfa_challenges (
    id         TEXT PRIMARY KEY,
    user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    code_hash  TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    attempts   INTEGER NOT NULL DEFAULT 0
) STRICT;

-- Per-tenant MFA requirement. A row exists only for tenants that have set a
-- policy; absence means not required.
CREATE TABLE tenant_mfa_policy (
    tenant   TEXT PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
    required INTEGER NOT NULL DEFAULT 0
) STRICT;