Safe database migrations for an app that is already live

Treat the schema as a contract with deployed clients

A mobile release does not replace every installed app at once. Some people update immediately, others keep the previous version, and background requests may arrive from either. Your database change must account for that overlap.

Imagine adding a preference that the new dashboard reads. If the app reaches production before the column exists, the query fails. If the migration removes a field the old dashboard still selects, existing installations can fail instead. The release involves both schema order and client compatibility.

Write down which app versions, API handlers, scheduled jobs and reports use the affected object. Include reads, writes, defaults and permissions. This inventory becomes the contract you verify before changing it.

Capture the change in migration history

A migration is a versioned database change, not just SQL copied into a dashboard. Supabase's migration guide explains how migration files record schema changes and move them between environments. Once that workflow is established, untracked remote edits can leave the real schema and migration history inconsistent.

Review the migration in version control alongside the code that depends on it. A reviewer should see what changes, which existing behavior must continue and how you will verify the result.

Use a disposable local database for reset-and-replay checks. For a live database, rehearse the upgrade from a representative existing schema too. Starting empty does not test the rows and application assumptions accumulated over time.

Expand first, migrate usage, then contract

For a breaking change, consider an expand-and-contract sequence. Add the compatible structure first, deploy code that can use it, migrate existing data deliberately, and remove obsolete structure only after its consumers no longer require it.

For example, replacing one free-text customer name with separate fields may require both representations during the transition. Define which representation is authoritative and how updates remain consistent. Two writable copies without a synchronization rule can diverge.

PostgreSQL's table-alteration documentation describes the behavior of adding columns and constraints. The Prisma expand-and-contract example illustrates separating a data transition from final cleanup. Read the details for your database version rather than assuming every alteration is equally cheap. Consider locks, table size, null values and constraint validation before scheduling the work.

Test authorization as well as the new field

A schema change can appear successful under an administrator account while ordinary users lose access or see data they should not see. Exercise the application using the same authenticated roles it uses in production.

In Supabase, review row-level security policies, grants and functions affected by the change. The RLS documentation explains how policies control row access. Keep elevated server credentials out of client applications.

• A customer can read and update their own permitted records.
• Another customer cannot read or update those records.
• The old app still completes its essential queries.
• The new app handles an existing row with no newly entered data.

Test direct API access where appropriate; hiding a button does not enforce database authorization.

Separate code rollback from data recovery

Rolling the app back does not automatically reverse a backfill, restore a removed column or recover overwritten values. Define recovery for each part of the change before deploying it.

For an additive migration, reverting the new application behavior while leaving the unused addition in place may be sufficient. A destructive change needs a different plan. Identify what a restore would recover, what newer writes it could lose and how you would preserve or reconcile those writes.

For a large backfill, decide how to batch work, observe progress and resume safely after interruption. Make the transformation repeatable where possible, with explicit checks for completed rows. Do not treat repeated execution as safe merely because the script finishes twice.

Promote evidence, not assumptions

Supabase's environment guide describes testing and releasing changes through separate environments. Use staging to rehearse the actual order: compatible schema, application release, data transition and eventual cleanup.

Record the migration version, application build, checks performed and stop conditions. For the illustrative dashboard change, evidence would include successful old and new client queries, correct permissions and expected values for preexisting accounts.

After release, watch query failures, permission errors and the changed user workflow. The deployment is complete when the expected behavior works for the supported clients and you can explain how to recover if it does not.