{"record":{"id":"9428ba5df489b9b6","repo":"OpenNHP/opennhp","slug":"keystore-query-pending-otp-w","errorCode":null,"errorMessage":"keystore: query pending otp: %w","messagePattern":"keystore: query pending otp: %w","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"endpoints/server/keystore.go","lineNumber":320,"sourceCode":"\t}\n\n\tif err != sql.ErrNoRows {\n\t\treturn fmt.Errorf(\"keystore: query otp: %w\", err)\n\t}\n\n\t// Code did not match — track the failed attempt on the most recent\n\t// pending (unused, unexpired) OTP for this user+device.\n\terr = s.db.QueryRow(\n\t\t`SELECT id, expires_at, used, attempts FROM otp_records\n\t\t WHERE usr_id = ? AND dev_id = ? AND used = 0\n\t\t ORDER BY created_at DESC LIMIT 1`,\n\t\tuserId, deviceId,\n\t).Scan(&id, &expiresAt, &used, &attempts)\n\tif err == sql.ErrNoRows {\n\t\treturn common.ErrOTPInvalid\n\t}\n\tif err != nil {\n\t\treturn fmt.Errorf(\"keystore: query pending otp: %w\", err)\n\t}\n\n\tif time.Now().Unix() > expiresAt {\n\t\treturn common.ErrOTPExpired\n\t}\n\n\t// Increment failed-attempt counter.\n\tattempts++\n\tif attempts >= MaxOTPAttempts {\n\t\t// Too many attempts — invalidate the OTP.\n\t\t_, _ = s.db.Exec(`UPDATE otp_records SET used = 1, attempts = ? WHERE id = ?`, attempts, id)\n\t\tlog.Warning(\"keystore: otp rate-limited for user=%s device=%s after %d attempts\", userId, deviceId, attempts)\n\t\treturn common.ErrOTPRateLimited\n\t}\n\n\t_, err = s.db.Exec(`UPDATE otp_records SET attempts = ? WHERE id = ?`, attempts, id)\n\tif err != nil {\n\t\tlog.Error(\"keystore: update otp attempts: %v\", err)","sourceCodeStart":302,"sourceCodeEnd":338,"githubUrl":"https://github.com/OpenNHP/opennhp/blob/6e04ca5ff03222a699c24205cd4bf8fee9af7ffe/endpoints/server/keystore.go#L302-L338","documentation":"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.","triggerScenarios":"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).","commonSituations":"External schema edits adding NULLable columns to the SELECT list, crash-corrupted databases, heavy write contention during OTP validation storms.","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."],"exampleFix":"// before\nerr = s.db.QueryRow(`SELECT id, expires_at, used, attempts FROM otp_records ...`, userId, deviceId).Scan(&id, &expiresAt, &used, &attempts)\n// after\n// keep destinations in sync with the SELECT list; use nullable wrappers if columns may be NULL\nvar id int64; var expiresAt int64; var used bool; var attempts int\nerr = s.db.QueryRow(`SELECT id, expires_at, used, attempts FROM otp_records WHERE ...`, userId, deviceId).Scan(&id, &expiresAt, &used, &attempts)","handlingStrategy":"try-catch","validationCode":null,"typeGuard":null,"tryCatchPattern":"if err != nil && strings.Contains(err.Error(), \"query pending otp\") {\n    log.Error(\"keystore infra failure: %v\", err)\n    return http.StatusServiceUnavailable\n}","preventionTips":["Keep Scan destinations synchronized with SELECT lists","Use sql.Null* types for nullable columns","Maintain single-writer ownership of the db file"],"tags":["go","sqlite","query","otp"],"backgroundTag":"database-query-failed","analyzedSha":"6e04ca5ff03222a699c24205cd4bf8fee9af7ffe","analyzedAt":"2026-09-07T15:44:59.941Z","contentChangedAt":"2026-09-07T15:44:59.941Z","schemaVersion":2},"datasetVersion":"2026-09-16T04:17:20.429Z"}