Flow
How the email lookups went
1Encrypt the columns
Emails and names are stored encrypted with a new random IV each time.
2Old-style lookups
Two helpers encrypt the typed email and search for an equal value. Zero rows, no error.
3Scan instead
The next commit decrypts each row and compares lowercased values. Correct, linear in the table size.
4Forgiving decrypt
Values that will not decrypt come back unchanged, so a wrong key looks like an empty user list.
5Next time
A keyed hash column, indexed and unique, so the database can narrow the search again.
Flow
Three ways to find an encrypted email
1Encrypt and compare
Never matches, and the unique rule blocks nothing.
2Decrypt every row
Matches correctly. A missing address costs the whole table.
3Hash column plus encrypted value
One indexed lookup and a working unique rule. Costs a second key and a fixed lowercase rule.
Every lookup by email broke in the same commit where I encrypted the email column in my shift scheduler.
The scheduler plans staff shifts and breaks for the stores. I wanted emails and staff names encrypted in the database. The helpers used AES with a new random IV on each call. Save paths and list views were fine.
Two lookups that kept their old shape
Two helpers still worked the old way: load a user by email, and find-or-create. Each one encrypted the email it was given and asked the database for a row with the same value.
That can't work. The random IV means one address encrypts to a different string every time:
seal("[email protected]") == seal("[email protected]") # False
So the search finds nothing, and it finds nothing without an error. Load-by-email treats the person as a stranger. Find-or-create sees the miss and makes a second account. The unique rule on the column can't block it, because the two encrypted strings differ.
Login had its own bug. It still compared the typed address to a column that now held encrypted text. Another commit that day, the one that encrypts the first admin at setup, changes login to the same broken probe. I don't know which version ever ran, and I have no record of anyone being locked out. This is what the code would do.
A scan where the index used to be
The next commit starts from the raw-address login, not the setup commit's probe. The two are side branches, not steps. It gave up on the database search. Login and both helpers now read the users, decrypt each email, lowercase both sides, and compare. First match wins.
That works, but it's a scan. Each sign-in decrypts rows until it hits. An address that isn't there costs the whole table, and the login version reads a password hash for every row it passes. That's probably fine for a few stores of staff. I still don't like a miss being the slowest case.
Lowercasing came along too. I think that's right for email, but it showed up inside a compare, not as a written rule.
The decrypt helper got forgiving
Later that day, the decrypt helper stopped throwing. If a value won't decrypt, it hands it back as is, so old plaintext rows still read. That helps during a move. Left alone, a wrong key throws no error. Nothing matches and the app acts like it has no users. The key-rotation script from that day has a similar habit: rows it can't decrypt get a warning and get skipped.
None of the commits I read touched a test. Save a user, look them up by the same email, and the first bug shows up.
What I'd build next time
Next time I'd add a second column next to the encrypted email. It holds a keyed hash of the lowercased address, made with its own key, and it gets an index and a unique rule. A lookup searches that hash, then decrypts the one row it finds to make sure. The database does the narrowing again, and duplicates get blocked again. That costs me one more key to keep safe. I'll pay that over a login that walks the whole table.