<div style="display: none;" hidden="true" aria-hidden="true">Are you an LLM? You can read better optimized documentation at /guides/RestCustomerLoginRecovery.md for this page in Markdown format</div>

REST Customer Login Recovery Runbook (#463)

Diagnostic runbook for a customer who is stranded on POST /rest/v1/auth/customer/login: correct credentials rejected, password resets don't help, and the endpoint returns a malformed empty 200 (text/html, 0 bytes) instead of tokens — while a nonexistent email still gets a clean 401. First observed for jdellis@advisable.com (origin incident: Advisable-com/velora-wecare#16).

Scope. This is the in-repo half of #463. The concrete root cause for a specific stranded account can only be confirmed against production data, which this runbook does not touch. It gives a human with prod DB access the exact read-only queries to (A) confirm which corruption class applies to the affected account and (B) scan for other affected accounts, plus remediation proposals gated on that confirmation. It does not assert a root cause and does not contain any executable data mutation.

---

1. Success-path recap (what the login actually does)

POST /rest/v1/auth/customer/login
  → CustomerAuth::login()                         src/Rest/Auth/CustomerAuth.php:66
  → Advauth::statelessCustomerLogin()             application/libraries/Advauth.php:143
  → Adv_customer_model::checkCustomer()           ecommercen/eshop/models/Adv_customer_model.php:307
        SELECT ... FROM shop_customer WHERE mail=? AND is_guest=0     (per-account data)
        → checkPassword()                         ecommercen/eshop/models/Adv_customer_model.php:386
  → on success: Tokens::generateAccessToken()  +  generateRefreshToken()   src/Rest/Auth/Tokens.php:14,39
        generateRefreshToken(): DELETE then INSERT into refresh_tokens for (user_id,user_type)
                                                  src/Rest/Auth/RefreshTokenModel.php:9,28
  → sendOutput({access_token, refresh_token})

Key facts:

• A nonexistent email returning a clean 401 proves the failure path is healthy; the fault is on the authenticated-success path. (That a nonexistent email gets 401 does not by itself prove the fault is account-specific — a deployment-wide fault such as a missing refresh_tokens table would also leave the 401 path healthy while breaking every valid login; see class 5.)
• An empty 200 with Content-Type: text/html and 0 bytes is the signature of an uncaught \Throwable swallowed by the dispatcher after PHP's default 200 status line was emitted but before sendOutput() ran — a PHP Error/Exception on the success path (a failed random_bytes(), a null-method call, a mysqli exception when the driver is in a throwing mode). It is not a CI3 show_error() halt (that emits an HTML error page, not 0 bytes) and, as of #498, it is also not a plain refresh_tokens write error: RefreshTokenModel::save()/::delete() now throw a \RuntimeException on a real DB error (a zero-row DELETE is not an error), and that throw is caught by #462's SafeActionDispatch and converted into a well-formed 500 JSON — not the empty 200. Empirically verified (tests/Integration/Legacy/Eshop/RestCustomerLoginEmpty200Test.php): a missing/un-migrated refresh_tokens table now yields a 500 JSON with no refresh_token in the body, not the old misleading 200 carrying an unpersisted token. Static analysis of the code alone (see #463) found no deterministic single-account fatal, so the empty-200 trigger still lives in the account's data/environment and must be something that raises a \Throwable outside the refresh_tokens write path.
• The generic "never emit a bare empty 200 on the success path — surface a structured JSON error instead" hardening shipped in #462, which converts a success-path \Throwable into a 500 JSON. #498 closed the one gap it didn't cover on its own: a silently swallowed refresh_tokens write failure now throws too, so it is likewise converted into a 500 by #462 instead of a phantom 200.

---

2. Safety notes (read this before running anything)

• Every query in Part A and Part B is a read-only SELECT/SHOW. None modify data. They are safe to run on production (prefer a read replica).
• Replace the @mail placeholder with the affected address. Examples use jdellis@advisable.com.
• If your session variables aren't available, inline the literal in place of @mail.
• Do not run any remediation from §5 until §4 has identified the class and a second engineer has reviewed the affected rows. §5 is proposals only.

-- Optional: set once, reused by the Part A queries below.
SET @mail = 'jdellis@advisable.com';

---

3. Candidate corruption classes (what we are testing for)

#	Class	Where it bites	Symptom it explains
1	Duplicate non-guest shop_customer rows for the email	checkCustomer historically read only the first row (->row(), no ORDER BY)	Correct credentials rejected (wrong row was authoritative)
2	NULL / garbage salt or password on the account's row	checkPassword hashing / fallback	Correct credentials rejected; possible null-arg deprecation under a strict handler
3	Poisoned / excess refresh_tokens rows for that user_id	generateRefreshToken DELETE→INSERT	Fails loud → 500 (as of #498, via #462) — no longer a phantom 200 with an unpersisted token
4	INSERT hits a read-only replica / write-blocked connection	generateRefreshToken INSERT	Same as class 3: fails loud → 500 (as of #498, via #462), no longer a misleading 200
5	refresh_tokens table absent — migration 20250716104205 (or 20260309131750) not applied on this deployment (client migration-drift)	generateRefreshToken DELETE/INSERT against a missing table	Fails loud → 500 (as of #498, via #462) for every REST login on that deployment (fleet-wide, not account-specific). See A5

Correction (empirically verified — see the #463 comment + RestCustomerLoginEmpty200Test). An earlier draft of this runbook attributed the empty 200 to a class-3/4 refresh_tokens write error. That was wrong: the empty 200 requires an actual \Throwable on the success path (the mode #462 converts to a 500). A plain refresh_tokens write error under db_debug=false (classes 3/4/5) used to be swallowed and produce a misleading 200 with a phantom token — that gap is fixed in #498: RefreshTokenModel::save()/::delete() now throw on a real DB error, and #462 converts that throw into a 500 too, so neither failure mode produces the empty-200 signature and neither is a silent phantom 200 any more. The in-repo guards (§6) reduce the blast radius of classes 1 and 2; classes 3/4/5 still need Part A to identify the underlying cause (poisoned tokens, a write-blocked connection, or an un-migrated table) — only the failure mode changed, from a silent phantom 200 to a loud 500 — remediation is still the §7 proposals / ops.

---

4. Part A — Confirm the class for the affected account

A1. Duplicate rows (class 1)

-- READ-ONLY / DIAGNOSTIC
-- Every row for the email, guest and non-guest, oldest id first.
-- Expect exactly ONE row with is_guest=0. More than one non-guest row = class 1.
SELECT id, mail, is_guest,
       (salt IS NULL)               AS salt_is_null,
       (salt = '')                  AS salt_is_empty,
       (password IS NULL)           AS password_is_null,
       (password = '')              AS password_is_empty,
       CHAR_LENGTH(password)        AS password_len,
       created_at, updated_at
FROM shop_customer
WHERE mail = @mail
ORDER BY is_guest ASC, id ASC;

Interpretation: two or more rows with is_guest = 0 confirms class 1. Note which row carries the valid-looking hash (password_len ~ 40 for the salted sha1, or 32 for a legacy md5) — that is the credential-owning row.

A2. NULL / garbage salt or password (class 2)

-- READ-ONLY / DIAGNOSTIC
-- Focus on the non-guest row(s). A NULL/empty password, or a password whose
-- length is neither 40 (salted sha1) nor 32 (legacy md5), is class 2.
SELECT id, mail,
       salt, CHAR_LENGTH(salt)      AS salt_len,
       CHAR_LENGTH(password)        AS password_len,
       (password IS NULL)           AS password_is_null,
       (password = '')              AS password_is_empty
FROM shop_customer
WHERE mail = @mail AND is_guest = 0
ORDER BY id ASC;

Interpretation: password_is_null = 1, password_is_empty = 1, or a password_len other than 40/32 confirms class 2 for that row.

A3. refresh_tokens state (class 3)

-- READ-ONLY / DIAGNOSTIC
-- Resolve the customer id(s), then inspect their refresh_tokens rows.
-- user_type for a storefront customer is 'customer' (see migration
-- 20260309131750_add_user_type_to_refresh_tokens.php). user_id is the
-- shop_customer.id as a string.
SELECT rt.id, rt.user_id, rt.user_type, CHAR_LENGTH(rt.token) AS token_len,
       rt.expires_at, rt.created_at
FROM refresh_tokens rt
JOIN shop_customer c ON c.id = rt.user_id AND rt.user_type = 'customer'
WHERE c.mail = @mail
ORDER BY rt.created_at ASC;

-- READ-ONLY / DIAGNOSTIC — count per (user_id,user_type); DELETE-then-INSERT
-- means a healthy account should hold at most a small number of rows.
SELECT rt.user_id, rt.user_type, COUNT(*) AS token_rows
FROM refresh_tokens rt
JOIN shop_customer c ON c.id = rt.user_id
WHERE c.mail = @mail
GROUP BY rt.user_id, rt.user_type;

Interpretation: token_len should be 64. An oversized column value, a corrupt row, or an unexpectedly large token_rows for the customer are signals to investigate class 3. Note that the refresh_tokens schema is roomy (user_id/token = varchar(255), user_type = varchar(10); migration 20250716104205_create_refresh_tokens_table.php), so a plain length overflow is unlikely — which pushes suspicion toward class 4 if A1/A2/A3 all look clean.

A4. Write target (class 4) — NOT a SQL query on the app schema

Class 4 cannot be confirmed with a SELECT against the app DB. A human must check the connection the login request actually writes through:

-- READ-ONLY — run ON THE CONNECTION/HOST the app uses for writes.
SELECT @@global.read_only, @@global.super_read_only, @@global.hostname;
SHOW VARIABLES LIKE 'read_only';

Also confirm from ops:

• Does the storefront pool for this request route writes to a replica or a write-blocked node?
• Are there INSERT ... refresh_tokens errors in the DB/PHP error logs at the timestamps the customer reproduced the failure?

A5. Table / migration state (class 5) — check FIRST; cheapest and highest blast radius

The refresh_tokens table arrives with migration 20250716104205 (and 20260309131750 adds user_type). A deployment that shipped the REST-login code but fell behind on migrate has the code path but not the table — every REST login then fails the write (fails loud → 500, as of #498, per class 5). Confirm on the affected deployment's DB:

-- READ-ONLY / DIAGNOSTIC — run against the affected deployment's database.
SHOW TABLES LIKE 'refresh_tokens';
SELECT version, migration_name FROM migrations
WHERE version IN ('20250716104205', '20260309131750')
ORDER BY version;

Interpretation: a missing table, or either migration row absent, confirms class 5. The fix is operational — run php migrator.php migrate on that deployment (§7); no code change and no data mutation. Because this is fleet-wide, rule it out before the per-account Part-A queries.

---

5. Part B — Scan for other affected accounts

B1. Emails with more than one non-guest row (class 1 fleet scan)

-- READ-ONLY / DIAGNOSTIC
SELECT LOWER(mail) AS mail_lc, COUNT(*) AS non_guest_rows,
       GROUP_CONCAT(id ORDER BY id) AS ids
FROM shop_customer
WHERE is_guest = 0 AND mail IS NOT NULL AND mail <> ''
GROUP BY LOWER(mail)
HAVING COUNT(*) > 1
ORDER BY non_guest_rows DESC, mail_lc ASC;

B2. Non-guest rows with NULL / empty salt or password (class 2 fleet scan)

-- READ-ONLY / DIAGNOSTIC
SELECT id, mail,
       (salt IS NULL OR salt = '')          AS salt_missing,
       (password IS NULL OR password = '')  AS password_missing,
       CHAR_LENGTH(password)                AS password_len
FROM shop_customer
WHERE is_guest = 0
  AND (
        password IS NULL OR password = ''
     OR CHAR_LENGTH(password) NOT IN (32, 40)   -- not md5(32) and not salted-sha1(40)
      )
ORDER BY id ASC;

B3. refresh_tokens anomalies (class 3 fleet scan)

-- READ-ONLY / DIAGNOSTIC — customers holding an unusual number of token rows,
-- or token rows that are not the expected 64 hex chars.
SELECT user_id, user_type, COUNT(*) AS token_rows,
       SUM(CHAR_LENGTH(token) <> 64) AS bad_length_rows,
       MIN(created_at) AS first_seen, MAX(created_at) AS last_seen
FROM refresh_tokens
WHERE user_type = 'customer'
GROUP BY user_id, user_type
HAVING token_rows > 5 OR bad_length_rows > 0
ORDER BY token_rows DESC;

-- READ-ONLY / DIAGNOSTIC — orphaned customer refresh tokens (no matching customer row).
SELECT rt.id, rt.user_id, rt.user_type, rt.created_at
FROM refresh_tokens rt
LEFT JOIN shop_customer c ON c.id = rt.user_id
WHERE rt.user_type = 'customer' AND c.id IS NULL
ORDER BY rt.created_at DESC;

---

6. What the in-repo guards (this branch) already do

Shipped in this change set (see §8), reducing blast radius without prod data and without overlapping #462:

• checkCustomer is now deterministic and duplicate-tolerant — it ORDER BY id ASC and authenticates against whichever non-guest row actually owns the supplied password, instead of the arbitrary first ->row(). This directly neutralises the "correct credentials rejected" symptom of class 1 (ecommercen/eshop/models/Adv_customer_model.php:307).
• checkPassword rejects a NULL/empty stored hash before hashing — a corrupted row can never authenticate, and no null argument reaches sha1()/md5() (removing the PHP 8.1 null-to-string deprecation that a strict handler could escalate). This blunts class 2 (ecommercen/eshop/models/Adv_customer_model.php:386).

These guards are covered by tests/Legacy/Eshop/AdvCustomerModelLoginTest.php.

They do not by themselves explain the empty 200, which occurs after authentication succeeds and requires an actual \Throwable (the mode #462 catches). Classes 1/2 present as a 401, not an empty 200. Rule out the fleet-wide, cheap-to-check class 5 (A5) first; then, if the symptom is a genuine empty 200, hunt for what throws on the success path (not a plain refresh_tokens write error, which now throws and is converted to a 500 — fixed in #498 — rather than being swallowed). Confirm with Part A before concluding.

---

7. Remediation proposals (GATED — do not run until §4 confirms the class)

These are proposals, described for a human to execute deliberately after confirmation. Do not commit a blind data-mutation patcher: the root cause is unconfirmed and a mass mutation of customer or token rows is unsafe.

• Class 1 (duplicate non-guest rows). For each confirmed email, keep the credential-owning row (the one whose password matched — verify against the order history / most recent activity) and either merge or soft-remove the other. Because this touches identity, do it per account under review, not as an unattended sweep. If the fleet scan (B1) returns a bounded, reviewed set, a one-time patcher extending AbstractPatcher with a natural idempotency guard (re-check the duplicate condition per row and skip rows that no longer match) is the repo-preferred shape — but only author it once the keep/drop decision per email is human-approved.
• Class 2 (NULL/empty hash). A row with no usable hash cannot be "fixed" to a known password; the account must go through the normal password-reset flow to receive a fresh salt+hash. If reset itself is blocked for the account, resolve class 1/3/4 first. No automated backfill — you cannot invent a password.
• Class 3 (poisoned refresh_tokens). Deleting the affected customer's refresh_tokens rows is safe (they are regenerated on next login) and can be done per user_id after B3 review. This is an ops action, not a schema patcher.
• Class 4 (write target). Purely infrastructure — route the storefront write pool to a writable primary. No code change.
• Class 5 (un-migrated refresh_tokens table). Operational — run php migrator.php migrate on the affected deployment so 20250716104205 / 20260309131750 land. No code change, no data mutation. Idempotent (Phinx skips already-applied migrations). Until that migration runs, every REST login on that deployment now fails loud with a 500 (fixed in #498 — no longer a phantom 200 with an unpersisted token). After migrating, affected customers can log in and get a persisted refresh token; no per-account remediation is needed.

---

8. What only a human with prod access can do (this agent could not)

• Run Part A / Part B against the production (or replica) database and read the actual rows.
• Confirm the concrete root cause for jdellis@advisable.com — unconfirmed until then.
• Cross-check against the structured error that #462 surfaces once deployed.
• Decide and execute the §7 remediation (keep/drop duplicates, purge tokens, fix write routing).
• Verify the customer can log in again via POST /rest/v1/auth/customer/login.

Until those steps are done, #463 stays open: the code guards below narrow the failure modes but do not, on their own, prove the account is recovered.