Storing the verification phone as a keyed HMAC made OTP lookups independent
of the encryption mode, but the hash is one-way — and customer service reads
those same rows to chase people who requested a code and never finished
registering. That workflow would have lost the number entirely.
The verification tables now carry both forms: phone_number holds the lookup
key, and a new phone_enc column holds the encrypted number, which is
decryptable when a human needs to call.
The two follow-up queries also compared the verification row against the
driver/passengers tables and the notes tables by matching ciphertext, which
only ever worked because encryption was deterministic. Under GCM every number
would have looked unregistered and every note would have disappeared. Both now
read the number from phone_enc and match on normalised plaintext, so they are
correct under either mode.
Rows written before phone_enc existed are skipped rather than shown without a
number.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
