What causes "keystore: query pending otp: %w" in OpenNHP/opennhp?

Inspect the wrapped cause for the SQLite error code.

keystore: query pending otp

Error message

keystore: query pending otp: %w

What it means

After a code mismatch, ValidateOTP loads the most recent pending OTP row to count the failed attempt; any error other than ErrNoRows on that SELECT is wrapped as 'keystore: query pending otp'. ErrNoRows is translated to the sentinel ErrOTPInvalid, so this wrapper always indicates a genuine DB failure.

Solutions

Inspect the wrapped cause for the SQLite error code.
If Scan mismatch, align the SELECT list with the four scan destinations or use sql.Null* types.
Check WAL/-shm file writability and single-writer ownership.
Use PRAGMA integrity_check and restore from backup if corruption is confirmed.

Example fix

// before
err = s.db.QueryRow(`SELECT id, expires_at, used, attempts FROM otp_records ...`, userId, deviceId).Scan(&id, &expiresAt, &used, &attempts)
// after
// keep destinations in sync with the SELECT list; use nullable wrappers if columns may be NULL
var id int64; var expiresAt int64; var used bool; var attempts int
err = s.db.QueryRow(`SELECT id, expires_at, used, attempts FROM otp_records WHERE ...`, userId, deviceId).Scan(&id, &expiresAt, &used, &attempts)

Defensive patterns

Strategy: try-catch

Try / catch

if err != nil && strings.Contains(err.Error(), "query pending otp") {
    log.Error("keystore infra failure: %v", err)
    return http.StatusServiceUnavailable
}

Prevention

Keep Scan destinations synchronized with SELECT lists
Use sql.Null* types for nullable columns
Maintain single-writer ownership of the db file

When it happens

Trigger: SELECT id, expires_at, used, attempts ... fails due to SQLITE_BUSY, corruption, or Scan column/type mismatch (e.g. NULL in a non-nullable-scanned column).

Common situations: External schema edits adding NULLable columns to the SELECT list, crash-corrupted databases, heavy write contention during OTP validation storms.

Understand the failure class

Background: Database query failed: Internal Server Error 500s wrapping SQL, Prisma, and connection failures — what to check first — this error's family across 16 libraries.

Related errors

AI-assisted analysis of OpenNHP/opennhp@6e04ca5ff0 (2026-09-07). Data as JSON: /api/errors/9428ba5df489b9b6. Report an issue: GitHub.

Appendix: source

Thrown at endpoints/server/keystore.go:320

	}

	if err != sql.ErrNoRows {
		return fmt.Errorf("keystore: query otp: %w", err)
	}

	// Code did not match — track the failed attempt on the most recent
	// pending (unused, unexpired) OTP for this user+device.
	err = s.db.QueryRow(
		`SELECT id, expires_at, used, attempts FROM otp_records
		 WHERE usr_id = ? AND dev_id = ? AND used = 0
		 ORDER BY created_at DESC LIMIT 1`,
		userId, deviceId,
	).Scan(&id, &expiresAt, &used, &attempts)
	if err == sql.ErrNoRows {
		return common.ErrOTPInvalid
	}
	if err != nil {
		return fmt.Errorf("keystore: query pending otp: %w", err)
	}

	if time.Now().Unix() > expiresAt {
		return common.ErrOTPExpired
	}

	// Increment failed-attempt counter.
	attempts++
	if attempts >= MaxOTPAttempts {
		// Too many attempts — invalidate the OTP.
		_, _ = s.db.Exec(`UPDATE otp_records SET used = 1, attempts = ? WHERE id = ?`, attempts, id)
		log.Warning("keystore: otp rate-limited for user=%s device=%s after %d attempts", userId, deviceId, attempts)
		return common.ErrOTPRateLimited
	}

	_, err = s.db.Exec(`UPDATE otp_records SET attempts = ? WHERE id = ?`, attempts, id)
	if err != nil {
		log.Error("keystore: update otp attempts: %v", err)

View on GitHub (pinned to 6e04ca5ff0)