My code review and live database disagreed. How do you catch schema drift?
I’m about to let visitors try my app before they sign up. The easy way is anonymous sign-ins: every visitor gets a real user ID, so limits, row-level security, and the saved result all keep working, and signing up later converts that same user.
Before writing any of it, I had Claude Code read the code, and I separately queried the live database. The two disagreed.
The code review said the results table requires a user id. The live column is nullable. The migration files said one thing and the database did another.
My users table requires an email, and a trigger copies the email from the auth table into it every time a user is created. An anonymous user has no email. I haven’t tried it, but a trigger that fails inside sign-up usually takes the whole sign-up down with it. On launch day that would have looked like a vague “database error”, and I would have blamed the auth setting.
The fix is a new migration that lets email be empty, then a check of every query and job that assumes an email exists (the weekly email, for one). It isn’t shipped yet.
The lesson: migration files are what I meant to do, and the live database is what’s true. I’ve already been bitten by silent failures from missing policies, and I still trusted a summary of the code over the database.
How do you keep migrations and the live schema honest? A schema diff in CI, a dump you compare by hand, or just querying production before every change?
Replies
An agent resolved a broken migration test by loosening the assertion instead of updating the model, which caused a schema mismatch. The suite stayed green for days while prod drifted. Check the diff on test files before even looking at the migration.
same rule here.. a new column goes in by hand in the sql editor, so before any deploy that reads it the agent asks prod for that one column first, and if it comes back missing nothing ships. for the trigger i'd sign up one throwaway anon user on a copy before launch and watch what breaks. are you on supabase, and do you get a branch to try that on?
My app sits on a document database with no enforced schema, so drift is the default there, and the defence moved into the code instead. Every document is read into a network model where every single field is nullable, all parsing and null handling lives in that one file, and a mapper turns it into the clean model the app uses, applying defaults. The screens never see the raw shape. The document shapes are written down in one notes file, and that file is what gets checked when something changes, not my memory of the code. For your case, your own line says it: treat the live database as the truth, and make the code survive the email being missing, rather than trusting that it never will be.
Same lesson here. The migration files are a copy, the live database is the original. Before any change I take a dump, check it worked, and read the schema straight from production instead of trusting the files. Same rule I use for money: I trust the bank statement over my own books.