When someone ALTERs your table behind our back

Here's the assumption most tools in this category quietly make: that they are the only thing writing to your database. It's a comfortable assumption and it is wrong. Your application has migrations. Your colleague has psql. Your ORM has opinions.

Since the database is yours, not a copy we keep, SchemaStack has to cope with the schema changing underneath it. That's drift: our picture of your database and your actual database, no longer the same picture.

Two checks, on purpose

The quick one hashes both shapes. Walk the stored metadata into a deterministic string — tables sorted, then per column name:type:nullability:unique:pk, then foreign keys with their cascade rules, then indexes — and SHA-256 it. Do the same to the live database. Same hash, no drift. It's one comparison and answers the only question you usually have.

The full one introspects and reports. Tables added, tables removed, and per modified table: columns added, removed and altered, primary key changes, unique constraint changes, index changes, and foreign key cascade changes. Column comparison is field by field — type, nullability, uniqueness, defaults — so "the column is still there but it's TEXT now" is a finding, not a shrug.

Then sync reconciles: it updates our metadata to match your database. Note the direction. Sync issues no DDL. It will never alter your database to match our metadata — that's what the previewed migrations are for, and they're a deliberate action, not a reconciliation.

What sync does do is delete metadata for columns your database no longer has, and — on a full sync — views for tables that are gone. That's a real deletion of configuration: display names, widget types, validation rules attached to a column that vanished. The underlying data is untouched, because the underlying column already went.

Two bugs this post found

Writing this honestly meant reading the code carefully, which surfaced two things worth admitting rather than quietly patching:

Computed columns were being deleted by sync. A formula column exists only in metadata — evaluated at query time, no database column behind it. The hash path knew to ignore them. The drift path knew to ignore them. The diff path didn't, so every sync saw a column "removed from the database" and deleted it, along with its constraints. Our own documentation promised they were preserved. Fixed, with a test.

Imported database views drifted forever. Import reads tables and views; drift and hashing read only tables. So any imported view read as "removed from the database" on every single check — permanent, unfixable false-positive drift, and a hash that never matched. Fixed by making all three introspections agree, which is exactly what our internal schema checklist says they must.

Both had been true for as long as those features had existed, and both were invisible because nothing compared the three code paths against each other. That's the argument for checking claims before publishing them: the post came first, the bugs came out because of it.

Migrations, meanwhile

Drift is about changes from outside. Changes from inside — you editing a column type in the grid — go through migration coordination, which knows what each engine actually locks:

While a blocking migration runs, data endpoints answer 503 with Retry-After rather than hanging, and only one blocking migration per table is allowed to start. A migration can't lock a table indefinitely — there's a hard ceiling regardless of how badly its duration was estimated.

What it doesn't do (yet)

• Drift covers tables, columns, primary keys, single-column uniques, indexes and foreign key cascade rules. It does not see triggers, stored procedures, functions, materialized views, sequences, CHECK constraints or row-level security policies. If someone adds a trigger, we won't tell you.
• A new or dropped foreign key isn't reported as such — only cascade-rule changes on columns we already know about. FK topology drift shows up indirectly, through the columns.
• Sync's deletions aren't reversible. Removed columns are hard-deleted from metadata, not hidden — the display name, widget type and validation rules attached to a column that vanished go with it.
• sync_view_columns only backfills views that have none — it doesn't reconcile existing view columns, despite what its name suggests.
• import_schema and sync_schema are the same operation. Two names, one behaviour.
• The blast radius of a cascading foreign key isn't computed. The migration guard checks the table you're changing, not the tables a cascade would reach.
• There's no scheduling: the dry run tells you a migration will block reads for four minutes, and then it's on you to pick the hour.

The schema migrations guide covers the mechanics, and database compatibility lists what's understood fully, partially, and not at all.