Sync UI-created tables/functions into local migrations

[ahmedsam199]

When a table, function, or other schema object is created through the Studio UI, there's currently no easy way to bring that change into local migrations — you either rewrite the SQL manually or pull a full DB dump.

Idea: Track the SQL definition of anything created via the UI in an internal table, then let developers run a command locally to pull all pending changes and generate proper migration files in one go — instead of one huge SQL dump.

[pacocartones]

This would remove a real source of drift. I would make the first version an explicitly reviewable, non-authoritative export, rather than treating an internal record of UI-generated SQL as the source of truth.

For example, an export could generate one migration made of object-level DDL, ordered deterministically by dependencies, together with a base-state fingerprint (such as the local migration head plus a schema checksum). Before writing the migration, the command could refuse or require an explicit choice when that base state no longer matches.

That gives useful behaviour in the cases that are otherwise risky:

• a table is created in Studio, then exported locally;
• manual SQL changes happen after the Studio change;
• two branches each create schema objects in Studio and later converge.

In the latter two cases, surfacing a conflict is preferable to silently producing a migration that duplicates or overwrites DDL. How do you see this composing with supabase db diff and with migrations that have already been applied outside Studio? A small acceptance matrix for those paths would make the feature especially trustworthy.

[ahmedsam199]

The core problem: local migration history and production's actual schema can silently diverge, since Studio writes DDL straight to the DB with no corresponding migration file.

Concrete example: you add a column via Studio directly on production. Later, someone writes a new migration locally against the local schema, which has no idea that column exists. Now if you rebuild staging (or any new env) from migrations, that column never gets created — the environments have permanently diverged, and nobody notices until something breaks.

You can fix those by either

Single source of truth: track everything SQL-related (tables, functions, etc.) in one place regardless of whether it originated in Studio or a local migration.
Or go the other way and lock Studio from writing DDL directly, so migrations stay the only path into the schema.

Also worth noting on db diff: if you had a single source of truth for schema definitions, you wouldn't need to diff your database structure to detect drift in the first place — the source of truth would already reflect every change, Studio or migration. db diff is really a workaround for the lack of that single source.

Like looking into it my idea was like fast suggestion on the problem im 100% sure there is a way better to solve this issue

[pacocartones]

You are describing the right invariant: every schema change that may be deployed must eventually be represented in version control. I would separate that goal from the mechanism, though.

There are three different records here:

1. The live Postgres catalog — authoritative for the project’s current state.
2. Migration files (or declarative schema files) in Git — authoritative for reproducing that state in a new environment.
3. supabase_migrations.schema_migrations — an application ledger: it says which migration versions ran, but it cannot describe every semantic change by itself.

That distinction is why I would not make an internal log of Studio-generated SQL the single source of truth. It would miss changes from the SQL editor, direct connections, extensions, and emergency operations; it would also make the log a second schema-representation system that can drift in its own way.

The current primitives already show the right recovery model: db diff -f captures local Dashboard changes as a migration, while db pull reconciles remote drift and migration repair fixes the applied-history record when necessary. The opportunity is to make that path safe and obvious, not to replace it with an opaque UI-DDL ledger.

A strong feature shape could be “capture remote schema changes”:

• preflight the local migration head, remote migration history, and a remote-vs-shadow schema diff;
• present normalized, object-level DDL plus unsupported/ambiguous changes for review;
• create a named local migration only after the user approves the exact diff;
• require an explicit resolution when the base fingerprint has changed, instead of generating a migration on an unknown branch state;
• keep an audit trail of provenance (actor, timestamp, source: Studio/SQL editor/CLI), but treat it as diagnostics—not as canonical schema.

That gives the desired workflow for “I added a column in production and need to bring it back into Git,” while remaining correct when the mutation did not originate in Studio.

I would measure an MVP against four acceptance cases: a single Studio change; a Studio change followed by manual remote SQL; two branches that both diverge from the same remote base; and a remote change already captured by db pull. In every non-clean case, the tool should stop with an intelligible conflict report rather than silently manufacture history.

Would that “reviewable capture + provenance” framing solve the pain you have in mind, while preserving migrations/Git as the reproducible source of truth?

[ahmedsam199]

The last line is literally what happened there was an app with multiple people working on it. Different devs were making schema changes directly in Studio on their own branches/environments, and when those branches converged, the local migration history didn't reflect what actually existed in Postgres anymore. Nobody had a single point where the changes comes from so the drift just sat there until something broke now i basically had the app i have no way to run it since some core table are getting created from the studio which does not exist on the migrations.
Like at least there should be one place to re run the app with latest setup on production locally at least for testing and development purposes

[pacocartones]

@ahmedsam199 Yes — that is no longer mainly an export problem; it is a recovery and re-baselining problem. The important good news is that you do not need to reconstruct the original sequence of Studio clicks to recover. You need the team to deliberately choose the current production catalog as the canonical snapshot at one point in time, then make Git reproducible from it.

A safe recovery runbook would be:

1. Freeze schema DDL briefly and take a schema-only backup/fingerprint of the canonical production project.
2. On a recovery branch, compare the schema produced by the current migration head with that canonical catalog and generate a reconciliation migration containing only the missing/changed objects.
3. Review that migration as normal code, then prove the invariant in a clean database: empty database + all migrations must produce the same schema fingerprint as production.
4. Commit the reconciliation migration to Git so fresh local, CI, preview, and staging environments can finally reproduce the application.
5. Do not blindly execute that migration against the canonical production database, where its objects already exist. After review, record that exact migration as already applied there through the existing migration-history repair flow; any non-canonical environment receives the migration normally.

That separates two things that are easy to conflate: adopting the current production state into history and running a change against production. The first is what repairs your project; the second would be dangerous in this case.

For the product, I think this deserves a first-class guided command such as “Adopt remote schema into migrations”, not merely another diff button. Its preflight should show: local migration head, remote migration history, object-level diff, and a schema fingerprint. Its final screen should make the two actions explicit: “write reviewed migration to Git” and, only for the canonical remote, “mark this migration as already represented”. If the fingerprint changes during the flow, it must stop rather than guess.

The success criterion is wonderfully concrete: a new developer can clone the repository, reset locally, and run the app without any hidden Studio-created table. Once that invariant holds, Studio can still be convenient, but every change needs a visible path back into version control.

Would a guided recovery path like this have let you rescue that application without manually rediscovering every missing table?