Someone hotfixed prod in the SQL editor at 2am. They added an index to stop a query from timing out, told nobody, and went back to bed. It worked. The incident closed. And now your production database no longer matches the schema in your migrations — nobody knows, and it will stay that way until the next deploy fails for a reason no one can explain.
That gap between what your migrations say your database is and what it actually is has a name: drift. This post is about catching it before it catches you.
What schema drift actually is
Drift is any schema change that happens outside your migration flow. A column added by hand. An index dropped to reclaim disk. A default changed in the dashboard. A permission tweaked to unblock a support ticket.
None of these are mistakes on their own. The mistake is that they never made it back into version control, so every environment now tells a slightly different story. Your dev database, your staging database, and your prod database have quietly diverged, and the migration you’re about to run was written against a schema that no longer exists.
The reason drift is dangerous is not the change itself — it’s the invisibility. You can’t diff what you can’t see.
Detecting drift by hand
You don’t need a tool to start. Postgres already knows its own shape, and you can ask it. The information_schema and pg_catalog views expose every table, column, type, default, and constraint.
Here’s a query that lists the full column-level shape of your public schema:
select
table_name,
column_name,
data_type,
is_nullable,
column_default
from information_schema.columns
where table_schema = 'public'
order by table_name, ordinal_position;Run that against two environments and you have two lists you can diff. But eyeballing a few hundred rows across dev and prod is exactly the kind of task humans are bad at. Comparing a single value is easier.
You can collapse the whole schema into one fingerprint — a hash that changes if anything about the columns changes:
select md5(
string_agg(
table_name || column_name || data_type || is_nullable ||
coalesce(column_default, ''),
'|' order by table_name, ordinal_position
)
) as schema_fingerprint
from information_schema.columns
where table_schema = 'public';Run it against staging and prod. If the two fingerprints match, the column layout is identical. If they don’t, something moved — and you go back to the first query to find out what.
This is enough to catch the obvious cases. It’s also where the manual approach starts to hurt.
Where hand-rolling stops working
The fingerprint above only covers columns. A complete drift check has to account for indexes, constraints, sequences, functions, triggers, row-level security policies, and grants — each living in a different catalog view with its own quirks. You end up maintaining a small pile of SQL that itself drifts.
Then there’s the harder problem: nobody is watching. A fingerprint only helps if someone runs it. Drift doesn’t announce itself, so the check has to be continuous, and it has to run against production where you’re least likely to want a cron job you wrote at 2am poking at the catalog.
Supabase branching gives you preview databases to develop against, which is genuinely useful — but a preview branch tells you nothing about whether prod drifted after you branched. Point-in-time comparison is a different job from environment provisioning.
How LeafSQL handles it
LeafSQL takes a snapshot of each environment’s full schema — we call these leaflets — and stores them per environment: dev, staging, prod. A leaflet is the complete structural fingerprint, not just columns: tables, indexes, constraints, functions, policies, the lot.
When a new snapshot of an environment no longer matches its last known-good leaflet, that’s drift, and LeafSQL surfaces it as an alert instead of waiting for you to ask. You see exactly which objects changed, in which environment, and how the two versions differ — the same structured diff you’d get from the fingerprint approach above, minus the pile of catalog SQL you’d otherwise own forever.
From there the fix is a normal one: reconcile the change into a migration PR — the forward and rollback SQL, pushed to your GitHub repo — so the out-of-band change becomes a reviewed, versioned one. The 2am index gets a name, a diff, and an author.
We’re deliberately narrow about what this does. LeafSQL detects and diffs drift; it doesn’t silently “heal” it by running SQL against prod on your behalf. Agents propose, you approve. The reconciliation is a PR you read, not a background job you trust.
Where to start
If you have five minutes, run the fingerprint query against two environments right now. It’s the cheapest drift check there is, and it will tell you today whether your databases already disagree. Most teams are surprised.
If you’d rather not run it by hand forever, that’s the gap LeafSQL fills — continuous leaflets per environment, drift alerts, and reconciliation as a migration PR. You can read more about how environment diffs work, why we treat AI-generated writes as guarded, reviewable changes, or see the schema workflow in the product.
Drift is normal. Not knowing about it is the part you can fix.