2FA sign-in fails: better-auth 1.6.30 lockout writes columns two_factor doesn't declare

[CraigSteven]

Environment: ghcr.io/quackbackio/quackback:main, self-hosted via docker-compose.prod.yml, single workspace, Postgres 17, better-auth 1.6.30. The failure is server-side.

Actual: POST /api/auth/two-factor/verify-totp returns 500, then 401 on retry, for a valid code. Every user with 2FA enabled is locked out, and enrolment fails the same way so it cannot be cleared in-product.

Expected: a valid TOTP code completes sign-in.

Failed query: update "two_factor" set  where "two_factor"."id" = $1
  returning "id", "user_id", "secret", "backup_codes", "verified", "created_at"

Cause

better-auth 1.6.30's twoFactor plugin added an account-lockout feature, enabled unless explicitly disabled (plugins/two-factor/verify-two-factor.mjs: enabled: lockout?.enabled ?? true). The plugin declares the two fields it needs in its own schema export (plugins/two-factor/schema.mjs):

failedVerificationCount: { type: "number", required: false, defaultValue: 0, input: false, returned: false },
lockedUntil:             { type: "date",   required: false,                   input: false, returned: false }

Quackback's two_factor table (packages/db/src/schema/auth.ts:221) declares neither, so the Drizzle table object has no matching keys. Drizzle drops unknown keys from .set() rather than erroring, which produces the empty SET above.

Both verification paths write these fields — plugins/two-factor/totp/index.mjs:201 on failure via recordTwoFactorFailure, and :204 on success via resetTwoFactorFailures, unconditionally. Because the success path writes too, a correct code fails exactly like a wrong one.

Reproduce

1. Deploy ghcr.io/quackbackio/quackback:main.
2. Enable 2FA for a user, or use an account that already has it.
3. Sign out, then sign in and enter a valid TOTP code.
4. First attempt returns 500 with the query above; further attempts return 401.

The generated SQL reproduces with Drizzle alone, no Quackback or better-auth involved:

import { pgTable, text, uuid, boolean } from 'drizzle-orm/pg-core'
import { drizzle } from 'drizzle-orm/node-postgres'
import { eq } from 'drizzle-orm'

const twoFactor = pgTable('two_factor', {
  id: uuid('id').primaryKey(),
  userId: uuid('user_id').notNull(),
  secret: text('secret').notNull(),
  backupCodes: text('backup_codes').notNull(),
  verified: boolean('verified').notNull(),
})

const db = drizzle({ client: { query: async () => ({ rows: [] }) } })
console.log(db.update(twoFactor)
  .set({ failedVerificationCount: 0, lockedUntil: null })
  .where(eq(twoFactor.id, '00000000-0000-0000-0000-000000000000'))
  .toSQL().sql)
// → update "two_factor" set  where "two_factor"."id" = $1

Adding the columns in Postgres alone does not fix it — the mapping comes from the schema in code.

Version exposure

1.6.30 was pinned in #380 (2026-08-19), after v0.13.2. That PR's diff touches only apps/web/package.json and the root package.json: the bump carried no schema change or migration.

Note v0.13.2 declares "better-auth": "^1.6.16", so a release build resolving that caret past 1.6.30 would hit this too — this may not be main-only in practice.

Columns absent on main (0204d1e), fix/messenger-defaults-followup, feat/simpler-messenger-defaults and saas.

Workaround for anyone locked out

UPDATE "user" SET two_factor_enabled = false WHERE two_factor_enabled = true;
-- also needed if the workspace requires 2FA, else sign-in forces enrolment
-- and fails with "Could not start 2FA setup":
UPDATE settings SET auth_config = jsonb_set(coalesce(auth_config,'{}')::jsonb,
  '{twoFactor}', coalesce(auth_config::jsonb->'twoFactor','{}'::jsonb)
  || '{"required":false}'::jsonb, true)::text;

then clear the cached settings:tenant key and restart. Existing TOTP secrets are untouched, so 2FA can be switched back on once fixed.

Fix

Add the columns the plugin declares, matching its types and defaults:

failedVerificationCount: integer('failed_verification_count').notNull().default(0),
lockedUntil: timestamp('locked_until', { withTimezone: true }),

plus a migration. The alternative, twoFactor({ accountLockout: { enabled: false } }), avoids the schema change but gives up brute-force protection.

Suggested regression test

Existing 2FA coverage is component-level (two-factor-enroll-steps, portal-auth-form-inline.two-factor, two-factor-under-password) plus a lifecycle-audit test — none execute better-auth's own database writes, so the adapter path is untested. An integration test driving the real endpoint against a migrated test database would have caught this:

it('accepts a valid TOTP code at sign-in', async () => {
  // enrol a user, generate a live code from the stored secret
  const res = await auth.api.verifyTOTP({ body: { code: totp(secret) }, headers })
  expect(res.status).toBe(200)
})

The happy path alone is sufficient, since the failing write is in resetTwoFactorFailures on success — no need to simulate lockout.

For the general case: because each plugin exports a machine-readable schema, a CI check can diff the declared fields against the Drizzle tables and fail when a dependency bump introduces new ones. That would catch the next occurrence at build time rather than at sign-in. Possibly a natural extension of packages/db/scripts/check-drift.ts, and related in spirit to #341.

[mortondev]

Fixed on main.

better-auth 1.6.30 writes failed_verification_count and locked_until on every TOTP verify (success resets, failure increments). Our two_factor table never declared those columns, so Drizzle dropped the unknown keys and emitted update "two_factor" set where …. Enrolment and sign-in both 500'd on a valid code.

• Schema + migration 0278_two_factor_lockout: fix(auth): persist better-auth 2FA lockout columns #536 (bcd4e6b76)
• Fleet replay-gate list + CONTRACT.md scan count: fix(fleet): include 0278 in the post-0248 replay-gate span #537 (8c552b675)

To pick this up: run migration 0278 (or upgrade to a build that includes it). Existing TOTP secrets are left in place, so you can switch 2FA back on after the columns exist — the SQL workaround in the issue body is only needed until then.

Self-hosted: bun run db:migrate / image startup migrator. Cloud: this lands when the fleet rolls a digest that includes 0278.