What is a schema migration?

Why should every schema change be a migration file?

Because a change made by hand on production cannot be tested first, repeated on staging or reviewed by anyone. A migration file can. It is written as SQL, committed next to the code that needs it, run by the same pipeline everywhere, and recorded in the database so it is never applied twice.

In a prototype, the database changes whenever a prompt needs it to, often directly on the live project. That works while there is no data worth keeping. Once there is, the gap between what the code expects and what the live database holds is where outages start.

How do you create and apply a migration in a Supabase project?

With the Supabase CLI, each change becomes a timestamped SQL file in the supabase/migrations folder, tested on a local database and then pushed to the hosted project. Supabase records which files each database has already run, so a push only applies the new ones, in order.

1. 01Create the filesupabase migration new add_invoices_table writes an empty, timestamped SQL file into supabase/migrations. If you made the change in the local dashboard instead, supabase db diff -f add_invoices_table writes the SQL for you.
2. 02Rebuild locallysupabase db reset rebuilds the local database from every migration and then runs supabase/seed.sql, which proves the files still work from scratch.
3. 03Pushsupabase db push compares the folder with the supabase_migrations.schema_migrations table on the remote database and runs only what is missing. Run it on staging before production.

Supabase's documentation warns that editing the remote database directly causes sync errors on the next push, and that only one person should push at a time. In a Lovable project that means deciding who owns the database before a developer joins: the handover guide covers that step.

What makes a schema migration safe to run on production?

A safe migration never leaves the running code facing a database it does not understand. The pattern for that is parallel change, also called expand and contract: add the new structure beside the old, move every reader and writer across, and remove the old structure only once nothing uses it.

• Expand: add the new column or table, so old and new code both work.
• Migrate: backfill the data and switch the code over, which can take several releases.
• Contract: drop the old column in a later migration, once nothing reads it.
• Test on a copy with realistic data, behind a recent backup with point-in-time recovery where you can.
• Write row-level security policies in the same migration as the table they protect.

What are the common schema migration mistakes in AI-built apps?

The usual ones are changes made in the dashboard that never became files, a column renamed in one step while the live code still reads the old name, and tables created without row-level security because the policy was meant to follow later. Each one passes a demo and fails with real users.

If your project already has drift between its files and its live database, that is the kind of untangling our vibe coding rescue starts with.